AnalyticDB:Importer des données OSS à l'aide de tables externes

Enterprise, basic, and data lakehouse editions

1. Accédez à l'éditeur SQL Development.

   1. Connectez-vous à la console AnalyticDB for MySQL. Dans le coin supérieur gauche de la console, sélectionnez une région. Dans le volet de navigation de gauche, cliquez sur Clusters. Localisez le cluster à gérer et cliquez sur son ID.

   2. Dans le volet de navigation de gauche, accédez à Job Development > SQL Development.

2. Importez les données.

   Vous pouvez importer des données soit via l'importation standard (par défaut), soit via l'importation élastique. En mode d'importation standard, le système lit les données source depuis les nœuds de calcul et crée des index sur les nœuds de stockage, consommant ainsi des ressources de calcul et de stockage. La méthode d'importation élastique est prise en charge uniquement pour les clusters Enterprise Edition, Basic Edition, and Data Lakehouse Edition exécutant la version du noyau 3.1.10.0 ou ultérieure et disposant d'un groupe de ressources de type job. Pour plus d'informations, consultez la rubrique Méthodes d'importation des données.

   Importation standard

   1. Créez une base de données externe.

      CREATE EXTERNAL DATABASE adb_external_db;

   2. Créez une table externe. Utilisez l'instruction CREATE EXTERNAL TABLE pour créer une table externe OSS dans la base de données externe adb_external_db. Cet exemple utilise adb_external_db.person.

      Remarque

      La table externe AnalyticDB for MySQL doit avoir les mêmes noms de champs, nombre de champs, ordre des champs et types de données que le fichier source OSS.

      Créer une table externe OSS non partitionnée

      CREATE EXTERNAL TABLE adb_external_db.person
      (
       id INT,
       name VARCHAR(1023),
       age INT,
       dt VARCHAR(1023)
      )
      ROW FORMAT DELIMITED FIELDS TERMINATED BY  ','
      STORED AS TEXTFILE
      LOCATION  'oss://testBucketName/adb/dt=2023-06-15/';

      Créer une table externe OSS partitionnée

      Avant d'interroger une table externe OSS partitionnée, vous devez la créer et ajouter les partitions nécessaires.

      1. Créez une table externe OSS partitionnée.

         CREATE EXTERNAL TABLE adb_external_db.person
         (
          id INT,
          name VARCHAR(1023) ,
          age INT
         )
         PARTITIONED BY (dt STRING)
         ROW FORMAT DELIMITED FIELDS TERMINATED BY  ','
         STORED AS TEXTFILE
         LOCATION  'oss://testBucketName/adb/';

      2. Vous pouvez utiliser l'instruction ALTER TABLE ADD PARTITION pour ajouter manuellement une partition, ou l'instruction MSCK REPAIR TABLE pour rechercher et ajouter automatiquement les partitions.

         ALTER TABLE adb_external_db.person ADD PARTITION (dt='2023-06-15') LOCATION 'oss://testBucketName/adb/dt=2023-06-15/';

         Remarque

         • Pour ajouter ou supprimer des partitions d'une table externe OSS, consultez la rubrique ALTER TABLE (tables externes).

         • Pour synchroniser automatiquement les partitions d'une table externe OSS, consultez la rubrique MSCK REPAIR TABLE.

      Pour plus d'informations sur la syntaxe de création des tables externes OSS, consultez la rubrique CREATE EXTERNAL TABLE.

   3. Interrogez les données.

      Après avoir créé la table externe, vous pouvez exécuter une instruction SELECT dans AnalyticDB for MySQL pour interroger les données depuis OSS.

      SELECT * FROM adb_external_db.person;

      Le résultat suivant est renvoyé :

      +------+-------+------+-----------+
      | id   | name  | age  | dt        |
      +------+-------+------+-----------+
      |    1 | james |   10 |2023-06-15 |
      |    2 | bond  |   20 |2023-06-15 |
      |    3 | jack  |   30 |2023-06-15 |
      |    4 | lucy  |   40 |2023-06-15 |
      +------+-------+------+-----------+

   4. Créez une base de données dans AnalyticDB for MySQL. Si une base de données existe déjà, ignorez cette étape. Voici un exemple d'instruction :

      CREATE DATABASE adb_demo; 

   5. Créez une table dans AnalyticDB for MySQL pour stocker les données OSS importées. Voici un exemple d'instruction :

      Remarque

      La table interne doit correspondre à la table externe de l'étape b en termes de noms de champs, de nombre de champs, d'ordre des champs et de types de données.

      CREATE TABLE IF NOT EXISTS adb_demo.adb_import_test(
          id INT,
          name VARCHAR(1023),
          age INT,
          dt VARCHAR(1023)
      )
      DISTRIBUTED BY HASH(id);

   6. Importez les données dans la table.

      • Méthode 1 : Utilisez l'instruction INSERT INTO pour importer les données. Si une valeur de clé primaire est dupliquée, les nouvelles données sont ignorées. Cela équivaut à utiliser INSERT IGNORE INTO. Pour plus d'informations, consultez la rubrique INSERT INTO.

        INSERT INTO adb_demo.adb_import_test SELECT * FROM adb_external_db.person;

      • Méthode 2 : Utilisez l'instruction INSERT OVERWRITE INTO pour importer les données de manière synchrone. Cette opération écrase les données existantes dans la table.

        INSERT OVERWRITE INTO adb_demo.adb_import_test SELECT * FROM adb_external_db.person;

      • Méthode 3 : Utilisez l'instruction INSERT OVERWRITE INTO pour importer les données de manière asynchrone. Pour plus d'informations, consultez la rubrique Écriture asynchrone.

        SUBMIT JOB INSERT OVERWRITE adb_demo.adb_import_test SELECT * FROM adb_external_db.person;

   Importation élastique

   1. Créez une base de données. Si une base de données existe déjà, ignorez cette étape. Voici un exemple d'instruction :

      CREATE DATABASE adb_demo; 

   2. Créez une table externe.

      Remarque

      • La table externe AnalyticDB for MySQL doit avoir les mêmes noms de champs, nombre de champs, ordre des champs et types de données que le fichier source OSS.

      • L'importation élastique prend uniquement en charge la création de tables externes avec l'instruction CREATE TABLE.

      CREATE TABLE oss_import_test_external_table
      (
        id INT(1023),
        name VARCHAR(1023),
        age INT,
        dt VARCHAR(1023)
      )
      ENGINE='OSS'
      TABLE_PROPERTIES='{
          "endpoint":"oss-cn-hangzhou-internal.aliyuncs.com",
          "url":"oss://testBucketName/adb/dt=2023-06-15/person.csv",
          "accessid":"accesskey_id",
          "accesskey":"accesskey_secret",
          "delimiter":","
      }';

      Important

      Lors de la création d'une table externe, les paramètres TABLE_PROPERTIES pris en charge varient selon le format de fichier (CSV, Parquet ou ORC) :

      • Format CSV : Seuls les paramètres endpoint, url, accessid, accesskey, format, delimiter, null_value, maxlinelength et partition_column sont pris en charge.

      • Format Parquet : Seuls les paramètres endpoint, url, accessid, accesskey, format, maxlinelength et partition_column sont pris en charge.

      • Format ORC : Seuls les paramètres endpoint, url, accessid, accesskey, format, maxlinelength et partition_column sont pris en charge.

      Pour plus d'informations sur les paramètres configurables pour les tables externes et leurs descriptions, consultez les rubriques Tables externes OSS non partitionnées et Tables externes OSS partitionnées.

   3. Interrogez les données.

      Après la création de la table externe, vous pouvez exécuter une instruction SELECT dans AnalyticDB for MySQL pour interroger les données depuis OSS.

      SELECT * FROM oss_import_test_external_table;

      Le résultat suivant est renvoyé :

      +------+-------+------+-----------+
      | id   | name  | age  | dt        |
      +------+-------+------+-----------+
      |    1 | james |   10 |2023-06-15 |
      |    2 | bond  |   20 |2023-06-15 |
      |    3 | jack  |   30 |2023-06-15 |
      |    4 | lucy  |   40 |2023-06-15 |
      +------+-------+------+-----------+
      4 rows in set (0.35 sec)

   4. Créez une table dans AnalyticDB for MySQL pour stocker les données OSS importées. Voici un exemple d'instruction :

      Remarque

      La table interne doit correspondre à la table externe de l'étape b en termes de noms de champs, de nombre de champs, d'ordre des champs et de types de données.

      CREATE TABLE adb_import_test
      (
        id INT,
        name VARCHAR(1023),
        age INT,
        dt VARCHAR(1023)
      )
      DISTRIBUTED BY HASH(id);

   5. Importez les données.

      Important

      L'importation élastique prend uniquement en charge l'importation de données via l'instruction INSERT OVERWRITE INTO.

      • Méthode 1 : Exécutez l'instruction INSERT OVERWRITE INTO pour importer les données de manière élastique, en écrasant les données existantes dans la table. Voici un exemple d'instruction :

        /*+elastic_load=true, elastic_load_configs=[adb.load.resource.group.name=resource_group]*/
        INSERT OVERWRITE INTO adb_demo.adb_import_test SELECT * FROM adb_demo.oss_import_test_external_table;

      • Méthode 2 : Exécutez de manière asynchrone l'instruction INSERT OVERWRITE INTO pour importer les données de manière élastique. Vous pouvez utiliser l'instruction SUBMIT JOB pour soumettre une tâche asynchrone planifiée en arrière-plan.

        /*+elastic_load=true, elastic_load_configs=[adb.load.resource.group.name=resource_group]*/
        SUBMIT JOB INSERT OVERWRITE INTO adb_demo.adb_import_test SELECT * FROM adb_demo.oss_import_test_external_table;

        Important

        Vous ne pouvez pas définir de file d'attente de priorité lors de la soumission asynchrone d'une tâche d'importation élastique.

        Le résultat suivant est renvoyé :

        +---------------------------------------+
        | job_id                                |
        +---------------------------------------+
        | 202308151719510210170190**********    |

      Après avoir soumis une tâche asynchrone via SUBMIT JOB, le résultat renvoyé indique uniquement que la tâche a été soumise avec succès. Vous pouvez utiliser l'ID de tâche pour arrêter la tâche asynchrone ou interroger son statut afin de déterminer si elle a été exécutée correctement. Pour plus d'informations, consultez la rubrique Soumettre une tâche d'importation asynchrone.

      Paramètres Hint :

      • elastic_load : spécifie s'il faut utiliser l'importation élastique. Valeurs possibles : true et false. Valeur par défaut : false.

      • elastic_load_configs : paramètres de configuration de la fonctionnalité d'importation élastique. Vous devez placer les paramètres entre crochets ([ ]) et séparer plusieurs paramètres par des barres verticales (|). Le tableau suivant décrit les paramètres.

        Paramètre	Obligatoire	Description
        adb.load.resource.group.name	Oui	Nom du groupe de ressources de tâches qui exécute la tâche d'importation élastique.
        adb.load.job.max.acu	Non	Quantité maximale de ressources pour une tâche d'importation élastique. Unité : unités de calcul AnalyticDB (ACU). Valeur minimale : 5 ACU. Valeur par défaut : nombre de shards plus 1. Exécutez l'instruction suivante pour interroger le nombre de shards dans le cluster : SELECT count(1) FROM information_schema.kepler_meta_shards;
        spark.driver.resourceSpec	Non	Type de ressource du pilote Spark. Valeur par défaut : small. Pour connaître les valeurs valides, consultez la colonne Type du tableau « Paramètres de configuration des applications Spark » de la rubrique relative aux paramètres de configuration Conf.
        spark.executor.resourceSpec	Non	Type de ressource de l'exécuteur Spark. Valeur par défaut : large. Pour connaître les valeurs valides, consultez la colonne Type du tableau « Paramètres de configuration des applications Spark » de la rubrique relative aux paramètres de configuration Conf.
        spark.adb.executorDiskSize	Non	Capacité du disque de l'exécuteur Spark. Valeurs valides : (0 100]. Unité : GiB. Valeur par défaut : 10 GiB. Pour plus d'informations, consultez la section « Spécifier les ressources du pilote et de l'exécuteur » de la rubrique relative aux paramètres de configuration Conf.

   6. (Facultatif) Vérifiez si la tâche d'importation soumise est une tâche d'importation élastique.

      SELECT job_name, (job_type = 3) AS is_elastic_load FROM INFORMATION_SCHEMA.kepler_meta_async_jobs where job_name = "2023081818010602101701907303151******";

      Le résultat suivant est renvoyé :

      +---------------------------------------+------------------+
      | job_name                              | is_elastic_load  |
      +---------------------------------------+------------------+
      | 20230815171951021017019072*********** |       1          |
      +---------------------------------------+------------------+

      Si la valeur de is_elastic_load est 1, la tâche d'importation soumise est une tâche d'importation élastique. Si la valeur est 0, il s'agit d'une tâche d'importation standard.

Data warehouse edition

1. Se connecter à un cluster et créer une base de données.

   CREATE DATABASE adb_demo;

2. Créez une table externe. Utilisez la syntaxe CREATE TABLE pour créer une table externe OSS au format CSV, Parquet ou ORC. Pour plus d'informations sur la syntaxe, consultez la rubrique Syntaxe des tables externes OSS.

   Cette rubrique utilise une table externe non partitionnée au format CSV comme exemple.

   CREATE TABLE IF NOT EXISTS oss_import_test_external_table
   (
       id INT,
       name VARCHAR(1023),
       age INT,
       dt VARCHAR(1023) 
   )
   ENGINE='OSS'
   TABLE_PROPERTIES='{
       "endpoint":"oss-cn-hangzhou-internal.aliyuncs.com",
       "url":"oss://testBucketname/adb/dt=2023-06-15/person.csv",
       "accessid":"accesskey_id",
       "accesskey":"accesskey_secret",
       "delimiter":",",
       "skip_header_line_count":0,
       "charset":"utf-8"
   }'; 

3. Interrogez les données de la table externe oss_import_test_external_table.

   Remarque

   Pour les fichiers de données CSV, Parquet ou ORC, l'interrogation d'une grande table externe peut entraîner une surcharge importante des performances. Pour améliorer l'efficacité des requêtes, nous vous recommandons d'importer les données de la table externe OSS vers AnalyticDB for MySQL avant d'exécuter les requêtes, comme décrit aux étapes 4 et 5.

   SELECT * FROM oss_import_test_external_table;

4. Créez une table dans AnalyticDB for MySQL pour stocker les données importées depuis la table externe OSS.

   CREATE TABLE IF NOT EXISTS adb_oss_import_test
   (
      id INT,
      name VARCHAR(1023),
      age INT,
      dt VARCHAR(1023) 
   )
   DISTRIBUTED BY HASH(id);

5. Exécutez une instruction INSERT pour importer les données de la table externe OSS vers AnalyticDB for MySQL.

   Important

   Par défaut, les opérations d'importation utilisant INSERT INTO ou INSERT OVERWRITE SELECT s'exécutent de manière synchrone. Pour les grands ensembles de données, tels que ceux de plusieurs centaines de gigaoctets, le client doit maintenir une connexion persistante au serveur AnalyticDB for MySQL pendant une longue période. Durant ce temps, des problèmes réseau peuvent interrompre la connexion et provoquer l'échec de l'importation. Par conséquent, pour les volumes de données importants, nous vous recommandons d'utiliser SUBMIT JOB INSERT OVERWRITE SELECT pour effectuer l'importation de manière asynchrone.

   • Méthode 1 : Exécutez l'instruction INSERT INTO pour importer les données. Si une clé primaire est dupliquée, l'opération d'écriture actuelle est ignorée et les données ne sont pas mises à jour. Ce comportement équivaut à INSERT IGNORE INTO. Pour plus d'informations, consultez la rubrique INSERT INTO. Voici un exemple d'instruction :

     INSERT INTO adb_oss_import_test
     SELECT * FROM oss_import_test_external_table;

   • Méthode 2 : Exécutez l'instruction INSERT OVERWRITE pour importer les données, en écrasant les données existantes dans la table. Voici un exemple d'instruction :

     INSERT OVERWRITE adb_oss_import_test
     SELECT * FROM oss_import_test_external_table;

   • Méthode 3 : Exécutez de manière asynchrone l'instruction INSERT OVERWRITE pour importer les données. Vous pouvez utiliser SUBMIT JOB pour soumettre une tâche asynchrone pour une planification en arrière-plan. Vous pouvez ajouter un hint (/*+ direct_batch_load=true*/) avant la tâche d'écriture pour accélérer celle-ci. Pour plus d'informations, consultez la rubrique Écriture asynchrone. Voici un exemple d'instruction :

     SUBMIT JOB INSERT OVERWRITE adb_oss_import_test
     SELECT * FROM oss_import_test_external_table;

     Le résultat suivant est renvoyé :

     +---------------------------------------+
     | job_id                                |
     +---------------------------------------+
     | 2020112122202917203100908203303****** |

     Pour plus d'informations sur la soumission d'une tâche asynchrone, consultez Soumettre une tâche d'importation asynchrone.

Enterprise, Basic, and Data Lakehouse

Pour obtenir des informations sur la syntaxe de création de tables externes OSS dans les éditions Enterprise Edition, Basic Edition et Data Lakehouse Edition, consultez Tables externes OSS.

Data Warehouse

CREATE TABLE [IF NOT EXISTS] table_name
(column_name column_type[, …])
ENGINE='OSS'
TABLE_PROPERTIES='{
    "endpoint":"endpoint",
    "url":"OSS_LOCATION",
    "accessid":"accesskey_id",
    "accesskey":"accesskey_secret",
    "format":"csv|orc|parquet|text
    "delimiter|field_delimiter":";",
    "skip_header_line_count":1,
    "charset":"utf-8"
}';

CREATE TABLE adb_csv_hive_format_oss (
  a tinyint,
  b smallint,
  c int,
  d bigint,
  e boolean,
  f float,
  g double,
  h varchar,
  i varchar, -- binary
  j timestamp,
  k DECIMAL(10, 4),
  l varchar, -- char(10)
  m varchar, -- varchar(100)
  n date
) ENGINE = 'OSS' TABLE_PROPERTIES='{
    "format": "csv",
    "endpoint":"oss-cn-hangzhou-internal.aliyuncs.com",
    "accessid":"accesskey_id",
    "accesskey":"accesskey_secret",
    "url":"oss://testBucketname/adb_data/",
    "delimiter": "\\1",
    "null_value": "\\\\N",
    "oss_ignore_quote_and_escape": "true",
    "ossnull": 2
}';

Mapping des types de données entre les fichiers Parquet et **AnalyticDB for MySQL**

Mapping des types de données des fichiers ORC vers **AnalyticDB for MySQL**

Mapping des types de données entre les fichiers Paimon et **AnalyticDB for MySQL**