3

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}'''

$$;
Sergiu
  • 4,039
  • 1
  • 13
  • 21
user17694172
  • 31
  • 1
  • 2

2 Answers2

5

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.

clb
  • 86
  • 1
-1
from snowflake.snowpark import Session
session =  Session.builder.configs(connection_parameters).create()
get_tables = session.sql("select table_name from  INFORMATION_SCHEMA.TABLES")
Dave Welden
  • 1,122
  • 6
  • 6