この記事は公開されてから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のイメージです。
sample_data.csv
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-sample
S3バケット の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型に型変換している点です。
何回か実行してみて結果を確認します。
1回目
2回目
3回目
UNIX時間で計算することで、秒単位で時間を作ることができました。
最後に
今回はrandom
関数を使うことで、UNIX時間に変換した2時点間の時点をランダムに作る方法を検証しました。
どこかで誰かのお役に立てば幸いです。