# Hive external table with projection partition

**URL:** <https://www.starburst.io/community/forum/t/hive-external-table-with-projection-partition/249>\
**Category:** Trino\
**Tags:** partition, hive\
**Created:** [March 21, 2023, 5:35pm UTC](https://www.starburst.io/community/forum/t/hive-external-table-with-projection-partition/249 "2023-03-21T17:35:26Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![shashideore](https://www.starburst.io/community/forum/letter_avatar_proxy/v4/letter/s/9de0a6/32.png) [@shashideore](https://www.starburst.io/community/forum/u/shashideore)\
**Post date:** [March 21, 2023, 5:35pm UTC](https://www.starburst.io/community/forum/t/hive-external-table-with-projection-partition/249/1 "2023-03-21T17:35:27Z")

</div>

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’  
)

---

<div class="post-metadata">

**Author:** ![monimiller](https://www.starburst.io/community/forum/user_avatar/www.starburst.io/monimiller/32/140_2.png) [@monimiller](https://www.starburst.io/community/forum/u/monimiller)\
**Post date:** [July 17, 2023, 2:24pm UTC](https://www.starburst.io/community/forum/t/hive-external-table-with-projection-partition/249/2 "2023-07-17T14:24:05Z")

</div>

Here’s an example of a working query:

```auto
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.

---

<div class="post-metadata">

**Author:** ![simpligility](https://www.starburst.io/community/forum/user_avatar/www.starburst.io/simpligility/32/190_2.png) [@simpligility](https://www.starburst.io/community/forum/u/simpligility)\
**Post date:** [July 19, 2023, 3:48am UTC](https://www.starburst.io/community/forum/t/hive-external-table-with-projection-partition/249/3 "2023-07-19T03:48:17Z")

</div>

It looks like you are using invalid table property names. Refer to the [table properties list from the Hive connector documentation](https://trino.io/docs/current/connector/hive.html#table-properties) for the correct names. Also make sure to use the same version of Trino as the set of documentation you are referring to.