1
0
During the last post we discussed about the scenario to ingest multiple CSV file into Snowflake. As we knew the table structure was not available beforehand and created the table structure at run time and ingest the csv files accordingly. In continuation of the same we will see if we receive the multiple JSON files in a directory. And how to process these files in snowflake when the table structure is not available in advance.
Consider the scenario if we are receiving ‘N’ number of JSON file with different format in a particular directory and we need to consume these JSON in snowflake. Processing the semi structured file format is quite different as compared to the CSV files. As we know snowflake provides VARIANT column in a table to consume the JSON/XML file. So our first task would be create the table with VARIANT column programmatically. Once table is done will copy the JSON content in this table.
In order to automate this process we have used the Python, which will read the JSON file from the directory via json.load(). Later on we will connect with Snowflake and create the table with VARIANT column. Finally by using json.dumps() will be inserting the data into Snowflake table. This automated process will help us to read the JSON file and create and load the data into snowflake without manual intervention.
Please find the below code:
Tables got created successfully in SNOWFLAKE:
