Create a view in SnowFlake dynamically using JSON string

Question

I need to create dynamic views from JSON string data

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

This is a kind of manual view for one of the table but i want to code in generic way so that i will pass the table name which is having JSON data and view will be created and output will be tabular format.

I think you're in for a challenge here. Since JSON doesn't have data types and can be nested with all sorts of arrays and sub-attributes, you'd need a pretty robust script outside of Snowflake to determine the data types, the depth of your FLATTEN statements, etc. in order to truly create a dynamic view. If your JSON is just a list of attributes, that'd help, but you'd still need something to evaluate data types before creating your view. — Mike Walton
Also, JSON data might be dynamic as well, which means that any view should be regenerated every time the JSON data is updated, otherwise you risk missing out on new attributes. — Hans Henrik Eriksen

Simeon Pilgrim Simeon Pilgrim · Accepted Answer · 2019-10-22T22:24:47

If you are wanting to have the view created in snowflake driven by data (as compared to using a tool to create the views client side, which we do in our company) I think you only hope will be stored procedures. In the detailed usage doc's it reminds you DDL operations commits the current transaction (which is always good to remember) but also implies that you can do DDL, which is what you are asking. Which means you should be able to write some javascript that builds the create view command you are want based on data handed to it.

Create a view in SnowFlake dynamically using JSON string

2 Answers