is it possible to write a sql query in a python UDF in snowflake snowpark?-CodePudding

i'm trying to create a new UDF function inside snowflake. in this UDF, I need to write a SQL query that will return a list of tables, and than I need to do some python code around it, like this example:

create or replace function SnowparkPrivateSchema()
returns string
language python
runtime_version=3.8
handler='SnowparkPrivateSchema'
as $$
def SnowparkPrivateSchema(self, symbol, quantity, price):
   get_tables = '''select table_name from  INFORMATION_SCHEMA.TABLES'''
   for table in get_tables:
      '''create or replace table clone_user.{table} clone {table}'''

$$;

CodePudding user response：

from snowflake.snowpark import Session
session =  Session.builder.configs(connection_parameters).create()
get_tables = session.sql("select table_name from  INFORMATION_SCHEMA.TABLES")

CodePudding user response：

You cannot execute SQL inside of a Snowflake UDF (the same is true for Python/Snowpark as well as standard SQL, Javascript, etc.). UDFs can only take in one row of data, and output a scalar value from that input.

You can execute SQL from a Snowpark Python stored procedure (as well as a stored proc written w/ any other language); A Python stored procedure can be used to execute SQL just like a SQL stored proc, including potentially invoking your UDF that performs some operation on the data returned from your SQL queries.