首页 云原生数据仓库AnalyticDB MySQL版 数据开发 数据导入导出 OSS 通过外表导入OSS数据至湖仓版

通过外表导入OSS数据至湖仓版

本文介绍如何通过外表查询OSS数据，并将OSS中的数据文件导入AnalyticDB MySQL版湖仓版（3.0）集群。

前提条件

示例数据说明

本文示例将数据文件person.txt上传至OSS中的<bucket-name>.oss-cn-hangzhou.aliyuncs.com/adb/目录，数据行分隔符为换行符，列分隔符为英文逗号（,）。person.txt中的示例数据如下：

1,james,10
2,bond,20
3,jack,30
4,lucy,40

操作步骤

  1. 进入SQL开发编辑器。
    1. 登录云原生数据仓库AnalyticDB MySQL控制台。
    2. 在页面左上角，选择集群所在地域。
    3. 在左侧导航栏，单击集群列表。
    4. 在湖仓版（3.0）页签下，单击目标集群ID。
    5. 在左侧导航栏，单击作业编辑器 > SQL开发。

  2. 创建外部数据库。

    CREATE EXTERNAL DATABASE adb_external_db;

  3. 创建外表。使用CREATE TABLE语句在目标库adb_external_db中创建OSS外表。本文以adb_external_db.person为例。

    说明

    AnalyticDB MySQL外表和OSS中数据文件的字段名称、字段数量、字段顺序、数据类型必须相同。

    创建OSS非分区表

    CREATE EXTERNAL TABLE adb_external_db.person
(
 id STRING ,
 name STRING ,
 age INT
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY  ','
STORED AS TEXTFILE
LOCATION  'oss://adb-test/test_db/person/';

    语法说明，请参见CREATE EXTERNAL TABLE。

    创建OSS分区表

    CREATE EXTERNAL TABLE adb_external_db.partition_table_test
(
 id STRING ,
 name STRING ,
 age INT
)
PARTITIONED BY (dt STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY  ','
STORED AS TEXTFILE
LOCATION  'oss://adb-test/test_db/partition_table_test/';

    语法说明，请参见CREATE EXTERNAL TABLE。

    说明

  4. 查询数据。

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

    SELECT * FROM adb_external_db.person;

    返回结果如下：

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

  5. 将OSS数据导入到AnalyticDB MySQL中。

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

      CREATE DATABASE adb_demo;

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

      说明

      创建的adb_demo.adb_import_test表和步骤3中创建的外表的字段名称、字段数量、字段顺序、数据类型必须相同。

      CREATE TABLE IF NOT EXISTS adb_demo.adb_import_test(
    id int,
    name varchar(1023),
    age int
)
DISTRIBUTE BY HASH(id);
    3. 向表中写入数据，示例如下：
      • 方式一：执行INSERT INTO导入数据，当主键重复时会自动忽略当前写入数据，不做更新，作用等同于INSERT IGNORE INTO，详情请参见INSERT INTO。示例如下： 
        INSERT INTO adb_demo.adb_import_test
SELECT * FROM adb_external_db.person;
        通过SELECT查询写入表中的数据，示例如下：
        select * from adb_demo.adb_import_test;
        返回结果如下：
        +------+-------+------+
| id   | name  | age  |
+------+-------+------+
|    1 | james |   10 |
|    2 | bond  |   20 |
|    3 | jack  |   30 |
|    4 | lucy  |   40 |
+------+-------+------+
      • 方式二：执行INSERT OVERWRITE INTO导入数据，会覆盖表中原有的数据。示例如下： 
        INSERT OVERWRITE INTO adb_demo.adb_import_test
SELECT * FROM adb_external_db.person;
      • 方式三：异步执行INSERT OVERWRITE INTO导入数据。通常使用SUBMIT JOB提交异步任务，由后台调度，可以在写入任务前增加Hint（/* direct_batch_load=true*/）加速写入任务。详情请参见异步写入。示例如下： 
        SUBMIT job 
INSERT OVERWRITE INTO adb_demo.adb_import_test
SELECT * FROM adb_external_db.person;
        返回结果如下：
        +---------------------------------------+
| job_id                                |
+---------------------------------------+
| 2020112122202917203100908203303****** |

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

阿里云首页 云原生数据仓库AnalyticDB MySQL版 相关技术圈