| Getting started MySQL as Document Data Store Chihiro Ito (@chiroito) Technologist & Oracle Groundbreaker Advocate Oracle Japan March 26, 2019 @Oracle MySQL Cafe #2 April 19, 2019 @中国地方DB勉強会 in 沖縄 April 20, 2019 @Open Source Conference 2019 OKINAWA
| What's X DevAPI? • This slide doesn't explain what's X DevAPI. • If you would like to know it, please google X DevAPI. • This slide explains X DevAPI using the code. • NOTICE – Sample code is focused on understanding X DevAPI. – Therefore, some code is not suitable for production applications. 4 X DevAPIについての資料はインターネット上にたくさんあるのでそちらをご覧ください。今回はコードで説明します。 このスライドに登場するコードはX DevAPI の理解に焦点をあてているため、実際のアプリケーションには適さないコードを含んでいますのでご注意下さい。
| • Bulletin board system • Each BBS specialized in area 7 Demo scenario Taro How is MySQL? xxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxx xxxxxxxxxxx Cool ! Hanako What is green tea? xxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxx xxxxxxxxxxx Good ! User Name Title Text 本スライドにはいくつかのデモを含みます。デモで使用するシステムは地域を意識した掲示板システムをイメージしてます。
| •Contents –Title –Text –User Id –Area Id •User –Name 8 •Area –ID –Name Sample Data on this demo Collection Table デモシステムで使用するデータは3種類です。 システムの成長に合わせて型が変りそうなコンテンツとユーザの情報はコレクションとし、変らなさそうな地域のマスタ情報はテーブルとします。 Data models are likely to change in the future. Data model is hard to change.
| Session • Create/get schema • Begin/commit/rollback Schema • Create/get collection • Create/get Table 11 Collection – Manage JSON (Find, Add, etc) – ID field is created by X DevAPI Table • Manage tables (Select, Insert, etc) • collection can be used as table What should developers know in X DevAPI X DevAPIを使う上で覚える必要があるインターフェースはこの4つです。 あとは流れるようなインターフェースでコードを書くためあまりインターフェースやクラスを意識しません。
| 12 Relation of classes in X DevAPI Schema Collection create/get Session Table create/get get 先ほどの4つのインターフェースの関係です。 SessionFactoryクラスを使ってSessionオブジェクトを作成し、SessionオブジェクトはSchemaを作成したり取得します。 SchemaオブジェクトはCollectionオブジェクトの作成や取得とTableオブジェクトを取得します。
| Connect to MySQL using X DevAPI String url = "mysqlx://user:[email protected]:33060"; SessionFactory sf = new SessionFactory(); Session session = sf.getSession(url); // code session.close(); 13 Java X DevAPIを使用してMySQLへ接続します。 プロトコル、ユーザ名、パスワード、ホスト名、ポート番号を組み合わせてURLとしてSessionFactoryオブジェクトのgetSessionメソッドの引数に指定します。 プロトコルはmysqlx、デフォルトのポート番号は33060となり、JDBCでMySQLへ接続するときのプロトコル、ポート番号とは異なるので注意して下さい。
| Use collection as table Table contents = foo.getCollectionAsTable("Contents"); 16 SELECT T.table_name AS name, IF(ANY_VALUE(T.table_type) LIKE '%VIEW',IF(COUNT(*)=1 AND COUNT(CASE WHEN (column_name = 'doc' AND data_type = 'json') THEN 1 ELSE NULL END)=1, 'COLLECTION_VIEW', 'VIEW'), IF(COUNT(*)-2 = COUNT(CASE WHEN (column_name != '_id' AND column_name != 'doc' AND generation_expression RLIKE 'json_extract¥¥(`doc`,(_[[:alnum:]]+)?¥¥¥¥''¥¥$((¥¥*{2})?(¥¥[([[:digit:]]+|¥¥*)¥¥]|¥¥.([[:alpha:]_¥¥ $][[:alnum:]_¥¥$]*|¥¥*|¥¥".*¥¥")))*¥¥¥¥''¥¥)') THEN 1 ELSE NULL END) AND COUNT(CASE WHEN (column_name = 'doc' AND data_type = 'json') THEN 1 ELSE NULL END)=1 AND COUNT(CASE WHEN (column_name = '_id' AND generation_expression RLIKE '^json_unquote¥¥(json_extract¥¥(`doc`,(_[[:alnum:]]+)?¥¥¥¥''¥¥$¥¥._id¥¥¥¥''¥¥)¥¥)$') THEN 1 ELSE NULL END)=1, 'COLLECTION', 'TABLE')) AS type FROM information_schema.tables AS T LEFT JOIN information_schema.columns AS C ON (T.table_schema = C.table_schema AND T.table_name = C.table_name) WHERE T.table_schema = 'foo' AND T.table_name LIKE 'fo%' GROUP BY name ORDER BY name Java SQL X DevAPIには、コレクションを表として使う機能があります。 SchemaオブジェクトのgetCollectionAsTableメソッドにコレクション名を指定することでTableオブジェクトを取得します。 これにより、非常に複雑なSELECT文が実行されます。
| Modify a document by field 27 ModifyStatement stmt = contents.modify("_id=:A") .bind("A",id).set("text", "Great"); Result result = stmt.execute(); UPDATE `foo`.`contents` SET doc= JSON_SET(JSON_SET(doc,'$.text','Great'),'$._id',JSON_EXTRACT(`doc`,'$._id')) WHERE (JSON_EXTRACT(doc,'$._id') = '00005c73ba83000000000000004a') SQL Java 特定の条件に一致したドキュメントを対象に、そのドキュメントの一部を変更します。この例ではドキュメントIDが先述のidと等しいドキュメントの文章をGreatに変更します。 Collectionオブジェクトのmodifyメソッドに_idが変数Aと等しいものを取るように指定してModifyStatementを作成し、bindメソッドでAに先述のidを指定します。 ドキュメントの一部を変更するためsetメソッドで変更後の値としてGreatを指定します。これにより条件をWHERE句に指定したUPDATE文を実行します。
| Replace a document by id 29 Result result = contents.replaceOne(id, newContent); UPDATE `foo`.`contents` SET doc=JSON_SET(JSON_SET(doc,'$',JSON_OBJECT('area','1','text','Awesome !','title' ,'MySQL Document store','user','00005c96cc540000001')), '$._id',JSON_EXTRACT(`doc`,'$._id')) WHERE (JSON_EXTRACT(doc,'$._id') = '00005c73ba83000000000000004a') SQL Java ドキュメントのIDを指定して、既にあるドキュメントを新しいドキュメントで置き換えます。この例ではドキュメントIDが先述したidのドキュメントを置き換えます。 これにはCollectionオブジェクトのreplaceOneメソッドにドキュメントのIDとしてidを指定し、新しいドキュメントとしてnewContentを指定します。 これによってドキュメントの_idフィールドをWHERE句に指定したUPDATE文が実行されます。
| Remove a document by id 31 Result result = contents.removeOne(id); DELETE FROM `foo`.`contents` WHERE (JSON_EXTRACT(doc,'$._id') = '00005c73ba83000000000000004a') SQL Java ドキュメントのIDが分かっている特定のドキュメントを削除します。この例ではドキュメントIDが先述のidのドキュメントを削除します。 これにはCollectionオブジェクトのremoveOneメソッドにドキュメントのIDとしてidを指定します。 これによってドキュメントから_idを取り出してその値がidと等しい条件WHERE句に指定したDELETE文が実行されます。
| 33 Demo #2 contents.add(doc).execute(); contents.find().execute().forEach(d -> System.out.println(String.format("| %s | %s |", d.get("_id"), d.get("title")))); | "00005cb73cda0000000000000066" | "MySQL Document store" | 1.Add a "content" into the collection 2.Show all "contents" using SQL 3. Find all "contents" using X DevAPI
| Drop Index 35 contents.dropIndex("areaIndex"); ALTER TABLE `foo`.`contents` DROP INDEX `areaIndex`, DROP COLUMN `$ix_i_4F6F5D1B1EC6D0F28A8E18CAB2C5EB2C03DB2D2C` SQL Java 索引を削除します。この例では先ほど作成したコンテンツコレクションの地域IDに作成した索引を削除します。 CollectionオブジェクトのdropIndexメソッドに索引名を指定します。 これにより、索引と仮想列が削除されます。
| Demo#3 1. Add 1,000 "contents" into the collection 2. Show explain plan 3. Add an index by "area" field in the collection 4. Show index 5. Show explain plan again 36
| 37 Demo#3 for (int i = 0; i < 1000; i++) { JsonString title = new JsonString().setValue("Title"); JsonNumber area = new JsonNumber().setValue("" + (i % 47)); JsonString user = new JsonString().setValue("00005c96cc54000000000000c8d7"); JsonString text = new JsonString().setValue("xxx"); DbDoc d = new DbDocImpl().add("title", title).add("area", area).add("user", user).add("text", text); contents.add(d).execute(); } 1. Add 1,000 "contents" into the collection 2. Show explain plan
| 38 Demo#3 3. Add an index by "area" field in the collection 5. Show explain plan again contents.createIndex("areaIndex", "{¥"fields¥": [{¥"field¥":¥"$.area¥", ¥"type¥":¥"INTEGER¥"}]}"); session.sql(String.format("ANALYZE TABLE %s.%s;", foo.getName(), contents.getName())); 4. Show index
| Execute native SQL 39 String sql = “SELECT doc->>'$.title' AS title FROM foo.contents"; SqlResult result = session.sql(sql).execute(); SELECT doc->>'$.title' AS title FROM foo.contents SQL Java 結合をするためにはコレクションに対してSQLを書かないといけません。書き方が特殊なので紹介します。SQLの実行にはSessionオブジェクトのsqlメソッドを使用します。 この例ではコンテンツのコレクションにある全てのドキュメントのタイトルを取得します。 コレクションに格納されてるドキュメントの各要素を取り出すには、doc->>'$.要素名'のように指定します。title要素の場合はdoc->>'$.title'と指定します。
| 41 Joining a collection and a table ID NAME 1 Tokyo 2 Kyoto { "title" :"MySQL Document store", "text" :"Cool!", "area" :"1", "user" :"00005c96cc540000001" } Content collection Area table コレクションとテーブルを結合します。今回は、コンテンツのコレクションと地域の表を結合します。 結合にはコンテンツコレクションのareaフィールドと地域表のIDカラムを使います。
| Joining a collection and a table 42 String sql = "SELECT c.doc->>'$.title' AS title, a.name AS area FROM foo.contents c LEFT OUTER JOIN foo.area a ON c.doc->>'$.area' = a.id"; SqlResult result = session.sql(sql).execute(); SELECT c.doc->>'$.title' AS title, a.name AS area FROM foo.contents c LEFT OUTER JOIN foo.area a ON c.doc->>'$.area' = a.id SQL Java コンテンツのコレクションから地域を取り出すには doc->>'$.area'を指定します。地域表のIDはidカラムを指定するだけです。 これらの２つの値をLEFT OUTER JOINで結合します。コレクションから値の取り出し方が特殊なだけで表の結合と変りません。
| Caution !! Which _id should you use? 44 コレクションとして使われる表の _id と doc->>'$._id' は同じ値が入っています。 しかし、id は索引が貼られていますが、doc->>'$._id' には索引が貼られていないため注意が必要です。 • _id column – Indexed – You should use _id column in WHERE and JOIN • doc->>'$._id' – Not Indexed – You shouldn't use doc->>'$._id' in WHERE and JOIN
| Joining two collection 45 String sql = "SELECT c.doc->>'$.title' AS title, u.doc->>'$.name' AS user FROM foo.contents c LEFT OUTER JOIN foo.users u ON c.doc->>'$.user' = u._id"; SqlResult result = session.sql(sql).execute(); SELECT c.doc->>'$.title' AS title, u.doc->>'$.name' AS user FROM foo.contents c LEFT OUTER JOIN foo.users u ON c.doc->>'$.user' = u._id SQL Java コレクション同士の結合は少し注意が必要です。コンテンツコレクションのuserフィールドにはユーザコレクションの_idフィールドの値を入れましょう。 コンテンツのコレクションからユーザを取り出すには doc->>'$.user'を指定します。ユーザコレクションのIDは_idカラムを指定します。 これらの２つの値をLEFT OUTER JOINで結合します。ユーザの_idカラムを使うことでドキュメントの解析が不要になります。
| Demo #4 1. Add 1,000 content and 100 user, and insert 50 area 2. Show explain plan of joining a collection and a table 3. Show explain plan of joining two collections using doc->>'$._id' 4. Show explain plan of joining two collections using _id 46
| 47 Demo #4 1. Add a content and 100 user and Insert 100 area 2. Show explain plan of joining a collection and a table 3. Show explain plan of joining two collections using doc->>'$._id' 4. Show explain plan of joining two collections using _id
chiroito
kazuhitotakahashi
lycorp_recruit_jp
ysknsid25
shujisado
nikinusu
ryomrt
tenshoku_draft
negi111111
hirosatogamo
kazzpapa3
tohutohu
ken5scal
tanoku
marktimemedia
bluesmoon
csswizardry
kastner
holman
morganepeng
paigeccino
bryan
bermonpainter
rmw
