2016-11-08

AWS : Fluentdを使ってEC2からApacheのログをS3へ

月花です。
久しぶりの技術記事です。

最近AWSを触っていて、わかりにくいなあと思ったS3との連携について。

AWSでは、障害が起こったりAutoScalingしてたりすると、EC2インスタンスが勝手にシャットダウンされてしまう。
このとき、ログが吹っ飛ぶのでS3へ逃しておく必要がある。

前提

WebサーバとしてのEC2が複数
OSはAmazon Linux
Apache
S3バケットはサーバ分作る

やりたいこと

Apacheのアクセス/エラーログを両方S3へ逃す
設定は軽いほうがいい
自動でやってほしい
{server_name}/access_log/{YYYY}/{MM}/{DD}/access_log_{YYYY}-{MM}-{DD}_{HH}.log みたいにしてほしい

アプローチ

まずはどのみちS3へ転送が必要なため、EC2インスタンスそれぞれにS3フルアクセス権限を付与する。

これで、S3への操作権限を得た。このままcpコマンドでコピーすることもできるが、勝手にシャットダウンされて吹っ飛ぶので極めて短い間隔のcronかなにかでcpすることになる。
しかも、都度時間を見て、ディレクトリなければ作るなんてコード書くのめんどくさいし非効率。

そこで Fluentd を用いて、自動化してもらう。
こういうのを、Web Storage Archiveパターンと呼ぶらしい。
CDP:Web Storage Archiveパターン - AWS-CloudDesignPattern

S3へのアクセス権限を与える

まずはIAMロール・・・ではなく、既にEC2インスタンスは動いてしまっているので、IAMユーザを作成する。
サーバ分作ったら（1つでもいいのかしら）、アクセスキーとかをメモしておく。

下記の「AWS CLI を使用してロールを切り替えるには」を参考に、起動中EC2インスタンスに接続し、ユーザを割り当てる。
docs.aws.amazon.com

$ aws configure
AWS Access Key ID [None]: your_key_id
AWS Secret Access Key [None]: your_sec_key
Default region name [None]: ap-northeast-1
Default output format [None]: json

これでアクセス権限を付与できた。
Amazon LinuxにはデフォルトでAWS CLIという、AWSサービスをいじるためのクライアントが搭載されており、S3関連のコマンドは以下の記事に詳しい。
www.task-notes.com

なお、再起動は必要ないので、そのまま叩いてみて、成功なら成功である。失敗したら頑張れ。

NTPを設定する

Amazon LinuxのデフォルトはUTCなので、設定してないと、Fluentdくんが作るファイルを時刻を使って階層化するとき、めちゃくちゃになる。
docs.aws.amazon.com

インスタンスのセキュリティグループのルールでは、ポート 123 (NTP) でアウトバウンド UDP トラフィックを許可する必要があります。ネットワーク ACL のルールでは、ポート 123 でインバウンドとアウトバウンドの UDP トラフィックを両方許可する必要があります。

筆者はこれを忘れてハマった。

Fluentd のインストール

公式ページ
docs.fluentd.org
を参考に、とりあえず入れるだけ入れる。

$ curl -L https://toolbelt.treasuredata.com/sh/install-redhat-td-agent2.sh | sh

こういうインストール方法、めちゃめちゃ助かる。

素のままでも使いやすいっぽいのだが、やりたいことがあるので、Fluentdのプラグインをいれる。
github.com

$ /usr/sbin/td-agent-gem install fluent-plugin-forest

Fluentd の設定

どのファイルをどれくらいの間隔で、どんな名前でどこに転送するのかを定義する。
その定義ファイルは、以下にある。

/etc/td-agent/td-agent.conf

まずは、source節を編集して、どのファイルを対象とするかを定義する。
なお、基本的に末尾に追記で問題ない。

<source>
  @type tail
  format apache
  path /var/log/httpd/access_log
  pos_file /var/log/td-agent/httpd.access.log.pos
  tag s3.httpd.access
</source>

ここでは例として、Apacheのアクセスログを対象とする。

type	tailにするとtailコマンドで末尾をフォローしてくれる。このタイプによってオプションが変わる。ここから先は全部tailが対象のオプション
format	対象とするログのフォーマットを選択する*1
path	対象のログへのフルパス
pos_file	前回どこまで読んだかが書いてある栞ファイル。Fluentdが勝手に生成するので、わかりやすい名前にしておく
tag	ソースごとにタグ付けが行える

このあと、エラーログを見るsourceも書いておいた。
タグは s3.httpd.error とした。

次に、さきほどのソースを、どれくらいの間隔で、どんな名前で、どこに転送するのかを定義するために、match節を記述する

<match s3.*.*>
  type forest
  subtype s3
  <template>
    aws_key_id your_key_id
    aws_sec_key your_sec_key
    s3_bucket bucket_name
    s3_region ap-northeast-1
    path ${tag_parts[1]}/${tag_parts[2]}_log/
    buffer_path /var/log/td-agent/s3/${tag_parts[2]}_log
    time_slice_format %Y/%m/%d/${tag_parts[2]}_log_%Y-%m-%d_%H
    time_slice_wait 10m
    #flush_interval 3s
    buffer_chunk_limit 256m
  </template>
</match>

match	タグに対応している。このmatch節はどのソースに使うのか、ここで定義する
type	さきほどインストールしたプラグインを使うのでforestとする
subtype	S3に投げるため。この辺は用途によって決まってくる
aws_key_id	IAMユーザのキーID
aws_sec_key	IAMユーザのシークレットキー
s3_bucket	S3バケット名。なければ作ってくれる
s3_region	S3のあるリージョン。例では東京
path	バケット直下からの共通のパス。forestによって、${tag_parts}が有効になっている。matchのところで指定した、*の部分が配列になって1から順に収まっている。このおかげで、複数のソースに対して1つのmatchで済むようになる
buffer_path	S3へ転送する前のバッファファイル。わかりやすい名前にしておく
time_slice_format	生成したいファイル名。年月日とかはforestがなくても取れるから階層化しておこう
time_slice_wait	S3へ転送する間隔
flush_interval	こいつがあると、上記の間隔より優先される。例ではコメントアウトしているが、3秒なのでテスト向け
buffer_chunk_limit	指定したサイズを超えたバッファファイルはエンキューされて、次のバッファファイルが作られる

Fluentd を起動してみる

いつものやつで起動して、ログを見てみる。

$ chkconfig td-agent on
$ service td-agent start
$ td-agent td-agent:                                         [  OK  ]
$ cat /var/log/td-agent/td-agent.log

起動はしたが、エラーがでていた。

2016-11-08 17:13:58 +0900 [error]: Permission denied @ rb_file_s_stat - /var/log/httpd/access.log
2016-11-08 17:13:58 +0900 [error]: suppressed same stacktrace

出たー、Permissionだ！！！！
ありがちなハマり。

多少強引だが、他の設定をいじらなくてよいので、Fluentd の実行ユーザをrootにしてしまおう。
kenzo0107.hatenablog.com

TD_AGENT_USER=td-agent 
TD_AGENT_GROUP=td-agent

↓ここをこう↓

TD_AGENT_USER=root
TD_AGENT_GROUP=root

restartしてログを見てみると、

2016-11-08 18:51:54 +0900 [info]: listening fluent socket on 0.0.0.0:24224
2016-11-08 18:51:54 +0900 [info]: listening dRuby uri="druby://127.0.0.1:24230" object="Engine"
2016-11-08 18:51:54 +0900 [info]: following tail of /var/log/httpd/access_log
2016-11-08 18:51:54 +0900 [info]: following tail of /var/log/httpd/error_log

ということで、無事にログファイルがフォローされた。

S3を確認

AWSのサイトでバケットを見に行こう。

今回の例では

すべてのバケット /bucket_name/httpd/access_log/2016/11/08/access_log_2016-11-08_17_2.gz

という具合になって、終わり。

*1:一覧 : docs.fluentd.org

2016-10-06

飯テロ : 料理を撮るのに5秒以上かけてはならない

飯テロ

月花です。

もうだいぶ前から、表題のようなポリシーのもとで飯テロを実施しています。
本当は3秒に納めたいのですが「4秒以上」だと語呂が悪く「3秒以上」だとセンセーショナルではあるが、実際厳しいのでこの表題になりました。

ここでは、私の飯テロにおけるポリシーを紹介します。

以下の目次でお送りします。

なぜ5秒なのか
5秒に収める理由
メリット・デメリットとは
動画は例外である
さいごに

なぜ5秒なのか

昨今、スマホのカメラ性能が爆上がりしていて、3秒に収まるからです。

iPhone5ではピントの合わせにかかる時間が微妙ではあったのですが、iPhone6にもなれば瞬間で合うため、カメラアプリさえ立ち上がればすぐ撮れます。
カメラアプリの起動はiOS10になってから、コントロールセンターにアクセスしなくてもロック画面で左フリックをするだけで起動します。
こちらの方法であれば、横持ちに変える動作と並行することができ、さらなるタイム短縮ができるでしょう。

5秒に収める理由

席に料理が到達した瞬間が、その料理にとっての最高の一瞬だからです。

料理というものは、席に到着した瞬間、2つの最高ポイントを持っています。
つまり、

味
見た目

です。

味に関しては言わずもがな、出来たてを食べるのが美味しいに決まっています。ホイル焼きを席でちょっと蒸らせみたいなやつは、そのホイルを誰かが開ける瞬間が最高点です。
見た目は誰も手を付けなければ崩れませんが、重力に負けることはありますし、写真を撮らない人間を待たせるわけにはいきません。

なので、席についていい感じの場所においたら、秒で決めましょう。
前の章の通りにやれば、秒で決められますね？
そしてここが重要なのですが、Twitterの誘惑を断ち切り、スリープ状態にする時間も惜しみ、できるだけ早く食器を持って確保に向かいましょう。

今、出来る限り最高の瞬間の料理を食べようとしています。
この状態にある人間を待たせる飯テロリストは時間がかかりすぎです。
なので自分も誰かを待たせないよう、秒でキメましょう。

メリット・デメリットとは

メリットとしては、その料理の最高の瞬間を体験できることが一番大きいと考えています。
また、写真を撮らない友人・恋人を待たせておく時間を最小限にできます。
さらに、すぐにスマホをいじりつづけないことで、会話がしやすくなります。誰かまごついている人が居たとしても、すぐに写真をキメたか撮らなかった人間は会話で間をもたせることができるのです。
撮った後全員がツイートのためにスマホをいじっていれば、その空間は料理が冷めていくばかりで会話もない完全な無が広がるばかりです。これだけは絶対に避けたいところですよね。

デメリットとしてはまず、飯テロの速報性が失われることが大きいです。
また、友人と一緒に写真を上げることで、だれだれと一緒に来ていますアピールができますが、このチャンスがなくなります。
しかし、速報性に関しては、友人間で出遅れるだけで、3口ほど味わって食べてからやればいいです。フォロワーにとってはさほど影響ありません。
本当に美味しかった場合、ツイートを忘れてしまうこともありますが、大丈夫です。飯テロのゴールデンタイムは深夜なので、持って帰って派手にやりましょう。

友人と一緒に来ていますアピールは、複数のやり方があります。
終わってから、今日はだれだれとどこそこに行きました♡スタイルや、引用ツイート、タグ付け、などなど時間に縛られない方法がいくらでも用意されています。

このことから、デメリットは工夫次第で解消できるといえます。

動画は例外である

動画は例外です。実際に無理ということもありますし、かといって動画は録りたいですよね。

では、動画が必要な料理の状態を整理してみましょう。

煮えたぎっていて、その状態を良しとする鍋などの料理
煮えたぎっていて、冷めるまで食べられない麻婆豆腐などの料理
セルフスタイルの焼肉やお好み焼き
店員が仕上げまたはパフォーマンスをしている最中

これくらいではありませんか？
このような状態にあっては、すぐ食べることができなかったり、多少放っておいても劣化することはありません。
であれば、ゆっくり動画を撮る余裕があります。

静止した状態の料理では、3秒より長い時間同じ料理が写っている状態というのは、間延びし過ぎではないかと思っています。
わざと手ブレをさせるvineなどを使った技については、そもそも短い時間の動画であることが前提のため、表題と照らし合わせば、そう悪くはないはずです。

さいごに

いかがだったでしょうか。
最高の飯テロと最高の料理、最高の友人たちで幸せな時間を過ごす秘訣は、これです。
酔うなどのアクシデントにより、私自身100%はできていませんが、出来る限り心がけています。

さて、私が所属する同人音楽サークル dat file records では、同人音楽を中心に据えながらも、飯テロリストとして貪欲な活動をしております。
過去には、飯テロE.P.という、飯テロコンピレーションを発表しています。

いよいよ今週末に迫りました東方紅楼夢では、二色蓮花蝶アレンジオンリーコンピ、「ここに神社を建てよう」を頒布いたします。
スペースはた-24b でございます。当日私は霊夢のコスをして、売り子をしています。

また、11/19には、過去東京で行われた IzanagiDistribution が大阪日本橋BAR Guildにて再起動いたします。
私はこの IzanagiDistribution Ver.2.0 の特設サイトのデザイン・コーディングを担当いたしました。
こちらでのコスは未定ですが、スタッフとして参加致します。

この記事が皆様の最高の飯テロライフの参考になれば幸いです。

2016-09-21

NetBeans : 既存のプロジェクトをBitBucketにpushする覚書

よくBitBucketでプロジェクト作るの忘れたまま書き始めるので覚え書き。

既存のプロジェクトの初期化

まず、NetBeans上の既存プロジェクトのgit初期化をする。
プロジェクトを選択して、チーム > Git > リポジトリの初期化
で、ダイアログが出るのでパスを確認してOKならOK。

gitの構成をBitBucket用に設定する

次に、BitBucketにリポジトリを作成し、リポジトリ名を控えておく。
仮にログインユーザ名をuser、リポジトリ名をtestProjectとする。

NetBeansでリポジトリの初期化が済んだプロジェクトを選択し、チーム > リポジトリ > 構成を開くを選択すると、エディタでconfigファイルが開かれる。
ここに、下記のフォーマットをコピペする。

[pack]
	buildbitmaps = false
[core]
	repositoryformatversion = 0
	filemode = true
	logallrefupdates = true
	precomposeunicode = true
	bare = false
[remote "origin"]
	url = https://user@bitbucket.org/user/testProduct.git
	fetch = +refs/heads/*:refs/remotes/origin/*
[branch "master"]
	remote = origin
	merge = refs/heads/master
[user]
	name = Name
	email = Email

このうち、[remote "origin"]と、[user]を改変する必要がある。

ここでついでに設定を書き換えてもいいし、NetBeans上とかコマンドで設定してもいい。
あとはこのままcommitしてpushするだけ。

2016-08-31

amazon : 動画をPS4で見る

筆者はプライム会員である。
話すとよくびっくりされるのだが、意外と安いことを話すと、みんなも意外だと思うらしい。
年会費で4桁は私の中では十分お手軽なサービスなのだ。

私がアマゾンで動画を見なかった理由

結論から言うと、PS4にネイティブアプリがあることを知らなかったのだ。
PS4で見るならブラウザで見るのかな、となんとなく思っていてちゃんと調べていなかったからだ。

私のPCはMac Book Airである。つまり、HDMI端子がない。
ところが、うちで一番いいディスプレイはThunderboltケーブルに対応していない。

なので、一番いいディスプレイはPS4のためだけにあり、なんならFF14専用と言っても差し支えない。
完全にFF14のためと思っているたため、MBAを使っているときはPS4は動いていなかった。
PS4を使っているときは、クラフターなので基本的にMBAはエリオネスを表示している。

この間、プライム会員の話をしたときに、ちゃんと調べようと思って調べたのだが、どうやらPS4でも専用アプリがあるようだった。
ちょうど、FF14から離れている時期で、PS4が完全に停止している状況だったのでちょうどよかった。

インストール

これはものすごく簡単だった。

メニューから、ディスプレイ型のアイコン（テレビ&ビデオ）を選択すると、下に各種動画配信サービスのアイコンが現れる。
それらは、アプリが存在するもので、好きなのを選んでクリック数回でインストールができた。

amazonの場合はここからもお手軽で、メールアドレスとパスワードを打ち、登録しているクレジットカードを選択すればそれで完了だった。
プライム会員ならばクレジットカードは登録してあるので、もろもろコミで一瞬で終わった。

そのぶん、パスワードはしっかりしてないといけないが。

何見たの？

孤独のグルメです。

2016-08-24

Elixir : エリクサーをとりあえずひとくち

Elixir プログラミング

ことはじめ記事の一歩前のやつです。

新しい言語を触りたい

筆者はこれまでに、Java・JavaScript・PHP・Python・Rubyを、書けると言えるくらい書いてきた。
CodeIQの初級問題レベルのトイプログラムならもっと多いのだが、まあここらへんは人によってボーダーが違うのでなんとも。

結果的にWebプログラミングに落ち着き、仕事ではPHPを使っている。

でもいいかげんPHPも飽きてきた。ここらで一発足りないやつをやろうということで、いろいろ考えてはいた。
Webデザインも研究中だし、個人Webページもあってもいいかも、いっそ安いvpsを遊び場サーバとして借りるか、となり、いよいよ現実味を帯びてきていた。
さて、Ruby on Rails・nodeJS・Scala ・・・って結局使ったことある言語じゃん！

このタイミングで、雑談中にある言語を教えてもらった。

それが Elixir（エリクサー）だった。
この言語はErlang（アーラン。削除さんの曲ではない）のVM上で動く言語であり、言語実装もErlangでなされている。
Erlang自体にも興味はあり、触ってみたかったところでいい出会いを果たすことができた。

しかもこのElixirのWebアプリケーションフレームワークとして、 Phoenix（フェニックス）が存在するらしい。
筆者は根っからのスクウェアファンであり、エリクサーとフェニックスという名前だけでもうやるしかないという結論に至った。

f:id:gekka9:20160824120039p:plain
Elixir
ロゴの紫が美しい。

Elixirとは

Elixir自体はこの記事が詳しい。

qiita.com

2012年に登場した、かなり新しい言語であるらしく、並行処理に長けている。
もともとのErlang自体はインフラのために作られた言語で、並行処理と障害耐性の観点で、有効になるような特徴を備えている。
だからネットユーザの増加に伴い、並行処理に移行していく流れの中で（TwitterがScalaに移行したように）、その選択肢として登場したということ・・・らしい。
ElixirはRuby on Railsの開発コアメンバーが開発した言語であり、Webアプリケーションの流れの中でのまさに正統な変遷であると考えていいのではないかと思う。

もうRuby on RailsとかnodeJSでウオオスゲエなんて言っていては遅すぎるし、なんなら私もElixirを知ること自体が既に遅すぎるくらいの激流であることに改めて気付かされた。
既に選択肢としてある程度の知識を持ち、常に提示できるようにしておかなければならない。

ということで、多少の焦りとともに、学んでいく。

とりあえず読みたい

プログラミングElixir

作者: Dave Thomas,笹田耕一,鳥井雪
出版社/メーカー: オーム社
発売日: 2016/08/19
メディア: 単行本（ソフトカバー）
この商品を含むブログを見る

技術書って現物があるとやる気が出ると思う。
確かにネットに記事はいっぱい転がってるんだけど、余計なサーフィンしちゃったり、断片化した情報を集めたりするのって時間がかかる。
あと、ネットでいくらか記事を見たら、この本の中のサンプルを引用しているところがいくつも見つかった。
じゃあもう買えばいいじゃん。
技術書買ったからにはやらないとね、というモチベーションも維持できる。

そしてこの本には、オブジェクト指向に慣れた体を徐々に関数型に慣らしていく過程を追体験できるというレビューがあり、その通りならばバチバチのオブジェクト指向プログラマの筆者に適すると考えたからだ。

続きは、これを読んでから。

2016-08-16

CakePHP : 同カラムに対する複数のLIKEを連結する

PHP PostgreSQL プログラミング CakePHP

前回の続き。

前回はPostrgreSQLから配列を取り出す方だったが、次はそれを使う方。

対象のテーブル

データ

product_id	code	tag	publisher
101	123456	書籍,技術書,Java	オライリー
102	234567	書籍,技術書,PHP	ソフトバンククリエイティブ
103	345678	書籍,技術書,PHP	ソフトバンクパブリッシング
104	456789	CD,アニメ,アイカツ	ランティス

NGフィルタ

target	filter_string
tag	CD
tag	Java
publisher	ソフトバンククリエイティブ

データに対して、NGフィルタをかけたい、というもの。
結果としては、

product_id	code	tag	publisher
103	345678	書籍,技術書,PHP	ソフトバンクパブリッシング

こうなることを想定する。

ストアドを使って一撃でやってもいいのだが、メンテが大変なので間にPHPを挟んで、NGフィルタの取得と、その結果をWHERE句に含めた取得クエリの2回発行することにする。

CakePHP のWHERE句指定方法

CakePHPでは、SQLそのものの作成はモデルクラスに任せることになっている。
このため、SELECT句やFROM句、WHERE句の配列をモデルクラスに渡すことまでがコントローラというロールの限界であり、これ以上のところに踏み込んではいけない。

だが、このWHERE句がややこしくてちょっとハマった。
今回のNGフィルタでは NOT LIKE 〜〜〜 AND NOT LIKE 〜〜〜 AND ・・・という風にANDで接続していけばいい。
この配列を作る。

結論から言うと、今回の場合求められる配列は以下のようになる。

<?php
$conditions = array(
                  array('tag NOT LIKE'=>'%CD%'),
                  array('tag NOT LIKE'=>'%Java%'),
                  array('publisher NOT LIKE'=>'%ソフトバンククリエイティブ%'),
              );

明示的に書かない限り、ANDで連結されるようになっている。
また、演算の種類を省略すると、イコールになる。
今回はANDは省略し、NOT LIKE を指定している。

下記のようなシンプルな形もあるのだが、

<?php
$conditions = array(
                  'tag NOT LIKE'=>'%CD%',
                  'tag NOT LIKE'=>'%Java%',   // ←キーの重複
                  'publisher NOT LIKE'=>'%ソフトバンククリエイティブ%',
              );

今回は同じカラムに対して同じ判定方法で複数の条件があるため、キーが重複してしまい、後者を使うことができない。

なので、前者を作るために、前回の記事の内容を使う。

WHERE句のもととなる配列を作る

さきほどのこのクエリを使って、

SELECT target, json_agg(filter_string) AS json_filter
FROM filters
GROUP BY target

こうする

target	json_filter
tag	["CD","Java"]
publisher	["ソフトバンククリエイティブ"]

そうなったら、こうする

<?php
$conditions=array();
foreach($filters as $filter){
    $target=$filter['Filter']['target'];
    foreach(json_decode($filter['Filter']['json_filter']) as $filterString){
        $conditions[]=array($target.' NOT LIKE'=>'%'.$filterString.'%');
    }
}

すると$conditionsはこうなって完成

<?php
$conditions = array(
                  array('tag NOT LIKE'=>'%CD%'),
                  array('tag NOT LIKE'=>'%Java%'),
                  array('publisher NOT LIKE'=>'%ソフトバンククリエイティブ%'),
              );

実際に取得する

こうだ

<?php
$result = $this->Data->find('all', array(
                'fields' => array('Data.*'),
                'conditions' => $conditions,
            ));

以上だ！

余談

PHPでは、array()を用いた配列の宣言時にデータを入れる場合、最終要素の後にカンマを余分にいれてもエラーにならない。
一見ややこしい仕様だが、これには実用上かなり助けられている。

たとえば、以前こうなっていたデータが、

<?php
$conditions = array(
                  array('tag NOT LIKE'=>'%CD%'),
                  array('tag NOT LIKE'=>'%Java%'),
                  array('publisher NOT LIKE'=>'%ソフトバンククリエイティブ%')  //←カンマがないことに注目
              );

誰かの編集でこうなったとする

<?php
$conditions = array(
                  array('tag NOT LIKE'=>'%CD%'),
                  array('tag NOT LIKE'=>'%Java%'),
                  array('publisher NOT LIKE'= '%ソフトバンククリエイティブ%'),  //←カンマがあることに注目
                  array('publisher NOT LIKE'=>'%ランティス%')
              );

これらの2ファイルでdiffを取ると、意味上では1行の変更で1要素の追加であるにもかかわらず、カンマが追加されていることによって、感覚的に変更されていない3番目の要素も変更した行となってしまう。

バージョン管理システムやdiffツールで変更があった箇所を精査する際、この元から書いておけばよかったカンマ1個のために、精査する行が増えてしまう。
さらに、よくある変更ではあるので、なんだカンマだけの変更か、と軽く見てしまうと、実はその行にもう1箇所変更があり、文法エラーが新たに混入したことに気づけない。

上記のため、筆者は必ず最後の要素でもカンマを書き込んでいる。

2016-08-16

PostgreSQL : 配列を配列として取り出したい

PostgreSQL PHP プログラミング

はじめての技術メモです。
今まで雑魚実装をしていたことに気づいたので、覚え書き。

本来はタイトル通り、技術関係のことを書こうかなと思ってたけど、書くほどのことをしなかったので、ようやく記事が書けた。

PostgreSQLから配列を取り出す

例えばこういうテーブルを対象とする。

product_id	tag
101	書籍
101	エッセイ
102	DVD
102	アニメ
102	アイカツ

こういったテーブルから、下記のような形式のデータを抽出したい。

product_id	array_tag
101	{書籍,エッセイ}
102	{DVD,アニメ,アイカツ}

よくある（？）、正規化されたテーブルをいい感じにするやつ（表現しにくい）。
これをサブクエリにして、商品データとJOINするとかだろう。

これ自体は、下記のようなクエリでサックリいく。

SELECT product_id, array_agg(tag) AS array_tag
FROM tags
GROUP BY product_id

array_agg() はバージョン8.4以降から使える。
でもこれだとまだちょっと足りない。

正確で堅い手法でパースしたい

上記のクエリを発行した結果では、PostgreSQL内では配列だったとしても、受け取る言語の実装によっては文字列になる。
文字列が返った場合、パースして配列にするのがめんどくさい。
メタ的な意味ではなくデータ自体に波括弧があったら？カンマがあったら？
単純に波括弧を除外してカンマでsplitするだけでは全然ダメ。
たとえRFCを熟知していようが、自分でこういうの書くのは絶対ダメ。顧客が思い通りにデータを入れると思うな。

もしこれがJSONやXMLであれば、大概の言語は基本実装なりライブラリがあるので、このクエリを発行したモジュールの言語で対応することができる。

今回は、JSONにして、PHPでパースする。

array_agg の結果をJSONにする

PostgreSQL バージョン9.3 からは、json_aggという集約関数がある。
array_aggのjson版となっている。
PostgreSQL内では9.2からjson型が存在するので、json型だが、PHPに返ってくるのは当然文字列。
まあjsonだしね。

これをjson_decodeでデコードしてやれば、解決する。

クエリ

SELECT product_id, json_agg(tag) AS json_tag
FROM tags
GROUP BY product_id

PHP実装例

<?php
// $result に上記クエリの結果が入っていると考えて
foreach($result as $record){
    $productId = $record['product_id'];
    $decodedRecord = json_decode($record['json_tag']);
    foreach($decodedRecord as $tag){
        //なんか処理
    }
}

バージョン9.2での実装

上記はバージョン9.3 でのカンタンな実装だが、json実装当初の9.2でも、同様のことは実現できる。
筆者はjson_aggを知らなかったのでこっちの方法でやっていた。
PostgreSQL内で配列からjsonに変換することができるから、array_aggの結果をjsonに変換すればいいだけ。

SELECT product_id, array_to_json(array_agg(tag)) AS json_tag
FROM tags
GROUP BY product_id

経験上こういう整形は出来る限りのところまでDBにやらせたほうが速いからDBに生きがいを与える。

応用する

下記のページにバージョン9.4系でのjson関係の関数が一覧になっている。
PostgreSQLでjsonをこねくりまわして変態になろう！
JSON関数と演算子 : https://www.PostgreSQL.jp/document/9.4/html/functions-json.html