AnalyticDB for MySQL支持通过外表和INSERT INTO方式将AnalyticDB for MySQL中的数据导出到对象存储OSS(Object Storage Service)中。将数据导出到OSS功能只支持CSV和Parquet格式文件,不支持TEXT格式文件。

前提条件

  • 通过以下步骤在对象存储服务OSS中创建存储AnalyticDB for MySQL数据的目录:
    1. 开通对象存储服务,详情请参见开通OSS服务
      说明 OSS与AnalyticDB for MySQL所属Region相同。
    2. 在OSS中创建存储空间,详情请参见创建存储空间
    3. 在OSS中新建目录,详情请参见新建文件夹

      例如,在OSS中新建目录adb_data/,从AnalyticDB for MySQL中导出的数据将存储在该目录下。

      新建目录
  • 根据AnalyticDB for MySQL快速入门,完成创建集群、设置白名单、创建账号和数据库等准备工作,详情请参见AnalyticDB for MySQL快速入门
说明 如果AnalyticDB for MySQL集群是弹性模式,请先登录AnalyticDB MySQL控制台,查看集群信息,在网络信息栏启用ENI(Elastic Network Interface,弹性网卡)网络。启用ENI网络

操作步骤

本示例将AnalyticDB for MySQL的adb_demo库中的source_table表数据导出至OSS的adb_data文件夹下。

  1. 连接目标集群,进入源数据库。
  2. adb_demo数据库中创建外表,详情请参见创建OSS外表语法
  3. 根据外表类型选择执行写入语句,将源数据写入到步骤2创建的外表中,不同的外表类型支持的语法请参见未做分区的普通外表语法支持分区外表语法支持
  4. 待步骤3的写入任务结束后,您可登录OSS控制台,在目标文件夹下查看导出到OSS的数据。

未做分区的普通外表语法支持

说明 本节中各类外表写出功能,对Parquet格式外表写出功能默认关闭。您可通过hint开放写入。
/*+oss_parquet_write_enable=true*/ INSERT INTO ... 
INSERT SELECT FROM
功能:如果您的数据在其他表中已经存在,可以通过INSERT SELECT FROM将数据复制到外表。
语法:
INSERT INTO table_name 
SELECT select_statement FROM from_statement;       
例句:
insert into oss_table select col1, col2, col3 from source_table;
支持状态 异常信息 功能描述
支持。写入的外表必须保持列个数的完整,不允许用户指定只写入一部分的列。 将源表的数据写入外表对应的OSS位置,每次写入会产生新的OSS数据文件。
REPLACE SELECT FROM
功能:由于OSS外表不支持定义主键,因此REPLACE SELECT FROM的写入表现与INSERT SELECT FROM保持一致,都是将数据复制到另外一张表;如果目标表已有数据,已有数据保持不变,新数据追加到新的OSS数据文件。
语法:
REPLACE INTO table_name 
SELECT select_statement FROM from_statement;     
例句:
replace into oss_table select col1, col2, col3 from source_table;
支持状态 异常信息 功能描述
支持。写入的外表必须保持列个数的完整,不允许用户指定只写入一部分的列。 将源表的数据写入外表对应的OSS位置,每次写入会产生新的OSS数据文件。
INSERT OVERWRITE INTO SELECT
功能:INSERT OVERWRITE INTO SELECT用于向表中批量插入数据。如果目标外表中已存在数据,则每次写入会先删除原外表路径下全部数据文件,再产生新的OSS数据文件。
语法:
INSERT OVERWRITE INTO table_name 
SELECT select_statement FROM from_statement; 
例句:
insert overwrite into oss_table 
select col1, col2, col3 from source_table;
支持状态 异常信息 功能描述
支持,表现为删除根目录下全体文件后再重新写入。写入的外表必须保持列个数的完整,不允许用户指定只写入一部分的列。 将源表的数据写入外表对应的OSS位置,每次写入会先删除原外表路径下全部数据文件,再产生新的OSS数据文件。
导出到OSS单文件(仅限CSV格式,Parquet格式不允许导出到单一文件)
功能:通过hint指定唯一的OSS文件,将数据导出到此文件中。包含overwrite关键字时,覆盖外表TABLE_PROPERTIES中定义的目录下旧的同名文件,不影响该目录下其他文件。
版本说明:
  • 3.1.2版本之前的AnalyticDB for MySQL实例:不支持此功能,文件名由系统自动命名,将会导出多个文件。根据任务并发速度动态确定目标文件数目。
  • 3.1.2及之后版本的AnalyticDB for MySQL实例:支持通过hint支持将AnalyticDB for MySQL CSV格式的普通外表导出到OSS单文件,用户在导出时可自定义文件名。不带hint的情况下,与3.1.2之前的版本保持一致(导出多个文件)。
语法:
/*output_filename=adb.txt*/INSERT [OVERWRITE] table_name 
SELECT select_statement FROM from_statement;
例句:
/*output_filename=adb.txt*/INSERT [OVERWRITE] oss_table 
SELECT * FROM source_table;
支持状态 异常信息 功能描述
支持。写入的外表必须保持列个数的完整,不允许用户指定只写入一部分的列。 将源表的数据写入外表对应的OSS位置,每次写入会产生新的OSS数据文件。
未带hint的各类导出效果:自动导出多个文件
导出到单一文件的效果:导出单文件

分区外表语法支持

分区表写出数据时,数据文件内不包含分区列的数据,分区列的数据信息以OSS目录的形式展现。

例如:分区表中定义了2个分区列,3个普通列。其中一级分区名称为pcol1,分区数值为1;二级分区名称为pcol2,分区数值为a。分区表数据导出到OSS的保存路径为adb_data/,则向pcol1=1且pcol2=a的外表分区写出数据时,数据文件相对路径目录为:adb_data/pcol1=1/pcol2=a/;且外表CSV/Parquet数据文件内不包含pcol1与pcol2这两列的值,只包含3列普通列的值。

说明 本节中各类分区外表写出功能,对CSV和Parquet格式分区外表写出功能均默认关闭。您可通过hint开放写入,CSV和Parquet格式分区外表均使用以下语句:
/*+oss_parquet_write_enable=true*/ INSERT INTO ... 
INSERT INTO PARTITION SELECT FROM
功能:INSERT INTO PARTITION INTO SELECT用于向带分区的外表中批量插入数据。写入时要求指明全部分区信息,且分区的值不能为空。
语法:
INSERT into table_name PARTITION(par1=val1,par2=val2,...) 
SELECT select_statement FROM from_statement;
例句:
insert into oss_table_par PARTITION(par1=val1,par2=val2) 
select col1, col2, col3 from source_table;
支持状态 异常信息 功能描述
支持。写入时,在对应分区追加写入。写入时要求指明全部分区信息,且分区的值不能为空。写入的外表必须保持列个数的完整,不允许用户指定只写入一部分的列。
  • 当分区信息不全时:Invalid: define partition columns number is partition_num,not equal to insert partition number m。
  • 当分区的值为空时:Query execution error: : PARTITION value can not be null。
将源表的数据写入外表对应的OSS位置下的对应分区目录中,每次写入会产生新的OSS数据文件。
REPLACE INTO PARTITION SELECT FROM
功能:由于外表不支持主键,REPLACE INTO PARTITION SELECT FROM行为表现与INSERT INTO PARTITION SELECT FROM一样。
语法:
REPLACE into table_name PARTITION(par1=val1,par2=val2,...) 
SELECT select_statement FROM from_statement;   
例句:
REPLACE into oss_table_par PARTITION(par1=val1,par2=val2) 
select col1, col2, col3 from source_table;
支持状态 异常信息 功能描述
支持。写入时,在对应分区追加写入。写入时要求指明全部分区信息,且分区的值不能为空。写入的外表必须保持列个数的完整,不允许用户指定只写入一部分的列。
  • 当分区信息不全时:Invalid: define partition columns number is partition_num,not equal to insert partition number m。
  • 当分区的值为空时:Query execution error: : PARTITION value can not be null。
将源表的数据写入外表对应的OSS位置下的对应分区目录中,每次写入会产生新的OSS数据文件。
INSERT OVERWRITE [INTO] PARTITION SELECT
功能:INSERT OVERWRITE [INTO] PARTITION [IF NOT EXISTS] SELECT用于向带分区的外表中批量插入数据。写入时要求指明全部分区信息,且分区的值不能为空。如果目标外表中已存在数据,每次写入会先删除原外表对应分区目录下全部数据文件,再产生新的OSS数据文件。
语法:
INSERT OVERWRITE [INTO] table_name PARTITION(par1=val1,par2=val2,...)[IF NOT EXISTS] 
SELECT select_statement FROM from_statement;
例句:
INSERT OVERWRITE into oss_table_par PARTITION(par1=val1,par2=val2) IF NOT EXISTS 
select col1, col2, col3 from source_table;
支持状态 异常信息 功能描述
支持。在对应分区删除旧文件后,再写入。如果带有if not exists,则先判断分区是否存在,如果存在则不执行。写入时要求指明全部分区信息,且分区的值不能为空。写入的外表必须保持列个数的完整,不允许用户指定只写入一部分的列。
  • 当分区信息不全时:Invalid: define partition columns number is partition_num,not equal to insert partition number m。
  • 当分区的值为空时:Query execution error: : PARTITION value can not be null。
  • 如果带有if not exists,则先判断分区是否存在,如果存在则报错不执行覆盖写入:partition is already existed。
将源表的数据写入外表对应的OSS位置下的对应分区目录中,每次写入会先删除原外表对应分区目录下全部数据文件,再产生新的OSS数据文件。如果带有if not exists,则先判断分区是否存在,如果存在则不执行。

不支持语法

ADB for MySQL 3.0 不支持自定义行级写入的插入语法,具体不支持的语法如下:

INSERT INTO VALUES
语法:
INSERT [IGNORE] 
    INTO table_name 
    [( column_name [, …] )]
    [VALUES]
    [(value_list[, …])]
    [query];     
REPLACE INTO VALUES
语法:
REPLACE 
    INTO table_name
    [(column_name,...)]
    VALUES
    ({常量|NULL|DEFAULT},...),(...),...      
分区外表暂不支持未指定分区的写入INSERT SELECT FROM
语法:
INSERT INTO table_name 
SELECT select_statement FROM from_statement;       
分区外表不支持导出到OSS单文件
语法:
/*output_filename=adb.txt*/INSERT into table_name PARTITION(par1=val1,par2=val2,...) 
SELECT select_statement FROM from_statement;

/*output_filename=adb.txt*/REPLACE into table_name PARTITION(par1=val1,par2=val2,...) 
SELECT select_statement FROM from_statement;

/*output_filename=adb.txt*/INSERT OVERWRITE [INTO] table_name PARTITION(par1=val1,par2=val2,...)[IF NOT EXISTS] 
SELECT select_statement FROM from_statement;