问题描述
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?
这些是我的参考文献:
- Hive Language Manual: Date/Time Types
- Hive Language Manual: Date Functions
更新:关于人类可读性
这里我在MySQL上举一个例子来说明我的观点关于人类可读性:
Here I give an example on MySQL to illustrate my pointabout 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 stringrepresentation of datetime ishuman readable, but then it lost the property of datetime andwill 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 nota 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", beingthe number of seconds/milliseconds since Jan 1, 1970, is indeednot 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类型?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！