Athenaで2時点間の時点をランダムに作ってみた

nayu.t.s

2022.01.24

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

データアナリティクス事業本部の鈴木です。

Athenaで、タイムスタンプを持つ2つのカラムから、各時点間の時間をランダムに作りたいことがあったので、やり方を考えてみました。

やりたいこと

各レコードに時点1と時点2の値があったときに、間の時点をランダムに作り出したいです。

この方法として、Athena エンジンバージョン2が基づいているのPresto 0.217ドキュメントの6.6. Mathematical Functions and Operatorsのセクションに、random関数が記載されているので試してみました。

random関数は、引数として渡した整数と0の間の擬似乱数を返します。引数がない場合は0から1の間の値を返します。

引数ありの場合

引数なしの場合

厳密になにかの分布に従って値を生成したいというよりは、ラフにランダムな時点を取るのが目標です。

準備

検証ため、以下のようなCSVファイルを手作りしました。 t1が時点1、t2が時点2のイメージです。

t1, t2
2020-9-22 00:00:05,2020-10-02 01:00:00
2021-2-03 00:00:00,2021-2-12 10:20:00
2021-3-16 07:00:00,2021-3-17 00:06:00
2021-11-08 00:00:00,2021-11-15 00:00:00
2021-12-12 00:00:00,2021-12-12 03:00:00

cm-nayuts-sampleS3バケットのsample/に配置するとします。

また、データを読み込むためのテーブルも作成しておきます。

CREATE EXTERNAL TABLE IF NOT EXISTS cm_nayuts.sample_data (
  t1 string, 
  t2 string
  )
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.serde2.OpenCSVSerde' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://cm-nayuts-sample/sample/'
TBLPROPERTIES (
  'skip.header.line.count'='1')

やってみる

時点1と時点2をUNIX時間に変換したあと、差分をrandomに渡すことで、時点1からの経過時間をランダムに作るようにしました。今回は、以下のようなSQLを作ってみました。

WITH source_data_unixtime AS (
    SELECT
      CAST(to_unixtime(date_parse(t1, '%Y-%m-%d %H:%i:%s')) AS BIGINT) AS t1,
      CAST(to_unixtime(date_parse(t2, '%Y-%m-%d %H:%i:%s')) AS BIGINT) AS t2
    FROM cm_nayuts.sample_data
),
source_with_t_inter AS (
    SELECT
      t1 AS t1,
      t2 AS t2,
      t1 + random(t2 - t1) as t_inter
    FROM source_data_unixtime       
)
SELECT
  from_unixtime(t1) as t1,
  from_unixtime(t2) as t2,
  from_unixtime(t_inter) as t_inter
FROM source_with_t_inter

ポイントはハイライトした箇所で、to_unixtimeでタイムスタンプをUNIX時間に変換したとき、DOUBLE型の値が返るため、randomで処理できるようBIGINT型に型変換している点です。

何回か実行してみて結果を確認します。