PostgreSQL 10.6のテーブルにTSVファイルからデータをコピーする
カテゴリ：OSSセットアップ | ソフトウェア：PostgreSQL | タグ：
最終更新日：2021/01/01 | 公開日：2019/10/25
目次
概要
「Apacheのアクセスログをタブ区切りのTSV形式に変換」でApacheのアクセスログを TSV形式（タブ区切り）のファイルに変換しました。 このTSV形式のファイルからPostgreSQLのテーブルにデータをコピーします。 今回はPostgreSQL 10がインストールされている環境にデータベース、スキーマ、テーブルを作成した後に、 作成したテーブルにデータをコピーします。
構成
検証環境
サーバにはAmazonのEC2インスタンスを利用しました。 AmazonマシンイメージはAWS標準のものではなく、AWS Marketplaceから取得しています。 サーバのスペックは以下のとおりです。
■サーバスペック
| 項目 | 内容 |
|---|---|
| Amazonマシンイメージ | CentOS 7 (x86_64) - with Updates HVM |
| インスタンスタイプ | t2.micro |
| vCPU | 1 |
| メモリ | 1GiB |
| ディスク | SSD 8GiB |
| リージョン | アジアパシフィック (東京) |
サーバ構成
OSバージョン
CentOS Linux release 7.5.1804 (Core)
ソフトウェア・パッケージ一覧
- postgresql10-libs-10.6-1PGDG.rhel7.x86_64.rpm
- postgresql10-server-10.6-1PGDG.rhel7.x86_64.rpm
- postgresql10-10.6-1PGDG.rhel7.x86_64.rpm
手順
事前準備
データベースの作成
PostgreSQLのスーパーユーザと紐づいたOSユーザ(postgres)でpsqlを実行します。 loganalysis という名前のデータベースを作成します。 今回テーブルにコピーするデータはApacheのアクセスログで、 このアクセスログには原則としてマルチバイト文字は含まれませんが、 バックスラッシュ(\)は含まれる可能性があります。 エンコーディングとしてデフォルトのUTF-8を採用した場合、データ内にバックスラッシュが 含まれるとエスケープシーケンスと誤認されて正しくデータがコピーされない可能性があります。 この問題に対応するために、シングルバイトのエンコーディングであるSQL_ASCIIを指定します。 データベースオーナーはスーパーユーザ（postgres）ではない一般ユーザの pguser01 を指定します。
# su - postgres
Last login: Sat Sep 14 11:50:57 UTC 2019 on pts/0
-bash-4.2$ psql
psql (10.6)
Type "help" for help.
postgres=# CREATE DATABASE loganalysis OWNER pguser01 TEMPLATE template0 ENCODING 'SQL_ASCII' LC_COLLATE 'C' LC_CTYPE 'C';
CREATE DATABASE
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-------------+----------+-----------+---------+-------+-----------------------
loganalysis | pguser01 | SQL_ASCII | 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
test_db | pguser01 | UTF8 | C | C |
(5 rows)
postgres=#
スキーマの作成
作成した loganalysis データベースに接続してスキーマを作成します。 作成するスキーマ名は、なんとなくOracleに倣ってユーザ名と同一にします。 （当然スキーマ名は何でもいいです） 初期状態で存在している public スキーマは不要なので削除します。
postgres=# \c loganalysis
You are now connected to database "loganalysis" as user "postgres".
loganalysis=# \dn
List of schemas
Name | Owner
--------+----------
public | postgres
(1 row)
loganalysis=# CREATE SCHEMA pguser01 AUTHORIZATION pguser01;
CREATE SCHEMA
loganalysis=# DROP SCHEMA public;
DROP SCHEMA
loganalysis=# \dn
List of schemas
Name | Owner
----------+----------
pguser01 | pguser01 ←作成された
(1 row)
loganalysis=# \q
-bash-4.2$ exit
logout
DDLの作成
テーブルを作成するためのDDLを作成します。 サイズが異常に大きいカラムがあるのは、Apacheへの不正アクセスによって出力されたログを格納するためです。 不正アクセスの際には、異常に長いURLやリファラ、ユーザエージェントがログとして出力されていましたので、 その長いデータが入るカラムサイズを指定しています（実績を元にサイズを決めています）。
# su - pguser01 $ cd /data/sql/ $ vi sakura_access_log.ddl
ファイル名：/data/sql/sakura_access_log.ddl
※ファイルを新規作成※
CREATE TABLE sakura_access_log (
server_name VARCHAR(30),
remote_hostname VARCHAR(200),
time VARCHAR(30),
request_method VARCHAR(20),
request_uri VARCHAR(8000),
status INTEGER,
response_size INTEGER,
referer VARCHAR(9000),
user_agent VARCHAR(4000)
);
DDLを実行してテーブルの作成
データベースのオーナーである pguser01 ユーザで loganalysis データベースに接続します。 PostgreSQLのメタコマンドの \i を使用してDDLを実行し、sakura_access_log テーブルを作成します。 テーブル作成後はテーブルの一覧とテーブルの構成情報を表示して、正常にテーブルが作成されていることを確認します。
$ psql -U pguser01 loganalysis Password for user pguser01: ←パスワードを入力 psql (10.6) Type "help" for help. loganalysis=> \i /data/sql/sakura_access_log.ddl ←DDLを実行 CREATE TABLE loganalysis=> \dt ←テーブルの一覧を表示 List of relations Schema | Name | Type | Owner ----------+-------------------+-------+---------- pguser01 | sakura_access_log | table | pguser01 ←テーブルが作成された (1 row) loganalysis=> \d sakura_access_log ←作成したテーブルの構成情報を表示 Table "pguser01.sakura_access_log" Column | Type | Collation | Nullable | Default -----------------+-------------------------+-----------+----------+--------- server_name | character varying(30) | | | remote_hostname | character varying(200) | | | time | character varying(30) | | | request_method | character varying(20) | | | request_uri | character varying(8000) | | | status | integer | | | response_size | integer | | | referer | character varying(9000) | | | user_agent | character varying(4000) | | | loganalysis=>
データコピー
TSVファイルからテーブルにデータをコピー
メタコマンドの \COPY を使用してローカルディスク上にある TSVファイルからPostgreSQLのテーブルにデータをコピーします。
loganalysis=> \COPY sakura_access_log FROM '/data/log/access_log_20190901.tsv' WITH DELIMITER E'\t';
COPY 10369 ←10,369行のデータがテーブルにコピーされた
事後確認
テーブルのデータ件数を確認
テーブルに入っているデータ件数を確認して、データコピー時に表示されたデータ件数と同じであることを確認します。
loganalysis=> SELECT COUNT(*) FROM sakura_access_log;
count
-------
10369 ←テーブルには10,369件のデータが入っている
(1 row)
loganalysis=>
テーブルに入っているデータの確認
次はデータ件数だけでなく、テーブルに入っているデータの中身を確認します。 WHERE句を指定して適当にデータを絞ってますが任意です。
loganalysis=> SELECT * FROM sakura_access_log WHERE server_name = 'ossfan.net';
server_name | remote_hostname | time | request_method | request_uri | status | response_size | referer | user_agent
-------------+---------------------------------------------------------+----------------------------+----------------+---------------------------------------------------------------------------------------------------------------------------------+--------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ossfan.net | ip-54-36-150-xx.a.ahrefs.com | 01/Sep/2019:00:01:10 +0900 | GET | /category/kubernetes.html | 200 | 15636 | - | Mozilla/5.0 (compatible; AhrefsBot/6.1; +http://ahrefs.com/robot/)
ossfan.net | kd1211091700xx.ppp-bb.dion.ne.jp | 01/Sep/2019:00:02:31 +0900 | GET | /rss.xml | 200 | 8022 | - | Tiny Tiny RSS/17.12 (0cd4a88) (http://tt-rss.org/)
ossfan.net | 46.229.168.xxx | 01/Sep/2019:00:02:39 +0900 | GET | /category/jenkins.html | 200 | 13763 | - | Mozilla/5.0 (compatible; SemrushBot/6~bl; +http://www.semrush.com/bot.html)
ossfan.net | crawl-66-249-79-xxx.googlebot.com | 01/Sep/2019:00:03:12 +0900 | GET | /memo/archives/2010/06/24190219.html | 200 | 21145 | - | Mozilla/5.0 (Linux; Android 6.0.1; Nexus 5X Build/MMB29P) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/41.0.2272.96 Mobile Safari/537.36 (compatible; Googlebot/2.1; +http://www.google.com/bot.html)
ossfan.net | 46.229.168.xxx | 01/Sep/2019:00:03:16 +0900 | GET | /setup/tips-09.html | 200 | 23854 | - | Mozilla/5.0 (compatible; SemrushBot/6~bl; +http://www.semrush.com/bot.html)
ossfan.net | 58-189-99-xxxf1.shg1.eonet.ne.jp | 01/Sep/2019:00:03:17 +0900 | GET | /setup/linux-17.html | 200 | 26807 | https://www.google.co.jp/ | Mozilla/5.0 (iPhone; CPU iPhone OS 12_4 like Mac OS X) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/12.1.2 Mobile/15E148 Safari/604.1
ossfan.net | 58-189-99-xxxf1.shg1.eonet.ne.jp | 01/Sep/2019:00:03:17 +0900 | GET | /images/nav/nav-news.gif | 200 | 2048 | http://ossfan.net/setup/linux-17.html | Mozilla/5.0 (iPhone; CPU iPhone OS 12_4 like Mac OS X) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/12.1.2 Mobile/15E148 Safari/604.1
ossfan.net | 58-189-99-xxxf1.shg1.eonet.ne.jp | 01/Sep/2019:00:03:17 +0900 | GET | /images/nav/nav-install.gif | 200 | 2147 | http://ossfan.net/setup/linux-17.html | Mozilla/5.0 (iPhone; CPU iPhone OS 12_4 like Mac OS X) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/12.1.2 Mobile/15E148 Safari/604.1
（以下省略）
loganalysis=> \q
$