Migrating MySQL Queries to N1QL: Couchbase Connect 2015

Migrating MySQL Queries to N1QL: Couchbase Connect 2015

1. 1. MIGRATING RDBMS QUERIES TO N1QL Parag Agarwal, Couchbase Raju Suravarjjala, Couchbase
2. ©2015 Couchbase Inc. Agenda Motivation of Migration Factors in migration Data model Data type Keys and Indexes Query statements CRUD operations Summary Demo
3. ©2015 Couchbase Inc. Motivation for Migration to Couchbase Flexible/Dynamic Schema Support for JSON and Binary data Hierarchical data Extension of SQL Highly Scalable Highly Available
4. ©2015 Couchbase Inc. RDBMS (e.g., MySQL): How to migrate to Couchbase? order_no (pk) cust_id (fk) purchase_dt item_id (fk) o1 1234 2015-01-01 00:00:00 a1234 cust_id (pk) Name Address phone_no c1 Adam Smith xxx 4087378383 SALES item_id (pk) desc manufacture_dt i1 shirt 2014-01-01 00:00:00 ORDER CUSTOMER ITEM SELECT * FROM ORDER SELECT * FROM CUSTOMER SELECT * FROM CUSTOMER CREATE PRIMARY INDEX P_IDX ON ORDER CREATE INDEX IDX ON ORDER(Name) SELECT * FROM ORDER o INNER JOIN CUSTOMER c ON o.cust_id = c. o.cust_id SELECT * FROM ORDER o WHERE o.cust_id = (SELECT cust_id from CUSTOMER)
5. ©2015 Couchbase Inc. RDBMS (e.g., MySQL) : Factors in migration CRUD Ops Keys Data Types Data Model Indexes SQL statements
6. Data Model
7. ©2015 Couchbase Inc. DATA MODEL: Map RDBMS to Couchbase order_no (pk) cust_id (fk) purchase_dt item_id (fk) o1 1234 2015-01-01 00:00:00 a1234 cust_id (pk) Name Address phone_no c1 Adam Smith xxx 4087378383 SALES item_id (pk) desc manufacture_dt i1 shirt 2014-01-01 00:00:00 ORDER CUSTOMER ITEM How to map RDBMS database to Couchbase bucket(s)/JSON documents?
8. ©2015 Couchbase Inc. DATA MODEL: Map RDBMS to Couchbase RDBMS Couchbase Database Bucket(s) Table Bucket(s)/keyspaces Row JSON Document Fixed Schema Flexible Schema
9. ©2015 Couchbase Inc. MapTables to Buckets: Normalized Data order_no (pk) cust_id (fk) purchase_dt item_id (fk) o1 c1 2015-01-01 00:00:00 i1 cust_id (pk) Name Address phone_no c1 Adam Smith xxx 4087378383 SALES item_id (pk) desc manufacture_dt i1 shirt 2014-01-01 00:00:00 ORDER CUSTOMER ITEM BUCKET: order “o1”: { “cust_id”:”c1”, “purchase_dt”: “2015-01-01 00:00:00”, “item_id”:”i1” } BUCKET: customer “c1”: { “Name”: “Adam Smith”, “Address”: “XXX”, “phone_no”: 4087378383 } BUCKET: item “i1”: { “desc”: “shirt”, “manufacture_dt”: “2015-01-01 00:00:00” }
10. ©2015 Couchbase Inc. Map Database to a Bucket: Normalized Data order_no (pk) cust_id (fk) purchase_dt item_id (fk) o1 c1 2015-01-01 00:00:00 i1 cust_id (pk) Name Address phone_no c1 Adam Smith xxx 4087378383 SALES item_id (pk) desc manufacture_dt i1 shirt 2014-01-01 00:00:00 ORDER CUSTOMER ITEM BUCKET: sales “o1”: { “order”:{ “cust_id”:”c1”, “purchase_dt”: “2015-01-01 00:00:00”, “item_id”:”i1” }} “c1”: { ”customer”:{ “Name”: “Adam Smith”, “Address”: “XXX”, “phone_no”: 4087378383 }} “i1”: { “item”:{ “desc”: “shirt”, “manufacture_dt”: “2015-01-01 00:00:00” }}
11. ©2015 Couchbase Inc. Map Database(s) to a Bucket: Normalized Data order_no (pk) cust_id (fk) purchase_dt item_id (fk) o1 c1 2015-01-01 00:00:00 i1 cust_id (pk) Name Address phone_no c1 Adam Smith xxx 4087378383 SALES item_id (pk) desc manufacture_dt i1 shirt 2014-01-01 00:00:00 ORDER CUSTOMER ITEM BUCKET: Default “o1”: { “sales”:{ “order”:{ “cust_id”:”c1”, “purchase_dt”: “2015-01-01 00:00:00”, “item_id”:”i1” }}} “c1”: { “sales”:{ “customer”:{ “Name”: “Adam Smith”, “Address”: “XXX”, “phone_no”: 4087378383 }}} “i1”: { “sales”:{ “item”:{ “desc”: “shirt”, “manufacture_dt”: “2015-01-01 00:00:00” }}}
12. ©2015 Couchbase Inc. Map Database(s) to a Bucket: Denormalized Data order_no (pk) cust_id (fk) purchase_dt item_id (fk) o1 c1 2015-01-01 00:00:00 i1 cust_id (pk) Name Address phone_no c1 Adam Smith xxx 4087378383 SALES item_id (pk) desc manufacture_dt i1 shirt 2014-01-01 00:00:00 ORDER CUSTOMER ITEM BUCKET: Default “o1”: { “sales”:{ “order”:{ “purchase_dt”: “2015-01-01 00:00:00” “customer”: { “Name”: “Adam Smith”, “Address”: “XXX”, “phone_no”: 4087378383 }, “item”:{ “desc”: “shirt”, “manufacture_dt”: “2015-01-01 00:00:00” } } }
13. DataType
14. ©2015 Couchbase Inc. DATATYPES MAPPING Data Feature/Type RDBMS Couchbase Case Sensitivity Yes/No Yes (fields only)/No Numbers supported supported String supported supported boolean supported as tinyint supported datetime supported supported as string in JSON Spatial data supported supported MISSING not supported supported NULL supported supported Objects and Arrays not supported supported Blobs supported supported
15. ©2015 Couchbase Inc. DATATYPES : MISSING in JSON MISSING To track if a given field is present in a JSON document SELECT * from customer where name IS MISSING use to check presence of (name, value) pair Bucket: default “1”:{ “name”:“dhdh” } “2”:{ “value”:“xxxx” } Results in output [{“value”:“xxxx”}]
16. ©2015 Couchbase Inc. FOURVALUE LOGIC: Impact of MISSING A B A OR B A AND B TRUE NULL TRUE NULL FALSE NULL FALSE NULL TRUE MISSING TRUE MISSING FALSE MISSING TRUE MISSING NULL MISSING NULL MISSING NULL NULL NULL NULL MISSING MISSING MISSING MISSING backward compatible with RDBMS
17. ©2015 Couchbase Inc. FOURVALUE LOGIC: Impact of MISSING A NOT A TRUE FALSE FALSE TRUE MISSING MISSING NULL NULL backward compatible with RDBMS
18. KEYS AND INDEXING
19. ©2015 Couchbase Inc. KEYS : PRIMARY KEY order_no (pk) cust_id (fk) purchase_dt item_id (fk) o1 c1 2015-01-01 00:00:00 i1 cust_id (pk) Name Address phone_no c1 Adam Smith xxx 4087378383 SALES item_id (pk) desc manufacture_dt i1 shirt 2014-01-01 00:00:00 ORDER CUSTOMER ITEM BUCKET: Default “o1”: { “sales”:{ “order”:{ “cust_id”:”c1”, “purchase_dt”: “2015-01-01 00:00:00”, “item_id”:”i1” }}} “c1”: { “sales”:{ “customer”:{ “Name”: “Adam Smith”, “Address”: “XXX”, “phone_no”: 4087378383 }}} “i1”: { “sales”:{ “item”:{ “desc”: “shirt”, “manufucture_dt”: ”2015-01-01 00:00:00” }}}primary is key of (key, value) pair primary/surrogate key
20. ©2015 Couchbase Inc. KEYS : FOREIGN KEY order_no (pk) cust_id (fk) purchase_dt item_id (fk) o1 c1 2015-01-01 00:00:00 i1 cust_id (pk) Name Address phone_no c1 Adam Smith xxx 4087378383 SALES item_id (pk) desc manufacture_dt i1 shirt 2014-01-01 00:00:00 ORDER CUSTOMER ITEM BUCKET: Default “o1”: { “sales”:{ “order”:{ “cust_id”:”c1”, “purchase_dt”: “2015-01-01 00:00:00”, “item_id”:”i1” }}} “c1”: { “sales”:{ “customer”:{ “Name”: “Adam Smith”, “Address”: “XXX”, “phone_no”: 4087378383 }}} “i1”: { “sales”:{ “item”:{ “desc”: “shirt”, “manufucture_dt”: ”2015-01-01 00:00:00” }}} primary secondary secondary
21. ©2015 Couchbase Inc. Indexing Indexing provided using views (existed prior to 4.0 Beta) global secondary indexes (gsi) Support for primary index provided by views provided by gsi Support for secondary index provided by views provided by gsi Support for hints (views/gsi), similar to MySQL
22. ©2015 Couchbase Inc. Indexes : Primary Indexes Primary indexes are built only using key of (key,value) pair GSI CREATE Example: CREATE primary index on customer using gsi VIEW CREATE Example: CREATE primary index on customer using view Alias CREATE Example: CREATE primary index pk_index1 on customer using gsi VIEW Drop Example: DROP primary index on customer using view GSI Drop Example: DROP primary index on customer using gsi
23. ©2015 Couchbase Inc. Indexes : Secondary Indexes Simple: CREATE INDEX idx on customer(cust_id) using GSI (does not throw error if cust_id does not exist) Composite: CREATE INDEX idx on customer(cust_id, Name) using GSI Where condition: CREATE INDEX idx on customer(Name) WHERE Name LIKE ‘a%’ using GSI Computed: CREATE INDEX idx on employee(First_name || “ “ || Last_Name) using GSI Drop index: DROP INDEX customer.idx USING GSI
24. SQL Statements
25. ©2015 Couchbase Inc. Operators: Searching in JSON (NESTED DATA) BUCKET: Default “o1”: { “sales”:{ “order”:{ “purchase_dt”: “2015-01-01 00:00:00” “customer”: { “Name”: “Adam Smith”, “Address”: “XXX”, “phone_no”: 4087378383 }, “item”:{ “desc”: “shirt”, “manufacture_dt”: “2015-01-01 00:00:00”, “store_id”:[11,22,33] } } } Example: Field SELECT c.Name FROM sales.order.customer c Example: Array Element SELECT i.store_id[1] str_id FROM sales.order.item i Example: All fields SELECT o.customer.* FROM sales.order o [ { “Name”: “Adam Smith”, “Address”: “XXX”, “phone_no”: 4087378383 } ] [ { “str_id”:22 } ] [ { “Name”: “Adam Smith” } ] INPUT: (KEY, VALUE) N1QL JSON OUTPUT
26. ©2015 Couchbase Inc. Operators: Searching in JSON (NON-EXISTING FIELDS) IS MISSING SELECT * FROM sales.customer WHERE Name IS MISSING example json { “id”:”233” } No Errors for missing fields SELECT * FROM sales.customer WHERE field= ‘xxx’ example json { “name”:NULL } New featureFlexible Schema
27. ©2015 Couchbase Inc. Operators: Searching in JSON (NON-EXISTINGVALUES) IS VALUED SELECT * FROM sales.customer WHERE Name IS VALUED example json { “name”:”Adam Smith”, “id”:”” } IS NULL SELECT * FROM sales.customer WHERE Name IS NULL example json { “name”:NULL } same as sqlNew feature
28. ©2015 Couchbase Inc. JOINS: LEFT [OUTER], INNER order_no (pk) cust_id (fk) purchase_dt item_id (fk) o1 c1 2015-01-01 00:00:00 i1 cust_id (pk) Name Address phone_no c1 Adam Smith xxx 4087378383 SALES item_id (pk) desc manufacture_dt i1 shirt 2014-01-01 00:00:00 ORDER CUSTOMER ITEM BUCKET: sales “o1”: { “order”{ “cust_id”:”c1”, “purchase_dt”: “2015-01-01 00:00:00”, “item_id”:”i1” }} “c1”: { “customer”:{ “Name”: “Adam Smith”, “Address”: “XXX”, “phone_no”: 4087378383 }} “i1”: { “item”:{ “desc”: “shirt”, “manufucture_dt”: ”2015-01-01 00:00:00” }} ON KEYS outer_keyspace.field matches with primary key of inner_keyspace SQL SELECT * FROM ORDER o INNER JOIN CUSTOMER c ON o.cust_id = c.cust_id INNER JOIN ITEM i ON o.item_id = i.item_id N1QL SELECT * FROM sales.order INNER JOIN sales.customer ON KEYS sales.order.cust_id INNER JOIN sales.item ON KEYS sales.order.item_id
29. ©2015 Couchbase Inc. JOINS: OUTPUT OF JOIN - NESTED DATA BUCKET: sales “o1”: { ”order”{ “cust_id”:”c1”, “purchase_dt”: “2015-01-01 00:00:00”, “item_id”:”i1” }} “c1”: { ”customer”:{ “Name”: “Adam Smith”, “Address”: “XXX”, “phone_no”: 4087378383 }} “i1”: { “item”:{ “desc”: “shirt”, “manufacture_dt”: “2015-01-01 00:00:00” }} [ { “order”:{ “purchase_dt”: ”2015-01-01 00:00:00” }, “customer”: { “Name”: “Adam Smith”, “Address”: “XXX”, “phone_no”: 4087378383 }, “item”:{ “desc”: “shirt”, “manufacture_dt”: “2015-01-01 00:00:00” } } ] N1QL SELECT * FROM sales.order INNER JOIN sales.customer ON KEYS sales.order.cust_id INNER JOIN sales.item ON KEYS sales.order.item_id OUTPUT JSONINPUT : (KEY, VALUE )
30. ©2015 Couchbase Inc. NEST : CREATE NESTED JSON BUCKET: sales “o1”: { “order”{ “cust_id”:“c1”, “purchase_dt”: “2015-01-01 00:00:00”, “item_id”:“i1” }} “c1”: { “customer”:{ “Name”: “Adam Smith”, “Address”: “XXX”, “phone_no”: 4087378383 }} “i1”: { “item”:{ “desc”: “shirt”, “manufucture_dt”: “2015-01-01 00:00:00” }} N1QL SELECT sales.order.*, sales.item.* FROM sales.order NEST sales.item ON KEYS sales.order.item_id [ { “order”:{ “purchase_dt”: “2015-01-01 00:00:00” }, “item”:[ { “desc”: “shirt”, “manufucture_dt”: “2015-01-01 00:00:00” } ] } ] NESTED JSONINPUT: (KEY, VALUE)
31. ©2015 Couchbase Inc. UNNEST: REMOVE NESTING FROM JSON N1QL SELECT oi.purchase_id, c.customer_name, i.desc FROM sales.order o UNNEST order.customer c UNNEST order.item i NESTING REMOVED JSON BUCKET: sales “o1”: { “order”:{ “purchase_dt”: “2015-01-01 00:00:00” }, “customer”: [ { “Name”: “Adam Smith”, “Address”: “XXX”, “phone_no”: 4087378383 } ], “item”:[ { “desc”: “shirt”, “manufucture_dt”: “2015-01-01 00:00:00” } ] } [ { “purchase_dt”: “2015-01-01 00:00:00” “Name”: “Adam Smith”, “desc”: “shirt” } ] INPUT : (KEY, VALUE)
32. ©2015 Couchbase Inc. USE KEYS: Improving Full Scan Efficiency Example: One value SELECT * FROM customer USE KEYS “c1” Example: Array SELECT * FROM customer USE KEYS [“c1”, “c2”] Using primary keys of particular(s) value for a given bucket Example: One value SELECT * FROM sales.order o WHERE o.cust_id = ( SELECT meta(c).id FROM sales.customer c USE KEYS “c1” ) Example: One value SELECT * FROM sales.order o USE KEYS “o1” INNER JOIN sales.customer c ON KEYS o.cust_id
33. ©2015 Couchbase Inc. FUNCTIONS: Comparing DATETIME Fields andValues Operations on date time fields require date time functions in N1QL SQL SELECT * FROM order WHERE purchase_dt > “1980-01-01 00:00:00” N1QL SELECT * FROM order WHERE MILLIS(purchase_dt) > MILLIS(‘1980-01-01 00:00:00’) SQL SELECT * FROM order WHERE purchase_dt > Now() N1QL SELECT * FROM order WHERE MILLIS(purchase_dt) > NOW_MILLIS()
34. CRUD OPERATIONS
35. ©2015 Couchbase Inc. CRUD OPERATIONS : INSERT/UPSERT SQL INSERT INTO Customer (ID, NAME) VALUES (“c1”, “Adam Smith”) N1QL INSERT INTO CUSTOMER (KEY, VALUE) VALUES (“c1”, {“Name”: “Adam Smith”}) Returning * SQL INSERT INTO Customer (ID, NAME) VALUES (“c1”, “Adam Smith”) on duplicate key update INSERT N1QL UPSERT INTO CUSTOMER (KEY, VALUE) VALUES (“c1”, {“Name”: “Adam Smith”}) Returning * UPSERT not present in MySQLpresent in MySQL supported with format change supported
36. ©2015 Couchbase Inc. CRUD OPERATIONS : UPDATE/DELETE SQL UPDATE Customer SET Name= “Adam Smith” WHERE ID=“ddd” N1QL UPDATE Customer e SET e.FIELD= “Adam Smith” same as sql SQL DELETE FROM CUSTOMER e where e.ID = “c1” N1QL DELETE FROM CUSTOMER e where e.cust_id = “c1” same as sql UPDATE DELETE BACKWARD COMPATIBLE
37. ©2015 Couchbase Inc. CRUD OPERATIONS : UPDATE/DELETE SQL UPDATE Customer SET Name= “Adam Smith” WHERE ID=“ddd” N1QL UPDATE Customer e USE KEYS “c1” SET e.FIELD= “Adam Smith” Returning * different format SQL DELETE FROM CUSTOMER e where e.ID = “c1” N1QL DELETE FROM CUSTOMER e USE KEYS “c1” Returning * different format UPDATE DELETE NEW FEATURE: Returning, USE KEYS
38. Summary
39. ©2015 Couchbase Inc. Summary Data Model: Move from fixed schema to Flexible schema Data Type: Data types are backward compatible, new data types( MISSING, Arrays, Objects) Keys and Indexes: backward compatible with two index services (views/gsi) SQL Statements: JOINs, NEST, UNNEST, Datetime functions, new Operators for new data types (MISSING/Arrays/Objects) CRUD Operations: INSERT/DELETE/UPDATE are backward compatible with new operations UPSERT
40. Demo
41. ©2015 Couchbase Inc. Demo Database used: World Database MySQL Vs Couchbase 4.0 Beta Migration of data Migration tool: http://www.talend.com/ Create Primary Indexes Run queries to use primary indexes Explain on queries
42. Thank you.