Treasure Dataで大規模なマスタデータを扱う際にはtimeカラムインデックスを活用しよう

2016/09/13 - データベース, ビッグデータ Presto, Treasure Data, ビッグデータ

DACではTreasure Dataを利用して各種データの蓄積や集計を行っています。Treasure Dataは時系列のデータを扱うのに特にすぐれたアーキテクチャなのですが、セグメントIDとユーザーIDの組み合わせといった大量のマスタデータを利用した計算にも利用することもできます。そのような場合にtimeカラムインデックスを活用してマスタデータを高速に抽出する方法について解説します。

Treasure Dataでは時系列のデータを効率よくあつかうため、timeカラムが固定インデックスとなっており、3600秒（1時間）ごとのパーティショニングに分けてインポートされます。この性質を利用して、時系列で格納する必要のないデータについては「セグメントID * 3600」「カテゴリID * 3600」のようにマスタのキー値を元に作成した時間を設定することで値が高速に取得できるようになります。

検証のためにtimeカラムをセグメントIDとして設定したテーブルを作成します。テーブル作成の元ネタはセグメントとユーザーのM：N対応を縦持ちで持つテストデータで、総行数は約8億行あります。

-- timeカラムをセグメントID * 3600の設定
INSERT INTO TABLE tmp_segment_time_test
SELECT
segment_id * 3600 time,
segment_id,
tuuid
FROM
segment_data

-- timeカラムをセグメントID * 3600の設定

INSERT INTO TABLE tmp_segment_time_test

SELECT

segment_id * 3600 time,

segment_id,

tuuid

FROM

segment_data

指定したふたつのセグメント同士の重複ユーザー数を抽出してみましょう。

-- segment_idでセグメントを指定
SELECT
COUNT(distinct t1.tuuid)
FROM
(SELECT tuuid FROM tmp_segment_time_test t1 WHERE segment_id = 61306) t1
INNER JOIN (SELECT tuuid FROM tmp_segment_time_test t1 WHERE segment_id = 51486) t2
ON t1.tuuid = t2.tuuid

-- segment_idでセグメントを指定

SELECT

COUNT(distinct t1.tuuid)

FROM

(SELECT tuuid FROM tmp_segment_time_test t1 WHERE segment_id = 61306) t1

INNER JOIN (SELECT tuuid FROM tmp_segment_time_test t1 WHERE segment_id = 51486) t2

ON t1.tuuid = t2.tuuid

Presto計算ログ（1分36秒）

-- memory:1.02GB, peak memory:10.22GB, queued time:1.24ms
20160816_145600_04229_uspws                                                                                                        1.60m           rows  bytes bytes/sec done   total             
[0] output &lt;- aggregation &lt;- [1]                                                                                                   FINISHED          10    90B  15.0KB/s    1 /     1             
 [1] aggregation &lt;- aggregation &lt;- [2]                                                                                             FINISHED      36,657  1.8MB  23.5MB/s   60 /    60             
  [2] aggregation &lt;- project &lt;- innerjoin &lt;- left:project &lt;- filter &lt;- aone_odessa_dev.tmp_segment_time_test &lt;- right:remoteSource FINISHED 951,752,677 24.6GB  39.4MB/s  110 /   111 [*] FullScan
   [3] project &lt;- filter &lt;- aone_odessa_dev.tmp_segment_time_test                                                                  FINISHED 810,615,799 17.5GB  21.2MB/s   61 /    61 [*] FullScan
finished at 2016-08-16T14:57:36Z

-- memory:1.02GB, peak memory:10.22GB, queued time:1.24ms

20160816_145600_04229_uspws 1.60m rows bytes bytes/sec done total

[0] output <- aggregation <- [1] FINISHED 10 90B 15.0KB/s 1 / 1

[1] aggregation <- aggregation <- [2] FINISHED 36,657 1.8MB 23.5MB/s 60 / 60

[2] aggregation <- project <- innerjoin <- left:project <- filter <- aone_odessa_dev.tmp_segment_time_test <- right:remoteSource FINISHED 951,752,677 24.6GB 39.4MB/s 110 / 111 [*] FullScan

[3] project <- filter <- aone_odessa_dev.tmp_segment_time_test FINISHED 810,615,799 17.5GB 21.2MB/s 61 / 61 [*] FullScan

finished at 2016-08-16T14:57:36Z

指定のセグメントだけを取りたいのにテーブルに対するフルスキャンが走っており、ピークメモリ使用量も大きくなっています。これに対してtimeカラムでセグメントIDを指定してみます。

-- timeでセグメントを指定
SELECT
COUNT(distinct t1.tuuid)
FROM
(SELECT tuuid FROM tmp_segment_time_test t1 WHERE time = 61306 * 3600) t1
INNER JOIN (SELECT tuuid FROM tmp_segment_time_test t1 WHERE time = 51486 * 3600) t2
ON t1.tuuid = t2.tuuid

-- timeでセグメントを指定

SELECT

COUNT(distinct t1.tuuid)

FROM

(SELECT tuuid FROM tmp_segment_time_test t1 WHERE time = 61306 * 3600) t1

INNER JOIN (SELECT tuuid FROM tmp_segment_time_test t1 WHERE time = 51486 * 3600) t2

ON t1.tuuid = t2.tuuid

Presto計算ログ（20秒）

-- memory:1.03GB, peak memory:1.03GB, queued time:446.46us
20160816_145219_04225_uspws                                                                                                        19.54s         rows  bytes bytes/sec done   total                                                   
[0] output &lt;- aggregation &lt;- [1]                                                                                                   FINISHED          0     0B      0B/s    0 /     1                                                   
 [1] aggregation &lt;- aggregation &lt;- [2]                                                                                             FINISHED          0     0B      0B/s    0 /    60                                                   
  [2] aggregation &lt;- project &lt;- innerjoin &lt;- left:project &lt;- filter &lt;- aone_odessa_dev.tmp_segment_time_test &lt;- right:remoteSource FINISHED 14,391,787  712MB   121MB/s    5 /     6 [1976-12-29 10:00:00 UTC, 1976-12-29 10:00:00 UTC)
   [3] project &lt;- filter &lt;- aone_odessa_dev.tmp_segment_time_test                                                                  FINISHED 14,127,595  306MB  21.4MB/s    1 /     1 [1975-11-16 06:00:00 UTC, 1975-11-16 06:00:00 UTC)
finished at 2016-08-16T14:52:39Z

-- memory:1.03GB, peak memory:1.03GB, queued time:446.46us

20160816_145219_04225_uspws 19.54s rows bytes bytes/sec done total

[0] output <- aggregation <- [1] FINISHED 0 0B 0B/s 0 / 1

[1] aggregation <- aggregation <- [2] FINISHED 0 0B 0B/s 0 / 60

[2] aggregation <- project <- innerjoin <- left:project <- filter <- aone_odessa_dev.tmp_segment_time_test <- right:remoteSource FINISHED 14,391,787 712MB 121MB/s 5 / 6 [1976-12-29 10:00:00 UTC, 1976-12-29 10:00:00 UTC)

[3] project <- filter <- aone_odessa_dev.tmp_segment_time_test FINISHED 14,127,595 306MB 21.4MB/s 1 / 1 [1975-11-16 06:00:00 UTC, 1975-11-16 06:00:00 UTC)

finished at 2016-08-16T14:52:39Z

実行時間が20%程度になり、ピークメモリ使用量も10%程度に削減されています。timeカラムインデックスを利用しているため、セグメントIDが「1975-11-16 06:00:00 UTC」という扱いになっています。timeカラムインデックスを利用した格納・取得方法はHiveでもPrestoでも効きますので、時系列に格納する必要性のないデータについては、マスターデータのキーをtimeとして指定しながら格納することで高速な抽出ができるようになります。もちろん結果値は同等です。

注意点としてはtimeはBigInteger型であり、日付型としても扱われることから1億年と2000年前から検索するといった事はできません。このような値をtime値を格納すると正常にパーティショニングされず、timeを利用していないクエリについても正常に取得できなくなる可能性があります。このため大きなID番号を取り扱う際には「time * 3600」ではなく「time * 360」としたうえでセグメントIDとの複合キーにするなど、適切な範囲で散らばるようにIDをグルーピングすべきです。

以上、Treasure Dataで大規模なマスタデータを扱う際にはtimeカラムインデックスが利用できるというTIPSでした。

: ディープラーニングで「顔が似ているAKB48のメンバーを教えてくれるbot」を構築

概要こんにちは、システム開発部の中村です。今回は、Facebook Messenger APIを利用して、画像をアップロードすると、似ているAKB48のメンバーを教えてくれるbotを実装しました。尚、ディープラーニングやTensorFlowそのものの解説というより、「エンジンとしてディープ …

: Tableauを利用してMySQLとRedshiftのクロスDBジョインを実現する

はじめに RedshiftやTreasureDataなどのデータマート用のDBにはID単位の解析結果が格納され、ローカルのMySQLにはIDに紐づいた名称マスタが管理されている構成の場合、データマートのクロス集計結果に対してIDに紐づいた名称を付与したいことがあります。データマート用に用意したDB …

: fastavroとjqでAVRO形式のファイルからデータを取得しよう

AVRO形式のファイルを取り扱いたい AVROとはApacheプロジェクトのひとつとして開発されているデータ交換形式です。　コンパクトなバイナリで高速なシリアライズ・デシリアライズが行えるため、サーバーログなどに利用されています。弊社内での一部システムのログデータにも利用されているのですが、専用の …

: GoogleAppsScriptとTreasureData REST APIを使ってサーバレスにTwitterのデータを取得

またまたTreasureDataネタです。ただ、今回はクエリ系のネタではなく、GoogleAppsScriptとTreasureDataのREST APIを使ってTwitterのデータをTreasureDataに入れてみたので、その方法を紹介したいと思います。はじめにログデータだけではなく、公 …

: いま必要なのは「アナリティクスアプローチ」

こんにちは。ビッグデータ解析部のakiです。解析部で、Markezineでの連載をはじめましたのでご紹介です。いま必要なのは「アナリティクスアプローチ」、ビッグデータ活用の課題とこれから (http://markezine.jp/article/detail/21293) マーケターのかた向け …

: 巨大データベースのスケールアップと引越作業

はじめにビッグデータ解析部でオーディエンスデータ解析基盤の開発、運用を担当している Mike です。弊社ではインターネット広告配信ログをはじめとする「ビッグデータ」と呼ぶにふさわしいデータボリュームを扱うオーディエンスデータ解析基盤を構築しています。今秋、そのうちの１構成要素である、データサイズ …

: 気象予報士とビッグデータ解析の意外な関係

DACから気象予報士が誕生しましたビッグデータ解析部のMikeです。 2015年1月の気象予報士試験に合格し、めでたく4月からアドテク業界ただ一人（本当？）の気象予報士となりました。そんなわけで、今回は気象予報士とビッグデータ解析の関係についてお話したいと思います。なぜ気象予報士を目指したか …

: Treasure Dataで長期間の集計

プラットフォーム・ワン T氏です。プラットフォーム・ワンでは、DSPのMarketOneとSSPのYIELD ONE提供しています。 MarketOneやYIELD ONEのログを調査する場合にTreasure Dataを使うことがあります。Treasure Dataでは大量のデータに対してHive …

: PyStanによるはじめてのマルコフ連鎖モンテカルロ法

はじめにこんにちは。システム開発部の中村です。社内で行っている『データ解析のための統計モデリング入門』(所謂緑本)の輪読会に参加した所、大変わかりやすい本だったものの、Macユーザには悲しい事に実装サンプルがWinBUGSだったため、 9章の一般化線形モデルのベイズ推定によるアプローチをPyt …

: Treasure Dataの新機能(Data Tank)をAudienceOneのレポート機能で利用した話

Data Tankとは？ Treasure Dataの新機能でTreasure Dataのプラットフォーム上に構築されたデータマートです。 Tableau等のBIツールとの接続を想定されており、AWSでいうところのRedshift的なものだと考えるとわかりやすいかと。 Data TankはPostg …

PREV: [GoogleCloudPlatform]インタビュー記事が掲載されました！
NEXT: 第1回 DACライトニングトーク選手権開催！

Tom-N@ck	etrojan	skryo
taisuke-oomi	ryo	matsuari
忍ゑ	takusan	kazrin3
sae	jotaka	Rhiannon
tomoaki-ueda	yosuke-fukumoto	KTBT
hama	Mike	ダーハラ
yamaken	yuka	k-hal
鈴幸	kumata	SHIN-ICHI
shingo-tateishi	T氏	gicogico
pi★shi	OKAMOTO, Nau	ウッディー
shunpei-ishida	たにぐち	kaho-ikeda
waku	matsudasyo	クラヴマガ
Oyaman	Kiyoshi Ohno	aki

Treasure Dataで大規模なマスタデータを扱う際にはtimeカラムインデックスを活用しよう

関連記事