Sign up to take part
Registered users can ask their own questions, contribute to discussions, and be part of the Community!
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
dataiku.api_client().sql_query
and
dataiku.SQLExecutor2(connection="xxx").query_to_df
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"
)
Thanks @CatalinaS
Any update ? why it is working with notebook but not on recipe or scenario ?