１．はじめに
だいぶ前に書いたものが下書きに残っていたので投稿。
以前、全文検索について調べていた時に調査したものを備忘録として残します。
本稿はPostgreSQLの全文検索のやり方をまとめています。
２．前準備
2.1. 環境について
OS：CentOS7.9
DB：PostgreSQL9.6（develも必要。バージョンも合わせること）
※gccやcmakeが必要になりますので事前に入れてください。
その他、エラーが出たら必要に応じてインストールしてください。
2.2. pgroongaに必要なモジュールのインストール
pgroongaをインストールする際に必要になります。gitからダウンロードしましょう。
# git clone -b c_master https://github.com/msgpack/msgpack-c.git
# cd msgpack-c/
# cmake .
# make
# make install
# yum install -y https://packages.groonga.org/centos/7/groonga-release-latest.noarch.rpm
# yum install -y --enablerepo=epel groonga
# yum install -y groonga-devel
３．PostgreSQLの全文検索モジュールのインストール
3.1. pgroongaのインストール
# wget https://packages.groonga.org/source/pgroonga/pgroonga-2.2.8.tar.gz
# tar xvf pgroonga-2.2.8.tar.gz
# cd pgroonga-2.2.8
# echo 'export PKG_CONFIG_PATH=/usr/local/lib/pkgconfig/:/usr/lib64/pkgconfig/' >> ~/.bashrc
# make HAVE_MSGPACK=1
# make
# make install
エラーになる場合は以下のことを確認してください。
①pg_configコマンドが有効であること。
有効である場合は以下のような情報が表示されます。
[root@2e9dcb1b3a5d pgroonga-2.2.8]# pg_config
BINDIR = /usr/pgsql-9.6/bin
DOCDIR = /usr/pgsql-9.6/doc
HTMLDIR = /usr/pgsql-9.6/doc/html
INCLUDEDIR = /usr/pgsql-9.6/include
PKGINCLUDEDIR = /usr/pgsql-9.6/include
INCLUDEDIR-SERVER = /usr/pgsql-9.6/include/server
LIBDIR = /usr/pgsql-9.6/lib
PKGLIBDIR = /usr/pgsql-9.6/lib
LOCALEDIR = /usr/pgsql-9.6/share/locale
MANDIR = /usr/pgsql-9.6/share/man
SHAREDIR = /usr/pgsql-9.6/share
SYSCONFDIR = /etc/sysconfig/pgsql
PGXS = /usr/pgsql-9.6/lib/pgxs/src/makefiles/pgxs.mk
CONFIGURE = '--enable-rpath' '--prefix=/usr/pgsql-9.6' '--includedir=/usr/pgsql-9.6/include' '--mandir=/usr/pgsql-9.6/share/man' '--datadir=/usr/pgsql-9.6/share' '--libdir=/usr/pgsql-9.6/lib' '--with-perl' '--with-python' '--with-tcl' '--with-tclconfig=/usr/lib64' '--with-openssl' '--with-pam' '--with-gssapi' '--with-includes=/usr/include' '--with-libraries=/usr/lib64' '--enable-nls' '--enable-dtrace' '--with-uuid=e2fs' '--with-libxml' '--with-libxslt' '--with-ldap' '--with-selinux' '--with-systemd' '--with-system-tzdata=/usr/share/zoneinfo' '--sysconfdir=/etc/sysconfig/pgsql' '--docdir=/usr/pgsql-9.6/doc' '--htmldir=/usr/pgsql-9.6/doc/html' 'CFLAGS=-O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic' 'LDFLAGS=-Wl,--as-needed'
CC = gcc
CPPFLAGS = -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include
CFLAGS = -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic
CFLAGS_SL = -fPIC
LDFLAGS = -Wl,--as-needed -L/usr/lib64 -Wl,--as-needed -Wl,-rpath,'/usr/pgsql-9.6/lib',--enable-new-dtags
LDFLAGS_EX =
LDFLAGS_SL =
LIBS = -lpgcommon -lpgport -lselinux -lxslt -lxml2 -lpam -lssl -lcrypto -lgssapi_krb5 -lz -lreadline -lrt -lcrypt -ldl -lm
VERSION = PostgreSQL 9.6.20 #ここ結構重要。サーバーのバージョンと異なる（9.2等）場合はdevelのバージョンを合わせてください。
②pkg-config --list-allにgroongaが含まれていること。
存在しない場合、項番3.1.「pgroongaのインストール」の手順を見直してみてください。
3.2. DB作成
全文検索用のDBを作成します。
既存のDBを全文検索化したい場合は飛ばしていただいて結構です。
create database dev2
template template0
encoding 'utf-8'
lc_collate 'ja_JP.utf-8'
lc_ctype 'ja_JP.utf-8';
※DB名やオーナー等は自由に変えていただいて問題ありません。ご自身の環境に合わせてください。
3.3. pgroongaの有効化
全文検索したいデータベースへログインして以下のSQLを発行します。
CREATE EXTENSION pgroonga;
以上でDB側の全文検索の準備完了は完了です。
４．テーブル準備
今回は住所.jpさんにいいデータがそろっていましたので、こちらを使っていきます。
4.1. テーブル作成
住所データを格納するためのテーブルを作成するため、以下のSQLを発行します。
カラム名はそのまま直訳しました。
address_cd(住所CD)がプライマリーキーなので設定します。これは今回使わないのでなくてもいいです。
カラムの属性はすべてtextで作成します。全文検索は基本的にtext型が対象になります。
create table address (
address_cd text
,prefecture_cd text
,city_cd text
,town_cd text
,postal_code text
,office_flag text
,discontinued_flag text
,prefecture text
,prefecture_kana text
,city_town_village text
,city_town_village_kana text
,town_area text
,town_area_kana text
,town_area_supplement text
,kyoto_street_name text
,aza_chome text
,aza_chome_kana text
,supplement text
,office_name text
,office_name_kana text
,office_address text
,new_address_cd text
,constraint index_address primary key(
address_cd
)
);
4.2. CSVインポート
住所.jpからダウンロードしたCSV「zenkoku.csv」をインポートします。
中身はsjisだったのでencodingにsjisを指定します。
copy address from '/tmp/zenkoku.csv' delimiter ',' csv header encoding 'sjis';
4.3. インデックス作成
事業者名が扱いやすいので、これに対してインデックスを作成します。
create index pgroonga_index_address_office_name
on address
using pgroonga (office_name);
4.4. 検証
インデックスを作成したカラムに対して「会社」という単語で全文検索してみます。
pgroonga_scoreはスコアです。よく全文検索系のアプリにあるTerm Frequencyと呼ばれるものです。何個そのワードが含まれていたかを表します。
「&@」は全文検索用演算子になります。他には「&@~」「LIKE」「ILIKE」があります。詳しく知りたい場合はこちらをご覧ください。
select
pgroonga_score(tableoid, ctid) AS score
,*
from address
where office_name &@ '会社'
order by 1 desc
以上で全文検索の紹介を終わります。