How to Create external table in snowflake based on S3 partitioned data-CodePudding

I have an s3 URL as s3://Sales//region.

I have date buckets inside the region folder as:

refdate=2022-02-26, refdate=2022-02-25 and refdate=2022-02-24

How would I create an external table in snowflake with partition as ref date folder? what have I tried so far:

Create external table sales (..)
PARTITION BY refdate
with location = @sales_stage/region/
file_format = COMP_ap
aws_sns_topic='arn:aws:sns:us-west38:snowflake-dev-SNS'
auto_refresh = true ;

Thanks,

CodePudding user response：

create stage s2
  url='s3://Sales//region/'
  ...
  ;

create external table et1(
 date_part date as to_date(split_part(metadata$filename, '-', 3)
   || '/' || split_part(metadata$filename, '-', 4)
   || '/' || split_part(metadata$filename, '-', 5), 'YYYY/MM/DD'),
 timestamp bigint as (value:timestamp::bigint),
 partition by (date_part)
 location=@s2
 auto_refresh = true
 aws_sns_topic = 'arn:aws:sns:us-west38:snowflake-dev-SNS';

check the snowflake documentation here