Column recommendation to partition huge hive table

[#1]

Partitioned the table of size 39 TB on the date column. However most of the queries use id column to join with this huge table. I was wondering if adding bucketing would help on id column. Please recommend.

[#2]

Use Bucketing using ID and use sort , also store as ORC file format and compress and set hive.exec.orc.split.strategy=BI;

Example :

CREATE TABLE Employee(

ID BIGINT,

NAME STRING,

AGE INT,

SALARY BIGINT,

DEPARTMENT STRING

)

COMMENT 'This is Employee table clustered by id sorted by age into 5 buckets'

CLUSTERED BY(ID) SORTED BY(AGE)INTO 5 BUCKETS

STORED AS ORC

tblproperties (“orc.compress” = “SNAPPY”);

Let me know if you face any issue