Alternative to JSON Flattening via Target Table in Snowflake

0
votes

Per snowflake: https://docs.snowflake.net/manuals/user-guide/json-basics-tutorial-copy-into.html I created a target table (Testing_JSON), that is a single Variant column that contains an uploaded JSON file.

My Question is How can I cut out creating this "Target Table (i.e. Testing_JSON)" that is a single Variant Column that I have to reference to create the actual and only table I want (TABLE1), which contains the flattened JSON. I have found no way to read in a JSON file from my desktop and 'parse it on the fly' to create a flattened table, VIA THE UI. Not using the CLI as I know this can be done using PUT/COPY INTO 

create or replace temporary table TABLE1 AS
SELECT 
VALUE:col1::string AS COL_1,
VALUE:col2::string AS COL_2,
VALUE:col3::string AS COL_3

from TESTING_JSON 
  , lateral flatten( input => json:value);
2
jsonflattensnowflake-cloud-data-platformlateral

2 Answers

1
votes

You can't do this through the UI. If you want to do this then you need to use an external tool on your desktop or - as Mike mentioned - in the COPY statement.

1
votes

You're going to need to do this in a few steps from your desktop.

  1. use SnowSQL or some other tool to get your JSON file up to blob storage: https://docs.snowflake.net/manuals/sql-reference/sql/put.html
  2. use a COPY INTO statement to get the data loaded directly to the flattened table that you want to load to. This will require a SELECT statement in your COPY INTO: https://docs.snowflake.net/manuals/sql-reference/sql/copy-into-table.html

There is a good example of this here:

https://docs.snowflake.net/manuals/user-guide/querying-stage.html#example-3-querying-elements-in-a-json-file