0

I have a table with a column, can i create a partition based on an expression using that column

I read that IBM's Big SQL technology has this feature.

I also know we can partition in hive by a column but what about an expression?

In this case i am doing a cast..it could be any expression

CREATE TABLE INVENTORY_A (
  trans_id int, 
  product varchar(50), 
  trans_ts timestamp
) 
PARTITIONED BY ( 
   cast(trans_ts as date) AS date_part
)

I expect the records to be partitioned by the date value. So I expect that when a user writes a query like

select * from INVENTORY_A where trans_ts BETWEEN timestamp '2016-06-23 14:00:00.000' AND timestamp '2016-06-23 14:59:59.000'

the query will be smart enough to break the timestamp down by the date and do a filter only on the date

david
  • 88
  • 6

1 Answers1

0

You can use Dynamic partitioning and cast your variables in select query.

serge_k
  • 1,772
  • 2
  • 15
  • 21
  • I have gone through the post but it seems like in Hive the create table statement requires a partition by column to be specified with a datatype..if i specify the datatype as Date then in my query i will need to use the date field in the where clause. I want to use the timestamp field. Any thoughts? – david Apr 11 '19 at 08:35