Bird
Raised Fist0
SnowflakeHow-ToBeginner · 4 min read

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 a VARIANT column 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 VARIANT causes errors because JSON needs flexible storage.
  • Forgetting to specify FILE_FORMAT = (TYPE = 'JSON') in COPY INTO leads 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

StepCommandPurpose
1CREATE TABLE my_table (col VARIANT);Create table with VARIANT column for JSON
2CREATE STAGE my_stage URL='...' CREDENTIALS=...;Define storage location for JSON files
3COPY INTO my_table FROM @my_stage FILE_FORMAT=(TYPE='JSON');Load JSON files into table
4SELECT 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.