CODEWITHSUNDEEP
snowflake-cloud-data-platform
Karthik
Karthik

Reputation: 471

Snowflake:Read all files with wildcard

Requirement is to read all files from the S3 bucket starting with same name

enter image description here

Tried below it didn't work out

with s3 as  (
  select  $1 as json_array
  from '@stage/airflow/reponse__2022-06-06_05*'
  (file_format => 'public.S3_JSON')
)
select f.value,f.value:CompanyId
from s3, Table(Flatten(s3.json_array)) f

Upvotes: 1

Views: 3008

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175706

Using pattern:

with s3 as  (
  select  $1 as json_array
  from @stage
  (file_format => 'public.S3_JSON',
   pattern => 'airflow/reponse__2022-06-06_05.*')
)
select f.value,f.value:CompanyId
from s3, Table(Flatten(s3.json_array)) f;

More: Querying Data in Staged Files

PATTERN => 'regex_pattern'

A regular expression pattern string, enclosed in single quotes, specifying the file names and/or paths on the external stage to match.

Upvotes: 2

Related Questions