如何创建列类型为Timestamp的外部Hive表 [英] How to create an external Hive table with column typed Timestamp

问题描述

我想从HDFS中包含纪元的文本文件创建一个外部Hive表。假设文件位于 /user/me/test.txt 处。以下是档案内容：

I want to create an external Hive table from a text file containing epoch in HDFS. Let's say the file is located at the /user/me/test.txt. Here's the file content:

1354183921
1354183922

我已经安装了Hive 0.8.1并且应该可以使用Timestamp类型，所以我创建了这个表：

I have Hive 0.8.1 installed and should be able to use type Timestamp, so I created the table:

hive> CREATE EXTERNAL TABLE test1 (epoch Timestamp)
      LOCATION '/user/me';

然后我查询表格：

Then I queried the table:

SELECT * FROM test1;

并得到以下例外：

and got the following exception:

Failed with exception java.io.IOException:java.lang.IllegalArgumentException: 
Timestamp format must be yyyy-mm-dd hh:mm:ss[.fffffffff]

创建外部表时是否遗漏了任何东西？我很困惑，因为Hive（自0.8）以第二个Unix纪元格式支持Timestamp： https://cwiki.apache.org/Hive/languagemanual-types.html#LanguageManualTypes-Timestamps

Have I missed anything when creating the external table? I'm confused because Hive (since 0.8) supports Timestamp in the format of Unix epoch in second: https://cwiki.apache.org/Hive/languagemanual-types.html#LanguageManualTypes-Timestamps

推荐答案

时间戳字符串必须采用错误消息中指定的格式，并且链接为yyyy-mm-dd hh：mm：ss [.fffffffff]。要使用包含epoch的数据，您必须将其定义为BIGINT，然后使用内置的UDF from_unixtime（）转换为表示日期的字符串。然后，您可以使用其他内置的UDF日期功能来操纵这个日期。

The Timestamp string must be in the format specified in the error message and link, yyyy-mm-dd hh:mm:ss[.fffffffff]. To use data that contains epoch, you would have to define as a BIGINT and then use the built-in UDF, from_unixtime() to convert to a string representing the date. Then you could use the other built-in UDF date functions to manipulate this date.

这篇关于如何创建列类型为Timestamp的外部Hive表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！