この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
以前のエントリでAmazon Redshiftにおいて『文字列としての日付・時刻情報』を『タイムスタンプ型』のデータに変換する際の手順について下記エントリでご紹介しましたが、似たようなケースで『Unixtimeフォーマットの情報をタイムスタンプ型のデータに変換したい』というケースもあるかと思います。
Unixtime(Unix時間)については以下のWikipediaの情報をご参照ください。
当エントリでは、UnixtimeフォーマットのデータをAmazon Redshift上でタイムスタンプ型に変換する際の手法について2つ、ご紹介したいと思います。
データの準備
まずはデータ・環境の準備。以下サイトから『映画の評価』に関するデータをダウンロードします。最新版であるml-latest.zipというものを入手してください。
解凍した内容は以下の様になっています。
$ ls
README.txt links.csv movies.csv ratings.csv tags.csv
$ wc links.csv
30107 30107 633192 links.csv
$ wc movies.csv
30107 138586 1534286 movies.csv
$ wc ratings.csv
21622188 21622188 584276710 ratings.csv
$ wc tags.csv
516140 856229 18535621 tags.csv
$
Amazon S3の所定のバケットにファイルをアップロードした上で、Amazon Redshift上にテーブルを作成、データをCOPY処理で取り込みます。
DROP TABLE public.mllinks;
CREATE TABLE public.mllinks (
movie_id INT NOT NULL,
imdb_id INT,
tmdb_id INT
);
DROP TABLE public.mlmovies;
CREATE TABLE public.mlmovies (
movie_id INT NOT NULL,
title VARCHAR(200) NOT NULL,
genres VARCHAR(200) NOT NULL
);
DROP TABLE public.mlratings;
CREATE TABLE public.mlratings (
user_id INT NOT NULL,
movie_id INT NOT NULL,
rating FLOAT,
timestamp_bigint BIGINT,
timestamp TIMESTAMP
);
DROP TABLE public.mltags;
CREATE TABLE public.mltags (
user_id INT NOT NULL,
movie_id INT NOT NULL,
tags VARCHAR(300),
timestamp_bigint BIGINT,
timestamp TIMESTAMP
);
Unixtime形式の項目を保持するテーブルについては、従来のファイル項目に加えてTIMESTAMP型の項目もテーブルに用意しておき、COPY時にもテーブル項目を指定して取り込む形としました。
COPY public.mllinks
FROM 's3://xxxxxxxxxx/links.csv'
CREDENTIALS 'aws_access_key_id=XXXXXXXXXX;aws_secret_access_key=YYYYYYYYYY'
IGNOREHEADER 1 CSV;
COPY public.mlmovies
FROM 's3://xxxxxxxxxx/movies.csv'
CREDENTIALS 'aws_access_key_id=XXXXXXXXXX;aws_secret_access_key=YYYYYYYYYY'
IGNOREHEADER 1 CSV;
COPY public.mlratings
(user_id, movie_id, rating,timestamp_bigint)
FROM 's3://xxxxxxxxxx/ratings.csv'
CREDENTIALS 'aws_access_key_id=XXXXXXXXXX;aws_secret_access_key=YYYYYYYYYY'
IGNOREHEADER 1 CSV;
COPY public.mltags
(user_id, movie_id,tags,timestamp_bigint)
FROM 's3://xxxxxxxxxx/tags.csv'
CREDENTIALS 'aws_access_key_id=XXXXXXXXXX;aws_secret_access_key=YYYYYYYYYY'
IGNOREHEADER 1 CSV;
登録完了後の件数確認。
# SELECT COUNT(*) FROM public.mllinks;
count
-------
30106
(1 row)
# SELECT COUNT(*) FROM public.mlmovies;
count
-------
30106
(1 row)
# SELECT COUNT(*) FROM public.mlratings;
count
----------
21622187
(1 row)
# SELECT COUNT(*) FROM public.mltags;
count
--------
516139
(1 row)
SQL関数を使う
ここからは実際の手法に関する解説です。手法1つ目は、既存のSQL関数で何とかする方法。
Amazon Redshiftが準拠しているPostgreSQLでの日付/時刻関数と演算子を使った形で、'epoch'オプションを指定する事で経過秒数をタイムスタンプ値に変換する方法があるようです。これを使うと、SQLのみでUnixtimeフォーマットでのデータをTIMESTAMP型に変換する事が出来ます。
- 日付/時刻関数と演算子
- [Amazon Redshift] Converting unix epoch time into timestamps — Valkrysa
- How does it feel?: redshiftのSQLでunixtimeをtimestamp型に変換
UDFを使う
もう1つが、Amazon Redshiftの機能である『UDF』を使い、PythonコードでUnixtime→Timestamp型の変換を行なう関数を実装し、それをAmazon Redshiftのユーザー定義関数として取り込むというものです。以下にAWS謹製のUDFライブラリを纏めたものがあり、そこにちょうど今回の機能を満たすコードが展開されていましたので、これをそのまま使いたいと思います。
- amazon-redshift-udfs/scalar-udfs at master · awslabs/amazon-redshift-udfs · GitHub
- amazon-redshift-udfs/f_unixts_to_timestamp.sql at master · awslabs/amazon-redshift-udfs · GitHub
関数名をちょこっと変えた上で、実行します。
# CREATE OR REPLACE FUNCTION cm_unixts_to_timestamp(ts BIGINT, units CHAR(2))
RETURNS timestamp
STABLE
AS $$
import pandas
return pandas.to_datetime(ts, unit=units.rstrip())
$$ LANGUAGE plpythonu;
CREATE FUNCTION
動作確認
上記2つの方法をそれぞれ試してみたのが以下のSQL文です。6行目がSQL関数のみを使ったもの、7行目がUDFとなります。いずれの場合も、同じタイムスタンプ型の結果を表示していますね。
# SELECT
user_id,
movie_id,
rating,
timestamp_int,
TIMESTAMP 'epoch' + timestamp_int * INTERVAL '1 second' AS timestamp_calc,
cm_unixts_to_timestamp(timestamp_int, 's'),
timestamp
FROM
public.mlratings
WHERE
movie_id = 122882
ORDER BY
rating DESC,
user_id ASC
LIMIT 20;
user_id | movie_id | rating | timestamp_int | timestamp_calc | cm_unixts_to_timestamp | timestamp
---------+----------+--------+---------------+---------------------+------------------------+-----------
29 | 122882 | 5 | 1437250557 | 2015-07-18 20:15:57 | 2015-07-18 20:15:57 |
2313 | 122882 | 5 | 1436960396 | 2015-07-15 11:39:56 | 2015-07-15 11:39:56 |
2811 | 122882 | 5 | 1438028157 | 2015-07-27 20:15:57 | 2015-07-27 20:15:57 |
2956 | 122882 | 5 | 1432733365 | 2015-05-27 13:29:25 | 2015-05-27 13:29:25 |
3908 | 122882 | 5 | 1432622798 | 2015-05-26 06:46:38 | 2015-05-26 06:46:38 |
4273 | 122882 | 5 | 1432061318 | 2015-05-19 18:48:38 | 2015-05-19 18:48:38 |
6830 | 122882 | 5 | 1431806383 | 2015-05-16 19:59:43 | 2015-05-16 19:59:43 |
7470 | 122882 | 5 | 1431901341 | 2015-05-17 22:22:21 | 2015-05-17 22:22:21 |
8015 | 122882 | 5 | 1432824659 | 2015-05-28 14:50:59 | 2015-05-28 14:50:59 |
8809 | 122882 | 5 | 1433327659 | 2015-06-03 10:34:19 | 2015-06-03 10:34:19 |
13929 | 122882 | 5 | 1432355280 | 2015-05-23 04:28:00 | 2015-05-23 04:28:00 |
14311 | 122882 | 5 | 1438435752 | 2015-08-01 13:29:12 | 2015-08-01 13:29:12 |
14355 | 122882 | 5 | 1437346294 | 2015-07-19 22:51:34 | 2015-07-19 22:51:34 |
14705 | 122882 | 5 | 1435090379 | 2015-06-23 20:12:59 | 2015-06-23 20:12:59 |
15786 | 122882 | 5 | 1433671692 | 2015-06-07 10:08:12 | 2015-06-07 10:08:12 |
16323 | 122882 | 5 | 1436747794 | 2015-07-13 00:36:34 | 2015-07-13 00:36:34 |
17231 | 122882 | 5 | 1432356775 | 2015-05-23 04:52:55 | 2015-05-23 04:52:55 |
19308 | 122882 | 5 | 1432922628 | 2015-05-29 18:03:48 | 2015-05-29 18:03:48 |
21577 | 122882 | 5 | 1436645355 | 2015-07-11 20:09:15 | 2015-07-11 20:09:15 |
21720 | 122882 | 5 | 1436052297 | 2015-07-04 23:24:57 | 2015-07-04 23:24:57 |
(20 rows)
まとめ
以上、Amazon RedshiftにおけるUnixtime→TIMESTAMP型変換の手法の紹介でした。取り込むデータの内容によってはこういう形(Unixtime)で来ることも十分想定される一方、Amazon Redshiftや更にはBIツール等で活用する為にデータ型を変換させる必要が出てくるので、適切な変換方法を用意・または実装出来るようにしておきたいですね。こちらからは以上です。
おまけ
今回取り込んだデータは様々な映画の評価データが含まれています。試しにどんな感じになってるか見てみたいと思います。
まずは上記で実践した『レーティング』に関するタイムスタンプ情報(こちらはユーザーの評価したタイミングがタイムスタンプ情報となっているようです)の値を実際のテーブルに更新します。
# UPDATE public.mlratings
SET timestamp = updated_timestamp_ratings.timestamp_udf
FROM
(SELECT
user_id,
movie_id,
timestamp_bigint,
cm_unixts_to_timestamp(public.mlratings.timestamp_bigint, 's') AS timestamp_udf
FROM
public.mlratings) updated_timestamp_ratings
WHERE
public.mlratings.user_id = updated_timestamp_ratings.user_id
AND public.mlratings.movie_id = updated_timestamp_ratings.movie_id
AND public.mlratings.timestamp_bigint = updated_timestamp_ratings.timestamp_bigint;
UPDATE 21622187
更新を掛けた上で、レーティングの平均、登録データの最古・最新の情報を割り出してみました。V8&ヒャッハーなあのシリーズの評価はこんな感じになっているようです。
# SELECT
spec_movies.movie_id,
spec_movies.title,
AVG(spec_movies.rating) AS rating,
MIN(spec_movies.timestamp),
MAX(spec_movies.timestamp)
FROM
(SELECT
public.mlmovies.movie_id,
public.mlmovies.title,
public.mlratings.rating,
public.mlratings.timestamp
FROM
public.mlmovies
INNER JOIN public.mlratings
ON public.mlmovies.movie_id = public.mlratings.movie_id
WHERE
public.mlmovies.title like'%Mad Max%') spec_movies
GROUP BY
spec_movies.movie_id,
spec_movies.title
ORDER BY
rating DESC;
movie_id | title | rating | min | max
----------+--------------------------------------+------------------+---------------------+---------------------
122882 | Mad Max: Fury Road (2015) | 3.92151898734177 | 2015-04-25 11:09:52 | 2015-08-06 05:16:14
3703 | Road Warrior, The (Mad Max 2) (1981) | 3.62527392257122 | 2000-05-22 18:13:35 | 2015-08-06 05:16:34
3702 | Mad Max (1979) | 3.53132832080201 | 2000-05-22 18:13:35 | 2015-08-06 05:16:34
3704 | Mad Max Beyond Thunderdome (1985) | 3.11204576043069 | 2000-05-22 18:13:35 | 2015-08-06 05:16:34
(4 rows)
また、再来週シリーズ最新7作目が公開されるあの超人気シリーズについては、このような評価となっているようです。最新7作目はどの様な評価となるのでしょうか。楽しみですね。
(中略)
WHERE
public.mlmovies.title like'%Star Wars%') spec_movies
(後略)
movie_id | title | rating | min | max
----------+---------------------------------------------------------------+------------------+---------------------+---------------------
260 | Star Wars: Episode IV - A New Hope (1977) | 4.17762422435721 | 1996-03-01 00:00:00 | 2015-08-06 06:45:08
1196 | Star Wars: Episode V - The Empire Strikes Back (1980) | 4.17274254599923 | 1996-10-23 22:18:00 | 2015-08-06 06:47:12
1210 | Star Wars: Episode VI - Return of the Jedi (1983) | 3.99245114627299 | 1996-10-24 15:05:55 | 2015-08-06 06:47:22
100089 | Star Wars Uncut: Director's Cut (2012) | 3.5 | 2013-04-04 20:18:22 | 2015-06-21 02:39:15
33493 | Star Wars: Episode III - Revenge of the Sith (2005) | 3.45986699659759 | 2005-05-20 16:35:03 | 2015-08-06 05:16:45
79006 | Empire of Dreams: The Story of the 'Star Wars' Trilogy (2004) | 3.42268041237113 | 2010-08-16 05:41:19 | 2015-07-15 19:44:25
5378 | Star Wars: Episode II - Attack of the Clones (2002) | 3.11275955810053 | 2002-05-05 01:12:15 | 2015-08-06 05:16:31
2628 | Star Wars: Episode I - The Phantom Menace (1999) | 3.09876160990712 | 1999-05-09 10:16:40 | 2015-08-06 05:16:30
61160 | Star Wars: The Clone Wars (2008) | 3.02260778128286 | 2008-08-23 02:36:08 | 2015-08-06 05:16:38
136485 | Robot Chicken: Star Wars (2007) | 3 | 2015-06-30 23:29:58 | 2015-07-07 04:10:28
135216 | The Star Wars Holiday Special (1978) | 1.91666666666667 | 2015-06-18 05:20:37 | 2015-07-24 17:19:18
109713 | Star Wars: Threads of Destiny (2014) | 1.25 | 2014-03-09 01:58:47 | 2014-12-27 23:24:38
(12 rows)