LoginSignup
28
23

More than 5 years have passed since last update.

@mithrandie

csvqについてのいろいろ

Last updated at Posted at 2019-02-17

csvq - SQL-like query language for csv は元々5人程のユーザに使ってもらうために公開していたもので，過去にドイツのほうでLinux MagazinやLinuxUserに小さく紹介してもらったことはあったのですが，先週より日本からのアクセスが増えたので，使うにあたってのちょっとしたことなどをこちらに書いておきます

想定用途

csvその他対応フォーマットの簡易的な計算や確認，微修正等．

クエリ実行の他，関数定義や外部コマンドの呼び出し，IFやWHILE等の制御構文により，複数のステートメントを組み合わせた簡単なスクリプトを書き，定型の処理として用意すること等も可能ですが，これらの機能は決してcsvをデータストアとして利用することを推奨するものではありません

読み込まれたcsvのデータはすべてメモリ上に保持されるため，データ量やマシンの搭載メモリ容量次第で快適に操作できる件数は変わってきます．目安として，搭載メモリ16GBのマシン上で，join等を利用しない1テーブルのみの読み込みでは最大1000万件程度のデータを想定しています

他の代替となるツールとの違いについて

csvqは，実行バイナリひとつでなるべく簡易に複数プラットフォーム上で同等の操作をおこなうことができるようにすることを目的としています．実行バイナリとcsvq用のクエリまたはスクリプトを記述したファイルを受け渡すことで，他のツールや実行者の使用するシステムに依存することなく同等の結果を得ることができます

その他，対話シェル，データ更新用クエリ等の機能があります

インストール

go getで最新のコミットを取得，ビルドし作成されるバイナリとReleaseページで配布する最新のバイナリは常に同一のものとなりますので，どちらの方法でインストールしても大丈夫です

他のパッケージに対して依存関係等のないツールですので，パッケージ管理システムでの配布は予定していません

計算結果の出力について

データはすべてstringまたはnull値として読み込まれ，なにも操作をしなければそのままの形で出力されます

演算子や関数でデータを操作した時点で自動で型の変換が行われます．where句等の中での型変換はselect句での出力には影響しません．select句の中で操作した出力結果を別の場所で利用する際にはそれぞれの値が適切な形になっているのか確認してください．データの型を明示的に指定する関数，データの形式を整えるための関数等も用意してあります

構文の確認

オンラインリファレンスを開かなくても，syntaxサブコマンドもしくはインタラクティブシェル上でのSYNTAXコマンドにより，使用可能な構文を確認できます．これはあくまでもcsvqの構文であり，SQLの構文を確認するためのものではありません

内部処理

読み込んだデータを1行1行地道に計算しているだけです．むずかしいことはなにもやっていません

大量データ検索の高速化

身も蓋もない話になりますが，出来るだけ多くのメモリと出来るだけ読み込み速度の速いSSDと出来るだけ多くのコアを持ったCPUを利用してください

-pオプションで並列処理の数を指定できます．デフォルトではOSの認識するコア数の半分の値となっています．Intel HTテクノロジー等の同時マルチスレッディングはこのツールの利用においてはあまり高速化には役立ちません．CPUのコア数とスレッド数が同数のシステムを使用している場合には，この値を変更することで，より高速化が可能です

その他，検索条件の最適化等もありませんので，テーブルの結合条件はwhere句ではなくjoin句に書く，より多くのレコードを絞り込める条件を条件式の左辺に書く等の，計算順序を意識した書き方が有用です．ただし，これらのことを考慮しなければならないような状況においては多くの場合，既存のDBMS等の利用を検討することがより良い選択肢となるかと思います

使用例

以下は現在の最新バージョン(1.9.1 Released on 2019/03/04)で動作を確認してあります

スクリプトの拡張子はなんでも良いのですが，.sqlまたは.cqlのファイルはインタラクティブシェル上で，SOURCEコマンドの引数の補完の候補として表示されるようにしてあります

例えばどこかの国の地方行政区分を簡易ツリー表示してみる

以下の機能の使用

  • 組み込み関数
  • ユーザ定義関数
  • 変数
  • 条件文
  • 算術，文字列結合演算子

データ

government.csv
id,name,parent_id
1,ラダトーム,
2,りゅうおうのしろ,
3,ガライ,1
4,マイラ,1
5,リムルダール,1
6,ドムドーラ,1
7,メルキド,1
8,あめのほこら,4
9,せいなるほこら,5

実行スクリプト

list_gov.sql
/*
 * ソート用文字列作成関数
 *   (現在WITH句のRECURSIVEでは幅優先探索のみ可能なため，結果のソートが必要)
 */
DECLARE gen_route FUNCTION (@id, @parent_route)
AS
BEGIN
    VAR @r := LPAD(@id, 2, '0');

    IF @parent_route IS NULL THEN
        --ソート時の自動型変換で数値として解釈されることを防ぐため先頭に文字を付加
        RETURN 'R' || @r;
    END IF;

    RETURN @parent_route || '-' || @r;
END;

/*
 * クエリ
 */
WITH
    RECURSIVE govs (id, name, parent_id, level, route)
    AS (
        SELECT id, name, parent_id, 0, gen_route(id, null)
          FROM government
         WHERE parent_id IS NULL
        UNION ALL
        SELECT g.id, g.name, g.parent_id, t.level + 1, gen_route(g.id, t.route)
          FROM government g
               INNER JOIN govs t ON t.id = g.parent_id
    )
SELECT id,
       LPAD(name, LEN(name)+level, '　') AS name,
       level,
       route
  FROM govs
ORDER BY route;

実行結果

$ csvq -s list_gov.sql
+----+--------------------+-------+-----------+
| id |        name        | level |   route   |
+----+--------------------+-------+-----------+
| 1  | ラダトーム         |     0 | R01       |
| 3  | 　ガライ           |     1 | R01-03    |
| 4  | 　マイラ           |     1 | R01-04    |
| 8  | 　　あめのほこら   |     2 | R01-04-08 |
| 5  | 　リムルダール     |     1 | R01-05    |
| 9  | 　　せいなるほこら |     2 | R01-05-09 |
| 6  | 　ドムドーラ       |     1 | R01-06    |
| 7  | 　メルキド         |     1 | R01-07    |
| 2  | りゅうおうのしろ   |     0 | R02       |
+----+--------------------+-------+-----------+

例えばどこかのお店の日次売上を確認してみる

以下の機能の使用

  • フラグ設定(出力をそのままマークダウンとして利用できる形式に)
  • 一時テーブル
  • 環境変数による外部からの値受け渡し
  • 文字列の置換による動的生成クエリの実行
  • 副問い合わせ

データ

items.csv
id,category_id,name
1,1,やくそう
2,1,キメラのつばさ
3,1,たいまつ
4,2,こんぼう
5,2,どうのつるぎ
6,2,はがねのつるぎ
7,3,ぬののふく
8,3,くさりかたびら
9,3,はがねのよろい
item_category.csv
id,name
1,アイテム
2,ぶき
3,ぼうぐ
sales_20180217.csv
id,item_id,price,quantity,created
1,1,24,1,2018-02-17T07:01:05
2,3,8,2,2018-02-17T08:05:59
3,1,24,4,2018-02-17T08:06:37
4,5,180,2,2018-02-17T08:06:48
5,1,24,1,2018-02-17T08:30:05
6,6,1500,1,2018-02-17T08:45:20
7,2,70,1,2018-02-17T08:46:03
8,7,20,1,2018-02-17T09:03:04
9,8,300,1,2018-02-17T09:12:21
10,6,1500,1,2018-02-17T09:31:03
11,9,3000,1,2018-02-17T09:31:15
12,1,24,2,2018-02-17T09:31:25
13,2,70,1,2018-02-17T09:31:32
14,1,24,1,2018-02-17T10:05:20
15,1,24,6,2018-02-17T11:41:15
16,1,24,2,2018-02-17T12:40:06
17,3,8,2,2018-02-17T12:40:19

実行スクリプト

sales_report.sql
/*
 * フラグ設定
 */
ADD '%Y%m%d' TO @@DATETIME_FORMAT; --YYYYMMDD形式のStringをDatetimeへ自動変換可能とする
SET @@FORMAT TO GFM; --SELECTクエリの出力形式をGithub Flavored Markdownテーブルに

/*
 * 日付が環境変数にセットされているか確認
 */
IF @%SALES_DATE == '' THEN
    TRIGGER ERROR '@%SALES_DATE is empty.';
END IF;

/*
 * 一時テーブル作成
 */
VAR @create_tmp_view := '
    DECLARE sales VIEW
        (id, item_id, price, quantity, amount)
        AS
        SELECT id, item_id, price, quantity, price*quantity
          FROM `sales_%s`;
';

EXECUTE @create_tmp_view USING @%SALES_DATE;

/*
 * データ抽出，表示
 */
PRINTF '## 売上 %s' USING DATETIME_FORMAT(@%SALES_DATE, '%Y-%m-%d');

ECHO '\n### 売上数\n\n';
SELECT i.id,
       MAX(i.3) AS name, --テーブル内の列番号でカラム指定可能
       SUM(s.quantity) AS sales_quantity
  FROM items i LEFT JOIN sales s ON s.item_id = i.id
GROUP BY i.id
ORDER BY sales_quantity DESC NULLS LAST;

ECHO '\n### 売上額\n\n';
SELECT i.id,
       MAX(i.name) AS name,
       SUM(s.amount) AS sales_amount
  FROM items i LEFT JOIN sales s ON s.item_id = i.id
GROUP BY i.id
ORDER BY sales_amount DESC NULLS LAST;

ECHO '\n### カテゴリ別売上額順位\n\n';
SELECT c.name AS category,
       RANK() OVER (PARTITION BY c.id ORDER BY t.sales_amount DESC NULLS LAST) AS `rank`,
       t.name AS name,
       t.sales_amount AS sales_amount
  FROM (SELECT i.id,
               MAX(i.category_id) AS category_id,
               MAX(i.name) AS name,
               SUM(s.amount) AS sales_amount
          FROM items i LEFT JOIN sales s ON s.item_id = i.id
        GROUP BY i.id) t
       INNER JOIN item_category c ON c.id = t.category_id
ORDER BY c.id ASC, `rank` ASC;

EXIT;

実行結果

$ env SALES_DATE=20180217 csvq -s sales_report.sql
## 売上 2018-02-17

### 売上数

|  id  |      name      | sales_quantity |
| ---- | -------------- | -------------: |
| 1    | やくそう       |             17 |
| 3    | たいまつ       |              4 |
| 2    | キメラのつばさ |              2 |
| 5    | どうのつるぎ   |              2 |
| 6    | はがねのつるぎ |              2 |
| 7    | ぬののふく     |              1 |
| 8    | くさりかたびら |              1 |
| 9    | はがねのよろい |              1 |
| 4    | こんぼう       |                |

### 売上額

|  id  |      name      | sales_amount |
| ---- | -------------- | -----------: |
| 9    | はがねのよろい |         3000 |
| 6    | はがねのつるぎ |         3000 |
| 1    | やくそう       |          408 |
| 5    | どうのつるぎ   |          360 |
| 8    | くさりかたびら |          300 |
| 2    | キメラのつばさ |          140 |
| 3    | たいまつ       |           32 |
| 7    | ぬののふく     |           20 |
| 4    | こんぼう       |              |

### カテゴリ別売上額順位

| category | rank |      name      | sales_amount |
| -------- | ---: | -------------- | -----------: |
| アイテム |    1 | やくそう       |          408 |
| アイテム |    2 | キメラのつばさ |          140 |
| アイテム |    3 | たいまつ       |           32 |
| ぶき     |    1 | はがねのつるぎ |         3000 |
| ぶき     |    2 | どうのつるぎ   |          360 |
| ぶき     |    3 | こんぼう       |              |
| ぼうぐ   |    1 | はがねのよろい |         3000 |
| ぼうぐ   |    2 | くさりかたびら |          300 |
| ぼうぐ   |    3 | ぬののふく     |           20 |

例えばweb APIから返されるJSONから値を集計してみる

以下の機能の使用

  • 外部コマンド呼び出し
  • JSONデータ読み込み
  • カーソル

実行スクリプト

csvq_download_report.sql
/*
 * Githubリリースページからのダウンロード数を集計(https://github.com/mithrandie/csvq/releases).
 *
 *   Usage:
 *     - Get information from GitHub API
 *       $ csvq -s csvq_download_report.sql
 *     - Load information from downloaded local file
 *       $ curl https://api.github.com/repos/mithrandie/csvq/releases -o ./releases/releases_20190302.json
 *       $ env GET_DATE=20190302 csvq -s csvq_download_report.sql
 */

-- 更新ログの出力を抑制
SET @@QUIET TO TRUE;

/*
 * 一時テーブル，カーソルを宣言
 */

-- Temporary table for release information.
DECLARE releases VIEW
    (tag_name, published_at, assets);

-- Temporary table for assets in release information.
DECLARE assets VIEW
    (tag_name, published_at, name, os, arch, download_count);

-- Cursor for releases.
DECLARE cur_releases CURSOR FOR
  SELECT tag_name, published_at, assets
    FROM releases;

/*
 * リリース情報を一時テーブルに挿入
 */
IF @%GET_DATE == '' THEN
    -- Github APIへ問い合わせ，結果を挿入
    VAR @releases_json := CALL('curl', '-s', 'https://api.github.com/repos/mithrandie/csvq/releases');

    INSERT INTO releases
        (tag_name, published_at, assets)
        SELECT tag_name, published_at, assets
          FROM JSON_TABLE("{tag_name, published_at, assets}", @releases_json);
ELSE
    -- あらかじめローカルに保存してある結果ファイルから結果を挿入
    VAR @insert_into_release_view := '
        INSERT INTO releases
            (tag_name, published_at, assets)
            SELECT tag_name, published_at, assets
              FROM JSON("{tag_name, published_at, assets}", `%s/releases/releases_%s.json`);
    ';

    EXECUTE @insert_into_release_view USING @%PWD, @%GET_DATE;
END IF;


/*
 * カーソルを使用しリリース情報からアセット情報を取り出し一時テーブルに挿入
 */
OPEN cur_releases;

WHILE VAR @tn, @pa, @as IN cur_releases
DO
    INSERT INTO assets
        (
            tag_name,
            published_at,
            name,
            os,
            arch,
            download_count
        )
        SELECT @tn,
               @pa,
               t.name,
               LIST_ELEM(t.name, '-', 2),
               LIST_ELEM(LIST_ELEM(t.name, '-', 3), '.', 0),
               t.download_count
          FROM JSON_TABLE('{name, download_count}', @as) AS t;
END WHILE;

CLOSE cur_releases;


/*
 * 集計
 */
ECHO '\n# Downloads per release\n';
SELECT tag_name,
       MAX(published_at) AS published_at,
       SUM(download_count) AS download_count
  FROM assets
GROUP BY tag_name
ORDER BY tag_name DESC;

ECHO '\n# Downloads per OS/Arch\n';
SELECT os,
       arch,
       SUM(download_count) AS download_count
  FROM assets
GROUP BY os, arch
ORDER BY os, arch;

EXIT;
28
23
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
28
23