How to create JSON/Nested JSON files in AWS S3 from Snowflake tables/views ?
JSON format is one of the widely data format with diverse uses in electronic data exchange, including that of web applications with servers. Most of the web applications use JSON to exchange the application information.
In this article we will cover how to create JSON files in AWS S3 from Snowflake tables/views.
JSON file content format:
{"Key" : "value"}Overview of snowflake function which is used in this article to work with JSON format:
OBJECT_CONSTRUCT : The function is useful to export Snowflake plain table to json. The OBJECT_CONSTRUCT function either accepts a sequence of zero or more key-value-pairs. <CLICK HERE for snowflake documentation>
Syntax:
OBJECT_CONSTRUCT( [<key1>, <value1> [, <keyN>, <valueN> ...]] )
OBJECT_CONSTRUCT( * )Example:
SELECT OBJECT_CONSTRUCT(‘NAME’, ’XXXX’, ’DEPT’,’ IT’, ’SCORE’, ’90');Output:
ARRAY_AGG: This function will accept input values and pivot them into an array, allowing a group of values to be returned for each row. Rather than performing an aggregate function against the values, such as SUM or AVG, they are pivoted into a list. <Click Here> for snowflake documentation.
Syntax:
ARRAY_AGG( [ DISTINCT ] <expr1> ) [ WITHIN GROUP ( <orderby_clause> ) ]Example:
WITH SAMPLE_ARRY_AGG AS
(
SELECT 'XXXX' AS NAME,'IT' AS DEPT ,'90' AS SCORE
UNION ALL
SELECT 'YYYY' AS NAME,'IT' AS DEPT,'190' AS SCORE
UNION ALL
SELECT 'ZZZZ' AS NAME,'CSE' AS DEPT,'90' AS SCORE
)
SELECT
'DEPARTMENT', SAMPLE_ARRY_AGG.DEPT,
ARRAY_AGG(OBJECT_CONSTRUCT(*))
FROM SAMPLE_ARRY_AGG
GROUP BY SAMPLE_ARRY_AGG.DEPTOutput:
How to extract JSON from Snowflake tables and views ?
Step 1: Script for creation of new Database, Schema and Table
CREATE OR REPLACE DATABASE DEMO;USE DATABASE DEMO;CREATE SCHEMA SNOW_EXTRACT;USE SCHEMA SNOW_EXTRACT;CREATE or REPLACE TABLE CLASS_STUDENT(
STUDENT_ID INT,
NAME VARCHAR(300),
DEPT VARCHAR(300),
GRADE VARCHAR(300),
ENTRY_DATE DATE);INSERT INTO CLASS_STUDENT
VALUES
(11401,'AAMEENA','IT','S',SYSDATE()),
(11402,'ALAGAPPAN','IT','S',SYSDATE()),
(11403,'ARUN','IT','B',SYSDATE()),
(11404,'ASHWIN','IT','A',SYSDATE()),
(11405,'ASHITA','IT','S',SYSDATE()),
(11406,'ALEX','IT','S',SYSDATE()),
(11407,'AROKIA MARRY PRICSILLA','IT','A',SYSDATE()),
(11108,'DOSS','CSE','S',SYSDATE());
Output:
Step 2: How to Constructs an object by defining keys and its corresponding values ?
SELECT
OBJECT_CONSTRUCT
('UNIQUE ID',STUDENT_ID,
'NAME OF THE STUDENT',NAME,
'DEPARTMENT',DEPT,
'JOINING DATE',ENTRY_DATE,
'SECURED GRADE',GRADE)
FROM
CLASS_STUDENT;
Step 3: By creating an External stage in snowflake which will help to load the data in AWS S3
CREATE OR REPLACE STAGE DEMO.SNOW_EXTRACT.SNOW_DEMO
URL = "s3://demoobjectconstruct"
CREDENTIALS=(aws_key_id='<Enter AWS Key ID>' aws_secret_key='<Enter AWS Secret Key>'— UNLOAD THE DATA IN JSON FORMATCREATE OR REPLACE FILE FORMAT JSON_FORMAT
TYPE = ‘JSON’;
Step 4 : Details of AWS S3 bucket with Access Key Creation
Create Access key details by navigating in to IAM menu in AWS:
Step 5: JSON file Generation
COPY INTO
@DEMO.SNOW_EXTRACT.SNOW_DEMO / UNLOADJSONFILE_DEMO
FROM
(
SELECT OBJECT_CONSTRUCT
(
'UNIQUE ID', STUDENT_ID,
'NAME OF THE STUDENT', NAME,
'DEPARTMENT', DEPT,
'JOINING DATE', ENTRY_DATE,
'SECURED GRADE', GRADE)
FROM CLASS_STUDENT)
FILE_FORMAT = JSON_FORMAT;
Output:
Step 6: How to create Nested JSON ?
COPY INTO @DEMO.SNOW_EXTRACT.SNOW_DEMO / NEXTEDJSONFILE_DEMO
FROM
(
WITH STUDENT_JSON AS(
SELECT
STUDENT_ID,NAME,DEPT,ENTRY_DATE,GRADE
FROM
CLASS_STUDENT
ORDER BY
STUDENT_ID,NAME,DEPT,ENTRY_DATE,GRADE)
SELECT
OBJECT_CONSTRUCT(
'DEPARTMENT',STUDENT_JSON.DEPT,
'COLUMNS',ARRAY_AGG(OBJECT_CONSTRUCT(*)))
FROM STUDENT_JSON
GROUP BY STUDENT_JSON.DEPT
ORDER BY STUDENT_JSON.DEPT)
FILE_FORMAT = JSON_FORMAT;
Output:
References:-
