CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
Faisal Shani
Faisal Shani

Reputation: 820

Load multiple JSON files from stage to table in Snowflake

I have a few hundred files in an internal named stage which I need to move to snowflake table. There is a pattern in the name of files which are in stage,

file1.json
file2.json
file3.json
...

I am trying to create a query that will load all files at once in the destination table, as we can use the PUT command with * in snowSQL. I am trying to run the following copy into query but its showing the result as

Copy executed with 0 files processed.

The query I am trying is

copy into test_table (file_name, load_date, data) from (
    select
        metadata$filename,
        current_timestamp,
        f.$1
    from @stagename/file*.json f
)
    file_format = (
        type = json
            strip_outer_array = true
        )
    force=true ;

Any suggestion on this? Thanks

Upvotes: 1

Views: 1033

Answers (1)

Sergiu
Sergiu

Reputation: 4608

You are not using the pattern correctly, it needs to be something like this:

select metadata$filename, current_timestamp, f.$1 from @stagename (file_format => 'myformat', pattern=>'file.*[.]json') f;

Upvotes: 1

Related Questions