■ はじめに
Hive で日付・日時の操作をする必要ができたので、メモ。 徐々に増やしていく。
目次
【0】関連する関数 【1】現在日時/日付の取得 1)現在日時 - CURRENT_TIMESTAMP 2)現在日付 - CURRENT_DATE 注意「unix_timestamp(void) is deprecated」について 【2】日付への変換 【3】日時・日付のフォーマット 【4】年/月/日を取得 【5】日時・日付の計算 【6】月初・月末の日付を取得 1)月初 - TRUNC 2)月末 - LAST_DAY
【0】関連する関数
* 以下を参考にするといい。
https://cwiki.apache.org/confluence/display/hive/languagemanual+udf#LanguageManualUDF-DateFunctions
【1】現在日時/日付の取得
1)現在日時 - CURRENT_TIMESTAMP
SELECT CURRENT_TIMESTAMP; -- 2021-02-12 18:15:48.148
2)現在日付 - CURRENT_DATE
SELECT CURRENT_DATE; -- 2021-02-12
注意「unix_timestamp(void) is deprecated」について
* 現在日時を取得する際に、以下のように ~~~~~~~~~ SELECT FROM_UNIXTIME(UNIX_TIMESTAMP()); -- 2021-02-12 09:10:17 ~~~~~~~~~ unix_timestamp(void) を使用している場合 非推奨なので current_timestamp を使うことを検討する => って警告文が出てくる => ただし、あくまで非推奨は、unix_timestamp(void) のみで、 unix_timestamp(string date) や unix_timestamp(string date, string pattern) は、 これに該当しない。(単純に置き換えもできない)
警告文
unix_timestamp(void) is deprecated. Use current_timestamp instead.
公式サイト
https://cwiki.apache.org/confluence/display/hive/languagemanual+udf#LanguageManualUDF-DateFunctions
より、一部抜粋 ~~~~~~~~~~~~~ unix_timestamp() - this has been deprecated since 2.0 in favour of CURRENT_TIMESTAMP constant. unix_timestamp(string date) unix_timestamp(string date, string pattern) ~~~~~~~~~~~~~
置き換え例
-- 置き換え前 SELECT *, -- e.g. '2021-05-22 10:33:23' from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss') AS ex1, -- e.g. '2021-05-22' to_date(from_unixtime(unix_timestamp(), 'yyyy-MM-dd')) AS ex2 FROM sample_table; -- 置き換え後 SELECT *, -- e.g. '2021-05-22 10:33:23' current_timestamp AS ex1, -- e.g. '2021-05-22' to_date(current_timestamp) AS ex2 FROM sample_table;
【2】日付への変換
* 文字列(YYYY-MM-DD HH:MM:DD)から日付(YYYY-MM-DD)へ変換
構文
# e.g. to_date("1970-01-01 00:00:00") = "1970-01-01" to_date(string timestamp) # 戻り値は、Hive versionによって異なる # v2.1.0より前Vesion: string # v2.1.0以降: date
【3】日時・日付のフォーマット
* DATE_FORMAT で指定する * from_unixtime も使える
日時
SELECT DATE_FORMAT(CURRENT_TIMESTAMP,'yyyy/MM/dd HH:mm:ss'); -- 2021/02/12 20:41:40 SELECT DATE_FORMAT('2020-11-11 15:30:12.084','yyyy/MM/dd HH:mm:ss'); -- 2020/11/11 15:30:12
日付
SELECT DATE_FORMAT(CURRENT_TIMESTAMP,'yyyy/MM/dd'); -- 2021/02/12 SELECT DATE_FORMAT('2020-11-11 15:30:12.084','yyyy/MM/dd'); -- 2020/11/11
【4】年/月/日 を取得
年
SELECT YEAR('2020-12-31'); -- 2020 SELECT YEAR(CURRENT_DATE); -- 2021
月
SELECT MONTH('2020-12-31'); -- 12
日
SELECT DAY('2020-12-31'); -- 31
【5】日時・日付の計算
* date_add (加算) で計算 => +が未来、ーで過去を計算できる(以下、サンプル参照) * date_sub (減算) もある
構文
# e.g. date_add('2008-12-31', 1) = '2009-01-01' date_add(date/timestamp/string startdate, tinyint/smallint/int days) # e.g. date_sub('2008-12-31', 1) = '2008-12-30' date_sub(date/timestamp/string startdate, tinyint/smallint/int days) # 戻り値は、Hive versionによって異なる # v2.1.0より前Vesion: string # v2.1.0以降: date
サンプル
-- 1年前を 'yyyy-MM'型(e.g. '2020-05') SELECT from_unixtime(unix_timestamp(date_add(current_date, -365), yyyMMdd, 'yyyy-MM');
【6】月初・月末の日付を取得
・TRUNC => 指定した値から切り捨てられる ・LAST_DAY => 月末の日付を返す
1)月初 - TRUNC
SELECT TRUNC('2021-01-12','MM'); -- 2021-01-01 SELECT TRUNC(CURRENT_DATE,'MM'); -- 2021-02-01
2)月末 - LAST_DAY
SELECT LAST_DAY('2021-01-12'); -- 2021-01-31 SELECT LAST_DAY(CURRENT_DATE); -- 2021-02-28
■ おまけ
コマンドを試すために、環境構築し、過去の記事を書き直した。
Apache Hadoop ~ 環境設定 / Linux編 ~
https://dk521123.hatenablog.com/entry/2020/01/01/000000
Hive / HiveQL ~ 環境設定 / Linux編 ~
https://dk521123.hatenablog.com/entry/2020/01/02/000000
Hadoop / Hive 環境構築時のトラブルシューティング
https://dk521123.hatenablog.com/entry/2021/02/12/160221
参考文献
https://docs.cloudera.com/cdp-private-cloud-base/7.1.5/impala-sql-reference/topics/impala-datetime-functions.html
https://dwgeek.com/hadoop-hive-date-functions-examples.html/
https://obstkel.com/spark-sql-date-functions
https://sparkbyexamples.com/apache-hive/hive-date-and-timestamp-functions-examples/
https://qiita.com/y-ken/items/400b7c70c324ac67af02
関連記事
Hive / HiveQL ~ 入門編 ~
https://dk521123.hatenablog.com/entry/2020/02/25/231235
Hive / HiveQL ~ HiveQL関数 / 文字列置換編 ~
https://dk521123.hatenablog.com/entry/2020/03/10/224640
Hive / HiveQL ~ HiveQL関数・regexp_replace編 ~
https://dk521123.hatenablog.com/entry/2021/06/18/184713
Hive / HiveQL ~ HiveQL関数・文字列関数編 ~
https://dk521123.hatenablog.com/entry/2021/06/21/231033
Hive / HiveQL ~ HiveQL関数 / NULL関連編 ~
https://dk521123.hatenablog.com/entry/2021/06/22/213241
Hive / HiveQL ~ あれこれ編 ~
https://dk521123.hatenablog.com/entry/2020/03/04/225943
Hive / HiveQL ~ 設定プロパティ ~
https://dk521123.hatenablog.com/entry/2020/09/19/152136
Apache Hadoop ~ 環境設定 / Linux編 ~
https://dk521123.hatenablog.com/entry/2020/01/01/000000
Hive / HiveQL ~ 環境設定 / Linux編 ~
https://dk521123.hatenablog.com/entry/2020/01/02/000000
Hadoop / Hive 環境構築時のトラブルシューティング
https://dk521123.hatenablog.com/entry/2021/02/12/160221
差集合 ~ EXCEPT / MINUS etc ~
https://dk521123.hatenablog.com/entry/2021/05/26/142059