Dropping Database Tables from python

stainlessteelra
stainlessteelra Registered Posts: 4 ✭✭✭

Trying to use dataiku's python environment to operationalize some tasks.

Specifically here it is needing to drop a table from a Redshift connection. I can successfully get the code to read the data but if i change the query to drop the table, then it fails.

api_key = personal_api_key

local_host = "host_name"

client = dataikuapi.DSSClient(local_host, local_api_key)
client._session.verify = False

CONNECTION = "nonprod_redshift"
QUERY = '''drop table schema.table_name;'''


# Using remote client, query against Redshift util table containing info
results = client.sql_query(connection=CONNECTION, query=QUERY)


Operating system used: Windows

Tagged:

Best Answers

  • Alexandru
    Alexandru Dataiker, Dataiku DSS Core Designer, Dataiku DSS ML Practitioner, Dataiku DSS Adv Designer, Registered Posts: 1,217 Dataiker
    edited July 17 Answer ✓

    Hi @stainlessteelra
    ,

    With sql_query() you can only result as an iterator and will not be able to use used to drop tables.

    However, if you want to be able to use the DSS connections details for the database one you can use a SQL Script Recipe. Create the SQL script recipe with a dummy output and place your drop statement there and then call their recipe from your python code. YOu can if needed update the SQL statement from the created recipe later on.

    Screenshot 2022-01-17 at 09.16.23.png

    import dataikuapi
    
    api_key = '<replace-api-key>'
    local_host = "http://localhost:11000"
    client = dataikuapi.DSSClient(local_host, api_key)
    client._session.verify = False
    
    project = client.get_project('PYTHON_TESTS')
    
    my_script_recipe = project.get_recipe("compute_test_dummy_dataset")
    my_script_recipe.run()
    

  • CoreyS
    CoreyS Dataiker Alumni, Dataiku DSS Core Designer, Dataiku DSS Core Concepts, Registered Posts: 1,150 ✭✭✭✭✭✭✭✭✭
    Answer ✓

    @stainlessteelra
    sounds like an interesting idea! Why not submit it on the Product Ideas Board? The Product Ideas board is here to let you share and exchange your ideas on how to improve Dataiku!

Answers

Setup Info
    Tags
      Help me…