Amazon Redshift: unixtime形式のタイムスタンプをTIMESTAMP型に変換する方法x2

Amazon Redshift: unixtime形式のタイムスタンプをTIMESTAMP型に変換する方法x2

Amazon Redshiftで良く使いそうな便利系SQL

Amazon Redshiftで良く使いそうな便利系SQL

#ビッグデータ
#Redshift UDF
#Amazon Redshift
#AWS
しんや

しんや

facebook logohatena logotwitter logo
Clock Icon2015.12.07

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

以前のエントリでAmazon Redshiftにおいて『文字列としての日付・時刻情報』を『タイムスタンプ型』のデータに変換する際の手順について下記エントリでご紹介しましたが、似たようなケースで『Unixtimeフォーマットの情報をタイムスタンプ型のデータに変換したい』というケースもあるかと思います。

Unixtime(Unix時間)については以下のWikipediaの情報をご参照ください。

当エントリでは、UnixtimeフォーマットのデータをAmazon Redshift上でタイムスタンプ型に変換する際の手法について2つ、ご紹介したいと思います。

データの準備

まずはデータ・環境の準備。以下サイトから『映画の評価』に関するデータをダウンロードします。最新版であるml-latest.zipというものを入手してください。

movielens_01

解凍した内容は以下の様になっています。

$ 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型に変換する事が出来ます。

以下に、この経過秒数をタイムスタンプ値に変換する方法を示します。 SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 982384720.12 * INTERVAL '1 second';

UDFを使う

もう1つが、Amazon Redshiftの機能である『UDF』を使い、PythonコードでUnixtime→Timestamp型の変換を行なう関数を実装し、それをAmazon Redshiftのユーザー定義関数として取り込むというものです。以下にAWS謹製のUDFライブラリを纏めたものがあり、そこにちょうど今回の機能を満たすコードが展開されていましたので、これをそのまま使いたいと思います。

関数名をちょこっと変えた上で、実行します。

# 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)

Share this article

facebook logohatena logotwitter logo

関連記事

【CDI】Informatica Cloud Data Integrationで、ソースのスキーマを自動的に読み込めるという「スキーマ変更処理」を検証してみた

【CDI】Informatica Cloud Data Integrationで、ソースのスキーマを自動的に読み込めるという「スキーマ変更処理」を検証してみた

User avatar
渡部晃季
2023.05.02
Redshiftで各テーブルのカラム毎の利用ブロック数を確認する

Redshiftで各テーブルのカラム毎の利用ブロック数を確認する

User avatar
k.uehara
2023.03.07
[レポート]AWSでデータメッシュアーキテクチャを構築する#ANT336 #reinvent

[レポート]AWSでデータメッシュアーキテクチャを構築する#ANT336 #reinvent

User avatar
nayu.t.s
2023.01.01
dbt Coalesce 2022と来年の告知

dbt Coalesce 2022と来年の告知

User avatar
ちゃあこ
2022.10.28
Classmethod's white logo
Facebook's logo
X's logo
YouTube's logo

© Classmethod, Inc. All rights reserved.