云原生数据仓库AnalyticDB MySQL版提供多种数据同步方案,可满足不同场景下的数据同步需求。本文以数据文件存储在OSS中为例,介绍如何将OSS中的数据文件导入AnalyticDB MySQL版adb_demo数据库中并进行查询。

前提条件

  • 通过以下步骤在OSS中创建存储AnalyticDB MySQL版数据的目录。
    1. 开通OSS服务。详情请参见开通OSS服务
    2. 创建存储空间。详情请参见控制台创建存储空间
      重要 OSS的存储空间与AnalyticDB MySQL版所属地域相同。
    3. 创建目录。详情请参见创建目录
    4. 上传测试文件。详情请参见控制台上传文件
      本示例将oss_import_test_data.txt文件上传至OSS中的<bucket-name>.oss-cn-hangzhou.aliyuncs.com/adb/目录,数据行分隔符为换行符,列分隔符为;,文件示例数据如下所示:
      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
      ...     
  • 根据AnalyticDB MySQL版入门指南,完成创建集群、设置白名单、创建账号和数据库等准备工作,详情请参见AnalyticDB MySQL数仓版(3.0)使用流程

操作步骤

  1. 通过CREATE TABLE,在adb_demo数据库中创建外表。创建CSV、Parquet或TEXT格式OSS外表的建表语法请参见创建OSS外表语法
  2. 查询OSS数据。

    查询外表映射表和查询AnalyticDB MySQL版内表语法没有区别,您可以方便地直接进行查询,如本步骤的示例代码所示。

    select uid, other from oss_import_test_external_table where uid < 100 limit 10;
    • 对于数据量较大的CSV或TEXT数据文件,强烈建议您按照后续步骤导入AnalyticDB MySQL版后再做查询,否则查询性能可能会较差。
    • 对于Parquet格式数据文件,直接查询的性能一般也比较高,您可以根据需要决定是否进一步导入到AnalyticDB MySQL版后再做查询。
  3. 通过CREATE TABLE,在adb_demo数据库中创建目标表adb_oss_import_test存储从OSS中导入的数据。
    CREATE TABLE IF NOT EXISTS adb_oss_import_test
    (
        uid string,
        other string
    )
    DISTRIBUTED BY HASH(uid);                  
  4. 执行INSERT语句将OSS数据导入AnalyticDB MySQL版
    重要 使用INSERT INTOINSERT OVERWRITE INTO SELECT导入数据时,默认是同步执行流程。如果数据量较大,达到几百GB,客户端到AnalyticDB MySQL服务端的连接会中断,导致数据导入失败。因此,如果您的数据量较大时,推荐使用SUB JOB INSERT OVERWRITE INTO SELECT异步执行导入。
    • 方式一:执行INSERT INTO导入数据,当主键重复时会自动忽略当前写入数据,不进行更新覆盖,作用等同于INSERT IGNORE INTO,详情请参见INSERT INTO。示例如下:
      INSERT INTO adb_oss_import_test
      SELECT * FROM oss_import_test_external_table;
    • 方式二:执行INSERT OVERWRITE INTO导入数据,会覆盖表中原有的数据。示例如下:
      INSERT OVERWRITE INTO adb_oss_import_test
      SELECT * FROM oss_import_test_external_table;
    • 方式三:异步执行INSERT OVERWRITE INTO导入数据。 通常使用SUBMIT JOB提交异步任务,由后台调度,可以在写入任务前增加Hint(/* direct_batch_load=true*/)加速写入任务。详情请参见异步写入。示例如下:
      SUBMIT JOB INSERT OVERWRITE INTO adb_oss_import_test
      SELECT * FROM oss_import_test_external_table;
      返回结果如下:
      +---------------------------------------+
      | job_id                                |
      +---------------------------------------+
      | 2020112122202917203100908203303****** |

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

  5. 执行以下命令,查询adb_oss_import_test表的数据。
    SELECT * FROM adb_oss_import_test;

创建OSS外表语法

创建OSS CSV格式外表

示例的oss_import_test_data.txt文件为CSV格式,本节介绍CSV格式的OSS外表创建语法。

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://<bucket-name>/adb/oss_import_test_data.csv",
    "accessid":"LTAIF****5FsE",
    "accesskey":"Ccw****iWjv",
    "delimiter":";",
    "skip_header_line_count":1
}'; 
参数是否必填说明
ENGINE='OSS'必填表示该表是外部表,使用的存储引擎是OSS。
TABLE_PROPERTIES用于告知AnalyticDB MySQL版如何访问OSS中的数据。
endpointOSS的EndPoint(地域节点)
说明 目前仅支持AnalyticDB MySQL版通过ECS的VPC网络访问OSS。

登录OSS控制台,单击目标Bucket,在Bucket概览页面查看EndPoint(地域节点)

urlOSS中源数据文件或文件夹的绝对路径。建议文件夹绝对路径以正斜线(/)结尾。
示例:
  • 文件:oss://<bucket-name>/adb/oss_import_test_data.csv
  • 文件夹:oss://<bucket-name>/adb_data/
accessid您在访问OSS中的文件或文件夹时所持有的AccessKey ID。

如何获取您的AccessKey ID和AccessKey Secret,请参见账号与权限

accesskey您在访问OSS中的文件或文件夹时所持有的AccessKey Secret。
delimiter定义CSV数据文件的列分隔符。例如您可以将列分隔符设置为英文逗号(,)。
null_value选填定义CSV数据文件的NULL值。默认将空值定义为NULL,即"null_value": ""
说明 AnalyticDB MySQL版集群需为V3.1.4.2或以上版本才支持配置该参数。关于版本信息,请参见新功能发布记录
ossnull选择CSV数据文件中NULL值的对应规则。取值范围如下:
  • 1(默认值):表示EMPTY_SEPARATORS,即仅将空值定义为NULL

    示例:a,"",,c --> "a","",NULL,"c"

  • 2:表示EMPTY_QUOTES,即仅将""定义为NULL

    示例:a,"",,c --> "a",NULL,"","c"

  • 3:表示BOTH,即同时将空值和""定义为NULL

    示例:a,"",,c --> "a",NULL,NULL,"c"

  • 4:表示NEITHER,即空值和""均不定义为NULL

    示例:a,"",,c --> "a","","","c"

说明 上述各示例的前提为"null_value": ""
skip_header_line_count定义导入数据时需要在开头跳过的行数。CSV文件第一行为表头,若设置该参数为1,导入数据时可自动跳过第一行的表头。

默认取值为0,即不跳过。

oss_ignore_quote_and_escape是否忽略字段值中的引号和转义。默认取值为false,即不忽略字段值中的引号和转义。
说明 AnalyticDB MySQL版集群需为V3.1.4.2或以上版本才支持设置该参数。关于版本信息,请参见新功能发布记录
创建OSS Parquet格式/OSS ORC格式外表
以Parquet格式为例,创建OSS外表的语句如下:
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://<bucket-name>/adb_data/",
    "accessid":"LTAIF****5FsE",
    "accesskey":"Ccw****iWjv",
    "format":"parquet"
}';
参数说明
ENGINE= 'OSS'表示该表是外部表,使用的存储引擎是OSS。
TABLE_PROPERTIES用于告知AnalyticDB MySQL版如何访问OSS中的数据。
endpointOSS的EndPoint(地域节点)(域名节点)。
说明 目前仅支持AnalyticDB MySQL版通过OSS中ECS的VPC网络(内网)访问OSS。

登录OSS控制台,单击目标Bucket,在Bucket概览页面查看EndPoint(地域节点)

urlOSS中源数据文件或文件夹的绝对路径。建议文件夹绝对路径以正斜线(/)结尾。
示例:
  • 文件:oss://<bucket-name>/adb/oss_import_test_data.parquet
  • 文件夹:oss://<bucket-name>/adb_data/
说明 建表时请将示例中的url替换为实际的OSS路径。
accessid您在访问OSS中的文件或文件夹时所持有的AccessKey ID。

如何获取您的AccessKey ID和AccessKey Secret,请参见账号与权限

accesskey您在访问OSS中的文件或文件夹时所持有的AccessKey Secret。
format数据文件的格式。
  • 创建Parquet格式文件的外表时需设置为parquet
  • 创建ORC格式文件的外表时需设置为orc
说明 不指定format时,默认格式为CSV。
说明
  • 外表创建语句中的列名需与Parquet或ORC文件中该列的名称完全相同(可忽略大小写),且列的顺序需要一致。
  • 创建外表时,可以仅选择Parquet或ORC文件中的部分列作为外表中的列,未被选择的列不会被导入。
  • 如果创建外表创建语句中出现了Parquet或ORC文件中不存在的列,针对该列的查询结果均会返回NULL。

创建Parquet格式文件的外表时,需要注意数据类型的对应关系,具体规则如下:

Parquet文件与AnalyticDB MySQL版3.0的数据类型映射关系如下表。
Parquet基本类型Parquet的logicalType类型AnalyticDB MySQL版3.0的数据类型
BOOLEANBOOLEAN
INT32INT_8TINYINT
INT32INT_16SMALLINT
INT32INT或INTEGER
INT64BIGINT
FLOATFLOAT
DOUBLEDOUBLE
  • FIXED_LEN_BYTE_ARRAY
  • BINARY
  • INT64
  • INT32
DECIMALDECIMAL
BINARYUTF-8
  • VARCHAR
  • STRING
  • JSON(如果已知Parquet该列内容为JSON格式)
INT32DATEDATE
INT64TIMESTAMP_MILLISTIMESTAMP或DATETIME
INT96TIMESTAMP或DATETIME
重要 Parquet格式外表暂不支持STRUCT类型,会导致建表失败。

针对带有分区的Parquet或CSV数据文件创建OSS外表

如果OSS数据源是包含分区的,会在OSS上形成一个分层目录,类似如下内容:
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
上述数据中p1为第1级分区,p2为第2级分区,p3为第3级分区。对应这种数据源,一般都希望以分区的模式进行查询,那么就需要在创建OSS外表时指明分区列。以Parquet格式为例,创建带有分区的OSS外表的语句如下:
CREATE TABLE IF NOT EXISTS oss_parquet_partition_table
(
  uid varchar,
  other varchar,
  p1 date,
  p2 int,
  p3 varchar
)
ENGINE='OSS'
TABLE_PROPERTIES='{
  "endpoint":"oss-cn-hangzhou-internal.aliyuncs.com",
  "url":"oss://<bucket-name>/adb/oss_parquet_data_dir",
  "accessid":"LTAIF****5FsE",
  "accesskey":"Ccw****iWjv",
  "format":"parquet",
  "partition_column":"p1, p2, p3"
}';
说明
  • TABLE_PROPERTIES中的partition_column属性必须声明分区列(本例中的p1、p2、p3)且partition_column属性里必须严格按照第1级、第2级、第3级的顺序声明(本例中p1为第1级分区,p2为第2级分区,p3为第3级分区)。
  • 列定义中必须定义分区列(本例中的p1、p2、p3)及类型,且分区列需要置于列定义的末尾。
  • 列定义中分区列的先后顺序需要与partition_column中分区列的顺序保持一致。
  • 可以作为分区列的数据类型有:BOOLEANTINYINTSMALLINTINTINTEGERBIGINTFLOATDOUBLEDECIMALVARCHARSTRINGDATETIMESTAMP
  • 查询时分区列和其它数据列的表现和用法没有区别。
  • 不指定format时,默认格式为CSV。

相关文档

更多导入数据方式,请参见支持的数据源