DATA LOADING IN SNOWFLAKE
Internal stages:
a. User stage
Whenever any user gets created in snowflake, it internally creates by default a user stage
Let’s see in practical by loading a file into user stage
Check the file ‘subjects’ what data is present (you can take your own file)
Now run the below in your snowsql for putting file into user stage (note: it varies from stage to
stage)
Put file ://< filepath> @~/<user> auto_compress=false;
Checking list of user stages
In case of user stage, you have to explicitly give column names to check instead of * as shown
Now moving data from user stage to any table
Firstly creating a table
Copying the data from user stage & can give file format option here, if file type is not
mentioned then default is csv
Let’s check in snowflake
l
Let’s clean up
b. Table stage
Table stage is the stage created default by snowflake when a table is created
Let’s check in practical
Let’s check the data in the file salary (select your file)
Let’s create a table salary
Note:
Via snowsql, table stage should have same name as table, cannot be altered or dropped, not a
DB object
Now put the file into table stage
Verifying table
List the files in table stages (this implies pointing to a location)
Direct select * works in table stages
Use copy command to load the data in to table_stage
Can specify file format option here. If file type is not given, default is csv
Validate
Let’s check in snowflake
Cleaning up
Dropping table will remove the data from stage table_stage
OR To reload, we can remove the files by using syntax
Remove @%salary pattern='.*.csv’;
Check the stage data now
Named stages
Internal named stage is a database object and as a user we can create it
Let’s see hands on
Let’s check the file ‘weather’
As for user stage and table stage we never created manually as they by default
But here, we had to create manually
Describe internal stage
Alter internal stage (good use case to load files via multiple users to multiple tables)
Let’s put the file into named stage ‘shared_stage2’
Select columns from internal named stage
Select * doesn’t work here, it only works for table stage
Now, let’s create a table to load internal named stage data
Copy the data from internal named stage to table
Cleaning up
Used cases of internal stages:
External stages
External stages deal with the files which are located externally at the cloud location where as
internal stages deals with the local files in the system.
Let’s see hands on
Creating an external stage by giving name as ‘contacts_stage’ and URL is referring to S3 bucket
List of files in different formats in our stage
As external stage is a database object, we can rename
Creating a table
Copying the data from external stage to table
Note: we are copying data of contacts5 csv data which has delimiter |, type csv
We got our data
To conclude, we brought the data into table from external source location (here we had taken
s3 bucket folder which we had files in it)
Cleaning up