Dynamically generate column list from incoming source file

Matillion ETL
21

I had not seen that article before but it's a very good one! Thanks for sharing. It's definitely a good approach to take. You can facilitate that process using Matillion.

 

To get you started in understanding how to expose JSON data natively you can use the sample file you provided and load that JSON into a table with a single column with a data type of variant. Then create this view that references the table and column you loaded the data into:

 

create or replace view first_level as

(

 select objects.value:"id"::number as ID, 

   objects.value:"first_name"::string as FIRST_NAME,

   objects.value:"last_name"::string as LAST_NAME,

   objects.value:"city"::string as CITY, 

   objects.value:"zip"::string as ZIP, 

   objects.value:"specialty"::string as SPECIALTY, 

   objects.value:"Network1"::string as NETWORK1,

   objects.value:"Network2"::string as NETWORK2,

   objects.value:"Network3"::string as NETWORK3

 from (YOUR_TABLE_GOES_HERE) d,

   TABLE(FLATTEN(INPUT => d.$1)) objects

);

 

This would be your first step in materializing the JSON data. The article you found would basically be replacing the view to add any additional columns as new data comes in. If you don't use all of the columns from the view that is fine as well but at least they will be available for use. I hope this helps get you further.

22

To add to my previous post and tie in the article you mentioned. This query will list the JSON columns and data types for the data in the table:

 

SELECT DISTINCT

  KEY as COLUMN_NAME,

  typeof(f.value) as COLUMN_DATA_TYPE

FROM 

 (YOUR_TABLE_GOES_HERE),

 LATERAL FLATTEN((YOUR_VARIANT_COLUMN_GOES_HERE), RECURSIVE=>true) f

WHERE

 TYPEOF(f.value) != 'OBJECT'

ORDER BY LOWER(KEY) ASC;