Step 1: Create a stage
In Snowflake, a stage is a point where data is loaded from a file. There are three different kinds of stages:
User stages: These are specific to each user and can be used to load data from a local file system or from a cloud storage provider such as Amazon S3, Microsoft Azure, or Google Cloud Storage.
Account stages: These can be used to load data from a cloud storage provider and are shared by all users in an account.
Internal stages: These are run by Snowflake and can be used to load data from remote systems, like a public HTTP(S) endpoint or an Amazon S3 bucket in a different account.
Here is an illustration of how to upload a CSV file to Snowflake:
— build a stage
CREATE STAGE my_stage
URL = 's3://my-bucket/my-folder/'
CREDENTIALS = (AWS_KEY_ID=''
AWS_SECRET_KEY='');
Step 2: Provide the right permissions
You must give the person or role who will be importing the data into Snowflake the right permissions. This includes the stage's USAGE authorization as well as the FILE FORMAT object's description of the file's format. You can grant permissions to a user or role in Snowflake using the GRANT command.
— give the user permissions
GRANT USAGE ON STAGE my_stage TO my_user;
GRANT USAGE ON FILE FORMAT my_file_format TO my_user;
Step 3: Make a file format
A file format specifies the formatting of the data in the file that is being loaded. The CREATE FILE FORMAT command in Snowflake can be used to create a file format.
— develop a file format
CREATE FILE FORMAT my_file_format
TYPE = 'CSV'
FIELD_DELIMITER = ','
RECORD_DELIMITER = '\n'
SKIP_HEADER = 1;
Step 4: Copy file to a table
The COPY INTO command can be used to load the data from the flat file into Snowflake once you have built a stage, given the necessary permissions, and created a file format. The file location, destination table, and file format are all specified by the COPY INTO command.
— load the data
COPY INTO my_table
FROM @my_stage/my_file.csv
FILE_FORMAT = my_file_format
ON_ERROR = 'ABORT_STATEMENT';
This command loads the data from the file my_file.csv in the my_stage stage into the table my_table, using the my_file_format file format. If any errors occur during the load operation, the command will abort the entire statement.