Converting a Array into individual rows in Snowflake

Mohammed Ali 0
Viewed 52

I have a Kafka Topic, which receives an array with multiple object in it like shown below.

[{"Id":2318805,"Booster Station":"Comanche County #1","TimeStamp":"2021-09-30T23:53:43.019","Total Throughput":2167.52856445125},{"Id":2318805,"Booster Station":"Comanche County #2","TimeStamp":"2020-09-30T23:53:43.019","Total Throughput":217.52856445125},]

when i load this in snowflake, it becomes one huge row, with all objects, i would like to store each object as individual row in snowflake, how can i achieve this, i am open to tweak at kafka level as or Connector.

My Kafka is AWS MSK, and i am using snowflake connector plugin for loading data in snowflake

enter image description here

Type of field enter image description here

apache-kafka-connect snowflake-cloud-data-platform
1 Answers

You can use Snowflake's flatten table function to flatten the arrays to individual rows:

create or replace temp table T1 as
select(parse_json($$[{"Id":2318805,"Booster Station":"Comanche County #1","TimeStamp":"2021-09-30T23:53:43.019","Total Throughput":2167.52856445125},
       {"Id":2318805,"Booster Station":"Comanche County #2","TimeStamp":"2020-09-30T23:53:43.019","Total Throughput":217.52856445125}]
       $$)) as JSON
;

select VALUE from T1, table(flatten(JSON));

This is assuming that the Kafka messages are stored as variant type. If they are string, you can use the parse_json function to convert them to variant.

From there, you can convert the individual objects to columns if you want:

select   VALUE:"Booster Station"::string as BOOSTER_STATION
        ,VALUE:Id::int as ID
        ,VALUE:TimeStamp::timestamp as TIME_STAMP
        ,VALUE:"Total Throughput"::float as TOTAL_THROUGHPUT
from T1, table(flatten(JSON));
BOOSTER_STATION ID TIME_STAMP TOTAL_THROUGHPUT
Comanche County #1 2318805 2021-09-30 23:53:43.019000000 2167.528564451
Comanche County #2 2318805 2020-09-30 23:53:43.019000000 217.528564451
Greg Pavlik0
Related