Create a Table in SnowFlake dynamically (Using JSON data from the staging area)

1
votes

Is there a way to create a table( with columns) dynamically by using the JSON file from the staging area?

I used the comman: 'copy into TableName from @StageName;'

This put all the different rows in my json file into a single column.

enter image description here

However, I want different columns. For example column1 should be "IP", column 2 should be "OS" and so on.

Thank you in advance!!

1
snowflake-cloud-data-platformsnowflake-schema

1 Answers

0
votes

I have implemented the same thing in my project.

So its a 2 step process.

1st Step - Create a stage table with variant data type table and copy into table from stage - which I can see you have already done that.

2nd Step - Either create a table or a view(since snowflake is superfast, View is the way to go for this dynamic extract of JSON data) which will read the data directly from this variant column, something like this

create or replace view schema.vw_tablename copy grants as  
SELECT
v:Duration::int Duration,
v:Connectivity::string Connectivity
...
from public.tablename

if your JSON has an array of structure, use below

create or replace view schema.vw_tablename copy grants as  
SELECT
v:Duration::int Duration,
v:Connectivity::string Connectivity,
f.value:Time::int as Event_Time,
from public.tablename,
table(flatten(v:arrayname)) f