通过外表导入至数仓版

前提条件

• AnalyticDB for MySQL集群的产品系列为数仓版。

  说明

  数仓版弹性模式集群需要在集群信息页面的网络信息区域，打开启用ENI网络的开关。

• AnalyticDB for MySQL集群与OSS存储空间位于同一地域。具体操作，请参见开通OSS服务。

示例数据说明

本示例将oss_import_test_data.csv文件上传至OSS中的testBucketname/adb/目录，数据行分隔符为换行符，列分隔符为半角分号（;）。oss_import_test_data.csv文件示例数据如下所示：

uid;other
12;hello_world_1
27;hello_world_2
28;hello_world_3
33;hello_world_4
37;hello_world_5
40;hello_world_6        

操作步骤

1. 在AnalyticDB for MySQL中创建数据库。

   CREATE DATABASE adb_demo;

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

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

   CREATE TABLE IF NOT EXISTS oss_import_test_external_table
   (
       uid string,
       other string
   )
   ENGINE='OSS'
   TABLE_PROPERTIES='{
       "endpoint":"oss-cn-hangzhou-internal.aliyuncs.com",
       "url":"oss://testBucketname/adb/oss_import_test_data.csv",
       "accessid":"LTAIF****5FsE",
       "accesskey":"Ccw****iWjv",
       "delimiter":";",
       "skip_header_line_count":1,
       "charset":"utf-8"
   }'; 

3. 查询oss_import_test_external_table外表数据。

   说明

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

   SELECT uid, other FROM oss_import_test_external_table WHERE uid < 100 LIMIT 10;

4. 在AnalyticDB for MySQL中创建表，用于存储从OSS外表导入的数据。

   CREATE TABLE IF NOT EXISTS adb_oss_import_test
   (
       uid string,
       other string
   )
   DISTRIBUTED BY HASH(uid);

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外表语法

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":"txt|orc|parquet",
    "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":"LTAIF****5FsE",
    "accesskey":"Ccw****iWjv",
    "url":"oss://testBucketname/adb_data/",
    "delimiter": "\\1",
    "null_value": "\\\\N",
    "oss_ignore_quote_and_escape": "true",
    "ossnull": 2
}';

parquet_partition_classic/
├── p1=2020-01-01
│ ├── p2=4
│ │ ├── p3=SHANGHAI
│ │ │ ├── 000000_0
│ │ │ └── 000000_1
│ │ └── p3=SHENZHEN
│ │     └── 000000_0
│ └── p2=6
│     └── p3=SHENZHEN
│         └── 000000_0
├── p1=2020-01-02
│ └── p2=8
│     ├── p3=SHANGHAI
│     │ └── 000000_0
│     └── p3=SHENZHEN
│         └── 000000_0
└── p1=2020-01-03
    └── p2=6
        ├── p2=HANGZHOU
        └── p3=SHENZHEN
            └── 000000_0

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":"parquet",
    "partition_column":"p1, p2, p3"
}';