エントリは誰にとっても面白くないので、すぐに使用例から始めましょう
% 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:
興味深いオプションの詳細。
-I ：JSONのエラーを無視します。 デフォルトでは、JSONで最初のエラーが検出された後、 jl-sql失敗します（ゼロ以外の戻りコード）。 このオプションを使用すると、この動作をオーバーライドして、そのようなエラーを単に無視できます。 stderrの警告出力が残る-B ：システムユーティリティsortによる外部ソートを使用せずにRAMでソートされる行/オブジェクトの数に制限を設定しsort-S ：バッファーとしてsort使用されるRAMの数に制限を設定します（ man sort -Sオプションの説明を参照）-T ：一時的なソートファイルをホストするディレクトリ
参照資料
ご清聴ありがとうございました。