Hive和SparkSQL不支持datetime类型? [英] Hive and SparkSQL do not support datetime type?

问题描述

Hive和SparkSQL是否不支持datetime的数据类型吗?

Is it true that Hive and SparkSQL do not support the datatype of datetime?

根据我对参考文献的阅读，它们似乎仅支持date和timestamp.前者没有时间成分(即小时，分钟和秒)；后者具有很高的精度(低至毫秒)，但不是很可读(它总是需要通过from_unixtime()或date_format()进行转换，并且结果将是字符串，而不是datetime类型).

From my reading of the references, they seem to support only date and timestamp. The former does not a time component (i.e. hour, minute, and second); the latter has high precision (down to millisecond) but is not very human readable (it always require a conversion by from_unixtime() or date_format(), and the result would be a string, not a datetime type).

相反，其他数据库系统(例如MySQL)的数据类型确实为datetime. (例如，参见此 ref )

In contrast, other database systems, such as MySQL does have a datatype of datetime. (E.g. see this ref)

任何好的建议/技巧如何解决此限制?

Any good suggestions/tips how to work around this limitation?

这些是我的参考文献:

1. Hive语言手册:日期/时间类型
2. Hive语言手册:日期函数

1. Hive Language Manual: Date/Time Types
2. Hive Language Manual: Date Functions

更新:关于人类可读性

这里我在MySQL上举一个例子来说明我的观点 关于人类可读性:

Here I give an example on MySQL to illustrate my point about human-readability:

-- MySQL code 
select 
  cast(now() as date)         as asDate,          -- human readable
  cast(now() as dateTime)     as asDateTime,      -- human readable  
  unix_timestamp(now())       as asUnixTimestamp, -- not H/R
  cast(from_unixtime(unix_timestamp(now())) 
        as dateTime) 
                              asDateTimeAgain     -- cast into dateTime to make it H/R

显示为:

(请注意第四列asDateTimeAgain，该列易于阅读)

(Pay attention to the fourth column asDateTimeAgain, which is human readable)

+------------+---------------------+-----------------+---------------------+
| asDate     | asDateTime          | asUnixTimestamp | asDateTimeAgain     |
+------------+---------------------+-----------------+---------------------+
| 2017-10-21 | 2017-10-21 22:37:15 |      1508625435 | 2017-10-21 22:37:15 |
+------------+---------------------+-----------------+---------------------+
1 row in set (0.00 sec)

并且限制不仅限于人类可读性.一串 datetime的表示形式是 易读，但随后它失去了datetime的属性，并且 将需要进一步转换回datatime以便进行日期/时间处理， 例如min()，max()，然后将值捕获到java.util.Date

And the limitation is not just about human-readability. A string representation of datetime is human readable, but then it lost the property of datetime and will require further conversion back into datatime for date/time processing, such as min(), max(), and capturing the values into java.util.Date

-- Hive/SparkSQL  code
select 
  current_date                    asDate,
  unix_timestamp()                asUnixTimestamp,
  from_unixtime(unix_timestamp(), 
    'yyyy-MM-dd HH:mm:ss')        asString

输出将是以下内容，其中第三列是字符串而不是 datetime类型

The output would be this, where the third column is a string and not a datetime type

------------------------------------------------------
| asDate     | asUnixTimestamp | asString            |
| ---------- | --------------- | ------------------- |
| 2017-10-21 | 1508625586      | 2017-10-21 22:39:46 |
------------------------------------------------------

推荐答案

(我在这里提供答案)

请勿将timestamp与"unix时间戳"相混淆

Do not confuse timestamp with"unix timestamp"

timestamp实际上是人类可读的；而"unix时间戳记" 自1970年1月1日以来的秒数/毫秒数的确是 不太可读.

timestamp is actually human readable; while "unix timestamp", being the number of seconds/milliseconds since Jan 1, 1970, is indeed not very human readable.

但是，我们可以使用cast()来转换后者(通过功能from_unixtime()) 得到前者.

However, we can use cast() to convert the latter (through a function from_unixtime()) to get the former.

-- Hive/SparkSQL  code
select 
  current_date                    asDate,          -- human readable
  unix_timestamp()                asUnixTimestamp, -- not human readable
  from_unixtime(unix_timestamp()) asString,        -- human readable
  cast(from_unixtime(unix_timestamp())             
    as date)                      asDate2,         -- human readable
  cast(from_unixtime(unix_timestamp())             
    as timestamp)                 asTimestamp      -- human readable

结果:

-------------------------------------------------------------------------------------------
| asDate     | asUnixTimestamp | asString            | asDate2    | asTimestamp           |
| ---------- | --------------- | ------------------- | ---------- | --------------------- |
| 2017-10-22 | 1508687321      | 2017-10-22 15:48:41 | 2017-10-22 | 2017-10-22 15:48:41.0 |
-------------------------------------------------------------------------------------------

这篇关于Hive和SparkSQL不支持datetime类型?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！