创建表时将字符串转换为hive中的时间戳

Question

我在csv中有一些实时数据。我想从这些数据创建外部表，以便我可以从hive查询它。挑战在于，这些文件将unix epoch作为last_updated_epoch，并将日期字段last_updated格式化为2021-08-04T06:48:55。我无法将此日期作为时间戳读取，因此我需要将这些字段转换为时间戳，以便根据日期和时间进行查询。

我能做的: 我能够创建一个时间戳作为BIGINT和last_updated作为字符串的表。但是不能在创建表时将last_updated作为时间戳。Select works fine:

select last_updated, cast(from_unixtime(last_updated_epoch) as TIMESTAMP) from station_mart limit 10;

Answer 1

你可以做的是:-

1. 使用您想要的数据类型创建外部表
将csv文件放到不同的位置。
2. 使用load data inpath命令将数据首先加载到一个临时表中然后是着陆地点中的数据插入到外部表中临时表。在将此数据加载到外部表中时，您可以
3. 要处理实时数据，可以创建步骤2和步骤3的脚本以低于SLA的间隔安排它，这将为您提供真实的数据时间。

Load data inpath命令会移动数据，所以你不必担心数据重复。

Answer 2

我建议用三种不同的方法将字符串转换为时间戳。如果您的表不是定义为STORED AS TEXTFILE的，那么前两种方法适用，如果您可以使用LazySimpleSerDe(与STORED AS TEXTFILE相同)，则第三种方法适用，在这种情况下，SerDe将在尽可能低的级别上进行转换。

select:中的转换

select last_updated as original_last_updated, --initial string 
--1st method (preferable, because it is not using SimpleDateFormat(which is really not so simple) class internally)
--you can additionally apply timestamp() construct, though in Hive String in proper format is fully interchangeable
regexp_replace(last_updated,'^(\d{4}-\d{2}-\d{2})T(\d{2}:\d{2}:\d{2})','$1 $2') as result1, 
--2nd method is using SimpleDateFormat under the hood, easier to read
from_unixtime(unix_timestamp(last_updated,"yyyy-MM-dd'T'HH:mm:ss")) as result2
from
(select '2021-08-04T06:48:55' as last_updated ) s

结果:

original_last_updated         result1                result2
2021-08-04T06:48:55           2021-08-04 06:48:55    2021-08-04 06:48:55

第三个方法-使用LazySimpleSerDe:

如果您正在使用LazySimpleSerDe(存储为TEXTFILE)，可以通过向SerDe属性"timestamp.formats"提供格式来支持其他时间戳格式(从带有HIVE-9298的1.2.0版本开始)。尝试将"yyyy-MM-dd'T'HH:mm:ss"作为属性值并将列类型定义为timestamp。虽然LazySimpleSerDe不识别引号，但可以在具有未引号值的简单CSV的情况下使用。