Discover the winners & finalists of the 2022 Dataiku Frontrunner Awards!READ THEIR USE CASES

Dropping Database Tables from python

Solved!
stainlessteelra
Level 2
Dropping Database Tables from python

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

0 Kudos
2 Solutions
AlexT
Dataiker

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()

 

View solution in original post

0 Kudos
CoreyS
Dataiker Alumni

@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!

Looking for more resources to help you use Dataiku effectively and upskill your knowledge? Check out these great resources: Dataiku Academy | Documentation | Knowledge Base

A reply answered your question? Mark as ‘Accepted Solution’ to help others like you!

View solution in original post

0 Kudos
3 Replies
AlexT
Dataiker

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()

 

0 Kudos
stainlessteelra
Level 2
Author

Thanks @AlexT !

That got me to a solution, still not elegant as I would have hoped for.

@Dataiku Team, Please add a python call that allows direct SQL execution for other purposes other than iterating over rows

0 Kudos
CoreyS
Dataiker Alumni

@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!

Looking for more resources to help you use Dataiku effectively and upskill your knowledge? Check out these great resources: Dataiku Academy | Documentation | Knowledge Base

A reply answered your question? Mark as ‘Accepted Solution’ to help others like you!
0 Kudos