athenaでcreate table のサポート型で対応してない形式の日付データを扱う

はじめに

athenaにてS3に保存したJSONファイルの以下の項目を日付形式で扱う方法

  "queued_at": "2019-04-12T06:02:48.231Z",

対応

TIMESTAMP型で定義

athenaでサポートされているデータ型で、日付型にTIMESTAMPがある

docs.aws.amazon.com

テーブル作成

CREATE EXTERNAL TABLE timestamp_test (
  queued_at timestamp
)
 ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://<バケット名>/'

クエリ

SELECT queued_at,
FROM timestamp_test;

以下のエラーが発生

Error running query: HIVE_BAD_DATA: Error parsing field value '2019-04-11T14:45:00.417Z' for field 3: Timestamp format must be yyyy-mm-dd hh:mm:ss[.fffffffff]

aws.amazon.com

Athena は、Java の TIMESTAMP 形式「YYYY-MM-DD HH:MM:SS.fffffffff」 (小数点以下 9 桁) を必要とします。

データが必須の TIMESTAMP 形式でない場合は、列を STRING として定義し、次に Presto の日付と時刻の関数を使用してフィールドを DATE または TIMESTAMP としてクエリ内に読み取ります。

STRING型で定義し、date_parse関数を使用

テーブル作成

CREATE EXTERNAL TABLE timestamp_test (
  queued_at string
)
 ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://<バケット名>/'

クエリ

SELECT queued_at,
         typeof(queued_at)
FROM timestamp_test;

値：2019-04-11T14:45:00.417Z
型：varchar

以下で指定子を確認

prestodb.github.io

SELECT queued_at,
        date_parse(queued_at,'%Y-%m-%dT%H:%i:%s.%f'),
         typeof(queued_at)
FROM timestamp_test;

クエリを実行すると以下のエラー
UTCのタイムゾーンを示す最後の"Z"指定子で定義できない。。

INVALID_FUNCTION_ARGUMENT: Invalid format: "2019-04-11T14:45:00.417Z" is malformed at "Z"

from_iso8601_timestamp関数を使用

aws.amazon.com

注意: データが ISO 8601 形式である場合は、操作を進める前に from_iso8601_timestamp() 関数を使用してデータを TIMESTAMP に変換してください。

from_iso8601_timestamp関数で取得

SELECT queued_at,
        from_iso8601_timestamp(queued_at),
         typeof(from_iso8601_timestamp(queued_at))
FROM timestamp_test;

値：2019-04-11 14:45:00.417 UTC
型：timestamp with time zone

無事timestamp型に変更！！

(追記)表示形式を指定

SELECT queued_at,
         date_format(from_unixtime(to_unixtime(from_iso8601_timestamp(queued_at))),'%H:%i:%s') AS "queued_time"
FROM timestamp_test;

値：14:45:00

(追記)各関数利用時のtype

SELECT 
    -- queued_at
    queued_at,
    from_iso8601_timestamp(queued_at) as iso8601,
    typeof(from_iso8601_timestamp(queued_at)) as iso8601_type,
    -- to_unixtime
    to_unixtime(from_iso8601_timestamp(queued_at)) as to_unixtime, 
    typeof(to_unixtime(from_iso8601_timestamp(queued_at))) as to_unixtime_type,
    -- from_unixtime
    from_unixtime(to_unixtime(from_iso8601_timestamp(queued_at))) as from_unixtime, 
    typeof(from_unixtime(to_unixtime(from_iso8601_timestamp(queued_at)))) as from_unixtime_type,
    -- date_add
    date_add('hour', 9,from_unixtime(to_unixtime(from_iso8601_timestamp(queued_at)))) as date_add, 
    typeof(date_add('hour', 9,from_unixtime(to_unixtime(from_iso8601_timestamp(queued_at))))) as date_add_type   
FROM timestamp_test;

queued_at	iso8601	iso8601_type	to_unixtime	to_unixtime_type	from_unixtime	from_unixtime_type	date_add	date_add_type
2019-04-13T14:45:00.372Z	2019-04-13 14:45:00.372 UTC	timestamp with time zone	1.555166700372E9	double	2019-04-13 14:45:00.372	timestamp	2019-04-13 23:45:00.372	timestamp