021234567891011121314151617181920212223242526272829303104

スポンサーサイト

上記の広告は1ヶ月以上更新のないブログに表示されています。
新しい記事を書く事で広告が消せます。
【 --/--/-- (--) 】 スポンサー広告 | TB(-) | CM(-)

PostgreSQLへのTPC-Hデータのロード

ログインロール(ユーザー)、テーブル空間、データベースと作って、ようやくテーブルが作れるようになったので、TPC-Hのテーブルを作った。
まだ空のままなので、ロードしてみる。

外部テーブルという手法で、実際にはOSのファイルシステム上にあるただのファイルをテーブルとして参照できるようにする手法もあるようだが、実際にロードすることとする。


SQLのcopyコマンドでテーブルへのテキストファイルのロード



PostgreSQLのテーブルへテキストファイルをロードするには、SQLのcopyコマンドを使うのが一般的らしい。

tpch=> copy nation from '/tmp/nation.tbl' delimiter '|';
ERROR: must be superuser to COPY to or from a file
HINT: Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.

ところが、copyコマンドを使ってファイルをロードするのは、スーパーユーザー権限をもったユーザー(postgresとか)でないといけないらしい。なんでそんな面倒な事になってるかはしらないけど...テーブルへのデータのロードなんて必須作業でしょ。

postgresユーザーでログインし直したけど、いろいろ試してもtpchデータベース下を参照できなかった。スキーマを作ってその下にテーブルを作ってたら参照できたのかな?

psqlの\copyコマンドなら大丈夫らしいので、それでやってみる。


psqlの\copyコマンドでテーブルへのテキストファイルのロード



psqlを使ってtpchユーザーでログインし直し、\copyコマンドを使ってみる。

\copy nation from '/tmp/nation.tbl'
ERROR: extra data after last expected column
CONTEXT: COPY nation, line 1: "0|ALGERIA|0| haggle. carefully final deposits detect slyly agai|"

権限の問題はクリアできてるようだが、データファイルの書式がまずいらしい。レコード末尾の区切り文字が悪さして、ファイルのフィールドがロード先テーブルに対し一つ余分にあると思われてるようだ。
たしかに区切り文字の使い方としては間違ってるなぁ。

sedで末尾のパイプを削除してしまおう。

[root@localhost tmp]# sed -i 's/\(.*\)|$/\1/' nation.tbl

てな感じで、-iオプションを指定して直接ファイルを編集するかたちで、各行の末尾のパイプ文字を削除してしまう。

そうしたら、やっとロードできるようになった。

[root@localhost tmp]# wc -l /tmp/nation.tbl
25 /tmp/nation.tbl

[root@localhost tmp]# psql -U tpch
Password for user tpch:
psql (8.4.18, server 9.3.2)
WARNING: psql version 8.4, server version 9.3.
Some psql features might not work.
Type "help" for help.

tpch=> \copy nation from '/tmp/nation.tbl' delimiter '|'
tpch=> select count(*) from nation;
count
-------
25
(1 row)

小さなファイルnationの例だけど、全部のファイルをロード完了できた。



スポンサーサイト
【 2014/03/19 (Wed) 】 データベース PostgreSQL | TB(0) | CM(0)

PostgreSQLへのTPC-Hデータのロード... の準備

TPC-Hのロード用データファイルは作成できた。次は実際にテーブルを作ってロードしよう。
まずはPostgreSQLへ作ってみる。
テーブルを作る前に、データベース環境定義的な目的のオブジェクトをたくさんつくらないといけない。
・ユーザー、ロール
・テーブル空間
・データベース
・スキーマ

仕事で使うようなデータベースの場合は、この辺の定義がかなり重要なのだろうけど、とりあえず作ってみるチュートリアルでは、まずは動く事を優先して、細かい設定は適当に...


PostgreSQLへのユーザー、ロールの作成



ログイン用のデータベースユーザーを作成するには、CREATE ROLEコマンドを使用する。PostgreSQLでは、ログインユーザーはロールのひとつという扱いのようだ。ロールは権限の集合に対する別名かと思ってたけど、PostgreSQLでの扱いは違うようだ。
というか、ロールにパスワードをつけてログイン可能にしたのがユーザー(=ログインロール)という事だろうか。

pgAdminを使ってGUIで操作する場合は、オブジェクトブラウザのログインロールのツリーから右クリックで「新しいログインロール」を選択すると、ユーザー名、パスワードなどをきいてくる。

このpgAdminのいいところは、GUI上で作成したものに対しても、該当するSQLコマンドを表示してくれるということ。(パスワードも人間の眼には判読不可能な形にしてくれる。)

CREATE ROLE postgres LOGIN
ENCRYPTED PASSWORD 'md53175bce1d3201d16594cebf9d7eb3f9d'
SUPERUSER INHERIT CREATEDB CREATEROLE REPLICATION;



PostgreSQLへのテーブル空間の作成



テーブルを作成する場所として、テーブル空間というものを指定する必要がある。これはデータベースをインストールしているOSから参照可能なディレクトリを指定すればよいようだ。

仮想マシンに追加マウントした仮想ディスク2本、/data/disk1と/data/disk2を、それぞれts_dataspace1とts_dataspace2として登録する。
pgAdminで見ると登録済みのテーブル空間がpg_で始まってたので、同じようにpg_で始まる名前をつけようと思ったら怒られた。
'The prefix "pg_" is reserved for system tablespaces.' というメッセージが出た。名前付けルールをチェックするとは。pgsqlでもpgAdminでも同じエラーが出せるので、データベースがチェックしてるんだろう。


CREATE TABLESPACE ts_dataspace1
OWNER postgres
LOCATION '/data/disk1';
CREATE TABLESPACE ts_dataspace2
OWNER postgres
LOCATION '/data/disk2';



PostgreSQLへのデータベースの作成



さきほど作成したテーブル空間を使い、データベースを作成する。使用する文字コード、ロケール等が指定できる。必須かどうかわからないけど、いちおう指定しておいた。
データベースとロールは同じ名前である必要はないだろうが、今回は同じにしておいた。


CREATE DATABASE tpch
WITH OWNER = tpch
ENCODING = 'UTF8'
TABLESPACE = ts_dataspace1
LC_COLLATE = 'en_US.UTF-8'
LC_CTYPE = 'en_US.UTF-8'
CONNECTION LIMIT = -1;



PostgreSQLへのスキーマの作成



スキーマというものを作成したデータベースに対し作成してもよかったのだが、デフォルトで同データベースのpublicスキーマが使えるので、それでよしとする。

この辺のデータベース環境定義的なオブジェクト、つまりユーザー・ロール・データベース・スキーマなどについては、まずはテーブルにデータをロードしたいという事であまり調べずに作ったので、あらためて文書を読み直すか。


PostgreSQLへのテーブルの作成



ようやくテーブルが作成できる。
pgAdmin使ってもよいのだが、こういうツールはカラムの定義が面倒で、キーボードとマウスの間を手が行ったり来たりして非常に時間がかかる。
せっかくDDL作ってあるのだから、psqlから直接流して作成することにする。
簡単にできあがった。

下のログはテーブルひとつ分についてのみ。

tpch=> create table SUPPLIER (
tpch(> S_SUPPKEY integer,
tpch(> S_NAME character(25),
tpch(> S_ADDRESS varchar(40),
tpch(> S_NATIONKEY integer,
tpch(> S_PHONE character(15),
tpch(> S_ACCTBAL numeric (20,2),
tpch(> S_COMMENT varchar(101),
tpch(> primary key (S_SUPPKEY)
tpch(> )
tpch-> ;
CREATE TABLE
tpch=>



 
【 2014/03/17 (Mon) 】 データベース PostgreSQL | TB(0) | CM(0)

TPC-H用テーブル(基礎編)

TPC-Hのデータファイルはできあがったので、ロードする先のテーブルを作成する。

ダウンロードしてきたテスト仕様書に、データモデルの図とカラム一覧が載っているので、それを参考に作る。


TPC-Hのデータモデル



tpch_datamodel.png

データモデル図を見ると、テーブルの数は少なめ。そこまで大変でなさそう。たった8個。カラムの数もそんなに多くない。

ただ、カラムの名前がいまいちダサいなぁ...
同じものをあらわすカラムが異なるテーブル間で同じ名前にならないよう、テーブル名の略称を冒頭につけてる。
たとえば、国をあらわすNATIONテーブルのキーNATIONKEYは、それを保持するテーブルNATION, SUPPLIER, CUSTOMERでそれぞれN_NATIONKEY, S_NATIONKEY, C_NATIONKEYという名前がつけられてる。

一部BIツールなどで、選択した複数のテーブルで同名のカラムがあったら自動的にジョイン対象としてくれるという機能があったりするけど、そういうのを使わせない方針なのだろうか。
TPC委員会がテスト対象としてる内容について、どうせそれで性能が変わるわけじゃあるまいし、SQL書く手間もコスト的に変わるほどとも思えない。


TPC-Hのテーブル定義(ベース)



それはさておいて、TPC-Hのテーブルのベースは下記のとおりかと。
データベースそれぞれのカスタマイズ・チューニングについてはこれをベースに発展させていくこととして、まずはこれなら対象の3データベースすべてで問題なく作成できるだろう。

create table CUSTOMER (
C_CUSTKEY integer,
C_NAME varchar(25),
C_ADDRESS varchar(40),
C_NATIONKEY integer,
C_PHONE character(15),
C_ACCTBAL numeric (20,2),
C_MKTSEGMENT character(10),
C_COMMENT varchar(117),
primary key (C_CUSTKEY)
)
;

create table LINEITEM (
L_ORDERKEY integer,
L_PARTKEY integer,
L_SUPPKEY integer,
L_LINENUMBER integer,
L_QUANTITY numeric (20,2),
L_EXTENDEDPRICE numeric (20,2),
L_DISCOUNT numeric (3,2),
L_TAX numeric (3,2),
L_RETURNFLAG character(1),
L_LINESTATUS character(1),
L_SHIPDATE date,
L_COMMITDATE date,
L_RECEIPTDATE date,
L_SHIPINSTRUCT character(25),
L_SHIPMODE character(10),
L_COMMENT varchar(44),
primary key (L_ORDERKEY, L_LINENUMBER)
)
;

create table NATION (
N_NATIONKEY integer,
N_NAME character(25),
N_REGIONKEY integer,
N_COMMENT varchar(152),
primary key (N_NATIONKEY)
)
;

create table ORDERS (
O_ORDERKEY integer,
O_CUSTKEY integer,
O_ORDERSTATUS character(1),
O_TOTALPRICE numeric (20,2),
O_ORDERDATE date,
O_ORDERPRIORITY character(15),
O_CLERK character(15),
O_SHIPPRIORITY integer,
O_COMMENT varchar(79),
primary key(O_ORDERKEY)
)
;

create table PART (
P_PARTKEY integer,
P_NAME varchar(55),
P_MFGR character(25),
P_BRAND character(10),
P_TYPE varchar(25),
P_SIZE integer,
P_CONTAINER character(10),
P_RETAILPRICE numeric (20,2),
P_COMMENT varchar(23),
primary key (P_PARTKEY)
)
;

create table PARTSUPP (
PS_PARTKEY integer,
PS_SUPPKEY integer,
PS_AVAILQTY integer,
PS_SUPPLYCOST numeric (20,2),
PS_COMMENT varchar(199),
primary key (PS_PARTKEY, PS_SUPPKEY)
)
;

create table REGION (
R_REGIONKEY integer,
R_NAME character(25),
R_COMMENT varchar(152),
primary key (R_REGIONKEY)
)
;

create table SUPPLIER (
S_SUPPKEY integer,
S_NAME character(25),
S_ADDRESS varchar(40),
S_NATIONKEY integer,
S_PHONE character(15),
S_ACCTBAL numeric (20,2),
S_COMMENT varchar(101),
primary key (S_SUPPKEY)
)
;


 
【 2014/03/15 (Sat) 】 データベース | TB(0) | CM(0)

チュートリアル的なデータベースの用意(tpc-h)

TPC-Hのテスト環境構築ツールを使って、チュートリアル用のデータベースをPostgreSQL, Oracle Express, Teradata Expressに作る。


TPC-Hに必要なファイルのダウンロード



テスト仕様書とデータ・クエリー生成プログラムを、サイトの右にあるリンクからダウンロード。
特に何の制約もなくダウンロードできるけど、データ・クエリー生成プログラムのZIPファイルが23MBもある。ちなみに解凍したら60MBを超える。w

テスト仕様書はたった2MBなんだけどね。


TPC-Hのデータ生成プログラム、datagenのビルド



ダウンロードしたZIPファイルを解凍すると、Cのソース、各種シェルスクリプト、テキストファイルなどが出てくる。
データ生成プログラムをビルドするには、dbgenディレクトリでmakeする。

データベースを稼働させてる仮想マシンLinuxは最小構成として作ったので、別のcygwinが動いてる64-bit Windows 環境を使ってビルドすることにする。

makefileはmakefile.suiteという名前なので、makefileって名前でコピーするかmake -f makefile.suiteとして実行しないといけない。
ついでにいうと、makefile.suiteを編集して、makefileをいじった点についてまとめると、

CC = gcc #最初は宣言だけで何も書かれてない。だったら宣言しなきゃデフォルトの使えるのにw
DATABASE= ORACLE #コメント中の候補から選ぶ。直接ロードでなくテキストデータ作るのなら何でもいい。
MACHINE=LINUX #CYGWINはないのね...LINUXと書いてビルドできたから別にいいけど。
WORKLOAD=TPCH #選択肢これしかないなら最初から書いとけよ
CGLAGS = -O3 … #O3最適化オプションを追加
LDFLAGS = -O3 #最適化オプションが最適化レベル1なので、もっと上げる

CFLAGS変数のO3最適化オプションは、必須ではないが入れた方が確実にいい。過去に同僚が使ってたのを見たとき、最適化オプションのありなしで速度がそれこそ何十倍という規模で違った記憶がある。


dbgenを実行してデータファイルの作成



dbgenプログラムがビルドできたら、コマンドラインから実行してロード用データを生成する。
nationなどのマスターデータは内容が変わらないけど、ordersなどのデータは実行時に与えるパラメータによって件数を変えられる。

dbgen -s n とやると、n GBのソースファイルを生成する形になる。
今回は、トータルで数GBしか領域のないデータベースに対しロードするので、1GBにする。


$ ./dbgen -s 1 -v
TPC-H Population Generator (Version 2.16.1)
Copyright Transaction Processing Performance Council 1994 - 2010
Generating data for suppliers table/
Preloading text ... 100%
done.
Generating data for customers tabledone.
Generating data for orders/lineitem tablesdone.
Generating data for part/partsupplier tablesdone.
Generating data for nation tabledone.
Generating data for region tabledone.

$ ls -l *.tbl
-rw-r--r--+ 1 xxxxxx Domain Users 24346144 Mar 8 10:43 customer.tbl
-rw-r--r--+ 1 xxxxxx Domain Users 759863287 Mar 8 10:43 lineitem.tbl
-rw-r--r--+ 1 xxxxxx Domain Users 2224 Mar 8 10:43 nation.tbl
-rw-r--r--+ 1 xxxxxx Domain Users 171952161 Mar 8 10:43 orders.tbl
-rw-r--r--+ 1 xxxxxx Domain Users 24135125 Mar 8 10:43 part.tbl
-rw-r--r--+ 1 xxxxxx Domain Users 118984616 Mar 8 10:43 partsupp.tbl
-rw-r--r--+ 1 xxxxxx Domain Users 389 Mar 8 10:43 region.tbl
-rw-r--r--+ 1 xxxxxx Domain Users 1409184 Mar 8 10:43 supplier.tbl



qgenでクエリー作成...?



クエリーは22個とさほど数も多くないし、数値型・文字列型を素直に使うだけのselect文がそんなに各データベース間の方言を意識して書き換えないといけないとも思えないので、そのまま各データベースに投入してエラーが出たら手で直すような形でいくことにする。


各データベースのテーブル作成・ロード



Teradata Expressには既に環境が作ってあるからそのままでいいとして(件数が合わせられなかったらロードし直すけど)、PostgreSQLとOracleについてはテーブルの定義を書いてやらないといけない。
これは結構なボリュームになりそうなので、後日、別記事で書くか。

ローダーについても調べないといけないし。


 
【 2014/03/13 (Thu) 】 データベース | TB(0) | CM(0)

チュートリアル的なデータベースの用意

昨日、インストールした後しばらく放置状態になってたPostgreSQL, Oracle Express, Teradata Expressの中を見て、チュートリアルで使えるデータがないか探した。
Teradata環境にTPC-Hデータがインストール済みなのを見て気付いたのだが、TPC委員会がデータベース性能測定用に用意してるデータモデル、データ、クエリーを使うという手があるなと思った。

本来の用途とは違うと思うが、各種データベース間で共通的に使えるという点は使えそうだ。


TPC委員会というのは、各種データベース製品・プラットフォーム間での性能を比較できるよう、共通のテストを用意してる団体。OracleやTeradataも会員企業のようだ。PostgreSQLを代表する団体は関わってなさそうだけど。

TPCのデータベースのテストとして用意されてるテストは、大きく分けてOLTP系(大量の軽量級トランザクションを処理する)データベースと、DSS系(少数の重量級分析を処理する)データベースの2種類がある。


OLTP系データベーステスト仕様



OLTP系テストの仕様として用意されているものとして、TPC-EとTPC-Cというものがあるようだ。
TPC-Cは既に古いテスト内容らしく、流通業で注文に応じ在庫を照会する処理をモデルにしたテストのようだ。
TPC-Eが現時点で新しいOLTPデータベースのテスト仕様で、証券会社の売買仲介業務に関する処理をモデルにしてるようだ。


DSS系データベーステスト仕様



DSS系テストの仕様としては、TPC-Hというのがある。
TPC-Hは流通業の売上げ等のデータをもとに各種分析業務をモデルにしてるようだ。

他には、TPC-VMSという仮想環境の検証のためのテストもあるし、サイトトップのニュースを見ると、ビッグデータに関するワーキンググループも2013年の終わり頃に発足したようだ。


採用するテスト



TPC-EとTPC-Hを採用する事にしよう。
TPC-Eは証券業務用データベースという事で、ちょうど年末あたりに考えていたデータベースと近い事を行えそう。

TPC-Hは何であるか話を聞いた事があるし、既にTeradataに環境が用意されていて、ゼロからのスタートでないので、とっかかりとして良さそうだ。

 
【 2014/03/11 (Tue) 】 データベース | TB(0) | CM(0)

チュートリアル的なデータベースの用意

正規表現からデータベースに戻ります



正規表現について書いてたのが長かったので、データベースについて書いてなかったなぁ。
いい加減データベースについて書くか。


データベースをインストールしてログインできるところまでは確認したから、次は実際にデータ入れて検索するところからだな。
データベース操作の細かい所は後でカスタマイズする事にして、まずはその場で簡単にデータとクエリーを用意して、それをPostgreSQL、Oracle、Teradataの3つで使ってみよう。感覚をつかむためにも。


PostgreSQL, Oracle Express, Teradata Expressのロード済みデータ



という事で、使えるデータがないかログインしてデモ用データなどがあらかじめロードされてないか見てみた。テーブルの中身まではチェックしてないが、名前的に見てこんな感じでチュートリアル的な環境が用意されてるっぽい。
・PostgreSQL…何もなさげ?
・Oracle Express…いろいろあるが、HRデータベースがそれっぽい。
・Teradata…Samplesデータベースの下に、たくさんのデータベースが用意されてる。
  financial
  manufacturing
  retail
  tpch
  transportation
  twm_md
  twm_results
  twm_source

TPCという手があったか。これを使えば簡単に同じテーブル、同じデータ、同じクエリーで比較できるな。
よし。TPCデータを使うことにしよう。


 
【 2014/03/09 (Sun) 】 データベース | TB(0) | CM(0)

bashで正規表現など使いデータの精査(文字列データその2)

bashで文字列の末尾のスペースを削除する



文字列の末尾にスペースがずっと続いている場合がたまにある。可変長文字列データなのに固定長として出力してしまったなど。末尾のスペースを除く方法は、他の日にも書いた、${param%%pattern}を使うと簡単。パターンマッチに使ってるファイル名展開(GLOB)を拡張させるため、シェルオプションにextglob(拡張ファイル名展開)を設定しないといけないが。


$ FIELD=" A "

$ shopt -s extglob

$ echo "===${FIELD%%+(\ )}==="
=== A===



なんで前後をダブルクオートと===で囲ってるかというと、本当にスペースがあるかどうか見やすくしてるだけ。
同様に${param##+(\ )}を使うと、冒頭のスペースを削除できる。

1文字以上を意味するプラス記号が、正規表現では指定したい文字列の後ろに書くところ、ここでは前に書かないといけないのがわかりにくい。


bashで文字列の前後のスペースを削除する



${param##pattern}と${param%%pattern}で冒頭と末尾のスペースを削除する方法。
Param部分は変数しか指定できないので、入れ子に書けないから不便。

$ shopt -s extglob

$ tmp=${FIELD%%+(\ )} #末尾のスペースを削除

$ echo "===${tmp##+(\ )}===" #冒頭のスペースを削除
===A===



もっと簡単な裏技的方法。単純にecho -n。Bashではechoもシェルのビルトイン機能なので別プロセス呼び出しのオーバーヘッドが発生しない。

$ echo "===${FIELD}==="
=== A ===

$ echo -n ${FIELD}
A

$ echo -n ${FIELD} | wc -c
1




クォートされた文字列からクォートを外す



文字列フィールドをシングルクォート(’)やダブルクォート(")で囲ってることはよくある話。
ローダーに渡す分には(恐らく)ローダーの方で勝手に外してくれるから問題ないけど、シェルスクリプトの中で文字数を確認したり値を使いたい場合があるかもしれない。

例えばクォート文字をQUOTE、クォートをエスケープする文字をQUOTE_ESC変数で持ってるとして、

$ echo ${QUOTED_STRING}
'This isn''t mom''s.'

$ QUOTE="'"

$ QUOTE_ESC="'"

$ TMP=${QUOTED_STRING//${QUOTE_ESC}${QUOTE}/${QUOTE}} #エスケープされたクォートをクォート文字単体に戻す

$ echo ${TMP}
'This isn't mom's.'

$ [[ ${TMP} =~ ^${QUOTE}.*[^${QUOTE}]$ ]] && DATA_ERROR_FLG=1 #クォートが閉じてないかチェック

$ [[ ${TMP} =~ ^[^${QUOTE}].*${QUOTE}$ ]] && DATA_ERROR_FLG=1 #クォートで始まってないのに閉じてないかチェック

$ TMP=${TMP#${QUOTE}} #冒頭のクォートを外す

$ UNQUOTED_STRING=${TMP%${QUOTE}} #末尾のクォートを外す

$ echo ${UNQUOTED_STRING}
This isn't mom's.




【 2014/03/02 (Sun) 】 OS Linuxコマンド(編集用) | TB(0) | CM(0)

Bashで正規表現など使いデータの精査(文字列データ)

文字列データは精査すべき内容はあまりない。
文字コードのチェック、指定した文字コードに相応しくない値が入ってるかのチェックなどの言語関係のチェックは、bashには敷居が高すぎる。


bashで文字列の長さを調べる



文字列フィールドには固定長・可変長があるが、いずれにせよ大抵の場合は最大で何文字までか指定されている。
そこで、与えられた文字列が何文字(正確には何バイト)かを確認する必要がある。
ローダーによってはエラーで落とさず勝手に切り詰めてくれる場合もあるけど、本当にそれでいいかという話もあるし。

Bashで変数paramの格納してる文字列の長さを調べたいとき、${#param}という表記を使うと、その文字列長が返せる。


$ FIELD="The quick brown fox jumps over the lazy dog."

$ echo ${#FIELD}
44


という事で、単純にbashの条件チェックにそのまま書き込むと、こんな感じになる。

[[ ${#FIELD} -le 127 ]] || DATA_ERROR_FLG=1


この${#param}という機能は、他の多くのbash文字列機能と同様に、中カッコで囲った中に与える一つ目の値は変数でなければならず、定数(文字列リテラルや変数を展開した値)は受け付けないのが多少不自由なときがあるが。
【 2014/03/01 (Sat) 】 OS Linuxコマンド(編集用) | TB(0) | CM(0)
プロフィール

Ed U Song

Author:Ed U Song
社内ノマドなエンジニア。
仕事で触れる機会のないものを自宅環境作って実験。

スポンサーリンク
最新コメント
最新トラックバック
検索フォーム


                                         
上記広告は1ヶ月以上更新のないブログに表示されています。新しい記事を書くことで広告を消せます。