Any way to duplicate this SQL query behavior using Python?

Options
info-rchitect
info-rchitect Registered Posts: 169 ✭✭✭✭✭✭
edited July 16 in Using Dataiku

Hi,

We need this behavior to work when executing SQL from Python.

show grants on database MYDB;
-- DKU_END_STATEMENT
create or replace temporary table db_check as
    select EXISTS(
        select * from table(result_scan(-1)) where "privilege" = 'USAGE' and "granted_on" = 'DATABASE' and "grantee_name" = 'MYROLE'
    ) as db_ok
;
-- DKU_END_STATEMENT
show grants on schema MYDB.MYSCHEMA;
-- DKU_END_STATEMENT
create or replace temporary table schema_check as
    select EXISTS(
        select * from table(result_scan(-1)) where "privilege" = 'USAGE' and "granted_on" = 'SCHEMA' and "grantee_name" = 'MYROLE'
    ) as schema_ok
;
-- DKU_END_STATEMENT
show grants on table MYDB.MYSCHEMA.MYTABLE;
-- DKU_END_STATEMENT
create or replace temporary table table_check as
    select EXISTS(
        select * from table(result_scan(-1)) where "privilege" = 'SELECT' and "granted_on" = 'TABLE' and "grantee_name" = 'MYROLE'
    ) as table_ok
;
-- DKU_END_STATEMENT
with combined as (
    select distinct db_ok, schema_ok, table_ok, (db_ok and schema_ok and table_ok) as rest_api_ok
    from db_check, schema_check, table_check
)
select * from combined;

When I try either of the following, I get the same error:

ERROR: Job failed: Error in Python process: At line 44: <class 'Exception'>: Query failed: b'Actual statement count 7 did not match the desired statement count 1.'

dku_connector = SQLExecutor2(connection='MYCONNECTION')

# ATTEMPT #1:
df = dku_connector.query_to_df(sql)

# ATTEMPT #2

mydataset = dataiku.Dataset('mydataset')
dku_connector.exec_recipe_fragment(output_dataset=mydataset, query=sql, overwrite_output_schema=True)

Just to be clear, the behavior required is the retention of state, such that temporary tables do not disappear so the final select statement can access those temporary tables.

thx much


Operating system used: Windows 10

Answers

  • Zach
    Zach Dataiker, Dataiku DSS Core Designer, Dataiku DSS Adv Designer, Registered Posts: 153 Dataiker
    edited July 17
    Options

    Hi @info-rchitect
    ,

    You can accomplish this by using pre_queries to create the temporary tables.

    For example, the following script will create a temporary table based on the existing table "other_table", then access the temporary table in the main query:

    from dataiku import SQLExecutor2
    
    executor = SQLExecutor2(connection="MY_CONNECTION")
    
    pre_queries = [
        "CREATE TEMPORARY TABLE temp_table AS SELECT * FROM other_table",
    ]
    query = "SELECT * from temp_table"
    
    df = executor.query_to_df(query, pre_queries=pre_queries)

    Reference documentation: https://developer.dataiku.com/12/api-reference/python/sql.html#dataiku.SQLExecutor2.query_to_df

Setup Info
    Tags
      Help me…