AnalyticDB:Import OSS data using external tables

Enterprise, basic, and data lakehouse editions

1. Navigate to the SQL Development editor.

   1. Log on to the AnalyticDB for MySQL console. In the upper-left corner of the console, select a region. In the left-side navigation pane, click Clusters. Find the cluster that you want to manage and click the cluster ID.

   2. In the left-side navigation pane, choose Job Development > SQL Development.

2. Import data.

   You can import data using either regular import (the default) or elastic import. In regular import mode, the system reads source data from compute nodes and creates indexes on storage nodes, consuming both compute and storage resources. The elastic import method is supported only for Enterprise Edition, Basic Edition, and Data Lakehouse Edition clusters that run kernel version 3.1.10.0 or later and have a job-type resource group. For more information, see Data import methods.

   Regular import

   1. Create an external database.

      CREATE EXTERNAL DATABASE adb_external_db;

   2. Create an external table. Use the CREATE EXTERNAL TABLE statement to create an OSS external table in the adb_external_db external database. This topic uses adb_external_db.person as an example.

      Note

      The AnalyticDB for MySQL external table must have the same field names, number of fields, field order, and data types as the source OSS file.

      Create a non-partitioned OSS external table

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

      Create a partitioned OSS external table

      Before you can query a partitioned OSS external table, you must create it and add the necessary partitions.

      1. Create a partitioned OSS external table.

         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. You can use the ALTER TABLE ADD PARTITION statement to add a partition manually, or use the MSCK REPAIR TABLE statement to automatically find and add partitions.

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

         Note

         • To add partitions to or remove partitions from an OSS external table, see ALTER TABLE (external tables).

         • To automatically synchronize the partitions of an OSS external table, see MSCK REPAIR TABLE.

      For more information about the syntax for creating OSS external tables, see CREATE EXTERNAL TABLE.

   3. Query data.

      After creating the external table, you can run a SELECT statement in AnalyticDB for MySQL to query data from OSS.

      SELECT * FROM adb_external_db.person;

      The following result is returned:

      +------+-------+------+-----------+
      | 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. Create a database in AnalyticDB for MySQL. If a database already exists, you can skip this step. The following is an example statement:

      CREATE DATABASE adb_demo; 

   5. Create a table in AnalyticDB for MySQL to store the imported OSS data. The following is an example statement:

      Note

      The internal table must match the external table from step b in field names, number of fields, field order, and data types.

      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. Import data into the table.

      • Method 1: Use the INSERT INTO statement. If a primary key is duplicated, the new data is ignored. This is equivalent to INSERT IGNORE INTO. For more information, see INSERT INTO. Example:

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

      • Method 2: Use the INSERT OVERWRITE INTO statement to import data synchronously. This overwrites existing data in the table. Example:

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

      • Method 3: Use the INSERT OVERWRITE INTO statement to import data asynchronously. For more information, see asynchronous write. Example:

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

   Elastic import

   1. Create a database. If a database already exists, skip this step. The following is an example statement:

      CREATE DATABASE adb_demo; 

   2. Create an external table.

      Note

      • The AnalyticDB for MySQL external table must have the same field names, number of fields, field order, and data types as the source OSS file.

      • Elastic import only supports creating external tables with the CREATE TABLE statement.

      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

      When you create an external table, the supported TABLE_PROPERTIES parameters vary based on the file format (CSV, Parquet, or ORC):

      • CSV format: Only the endpoint, url, accessid, accesskey, format, delimiter, null_value, and partition_column parameters are supported.

      • Parquet format: Only the endpoint, url, accessid, accesskey, format, and partition_column parameters are supported.

      • ORC format: Only the endpoint, url, accessid, accesskey, format, and partition_column parameters are supported.

      For more information about the parameters that you can set for external tables and their descriptions, see OSS non-partitioned external tables and OSS partitioned external tables.

   3. Query data.

      After the external table is created, you can run a SELECT statement in AnalyticDB for MySQL to query data from OSS.

      SELECT * FROM oss_import_test_external_table;

      The following result is returned:

      +------+-------+------+-----------+
      | 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. Create a table in AnalyticDB for MySQL to store the imported OSS data. The following is an example statement:

      Note

      The internal table must match the external table from step b in field names, number of fields, field order, and data types.

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

   5. Import data.

      Important

      Elastic import supports importing data only by using the INSERT OVERWRITE INTO statement.

      • Method 1: Run the INSERT OVERWRITE INTO statement to elastically import data, overwriting the existing data in the table. The following is an example statement:

        /*+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;

      • Method 2: Asynchronously run the INSERT OVERWRITE INTO statement to elastically import data. You can use the SUBMIT JOB statement to submit an asynchronous task that is scheduled in the background.

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

        You cannot set a priority queue when you asynchronously submit an elastic import task.

        The following result is returned:

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

      After you submit an asynchronous task by using SUBMIT JOB, the returned result indicates only that the task was submitted successfully. You can use the job ID to terminate the asynchronous task or query its status to determine whether the task was successfully executed. For more information, see Submit an asynchronous import job.

      Hint parameters:

      • elastic_load: specifies whether to use elastic import. Valid values: true and false. Default value: false.

      • elastic_load_configs: the configuration parameters of the elastic import feature. You must enclose the parameters within brackets ([ ]) and separate multiple parameters with vertical bars (|). The following table describes the parameters.

        Parameter	Required	Description
        adb.load.resource.group.name	Yes	The name of the job resource group that runs the elastic import job.
        adb.load.job.max.acu	No	The maximum amount of resources for an elastic import job. Unit: AnalyticDB compute units (ACUs). Minimum value: 5 ACUs. Default value: number of shards plus 1. Execute the following statement to query the number of shards in the cluster: SELECT count(1) FROM information_schema.kepler_meta_shards;
        spark.driver.resourceSpec	No	The resource type of the Spark driver. Default value: small. For information about the valid values, see the Type column in the "Spark application configuration parameters" table of the Conf configuration parameters topic.
        spark.executor.resourceSpec	No	The resource type of the Spark executor. Default value: large. For information about the valid values, see the Type column in the "Spark application configuration parameters" table of the Conf configuration parameters topic.
        spark.adb.executorDiskSize	No	The disk capacity of the Spark executor. Valid values: (0,100]. Unit: GiB. Default value: 10 GiB. For more information, see the "Specify driver and executor resources" section of the Conf configuration parameters topic.

   6. (Optional) Check whether the submitted import task is an elastic import task.

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

      The following result is returned:

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

      If the value of is_elastic_load is 1, the submitted import task is an elastic import task. If the value is 0, the task is a regular import task.

Data warehouse edition

1. Connect to a cluster and create a database.

   CREATE DATABASE adb_demo;

2. Create an external table. Use the CREATE TABLE syntax to create an OSS external table in CSV, Parquet, or ORC format. For more information about the syntax, see OSS external table syntax.

   This topic uses a non-partitioned external table in CSV format as an example.

   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. Query data from the oss_import_test_external_table external table.

   Note

   For CSV, Parquet, or ORC data files, querying a large external table can cause significant performance overhead. To improve query efficiency, we recommend importing the data from the OSS external table into AnalyticDB for MySQL before running queries, as described in Steps 4 and 5.

   SELECT * FROM oss_import_test_external_table;

4. Create a table in AnalyticDB for MySQL to store the data imported from the OSS external table.

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

5. Run an INSERT statement to import data from the OSS external table into AnalyticDB for MySQL.

   Important

   By default, data import operations that use INSERT INTO or INSERT OVERWRITE SELECT run synchronously. For large datasets, such as those in the hundreds of gigabytes, the client must maintain a persistent connection to the AnalyticDB for MySQL server for a long time. During this time, network issues may interrupt the connection and cause the import to fail. Therefore, for large data volumes, we recommend that you use SUBMIT JOB INSERT OVERWRITE SELECT to perform the import asynchronously.

   • Method 1: Run the INSERT INTO statement to import data. If a primary key is duplicated, the current write operation is ignored and the data is not updated. This behavior is equivalent to INSERT IGNORE INTO. For more information, see INSERT INTO. The following is an example statement:

     INSERT INTO adb_oss_import_test
     SELECT * FROM oss_import_test_external_table;

   • Method 2: Run the INSERT OVERWRITE statement to import data, overwriting the existing data in the table. The following is an example statement:

     INSERT OVERWRITE adb_oss_import_test
     SELECT * FROM oss_import_test_external_table;

   • Method 3: Asynchronously run the INSERT OVERWRITE statement to import data. You can use SUBMIT JOB to submit an asynchronous task for background scheduling. You can add a hint (/*+ direct_batch_load=true*/) before the write task to accelerate the task. For more information, see Asynchronous write. The following is an example statement:

     SUBMIT JOB INSERT OVERWRITE adb_oss_import_test
     SELECT * FROM oss_import_test_external_table;

     The following result is returned:

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

     For more information about how to submit an asynchronous task, see Submit an asynchronous import job.

Enterprise, Basic, and Data Lakehouse

For information about the syntax for creating OSS external tables in Enterprise Edition, Basic Edition, and Data Lakehouse Edition, see OSS external tables.

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

Parquet file and AnalyticDB for MySQL data type mapping

ORC file to AnalyticDB for MySQL data type mapping

Paimon files and AnalyticDB for MySQL data type mapping