通过外表导入OSS数据

企业版、基础版及湖仓版

1. 进入SQL开发编辑器。

   1. 登录云原生数据仓库AnalyticDB MySQL控制台，在左上角选择集群所在地域。在左侧导航栏，单击集群列表，然后单击目标集群ID。

   2. 在左侧导航栏，单击作业开发 > SQL开发。

2. 导入数据。

   数据导入方式分为常规导入（默认）和弹性导入。常规导入在计算节点中读取源数据，然后在存储节点中构建索引，消耗计算资源和存储资源。弹性导入在Serverless Spark Job中读取源数据和构建索引，消耗Job型资源组的资源。仅内核版本3.1.10.0及以上且已创建Job型资源组的企业版、基础版及湖仓版集群支持弹性导入数据。更多内容，请参见数据导入方式介绍。

   常规导入

   1. 创建外部数据库。

      CREATE EXTERNAL DATABASE adb_external_db;

   2. 创建外表。使用CREATE EXTERNAL TABLE语句在外部数据库adb_external_db中创建OSS外表。本文以adb_external_db.person为例。

      说明

      AnalyticDB for MySQL外表的字段名称、字段数量、字段顺序、字段类型需要与和OSS文件相同。

      创建OSS非分区外表

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

      创建OSS分区外表

      创建OSS分区外表，并添加分区，才能查询出OSS分区外表的数据。

      1. 创建OSS分区外表。

         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. 添加分区。您可以通过ALTER TABLE ADD PARTITION手动添加分区，也可以通过MSCK REPAIR TABLE自动识别并添加分区。

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

         说明

         • 增加或删除OSS外表的分区，请参见ALTER TABLE（外表）。

         • 自动同步OSS外表的分区，请参见MSCK REPAIR TABLE。

      OSS外表的语法说明，请参见CREATE EXTERNAL TABLE。

   3. 查询数据。

      数据表创建成功后，您可以在AnalyticDB for MySQL中通过SELECT语句查询OSS的数据。

      SELECT * FROM adb_external_db.person;

      返回结果如下：

      +------+-------+------+-----------+
      | 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. 在AnalyticDB for MySQL中创建数据库。如果有已创建的数据库，可以忽略本步骤。示例如下：

      CREATE DATABASE adb_demo; 

   5. 在AnalyticDB for MySQL中创建表用于存储从OSS中导入的数据。示例如下：

      说明

      创建的内表和步骤b中创建的外表的字段名称、字段数量、字段顺序、字段类型必须相同。

      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. 向表中导入数据。

      • 方法一：使用INSERT INTO语句导入数据，当主键重复时会自动忽略当前写入数据，数据不做更新，作用等同于INSERT IGNORE INTO，更多信息，请参见INSERT INTO。示例如下：

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

      • 方法二：使用INSERT OVERWRITE INTO语句同步导入数据，会覆盖表中原有的数据。示例如下：

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

      • 方法三：使用INSERT OVERWRITE INTO语句异步导入数据，更多信息，请参见异步写入。示例如下：

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

   弹性导入

   1. 创建数据库。如果有已创建的数据库，可以忽略本步骤。示例如下：

      CREATE DATABASE adb_demo; 

   2. 创建外表。

      说明

      • AnalyticDB for MySQL外表的字段名称、字段数量、字段顺序、字段类型需要与和OSS文件相同。

      • 弹性导入仅支持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":","
      }';

      重要

      创建外表时，CSV、Parquet、ORC格式的外表支持设置的TABLE_PROPERTIES参数不同：

      • CSV格式：仅支持设置endpoint、url、accessid、accesskey、format、delimiter、null_value和partition_column参数。

      • Parquet格式：仅支持设置endpoint、url、accessid、accesskey、format和partition_column参数。

      • ORC格式：仅支持设置endpoint、url、accessid、accesskey、format和partition_column参数。

      外表支持设置的参数及参数说明，请参见OSS非分区外表和OSS分区外表。

   3. 查询数据。

      数据表创建成功后，您可以在AnalyticDB for MySQL中通过SELECT语句查询OSS的数据。

      SELECT * FROM oss_import_test_external_table;

      返回结果如下：

      +------+-------+------+-----------+
      | 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. 在AnalyticDB for MySQL中创建表用于存储从OSS中导入的数据。示例如下：

      说明

      创建的内表和步骤b中创建的外表的字段名称、字段数量、字段顺序、字段类型必须相同。

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

   5. 导入数据。

      重要

      弹性导入仅支持通过INSERT OVERWRITE INTO语句导入数据。

      • 方法一：执行INSERT OVERWRITE INTO弹性导入数据，会覆盖表中原有的数据。示例如下：

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

      • 方法二：异步执行INSERT OVERWRITE INTO弹性导入数据。通常使用SUBMIT JOB提交异步任务，由后台调度。

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

        重要

        异步提交弹性导入任务时，不支持设置优先级队列。

        返回结果如下：

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

      使用SUBMIT JOB提交异步任务后，返回结果仅表示异步任务提交成功。您可以通过job_id终止异步任务或查询异步任务状态，判断任务是否执行成功。具体操作，请参见异步提交导入任务。

      Hint参数说明：

      • elastic_load：是否使用弹性导入方式。取值：true或false（默认值）。

      • elastic_load_configs：弹性导入方式支持配置的参数。参数需使用方括号（[ ]）括起来，且多个参数之间以竖线（|）分隔，支持配置的参数如下表所示：

        参数	是否必填	说明
        adb.load.resource.group.name	是	执行弹性导入任务的Job资源组名称。
        adb.load.job.max.acu	否	单个弹性导入任务最多使用的资源。单位为ACU，最小值为5 ACU。默认值为集群Shard个数+1。 执行如下语句可查询集群Shard个数： SELECT count(1) FROM information_schema.kepler_meta_shards;
        spark.driver.resourceSpec	否	Spark driver的资源规格。默认值为small。取值范围，请参见Spark应用配置参数说明的型号列。
        spark.executor.resourceSpec	否	Spark executor的资源规格。默认值为large。取值范围，请参见Spark应用配置参数说明的型号列。
        spark.adb.executorDiskSize	否	Spark executor的磁盘容量，取值范围为(0,100]，单位为GiB，默认值为10 Gi。更多信息，请参见指定Driver和Executor资源。

   6. （可选）查看已提交的导入任务是否为弹性导入任务。

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

      返回结果如下：

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

      is_elastic_load的返回值为1，表示已提交的导入任务是弹性导入任务；若为0，则表示已提交的导入任务是常规导入任务。

数仓版

1. 连接集群，创建数据库。

   CREATE DATABASE adb_demo;

2. 创建外表。使用CREATE TABLE语法创建CSV、Parquet或ORC格式的OSS外表。具体语法，请参见OSS外表语法。

   本文示例以CSV格式非分区外表为例。

   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. 查询oss_import_test_external_table外表数据。

   说明

   对于CSV格式、Parquet和ORC格式数据文件，数据量越大，通过外表查询的性能损耗越大。如果您需要进一步提升查询效率，建议您按照后续步骤（4~5）将OSS外表数据导入AnalyticDB for MySQL后再做查询。

   SELECT * FROM oss_import_test_external_table;

4. 在AnalyticDB for MySQL中创建表，用于存储从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. 执行INSERT语句将OSS外表数据导入AnalyticDB for MySQL。

   重要

   使用INSERT INTO或INSERT OVERWRITE SELECT导入数据时，默认是同步执行流程。如果数据量较大，达到几百GB，客户端到AnalyticDB for MySQL服务端的连接需要保持较长时间。在此期间，可能会因为网络因素导致连接中断，进而导致数据导入失败。因此，如果您的数据量较大时，推荐使用SUBMIT JOB INSERT OVERWRITE SELECT异步执行导入。

   • 方式一：执行INSERT INTO导入数据，当主键重复时会自动忽略当前写入数据，不进行更新覆盖，作用等同于INSERT IGNORE INTO，详情请参见INSERT INTO。示例如下：

     INSERT INTO adb_oss_import_test
     SELECT * FROM oss_import_test_external_table;

   • 方式二：执行INSERT OVERWRITE导入数据，会覆盖表中原有的数据。示例如下：

     INSERT OVERWRITE adb_oss_import_test
     SELECT * FROM oss_import_test_external_table;

   • 方式三：异步执行INSERT OVERWRITE导入数据。 通常使用SUBMIT JOB提交异步任务，由后台调度，可以在写入任务前增加Hint（/*+ direct_batch_load=true*/）加速写入任务。详情请参见异步写入。示例如下：

     SUBMIT JOB INSERT OVERWRITE adb_oss_import_test
     SELECT * FROM oss_import_test_external_table;

     返回结果如下：

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

     关于异步提交任务详情，请参见异步提交导入任务。

企业版、基础版及湖仓版

企业版、基础版及湖仓版OSS外表的语法及相关说明，请参见OSS外表。

数仓版

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文件与AnalyticDB for MySQL的数据类型映射关系

ORC文件与AnalyticDB for MySQL的数据类型映射关系

Paimon文件与AnalyticDB for MySQL的数据类型映射关系