CREATE TABLEを調べてPostgreSQLと比べてみる
というわけで先日インストールしたコミュニティエディションからマニュアルを引っ張って読んでみる。
CREATE [[GLOBAL | LOCAL] {TEMPORARY | TEMP}] TABLE table_name ( [ { column_name data_type [DEFAULT default_expr] [column_constraint [ ... ]] | table_constraint | LIKE other_table [{INCLUDING | EXCLUDING} {DEFAULTS | CONSTRAINTS}] ...} [, ... ] ] ) [ INHERITS ( parent_table [, ... ] ) ] [ WITH ( storage_parameter=value [, ... ] ) [ ON COMMIT {PRESERVE ROWS | DELETE ROWS | DROP} ] [ TABLESPACE tablespace ] [ DISTRIBUTED BY (column, [ ... ] ) | DISTRIBUTED RANDOMLY ] [ PARTITION BY partition_type (column) [ SUBPARTITION BY partition_type (column) ] [ SUBPARTITION TEMPLATE ( template_spec ) ] [...] ( partition_spec ) | [ SUBPARTITION BY partition_type (column) ] [...] ( partition_spec [ ( subpartition_spec [(...)] )] )
LIKEも対応しているし、ぱっと見たところPostgreSQLのHEADと比べてもそんなに違いはなさそうに思いますが、どうなんでしょう。
DISTRIBUTED BY | DISTRIBUTED RANDOMLY は先日説明しましたが、Greenplum独特且つ最も気にするポイントです。
その下にPARTITION BYがありますね。さらにSUBPARTITION TEMPLATEとかあってもう目眩がしそうなので深くは突っ込みません。気になる方は是非インストールして試してね。しつこいようですがCommunity Editionと言えども機能に差はありませんので。
DISTRIBUTED BYがあるのでPARTIITON BYはいらないだろうと思ったそこのあなた、鋭い。私も最初はそう思いました。が、これが結構重要らしいんですね。テーブル毎に行指向と列指向を使い分けられるので、同一テーブルのパーティション毎に戦略を変えたり、不要になった古いログデータを一括で削除するなどはパーティションあっての操作です。追記のみテーブル（AppendOnly）とかもやるのでパーティションで削除するわけです。まとめると、分散（DISTRIBUTED）は物理的な配置を決めるのに対して、分割（PARTITION）は論理的な配置を決めているわけですね。
せっかくなのでもう少し。
where storage_parameter is:
APPENDONLY={TRUE|FALSE}
ORIENTATION={COLUMN|ROW}
COMPRESSTYPE={ZLIB|QUICKLZ}
COMPRESSLEVEL={0-9 | 1}
FILLFACTOR={10-100}
OIDS[=TRUE|FALSE]
ストレージパラメータのところで「追記のみ」「列指向」「圧縮種類」「圧縮レベル」「充填率」「OID」などを決められます。最後の二つはPostgreSQLにもあるので割愛しますが、それ以外はDWHでは結構重要な機能のような気がします。追記のみを選択することによってトランザクション処理ができなくなりますが、行ヘッダが省略されるのでテーブルサイズが小さくなります。列指向も最近流行のようですが、整数なら整数、文字列なら文字列というように同じ性質を持つデータが並ぶため圧縮率が高くなる傾向にあるようです。その他の二つもどのように圧縮するかのパラメータですね。何を指定するとどのぐらい圧縮されるかはデータにも依るとおもいますので、ぜひ自分で試してみてください。
というわけでPostgreSQLとは全然比較していないですがCREATE TABLEの構文について調べてみました。
テーブル作成
どうもこんにちは。ilovegpdbです。
前回が力みすぎたので今日は気軽に行きます。
テーブルをつくって遊ぶ
インストールしたはいいけどテーブルがないと何もできないですね。
SQLはだいたいPostgreSQLと同じなのでPostgreSQL使ったことがあると何かと便利ですよ。
-- テーブルを作る CREATE TABLE sales( order_no bigint, product_code char(8), sales_time timestamp ) DISTRIBUTED BY (order_no);
みたいな感じにしてみました。あくまで例です。
CREATE TABLEと列定義は標準SQLとほぼ互換なので問題ないですね。後ろにDISTRIBUTED BY句がついています。Greenplumはシェアードナッシングのデータベースなので、セグメントサーバが一つのテーブルを水平分割して持ちます。このときの分散ポリシーをテーブル毎に決定できるわけです。キーはハッシュ関数にかけられてその結果で保存ノードが決まります。ハッシュテーブルのバケットがノードになった感じですね。ちなみにDISTRIBUTED RANDOMLYを指定すると完全にランダムに（ラウンドロビンで）分散されます。一見素敵な分散ポリシーに見えますが、分散キーはクエリの性能を左右するので決めておくことが重要です。そのうちわかります。
SELECT
db1=# INSERT INTO sales SELECT order_no, lpad((order_no / 2)::text || (order_no % 10), 8, '0'), '2010-01-01 00:00:00'::timestamp + ('' || order_no || 'min')::interval FROM generate_series(1, 1000000)order_no;
INSERT 0 1000000
Time: 7583.776 msgenerate_series()もPostgreSQLと同様に使えるので便利ですね。
db1=# SELECT * FROM sales LIMIT 10;
order_no | product_code | sales_time
----------+--------------+---------------------
1 | 00000001 | 2010-01-01 00:01:00
3 | 00000013 | 2010-01-01 00:03:00
5 | 00000025 | 2010-01-01 00:05:00
7 | 00000037 | 2010-01-01 00:07:00
9 | 00000049 | 2010-01-01 00:09:00
11 | 00000051 | 2010-01-01 00:11:00
13 | 00000063 | 2010-01-01 00:13:00
15 | 00000075 | 2010-01-01 00:15:00
17 | 00000087 | 2010-01-01 00:17:00
19 | 00000099 | 2010-01-01 00:19:00
(10 rows)
Time: 10.978 ms
db1=# SELECT count(*), avg(substr(product_code, 4, 4)::int) FROM sales;
count | avg
---------+--------
1000000 | 4999.5
(1 row)
Time: 399.057 ms集計機能も問題なし。EXPLAINしてみます。
db1=# EXPLAIN ANALYZE SELECT count(*), avg(substr(product_code, 4, 4)::int) FROM sales;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=16845.37..16845.38 rows=1 width=9)
Rows out: 1 rows with 1694 ms to end, start offset by 0.372 ms.
Executor memory: 8K bytes.
-> Gather Motion 2:1 (slice1; segments: 2) (cost=16845.31..16845.35 rows=1 width=9)
Rows out: 2 rows at destination with 1677 ms to first row, 1694 ms to end, start offset by 0.375 ms.
-> Aggregate (cost=16845.31..16845.32 rows=1 width=9)
Rows out: Avg 1.0 rows x 2 workers. Max 1 rows (seg0) with 1693 ms to end, start offset by 0.716 ms.
Executor memory: 8K bytes avg, 8K bytes max (seg0).
-> Seq Scan on sales (cost=0.00..11842.20 rows=500310 width=9)
Rows out: Avg 500000.0 rows x 2 workers. Max 500001 rows (seg0) with 0.036 ms to first row, 674 ms to end, start offset by 0.717 ms.
Slice statistics:
(slice0) Executor memory: 221K bytes.
(slice1) Executor memory: 240K bytes avg x 2 workers, 240K bytes max (seg0).
Total runtime: 1694.183 ms
(14 rows)
Time: 1695.052 ms二段で集計している様子が伺えますね。Greenplumはセグメントサーバを並列処理させることでクエリの高速化を図ります。分散集計はまさにこの典型で、いったんセグメント内で集計してからマスタサーバにかき集めて再集計しているようです。
まとめ
とりあえずSQLを実行しました。簡単なSQLの実行を確認できました。
ちなみに今回からxlargeインスタンスを使うことにしましたので、もう少し１ノードでセグメント数を増やしてみたいと思います。また、PostgreSQL単体の処理と比較してどのぐらい違うか比べてみたいです。
gpinitsystemでセグメントの初期化に失敗する
gpinitsystemが失敗してログを見ると下記のようなエラーが発生している場合
initdb: error 256 from: "/usr/local/greenplum-db-4.0.4.0/bin/postgres" --boot -x0 -F -c max_connections=125 -c shared_buffers=400 0 -c max_fsm_pages=200000 < "/dev/null" > "/dev/null" 2>&1
sysctlの設定が悪いことが多いようです。今回の例では上記のsysctl.confの追記をすべて消したらうまく行きました。こういうバッドノウハウはやってみないとわからないですね。
Greenplumをサーバ１台で動かす
前回予告しました通りレッツトライ。
Greenplumっていうと外資系の企業のホームページとかエンタープライズ系の技術情報サイトとかでアーキテクチャの話ばっかりで疲れますので、肩の力でも抜いて導入系の記事なんかどうでしょう。水曜どうでしょう。
Greenplumは「シェアードナッシング」とか「並列分散DB」とか言われますけど、もちろん１台のサーバで動かすことも可能ですので、まずはそのへんから行きたいと思います。手元に用意するものは特にありません。お使いのラップトップとメールアカウント、インターネットと小額のマネー（EC2を使うので）です。
とりあえずダウンロードする
http://www.greenplum.com/community/downloads/
画面下にスクロールするとLinuxのタブがあるのでそいつを開いて緑色の「Download Software」をクリッコします。メールアカウントとか適当でよいので入力するとすぐにダウンロード用メールが来ます。今回は上から二番目のCentOS5 64bit用（Red Hat Enterprise Linux 5.x / CentOS 5.x (x86 – 64bit) ）ってやつにします。
ちなみにご覧の通りVMwareのイメージもありますので、Windows PC上で動かす人もとりあえずこれダウンロードしてLinux動かすこともできます。
EC2でCentOSのサーバを用意する
Amazon EC2はご存知ですか。そうですか。一応説明しておくと仮想サーバを時間課金で利用できるいわゆるクラウドサービスの走りですね。Linuxサーバが別途用意できる人も割愛してください。ただサーバの追加とか試すには仮想環境はもってこいですので一考の価値があります。お金で解決できる問題はお金で解決するのがかっこいい大人ってもんです。嘘です。ただ、常時起動するのでもなければ10台ぐらい動かしてもランチ一回分にもならないと思いますので気にする必要はないんじゃないでしょうか。
ちなみにダウンロードしたCommunity Editionですが、「評価・研究・開発目的に限り」複数ノードでの利用も認められているようです。まーそんな内部でどう使われてるかなんて分かりっこないし落ちてるバイナリを勝手に使ってても（自粛）
AMIはCent5/x86_64またはRHEL5/x86_64の適当なのをスタートさせてください。EBSイメージがよいでしょう。
インストールする
gpadminユーザ作成
まずはユーザでも作ります。sshした段階でrootだと思うのでgpadminという名前で一般ユーザを作っておきましょう。
インストーラの解凍＆実行
ひきつづきrootのままでアップロードしたzipをunzipします。出てきた.binファイルを実行します。jdkのインストーラと一緒ですね。
[root@host ~]# unzip greenplum-db-4.0.4.0-build-3-SingleNodeEdition-RHEL5-x86_64.zip Archive: greenplum-db-4.0.4.0-build-3-SingleNodeEdition-RHEL5-x86_64.zip inflating: README_INSTALL inflating: greenplum-db-4.0.4.0-build-3-SingleNodeEdition-RHEL5-x86_64.bin [root@host ~]# ./greenplum-db-4.0.4.0-build-3-SingleNodeEdition-RHEL5-x86_64.bin
ライセンス条項が出てくるので良い子のみんなはちゃんと最後まで読んで、良くない子は適当に読み流してあとは"yes"を連発すればOK. /usr/local/greenplum-db-4.0.4.0/以下にクリーンインストールされましたとさ。/usr/local/greenplum-db/がシンボリックリンクになっています。
ちなみにこのあたりはunzipしたファイルREADME_INSTALLにも出てきます。
README_INSTALLに従ってOSパラメータを設定。
/etc/sysctl.confに以下を追記。Cent／RHELならデフォルトそのままでもいけるかも。デフォルトそのままがよさげ。
kernel.sem = 250 64000 100 512 kernel.shmmax = 500000000 kernel.shmmni = 4096kernel.shmall = 4000000000 kernel.sem = 250 64000 100 512 kernel.sysrq = 1 kernel.core_uses_pid = 1 kernel.msgmnb = 65536 kernel.msgmax = 65536 net.ipv4.tcp_syncookies = 1 net.ipv4.ip_forward = 0 net.ipv4.conf.default.accept_source_route = 0 net.ipv4.tcp_tw_recycle=1 net.ipv4.tcp_max_syn_backlog=4096 net.ipv4.conf.all.arp_filter = 1 net.core.netdev_max_backlog=10000 vm.overcommit_memory=2
念のためここでOS再起動して設定を反映させます。それにしても最近のEC2はOS再起動とかも激っパヤ。
gpadminユーザの設定
greenplumの各種コマンドを実行するのにシェルの設定が必要です。そのためのスクリプトが/usr/local/greenplum-db/greenplum_path.shにありますので、gpadminの$HOME/.bash_profileに追記します。
# .bash_profile # Get the aliases and functions if [ -f ~/.bashrc ]; then . ~/.bashrc fi # User specific environment and startup programs PATH=$PATH:$HOME/bin export PATH source /usr/local/greenplum-db/greenplum_path.sh
ログインしてログアウトするとgpstartとか見えてるはずです。見えてなければ何かおかしいのでご確認よろしこ。
[gpadmin@host ~]$ which gpstart /usr/local/greenplum-db/bin/gpstart
sshセットアップ
シングルノードでもsshがいります。とりあえず鍵作成。パスフレーズなしでお願いします。できたらlocalhostにログインできるところまで確認よろしこ。
[gpadmin@host ~]$ ssh-keygen -t rsa Generating public/private rsa key pair. Enter file in which to save the key (/home/gpadmin/.ssh/id_rsa): Created directory '/home/gpadmin/.ssh'. Enter passphrase (empty for no passphrase): Enter same passphrase again: Your identification has been saved in /home/gpadmin/.ssh/id_rsa. Your public key has been saved in /home/gpadmin/.ssh/id_rsa.pub. [gpadmin@host ~]$ cd .ssh [gpadmin@host .ssh]$ cat id_rsa.pub >> authorized_keys [gpadmin@host .ssh]$ chmod 600 authorized_keys [gpadmin@host .ssh]$ ssh localhost
DBの初期化と起動
データディレクトリ作成
どこでもいいですが、今回は/data/にします。/data/masterをマスタ、/data/segments以下にセグメントサーバのディレクトリです。
それぞれディレクトリ作っときます。
[root@host ~]# mkdir /data/ [root@host ~]# mkdir /data/master /data/segments [root@host ~]# chown gpadmin.gpadmin /data -R
gpinitsystem
PostgreSQLのinitdbみたいなものがgpinitsystemです。マスタとセグメントサーバの両方を初期化しますので結構大掛かりです。マスタとかセグメントとかの説明を端折りますけど面倒なので。
やることはいっぱいあるので設定ファイルがあります。/usr/local/greenplum-db/docs/cli_help/gp_init_config_exampleをいじればよいです。
[gpadmin@host ~]$ cp /usr/local/greenplum-db/docs/cli_help/gp_init_singlenode_example ~/
中身はディレクトリだけかえます。
59c59 < declare -a DATA_DIRECTORY=(/gpdata1 /gpdata2) --- > declare -a DATA_DIRECTORY=(/data/segments /data/segments) 74c74 < MASTER_DIRECTORY=/gpmaster --- > MASTER_DIRECTORY=/data/master
セグメントサーバは２つ作るので同じですが/data/segmentsを２度書いてます。お気になさらずに。
あとはホスト名をファイルにしておくのとssh鍵交換をすませておくこと。
[gpadmin@host ~]$ echo 127.0.0.1 > single_hostlist
[gpadmin@host ~]$ gpssh-exkeys -h 127.0.0.1
gpssh-exkeysで鍵交換を一発解決。ありがちなknown_hosts問題も解消。
実行
作成した設定ファイルは-cオプションでどぞ
[gpadmin@host ~]$ gpinitsystem -c gp_init_singlenode_example
"Greenplum Database instance successfully created."って出ればOK牧場みたいっすよ。
psqlで接続してあげてください。psqlがつながればあとは普通のPostgreSQLと同じなのでcreatedbしたりCREATE TABLEしたりSELECTしたりして玩んでください。あと、マスタディレクトリの場所を環境変数にセットしておかないと次回以降起動できないので忘れずに。
# .bash_profile export MASTER_DATA_DIRECTORY=/data/master/gpsne-1
ちなみにここにpostgresql.confとか入ってます。興味のある方はぜひ覗いてみるとよいんではないでしょうか。
復習用
gpinitsystemのログは~/gpAdminLogs/に出ます。あとインストールのインストラクションは/usr/local/greenplum-db/docs/GPDB-SingleNodeEdition-QuickStart.pdfにかいてあります。
まとめ
長くなってますが、エッセンスは簡単ですね。EC2起動も入って１時間ぐらいで実現可能なレベルですから。次はマルチサーバ構成にチャレンジしたいです安西先生。
BEGIN;
"Big Data"とか何とかでけっこうBuzzってる感のあるGreenplum DBですが、Community Edition (CE) があってとりあえず使ってみるにはちょうど良さそうなので使ってみる記録。サーバはないのでAmazonさんちのEC2の力に頼りまくることにします。DWHとかBIとか言うとエンタープライズな匂いがぷんぷんして敷居が高いように見えるけど実は結構イケるのではないかと。
リンク：http://www.greenplum.com/community/downloads/
インストール記事は別に書きます。とりあえずリンク。
