このエントリーをはてなブックマークに追加

概要

 「Apacheのアクセスログをタブ区切りのTSV形式に変換」でApacheのアクセスログを TSV形式(タブ区切り)のファイルに変換しました。 このTSV形式のファイルからPostgreSQLのテーブルにデータをコピーします。 今回はPostgreSQL 10がインストールされている環境にデータベース、スキーマ、テーブルを作成した後に、 作成したテーブルにデータをコピーします。

構成

検証環境

 サーバにはAmazonのEC2インスタンスを利用しました。 AmazonマシンイメージはAWS標準のものではなく、AWS Marketplaceから取得しています。 サーバのスペックは以下のとおりです。

■サーバスペック
項目内容
AmazonマシンイメージCentOS 7 (x86_64) - with Updates HVM
インスタンスタイプt2.micro
vCPU1
メモリ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

手順

(1) データベースの作成

 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=#

(2) スキーマの作成

 作成した 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

(3) 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)
);

(4) 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=>

(5) TSVファイルからテーブルにデータをコピー

 メタコマンドの \COPY を使用してローカルディスク上にある TSVファイルからPostgreSQLのテーブルにデータをコピーします。

loganalysis=> \COPY sakura_access_log FROM '/data/log/access_log_20190901.tsv' WITH DELIMITER E'\t';
COPY 10369 ←10,369行のデータがテーブルにコピーされた

(6) テーブルのデータ件数を確認

 テーブルに入っているデータ件数を確認して、データコピー時に表示されたデータ件数と同じであることを確認します。

loganalysis=> SELECT COUNT(*) FROM sakura_access_log;
 count
-------
 10369 ←テーブルには10,369件のデータが入っている
(1 row)

loganalysis=>

(7) テーブルに入っているデータの確認

 次はデータ件数だけでなく、テーブルに入っているデータの中身を確認します。 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
$

参考にしたサイト