Hive external table with projection partition

Trino
,
1

Can you share any example of creating an external table in trino with projection partition enabled.
When we tried running the following query it fails with

SQL Error [18]: Query failed (#): Catalog ‘hive’ table property ‘projection_partkey_format’ does not exist

CREATE TABLE hive.myschema.mytable(
c1 varchar(10)
,c2 int
,partkey int
)
WITH (
format = ‘parquet’,
external_location = ‘s3://my-bkt/my-table/’,
partitioned_by = ARRAY[‘partkey’],
projection_partkey_format=‘yyyyMMddHH’,
projection_partkey_interval=‘1’ ,
projection_partkey_interval_unit=‘HOURS’ ,
projection_partkey_range=‘NOW-13MONTHS,NOW+1DAY’,
projection_partkey_type=‘date’,
projection_enabled=‘true’
)

2

Here’s an example of a working query:

CREATE TABLE hive.data_infra.dataset_inventory (
   bucket varchar,
   key varchar,
   version_id varchar,
   is_latest boolean,
   is_delete_marker boolean,
   size bigint,
   last_modified_date bigint,
   e_tag varchar,
   storage_class varchar,
   is_multipart_uploaded boolean,
   replication_status varchar,
   encryption_status varchar,
   object_lock_retain_until_date bigint,
   object_lock_mode varchar,
   object_lock_legal_hold_status varchar,
   intelligent_tiering_access_tier varchar,
   bucket_key_status varchar,
   checksum_algorithm varchar,
   dt varchar WITH ( partition_projection_format = 'yyyy-MM-dd-HH-mm', partition_projection_interval = 1, partition_projection_interval_unit = 'HOURS', partition_projection_range = ARRAY['2022-05-11-00-00','NOW'], partition_projection_type = 'DATE' )
)
WITH (
   external_location = 's3://tusimple-dataset-inventory-reports/dataset/tusimple-dataset/dataset-inventory-report/hive',
   partition_projection_enabled = true,
   partitioned_by = ARRAY['dt']
)

Make sure to configure hive.partition-projection-enabled=true for it to work.

3

It looks like you are using invalid table property names. Refer to the table properties list from the Hive connector documentation for the correct names. Also make sure to use the same version of Trino as the set of documentation you are referring to.