0812345678910111213141516171819202122232425262728293010

スポンサーサイト

上記の広告は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)

PostgreSQLクライアントの追加

PostgreSQLのサーバー環境からpsqlするだけではつまらないので、接続ドライバーとクライアントをダウンロードする事にした。

接続ドライバーは、JDBCとODBC。クライアントアプリは、PostgreSQLのWikiとしてまとめられている中から、pgAdmin IIIというものを選ぶことにした。
pgAdmin IIIダウンロードページの一番下にあるのは最新版でないので、接続するとメタデータ取得に失敗してエラーが出まくって何も表示できなくなる。最新の名前をきちんと探そう。


JDBCドライバーのダウンロードページはこちら。JDBC3、JDBC4、JDBC41の3種類があるようだが、41って何だ?初めて聞いたぞ。WikiepdiaのJDBCページにも記述は存在しない。

ODBCドライバーのダウンロードページはこちら。ODBCは32ビットと64ビットの両方あるが、32ビットにしてみた。


接続確認に使ったアプリpgAdmin IIIは、ODBCやJDBCでなく、直接PostgreSQLのライブラリを使って接続しに行くようだ。ま、今回の接続確認という意味では、それで別にいいか。

クライアントのインストールは簡単に完了する。インストーラーを起動するだけ。


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

PostgreSQLにユーザーを作成する--createuser

サーバープロセスの起動まわりの設定をやっと終えたので、クライアントからログインできるよう設定する。

ユーザーpostgresでログインできるのは確認した。ただ、これは特権ユーザーなので、通常権限のユーザーを作る事にする。
いい名前が思いつかないので、hogeにしてしまおう。w


LinuxコマンドプロンプトからPostgreSQLデータベースユーザーを作る。データベースにログインした状態でないのに操作できてしまうのは違和感がある。もちろんデータベースログインした状態でも同等の操作はできるんだろうけど、逆にログインしてないと操作できないようにできるんだろうか?
これは後で調べてみる価値がありそうだな。
自分しか使わない実験環境なら関係ないけど、仕事でセキュリティをはじめ運用まわりを設計する時には絶対問題にされそう。


ま、それは後で調べるとして、とりあえず実際にユーザーを作ってみる。作るユーザーはhogeとpiyo。
PostgreSQL用の特権Linuxユーザー、postgresにsuして、Linuxシェル環境からcreateuserコマンドでデータベースユーザーを作成する。
(上に特権ユーザーと書いているが、LinuxのOSユーザーとしてはUIDが0でないので、スーパーユーザーではない。)


[root@localhost ~]# su - postgres
-bash-4.1$ createuser -D -E -R -S -P hoge
Enter password for new role:
Enter it again:
-bash-4.1$ createuser -D -E -R -S -P piyo
Enter password for new role:
Enter it again:
-bash-4.1$ psql postgres
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.




ユーザーが登録されてるか確認してみる。
pg_*というテーブル?ビュー?で、登録されたユーザーの一覧が参照できるようなので、それを使って一覧表示してみる。
Usernameが予約語なのかな?カラム名がUsenameになってるので間違えた。

postgres=# select username, passwd from pg_user;
ERROR: column "username" does not exist
LINE 1: select username, passwd from pg_user;
^
postgres=# select usename, passwd from pg_user;
usename | passwd
----------+----------
postgres | ********
hoge | ********
piyo | ********
(3 rows)

postgres=# \q
-bash-4.1$ psql -U piyo postgres
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.

postgres=> select * from pg_user;
usename | usesysid | usecreatedb | usesuper | usecatupd | userepl | passwd | valuntil | useconfig
----------+----------+-------------+----------+-----------+---------+----------+----------+-----------
postgres | 10 | t | t | t | t | ******** | |
hoge | 16384 | f | f | f | f | ******** | |
piyo | 16385 | f | f | f | f | ******** | |
(3 rows)

postgres-> \q


ちゃんと新たに追加したhogeとpiyoが認識されているのが確認できた。


psqlのコマンドプロンプトが細かい所に配慮が効いている。
ユーザーpostgresでログインしてるときと、他のユーザーでログインしているときで、プロンプトが変わっている。
特権ユーザーpostgresの時は、まるでシェル環境にrootでログインしているように、プロンプトに"#"がつく。
面白い。


ところで、piyoユーザーでログインしたとき、パスワードを聞かれなかったような...?
何か設定が漏れてるようだ。調べてやり直そう。



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

PostgreSQLのログインユーザーを作成 (1)

サーバープロセスの起動まわりの設定をやっと終えたので、クライアントからログインできるよう設定する。

ユーザーpostgresでログインできるのは確認した。ただ、これは特権ユーザーなので、通常権限のユーザーを作る事にする。
いい名前が思いつかないので、hogeにしてしまおう。w


LinuxコマンドプロンプトからPostgreSQLデータベースユーザーを作る。データベースにログインした状態でないのに操作できてしまうのは違和感がある。もちろんデータベースログインした状態でも同等の操作はできるんだろうけど、逆にログインしてないと操作できないようにできるんだろうか?
これは後で調べてみる価値がありそうだな。
自分しか使わない実験環境なら関係ないけど、仕事でセキュリティをはじめ運用まわりを設計する時には絶対問題にされそう。


ま、それは後で調べるとして、とりあえず実際にユーザーを作ってみる。作るユーザーはhogeとpiyo。
PostgreSQL用の特権Linuxユーザー、postgresにsuして、Linuxシェル環境からcreateuserコマンドでデータベースユーザーを作成する。
(上に特権ユーザーと書いているが、LinuxのOSユーザーとしてはUIDが0でないので、スーパーユーザーではない。)


[root@localhost ~]# su - postgres
-bash-4.1$ createuser -D -E -R -S -P hoge
Enter password for new role:
Enter it again:
-bash-4.1$ createuser -D -E -R -S -P piyo
Enter password for new role:
Enter it again:
-bash-4.1$ psql postgres
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.

postgres=# select username, passwd from pg_user;
ERROR: column "username" does not exist
LINE 1: select username, passwd from pg_user;
^
postgres=# select usename, passwd from pg_user;
usename | passwd
----------+----------
postgres | ********
hoge | ********
piyo | ********
(3 rows)

postgres=# \q
-bash-4.1$ psql -U piyo postgres
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.

postgres=> select * from pg_user;
usename | usesysid | usecreatedb | usesuper | usecatupd | userepl | passwd | valuntil | useconfig
----------+----------+-------------+----------+-----------+---------+----------+----------+-----------
postgres | 10 | t | t | t | t | ******** | |
hoge | 16384 | f | f | f | f | ******** | |
piyo | 16385 | f | f | f | f | ******** | |
(3 rows)

postgres-> \q


psqlのコマンドプロンプトが細かい所に配慮が効いている。
ユーザーpostgresでログインしてるときと、他のユーザーでログインしているときで、プロンプトが変わっている。
特権ユーザーpostgresの時は、まるでシェル環境にrootでログインしているように、プロンプトに"#"がつく。
面白い。

ところで、piyoユーザーでログインしたとき、パスワードを聞かれなかったような...?
何か設定が漏れてるようだ。調べてやり直そう。


【 2014/02/08 (Sat) 】 データベース PostgreSQL | TB(0) | CM(0)
プロフィール

Ed U Song

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

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


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