CODEWITHSUNDEEP
snowflake-cloud-data-platform
Kar
Kar

Reputation: 1016

Snowflake:Copy from S3 into Table with nested JSON

Requirement: To load the Nested JSON file into the Snowflake from S3

Error: SQL compilation error: COPY statement only supports simple SELECT from stage statements for import.

I know I can create a temporary table from the SQL, Is there a better way to load directly from the S3 into Snowflake

COPY INTO schema.table_A    FROM (
        WITH s3 AS (
                SELECT $1 AS json_array
                FROM '@public.stage' 
                    (file_format => 'public.json',
                         pattern => 'abc/xyz/.*')
            )
          SELECT DISTINCT
              CURRENT_TIMESTAMP()         AS exec_t,
              json_array                  AS data,
              json_array:id               AS id,
              json_array:code::text       AS code
          FROM s3,TABLE(Flatten(s3.json_array)) f
);

Upvotes: 1

Views: 715

Answers (1)

Marcel
Marcel

Reputation: 2622

Basically Transformations during loading come along with certain limitations, see here: https://docs.snowflake.com/en/user-guide/data-load-transform.html#transforming-data-during-a-load

If you still want to keep your code and not apply the transformations later, you may create a view on top of the stage and then basically you INSERT into another table based on SELECT * from that view.

Maybe avoiding the CTE is already helping.

Upvotes: 0

Related Questions