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

Options
JeanBaptisteP
JeanBaptisteP Dataiku DSS Core Designer, Registered Posts: 3 ✭✭✭

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

Capture d’écran 2022-08-26 à 15.32.16.png

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

Answers

  • Catalina
    Catalina Dataiker, Dataiku DSS Core Designer, Registered Posts: 135 Dataiker
    Options

    Hi @JeanBaptisteP
    ,

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

  • JeanBaptisteP
    JeanBaptisteP Dataiku DSS Core Designer, Registered Posts: 3 ✭✭✭
    Options

    There it is my code summarize:

    import dataikuclient = dataiku.api_client()connection = "MY_REDSHIFT_CONNECTION"query = """create external tableexternal_dataiku.bar (foo varchar(100),alpha varchar(100),beta varchar(100))row format delimitedfields terminated by '\t'stored as textfilelocation '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
    JeanBaptisteP Dataiku DSS Core Designer, Registered Posts: 3 ✭✭✭
    Options

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

Setup Info
    Tags
      Help me…