エントリは誰にとっても面白くないので、すぐに使用例から始めましょう

% cat log.json

 {"type": "hit", "client": {"ip": "127.1.2.3"}} {"type": "hit", "client": {"ip": "127.2.3.4"}} {"type": "hit", "client": {"ip": "127.3.4.5"}} {"type": "hit", "client": {"ip": "127.3.4.5"}} {"type": "hit", "client": {"ip": "127.1.2.3"}} {"type": "click", "client": {"ip": "127.1.2.3"}} {"type": "click", "client": {"ip": "127.2.3.4"}}

私たちは要求を満たします：

 % cat log.json | jl-sql 'SELECT client.ip, COUNT(*) AS count WHERE type = "hit" GROUP BY client.ip'

 {"client":{"ip":"127.1.2.3"},"count":2} {"client":{"ip":"127.2.3.4"},"count":1} {"client":{"ip":"127.3.4.5"},"count":2}

簡単な説明

お気づきかもしれませんが、 jl-sqlユーティリティを使用してクエリを実行し、改行文字（ "\n"

）で区切られたJSONオブジェクトのストリームを受け取ります。

このユーティリティはjl-sql-apiライブラリに基づいていることに注意してください。これに基づいて、JSONだけに限らず、任意の形式でデータ処理を簡単に実装できます。

SQLエンジンは、 WHERE

、 GROUP BY

、 HAVING

、 ORDER BY

および{LEFT|INNER} JOIN

サポートしています。 JOIN

に関しては、 ON

の式に制限があります。結合のサポートは、2つのフィールドの正確な対応、いわゆるEqui Joinによってのみ実装されます。

 SELECT id, @town.name AS townName JOIN `towns.json` ON @towns.id = townId

例

一意のIPアドレスを定義する

 % cat log.json | jl-sql 'SELECT DISTINCT client.ip AS ip'

 {"ip":"127.1.2.3"} {"ip":"127.2.3.4"} {"ip":"127.3.4.5"}

各グループの一意のアドレスの数を数える

 % cat log.json | jl-sql 'SELECT type, COUNT(DISTINCT client.ip) AS ipCount GROUP BY type'

 {"type":"click","ipCount":2} {"type":"hit","ipCount":3}

オブジェクトの再構築

アライアンス（ AS

）の助けを借りて、フィールドにエイリアスを割り当てるだけでなく、オブジェクト内に複雑な構造を作成することもできます。

 % echo '{"a": 1, "b": 2}' | jl-sql 'SELECT a AS sub.bar.first, b AS sub.foo.second'

 {"sub":{"bar":{"first":1},"foo":{"second":2}}}

削除して変更

SELECT

に加えて、 DELETE

およびUPDATE

もサポートされています。

 % cat log.json | jl-sql -b :ip=127.1.2.3 'DELETE WHERE client.ip = :ip'

 % cat log.json | jl-sql -b :ip=127.1.2.3 'UPDATE SET banned = true WHERE client.ip = :ip'

これらの例では、バインダーの使用（オプション-b

/ --bind

）も示されています。これらについては、対応するセクションで詳しく説明しています。

日付を操作する

 % echo '{"ts": "2017-01-18 00:00:00"}' | jl-sql 'SELECT ts - INTERVAL 2 MONTH 3 DAY 4 HOUR 5 MINUTE 6 SECOND AS past'

INTERVAL

キーワードを使用すると、特定の日付から期間を「追加」および「減算」できます。これはすべて、MySQLでINTERVAL

を使用することに似ています。

JSONは日付の個別のデータ型を提供しないため、文字列を使用して日付を保存します。ユーティリティは、 RFC2822またはISO 8601の形式を理解します。他のものも使用できますが、この場合の結果は予測できません。

システムタイムゾーンは、解析と日付の操作に使用されることに注意してください。これがjl-sql

ない場合は、 jl-sql

実行する前に、目的のタイムゾーンでTZ

環境変数を設定できます。

GitHubのドキュメントで日付の操作の詳細な説明を読むことができます。

ユニオン（ `JOIN`

）

JOIN

は少なくとも1つ以上のデータソースが必要です。SQLの観点では「テーブル」と呼ばれ、通常のファイルはそのようなソースとして機能します。

 % cat banned.json

 {"ip": "127.1.2.3"}

 % cat log.json | jl-sql -b :banned=banned.json 'SELECT * INNER JOIN {:banned} ON @banned.ip = client.ip'

 {"type":"hit","client":{"ip":"127.1.2.3"}} {"type":"hit","client":{"ip":"127.1.2.3"}} {"type":"click","client":{"ip":"127.1.2.3"}}

この例では、新しい概念が導入されました-データソース（ @banned

）、これについて、そして一般にJOINについての詳細は、対応するセクションにあります。

パフォーマンスと消費

このユーティリティは、大きなログを処理するように設計されています。タイプに応じて、要求はストリーミングモードまたは一時ストレージを使用するモードで実行できます。 ストリーミングモードの大きな利点は、 jl-sqlを組み合わせて実行できることです。たとえば、 tail -f

を使用すると、ログをリアルタイムでフィルタリングおよび再フォーマットできます。

ストリーミングモード -着信ストリームの完了を待たずに、要求が着信データをすぐに処理するモード。このような処理の計算の複雑さは、CPUによるO(n)

とメモリによるO(1)

です。このモードでは、ソートを必要としない最も単純なクエリ、 SELECT <fields...>

、 SELECT ... WHERE expression

のみを実行できます。
一時ストレージ使用モード -要求を完了するには、着信ストリームのソートが必要です。これは、FSに一時ストレージが必要になる可能性があることを意味します。このカテゴリには、 GROUP BY

、 ORDER BY

およびJOIN

含むクエリが含まれます。内部バッファーのサイズは、 -B

および-S

オプションで設定されます（オプションの説明を参照）。

大量のボリュームをソートするために、 jl-sqlはsort

システムユーティリティを使用します。これにより、より効率的なネイティブソートを使用できます。

設置

コードはJavaScriptでNode.jsの下に記述されているため、パッケージをインストールする最も簡単な方法はnpm

です。

 # npm install -g jl-sql

重要： 6.0.0以上のNode.jsバージョンが必要です。

汚れた詳細

仕分け

ソートは、標準式ORDER BY expression [{DESC|ASC}]

を使用して指定できます。複数方向を含む複数のフィールドによるソートが一度にサポートされます。

入力の非構造化データは、独自の調整を行います。ソートに使用されるフィールドは、一部のオブジェクトに存在しないか、異なるオブジェクトに異なるタイプのデータを持っている場合があります。したがって、関数STRING()

およびNUMBER()

てデータ型を明示的に指定することを常にお勧めします。

行の並べ替え

 ORDER BY STRING(field)

数字で並べ替え

 ORDER BY NUMBER(field)

ソートタイプを明示的に指定しない場合、ユーティリティはここで説明されているルールに従ってタイプを決定しようとします。タイプを判別できなかった場合、ソートは行で行われます。

値は、次の規則に従って文字列に変換されます。

データ型	文字列表現
ひも	ソース文字列
数	小数文字列
ブール値	`"true"` / `"false"`
ヌル	`"null"`
不足しているフィールド	`""` （空の文字列）
オブジェクト	N / A ^*
配列	N / A ^*

^* -文字列および配列の文字列への変換は指定されていないため、合計セット内のこれらの値の順序に依存しないでください。

常に明示的にデータ型を設定することをお勧めします 現在のデフォルトの動作は、潜在的に危険なものとして変更または完全に削除される可能性があります。

バインディング

コマンドラインでデータをスクリーニングする問題を解決するために、ユーティリティではバインダー（置換）を使用できます。

 jl-sql -b :type=hit jl-sql 'SELECT client.ip, COUNT(*) AS count WHERE type = :type GROUP BY client.ip'

ここで、 -b :type=hit

オプション-b :type=hit

は、名前:type

および値"hit"

ルックアップを指定します。これにより、通常のシェルエスケープを使用してSQLクエリを作成できます。

同様に、 JOIN

使用されるファイル名にワイルドカードを使用できます。

 jl-sql -b :towns=towns.json 'SELECT id, @town.name AS townName JOIN {:towns} ON @towns.id = townId'

これにより、お気に入りのシェルでオートコンプリートファイル名を使用できます。

JOIN

詳細については、対応するJOINセクションをご覧ください。

`JOIN`

JOIN

サポートでは、構文に新しいエンティティ（データソースの名前）を導入する必要がありました（「テーブル」と呼ぶことができます）。ここでの問題は、「クラシック」SQLでは、識別子ident1.ident2

どこがテーブルident1.ident2

名前で、どこがフィールドの名前であるかを常に判断できることです。 JSONではすべてがより複雑です。オブジェクトは異なるネストを持つことができるため、特別な構文がなければ、ユーザーがident1.ident2

を導入したときにident1.ident2

ていたことを正確に言うことはできません。 ident1

は、フィールド名またはテーブル名のいずれかです。

このあいまいさを解決するために、 @

@ident

構文が導入されました。 @

は、それに続く識別子が「テーブル」の名前であることを示すプレフィックスです。

記事の最初から例に戻り、さらに詳細に分析してみましょう。

 % cat log.json | jl-sql -b :banned=banned.json 'SELECT * INNER JOIN {:banned} ON @banned.ip = client.ip'

だから、最初から始めましょう：

-b :banned=banned.json

ソースファイルの名前でバインディングを作成します。これは必要な手順ではありませんが、シェルでループによる自動補完を実行でき、パス内の特殊文字をエスケープする必要もなくなります。

INNER JOIN {:banned}

は、 JOIN

バインダーを置き換える特別な構文です。バインダーがなければ、この行はINNER JOIN `banned.json`

ようになります。ここでは逆引用符の使用が必須です。そうでない場合、ピリオド（ .

）は特別な方法で解釈されます。

@banned.ip

ここで@banned

はテーブルの名前です。この場合、名前はバインディングの名前から自動的に派生しますが、エイリアスINNER JOIN {:banned} AS @someName

て明示的に指定することができ、このテーブルへのアクセスは@someName.ip

として発生します

ファイルと通常のデータの両方で、常にバインダーを使用することをお勧めします。 それは多くの問題を取り除きます。

現在サポートされているJOINは、 INNER JOIN

とLEFT JOIN

2種類のみです。要求でJOINタイプを指定しない場合、 INNER JOIN

が使用されます。

Kauはすでに前述したON @table.primary = foreign

、 ON

の式はON

ON @table.primary = foreign

ようになります。つまり、フィールド@table.primary

（接続されたテーブル）とforeign

フィールド（メインテーブル）の正確な対応です。

比較演算子

便宜上、演算子=

（およびそのエイリアス==

）は値のタイプを考慮せず、可能な限り値を比較しようとするため、式1 = "1"

の値はtrue

になりtrue

。対照的に、 ===

演算子は比較時に型も考慮するため、式1 === "1"

はすでにfalse

なりfalse

。比較ルールはJavaScriptで採用されているものと似ています。詳細についてはこちらをご覧ください。

オブジェクトと配列の比較

=

および===

演算子の動作は配列およびオブジェクトに対して定義されていないため、現時点ではこの比較を避ける必要があります。将来的には、オブジェクトと配列の比較が実装される予定です。

コマンドラインオプション

 % jl-sql -h

 Usage: jl-sql [OPTIONS] SQL OPTIONS: -h, --help show this help -I, --ignore-json-error ignore broken JSON -v, --verbose display additional information -B, --sort-in-memory-buffer-length=ROWS save up to ROWS rows for in-memory sort -S, --sort-external-buffer-size=SIZE use SIZE bytes for `sort` memory buffer -T, --temporary-directory=DIR use DIR for temporaries, not $TMPDIR or /tmp -b, --bind=BIND=VALUE+ bind valiable See full documentation at https://github.com/avz/jl-sql

興味深いオプションの詳細。

-I

：JSONのエラーを無視します。デフォルトでは、JSONで最初のエラーが検出された後、 jl-sql

失敗します（ゼロ以外の戻りコード）。このオプションを使用すると、この動作をオーバーライドして、そのようなエラーを単に無視できます。 stderrの警告出力が残る
-B

：システムユーティリティsort

による外部ソートを使用せずにRAMでソートされる行/オブジェクトの数に制限を設定しsort
-S

：バッファーとしてsort

使用されるRAMの数に制限を設定します（ man sort

-S

オプションの説明を参照）
-T

：一時的なソートファイルをホストするディレクトリ

参照資料

https://github.com/avz/jl-sql-メインユーティリティリポジトリ
https://github.com/avz/node-jl-sql-api-SQLエンジンリポジトリ

ご清聴ありがとうございました。

jl-sql：コマンドラインでのJSONログのSQLクエリ

簡単な説明

例

一意のIPアドレスを定義する

各グループの一意のアドレスの数を数える

オブジェクトの再構築

削除して変更

日付を操作する

ユニオン（ `JOIN`

）

パフォーマンスと消費

設置

汚れた詳細

仕分け

バインディング

`JOIN`

比較演算子

オブジェクトと配列の比較

コマンドラインオプション

参照資料

More articles:

jl-sql：コマンドラインでのJSONログのSQLクエリ

簡単な説明

例

一意のIPアドレスを定義する

各グループの一意のアドレスの数を数える

オブジェクトの再構築

削除して変更

日付を操作する

ユニオン（ JOIN (adsbygoogle = window.adsbygoogle || []).push({}); (adsbygoogle = window.adsbygoogle || []).push({}); (adsbygoogle = window.adsbygoogle || []).push({}); ）

パフォーマンスと消費

設置

汚れた詳細

仕分け

バインディング

JOIN (adsbygoogle = window.adsbygoogle || []).push({}); (adsbygoogle = window.adsbygoogle || []).push({}); (adsbygoogle = window.adsbygoogle || []).push({});

比較演算子

オブジェクトと配列の比較

コマンドラインオプション

参照資料

More articles:

ユニオン（ `JOIN`

）

`JOIN`