如何在hive中将unix纪元时间转换为日期字符串

I have a log file which contains timestamp column. The timestamp is in unix epoch time format.

我有一个包含时间戳列的日志文件。时间戳采用unix纪元时间格式。

I want to create a partition based on a timestamp with partitions year, month and day.

我想基于带有分区年，月和日的时间戳创建分区。

So far I have done this but it is throwing an error.

到目前为止，我已经做到了这一点，但这是一个错误。

PARSE ERROR cannot recognize input '(' in column type

Here is my code.

这是我的代码。

from (
      from raw_data
            MAP  ${PREFIX}raw_data.line
            USING 's3://scripts/clean.py'
            AS (timestamp STRING, name STRING)
      ) map_out
INSERT OVERWRITE TABLE date_base_data_temp PARTITION(year(timestamp), month(timestamp)), day(timestamp))) 
    select map_out.name;

4 个解决方案

#1

Oof, that looks ugly. Try using this function in Hive:

Oof，看起来很难看。尝试在Hive中使用此功能：

SELECT from_unixtime(unix_timestamp) as new_timestamp from raw_data ...

Or if timestamp is in ms instead of seconds:

或者，如果时间戳以ms而不是秒为单位：

SELECT from_unixtime(unix_timestamp DIV 1000) as new_timestamp from raw_data ...

That converts a unix timestamp into a YYYY-MM-DD HH:MM:SS format, then you can use the following functions to get the year, month, and day:

将unix时间戳转换为YYYY-MM-DD HH：MM：SS格式，然后您可以使用以下函数来获取年，月和日：

SELECT year(new_timestamp) as year, month(new_timestamp) as month, day(new_timestamp) as day ...

#2

With more recent releases of Hive and SparkSQL, data type of date and type casting options are available. Following should work in Hive as well as Spark SQL

随着最新版本的Hive和SparkSQL，可以使用日期和类型转换选项的数据类型。以下应该在Hive和Spark SQL中工作

SELECT cast(from_unixtime(epoch_datetime) as date) from myHiveTable

#3

If you need to convert the date in custom format, use this:

如果您需要以自定义格式转换日期，请使用以下命令：

select date_format(from_unixtime(epoch_datetime),'yyyMM') as formatted_date from myHiveTable;

which will return the date as yearMonth e.g. 201708

这将把日期作为yearMonth返回，例如201708

#4

Adding this query to the list where the timestamp needs to be converted to date string yyyy-MM-dd for a string partition:

将此查询添加到需要将时间戳转换为字符串分区的日期字符串yyyy-MM-dd的列表中：

hive> select date_format(from_unixtime(epoch_datetime), 'yyyy-MM-dd') as day from table_name limit 20;

-- If required, remove the millis precision for timestamps
hive> select date_format(from_unixtime(cast(epoch_datetime/1000 as bigint)), 'yyyy-MM-dd') as day from table_name limit 20;

#1