PartiQLを使ってみた JSON⽇の⾦曜⽇@新潟 12週⽬ 2019/09/13 (⾦) 笠原 宏

⾃⼰紹介 笠原 宏 (@kasacchiful) 新潟市在住 Rubyがお気に⼊り だけど、最近はRとPythonばかり... コミュニティ JAWS-UG新潟 (明⽇この会場で！) https://connpass.com/event/142705/ JaSST新潟実⾏委員 ASTER正会員 他 ⾊々...

本⽇の内容 新しい問い合わせ⾔語"PartiQL"を使ってみる Tutorialやってみた

PartiQLとは何か https://partiql.org/ AWSが今年の8⽉にオープンソースとして公開 https://aws.amazon.com/jp/blogs/opensource/announcing-partiql-one-query- language-for-all-your-data/ SQL互換 RDB以外に、KVS / JSON / CSV など、様々なデータソースに対して横断的に検索できる 問い合わせ⾔語およびそのリファレンス実装 参考: AWS、SQL互換の新問い合わせ⾔語「PartiQL」をオープンソースで公開。RDB、 KVS、JSON、CSVなどをまとめて検索可能 － Publickey

PartiQLの特徴 SQL互換 SQL-92の後⽅互換性があるので、SQLがそのまま動きます。 PartiQLは、SQLに最⼩限の拡張をしたもの ⼊れ⼦形式のデータが使える スキーマがデータに適⽤されていても、されていなくても、データが同じであれば結果は 変わらない データフォーマットに⾮依存 JSON、Parquet、ORC、CSV、他様々なデータフォーマットに依存しない データストアに⾮依存 もちろんデータストアにも依存しない

すでにAmazonやAWSサービス内で使われている Amazonの⼩売部⾨がRDBやNoSQLなど、様々なデータストアにデータを保存してお り、それらを効率よく活⽤するために必要になったから AWSサービスの⼀部にも使われている S3 Select / Glacier Select Redshift Spectrum Quantum Ledger Database (QLDB) 他にも、Couchbase ServerがPartiQLをサポートする⾒通し

チュートリアルをやってみた PartiQL Tutorial

必要なもの パッケージファイルのダウンロード (PartiQL REPLが含まれている) https://github.com/partiql/partiql-lang-kotlin/releases Java Runtime OpenJDK もしくは Oracle Java なお、実装はKotlin。 https://github.com/partiql/partiql-lang-kotlin

データ Tutorial/code/q1.env { 'hr': { 'employees': << -- a tuple is denoted by { ... } in the PartiQL data model { 'id': 3, 'name': 'Bob Smith', 'title': null }, { 'id': 4, 'name': 'Susan Smith', 'title': 'Dev Mgr' }, { 'id': 6, 'name': 'Jane Smith', 'title': 'Software Eng 2'} >> } }

PartiQLクエリ PartiQLのクエリは、SQL-92と後⽅互換性がある https://partiql.org/tutorial.html#partiql-queries-are-sql-compatible SELECT e.id, e.name AS employeeName, e.title AS title FROM hr.employees e WHERE e.title = 'Dev Mgr'

REPL起動と結果 > bin/partiql -e Tutorial/code/q1.env Welcome to the PartiQL REPL! PartiQL> SELECT e.id, e.name AS employeeName, e.title AS title FROM hr.employees e WHERE e.title = 'Dev Mgr' | | | | | ===' << { 'id': 4, 'employeeName': 'Susan Smith', 'title': 'Dev Mgr' } >> --- OK! (50 ms)

PartiQLデータモデル データストレージ形式の抽象化 SQLのテーブルだけでなく、JSON等のネストやタプル、半構造化フォーマットでよく⾒ られるデータも扱うようにするため そのため、PartiQL実装はJSON等のデータフォーマットをPartiQLデータモデルに従う形 で、PartiQLデータセットにマッピングさせる必要がある

以下は、 hr.employees テーブルをPartiQLデータモデルで⽰した場合 { 'hr': { 'employees': << -- a tuple is denoted by { ... } in the PartiQL data model { 'id': 3, 'name': 'Bob Smith', 'title': null }, { 'id': 4, 'name': 'Susan Smith', 'title': 'Dev Mgr' }, { 'id': 6, 'name': 'Jane Smith', 'title': 'Software Eng 2'} >> } } デリミタ: << .. >> unorderd collection (SQLのテーブルのようなデータを扱う場合)

JSONで表すとこんな感じ これをPartiQLで扱うには、PartiQLデータモデルとして変換した上で使⽤することになる { "hr" : { "employees": [ { "id": 3, "name": "Bob Smith", "title": null }, { "id": 4, "name": "Susan Smith", "title": "Dev Mgr" }, { "id": 6, "name": "Jane Smith", "title": "Software Eng 2"} ] } } JSONはダブルクォーテーションだが、PartiQLはシングルクォーテーション (SQL互換の ため)

JSONファイルを読み込ませるとどうなるか > bin/partiql -e ./q1.json No such binding: id 読み込めない

変換させる [ ] → << >> " → ' { 'hr' : { 'employees': << { 'id': 3, 'name': 'Bob Smith', 'title': null }, { 'id': 4, 'name': 'Susan Smith', 'title': 'Dev Mgr' }, { 'id': 6, 'name': 'Jane Smith', 'title': 'Software Eng 2'} >> } }

bin/partiql -e ./q1.json.env Welcome to the PartiQL REPL! PartiQL> 読み込めた

ネストするデータはどうか Tutorial/code/q2.env { 'hr': { 'employeesNest': << { 'id': 3, 'name': 'Bob Smith', 'title': null, 'projects': [ { 'name': 'AWS Redshift Spectrum querying' }, { 'name': 'AWS Redshift security' }, { 'name': 'AWS Aurora security' } ] -- タブルの中に配列がある }, { 'id': 4, 'name': 'Susan Smith', 'title': 'Dev Mgr', 'projects': [] }, { 'id': 6, 'name': 'Jane Smith', 'title': 'Software Eng 2', 'projects': [ { 'name': 'AWS Redshift security' } ] } >> } }

PartiQLクエリ SELECT e.name AS employeeName, p.name AS projectName FROM hr.employeesNest AS e, e.projects AS p -- ここが標準SQL の拡張表記 WHERE p.name LIKE '%security%'

結果 bin/partiql -e Tutorial/code/q2.env Welcome to the PartiQL REPL! PartiQL> SELECT e.name AS employeeName, p.name AS projectName FROM hr.employeesNest AS e, e.projects AS p WHERE p.name LIKE '%security%' | | | | | ===' << { 'employeeName': 'Bob Smith', 'projectName': 'AWS Redshift security' }, { 'employeeName': 'Bob Smith', 'projectName': 'AWS Aurora security' }, { 'employeeName': 'Jane Smith', 'projectName': 'AWS Redshift security' } >> --- OK! (69 ms)

他にもこんなこともできるよ (サブクエリ) SELECT e.name AS employeeName FROM hr.employeesNest AS e WHERE EXISTS ( SELECT * FROM e.projects AS p WHERE p.name LIKE '%security%') return << { 'employeeName': 'Bob Smith' }, { 'employeeName': 'Jane Smith' } >>

他にもこんなこともできるよ (Group by 句 / Having 句) SELECT e.name AS employeeName FROM hr.employeesNest AS e, e.projects AS p WHERE p.name LIKE '%security%' GROUP BY e.id, e.name HAVING COUNT(*) > 1 return << { 'employeeName': 'Bob Smith' } >>

他にもこんなこともできるよ (集計) SELECT e.name AS employeeName, COUNT(p.name) AS queryProjectsNum FROM hr.employeesNest e LEFT JOIN e.projects AS p ON p.name LIKE '%querying%' GROUP BY e.id, e.name return << { 'employeeName': 'Bob Smith', 'queryProjectsNum': 1 }, { 'employeeName': 'Susan Smith', 'queryProjectsNum': 0 }, { 'employeeName': 'Jane Smith', 'queryProjectsNum': 0 } >>

他にも⾊々できるので、チュートリアルを試してみよう PartiQL Tutorial

まとめ 新しい問い合わせ⾔語"PartiQL"を使ってみました 今の実装はJSONファイルをPartiQLデータセット形式に変換してあげないと、読み込め ない 実装次第で、JSONファイルを⾃動的にPartiQLデータセット形式に変換して扱える ようになるはず とりあえずTutorialやってみよう