How to Load JSON Data into Snowflake: Simple Steps
To load
JSON data into Snowflake, create a table with a VARIANT column and use the COPY INTO command to load JSON files from a stage. Snowflake automatically parses JSON into the VARIANT type for flexible querying.Syntax
Use the COPY INTO command to load JSON files into a table with a VARIANT column. The basic syntax is:
CREATE TABLE: Define a table with aVARIANTcolumn to store JSON.CREATE STAGE: Define a location where JSON files are stored (e.g., internal or external cloud storage).COPY INTO: Load JSON files from the stage into the table.
sql
CREATE TABLE my_json_table (data VARIANT); CREATE OR REPLACE STAGE my_stage URL='s3://mybucket/jsonfiles/' CREDENTIALS=(AWS_KEY_ID='your_key' AWS_SECRET_KEY='your_secret'); COPY INTO my_json_table FROM @my_stage FILE_FORMAT = (TYPE = 'JSON');
Example
This example shows how to create a table, stage, and load JSON data from an internal stage. It demonstrates loading JSON objects into a VARIANT column for flexible querying.
sql
CREATE OR REPLACE TABLE json_data (info VARIANT); CREATE OR REPLACE STAGE my_internal_stage; -- Upload your JSON file to the stage using Snowflake UI or CLI COPY INTO json_data FROM @my_internal_stage FILE_FORMAT = (TYPE = 'JSON'); -- Query the loaded JSON data SELECT info:name::string AS name, info:age::int AS age FROM json_data;
Output
NAME | AGE
--------|-----
Alice | 30
Bob | 25
Common Pitfalls
- Not defining the table column as
VARIANTcauses errors because JSON needs flexible storage. - Forgetting to specify
FILE_FORMAT = (TYPE = 'JSON')inCOPY INTOleads to incorrect parsing. - Uploading files to the stage incorrectly or using wrong stage path causes load failures.
- Trying to load JSON arrays directly without flattening can cause unexpected results.
sql
/* Wrong: Table column is VARCHAR instead of VARIANT */ CREATE TABLE wrong_table (data VARCHAR); COPY INTO wrong_table FROM @my_stage FILE_FORMAT = (TYPE = 'JSON'); /* Right: Use VARIANT column */ CREATE TABLE correct_table (data VARIANT); COPY INTO correct_table FROM @my_stage FILE_FORMAT = (TYPE = 'JSON');
Quick Reference
| Step | Command | Purpose |
|---|---|---|
| 1 | CREATE TABLE my_table (col VARIANT); | Create table with VARIANT column for JSON |
| 2 | CREATE STAGE my_stage URL='...' CREDENTIALS=...; | Define storage location for JSON files |
| 3 | COPY INTO my_table FROM @my_stage FILE_FORMAT=(TYPE='JSON'); | Load JSON files into table |
| 4 | SELECT col:key::type FROM my_table; | Query JSON data using path expressions |
Key Takeaways
Always use a VARIANT column to store JSON data in Snowflake.
Use COPY INTO with FILE_FORMAT set to JSON to load JSON files correctly.
Create and use a stage to reference your JSON file location.
Query JSON data using path expressions with ::type casting.
Check file paths and permissions to avoid load errors.
