Mixer le JSON dans une base de données

Présentation effectuée au Capitole du libre par Christophe Villeneuve sur "Mixer le JSON dans une base de données".
Cette présentation a pour but de vous montrer toutes les facettes du JSON dans une base de données comme MariaDB

Transcript

1. Mixer le JSON dans une base de données Christophe Villeneuve @hellosct1 @hellosct1@mamot.fr Capitole du Libre 2022
2. Atos open source - afup – lemug.fr – mariadb – drupal – mozilla - firefox – lemugfr - sumo – webextensions – VR – AR – XR - Cause commune 93.1 FM - TechSpeaker - Lizard - eyrolles – editions eni – programmez – linux pratique – webriver – elephpant - CommonVoice – Sécurité - Cybersécurité Christophe Villeneuve • Consultant Open Source • Dresseur animaux
3. @hellosct1 – Capitole du libre 2022 Aujourd’hui • Vous avez dit JSON ? • JSON dans une database • Compatibilité JSON • Avec du code !!!
4. @hellosct1 – Capitole du libre 2022 • Vous avez dit JSON ? • JSON dans une database • Compatibilité JSON • Avec du code !!!
5. @hellosct1 – Capitole du libre 2022 JSON • = JavaScript Object Notation • Format d'échange de données → API REST • Structure JSON { Clé : Valeur } • Exemple { "menu": { "id": "file", "value": "File", "popup": { "menuitem": [ { "value": "New", "onclick": "CreateNewDoc()" }, { "value": "Open", "onclick": "OpenDoc()" }, { "value": "Close", "onclick": "CloseDoc()" } ] } } }
6. @hellosct1 – Capitole du libre 2022 Modèle Utilisation de modèles de données hybrides avec MariaDB Relationnel Intégrité des données Transactions Fiabilité JSON Flexibilité Simplicité Omniprésence
7. @hellosct1 – Capitole du libre 2022 Quel différence ? • Format décrit en externe • Format auto-décrit Relationnel JSON Semi structuré Structuré
8. @hellosct1 – Capitole du libre 2022 Représentation • Données structurées et semi-structurées
9. @hellosct1 – Capitole du libre 2022 • Vous avez dit JSON ? • JSON dans une database • Compatibilité JSON • Avec du code !!!
10. @hellosct1 – Capitole du libre 2022 Colonnes dynamiques (1/4) create table newsql( id int auto_increment primary key, nom varchar(40), type enum ('animal', 'ordinateur'), prix int, nosql blob); COLUMN_CREATE( column_nr, value [as type], [ column_nr, value [as type] ], ...)
11. @hellosct1 – Capitole du libre 2022 Colonnes dynamiques (2/4)
12. @hellosct1 – Capitole du libre 2022 Colonnes dynamiques (3/4) >SELECT nom, column_list (nosql) FROM newsql;
13. @hellosct1 – Capitole du libre 2022 Colonnes dynamiques (4/4) >SELECT nom, COLUMN_JSON(nosql) FROM newsql; >SELECT id,type,nom, COLUMN_GET(nosql, 'couleur' as char) AS couleur,prix FROM newsql;
14. @hellosct1 – Capitole du libre 2022 Manipulation de JSON • En fonctions • Gestion en tableau – Array • Insert • Explose • Value • Structure • ... • En tables • Dispo 10.6 + • Utilisation – Dans des contextes – Référence à une table – Compatible • Select • Insert • Update • Delete • Sur le principe de JSON_DOC En fonctions En tables https://mariadb.com/kb/en/json-functions/ https://mariadb.com/kb/en/json_table/
15. @hellosct1 – Capitole du libre 2022 Json-data-type (1/5) • Disponible à partir de 10.2.7 • But – Pour rendre possible l'utilisation de colonnes JSON • dans la réplication basée sur les déclarations – de MySQL vers MariaDB – pour permettre à MariaDB de lire les mysqldumps de MySQL • JSON est un alias LONGTEXT • Benchmarks de MariaDB → performances sont au moins équivalentes • fonction JSON_VALID – Utilisée comme une contrainte CHECK https://mariadb.com/kb/en/json-data-type/
16. @hellosct1 – Capitole du libre 2022 Json-data-type (2/5) • Mysql -uroot -p • Create database cdl2022; • Use cdl2022;
17. @hellosct1 – Capitole du libre 2022 Json-data-type (3/5) • Create table extable (j JSON); • Desc extable;
18. @hellosct1 – Capitole du libre 2022 Json-data-type (4/5) • CREATE TABLE table2 ( j JSON CHECK (JSON_VALID(j)) ); https://mariadb.com/kb/en/json_valid/
19. @hellosct1 – Capitole du libre 2022 Json-data-type (5/5) • INSERT INTO table2 VALUES ('message test'); • INSERT INTO table2 VALUES ('{"id": 1, "name": "message succes"}'); • select * from table2;
20. @hellosct1 – Capitole du libre 2022 JSON_VALID create table demo2( id int auto_increment primary key, nom varchar(40), type enum ('animal', 'ordinateur'), prix int, attr JSON, CHECK (JSON_VALID(attr)) ); https://mariadb.com/kb/en/json_valid/ • Desc demo2 ;
21. @hellosct1 – Capitole du libre 2022 > INSERT INTO demo2 VALUES(NULL, 'Atari','ordinateur',500, '{"couleur": "gris", "type": "STe"}'); > INSERT INTO demo2 VALUES(NULL, 'elePHPant', 'animal',20, '{"taille": "normal", "description": "peluche"}'); > SELECT * FROM demo2 ;
22. @hellosct1 – Capitole du libre 2022 JSON_MERGE > UPDATE demo2 SET attr = JSON_MERGE(attr,'{"couleur": "bleu"}') where id=2; > SELECT * FROM demo2 ; https://mariadb.com/kb/en/json_merge/
23. @hellosct1 – Capitole du libre 2022 JSON_VALUE (1/2) > SELECT nom, type, prix, JSON_VALUE(attr, '$.couleur') couleur FROM demo2; https://mariadb.com/kb/en/json_value/
24. @hellosct1 – Capitole du libre 2022 JSON_VALUE (2/2) Toutes les colonnes > SELECT nom,type,prix, JSON_VALUE(attr, '$.couleur') couleur, JSON_VALUE(attr, '$.type') type, JSON_VALUE(attr, '$.taille') taille, JSON_VALUE(attr, '$.description') description FROM demo2;
25. @hellosct1 – Capitole du libre 2022 Ajout d’une colonne + Ajout d’attribut > ALTER TABLE demo2 ADD attr_couleur VARCHAR(32) AS (JSON_VALUE(attr, '$.couleur')); > SELECT * FROM demo2 ; > SELECT * FROM demo2 WHERE attr_couleur = 'bleu';
26. @hellosct1 – Capitole du libre 2022 JSON_EXTRACT > UPDATE demo2 SET attr = JSON_MERGE(attr,'{"couleur": "bleu"}') where id=2; > SELECT * FROM demo2 ; https://mariadb.com/kb/en/json_extract/
27. @hellosct1 – Capitole du libre 2022 JSON_OBJECT > UPDATE demo2 SET attr = JSON_OBJECT(attr,'{"couleur": "bleu"}') where id=2; > SELECT * FROM demo2 ; https://mariadb.com/kb/en/json_object/
28. @hellosct1 – Capitole du libre 2022 https://mariadb.com/kb/en/json_table/
29. @hellosct1 – Capitole du libre 2022 Standard : json_table > set @json=' [ {"name":"Laptop", "color":"black", "price":"1000"}, {"name":"Jeans", "color":"blue"} ]'; select * from json_table(@json, '$[*]' columns( name varchar(10) path '$.name', color varchar(10) path '$.color', price decimal(8,2) path '$.price' ) ) as jt;
30. @hellosct1 – Capitole du libre 2022 Avancé : JSON en tables (1/5) > CREATE TABLE people ( id INT NOT NULL, name VARCHAR(50) NOT NULL, pets JSON ); https://mariadb.com/fr/resources/blog/introducing-json-tables/
31. @hellosct1 – Capitole du libre 2022 Avancé : JSON en tables (2/5) > INSERT INTO people (id, name, pets) VALUES ( 1, 'Rob', '[{"type":"dog","name":"Duke"}, {"type":"cat","name":"Fluffy"}]');
32. @hellosct1 – Capitole du libre 2022 Avancé : JSON en tables (3/5) > SELECT id, JSON_VALUE(JSON_QUERY(pets, CONCAT('$[', ind.ind, ']')), '$.type') pet_type, JSON_VALUE(JSON_QUERY(pets, CONCAT('$[', ind.ind, ']')), '$.name') pet_name FROM people CROSS JOIN (SELECT 0 AS ind UNION ALL SELECT 1 AS ind) ind;
33. @hellosct1 – Capitole du libre 2022 Avancé : JSON en tables (4/5) > UPDATE people SET pets = JSON_INSERT(pets, '$[0].favorite_foods', JSON_ARRAY('chicken', 'salmon', 'carrots')); > UPDATE people SET pets = JSON_INSERT(pets, '$[1].favorite_foods', JSON_ARRAY('tuna', 'turkey')); [ { "type": "dog", "name": "Duke", "favorite_foods": [ "chicken", "salmon", "carrots" ] }, { "type": "cat", "name": "Fluffy", "favorite_foods": [ "tuna", "turkey" ] } ]
34. @hellosct1 – Capitole du libre 2022 Avancé : JSON en tables (5/5) SELECT p.id, pt.pet_type, pt.pet_name, pt.favorite_food FROM people p, JSON_TABLE(p.pets, '$[*]' COLUMNS ( pet_type VARCHAR(10) PATH '$.type', pet_name VARCHAR(25) PATH '$.name', NESTED PATH '$.favorite_foods[*]' COLUMNS (favorite_food VARCHAR(25) PATH '$') ) ) pt;
35. @hellosct1 – Capitole du libre 2022 • Vous avez dit JSON ? • JSON dans une database • Compatibilité JSON • Avec du code !!!
36. @hellosct1 – Capitole du libre 2022
37. @hellosct1 – Capitole du libre 2022 Librairie Connect • Installation du storage engine sudo apt-get install mariadb-plugin-connect • Activer le storage INSTALL SONAME 'ha_connect'; • Verification show engines; https://mariadb.com/kb/en/installing-the-connect-storage-engine/
38. @hellosct1 – Capitole du libre 2022 Connect • ETL : Extract Transform Load • Lecture / Ecriture / MAJ – TXT, DBF, INI, XML, JSON – MS Access, MS Excel, TBL (similaire à MERGE) – ODBC, MySQL,SQLite, Oracle, DB2, WMI… • Locale ou distant • Custom possible • Accès en parallèle sur multitables Installation dans My.cnf [mariadb] plugin_load_add = ha_connect
39. @hellosct1 – Capitole du libre 2022 Connect : Exemple CSV • Fichier role.csv • Console MariaDB • Select * from role; Firstname, lastname, role "Christophe","Villeneuve","Admin" "Martin","Dupont","User" > CREATE TABLE role ( firstname varchar(32) NOT NULL, lastname varchar(32) NOT NULL, role varchar(50) NOT NULL ) ENGINE=CONNECT TABLE_TYPE=CSV FILE_NAME ='/var/lib/mysql/role.csv' HEADER=1 SEP_CHAR=',' QUOTED=1;
40. @hellosct1 – Capitole du libre 2022 CONNECT JSON Table Type (1/2) • Fichier biblio3.json • Console MariaDB > create table jsample ( ISBN char(15), LANG char(2), SUBJECT char(32), AUTHOR char(128), TITLE char(32), TRANSLATED char(80), PUBLISHER char(20), DATEPUB int(4)) engine=CONNECT table_type=JSON File_name='biblio3.json'; https://mariadb.com/kb/en/connect-json-table-type/ [ { "ISBN": "9782212090819", "LANG": "fr", "SUBJECT": "applications", "AUTHOR": [ { "FIRSTNAME": "Jean-Christophe", "LASTNAME": "Bernadac" }, { "FIRSTNAME": "François", "LASTNAME": "Knab" } ], "TITLE": "Construire une application XML", "PUBLISHER": { "NAME": "Eyrolles", "PLACE": "Paris" }, "DATEPUB": 1999 },
41. @hellosct1 – Capitole du libre 2022 CONNECT JSON Table Type (2/2) > select isbn, author, title, publisher from jsample;
42. @hellosct1 – Capitole du libre 2022 JSON sans Attribut • Construction • select title, author, publisher, location from jsampall; create table jsampall ( ISBN char(15), Language char(2) jpath='$.LANG', Subject char(32) jpath='$.SUBJECT', Author char(128) jpath='$.AUTHOR[" and "]', Title char(32) jpath='$.TITLE', Translation char(32) jpath='$.TRANSLATOR.PREFIX', Translator char(80) jpath='$.TRANSLATOR', Publisher char(20) jpath='$.PUBLISHER.NAME', Location char(16) jpath='$.PUBLISHER.PLACE', Year int(4) jpath='$.DATEPUB') engine=CONNECT table_type=JSON File_name='biblio3.json';
43. @hellosct1 – Capitole du libre 2022 MariaDB ColumnStore (1/3) • Moteur de stockage orienté en colonnes – Utilise une architecture de données distribuées massivement parallèle – Système de stockage en colonnes – Utile pour le Big Data – Architecture (en parallèle) • Exécution de requêtes distribuée • Chargement de données • Depuis la version 10.6 – Disponible en tant que moteur de stockage pour le serveur MariaDB • Avant cela, il est uniquement disponible en téléchargement séparé.
44. @hellosct1 – Capitole du libre 2022 MariaDB ColumnStore (2/3) • Orienté en colonne – Chaque colonne est stockée dans un fichier séparé
45. @hellosct1 – Capitole du libre 2022 MariaDB ColumnStore (3/3) • Axée sur la rangée – Les Lignes stockées séquentiellement dans un fichier
46. @hellosct1 – Capitole du libre 2022 Connect : NoSQL Document protocol • Le module nosqlprotocol permet d'utiliser un serveur ou un cluster MariaDB – comme backend d'une application utilisant une bibliothèque client MongoDB®. • En interne, tous les documents sont stockés dans une table contenant deux colonnes : – une colonne id pour l'identifiant de l'objet – une colonne doc pour le document lui-même. MariaDB MaxScale App id doc
47. @hellosct1 – Capitole du libre 2022 Ex : mongoDB (1/2) https://mariadb.com/fr/resources/blog/how-to-manage-nosql-data-with-mariadb/ // Import the MongoDB Node driver module const { MongoClient } = require('MongoDB'); // MongoDB connection string const connectionUrl = 'mongodb://mxs:17017'; let db; // Connect to the database and return a MongoClient object const getDatabaseInstance = async (name) => { if (db) { return db; } try { const client = await MongoClient.connect(connectionUrl); db = client.db(name); } catch (err) { throw err; } finally { return db; } } module.exports = getDatabaseInstance; • MongoDB driver
48. @hellosct1 – Capitole du libre 2022 Ex : mongoDB (2/2) https://mariadb.com/fr/resources/blog/how-to-manage-nosql-data-with-mariadb/ • Export MariaDB [(none)]> use todo; MariaDB [todo]> show create table tasks; +-------+--------------------------------------------------------------------+ | Table | Create Table | +-------+--------------------------------------------------------------------+ | tasks | CREATE TABLE `tasks` ( `id` varchar(35) GENERATED ALWAYS AS (json_compact(json_extract(`doc`,'$._id'))) VIRTUAL, `doc` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`doc`)), UNIQUE KEY `id` (`id`), CONSTRAINT `id_not_null` CHECK (`id` is not null) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 | +-------+--------------------------------------------------------------------+ MariaDB [todo]> select json_value(doc, '$.description') description, json_value(doc, '$.completed') completed from tasks; +-------+------------------+ | description | completed | +-------+------------------+ | Task 1 | 0 | | Task 2 | 1 | | Task 3 | 0 | | Task 4 | 1 | +-------+------------------+
49. @hellosct1 – Capitole du libre 2022 • Vous avez dit JSON ? • JSON dans une database • Compatibilité JSON • Avec du code !!!
50. @hellosct1 – Capitole du libre 2022 Javascript (1/ • NPM • cat package.json $ mkdir nodejs-mariadb $ cd nodejs-mariadb $ sudo npm i mariadb $ sudo npm i express { "dependencies": { "express": "^4.18.2", "mariadb": "^3.0.2" } }
51. @hellosct1 – Capitole du libre 2022 Javascript (2/ • Résultat SQL select * from demo2; •
52. @hellosct1 – Capitole du libre 2022 Javascript (3/ • Résultat index.js const mariadb = require('mariadb'); const express = require('express'); const app = express(); const port = 5000; const pool = mariadb.createPool({ host: 'localhost', user:'root', password: 'root', database: "cdl2022" }); module.exports={ getConnection: function(){ return new Promise(function(resolve,reject){ pool.getConnection().then(function(connection){ resolve(connection); }).catch(function(error){ reject(error); }); }); } } app.get('/demo2', async (req, res) => { let conn; try { conn = await pool.getConnection(); var query = "select * from demo2"; var rows = await conn.query(query); // return the results res.send(rows); } catch (err) { throw err; } finally { if (conn) return conn.release(); } }); app.listen(port, () => console.log(`Listening on port ${port}`));
53. @hellosct1 – Capitole du libre 2022 Javascript (4/ $ node index.js • Navigateur http://localhost:5000/demo2
54. @hellosct1 – Capitole du libre 2022 Autre langages • PHP • Python • Java • C++ • Go • Rust
55. @hellosct1 – Capitole du libre 2022 En Résumé • La base de données doit pouvoir répondre aux besoins du web • Préparer l’avenir en NewSQL NoSQL + SQL
56. @hellosct1 – Capitole du libre 2022 Merci Christophe Villeneuve @hellosct1 @hellosct1@mamot.fr