Execute an SQL query out of transaction using python / sql recipe

JeanBaptisteP

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.query_to_df). 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

Catalina

Hi @JeanBaptisteP
,

Could you please share the python code that you are using?

JeanBaptisteP

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

JeanBaptisteP

Any update ? why it is working with notebook but not on recipe or scenario ?