TPC-Hを使ってテスト環境を作成する（Synapse SQLプール）

Synapse SQLプールの検証用にテストデータが欲しかったので、TPC-Hを使って環境を構築しました。

TPC-Hとは

TPC-H は RDBMS ベンチマーク仕様の一つで、売上分析データウェアハウスのデータモデリングを使い、意思決定支援システムの性能を測定、計測するもので、クエリは 22 種類定義されています。
TPC-H の詳細に関しては、下記 URL を参照してください。
http://www.tpc.org/tpch/

また、以下のようなデータモデリングを使用しています。

①TPC-Hのダウンロードとdbgen、qgenのセットアップ

今回環境を構築した環境はCentOS8です。

まずは必要なパッケージとインストールします。

sudo dnf install make
sudo dnf install gcc

先ほど紹介したURLよりTPC-H Toolのダウンロードを行います。
ダウンロード後unzipコマンド等で解凍し、作成されたディレクトリ内のdbgenというディレクトリへ移動します。
※私の場合TPC-Hのバージョンが2.18.0でした。

cd 2.18.0_rc2/dbgen/

dbgen内にmakefile.suiteが存在します。
今回はSynapse SQLプール用に作成したいので、SQL SERVER用に当該ファイルの以下の項目を書き換えます。(Synapse SQLプールはSQL Serverベースのクラウド型DataWarehouseの為SQL Serverを指定します。)

CC      = gcc
DATABASE= SQLSERVER
MACHINE = LINUX
WORKLOAD = TPCH
LDFLAGS = -O3

書き換え完了後以下のコマンドでdbgenとqgenをセットアップします。

make -f makefile.suite

ディレクトリ内に、dbgenとqgenがあれば成功です。

②テーブルのDDL

テーブルのDDLはdbgenディレクトリ内のdss.dllファイルの中に記載されています。
このDDLを使えばそのままSynapse SQLプールにテーブルの作成が可能です。

また、dbgenディレクトリ内のdss.riにはPKや外部キーに関するDDLが記載されています。一方Synapse SQLプールは外部キーが利用できない為、実行は出来ません。
以下の通りのコマンドでPKのみ作成は可能です。

-- For table REGION
ALTER TABLE REGION
ADD PRIMARY KEY NONCLUSTERED (R_REGIONKEY) NOT ENFORCED;

-- For table NATION
ALTER TABLE NATION
ADD PRIMARY KEY NONCLUSTERED (N_NATIONKEY) NOT ENFORCED;

-- For table PART
ALTER TABLE PART
ADD PRIMARY KEY NONCLUSTERED (P_PARTKEY) NOT ENFORCED;

-- For table SUPPLIER
ALTER TABLE SUPPLIER
ADD PRIMARY KEY NONCLUSTERED (S_SUPPKEY) NOT ENFORCED;

-- For table PARTSUPP
ALTER TABLE PARTSUPP
ADD PRIMARY KEY NONCLUSTERED (PS_PARTKEY,PS_SUPPKEY) NOT ENFORCED;

-- For table CUSTOMER
ALTER TABLE CUSTOMER
ADD PRIMARY KEY NONCLUSTERED (C_CUSTKEY) NOT ENFORCED;

-- For table LINEITEM
ALTER TABLE LINEITEM
ADD PRIMARY KEY NONCLUSTERED (L_ORDERKEY,L_LINENUMBER) NOT ENFORCED;

-- For table ORDERS
ALTER TABLE ORDERS
ADD PRIMARY KEY NONCLUSTERED (O_ORDERKEY) NOT ENFORCED;

③データの作成

先ほどmakeしたdbgenを利用してデータの作成が可能です。
以下のコマンドでデータの作成を行います。

./dbgen -s 1

上記は1GBのデータを作成する例です。TPC-Hは1スケールで1GB程度のデータの作成ができます。-sオプションでスケールを指定します。例えば1000GB程度のデータを出力するためには./dbgen -s 1000と実行します。
実行が成功すると以下のファイルが出力されます。

• supplier.tbl
• region.tbl
• part.tbl
• partsupp.tbl
• orders.tbl
• nation.tbl
• lineitem.tbl
• customer.tbl

また、データファイルを分割して作成したい場合以下の要領で分割して作成することができます。
この例では分割された全ファイルの合計で10GB(スケール10)のデータが出力されます。

for i in $(seq 1 10);do ./dbgen -v -f -s 10 -C 10 -S $i;done

ちなみにdbgen -hにてdbgenのヘルプが確認出来ます。

./dbgen -h

④クエリの作成

TPC-Hは22本のクエリが定義されていますが、このクエリを先ほどmakeしたqgenにて作成可能です。
dbgenディレクトリ内にqueriesディレクトリが存在しますが、この中のクエリがベースとなり22本のクエリが作成されます。

export DSS_QUERY=＜dbgen/queriesのディレクトリをフルパスで指定＞
for i in $(seq 1 22); do ./qgen -s 1 $i > $i.sql; done

1.sql~22.sqlまで出力されれば成功です。
クエリの作成にもスケールの指定が必要です。-sオプションにてクエリを実行するスケールを指定する必要があります。（例ではスケール1=1GBの例です。）

一方、一部SQLはそのままの状態では実行が出来ないので手動で変更してやる必要があるようです。
手動の変更が面倒な場合、以下のURLにSQL Serverで実行可能なSQLが用意されているので、これを流用するのもいいかもしれません。
https://www.sqlserver-dba.com/2011/09/this-is-a-followup-on-my-earlier-post-of-sql-server-test-data-generation-testing-tools-i-had-some-requests-for-my-set-up-pr.html

終わりに

また、次の機会に作成したデータをSynapse SQLプールに投入して、いろいろ検証したいと思います。