Manage snowflake data loading with Python script
We can integrate the data to snowflake in multiple ways, In this post will focus on loading the data from snowflake internal / external stage using Python script.
The COPY command provides option to loading batches of data available in external cloud storage or an internal stage within Snowflake. Copy command uses the predefined user created warehouse to read the data from snowflake internal / external stages to load the data into snowflake tables.
These on-the-fly transformations may include:
· Column reordering
· Column omission
· Casts
· Text truncation
Get Sriganesh Palani’s stories in your inbox
Join Medium for free to get updates from this writer.
Flow of the script:
The python script will read all the details from the meta data table (SNOWFLAKE_LOAD_INPUT), it hold all the details to load the data from either snowflake internal / external stage.
create or replace TABLE SNOWFLAKE_LOAD_INPUT (
PROCESS_ID NUMBER(10,0) NOT NULL comment 'Unique ID for each table',
PROCESS_SEQ NUMBER(10,0) NOT NULL comment 'common format for all table YYYYMMDD',
INPUT_DATE DATE comment 'insertion date of the records',
TABLE_NAME VARCHAR(100) NOT NULL comment 'target table name',
FILE_NAME VARCHAR(16777216) NOT NULL comment 'file name of processing file',
PROCESS_FLAG VARCHAR(1) NOT NULL default 'Y' comment 'process flag: Y for yes and N for No',
FILE_FORMAT VARCHAR(2000) NOT NULL comment 'file format name to the source table',
STAGE_NAME VARCHAR(2000) NOT NULL comment 'Stage Name'
);
Once meta data table is inserted with the details, python script will read the details and load the data to respective tables. Once the data load is completed the respective status will be updated into audit table. This audit table will give the detailed information about the data load.
You can manage the multiple table data load same time by inserting the details into meta data table.
Once the process completed the script will trigger the alert mail to respective users.
If you have any specific use case, suggestion or feedback feel free to command or reach out me @https://www.linkedin.com/in/sriganeshpalani/
