Question

Don't Want to Parse JOSN Nested Object while Loading to Snowflake Stage

  • 29 December 2022
  • 8 replies
  • 45 views
Lav Pandey
Rank

Hi,

 

I am new to using Streamsets, I am facing one challenge, 

Current Design

Below is the JSON Sample Input

{   

 "NAME": "TOM",   

 "ID": "101",   

 "DATE": "29-12-2022",   

 "ADDRESS": { "CITY": "NYC",  "REGION": "NYC",   "COUNTRY": "USA", "PIN": "1234" }

}

 

I want Output to be as :

NAME, ID, DATE, ADDRESS

TOM,101,29-12-2022,{ "CITY": "NYC","REGION": "NYC","COUNTRY": "USA","PIN": "1234"}

 

My Current design is giving me output as :

NAME, ID, DATE, CITY, REGION, COUNTRY, PIN

TOM,101,29-12-2022,NY,NYC,USA,1234

 

Note :  Current design is a handover code. Here, I don't want to parse the nested JSON, just want to represent it as it is.

8 replies

Bikram
Rank
Userlevel 5
Badge +1

@Lav Pandey 

May I know which format data do you want to send it to snowflake,

 

If you want to send column wise data then you can use field pivoted and field splitter  processor and proceed further.

 

If you can send me the nested sample data and how data should be into snowflake , then I can give a try to help you on it.

 

Bikram
Rank
Userlevel 5
Badge +1

@Lav Pandey 

can you please send me the sample input file from where you reading the file ,so I can give a try to help you on it.

Lav Pandey
Rank

Is it possible to try with tthe above sample File ?

Input :: 

{   

 "NAME": "TOM",   

 "ID": "101",   

 "DATE": "29-12-2022",   

 "ADDRESS": { "CITY": "NYC",  "REGION": "NYC",   "COUNTRY": "USA", "PIN": "1234" }

}

 

Desired Output ::

{ "NAME": "TOM",   "ID": "101",   "DATE": "29-12-2022",   "ADDRESS": { "CITY": "NYC",  "REGION": "NYC",   "COUNTRY": "USA", "PIN": "1234" }}

Current Output ::
{ "NAME": "TOM",   "ID": "101",   "DATE": "29-12-2022",  "CITY": "NYC",  "REGION": "NYC",   "COUNTRY": "USA", "PIN": "1234" }

Bikram
Rank
Userlevel 5
Badge +1

@Lav Pandey 

I am working on it and will update you on the same.

Bikram
Rank
Userlevel 5
Badge +1

@Lav Pandey 

I tried this and it works for me.

COPY INTO "DATA_BIKS"."PUBLIC"."JSON_TABLE" from ( SELECT * FROM @DATA_BIKS.PUBLIC.SourcesBiks/biks_temp/testjson/a.json ) FILE_FORMAT= ( TYPE = json TRIM_SPACE = TRUE ) ;

 

Data stored as it is.

 

 

 

 

saleempothiwala
Rank
Userlevel 4
Badge

@Lav Pandey 

As soon as you choose the data format as JSON, SDC will parse the data and read it as SDC Record, including the ADDRESS column that has json data string in it.

 

I tried creating a pipeline. Added JSON Generator to convert the ADDRESS nested field back to JSON. If you write address_json to Snowflake then it will write it as it is.

 

 

Here is the configuration for JSON Generator.

 

Lav Pandey
Rank

I got this issue fix. Thanks.

Lav Pandey
Rank

Many thanks to @Bikram . 

Reply


Terms & Conditions