Loading Data Using the Web Interface¶
You can use Snowsight or Classic Console to load files with structured data (e.g. CSV, TSV) or semi-structured data (e.g. JSON, Avro, ORC, Parquet, or XML).
Use the web interface to load data from files up to 50MB in size. To load larger files, or large numbers of files, use the Snowflake client, SnowSQL. Refer to Bulk Loading from a Local File System.
Loading Data Using Snowsight¶
You can use Snowsight to load files for the following scenarios:
Loading a file or multiple files from your local machine into an existing table.
Loading a file from a stage into an existing table.
Creating a new table when loading a local or staged file. Snowsight uses the INFER_SCHEMA table function to automatically detect the file metadata schema, retrieve the column definitions, and generate a new table. Note that this feature is in preview and doesn’t support XML files.
You can load data from individual files up to 50 MB in size.
Note
To use Snowsight to load data, you must use a role that has the OWNERSHIP privilege on the table that you want to load data into and the USAGE privilege on the database and schema that contain the table. See Table Privileges.
If you want to use a named file format, your role must also have the USAGE privilege on that specific file format.
Load files from your local machine into an existing table¶
Sign in to Snowsight.
In the navigation menu, select Data » Databases.
Select a specific database and schema.
In the object explorer, select the table that you want to load data into.
Select Load Data.
In the Load Data into Table dialog, select Browse.
Add structured or semi-structured data files.
If you do not have a default warehouse set for your user, select a warehouse.
Select Next.
Do one of the following:
Select a file format from the current database.
Select a file type to customize, and then select the relevant settings for your data file.
(Optional) Select what should happen if an error occurs during loading. By default, no data is loaded from the file.
Select Load.
Snowsight loads your file and displays the number of rows successfully inserted into the table.
Do one of the following:
To open a worksheet with SQL syntax for querying your table, select Query Data.
To close the dialog, select Done.
If your file cannot be loaded, for example, if the columns in the file do not match the columns specified in the table, an error message is displayed. To adjust your settings, select Back.
Load a file from a stage into an existing table¶
Before loading files from a stage into a table, you need to create a stage and upload files onto the stage. For instructions, see Staging Files Using Snowsight.
Sign in to Snowsight.
In the object explorer, select the stage that you want to load files from.
In the list of the files on the stage, select the file that you want to load into the table.
In the ellipsis dropdown menu of the selected file, select Load into table.
In the Load Data into Table dialog, select a database, schema, and a table where you want to load the file.
Select Next.
Do one of the following:
Select a file format from the current database.
Select a file type to customize, and then select the relevant settings for your data file.
(Optional) Select what should happen if an error occurs during loading. By default, no data is loaded from the file.
Select Load.
Snowsight loads your file from the stage to the table.
Alternatively, you can load a staged file into a table by copying the path of the staged file.
Follow steps 1-3 in the previous procedure.
In the ellipsis dropdown menu of the selected file on the stage, select Copy path.
In the object explorer, select the table that you want to load data into.
Select Load Data.
In the Load Data into Table dialog that appears, select Add from Stage.
Paste the path that you copied into the path field, and then select Add.
The staged file name appears.
Select Next.
Do one of the following:
Select a file format from the current database.
Select a file type to customize, and then select the relevant settings for your data file.
(Optional) Select what should happen if an error occurs during loading. By default, no data is loaded from the file.
Select Load.
Snowsight loads your file and displays the number of rows successfully inserted into the table.
Create a table when loading a file¶
Preview Feature — Open
Available to all accounts.
You can create a new table when loading a file by taking the following steps. Alternatively, you can open the Load Data into Table dialog from a stage by following the steps 1-4 in the previous section Load a file from a stage into an existing table and then take the following steps 6-10 to complete the same tasks.
Note
This feature supports delimited files, JSON, Parquet, Avro, and ORC files. It doesn’t support XML files.
To perform the tasks below, you must have the privileges for creating table.
Sign in to Snowsight.
In the navigation menu, select Data » Databases.
Select a specific database and schema.
Select Create.
In the drop-down menu, select Table » From File.
In the Load Data into Table dialog, select Browse to add a file, or Drag and drop a file to the dialog, or Add a file from stage. Optionally, choose the location of your file.
Select a database and a schema where you want the table to be created.
Enter a name for the new table and then select Next.
Snowsight detects the metadata schema for the file and returns the file format and column definitions identified by the INFER_SCHEMA function.
Review the inferred file format, data type, column name, and a sample of column data. Ensure all information is accurate and make updates if needed.
Select Load.
Snowsight loads the file and creates a new table for the file.
Loading Data Using Classic Console¶
The Classic Console provides a wizard for loading limited amounts of data into a table from a small set of files. The wizard performs the same PUT and COPY operations that you would using SQL, but combines the two phases (staging files and loading data) into a single operation and deleting all staged files after the load completes.
You can load data from files on your local machine or files already staged in an existing cloud storage location on Snowflake, Amazon S3, Google Cloud Storage, or Microsoft Azure.
Prerequisites for Loading Data¶
Before you start loading data using Classic Console, do the following:
Identify the location your data files. You can load data from one of the following:
Your local machine.
An internal or external stage.
A cloud storage location on Amazon S3, Google Cloud Storage, or Microsoft Azure that is not yet added to Snowflake as an external stage.
Determine the size of your data files. Each file that you load can be up to 50MB. If you want to load larger files, or large numbers of files, use the Snowflake client, SnowSQL, instead. Refer to Bulk Loading from a Local File System.
The role that you use to load data must have appropriate privileges:
To load data, your role must have the USAGE privilege on the database and the schema that contain the table that you load data into.
To create a stage when you load data, your role must have the CREATE STAGE privilege on the database schema.
To create a file format when you load data, your role must have the CREATE FILE FORMAT privilege on the database schema.
Start Loading Data¶
To start loading a file into a specific table, do the following:
Select Databases
.Select a specific database and schema.
Select the Tables tab.
Locate the table into which you want to load data.
Start loading data into a specific table by doing one of the following:
Select a table row, then select Load Data.
Select a table name to open the table details page, then select Load Table.
The Load Data wizard opens.
Select a warehouse to use to load data into the table. The drop-down includes any warehouse on which you have the USAGE privilege.
Select Next.
Select the Data to Load¶
Depending on where you choose to load data from, follow the relevant steps. If you want to load data from multiple locations, use the Load Data wizard multiple times.
To load data from your computer:
Select the Load files from your computer option, and select Select Files to browse to the files that you want to load.
Select one or more local data files and select Open.
Select Next.
To load data from an existing stage:
Select the Load files from external stage option.
Select an existing stage from the Stage dropdown list.
(Optional) Specify a path to the files in the stage.
Select Next.
To create a stage, for example to load data from external cloud storage:
Select the Load files from external stage option.
Select the + next to the Stage dropdown list.
Select the supported cloud storage service where your files are located.
Select Next.
Complete the fields to describe your stage. For more information, refer to CREATE STAGE.
Select Finish.
Your new stage is automatically selected from the Stage dropdown list.
(Optional) Specify a path to the files in the stage.
Select Next.
Finish Loading Data¶
After you select the files to load, finish loading data into your table.
Note
If your warehouse is not running when you finish loading data, you must wait for the warehouse to resume (up to 5 minutes) before data is loaded.
To finish loading data, do the following:
Select an existing named file format from the dropdown list, or create one.
To create a file format:
Select the + next to the dropdown list.
Fill in the fields to match the format of your data files. For descriptions of the options, refer to CREATE FILE FORMAT.
Select Finish.
Your new named file format is automatically selected from the dropdown list.
Determine how you want to handle errors that occur when the data is loaded:
If you want data loading to stop if an error occurs, select Load.
If you want errors to be handled in a different way:
Select Next.
Select the option that describes how you want to handle errors. For details about the options, refer to the
ON_ERRORsection of COPY INTO <table>.Select Load.
Snowflake loads the data into your selected table using the warehouse you selected.
Select OK to close the Load Data wizard.