create_schema.py

#import snowflake connector module
import snowflake.connector
import sys
import os
from cryptography.hazmat.backends import default_backend
from cryptography.hazmat.primitives.asymmetric import rsa
from cryptography.hazmat.primitives.asymmetric import dsa
from cryptography.hazmat.primitives import serialization 

# 確定輸入的參數是否給定正確
# Sample: python create_warehouse.py <account> <user> <role>
if len(sys.argv) < 4 :
    print("ERROR: Please pass the following command-line parameters in order:",end='\n')
    print("account,user,role.") 
    sys.exit(-1)
else:
    ACCOUNT = sys.argv[1]
    USER = sys.argv[2]
    ROLE = sys.argv[3]

with open("/Users/abehsu/Documents/Snowflake/Snowpipe_poc/rsa_key.p8", "rb") as key:
    p_key = serialization.load_pem_private_key(
        key.read(),
        password = os.environ['PRIVATE_KEY_PASSPHRASE'].encode(),
        backend=default_backend()
    )

pkb = p_key.private_bytes(
    encoding=serialization.Encoding.DER,
    format=serialization.PrivateFormat.PKCS8,
    encryption_algorithm=serialization.NoEncryption()
)


con = snowflake.connector.connect(
    account=ACCOUNT,    
    user=USER,
    role=ROLE,
    private_key=pkb
)

results = con.cursor().execute("select current_warehouse(), current_database(), current_schema();")

for result in results:
    print("current_warehouse","current_database","current_schema")
    print(result[0],result[1],result[2])


#Create Warehouse, Schema, Database


# #Error msg:snowflake.connector.errors.ProgrammingError: 090105 (22000): Cannot perform CREATE SCHEMA. This session does not have a current database. Call 'USE DATABASE', or use a qualified name.
# con.cursor().execute("""
# CREATE TRANSIENT SCHEMA IF NOT EXISTS testschema_mg;
# """)

# #Error msg:snowflake.connector.errors.ProgrammingError: 000006 (0A000): Multiple SQL statements in a single API call are not supported; use one API call per statement instead.
# con.cursor().execute("""
# USE DATABASE testdb_mg;
# CREATE TRANSIENT SCHEMA IF NOT EXISTS testschema_mg;
# """)


con.cursor().execute("""
USE DATABASE testdb_mg;
""")


con.cursor().execute("""
CREATE TRANSIENT SCHEMA IF NOT EXISTS testschema_mg;
""")

results = con.cursor().execute("select current_warehouse(), current_database(), current_schema();")

for result in results:
    print("current_warehouse","current_database","current_schema")
    print(result[0],result[1],result[2])