Snowflake external table partition by "Defining expression for partition column year is invalid"

user_7147255 0
Viewed 88

I have a parquet asset in s3 and wish to make an external table from this asset The asset is partitioned by year, month, day and hour.

My DDL is below

CREATE OR REPLACE external TABLE abc (
"year" int as (value:"partition_0"::int),
"month" int as (value:"partition_1"::int),
"day" int as (value:"partition_2"::int),
"hour" int as (value:"partition_3"::int),
"partition_key" varchar as (METADATA$EXTERNAL_TABLE_PARTITION)

)
PARTITION BY ("year", "month", "day", "hour")
PARTITION_TYPE = USER_SPECIFIED
WITH location = @abc
auto_refresh = true
file_format = (type = parquet);

When I try to partition by the following I get the following error

PARTITION BY ("year", "month", "day", "hour")

>>>Error: Defining expression for partition column year is invalid.

When I try to partition by partition_key as below, I don't get an error, but the external table is now empty

PARTITION BY ("partition_key")

>>> empty table

Anyone know what's going on here and how I can rectify this?

snowflake-cloud-data-platform
1 Answers

Ok, so using the CITIBIKE data which has parquet files:

s3://snowflake-workshop-lab/citibike-trips-parquet/ 
us-east-1   
EXTERNAL    
AWS

I can recreate the error using the file names parts as year/month/day:

create or replace external table cb (
    trip_id int as (value:TRIPID::int),
    filename char as (metadata$FILENAME),
    year int as (split_part(metadata$FILENAME,'/', 2)::int),
    month int as (split_part(metadata$FILENAME,'/', 3)::int),
    day int as (split_part(metadata$FILENAME,'/', 4)::int)
    
) 
    partition by (year, month, day)
    partition_type = USER_SPECIFIED
    with location = @CITIBIKE_TRIPS_PARQUET 
    auto_refresh = true 
    file_format = (type=parquet);

Defining expression for partition column YEAR is invalid.

If I comment out:

    --partition by (year, month, day)
    --partition_type = USER_SPECIFIED

It creates and I can read rows:

select * from cb limit 2;
VALUE TRIP_ID FILENAME YEAR MONTH DAY
{ "BIKEID": "2013-268", ... 813124 citibike-trips-parquet/2013/06/10/data_01a19496-0601-8b21-003d-9b03003c624a_1106_0_0.snappy.parquet 2,013 6 10
{ "BIKEID": "2013-220", ... 813161 citibike-trips-parquet/2013/06/10/data_01a19496-0601-8b21-003d-9b03003c624a_1106_0_0.snappy.parquet 2,013 6 10

Reading the create-external-table docs for partitioning-parameters the AWS example pulls a paths apart, but turns it back into a single date field:

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),
 col2 varchar as (value:col2::varchar))
 partition by (date_part)

thus:

create or replace external table cb (
    trip_id int as (value:TRIPID::int),
    filename char as (metadata$FILENAME),
    date_part date as to_date(
        split_part(metadata$FILENAME,'/', 2) || '/' ||
        split_part(metadata$FILENAME,'/', 3) || '/' ||
        split_part(metadata$FILENAME,'/', 4), 'YYYY/MM/DD')
) 
    partition by (date_part)
    --partition_type = USER_SPECIFIED
    with location = @CITIBIKE_TRIPS_PARQUET 
    auto_refresh = true 
    file_format = (type=parquet);

and then add a filter to my query:


select * from cb where date_part > '2020-04-01' limit 2;

those 2 rows

and the query profile shows a limited set of partitions where scanned (as one would expect)

just 8 partitions scanned

if I add partition_type = USER_SPECIFIED back into the code I get the error:

Defining expression for partition column DATE_PART is invalid.

which makes me think the original example would have worked, if this was dropped also, which testings shows that it does:

create or replace external table cb (
    trip_id int as (value:TRIPID::int),
    filename char as (metadata$FILENAME),
    year int as (split_part(metadata$FILENAME,'/', 2)::int),
    month int as (split_part(metadata$FILENAME,'/', 3)::int),
    day int as (split_part(metadata$FILENAME,'/', 4)::int)
    
) 
    partition by (year, month, day)
    --partition_type = USER_SPECIFIED
    with location = @CITIBIKE_TRIPS_PARQUET 
    auto_refresh = true 
    file_format = (type=parquet);

select * from cb where year = 2018 limit 2;
TRIP_ID FILENAME YEAR MONTH DAY
145400 citibike-trips-parquet/2018/01/06/data_01a19496-0601-8b21-003d-9b03003c624a_2906_6_0.snappy.parquet 2,018 1 6
145545 citibike-trips-parquet/2018/01/06/data_01a19496-0601-8b21-003d-9b03003c624a_2906_6_0.snappy.parquet 2,018 1 6

So reading the docs again, after all that:

Defines the partition type for the external table as user-defined. The owner of the external table (i.e. the role that has the OWNERSHIP privilege on the external table) must add partitions to the external metadata manually by executing ALTER EXTERNAL TABLE … ADD PARTITION statements.

Do not set this parameter if partitions are added to the external table metadata automatically upon evaluation of expressions in the partition columns.

I suspect the first partition by line is the exactly what this note is referring to in the "don't use this if you did that"..

user_439920
Related