CSVをデータベースのように扱ってSQL文で情報を抜き出す

会社で運用しているとあるシステムがエクスポートするデーター(target.csv)がイケてない。エクスポートされたデーターを他のマスターデーターと組み合わせなければ使い物にならない。

---

そのため、社員(employee.csv 情報多過ぎ、一般には見せられない情報あり)と組織(organization.csv 情報多過ぎ、階層構造)を組み合わせた社員情報(empinfo.csv)を作り、作業者に提供したい。

どんな方法があるのか探してみたところ、たくさんのコマンドを教えてくれるページに出会った。これはありがたい。この中からTextQLを中心に作業を進めていくことにした。
もた日記 / CSVの処理で使えそうなコマンドラインツール（column, textql, csvkit, xsv, visidata, csvtotable, daff, tabview）

やること。

• 使ってみて分かったTextQLの特徴
• UbuntuでTextQLを利用する
  • 文字コードと改行コードを変換
  • TextQLのインストール
  • 組織階層から所属組織の正式名称を取り出す
  • 社員と所属組織の正式名称を連結する
  • 共有フォルダにファイルを置いたら処理する仕組みを作る
    • スクリプトの作成
    • ファイルが置かれたら処理を起動する
    • 動作確認
• WindowsでTextQLを利用する
  • 文字コードと改行コードを変換
    • ダウンロード
    • 文字コード変換
  • TextQLの作成
    • Goのインストール
    • Gitのインストール
    • gccのインストール
    • TextQLのコンパイル
    • 動作確認
  • ダブルクリックで動作するバッチを作る
    • バッチの作成
    • 動作確認
• やったこと
  • gccのインストール(失敗)
    • インストール
    • アンインストール

 

使ってみて分かったTextQLの特徴

この記事を書く過程で気付いたTextQLの特徴。

• 速い(個人的感想、メモリが多い方が速い気がする)。 
• ダブルクォーテーションで囲まれた複数行の要素を扱える。
• 複数テーブル(CSV)で普通にリレーションが使える。
• 0から始まる数字は0欠けする。数字を積極的に数字として取り扱う思想。
• 使える関数は限られる。
  → LPADとかFORMATは使えない。LEFTは使えないがSUBSTRは使える。
• 文字コードはUTF-8が扱える。Shift JISは無理。

これらを理解しつつ便利に使っていく。

 

UbuntuでTextQLを利用する

とあるフォルダにCSVファイルを放り込んでしばらく待つと処理が起動し、処理が終われば社員情報(empinfo.csv)ができあがる、という仕組みにしていく。

文字コードと改行コードを変換

運用中のシステムからエクスポートされるCSVはWindowsで利用されることを想定しているから、Shift JISになっている。当然改行コードはCR+LF。

Ubuntuで処理するならUTF-8に変換する必要があるので、nkfコマンドを利用する。iconvとかdos2unixとかも確認したけれど、nkfが一番お手軽。

$ sudo apt install nkf

 

Shift JISからUTF-8に変換。

$ nkf -wLu employee.csv > employee_u.csv
$ nkf -wLu organization.csv > organization_u.csv

 

UTF-8からShift JISに変換。

$ nkf -sLw empinfo_u.csv > empinfo.csv

 

これをスクリプトに組み込んでいく。

TextQLのインストール

CSVをSQLで扱えるコマンド TextQL をインストールして試してみることにした。

$ sudo apt install textql

 

employee_u.csvとorganization_u.csvは1行目がヘッダー行になっていることを踏まえて、ちょろっとデーターを抽出してみる。

$ textql -header -sql "select 氏名 from employee_u where SYSID < 10" employee_u.csv
システム管理者
あい　うえお
かき　くけこ
さし　すせそ
…

 

ヘッダーがなく、列の番号を指定する場合には、以下が使える模様。

$ textql -sql "select c1,c2 from employee_u" employee_u.csv
1,システム管理者
2,あい　うえお
3,かき　くけこ
4,さし　すせそ
…

 

かなりキビキビ動作する印象。

 

組織階層から所属組織の正式名称を取り出す

組織(organization.csv→organization_u.csvに変換済み)には番号(SYSID)が付けられており、親組織の番号を示す列(親組織)がある。この条件で、子組織は全ての親組織の組織名をつなげて正式名称を取り出したい。

こういう再帰的な処理を実装するのは結構面倒だなぁと思いながら探してみたところ、さらっとやってのけているページが見つかった。
@IT / SQLの抽出結果を階層構造で表示するテクニック

start with connect by？だけど、使えるのはOracleだけなの？と探っていたら、標準SQLでたどる方法が書かれている。
i am BEST / Oracle の CONNECT BY 句を標準SQL で書き換える（1-1）（start with, connect by）
Qiita / 再帰SQL -図解-

では、まず最初にルートとなる各組織の親が取り出せることを確認。

select SYSID, 組織名
 from organization_u
 where 親組織 is null

 

$ textql -header -sql "select SYSID, 組織名 from organization_u where 親組織 is null" organization_u.csv
2,そしき
40,組織
…

 

とれた…では、一気に組織の正式名を作ってみる。あわせてヘッダーを出力し、必要な情報だけを取り出すように整理しておく。

with recursive p (ID, 組織名, 親組織) as (
select SYSID, 組織名, 親組織
 from organization_u
 where 親組織 is null
union all
select c.SYSID, (p.組織名 || ' ' || c.組織名) as 組織名, c.親組織
 from organization_u c, p
 where ('*' || p.ID) = c.親組織
) select ID, 組織名 from p

 

$ textql -header -output-header -sql "with recursive p (ID, 組織名, 親組織) as ( select SYSID, 組織名, 親組織 from organization_u where 親組織 is null union all select c.SYSID, (p.組織名 || ' ' || c.組織名) as 組織名, c.親組織 from organization_u c, p where ('*' || p.ID) = c.親組織) select ID, 組織名 from p" organization_u.csv
ID,組織名
2,そしき
3,そしき 人事部
8,そしき 総務部
12,そしき 法務部
…

※組織から取り出す情報を追加するときには、赤文字部分をセットで追加する。

とれた。思っていたよりもキビキビ動作する印象。

 

社員と所属組織の正式名称を連結する

階層構造になっていて面倒だった所属組織の正式名称が取り出せるようになったので、社員(employee.csv→employee_u.csvに変換済み)と連結して必要な情報を取り出す。

複数のファイルを参照するときには、ファイルを2つ指定すれば良いという簡単さ・高性能っぷり。

with recursive p (ID, 組織名, 親組織) as (
select SYSID, 組織名, 親組織
 from organization_u
 where 親組織 is null
union all
select c.SYSID, (p.組織名 || ' ' || c.組織名) as 組織名, c.親組織
 from organization_u c, p
 where ('*' || p.ID) = c.親組織
)
select e.SYSID, e.社員番号, e.氏名, e.ふりがな, p.組織名
 from employee_u e, p
 where e.所属組織 = ('*' || p.ID)
 order by 社員番号

 

$ textql -header -output-header -sql "with recursive p (ID, 組織名, 親組織) as ( select SYSID, 組織名, 親組織 from organization_u where 親組織 is null union all select c.SYSID, (p.組織名 || ' ' || c.組織名) as 組織名, c.親組織 from organization_u c, p where ('*' || p.ID) = c.親組織) select e.SYSID, e.社員番号, e.氏名, e.ふりがな, p.組織名 from employee_u e, p where e.所属組織 = ('*' || p.ID) order by 社員番号" organization_u.csv employee_u.csv
SYSID,社員番号,氏名,ふりがな,組織名
2,nnnnnnnnn1,あい うえお,あい うえお,そしき
36,nnnnnnnnn6,さし すせそ,さし すせそ,そしき 人事部
8,nnnnnnnnn3,かき くけこ,かき くけこ,そしき 総務部
…

※社員から取り出す情報を追加するときは、最後のselect文に追加する。

想像しているよりはるかに簡単にスピーディに欲しいCSVが得られた。

 

共有フォルダにファイルを置いたら処理する仕組みを作る

以前作った仕組みに追加することにした。

/home/samba/share以下をSambaで公開しており、そこにファイルが置かれたら/home/sambaに作成するスクリプトを起動する。

監視はIncronで行う。

スクリプトの作成

スクリプトはやりたいことをまっすぐ実行、一般ユーザー向けではないのでエラー処理は考えない…。

/home/samba/makeempinfo.sh

#!/bin/bash
WDIR='/home/samba/share/Test'

# employee.csvだったら編集する
if [ employee.csv = "${1##*/}" ]; then

    # 作業ディレクトリ移動
    cd $WDIR

    # UTF-8に変換
    nkf -wLu employee.csv > employee_u.csv
    nkf -wLu organization.csv > organization_u.csv

    # 編集する
    textql -header -output-header -sql "with recursive p (ID, 組織名, 親組織) as ( select SYSID, 組織名, 親組織 from organization_u where 親組織 is null union all select c.SYSID, (p.組織名 || ' ' || c.組織名) as 組織名, c.親組織 from organization_u c, p where ('*' || p.ID) = c.親組織) select e.SYSID, e.ログインID as 社員番号, e.氏名, e.ふりがな, case when instr(p.組織名,' ') <> 0 then substr(p.組織名, 1, instr(p.組織名,' ') - 1) else p.組織名 end as 会社名, p.組織名 from employee_u e, p where e.所属組織 = ('*' || p.ID) order by 社員番号" organization_u.csv employee_u.csv > empinfo_u.csv

    # 編集結果をShift JISに変換
    nkf -sLw empinfo_u.csv > empinfo.csv
    cp --preserve=mode,ownership --attrivutes-only employee.csv empinfo.csv

    # 作業ファイルを削除
    rm employee_u.csv
    rm organization_u.csv
    rm empinfo_u.csv

fi

 

ファイルが置かれたら処理を起動する

/home/samba/share/Testフォルダを監視し、employee.csvがファイル生成・更新されファイルが閉じられたら処理を起動する(MODIFYだと複数回呼ばれたので…)。

$ sudo incrontab -e

/home/samba/share/Test IN_CLOSE_WRITE,IN_DONT_FOLLOW /home/samba/makeempinfo.sh $@/$#

※この条件にするとファイルの転送待ちがいらないので、前の記事よりいい。

動作確認

実際にファイルを置いてみたところ、すぐに社員情報(empinfo.csv)ができあがる。
これはかなり快適。

 

WindowsでTextQLを利用する

運用を考えると、共有フォルダに個人情報の塊みたいなファイル(社員 employee.csv)を置くのはどうなんだろう？と思い始めた。

担当者がWindows端末で作業できるようにnkfとTextQLを用意する。

文字コードと改行コードを変換

次の項でWindowsで利用できるTextQLを作成するが、このコマンドはShift JISのファイルが扱えない(データーに全角文字が含まれている分には大丈夫なのかもしれないが、少なくとも列名としては使えなかった)。

実際に業務に組み込むとしたらバッチファイルにするだろうから、その中で事前にファイルを変換しておく。

また、抽出した結果をリダイレクトしてファイルに保管したが、結果はUTF-8でLFになっていたので、最後にShift JISでCR+LFのファイルに変換する。

変換にはnkfを利用する。

ダウンロード

Vectorにあるネットワーク用漢字コード変換フィルタをダウンロードする。展開すべきファイルについてはここに説明があった。
@IT / nkfツールで文字コードを変換する（Windows編）

併せて展開したrelease.txtを見ると、必要なファイルは…
vc2005\win32(98,Me,NT,2000,XP,Vista,7)Windows-31J\nkf32.exe
→ ファイル名をnkf.exeに変更しておくと、Ubuntuと同じ感覚で使える。
vc2005\dll(NT,2000,XP,Vista,7)\nkf32.dll
と分かる。

この2つのファイルをパスの通った場所にコピーしておく。
かなり基本的なコマンドなので、C:\Windowsフォルダにコピーしてみたが、どうにか動きそうだ。

>nkf --version
Network Kanji Filter Version 2.1.1 (2010-08-08)
Copyright (C) 1987, FUJITSU LTD. (I.Ichikawa).
Copyright (C) 1996-2010, The nkf Project.

 

文字コード変換

Shift JISからUTF-8への変換

>nkf -wLu employee.csv > employee_u.csv

 

UTF-8からShift JISへの変換

>nkf -Lw empinfo_u.csv > empinfo.csv

 

問題なく変換できた。これをバッチ処理に組み込む。

TextQLの作成

TextQLは本家ではソースコードで提供されている。

Ubuntuではバイナリがパッケージで提供されているけれど、Windowsにはない。Goというプログラミング言語で書かれており、Windows用には自分でバイナリを作ればいい。

以降の手順でできあがった実行ファイルには、必要なライブラリが全てリンクされているので単体で動作する。利用者はそれをコピーして使えば良い。

ここでコンパイル手順を整理しておく。といいつつ、ほぼこの教え通り。
えぢた 2.2 @k_zoar / TextQL を Windows で使う

Goのインストール

Goはコンパイル言語。

まずは公式サイトからWindows用のインストーラーをダウンロード。
The Go Programming Language
Qiita / WindowsにGo言語をインストールする方法まとめ

この日のバージョンは go1.13.1.windows-amd64.msi だった。
早速インストール、全てデフォルトで進めた。

上記サイトで紹介されているように、環境変数は以下の通り追加された。

GOPATH=%USERPROFILE%\go
PATH=%PATH%;%USERPROFILE%\go\bin

 

コマンドプロンプトで確認してみた。

>go version
go version go1.13.1 windows/amd64

>dir %userprofile%\go
 ドライブ C のボリューム ラベルがありません。
 ボリューム シリアル番号は AC30-599E です

 C:\Users\hogeuser のディレクトリ

ファイルが見つかりません

 

GOPATHに相当するディレクトリが作られていないようだけど…この先でTextQLをコンパイルするタイミングで作られたので大丈夫。

 

Gitのインストール

パッケージをインストール。

>go get -u github.com/dinedal/textql/...
go: missing Git command. See https://golang.org/s/gogetcmd
package github.com/dinedal/textql/...: exec: "git": executable file not found in %PATH%

 

gitがない、と怒られた。

そこで公式サイトからダウンロードしてくる。
Git --distributed-even-if-your-workflow-isnt

インストールは全てデフォルトで進めた。

 

gccのインストール

再度パッケージをインストール。

>go get -u github.com/dinedal/textql/...
# github.com/mattn/go-sqlite3
exec: "gcc": executable file not found in %PATH%

 

gccがない、と怒られた。

Goには2種類のコンパイラがあるのだが、gccが選択されているのでエラーが出ているのだろう。
golang.jp / Go言語の2種類のコンパイラの違い

MinGW 64bit で探すとこちらが見つかる。
mingw-w64 GCC for Windows 64 & 32 bits
SOURCEFORGE / MinGW-w64 - for 32 and 64 bit Windows

インストールの途中で選んだ項目はこちら。正直よく分からない項目が並んでいるが、コンパイル時に64ビットコンパイラを求められるので、x86_64の選択は必須。

インストールが終わったら、gccがインストールされたフォルダにPATHを通す。

C:\Program Files\mingw-w64\x86_64-8.1.0-posix-seh-rt_v6-rev0\mingw64\bin

※バージョンでフォルダ名が変わるようなので注意。参照ボタンから探していくと間違いがなくていいと思う。

環境変数はシステムのプロパティで設定できる。スタートメニューでsysdm.cplを開けば直接開くことができる。
このページにいくつものショートカットが示されていて勉強になった。
@IT / Windows 10でコントロールパネルの［システム］や［システムのプロパティ］を素早く開く方法

インストールとPATH通しが終わったら、以下のコマンドで状態を確認。

>gcc --version
gcc (x86_64-posix-seh-rev0, Built by MinGW-W64 project) 8.1.0
Copyright (C) 2018 Free Software Foundation, Inc.
This is free software; see the source for copying conditions.  There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.

 

TextQLのコンパイル

ここまで準備したところでコンパイル。

>go get -u github.com/dinedal/textql/...

 

しばらく待っていたら何の表示もされずに終了。
バイナリファイルは %USERPROFILE%\Go\bin に作成されている。

何かエラーが表示されたら、各種インストール・設定を見直してみる。

 

動作確認

Windows環境でUbuntuの項で書いたパラメーターを使いTextQLを実行してみたところ、Ubuntuのときと同じ結果が得られた。

素晴らしいなと思ったのは、Goをインストールしていない環境にバイナリ(textql.exe)をコピーしてみたところ、そこで使えたこと。ライブラリはスタティックリンクされるみたい。コンパイル環境を作るのは大変だけど、組織の中では誰かがちゃんとしたバイナリを作ればみんなで使える。

想定以上にSQL処理が早くて、これは便利！

 

ダブルクリックで動作するバッチを作る

作業フォルダにバッチファイルを置いておき、バッチファイルをダブルクリックしたら結果のファイルが作られる、そんな仕組みにしたい。

バッチの作成

スクリプトはやりたいことをまっすぐ実行、一般ユーザー向けではないのでエラー処理は考えない…。

REM @ECHO OFF ← 何も表示させたくないときにはコメントを外す
REM UTF-8に変換
nkf -wLu employee.csv > employee_u.csv
nkf -wLu organization.csv > organization_u.csv

REM 編集する
textql -header -output-header -sql "with recursive p (ID, 組織名, 親組織) as ( select SYSID, 組織名, 親組織 from organization_u where 親組織 is null union all select c.SYSID, (p.組織名 || ' ' || c.組織名) as 組織名, c.親組織 from organization_u c, p where ('*' || p.ID) = c.親組織) select e.SYSID, e.ログインID as 社員番号, e.氏名, e.ふりがな, case when instr(p.組織名,' ') <> 0 then substr(p.組織名, 1, instr(p.組織名,' ') - 1) else p.組織名 end as 会社名, p.組織名 from employee_u e, p where e.所属組織 = ('*' || p.ID) order by 社員番号" organization_u.csv employee_u.csv > empinfo_u.csv

REM 編集結果をShift JISに変換
nkf -sLw empinfo_u.csv > empinfo.csv

REM 作業ファイルを削除
del employee_u.csv
del organization_u.csv
del empinfo_u.csv

 

動作確認

社員(employee.csv)と組織(organization.csv)を置いてあるフォルダにバッチファイルを置いてダブルクリックしてみたところ、ちょっと考えて…社員情報(empinfo.csv)ができあがる。Windows端末は少しメモリが足らないのかな？

それでも数秒待つ程度、快適といえる。

 

やったこと

gccのインストール(失敗)

失敗 としているが、TextQLは64ビットコンパイラを要求するため、このパッケージでは上手くいかなかったというだけで、パッケージに問題があるわけではない。

Windows用のgccを探すと、MinGWが見つかる。
MinGW Minimalist GNU for Windows

多分、Windowsでgccを動かすというプロジェクトは他にもたくさんあるんだと思われ、その中で一番アクセスの多いページということなのだろう。

インストール

ダウンロードリンクをたどってインストーラーをダウンロード。
インストーラーはmingw-get-setup.exeで、これをインストールしたらMinGW Installation Managerが起動した。

このツールで gcc-c++ をインストールをマークして、Instllation→Apply Changesすると、モジュールがインストールされる。

ただ、これだけだと使えなくて、コマンドがインストールされた先にPATHを通しておかなきゃならない。インストーラーはやらない、自分でやれって書いてある。
MinGW / Getting Started

なので、今回インストールされたC:\MinGW\binを追加しておく。

バージョンを調べてみると…

>gcc --version
gcc (MinGW.org GCC-8.2.0-3) 8.2.0
Copyright (C) 2018 Free Software Foundation, Inc.
This is free software; see the source for copying conditions.  There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.

 

これで使えそう。と思ったのだが、この方法でインストールされるのは32ビット版だったため、使えなかった。

>go get -u github.com/dinedal/textql/...
# github.com/mattn/go-sqlite3
cc1.exe: sorry, unimplemented: 64-bit mode not compiled in

 

残念。

アンインストール

このプログラムは「プログラムと機能」で表示されず、スタートメニューで「アンインストール」をクリックしても反応しない。

これによると、ディレクトリを削除し、メニューから削除すればいい、システムに依存するファイルはインストールしないから…と書かれている。
Quora / How do you uninstall minGW?

ということで、以下を実施する。

1. PATHを通していたなら削除。
   システムのプロパティを開き、PATHからC:\MinGW\binを削除。
2. エクスプローラーを開いてC:\MinGWをフォルダごと削除。
3. スタートメニューからMinGW Installation Managerを削除。
   C:\MinGWを削除した後にメニューをクリックすれば削除するかどうか聞かれる。

これで削除完了。

 

さいごに

最初にシステムを開発するときの思想は、そのシステムが便利に使われ出すとユーザーの要望によって思わぬ方向に変化する、発展していく。

今回の問題となったシステムはUIはよくできていて、幾つかの改善でかなりユーザーに喜ばれるものになると思う。だけど、エクスポートされるデーターはおまけ感が凄すぎる、適当すぎる。設計者が利用シーンを全く想像できなかったのだろうし、それを指摘する上位者もいなかったのだろう。

これを補うためにユーザー側でツールを作ろう…いつもの通りエクセルでマクロを作るのかー、めんどくさいなぁとか思っていたところに便利なツールと使い方が見つかった。初めてなので時間がかかっていて、今回のことだけでいえばマクロを作った方が速かったかもしれないけれど、将来への投資だなって思えた。

できあがったツールはシステムに組み込めばユーザーの評価が上がる。自分の持ち物じゃないから頼んでやってもらうしかないけど、思想を語る以前の状態から脱却してもらいたい。発展するために必要な一歩だと思う。