How to convert bigint to datetime in hive?

Question

I had sqooped the data from teradata to Hive using sqoop import command.

For one of the tables in teradata, I have a date field . After sqooping, my date field is appearing as a timestamp with bigint datatype.

But I need the date field as a date datatype in hive table. Can anyone please suggest me in achieving this?

Solution 1

I've had this problem. My approach was to create the Hive table first. You should make an equivalence between Teradata datatypes and your Hive version datatypes. After that you can use the Sqoop argument --hive-table <table-name> to insert into that table.

Solution 2

select to_date(from_unixtime(your timestamp));

example:

select to_date(from_unixtime(1490985000));
output:2017-04-01

I hope it will work. please let me know if i am wrong.

Comments

[Andrew]

What does timestamp with bigint datatype look like? Add some samples to your post please.

[shaine]

i came across this last year the only way round I found was to cast the field as a varchar as part of the sqoop export.

[Stack 777]

My date field in hue is a 13-digit timestamp, which looks like : 1450051200000 which is of bigint type. But I'm expecting the date type instead of bigint in hue after sqooping.

[Stack 777]

I've tried to cast the bigint into string field using to_char function. But, instead of string , is there any way to convert directly from bigint to datetime type

[sumitya]

why don't you use Teradata function cast(column_name as date) function in sqoop query

[Stack 777]

Finally, resolved my issue by using the below sqoop import command, I'm able to convert the bigint value into timestamp. $ sqoop import ... --map-column-java id=String,value=Integer

[Stack 777]

The format which you have mentioned is working as expected. But, it is just a read right. what I'm expecting is , after sqoop import , the date field in hive should be of type datetime.

[Stack 777]

Can you please post the sqoop import command with equivalence between Teradata datatypes and my Hive version datatypes ?

[danielsepulvedab]

@Stack777 It would be just a CREATE TABLE statement in Hive, not an extra argument in sqoop. That way you can define your table schema just as you want it.