SNOWPARK - How to use a sql command returned in a dataframe in anoter sql command downstream

Question

How do I return the sql command in sql_df1 and automate the use of this command in another sql command

sql_df1= test_session.sql("select get_ddl('TABLE', 'A.TEST')")
sql_df1.show()

---------------------------------
|"GET_DDL('TABLE', 'A.TEST')"  |
---------------------------------
|create or replace TABLE TEST (  |
|   ID NUMBER(38,0),         |
|   AGE NUMBER(38,0),             |
|   YEAR NUMBER(38,0)             |
|);                             |
---------------------------------

I want to use the above command in the dataframe in a new sql command like below

sql_df2= test_session.sql(sql_df1)

Answer 1

There a few ways to do it.

If you're importing pandas, then something like this would work:

df = session.sql("SELECT GET_DDL('table', 'CITIBIKE_TRIPS')")
pdf = df.to_pandas()
print(pdf.values[:1][0][0])

I get as output:

create or replace TABLE CITIBIKE_TRIPS (
    RIDE_ID VARCHAR(16777216),
    RIDEABLE_TYPE VARCHAR(16777216),
    STARTED_AT TIMESTAMP_NTZ(9),
    ENDED_AT TIMESTAMP_NTZ(9),
    START_STATION_NAME VARCHAR(16777216),
    START_STATION_ID NUMBER(38,0),
    END_STATION_NAME VARCHAR(16777216),
    END_STATION_ID NUMBER(38,0),
    START_LAT FLOAT,
    START_LNG FLOAT,
    END_LAT FLOAT,
    END_LNG FLOAT,
    MEMBER_CASUAL VARCHAR(16777216)
);

Or using direct slicing on the Dataframe (not very beautiful though):

df = session.sql("SELECT GET_DDL('table', 'CITIBIKE_TRIPS')").collect()
ddl = str(df).split('=')[1].split(')]')[0]
print(ddl)

I get:

'create or replace TABLE CITIBIKE_TRIPS (\n\tRIDE_ID VARCHAR(16777216),\n\tRIDEABLE_TYPE VARCHAR(16777216),\n\tSTARTED_AT TIMESTAMP_NTZ(9),\n\tENDED_AT TIMESTAMP_NTZ(9),\n\tSTART_STATION_NAME VARCHAR(16777216),\n\tSTART_STATION_ID NUMBER(38,0),\n\tEND_STATION_NAME VARCHAR(16777216),\n\tEND_STATION_ID NUMBER(38,0),\n\tSTART_LAT FLOAT,\n\tSTART_LNG FLOAT,\n\tEND_LAT FLOAT,\n\tEND_LNG FLOAT,\n\tMEMBER_CASUAL VARCHAR(16777216)\n);'