Ice pick quickstart#
Ice pick can be used to extend the Snowpark Session to add additional functionality.
[2]:
from ice_pick import extend_session
from snowflake.snowpark import Session
import configparser
[3]:
# Create the connection and extend the session with ice_pick
# assumes credentials are in "snowflake_creds.config"
config = configparser.ConfigParser()
config.read('snowflake_creds.config')
session = extend_session(Session).builder.configs(dict(config['DEFAULT'])).create()
[4]:
# example schema object in a snowflake account:
obj = session.create_schema_object('TEST', 'SCHEMA_1', 'CUSTOMER', 'TABLE')
[9]:
# Functions on a schema object
ddl = obj.get_ddl()
print(f"ddl: \n {ddl} \n")
description = obj.get_description()
print(f"description pandas dataframe: \n {description.head()} \n")
grants_on = obj.get_grants_on()
print(f"grant on object pandas dataframe: \n {grants_on.head()} \n")
grant = obj.grant(["SELECT"], "PUBLIC")
print(f"grant SELECT to PUBLIC role on object status: \n {grant}")
ddl:
create or replace TABLE CUSTOMER (
C_CUSTKEY NUMBER(38,0),
C_NAME VARCHAR(25),
C_ADDRESS VARCHAR(40),
C_NATIONKEY NUMBER(38,0),
C_PHONE VARCHAR(15),
C_ACCTBAL NUMBER(12,2),
C_MKTSEGMENT VARCHAR(10),
C_COMMENT VARCHAR(117)
);
description pandas dataframe:
name type kind null? default primary key unique key \
0 C_CUSTKEY NUMBER(38,0) COLUMN Y None N N
1 C_NAME VARCHAR(25) COLUMN Y None N N
2 C_ADDRESS VARCHAR(40) COLUMN Y None N N
3 C_NATIONKEY NUMBER(38,0) COLUMN Y None N N
4 C_PHONE VARCHAR(15) COLUMN Y None N N
check expression comment policy name
0 None None None None
1 None None None None
2 None None None None
3 None None None None
4 None None None None
grant on object pandas dataframe:
created_on privilege granted_on \
0 2023-03-26 13:33:32.371000-07:00 OWNERSHIP TABLE
1 2023-03-26 19:37:22.093000-07:00 SELECT TABLE
2 2023-04-04 20:19:04.698000-07:00 SELECT TABLE
name granted_to grantee_name grant_option granted_by
0 TEST.SCHEMA_1.CUSTOMER ROLE ACCOUNTADMIN true ACCOUNTADMIN
1 TEST.SCHEMA_1.CUSTOMER ROLE ACCOUNTADMIN false ACCOUNTADMIN
2 TEST.SCHEMA_1.CUSTOMER ROLE PUBLIC false ACCOUNTADMIN
grant SELECT to PUBLIC role on object status:
Statement executed successfully.
[12]:
# Using the schema filter to return multiple schema objects
sp_filter = session.create_schema_object_filter(["TEST", "TEST_*"], [".*"], [".*"], ["tables", "Procedures"])
schema_object_list = sp_filter.return_schema_objects()
# get the ddl for all of the returned objects:
ddl_list = [schema_obj.get_ddl() for schema_obj in schema_object_list]
for ddl in ddl_list:
print(f"ddl for schema object: \n {ddl}")
ddl for schema object:
create or replace TABLE CUSTOMER (
C_CUSTKEY NUMBER(38,0),
C_NAME VARCHAR(25),
C_ADDRESS VARCHAR(40),
C_NATIONKEY NUMBER(38,0),
C_PHONE VARCHAR(15),
C_ACCTBAL NUMBER(12,2),
C_MKTSEGMENT VARCHAR(10),
C_COMMENT VARCHAR(117)
);
ddl for schema object:
create or replace TABLE LINEITEM (
L_ORDERKEY NUMBER(38,0),
L_PARTKEY NUMBER(38,0),
L_SUPPKEY NUMBER(38,0),
L_LINENUMBER NUMBER(38,0),
L_QUANTITY NUMBER(12,2),
L_EXTENDEDPRICE NUMBER(12,2),
L_DISCOUNT NUMBER(12,2),
L_TAX NUMBER(12,2),
L_RETURNFLAG VARCHAR(1),
L_LINESTATUS VARCHAR(1),
L_SHIPDATE DATE,
L_COMMITDATE DATE,
L_RECEIPTDATE DATE,
L_SHIPINSTRUCT VARCHAR(25),
L_SHIPMODE VARCHAR(10),
L_COMMENT VARCHAR(44)
);
ddl for schema object:
create or replace TABLE CUSTOMER (
C_CUSTKEY NUMBER(38,0),
C_NAME VARCHAR(25),
C_ADDRESS VARCHAR(40),
C_NATIONKEY NUMBER(38,0),
C_PHONE VARCHAR(15),
C_ACCTBAL NUMBER(12,2),
C_MKTSEGMENT VARCHAR(10),
C_COMMENT VARCHAR(117)
);
ddl for schema object:
create or replace TABLE LINEITEM (
L_ORDERKEY NUMBER(38,0),
L_PARTKEY NUMBER(38,0),
L_SUPPKEY NUMBER(38,0),
L_LINENUMBER NUMBER(38,0),
L_QUANTITY NUMBER(12,2),
L_EXTENDEDPRICE NUMBER(12,2),
L_DISCOUNT NUMBER(12,2),
L_TAX NUMBER(12,2),
L_RETURNFLAG VARCHAR(1),
L_LINESTATUS VARCHAR(1),
L_SHIPDATE DATE,
L_COMMITDATE DATE,
L_RECEIPTDATE DATE,
L_SHIPINSTRUCT VARCHAR(25),
L_SHIPMODE VARCHAR(10),
L_COMMENT VARCHAR(44)
);
ddl for schema object:
create or replace TABLE CUSTOMER (
C_CUSTKEY NUMBER(38,0),
C_NAME VARCHAR(25),
C_ADDRESS VARCHAR(40),
C_NATIONKEY NUMBER(38,0),
C_PHONE VARCHAR(15),
C_ACCTBAL NUMBER(12,2),
C_MKTSEGMENT VARCHAR(10),
C_COMMENT VARCHAR(117)
);
ddl for schema object:
CREATE OR REPLACE PROCEDURE "SP_PI"()
RETURNS FLOAT
LANGUAGE JAVASCRIPT
EXECUTE AS OWNER
AS '
return 3.1415926;
';
[15]:
# example of extension function: auto_union
from snowflake.snowpark.types import IntegerType, StringType, StructField, StructType, FloatType, NullType
schema_1 = StructType([StructField("a", IntegerType()), StructField("b", StringType())])
schema_2 = StructType([StructField("a", FloatType()), StructField("c", StringType())])
schema_3 = StructType([StructField("a", IntegerType()), StructField("c", StringType())])
schema_4 = StructType([StructField("c", StringType()), StructField("d", StringType())])
df_1 = session.create_dataframe([[1, "snow"], [3, "flake"]], schema_1)
df_2 = session.create_dataframe([[2.0, "ice"], [4.0, "pick"]], schema_2)
df_3 = session.create_dataframe([[6, "test_1"], [7, "test_2"]], schema_3)
df_4 = session.create_dataframe([["testing_d", "testing_f"], ["testing_g", "testing_h"]], schema_4)
union_dfs = [df_1, df_2, df_3, df_4]
unioned_df = session.auto_union(union_dfs)
unioned_df.show()
----------------------------------------
|"A" |"B" |"C" |"D" |
----------------------------------------
|1.0 |snow |NULL |NULL |
|3.0 |flake |NULL |NULL |
|2.0 |NULL |ice |NULL |
|4.0 |NULL |pick |NULL |
|6.0 |NULL |test_1 |NULL |
|7.0 |NULL |test_2 |NULL |
|NULL |NULL |testing_d |testing_f |
|NULL |NULL |testing_g |testing_h |
----------------------------------------