RDB Postgresql 9.4 beta 2 で JSONB を試す

2023-05-292023
40.5% (148.1/365)
May
90.8% (28.1/31)
Week 22
2.4% (0.1/7)
Day 29 Mon
17.4% (4.1/24)

訪問者

total: 9237
today: 1
yesterday: 1
now: 9

↑

更新

MenuBar

Dockerコンテナ
文字コードを SQL_ASCII から UTF-8 に変更する
JSONBを格納するテーブルを作る
JSONBの格納
検索
JSONからの要素の取り出し

Dockerコンテナ †

こういう検証をやるときにコンテナ便利やね → Docker
- 既存の資産がないクリーンな環境
- 何回でも作り直せる
構築スクリプト (Dockerfile)
- https://github.com/kagyuu/DockerExam
- ssh とプロセス監視の monit だけ入った ubuntu_monit_ssh の上に色々なサービスを乗っける
- 今回は postgresql 9.4 beta 2

atsushi/ubuntu_monit_ssh

# Pull base image.
FROM ubuntu:14.10

MAINTAINER Atsushi HONDOH <kagyuu@hondou.homedns.org>
 
## Proxy
# ENV http_proxy http://foo%40bar.com:password@proxy.bar.com:3124/
# ENV https_proxy http://foo%40bar.com:password@proxy.bar.com:3124/

## Install ssh and monit
RUN apt-get -y update && apt-get -y upgrade
RUN apt-get install -y openssh-server rsyslog monit

# Initialize sshd
RUN /etc/init.d/ssh start && /etc/init.d/ssh stop

# Permit only pubkey access
RUN sed -ri 's/UsePAM yes/UsePAM no/g' /etc/ssh/sshd_config
RUN echo "PasswordAuthentication no" >> /etc/ssh/sshd_config
RUN echo "PermitRootLogin no" >> /etc/ssh/sshd_config

RUN mkdir -p /var/run/sshd && chmod 755 /var/run/sshd

## Operator user
RUN addgroup wheel
RUN useradd -m -G wheel -s /bin/bash docker
RUN echo docker:password | chpasswd

# Setup sudoers
RUN echo "%wheel ALL=(ALL) ALL" >> /etc/sudoers
 
# Setup SSH
RUN mkdir /home/docker/.ssh
ADD authorized_keys /home/docker/.ssh/authorized_keys
RUN chown -R docker:docker /home/docker/.ssh/
RUN chmod 701 /home/docker
RUN chmod 700 /home/docker/.ssh
RUN chmod 600 /home/docker/.ssh/authorized_keys

## Setup Monit
ADD monit.conf /etc/monit/conf.d/monit.conf
ADD ssh.conf /etc/monit/conf.d/ssh.conf
ADD rsyslog.conf /etc/monit/conf.d/rsyslog.conf
 
## Expose ports
# 22    : ssh
# 2812  : monit
EXPOSE 22 2812
 
## Execute monit
CMD ["/usr/bin/monit","-I"]

atsushi/ubuntu_postgresql94b2

# Pull base image.
FROM atsushi/ubuntu_monit_ssh

MAINTAINER Atsushi HONDOH <kagyuu@hondou.homedns.org>

## Add PostgreSQL 9.4 beta2 for Ubuntu 14.10 Utopic
RUN echo "deb http://apt.postgresql.org/pub/repos/apt/ utopic-pgdg main 9.4" > /etc/apt/sources.list.d/pgdg.list
RUN apt-get -y update && apt-get -y upgrade
# "--force-yes" because pgsql is beta version
RUN apt-get -y --force-yes install postgresql

# make encoding UTF-8 (default is damn SQL_ASCII)
RUN pg_dropcluster --stop 9.4 main
RUN pg_createcluster --start -e UTF-8 9.4 main

# make docker super-use of postgresql
USER postgres
RUN /etc/init.d/postgresql start &&\
    psql -c "CREATE USER docker WITH SUPERUSER PASSWORD 'docker';" &&\
    createdb -O docker docker &&\
    /etc/init.d/postgresql stop
USER root

# allow remote access
RUN sed -ri 's/host    all             all             127.0.0.1\/32            md5/host    all             all
             0.0.0.0\/0               md5/g' /etc/postgresql/9.4/main/pg_hba.conf
RUN echo "listen_addresses='*'" >> /etc/postgresql/9.4/main/postgresql.conf

## Monit
ADD postgresql.conf /etc/monit/conf.d/postgresql.conf
 
## Expose ports
# 5432  : postgresql
EXPOSE 5432

## VOLUME 
VOLUME  ["/etc/postgresql", "/var/log/postgresql", "/var/lib/postgresql"]

build

$ docker build -t atsushi/ubuntu_postgresql94b2 ./
Sending build context to Docker daemon 14.85 kB
Sending build context to Docker daemon
Step 0 : FROM atsushi/ubuntu_monit_ssh
Step 1 : MAINTAINER Atsushi HONDOH <kagyuu@hondou.homedns.org>
Step 2 : RUN echo "deb http://apt.postgresql.org/pub/repos/apt/ utopic-pgdg main 9.4" > /etc/apt/sources.list.d/pgdg.list
...
Step 10 : ADD postgresql.conf /etc/monit/conf.d/postgresql.conf
Step 11 : EXPOSE 5432
Step 12 : VOLUME  ["/etc/postgresql", "/var/log/postgresql", "/var/lib/postgresql"]
Successfully built 3fe1ca3ec7d7

run

$ docker run -p 10022:22 -p 12812:2812 -p 5432:5432 -d atsushi/ubuntu_postgresql94b2

コンテナを落としてもデータを残したければ Docker Volume を使う
```
$ docker run --name pg94data1 atsushi/ubuntu_postgresql94b2 echo "data volume created"
data volume created
$ docker run --volumes-from pg94data1 -p 10022:22 -p 12812:2812 -p 5432:5432 -d atsushi/ubuntu_postgresql94b2
```
デーモン起動した postgresql94b2 コンテナの ["/etc/postgresql", "/var/log/postgresql", "/var/lib/postgresql"] は、最初に起動して直後に終了した postgresql94b2 コンテナの該当領域が使われる

↑

文字コードを SQL_ASCII から UTF-8 に変更する †

Postgresql 9.4 のデフォルトは SQL_ASCII

http://www.postgresql.org/docs/9.4/static/multibyte.html

docker@1aedc1ccdf37:~$ psql -l            
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
	LANGUAGE = (unset),
	LC_ALL = (unset),
	LC_PAPER = "en_US.UTF-8",
	LC_ADDRESS = "en_US.UTF-8",
	LC_MONETARY = "en_US.UTF-8",
	LC_NUMERIC = "en_US.UTF-8",
	LC_TELEPHONE = "en_US.UTF-8",
	LC_IDENTIFICATION = "en_US.UTF-8",
	LC_MEASUREMENT = "en_US.UTF-8",
	LC_TIME = "en_US.UTF-8",
	LC_NAME = "en_US.UTF-8",
	LANG = "en_US.UTF-8"
    are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
                             List of databases
   Name    |  Owner   | Encoding  | Collate | Ctype |   Access privileges   
-----------+----------+-----------+---------+-------+-----------------------
 docker    | docker   | SQL_ASCII | C       | C     | 
 postgres  | postgres | SQL_ASCII | C       | C     | 
 template0 | postgres | SQL_ASCII | C       | C     | =c/postgres          +
           |          |           |         |       | postgres=CTc/postgres
 template1 | postgres | SQL_ASCII | C       | C     | =c/postgres          +
           |          |           |         |       | postgres=CTc/postgres
(4 rows)

UTF-8 で作り直す

# make encoding UTF-8 (default is damn SQL_ASCII)
RUN pg_dropcluster --stop 9.4 main
RUN pg_createcluster --start -e UTF-8 9.4 main

docker@891fbc133dec:~$ psql -l
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
	LANGUAGE = (unset),
	LC_ALL = (unset),
	LC_PAPER = "en_US.UTF-8",
	LC_ADDRESS = "en_US.UTF-8",
	LC_MONETARY = "en_US.UTF-8",
	LC_NUMERIC = "en_US.UTF-8",
	LC_TELEPHONE = "en_US.UTF-8",
	LC_IDENTIFICATION = "en_US.UTF-8",
	LC_MEASUREMENT = "en_US.UTF-8",
	LC_TIME = "en_US.UTF-8",
	LC_NAME = "en_US.UTF-8",
	LANG = "en_US.UTF-8"
    are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
                             List of databases
   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges   
-----------+----------+----------+---------+-------+-----------------------
 docker    | docker   | UTF8     | C       | C     | 
 postgres  | postgres | UTF8     | C       | C     | 
 template0 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
 template1 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
(4 rows)

↑

JSONBを格納するテーブルを作る †

$ psql
psql (9.4beta2)
Type "help" for help.

docker=# CREATE DATABASE sample;
CREATE DATABASE
docker=# \c sample
You are now connected to database "sample" as user "docker".
sample=# CREATE TABLE articlej_tbl (
sample(#   id bigserial primary key,
sample(#   onCreate timestamp default NULL,
sample(#   onUpdate timestamp default NULL,
sample(#   contents jsonb NOT NULL
sample(# );
CREATE TABLE
sample=# CREATE INDEX articlej_tbl_idx_contents ON articlej_tbl USING gin (contents);
CREATE INDEX
sample=#

id : 人工キー (PK)
onUpdate : 楽観ロック用
contents : JSON で記事を格納する

JSONB列に、GIN (転置インデックス) を設定するのがミソ
GIN は、本の索引のようなもの。
- データの値から、データ構造体をひける索引
- 全文検索などでよく使われる。単語からその単語が使われているページのリストをひける表を作っておいて、全文検索時には表を見る
- JSONB では、JSONの項目の値から JSON をひけるような表を作っている
Postgresql の JSONB の製作者(オレグさん@モスクワ大)が講演で、JSONB は、B-Tree を使っている MongoDB よりも数倍速いっていってた

↑

JSONBの格納 †

docker@42649b72cb9c:~$ psql -n -d sample
psql (9.4beta2)
Type "help" for help.

sample=# \d
                List of relations
 Schema |        Name         |   Type   | Owner
--------+---------------------+----------+--------
 public | articlej_tbl        | table    | docker
 public | articlej_tbl_id_seq | sequence | docker
(2 rows)

sample=# INSERT INTO articlej_tbl (onCreate,onUpdate,contents) VALUES (
now(),
now(),
'{"title" : "ポスト「京」",
"category" : ["Computer","Science"],
"format" : "HTML",
"author" : "浅川 直輝",
"publisher" : "日経コンピュータ",
"date" : "2014/10/02",
"url" : "http://itpro.nikkeibp.co.jp/atcl/news/14/100201173/",
"keywords" : ["理研","京","富士通","SPARC","64","X","TMSC"],
"summary" : "理化学研究所は2014年10月1日、理研のスパコン「京」の後継となるエクサ級（1エクサFLOPS前後）のスパコンについて、
基本設計を共同で行う事業者として富士通を選定したと発表した。",
"memo" : ""}'
);
INSERT 0 1
sample=# INSERT INTO articlej_tbl (onCreate,onUpdate,contents) VALUES (
now(),
now(),
'{"title" : "火星ミッション模擬実験、参加者に聞く",
"category" : ["Space","Planet"],
"format" : "PDF",
"author" : "Kelly McMillan",
"publisher" : "National Geographic",
"date" : "2014/10/16",
"url" : "http://www.nationalgeographic.co.jp/news/news_article.php?file_id=20141016003",
"keywords" : ["火星","ミッション","NASA"],
"summary" : "NASAの支援により、ハワイ大学マノア校が実施する宇宙探査模擬実験プロジェクト「HI-SEAS」（Hawaii Space Exploration Analog and Simulation）の一環で、
火星の宇宙ステーションでの生活を模した実験が始まる。",
"memo" : ""}'
);
INSERT 0 1
sample=#

↑

検索 †

↑

select * from articlej_tbl; †

キーが同じ順番にソートされているのと、改行やスペースがなくなっていることがわかる

↑

select id from articlej_tbl where contents @> '{"format" : "HTML"}'; (JSONの要素で絞込) †

sample=# select id from articlej_tbl where contents @> '{"format" : "HTML"}';
 id
----
  1
(1 row)

sample=# select id from articlej_tbl where contents @> '{"format" : "PDF"}';
 id
----
  2
(1 row)

where '{"format" : "HTML"}' <@ contents; とも書ける

↑

select '{"a":1, "b":2}'::jsonb ? 'b'; (Key を含むか?) †

sample=# select '{"a":1, "b":2}'::jsonb ? 'b';
 ?column?
----------
 t
(1 row)

sample=# select '{"a":1, "b":2}'::jsonb ? 'c';
 ?column?
----------
 f
(1 row)

↑

select '{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'd']; (b, d いずれかの key を含む) †

sample=# select '{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'd'];
 ?column?
----------
 t
(1 row)

↑

select '{"a":1, "b":2, "c":3}'::jsonb ?& array['b', 'd']; (b, d どちらの key を含む) †

sample=# select '{"a":1, "b":2, "c":3}'::jsonb ?& array['b', 'd'];
 ?column?
----------
 f
(1 row)

↑

現時点では、JSONB の比較演算子 (Operator) は、@>、<@、?、?|、?& だけ †

将来的には、JSQuery が使えるようになるはず
- Alexander Korotkov さんの PostgreSQL Plugin https://github.com/akorotkov/jsquery
JSONB の演算子は少ないので、次章の要素取り出しを利用して副問い合わにして、複雑な検索を実現する ... というか、そんなことしたら実用的な速度がでないので、@> だけで済むような要件のデータストアに JSONB を使う

↑

JSONからの要素の取り出し †

↑

select contents->'keywords' from articlej_tbl; (JSON から JSON オブジェクトを取り出す) †

sample=# select contents->'keywords' from articlej_tbl;
                          ?column?
------------------------------------------------------------
 ["理研", "京", "富士通", "SPARC", "64", "X", "TMSC"]
 ["火星", "ミッション", "NASA"]
(2 rows)

↑

select contents->'keywords'->1 from articlej_tbl; (JSON 配列から n 番目の要素オブジェクトを取り出す) †

sample=# select contents->'keywords'->1 from articlej_tbl;
     ?column?
-------------------
 "京"
 "ミッション"
(2 rows)

↑

select contents->>'keywords' from articlej_tbl; (JSON から TEXT を取り出す) †

sample=# select contents->>'keywords' from articlej_tbl;
                          ?column?
------------------------------------------------------------
 ["理研", "京", "富士通", "SPARC", "64", "X", "TMSC"]
 ["火星", "ミッション", "NASA"]
(2 rows)

sample=# select pg_typeof(contents->>'keywords') from articlej_tbl;
 pg_typeof
-----------
 text
 text
(2 rows)

sample=# select pg_typeof(contents->'keywords') from articlej_tbl;
 pg_typeof
-----------
 jsonb
 jsonb
(2 rows)

contents->>'keywords' は TEXT なので、contents->>'keywords'->2 はエラーになります

↑

select contents->'keywords'->>1 from articlej_tbl; (JSON 配列から n 番目の TEXT を取り出す) †

sample=# select contents->'keywords'->>1 from articlej_tbl;
    ?column?
-----------------
 京
 ミッション
(2 rows)

↑

select contents#>'{keywords,1}' from articlej_tbl; (JSON からパス指定で JSON オブジェクトを取り出す) †

sample=# select contents#>'{keywords,1}' from articlej_tbl;
     ?column?
-------------------
 "京"
 "ミッション"
(2 rows)

↑

select contents#>>'{keywords,1}' from articlej_tbl; (JSON からパス指定で TEXT を取り出す) †

sample=# select contents#>>'{keywords,1}' from articlej_tbl;
     ?column?
-------------------
 "京"
 "ミッション"
(2 rows)

Computer