DWHをPureDataからIIASに移行した話

Database DWH

f:id:vasilyjp:20200616190117j:plain

こんにちは！　ZOZOテクノロジーズの中坊（e_tyubo）です。

概要

私が所属しているマーケティングオートメーション（以下MA）を担当するチームでは、ユーザ毎にパーソナライズされた情報をメールやアプリのPush通知で配信しています。その際に利用するZOZOTOWNやWEARのデータは我々が管理する専用のデータベースに集約されています。このデータベースには日々のユーザの行動ログが記録されるため、必然的にデータ量は大きいものになります。このような巨大なデータを管理することに特化したデータベースはデータウェアハウス（以下DWH）と呼ばれていて、実績の可視化やマーケティングに活用するための分析等の用途に使われます。

MAチームでは配信内容をユーザ単位で最適化するためにDWHを利用していて、最近まではIBM PureData System for Analytics（以下PureData）を利用していました。しかし、PureDataの保守が終了してしまうため別のデータベースに移行する必要がありました。我々は互換性を考慮し、後継機であるIBM Integrated Analytics System（以下IIAS）へ移行することにしました。

PureDataとIIASはどちらもIBM製品であるため高い互換性がありますが、一部の機能については挙動の違いもあり個別に対応が必要となりました。本記事では新しく導入したIIASの特徴と、移行の際にぶつかった問題とその解決方法について紹介します。

IIASの特徴

最初に、IIASがどのようなデータベースなのかを紹介します。

IIASはデータベースそのものを指すのではなく、サーバ筐体や周辺ツールを含めたパッケージ製品の名称です。公式ドキュメントに書かれている通り、Db2 Warehouseという製品を中核とし、様々な周辺ツールが提供されています。IIASはクラウドサービスではなく、計算機本体を購入し管理する必要がある、いわゆるオンプレミスなデータベースです。よく利用されるBigQueryの様なフルマネージドなDWHとは異なり、サーバ本体を自社で保有する必要があります。

Db2 Warehouse

以前まで利用していたPureDataはPostgreSQLベースでしたが、IIASは先程も述べた通り、Db2 Warehouseという製品を利用しています。Db2はIBMが開発したデータベースで、1983年にリリースされた歴史の長いRDBMSです。元々はWebアプリケーション等の裏側でリアルタイムな処理を主として利用されていたデータベースですが、改良が重ねられ、DWHとしての機能も果たすようになっていきました。

Db2 Warehouseもまた、データベースだけを指す言葉ではなく、データベースエンジンと周辺機能をDocker上で動作させるために必要な機能を提供する製品の名称です。公式ドキュメントの図にも書かれている通り、GUIでデータベースの操作ができるWebコンソールやログインユーザの管理用のLDAPが標準で搭載されており、セットアップ後は様々な機能が利用可能になります。特徴的な点としてDb2 Warehouseの機能はDocker Imageで提供されている点が挙げられます。無料版も存在していて、Docker Hub1か、IBM Cloud Container Registryから入手が可能です。開発用の環境を自前で構築する際には利用が可能ですので、導入前の検討材料や本番で実施しにくいテストの実行環境として活用ができます。

複数のノードで分散処理

IIASには物理サーバが複数台含まれており、それぞれのサーバを使って1つのデータベースを動かします。RDBMSではしばしばシャーディングと呼ばれる方法でデータを分割をして負荷分散させることがありますが、Db2 Warehouseは標準でそのような仕組みを備えています。

また、Db2 Warehouseでは物理的なサーバ単位だけでなく、サーバの中に論理ノードという単位でデータを分けて保持できます。これはあるサーバの中でさらに論理的にデータを分割し、保持する仕組みです。Db2 Warehouseではノード毎に保持しているデータを、ノード毎に処理することで効率的にSQLを実行する仕組みを備えています。この場合CPU、メモリ、ストレージは同一サーバ上のものを共有することになります。しかし、利用可能なCPUのコア数が論理ノード数に対して十分な数がある場合はノード単位で同時に処理をすることで処理効率の改善が期待できます。

下記のようなSELECTステートメントを例に考えてみましょう。

SELECT * FROM SampleTable

SampleTableというテーブルから全てのカラムをSELECTする単純なクエリです。1つのテーブルですが、Db2 Warehouseの仕組み上データは複数サーバに分散されているため、取得経路は下図のようになります。

分散している様子

この図は物理サーバが2台あり、その中でさらに論理ノードに分かれているケースを表しています。図のように全ての論理ノードからデータを集めて、全て取得し終わったら要求した全てのデータを返します。このようにDb2 Warehouseはそれぞれのノードで処理を並列実行することでクエリのパフォーマンスを効率化しています。

分散キー

先ほどデータが各ノードに分散配置されると書きましたが、分散させるには分ける基準となる情報が必要です。そのため、Db2 Warehouseには分散キーという属性をカラムに指定できます。

分散の方式にはハッシュ分散とランダム分散の2種類があり、分散キーの作成時にどちらを利用するか指定できます。ランダム分散は適切なキーが存在しない場合にDb2 Warehouseが自動で分散キーを決定する方式ですが、利用するケースがなかったため説明は割愛します。

ハッシュ分散は、カラムの値を用いて分散させる方式です。例えば会員情報を管理するためのMemberというテーブルがあった場合、分散キーは下記のように指定できます。memberIdはユニークな値のみ保持することとします。

CREATE TABLE Member (
    memberId INTEGER,
    name VARCHAR(50)
)
DISTRIBUTE BY HASH(memberId)
ORGANIZE BY COLUMN

ハッシュ分散は、指定したキーの値と格納先ノードを対応させるマップを作成し、それに沿ってデータを配置します。下記の画像はノードが4つ存在する場合に作成されるマップのイメージ図です。

ハッシュ分散

値の重複がある場合、データは同じノードに格納されるため、均一化のためにはカーディナリティ（値のばらつき具合）が高い列を選択する必要があります。今回のケースではmemberIdがユニークであるため、均一にデータが配置されます。

次に分散キーの効果的な利用方法について説明します。並列処理を効率的に実行するためには各ノードで検索条件にマッチするデータ量を均一化することが必要です。もし特定ノードにデータが偏っていた場合、そのノードで実行されるデータ取得処理が終わるまで全体の処理を終えることができません。

データスキッピング

JOINを例に考えてみます。例えば、下記のような会員のお気に入り商品の情報を管理するFavoriteProductテーブルがあるとします。

CREATE TABLE FavoriteProduct (
    memberId INTEGER,
    productId INTEGER
)
DISTRIBUTE BY HASH(memberId, productId)
ORGANIZE BY COLUMN

下記のクエリでお気に入り情報に紐づく会員情報を取得するケースを考えましょう。

SELECT
    productId
    ,name
FROM
    FavoriteProduct 
    INNER JOIN Member ON Member.memberId = FavoriteProduct.memberId

この場合FavoriteProductが保持しているmemberIdを使ってMemberテーブルを検索するため、分散キーを利用することになります。MemberテーブルのmemberIdはユニークなキーであり、均一に分散されているため十分効率的にSELECTを実行できると考えられます。

このように、検索条件に対していかにデータを均等に分散させるかがパフォーマンスを考慮する上で重要になります。

つまり分散キーを選択する際には、下記の条件を満たしているケースが望ましいです。

  • カーディナリティが高い列を指定する
  • 検索条件に指定される列を指定する

必ずしもこの条件を満たす必要は無いですが、分散キーを選択する際の基準として考慮しています。

列指向と行指向

Db2の特徴として列指向と行指向を両方サポートしているという点が挙げられます。行指向はレコード単位でデータを保持し、列指向はカラム毎にデータを保持する方式の事を言います。

行指向と列指向

MySQLやPostgreSQL、SQL Server等のRDBMSはテーブルの中から特定のレコードを取得する事に重きを置いているため、行毎にデータを保持しておくことが効果的です。しかし、Db2 WarehouseのようなDWHは大量のデータを保持する事を想定しており、列単位にデータを保持することで大量データの中から特定の列のみを使った処理を効率的に実行できます。

Db2 Warehouseは列単位でデータを保持することにより、特に下記の利点があるようです。

  • 不要列を読み込む必要がないため、メモリを効率的に利用できる
  • 列の中に同じデータが含まれているケースが多いため、データ圧縮率が高い

IIASで利用する際にはデフォルトで列指向なテーブルが作成されますが、明示的に行指向テーブルを作成することも可能です。

CREATE TABLE Member (
    memberId INTEGER,
    name VARCHAR(50)
)
DISTRIBUTE BY HASH(memberId)
ORGANIZE BY ROW

上記のようにORGANIZE BY ROWを指定すると行指向テーブルが作成されます。DWHの用途を考えると列指向なテーブルを使うことの方が多いと思いますが、必要に応じて使い分けることができます。

データスキッピング

一般的なRDBMSでは検索を効率的に実施するためにindexを利用しますが、列指向で保持しているデータについては同じ方式では有効に検索できません。そこでindexではなくデータスキッピングという仕組みを用いてカラムの中のデータ検索を効率化しています。

データスキッピングとは、一定のデータ件数毎に各列が持つデータの最大値と最小値をメタデータとして保持し、レコードが存在する範囲を絞り込みやすくする機能です。

例えばMemberテーブルに登録時刻を保持するregistDtというカラムがある場合を考えます。

CREATE TABLE Member (
    memberId INTEGER,
    name VARCHAR(50),
    registDt DATETIME
)
DISTRIBUTE BY HASH(memberId)
ORGANIZE BY COLUMN

下記のWHERE句を用いて2020年4月に登録した会員をSELECTします。

WHERE registDt >= '2020-04-01 00:00:00' AND registDt < '2020-05-01 00:00:00'

この時データスキッピングの機能により、対象レコードを効果的に絞り込むことが可能です。

データスキッピング

上図のように、メタデータを使って2020年4月1日より古いデータと2020年5月1日より新しいデータが存在する範囲を不要だと判断できるため、処理が効率化されます。

Webコンソール

IIASをセットアップすると、Db2 Warehouse専用のWebコンソールが利用可能になります。様々な機能が利用可能ですが、特に頻繁に利用する機能は下記の通りです。

  • クエリの実行
  • テーブルやViewの定義の確認
  • 実行中クエリの確認（実行計画も参照可能）
  • ユーザの管理
  • ディスク、CPU、メモリの利用状況の確認

Mac用に標準ツールが提供されていないこともあり、Webコンソールからクエリを実行することが多いです。

PureDataからIIASに移行する際の注意点

次はPureDataからIIASへ移行する際に課題となったポイントをまとめます。

冒頭でも述べた通り、PureDataとDb2 Warehouseは高い互換性がありますが、一部挙動に相違が見られます。ここでは移行に当たって書き換えや、対応が必要となった機能の一部を紹介します。

FROM句は必須

PureDataで現在時刻を取得する時は、FROM句を指定せず下記のクエリで実現可能です。

SELECT CURRENT_DATE

しかし、Db2 Warehouseでは明示的に指定が必須です。

SELECT CURRENT_DATE FROM SYSIBM.SYSDUMMY1

そのため単純に値の中身を確認するような場合には、上記のように、SYSIBMスキーマに用意されたダミーテーブルを利用します。

もしくは、VALUESを使うことでも同様の結果が得られます。

VALUES(CURRENT_DATE)

LENGTHとCHARACTER_LENGTH

PureDataでは文字列に対してLENGTH関数を使うと文字数が返ってきましたが、Db2 Warehouseでは挙動が異なります。Db2 Warehouse内で扱う文字列の文字コードがUTF-8となっている前提で考えます。

SELECT LENGTH('Hello') FROM SYSIBM.SYSDUMMY1
SELECT LENGTH('こんにちは') FROM SYSIBM.SYSDUMMY1

どちらも文字数で見ると5ですが、結果は下記の通りです。

5
15

これはDb2 WarehouseがLENGTHによって文字数ではなく、文字列の合計バイト数を返していることが原因です。UTF-8の文字列ではアルファベットは1バイト、ひらがなは3バイトで表現されます。

単純な文字数をカウントしたい時は、CHARACTER_LENGTHを使って書くことで期待した結果を得ることができます。

SELECT CHARACTER_LENGTH('Hello') FROM SYSIBM.SYSDUMMY1
SELECT CHARACTER_LENGTH('こんにちは') FROM SYSIBM.SYSDUMMY1

ウィンドウ関数の中でrandomが使えない

我々のチームで管理しているクエリの中にはウィンドウ関数を利用しているものが多数ありますが、Db2 Warehouseにはその中でrandom関数を利用できないという制約があります。例えば、Memberテーブルが住んでいる都道府県のidを保持しているとしましょう。

CREATE TABLE Member (
    memberId INTEGER,
    prefectureId INTEGER,
    name VARCHAR(50)
)
DISTRIBUTE BY HASH(memberId)
ORGANIZE BY COLUMN

この時、下記のように都道府県ごとにランダムな値を割り振るクエリは利用できません。

SELECT
    memberId
    ,RANDOM() OVER(PARTITION BY prefectureId)
FROM
    Member

random関数を利用する場合はウィンドウ関数の中での利用を回避する必要があります。

下記のようにサブクエリで割り振ったランダムな値を基準にROW_NUMBERを振り直すことで、無作為に選ばれた連番をSELECTすることが可能です。

SELECT
    memberId
    ,ROW_NUMBER() OVER(PARTITION BY prefectureId ORDER BY randomNum) AS randomRowNum
FROM
    (
    SELECT
        memberId
        ,prefectureId
        ,RANDOM() AS randomNum
    FROM
        Member
    ) AS A

NULLの順序が異なる

NULLを含むカラムを並び替える際に、NULLが先頭に来るか末尾に来るかはデータベースによって異なります。PureDataとIIASの並び順は表の通りです。

DB ASC時のNULLの順序 DESC時のNULLの順序
PureData 先頭 末尾
IIAS 末尾 先頭

例えば何かしらの値でスコアリングして、大きい順に並べた結果を先頭から特定件数を抽出するようなクエリでNULLがヒットするようになってしまう可能性があります。ORDER BY ASCのケースで対応が必要な箇所はありませんでしたが、上記のようなケースが存在する場合はORDER BY DESCを利用している箇所で修正が必要です。

この件の対処法をサポートに問い合わせたところ、システム全体でNULLの並び順を指定する設定はないとのことでした。そのため、ORDER BY DESCを使っている箇所で NULLS LAST を指定することで対処しました。

ORDER BY DESC NULLS LAST

上記のように指定することで対処が可能です。対応としてはクエリ自体をNULLを利用しない方式に組み替えることも考えられますが、影響範囲と改修コストの兼ね合いで一括置換できる方式を選択しました。

SORTHEAP

DWHは巨大なデータを扱うため、メモリの利用量も多くなります。特にSORTHEAPと呼ばれるメモリは、Db2 Warehouseがクエリを実行する際にJOINやソートの時に一時的にデータを格納するために使われており、パフォーマンスに大きく関わります。PostgreSQLで言う所のwork_memのようなものです。

この値を超えるデータ量を扱うと、メモリに収まらないためディスク書き込みによるパフォーマンス低下を招く可能性があります。また、オプティマイザがメモリ消費の少ない実行計画を選択することで実行時間が長くなる可能性もあります。処理に十分なメモリが割り当てられていないとパフォーマンスが顕著に低下することがあるためSORTHEAPの設定値は調整を検討する価値があります。

ただし、SORTHEAPの値はあくまで1つのステートメント単位で利用できるメモリ量であり、同時接続数が増えれば増えるほど全体として利用するメモリの量は増えてしまいます。そのためDb2 Warehouseではシステム全体でSORTHEAPとして利用できるメモリの総量をSHEAPTHRES_SHRによって規定しています。全ての実行中クエリがSORTHEAPを最大まで利用している場合、理論的に同時接続できる最大数はSHEAPTHRES_SHR/SORTHEAPによって決まります。SHEAPTHRES_SHRを超えてメモリを利用することはできないため、SORTHEAPに割り当てるメモリの量には注意が必要です。

まとめ

本記事ではIIASの特徴とPureDataからIIASへ移行する際に考慮すべきポイントをまとめました。

MAチームではMA基盤上のアプリケーションの開発・運用だけでなく、データ連携の仕組みの開発・運用も行なっており、ビッグデータを活用したデータ基盤の改善に取り組んでいます。目立たない分野ですが非常にサービスへの影響は大きく、挑戦のしがいがあるチームです。

ZOZOテクノロジーズでは、一緒にサービスを作り上げてくれる方を募集中です。ご興味のある方は、以下のリンクからぜひご応募ください。

tech.zozo.com

  1. Docker Hubで提供されるDocker Imageについては2020年3月31日以降はメンテナンスが終了しており、非推奨となりました。↩

カテゴリー