Treasure Dataで大規模なマスタデータを扱う際にはtimeカラムインデックスを活用しよう
DACではTreasure Dataを利用して各種データの蓄積や集計を行っています。Treasure Dataは時系列のデータを扱うのに特にすぐれたアーキテクチャなのですが、セグメントIDとユーザーIDの組み合わせといった大量のマスタデータを利用した計算にも利用することもできます。そのような場合にtimeカラムインデックスを活用してマスタデータを高速に抽出する方法について解説します。
Treasure Dataでは時系列のデータを効率よくあつかうため、timeカラムが固定インデックスとなっており、3600秒(1時間)ごとのパーティショニングに分けてインポートされます。この性質を利用して、時系列で格納する必要のないデータについては「セグメントID * 3600」「カテゴリID * 3600」のようにマスタのキー値を元に作成した時間を設定することで値が高速に取得できるようになります。
検証のためにtimeカラムをセグメントIDとして設定したテーブルを作成します。テーブル作成の元ネタはセグメントとユーザーのM:N対応を縦持ちで持つテストデータで、総行数は約8億行あります。
1 2 3 4 5 6 7 8 |
-- timeカラムをセグメントID * 3600の設定 INSERT INTO TABLE tmp_segment_time_test SELECT segment_id * 3600 time, segment_id, tuuid FROM segment_data |
指定したふたつのセグメント同士の重複ユーザー数を抽出してみましょう。
1 2 3 4 5 6 7 |
-- 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秒)
1 2 3 4 5 6 7 |
-- 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を指定してみます。
1 2 3 4 5 6 7 |
-- 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秒)
1 2 3 4 5 6 7 |
-- 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でした。

関連記事
-
-
【超入門】Hivemallで機械学習 〜Treasure Dataでロジスティック回帰編〜
こんにちは。俺やで。 ビッグデータとかデータサイエンティストとかいう言葉が未だブームですね。 (「データサイエンティスト」は下火か。) ビッグデータ扱えるエンジニアも、 統計解析ができるアナリストも、 どっちもできるスーパーマンも世の中にはたくさんいますが、 ビッグデータも統計解析も扱えるインフラは …
-
-
HivemallでMinhash!〜似てる記事を探し出そう。〜
こんにちは。俺やで。 前回の投稿に続き(間が空きましたが)、 ビッグデータに対応したHiveで使える機械学習ライブラリ、 「Hivemall」の使い方、第2弾となります。 今回はMinhashという手法について書きたいと思います。 ※前回 【超入門】Hivemallで機械学習 〜Treasure D …
-
-
最強のSQLクライアント(GUIツール)「TeamSQL」を使ってみた!
はじめに みなさんこんにちは、プロダクト開発本部の亀梨です。 普段はXmediaOneというメディアプランニング・広告運用管理・トラッキング・マーケティング分析を行う 統合プラットフォームの開発を担当しています。 エンジニアの皆さん、SQLクライアント(GUIツール)って何使ってます? わたくしはこ …
-
-
Treasure Dataの新機能(Data Tank)をAudienceOneのレポート機能で利用した話
Data Tankとは? Treasure Dataの新機能でTreasure Dataのプラットフォーム上に構築されたデータマートです。 Tableau等のBIツールとの接続を想定されており、AWSでいうところのRedshift的なものだと考えるとわかりやすいかと。 Data TankはPostg …
-
-
fastavroとjqでAVRO形式のファイルからデータを取得しよう
AVRO形式のファイルを取り扱いたい AVROとはApacheプロジェクトのひとつとして開発されているデータ交換形式です。 コンパクトなバイナリで高速なシリアライズ・デシリアライズが行えるため、サーバーログなどに利用されています。 弊社内での一部システムのログデータにも利用されているのですが、専用の …
-
-
ディープラーニングで「顔が似ているAKB48のメンバーを教えてくれるbot」を構築
概要 こんにちは、システム開発部の中村です。 今回は、Facebook Messenger APIを利用して、 画像をアップロードすると、似ているAKB48のメンバーを教えてくれるbotを実装しました。 尚、ディープラーニングやTensorFlowそのものの解説というより、 「エンジンとしてディープ …
-
-
GoogleAppsScriptとTreasureData REST APIを使ってサーバレスにTwitterのデータを取得
またまたTreasureDataネタです。 ただ、今回はクエリ系のネタではなく、GoogleAppsScriptとTreasureDataのREST APIを使ってTwitterのデータをTreasureDataに入れてみたので、その方法を紹介したいと思います。 はじめに ログデータだけではなく、公 …
-
-
Google BigQuery / Tableauを使ってみた
TableauからGoogle BigQueryへ接続してみました。 弊社で利用しているTreasureDataからデータ出力してBigQueryへロード、Tableauから接続まで実際に行った手順について記載します。 TreasureDataからAmazonS3へデータ出力 まず、データが蓄積され …
-
-
PyStanによるはじめてのマルコフ連鎖モンテカルロ法
はじめに こんにちは。システム開発部の中村です。 社内で行っている『データ解析のための統計モデリング入門』(所謂緑本)の輪読会に参加した所、 大変わかりやすい本だったものの、Macユーザには悲しい事に実装サンプルがWinBUGSだったため、 9章の一般化線形モデルのベイズ推定によるアプローチをPyt …
-
-
【Hivemall入門】RandomForestで毒キノコ推定モデルを作る
こんにちは。俺やで。 今回も前回から間が空いてしましたが、ビッグデータに対応したHiveで使える機械学習ライブラリ、 Hivemallの使い方について、書かせていただければと思います。 なお今回はQiitaのTreasure Data / Advent Calender 2015の12/3日分として …