Jeudis du Libre - MySQL comme Document Store

  • Published on
    22-Mar-2017

  • View
    185

  • Download
    0

Transcript

1 / 902 / 90Safe Harbor StatementThe following is intended to outline our general product direction. It is intended forinformation purpose only, and may not be incorporated into any contract. It is not acommitment to deliver any material, code, or functionality, and should not be relied up inmaking purchasing decisions. The development, release and timing of any features orfunctionality described for Oracle's product remains at the sole discretion of Oracle.Copyright @ 2017 Oracle and/or its affiliates. All rights reserved.3 / 90about.me/lefredQui suis-je ?Copyright @ 2017 Oracle and/or its affiliates. All rights reserved.4 / 90Frdric Descamps@lefredvangliste MySQLBidouille MySQL depuis la version 3.23devops croyant et praticantMySQL Community Manager depuis mai2016Copyright @ 2017 Oracle and/or its affiliates. All rights reserved.5 / 90SGDB & MySQLBase de donnes RelationnellesCopyright @ 2017 Oracle and/or its affiliates. All rights reserved.6 / 90Bases de donnes RelationnellesIntgrit des donnesCopyright @ 2017 Oracle and/or its affiliates. All rights reserved.7 / 90Bases de donnes RelationnellesIntgrit des donnesnormalizationCopyright @ 2017 Oracle and/or its affiliates. All rights reserved.8 / 90Bases de donnes RelationnellesIntgrit des donnesnormalizationcontraintes (cls trangres, ...)Copyright @ 2017 Oracle and/or its affiliates. All rights reserved.9 / 90Bases de donnes RelationnellesIntgrit des donnesnormalizationcontraintes (cls trangres, ...)Atomicit, Cohrence, Isolarion et Durabilit - ACIDCopyright @ 2017 Oracle and/or its affiliates. All rights reserved.10 / 90Bases de donnes RelationnellesIntgrit des donnesnormalizationcontraintes (cls trangres, ...)Atomicit, Cohrence, Isolarion et Durabilit - ACIDtransactionsCopyright @ 2017 Oracle and/or its affiliates. All rights reserved.11 / 90Bases de donnes RelationnellesIntgrit des donnesnormalizationcontraintes (cls trangres, ...)Atomicit, Cohrence, Isolarion et Durabilit - ACIDtransactionsSQLCopyright @ 2017 Oracle and/or its affiliates. All rights reserved.12 / 90Bases de donnes RelationnellesIntgrit des donnesnormalizationcontraintes (cls trangres, ...)Atomicit, Cohrence, Isolarion et Durabilit - ACIDtransactionsSQLpuissant language de requtesCopyright @ 2017 Oracle and/or its affiliates. All rights reserved.13 / 90SGDB & MySQLBase de donnes NoSQLCopyright @ 2017 Oracle and/or its affiliates. All rights reserved.14 / 90NoSQL ou Document StoreSans schmaCopyright @ 2017 Oracle and/or its affiliates. All rights reserved.15 / 90NoSQL ou Document StoreSans schmapas de design de schmas, pas de normalization, de cls trangres, decontraintes, de type de donnes (data types), etc...Copyright @ 2017 Oracle and/or its affiliates. All rights reserved.16 / 90NoSQL ou Document StoreSans schmapas de design de schmas, pas de normalization, de cls trangres, decontraintes, de type de donnes (data types), etc...dvelopement initial trs rapideCopyright @ 2017 Oracle and/or its affiliates. All rights reserved.17 / 90NoSQL ou Document StoreSans schmapas de design de schmas, pas de normalization, de cls trangres, decontraintes, de type de donnes (data types), etc...dvelopement initial trs rapideStructures flexibles des donnesCopyright @ 2017 Oracle and/or its affiliates. All rights reserved.18 / 90NoSQL ou Document StoreSans schmapas de design de schmas, pas de normalization, de cls trangres, decontraintes, de type de donnes (data types), etc...dvelopement initial trs rapideStructures flexibles des donnesobjets et tableaux imbriqusCopyright @ 2017 Oracle and/or its affiliates. All rights reserved.19 / 90NoSQL ou Document StoreSans schmapas de design de schmas, pas de normalization, de cls trangres, decontraintes, de type de donnes (data types), etc...dvelopement initial trs rapideStructures flexibles des donnesobjets et tableaux imbriquscertaines donnes sont simplement naturellement non structures et nepeuvent tre modlises de faon optimale dans un modle relationelCopyright @ 2017 Oracle and/or its affiliates. All rights reserved.20 / 90NoSQL ou Document StoreSans schmapas de design de schmas, pas de normalization, de cls trangres, decontraintes, de type de donnes (data types), etc...dvelopement initial trs rapideStructures flexibles des donnesobjets et tableaux imbriquscertaines donnes sont simplement naturellement non structures et nepeuvent tre modlises de faon optimale dans un modle relationelpersistence des objets sans utilisation d'un ORM (mapping objet-relationnel)Copyright @ 2017 Oracle and/or its affiliates. All rights reserved.21 / 90NoSQL ou Document Store (2)JSONCopyright @ 2017 Oracle and/or its affiliates. All rights reserved.22 / 90NoSQL ou Document Store (2)JSONproche du frontendCopyright @ 2017 Oracle and/or its affiliates. All rights reserved.23 / 90NoSQL ou Document Store (2)JSONproche du frontend"natif " en JavascriptCopyright @ 2017 Oracle and/or its affiliates. All rights reserved.24 / 90NoSQL ou Document Store (2)JSONproche du frontend"natif " en Javascriptutilis en Node.jsCopyright @ 2017 Oracle and/or its affiliates. All rights reserved.25 / 90NoSQL ou Document Store (2)JSONproche du frontend"natif " en Javascriptutilis en Node.jsAprentissage facile, et facile utiliserCopyright @ 2017 Oracle and/or its affiliates. All rights reserved.26 / 90Les challenges actuelsles dveloppeurs veulent avancer plus rapidementCopyright @ 2017 Oracle and/or its affiliates. All rights reserved.27 / 90Les challenges actuelsles dveloppeurs veulent avancer plus rapidementle dlai de mise en production est un cot suprieurCopyright @ 2017 Oracle and/or its affiliates. All rights reserved.28 / 90Les challenges actuelsles dveloppeurs veulent avancer plus rapidementle dlai de mise en production est un cot suprieurrapidit de cration de prototypes, itrations rapides, ...Copyright @ 2017 Oracle and/or its affiliates. All rights reserved.29 / 90Avantages de SQL et de NoSQLNoSQL SQLCopyright @ 2017 Oracle and/or its affiliates. All rights reserved.30 / 90Et donc, Bases de donnes Relationnelles ou Document Store ??Copyright @ 2017 Oracle and/or its affiliates. All rights reserved.31 / 90Et donc, Bases de donnes Relationnelles ou Document Store ??Pourquoi pas les deux ?Copyright @ 2017 Oracle and/or its affiliates. All rights reserved.32 / 90Quelles sont les tapes ?NoSQL avec MySQLCopyright @ 2017 Oracle and/or its affiliates. All rights reserved.33 / 90Faire de MySQL un Document StoreCopyright @ 2017 Oracle and/or its affiliates. All rights reserved.34 / 90Faire de MySQL un Document Store1. support de JSON datatypeCopyright @ 2017 Oracle and/or its affiliates. All rights reserved.35 / 90Faire de MySQL un Document Store1. support de JSON datatype2. CRUD OperationsCopyright @ 2017 Oracle and/or its affiliates. All rights reserved.36 / 90Faire de MySQL un Document Store1. support de JSON datatype2. CRUD Operations3. X PluginCopyright @ 2017 Oracle and/or its affiliates. All rights reserved.37 / 90Faire de MySQL un Document Store1. support de JSON datatype2. CRUD Operations3. X Plugin4. X protocol & MySQL ShellCopyright @ 2017 Oracle and/or its affiliates. All rights reserved.38 / 90MySQL 5.7Support de JSONCopyright @ 2017 Oracle and/or its affiliates. All rights reserved.39 / 90Support JSONType natif de donnes (native datatype)Copyright @ 2017 Oracle and/or its affiliates. All rights reserved.40 / 90Support JSONType natif de donnes (native datatype)Valeurs en JSON stockes dans des tables MySQLCopyright @ 2017 Oracle and/or its affiliates. All rights reserved.41 / 90Support JSONType natif de donnes (native datatype)Valeurs en JSON stockes dans des tables MySQLFormat de stokage JSON en binaire (UTF8MB4)Copyright @ 2017 Oracle and/or its affiliates. All rights reserved.42 / 90Support JSONType natif de donnes (native datatype)Valeurs en JSON stockes dans des tables MySQLFormat de stokage JSON en binaire (UTF8MB4)Conversion partir des types SQL "natifs" vers des valeurs JSONCopyright @ 2017 Oracle and/or its affiliates. All rights reserved.43 / 90Support JSONType natif de donnes (native datatype)Valeurs en JSON stockes dans des tables MySQLFormat de stokage JSON en binaire (UTF8MB4)Conversion partir des types SQL "natifs" vers des valeurs JSONFunctions de manipulation du JSONCopyright @ 2017 Oracle and/or its affiliates. All rights reserved.44 / 90Support JSONType natif de donnes (native datatype)Valeurs en JSON stockes dans des tables MySQLFormat de stokage JSON en binaire (UTF8MB4)Conversion partir des types SQL "natifs" vers des valeurs JSONFunctions de manipulation du JSONextraire (JSON_EXTRACT, JSON_KEYS, ...)Copyright @ 2017 Oracle and/or its affiliates. All rights reserved.45 / 90Support JSONType natif de donnes (native datatype)Valeurs en JSON stockes dans des tables MySQLFormat de stokage JSON en binaire (UTF8MB4)Conversion partir des types SQL "natifs" vers des valeurs JSONFunctions de manipulation du JSONextraire (JSON_EXTRACT, JSON_KEYS, ...)inpecter (JSON_CONTAINS, ...)Copyright @ 2017 Oracle and/or its affiliates. All rights reserved.46 / 90Support JSONType natif de donnes (native datatype)Valeurs en JSON stockes dans des tables MySQLFormat de stokage JSON en binaire (UTF8MB4)Conversion partir des types SQL "natifs" vers des valeurs JSONFunctions de manipulation du JSONextraire (JSON_EXTRACT, JSON_KEYS, ...)inpecter (JSON_CONTAINS, ...)modifier (JSON_SET, JSON_INSERT, JSON_REMOVE, ...)Copyright @ 2017 Oracle and/or its affiliates. All rights reserved.47 / 90Support JSONType natif de donnes (native datatype)Valeurs en JSON stockes dans des tables MySQLFormat de stokage JSON en binaire (UTF8MB4)Conversion partir des types SQL "natifs" vers des valeurs JSONFunctions de manipulation du JSONextraire (JSON_EXTRACT, JSON_KEYS, ...)inpecter (JSON_CONTAINS, ...)modifier (JSON_SET, JSON_INSERT, JSON_REMOVE, ...)cration de tableaux et d'objets (JSON_ARRAY, JSON_OBJECT)Copyright @ 2017 Oracle and/or its affiliates. All rights reserved.48 / 90Support JSONType natif de donnes (native datatype)Valeurs en JSON stockes dans des tables MySQLFormat de stokage JSON en binaire (UTF8MB4)Conversion partir des types SQL "natifs" vers des valeurs JSONFunctions de manipulation du JSONextraire (JSON_EXTRACT, JSON_KEYS, ...)inpecter (JSON_CONTAINS, ...)modifier (JSON_SET, JSON_INSERT, JSON_REMOVE, ...)cration de tableaux et d'objets (JSON_ARRAY, JSON_OBJECT)recherche d'objets (JSON_SEARCH)Copyright @ 2017 Oracle and/or its affiliates. All rights reserved.49 / 90Support JSON (2)Inline SQL JSON path expressions SELECT doc->'$.object.array[0].item' FROM some_tableCopyright @ 2017 Oracle and/or its affiliates. All rights reserved.50 / 90Support JSON (2)Inline SQL JSON path expressions SELECT doc->'$.object.array[0].item' FROM some_tableOprateurs boolens (comparaison de valeurs JSON, ...) foo = doc->'$.eld'Copyright @ 2017 Oracle and/or its affiliates. All rights reserved.51 / 90Support JSON (3)le meilleur pour la fin...Copyright @ 2017 Oracle and/or its affiliates. All rights reserved.52 / 90Support JSON (3)le meilleur pour la fin...Colonnes Gnres/Virtuelles (generated/virtual columns)Copyright @ 2017 Oracle and/or its affiliates. All rights reserved.53 / 90Support JSON (3)le meilleur pour la fin...Colonnes Gnres/Virtuelles (generated/virtual columns)Index de donnes JSONCopyright @ 2017 Oracle and/or its affiliates. All rights reserved.54 / 90Support JSON (3)le meilleur pour la fin...Colonnes Gnres/Virtuelles (generated/virtual columns)Index de donnes JSONCls trangres vers donnes JSONCopyright @ 2017 Oracle and/or its affiliates. All rights reserved.55 / 90Support JSON (3)le meilleur pour la fin...Colonnes Gnres/Virtuelles (generated/virtual columns)Index de donnes JSONCls trangres vers donnes JSONVues SQL pour des donnes JSONCopyright @ 2017 Oracle and/or its affiliates. All rights reserved.56 / 90Support JSON (3)le meilleur pour la fin...Colonnes Gnres/Virtuelles (generated/virtual columns)Index de donnes JSONCls trangres vers donnes JSONVues SQL pour des donnes JSONALTER TABLE test_features ADD COLUMN street VARCHAR(30) GENERATED ALWAYS AS (json_unquote(json_extract(`feature`,'$.properties.STREET'))) VIRTUAL;Copyright @ 2017 Oracle and/or its affiliates. All rights reserved.57 / 90Document StoreOprations CRUDCopyright @ 2017 Oracle and/or its affiliates. All rights reserved.58 / 90Oprations CRUDCreate, Read, Update et Delete (CRUD) sont les 4 oprations qui peuvent tre excutessur une collection (Document Store Schema) ou une table.CREATE - collection.add()Copyright @ 2017 Oracle and/or its affiliates. All rights reserved.59 / 90Oprations CRUD (2)READ - collection.find()Copyright @ 2017 Oracle and/or its affiliates. All rights reserved.60 / 90Oprations CRUD (3)UPDATE - collection.modify()Copyright @ 2017 Oracle and/or its affiliates. All rights reserved.61 / 90Oprations CRUD (4)DELETE - collection.remove()Copyright @ 2017 Oracle and/or its affiliates. All rights reserved.62 / 90Document StoreX PluginCopyright @ 2017 Oracle and/or its affiliates. All rights reserved.63 / 90X PluginPlugin du serveur MySQL qui active la communication en utilisant le X Protocol.Ce pluging:Copyright @ 2017 Oracle and/or its affiliates. All rights reserved.64 / 90X PluginPlugin du serveur MySQL qui active la communication en utilisant le X Protocol.Ce pluging:supporte les clients ayant implment le X DevAPICopyright @ 2017 Oracle and/or its affiliates. All rights reserved.65 / 90X PluginPlugin du serveur MySQL qui active la communication en utilisant le X Protocol.Ce pluging:supporte les clients ayant implment le X DevAPItransforme MySQL en Document Store.Copyright @ 2017 Oracle and/or its affiliates. All rights reserved.66 / 90X PluginPlugin du serveur MySQL qui active la communication en utilisant le X Protocol.Ce pluging:supporte les clients ayant implment le X DevAPItransforme MySQL en Document Store.mysql> INSTALL PLUGIN mysqlx SONAME "mysqlx.so"oumysqlsh -u user --classic --dba enableXProtocolCopyright @ 2017 Oracle and/or its affiliates. All rights reserved.67 / 90Document StoreX ProtocolCopyright @ 2017 Oracle and/or its affiliates. All rights reserved.68 / 90X ProtocolProtocole qui permet de communiquer avec un MySQL Server sur lequel X Plugin est activ.Copyright @ 2017 Oracle and/or its affiliates. All rights reserved.69 / 90X ProtocolProtocole qui permet de communiquer avec un MySQL Server sur lequel X Plugin est activ.Et donc le X Protocol permet deffectuer des oparations CRUD et SQL, lauthentificationvia SASL, le "streaming" (pipelining) des commandes.Copyright @ 2017 Oracle and/or its affiliates. All rights reserved.70 / 90Document StoreThe X DevAPICopyright @ 2017 Oracle and/or its affiliates. All rights reserved.71 / 90Oprations orientes Document via SQLTrs puissantCopyright @ 2017 Oracle and/or its affiliates. All rights reserved.72 / 90Oprations orientes Document via SQLTrs puissantPermet la construction de requtes complexesCopyright @ 2017 Oracle and/or its affiliates. All rights reserved.73 / 90Oprations orientes Document via SQLTrs puissantPermet la construction de requtes complexesMais... encore et toujours difficile utiliser pour les non initisCopyright @ 2017 Oracle and/or its affiliates. All rights reserved.74 / 90Oprations orientes Document via SQLTrs puissantPermet la construction de requtes complexesMais... encore et toujours difficile utiliser pour les non initisCREATE TABLE product ( id VARCHAR(32) GENERATED ALWAYS AS (JSON_EXTRACT(doc, '$.id')) STORED, doc JSON);INSERT INTO product VALUYES (1, '{...}');SELECT * FROM product WHERE JSON_EXTRACT(doc, '$.eld') = value;Copyright @ 2017 Oracle and/or its affiliates. All rights reserved.75 / 90The X DevAPIAbstraction au-dessus de SQLCopyright @ 2017 Oracle and/or its affiliates. All rights reserved.76 / 90The X DevAPIAbstraction au-dessus de SQLAx sur 4 oprations CRUD de base (Create, Read, Update, Delete)Copyright @ 2017 Oracle and/or its affiliates. All rights reserved.77 / 90The X DevAPIAbstraction au-dessus de SQLAx sur 4 oprations CRUD de base (Create, Read, Update, Delete)Native Language APICopyright @ 2017 Oracle and/or its affiliates. All rights reserved.78 / 90The X DevAPIAbstraction au-dessus de SQLAx sur 4 oprations CRUD de base (Create, Read, Update, Delete)Native Language APIPas ncessaire de connatre le SQLCopyright @ 2017 Oracle and/or its affiliates. All rights reserved.79 / 90The X DevAPIAbstraction au-dessus de SQLAx sur 4 oprations CRUD de base (Create, Read, Update, Delete)Native Language APIPas ncessaire de connatre le SQLX ProtocolCopyright @ 2017 Oracle and/or its affiliates. All rights reserved.80 / 90The X DevAPIAbstraction au-dessus de SQLAx sur 4 oprations CRUD de base (Create, Read, Update, Delete)Native Language APIPas ncessaire de connatre le SQLX Protocolles requtes CRUD sont encodes au niveau du protocolCopyright @ 2017 Oracle and/or its affiliates. All rights reserved.81 / 90Exemplemysql-py> mybooks.nd("Title LIKE 'Und%'")[ { "Authors": [ "Alexander Sasha Pachev", "Sasha Pachev" ], "ISBN-13": "9780596009571", "Language": "en", "Publisher": ""O'Reilly Media, Inc."", "Title": "Understanding MySQL Internals", "Year": "2007", "_id": "ce9fd5b762ffe6117055685b359e77d5" }]Copyright @ 2017 Oracle and/or its affiliates. All rights reserved.82 / 90Exemplemysql-py> mybooks.nd("Title LIKE 'Und%'")[ { "Authors": [ "Alexander Sasha Pachev", "Sasha Pachev" ], "ISBN-13": "9780596009571", "Language": "en", "Publisher": ""O'Reilly Media, Inc."", "Title": "Understanding MySQL Internals", "Year": "2007", "_id": "ce9fd5b762ffe6117055685b359e77d5" }]Et MySQL le transforme de manire transparente en:SELECT doc FROM `books`.`mybooks` WHERE (JSON_UNQUOTE(JSON_EXTRACT(doc,'$.Title')) LIKE 'Und%');Copyright @ 2017 Oracle and/or its affiliates. All rights reserved.83 / 90Document Store & X DevAPI & MySQL Shellpetite dmo ?Copyright @ 2017 Oracle and/or its affiliates. All rights reserved.84 / 90Dmo (1)[fred@imac2 ~] $ mysqlsh --py Welcome to MySQL Shell 1.0.8-rcCopyright (c) 2016, 2017, Oracle and/or its afliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsafliates. Other names may be trademarks of their respectiveowners.Type '\help', '\h' or '\?' for help, type '\quit' or '\q' to exit.Currently in Python mode. Use \sql to switch to SQL mode and execute queries.mysql-py> \c root@localhostCreating a Session to 'root@localhost'Enter password: Node Session successfully established. No default schema selected.Copyright @ 2017 Oracle and/or its affiliates. All rights reserved.85 / 90Dmo (2)mysql-py> db=session.create_schema("books")mysql-py> mybooks=db.create_collection('mybooks')mysql-py> from isbntools.app import *mysql-py> isbn=9781449314286mysql-py> doc=meta(str(isbn))mysql-py> doc["Annee"]=doc.pop("Year")mysql-py> mybooks.add(doc)Query OK, 1 item affected (0.10 sec)mysql-py> isbn=9780596009571mysql-py> doc=meta(str(isbn))mysql-py> doc["Annee"]=doc.pop("Year")mysql-py> mybooks.add(doc)mysql-py> isbn=9781430230571mysql-py> doc=meta(str(isbn))mysql-py> doc["Annee"]=doc.pop("Year")mysql-py> mybooks.add(doc)Copyright @ 2017 Oracle and/or its affiliates. All rights reserved.86 / 90Dmo (3)mysql-py> mybooks.nd()[ { "Authors": [ "Baron Schwartz", "Peter Zaitsev", "Vadim Tkachenko" ], "ISBN-13": "9781449314286", "Language": "en", "Publisher": ""O'Reilly Media, Inc."", "Title": "High Performance MySQL", "Annee": "2012", "_id": "66f01d3762ffe6117055685b359e77d5" }, ...]3 documents in set (0.00 sec)Copyright @ 2017 Oracle and/or its affiliates. All rights reserved.87 / 90Dmo (4)mysql-py> mybooks.nd("Title LIKE '%MySQL%'").elds(["Title","Annee"]).sort("Annee")[ { "Annee": "2007", "Title": "Understanding MySQL Internals" }, { "Annee": "2012", "Title": "High Performance MySQL" }]mysql-py> mybooks.create_index("Annee_idx").eld("Annee","TEXT(4)", False).execute();Copyright @ 2017 Oracle and/or its affiliates. All rights reserved.88 / 90Dmo (5)mysql> show create table books.mybooks\G *************************** 1. row *************************** Table: mybooksCreate Table: CREATE TABLE `mybooks` ( `doc` json DEFAULT NULL, `_id` varchar(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,'$._id'))) STORED NOT NULL, `$ix_t4_589943C550621CA4FF9A742745C76C955B6DDE8E` text GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,'$.Annee'))) VIRTUAL, PRIMARY KEY (`_id`), KEY `Annee_idx` (`$ix_t4_589943C550621CA4FF9A742745C76C955B6DDE8E`(4))) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4Copyright @ 2017 Oracle and/or its affiliates. All rights reserved.89 / 90MerciDes Questions ?Copyright @ 2017 Oracle and/or its affiliates. All rights reserved.90 / 90

Recommended

View more >