SQLExecutor2 exec_recipe_fragment multiple statements and

cronos003
cronos003 Registered Posts: 7 ✭✭✭✭
edited July 18 in Using Dataiku

I'm following the tutorial found here https://www.dataiku.com/learn/guide/code/python/use-python-sql.html but had some additional questions.

1. Is it possible to submit multiple statements with SQLExecutor2.exec_recipe_fragment()? The script creates multiple volatile tables before constructing the final table. I want to use exec_recipe_fragment to perform the entire SQL script in-database.

2. Is it possible to run exec_recipe_fragment without specifying an input source? I'm able to start a SQL - SQL script recipe without specifying an input dataset so it seems like I should be able to accomplish the same here.

I'm essentially trying to recreate the functionality of the recipe SQL - SQL script but with additional error handling that will be coded in python.

Psuedo code of what I'm trying to accomplish:


import dataiku
import pandas as pd, numpy as np
from dataiku.core.sql import SQLExecutor2

executor = SQLExecutor2(connection="Tera_conn")

sql = """
CALL TERA_DB.conditional_drop('TERA_DB', 'ProjKey_TABLE_TO_CREATE', outmsg);


CREATE MULTISET TABLE TERA_DB.ProjKey_TABLE_TO_CREATE
, NO FALLBACK, NO BEFORE JOURNAL, NO AFTER JOURNAL AS
(
SELECT top 1 * from TERA_DB.OTHER_TABLE
)
WITH DATA
PRIMARY INDEX(dw_acct_key);
"""

SQLExecutor2.exec_recipe_fragment(query = sql)

Tagged:

Best Answer

Answers

  • cronos003
    cronos003 Registered Posts: 7 ✭✭✭✭
    Thanks for input. I got it to work.

    However the automated table handling is defeating my purpose for using SQL from a Python recipe. Ideally I would be able to replicate the functionality of a 'SQL - SQL script' recipe using a Python recipe. I had originally tried SQL from a Python recipe to perform some error handling but the fact that the final table is dropped before any SQL statements are sent undermines the whole point of performing some error checking on the tables.

    Thanks for the link. I had found it earlier but the documentation seems rather lacking.
  • info-rchitect
    info-rchitect Registered Posts: 179 ✭✭✭✭✭✭

    This 'Connection name not specified' error should be a bug that is fixed, when running the 'execute_recipe_fragment' from a Python recipe.

Setup Info
    Tags
      Help me…