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
   

   View Slide

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
   

   View Slide

3. @hellosct1 – Capitole du libre 2022
   Aujourd’hui
   ●
   Vous avez dit JSON ?
   ●
   JSON dans une database
   ●
   Compatibilité JSON
   ●
   Avec du code !!!
   

   View Slide

4. @hellosct1 – Capitole du libre 2022
   ●
   Vous avez dit JSON ?
   ●
   JSON dans une database
   ●
   Compatibilité JSON
   ●
   Avec du code !!!
   

   View Slide

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()" }
   ]
   }
   }
   }
   

   View Slide

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
   

   View Slide

7. @hellosct1 – Capitole du libre 2022
   Quel différence ?
   ●
   Format décrit en
   externe
   ●
   Format auto-décrit
   Relationnel JSON
   Semi structuré
   Structuré
   

   View Slide

8. @hellosct1 – Capitole du libre 2022
   Représentation
   ●
   Données structurées et semi-structurées
   

   View Slide

9. @hellosct1 – Capitole du libre 2022
   ●
   Vous avez dit JSON ?
   ●
   JSON dans une database
   ●
   Compatibilité JSON
   ●
   Avec du code !!!
   

   View Slide

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]
    ],
    ...)
    

    View Slide

11. @hellosct1 – Capitole du libre 2022
    Colonnes dynamiques (2/4)
    

    View Slide

12. @hellosct1 – Capitole du libre 2022
    Colonnes dynamiques (3/4)
    >SELECT nom, column_list (nosql) FROM newsql;
    

    View Slide

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;
    

    View Slide

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/
    

    View Slide

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/
    

    View Slide

16. @hellosct1 – Capitole du libre 2022
    Json-data-type (2/5)
    ●
    Mysql -uroot -p
    ●
    Create database cdl2022;
    ●
    Use cdl2022;
    

    View Slide

17. @hellosct1 – Capitole du libre 2022
    Json-data-type (3/5)
    ●
    Create table extable (j JSON);
    ●
    Desc extable;
    

    View Slide

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/
    

    View Slide

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;
    

    View Slide

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 ;
    

    View Slide

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 ;
    

    View Slide

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/
    

    View Slide

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/
    

    View Slide

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;
    

    View Slide

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';
    

    View Slide

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/
    

    View Slide

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/
    

    View Slide

28. @hellosct1 – Capitole du libre 2022
    https://mariadb.com/kb/en/json_table/
    

    View Slide

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;
    

    View Slide

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/
    

    View Slide

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"}]');
    

    View Slide

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;
    

    View Slide

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"
    ]
    }
    ]
    

    View Slide

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;
    

    View Slide

35. @hellosct1 – Capitole du libre 2022
    ●
    Vous avez dit JSON ?
    ●
    JSON dans une database
    ●
    Compatibilité JSON
    ●
    Avec du code !!!
    

    View Slide

36. @hellosct1 – Capitole du libre 2022
    

    View Slide

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/
    

    View Slide

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
    

    View Slide

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;
    

    View Slide

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
    },
    

    View Slide

41. @hellosct1 – Capitole du libre 2022
    CONNECT JSON Table Type (2/2)
    > select isbn, author, title, publisher from jsample;
    

    View Slide

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';
    

    View Slide

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é.
    

    View Slide

44. @hellosct1 – Capitole du libre 2022
    MariaDB ColumnStore (2/3)
    ●
    Orienté en colonne
    – Chaque colonne est stockée dans un fichier séparé
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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 |
    +-------+------------------+
    

    View Slide

49. @hellosct1 – Capitole du libre 2022
    ●
    Vous avez dit JSON ?
    ●
    JSON dans une database
    ●
    Compatibilité JSON
    ●
    Avec du code !!!
    

    View Slide

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"
    }
    }
    

    View Slide

51. @hellosct1 – Capitole du libre 2022
    Javascript (2/
    ●
    Résultat SQL
    select * from demo2;
    ●
    

    View Slide

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}`));
    

    View Slide

53. @hellosct1 – Capitole du libre 2022
    Javascript (4/
    $ node index.js
    ●
    Navigateur
    http://localhost:5000/demo2
    

    View Slide

54. @hellosct1 – Capitole du libre 2022
    Autre langages
    ●
    PHP
    ●
    Python
    ●
    Java
    ●
    C++
    ●
    Go
    ●
    Rust
    

    View Slide

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
    

    View Slide

56. @hellosct1 – Capitole du libre 2022
    Merci
    Christophe Villeneuve
    @hellosct1
    @hellosct1@mamot.fr
    

    View Slide