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 250 MB in size. To load larger files, or large numbers of files, use the Snowflake client, SnowSQL. For more information, see 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 or 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. This feature doesn’t support XML files.
You can load data from individual files up to 250 MB in size.
Access control privileges¶
Snowsight does not support using database roles to grant privileges on a database, schema, or table. Use an account role to interact with Snowsight. For more information, see types of roles.
Loading data using Snowsight¶
Loading data using Snowsight requires a role with a minimum of the following privileges:
Object |
Privilege |
Notes |
---|---|---|
Database |
USAGE |
|
Schema |
USAGE |
|
Stage |
USAGE |
Required for loading a file from a stage. |
File format |
USAGE |
Required for using a named file format. |
Table |
OWNERSHIP |
Creating a new table when loading data using Snowsight¶
Creating a new table when loading data using Snowsight requires a role with a minimum of the following privileges:
Object |
Privilege |
Notes |
---|---|---|
Database |
USAGE |
|
Schema |
CREATE TABLE |
|
Stage |
USAGE |
|
Table |
OWNERSHIP |
Load a file into an existing table¶
Sign in to Snowsight.
In the navigation menu, select Data » Add Data.
On the Add Data page, select Load data into a Table.
The Load Data into Table dialog appears.
In the Load Data into Table dialog, select or create a database and schema where you want the table to be created.
Select Browse to add a file from your local machine, Drag and drop to upload files, or Add from stage.
If you select Add from stage, the stage explorer will appear. In the stage explorer, you can navigate into stages and subfolders and select specific folders and files from the stage. If you select Add without selecting any specific files on the stage, the root stage, which includes all the files and folders on the stage, will be added.
The maximum number of files that can be shown in a stage folder is 250.
After adding files, select Next. The table schema dialog appears.
In the table schema dialog, do 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 one of the Match by column names options to automatically match the source file and the target table. The default option is case insensitive.
Note that schema detection and visual column mapping does not support XML.
Select the Edit Schema tab on the right side of the table schema dialog. You can see any discrepancy between the source file and the target table. Make adjustments as needed.
If you select the Table Preview tab, you can preview how the data of the incoming source file will look in the target table.
Select Load.
Snowsight loads your file and displays the number of rows successfully inserted into the table.
Create a table when loading a file¶
You can create a new table when loading a file by taking the following steps.
Note
This feature supports delimited files, JSON, Parquet, Avro, and ORC files. It doesn’t support XML files.
Sign in to Snowsight.
In the navigation menu, select Create » Table » From File.
The Load Data into Table dialog appears.
In the Load Data into Table dialog, select or create a database and schema where you want the table to be created.
Select Browse to add a file from your local machine, Drag and drop to upload files, or Add from stage.
If you select Add from stage, the stage explorer will appear. In the stage explorer, you can navigate into stages and subfolders and select specific folders and files from the stage. If you select Add without selecting any specific files on the stage, the root stage, which includes all the files and folders on the stage, will be added.
Note that the maximum number of files that can be shown in a stage folder is 250.
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 250 MB. 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 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_ERROR
section 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.