是否可以在Hive CREATE TABLE语句本身中为传入的数据指定日期/时间戳格式? [英] Is there a way to specify Date/Timestamp format for the incoming data within the Hive CREATE TABLE statement itself?
问题描述
我有一个CSV文件,其中包含以下格式的日期和时间戳记值。例如:
I've have a CSV files which contain date and timestamp values in the below formats. Eg:
Col1|col2
01JAN2019|01JAN2019:17:34:41
但是当我在create语句中将Col1定义为Date并将Col2定义为Timestamp时,Hive表仅返回 NULL
当我查询时。
But when I define Col1 as Date and Col2 as Timestamp in my create statement, the Hive tables simply returns NULL
when I query.
CREATE EXTERNAL TABLE IF NOT EXISTS my_schema.my_table
(Col1 date,
Col2 timestamp)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ‘|’
STORED AS TEXTFILE
LOCATION 'my_path';
相反,如果我将数据类型定义为简单的 string
然后它起作用。但这不是我想要的表的样式。
Instead, if I define the data types as simply string
then it works. But that's not how I want my tables to be.
我希望表能够读取正确类型的传入数据。我该如何实现?是否可以使用 CREATE
语句本身定义传入数据的预期数据格式?
I want the table to be able to read the incoming data in correct type. How can I achieve this? Is it possible to define the expected data format of the incoming data with the CREATE
statement itself?
有人可以吗?帮帮我?
Can someone please help?
推荐答案
自Hive 1.2.0起,可以提供其他SerDe属性 timestamp.formats
。有关更多详细信息,请参见此Jira: HIVE-9298
As of Hive 1.2.0 it is possible to provide additional SerDe property "timestamp.formats"
. See this Jira for more details: HIVE-9298
ALTER TABLE timestamp_formats SET SERDEPROPERTIES ("timestamp.formats"="ddMMMyyyy:HH:mm:ss");
这篇关于是否可以在Hive CREATE TABLE语句本身中为传入的数据指定日期/时间戳格式?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!