Sign up to take part
Registered users can ask their own questions, contribute to discussions, and be part of the Community!
I try to use python sdk to execute an sql query (with pre queries) like I'm able to do in SQL notebooks to create a redshift external table.
(Similarly I also tried to do it using scenario and sql recipe but I have the same issue)
In SQL notebook, I'm able to execute my queries but I have to select "Execute out of transaction" to make my query (cf. screenshot):
In python, I try to use
In both case, I receive the error message
ERROR: CREATE EXTERNAL TABLE cannot run inside a transaction block
I look at the documentation (respectively https://doc.dataiku.com/dss/latest/python-api/client.html#dataikuapi.DSSClient.sql_query and https://doc.dataiku.com/dss/latest/python-api/sql.html?highlight=sqlexecutor2#dataiku.SQLExecutor2.q...). In both case, I did not found a way to run my query outside of a transaction block.
Can I have some help to execute an sql query "out of transaction" using python code? Alternatively
(ps : I'm aware that redshift external tables are not readable from dataiku 9.)
Operating system used: Os X
There it is my code summarize:
import dataiku client = dataiku.api_client() connection = "MY_REDSHIFT_CONNECTION" query = """ create external table external_dataiku.bar ( foo varchar(100), alpha varchar(100), beta varchar(100) ) row format delimited fields terminated by '\t' stored as textfile location 's3://my_S3_bucket/mydata.csv' TABLE PROPERTIES ('skip.header.line.count'='1'); """ client.sql_query( "SELECT * FROM external_dataiku.bar", connection=connection, pre_queries=[query], type='sql', project_key="TEST_REDSHIFT" )