date - 在 hive 表中创建具有日期数据类型的列

Question

我使用值在 HIVE(0.10.0) 中创建了表:

2012-01-11  17:51   Stockton    Children's Clothing     168.68  Cash
2012-01-11  17:51   Tampa       Health and Beauty       441.08  Amex
............

这里日期和时间是制表符分隔值，我需要处理日期列，由于 Hive 不允许“日期”数据类型，我使用“TIMESTAMP”作为第一个日期列(2012-01-11，...)，
但是在创建表后，它显示第一列的 NULL 值。

如何解决这个问题？请指导。

Answer 1

我将数据加载到一个表中，所有列都定义为 string然后转换日期值并加载到另一个表中，该列被定义为 DATE .它似乎工作没有任何问题。唯一的区别是我使用的是 Shark 版本的 Hive，老实说，我不确定与实际的 Hive 和 Shark Hive 是否有任何深刻的区别。

数据:

hduser2@ws-25:~$ more test.txt 
2010-01-05  17:51   Visakh
2013-02-16  09:31   Nair

代码:

[localhost:12345] shark>  create table test_time(dt string, tm string, nm string) row format delimited fields terminated by '\t' stored as textfile;
Time taken (including network latency): 0.089 seconds
[localhost:12345] shark> describe test_time;
dt  string  
tm  string  
nm  string  
Time taken (including network latency): 0.06 seconds
[localhost:12345] shark> load data local inpath '/home/hduser2/test.txt' overwrite into table test_time;                                                   
Time taken (including network latency): 0.124 seconds
[localhost:12345] shark> select * from test_time;
2010-01-05  17:51   Visakh
2013-02-16  09:31   Nair
Time taken (including network latency): 0.397 seconds
[localhost:12345] shark> select cast(dt as date) from test_time;
2010-01-05
2013-02-16
Time taken (including network latency): 0.399 seconds
[localhost:12345] shark> create table test_date as select cast(dt as date) from test_time;
Time taken (including network latency): 0.71 seconds
[localhost:12345] shark> select * from test_date;
2010-01-05
2013-02-16
Time taken (including network latency): 0.366 seconds
[localhost:12345] shark> 

如果您正在使用 TIMESTAMP ，然后您可以尝试将日期和时间字符串连接起来，然后进行转换。

create table test_1 as select cast(concat(dt,' ', tm,':00') as string) as ts from test_time;

select cast(ts as timestamp) from test_1;