GnpYkGH0

1#import snowflake connector module
2import snowflake.connector
3import sys
4import os
5from cryptography.hazmat.backends import default_backend
6from cryptography.hazmat.primitives.asymmetric import rsa
7from cryptography.hazmat.primitives.asymmetric import dsa
8from cryptography.hazmat.primitives import serialization 
9
10
11
12with open("/Users/abehsu/Documents/Snowflake/snowflake_snowpipe_sample/rsa_key.p8", "rb") as key:
13    p_key = serialization.load_pem_private_key(
14        key.read(),
15        password = os.environ['PRIVATE_KEY_PASSPHRASE'].encode(),
16        backend=default_backend()
17    )
18
19pkb = p_key.private_bytes(
20    encoding=serialization.Encoding.DER,
21    format=serialization.PrivateFormat.PKCS8,
22    encryption_algorithm=serialization.NoEncryption()
23)
24
25
26con = snowflake.connector.connect(
27    account="fm34255.us-east-1",    
28    user="connect_demo",
29    role="KEYPAIR",
30    private_key=pkb
31)
32
33con.cursor().execute("""
34    USE DATABASE SNOWPIPE_DEMO;
35""")
36
37
38# Create Fileformat Object
39print("Starting create Fileformat Object;")
40
41results = con.cursor().execute("""
42    CREATE OR REPLACE FILE FORMAT csv_format TYPE=CSV 
43    FIELD_DELIMITER=',' FIELD_OPTIONALLY_ENCLOSED_BY = '\042'
44    RECORD_DELIMITER = '\n' SKIP_HEADER=1 NULL_IF=('NULL','null');
45""")
46
47print("---------------------------------")
48
49for result in results:
50    print(result[0])
51
52print("Successfully create Fileformat Object;")
53
54
55
56# Create target table object
57print("Starting create target table object;")
58
59results = con.cursor().execute("""
60    CREATE OR REPLACE TABLE LIBRARY(
61        BibNum number,
62        Title text,
63        Author text default null,
64        ISBN text default null,
65        PublicationYear text,
66        Publisher text,
67        Subjects text default null,
68        ItemType text default null,
69        ItemCollection text,
70        FloatingItem text default null,
71        ItemLocation text default null,
72        ReportDate timestamp,
73        ItemCount number
74    );
75""")
76
77print("---------------------------------")
78
79for result in results:
80    print(result[0])
81
82print("Successfully create target table object;")
83
84
85
86# Create pipe object
87print("Starting create pipe object;")
88
89results = con.cursor().execute("""
90    CREATE PIPE IF NOT EXISTS SNOWPIPE_DEMO.PUBLIC.MYPIPE
91    AS COPY INTO LIBRARY FROM @SNOWPIPE_STAGE
92    FILE_FORMAT = (FORMAT_NAME = 'csv_format')
93""")
94
95print("---------------------------------")
96
97for result in results:
98    print(result[0])
99
100print("Successfully create pipe object;")