AnalyticDB MySQL版支持通过外表导入导出数据。本文介绍如何通过AnalyticDB MySQL的外部映射表将AnalyticDB MySQL数据导出至HDFS。

前提条件

  • AnalyticDB MySQL版集群需为V3.1.4.4或以上版本。
    说明
  • 已创建HDFS集群，并在HDFS集群中创建了一个新的文件夹（本示例中文件夹名为hdfs_output_test_csv_data），用于保存导入的AnalyticDB MySQL版数据。
    说明 使用INSERT OVERWRITE进行导入时，系统会覆盖目标文件夹下的原始文件。为避免原始文件被覆盖，建议在导出时创建一个新的目标文件夹。
  • 已在HDFS集群中为AnalyticDB MySQL集群配置如下服务访问端口：
    • namenode：用于读写文件系统元信息。您可以在fs.defaultFS参数中配置端口号，默认端口号为8020。

      详细配置方式，请参见core-default.xml。

    • datanode：用于读写数据。您可以在dfs.datanode.address参数中配置端口号，默认端口号为50010。

      详细配置方式，请参见hdfs-default.xml。

  • 如果您的AnalyticDB MySQL集群是弹性模式，您需要在集群信息页面的网络信息区域，打开启用ENI网络的开关。启用ENI网络

注意事项

  • AnalyticDB MySQL版集群仅支持导出文件格式为CSV和Parquet的数据至HDFS。不支持导出文件格式为ORC的数据。
  • AnalyticDB MySQL版集群不支持自定义行级写入的INSERT语法，如INSERT INTO VALUES和REPLACE INTO VALUES。
  • 不支持通过分区外表导出单个文件至HDFS。
  • 通过分区外表导出数据时，数据文件内不包含分区列的数据，分区列的数据信息以HDFS目录的形式展现。

    例如，已在分区外表中定义了3个普通列和2个分区列。其中一级分区列的列名为p1，分区列的值为1。二级分区名称为p2，分区数值为a，现需要通过分区外表将数据导出到HDFS的adb_data/路径下。

    那么当p1=1且p2=a的外表分区导出数据时，数据文件相对路径目录为adb_data/p1=1/p2=a/，且外表CSV或Parquet数据文件内不包含p1和p2这两列，只包含3列普通列的值。

操作步骤

  1. 连接目标AnalyticDB MySQL集群。详细操作步骤，请参见连接集群。
  2. 创建源数据库。详细操作步骤，请参见创建数据库。

    本示例中，AnalyticDB MySQL集群的源库名为adb_demo。

  3. 创建源表并插入源数据。

    您可以使用以下语句在源库adb_demo中创建一张源表adb_hdfs_import_source，建表语句如下： 

    CREATE TABLE IF NOT EXISTS adb_hdfs_import_source
(
    uid string,
    other string
)
DISTRIBUTED BY HASH(uid);
    往源表adb_hdfs_import_source中插入一行测试数据，语句如下：
    INSERT INTO adb_hdfs_import_source VALUES ("1", "a"), ("2", "b"), ("3", "c");
  4. 创建外部映射表。

    您可以使用以下语法在源库adb_demo中创建一张外部映射表，用于将AnalyticDB MySQL数据导出至HDFS：

    • 创建普通外部映射表（本文示例中目标表名为hdfs_import_external），语法如下。
      CREATE TABLE IF NOT EXISTS hdfs_import_external
(
    uid string,
    other string
)
ENGINE='HDFS'
TABLE_PROPERTIES='{
    "format":"csv",
    "delimiter":",",
    "hdfs_url":"hdfs://172.17.***.***:9000/adb/hdfs_output_test_csv_data"
}';
    • 创建带分区的外部映射表时（本文示例中目标表名为hdfs_import_external_par），需要同时在创建语句中定义普通列（如uid和other）和分区列（如p1、p2和p3），语法如下。
      CREATE TABLE IF NOT EXISTS hdfs_import_external_par
(
    uid string,
    other string,
    p1 date,
    p2 int,
    p3 varchar
)
ENGINE='HDFS'
TABLE_PROPERTIES='{
    "format":"csv",
    "delimiter":",",
    "hdfs_url":"hdfs://172.17.***.***:9000/adb/hdfs_output_test_csv_data"
    "partition_column":"p1, p2, p3"
}';
    说明
    • AnalyticDB MySQL版集群仅支持导出文件格式为CSV和Parquet的数据至HDFS。不支持导出文件格式为ORC的数据。
    • 创建外表的详细语法说明，请参见创建HDFS外表和创建带分区的HDFS外表。
  5. 将源AnalyticDB MySQL版集群中的数据导出至目标HDFS中。

后续步骤

导出完成后，您可以通过Hadoop客户端到目标文件夹hdfs_output_test_csv_data中查看导出的数据文件。您也可以登录AnalyticDB MySQL版集群，在外表中（分区外表与普通外表查询语句一致，本示例以普通外表hdfs_import_external为例）执行如下语句查询已导出的数据：
SELECT * FROM hdfs_import_external LIMIT 100;

附录1：数据导出语法（普通外表）

若创建外表时未指定分区列，您可以根据业务需要选择如下几种方式导出数据：

  • 方式一：如果您的数据已存在于目标表中，可以通过INSERT INTO语句将数据导入外表。使用该语句会将源表的数据写入外表对应的HDFS位置，每次写入会产生新的HDFS数据文件。
    说明 外表里的列和需要导出的列，必须保持列个数的完整。INSERT INTO为增量写入，会额外产生新的文件，不会覆盖旧的历史文件。
    语法如下。
    INSERT INTO <target_table> 
SELECT <col_name> FROM <source_table>;
    示例如下。
    INSERT INTO hdfs_import_external
SELECT col1, col2, col3 FROM adb_hdfs_import_source;
    说明 col1, col2, col3表示外表中的所有列。
  • 方式二：HDFS外表不支持定义主键，因此REPLACE INTO的写入表现与INSERT INTO一致，都会将数据复制到外表。如果目标表内已有数据，执行REPLACE INTO语句导入时，已有数据保持不变，新数据会被追加到目标数据文件中。
    说明
    • 写入的外表必须保持列个数的完整，不允许用户指定只写入一部分的列。
    • REPLACE INTO为增量写入，会额外产生新的文件，不会覆盖旧的历史文件。
    语法如下。
    REPLACE INTO <target_table> 
SELECT <col_name> FROM <source_table>;
    示例如下。
    REPLACE INTO hdfs_import_external
SELECT col1, col2, col3 FROM adb_hdfs_import_source;
  • 方式三：您可以使用INSERT OVERWRITE INTO语法向外表中批量插入数据。如果目标外表中已存在数据，每次写入会先删除外表路径下的全部数据文件，再产生新的HDFS数据文件。
    重要
    • 写入的外表必须保持列个数的完整，不允许指定只写入部分的列。
    • INSERT OVERWRITE INTO为覆盖写入，会覆盖导出目录内已有的历史数据，谨慎使用。
    语法如下。
    INSERT OVERWRITE INTO <target_table> 
SELECT <col_name> FROM <source_table>;
    示例如下。
    INSERT OVERWRITE INTO hdfs_import_external 
SELECT col1, col2, col3 FROM adb_hdfs_import_source;
  • 方式四：异步执行INSERT OVERWRITE INTO导出数据，语法如下。
    SUBMIT job INSERT OVERWRITE INTO <target_table> 
SELECT <col_name> FROM <source_table>;
    示例如下。
    SUBMIT JOB INSERT OVERWRITE INTO hdfs_import_external  
SELECT col1, col2, col3 FROM adb_hdfs_import_source;
    返回结果如下。
    +---------------------------------------+
| job_id                                |
+---------------------------------------+
| 2020112122202917203100908203303****** |
+---------------------------------------+

    您还可以根据上述job_id查看异步任务的状态。更多详情，请参见异步提交导入导出任务。

附录2：数据导出语法（分区外表）

分区外表在语法中加入PARTITION字段导出数据，您还可以通过指定PARTITION字段中的分区列和分区值来确定是否使用静态或者动态分区。

  • 方式一：您可以使用INSERT INTO PARTITION语法往带分区的外表中批量插入数据。
    说明 写入时，数据将在对应分区追加写入，每次写入会产生新的HDFS数据文件，历史数据不会被覆盖；写入的外表必须保持列个数的完整，不允许用户指定只写入一部分的列。
    • 全静态分区
      语法如下。
      INSERT INTO <target_table> PARTITION(par1=val1,par2=val2,...) 
SELECT <col_name> FROM <source_table>;
      示例如下。
      INSERT INTO hdfs_import_external_par PARTITION(p1='2021-05-06',p2=1,p3='test') 
SELECT col1, col2, col3, FROM adb_hdfs_import_source;
    • 半静态半动态分区
      说明 静态列必须位于动态列的前面，不允许穿插使用。
      语法如下。
      INSERT INTO <target_table> PARTITION(par1=val1,par2,...) 
SELECT <col_name> FROM <source_table>;
      示例如下。
      INSERT INTO hdfs_import_external_par PARTITION(p1='2021-05-27',p2,p3)  
SELECT col1, col2, col3, FROM adb_hdfs_import_source;
    • 全动态分区（即不需要PARTITION字段）
      语法如下。
      INSERT INTO <target_table> 
SELECT <col_name> FROM <source_table>;
      示例如下。
      INSERT INTO hdfs_import_external_par  
SELECT col1, col2, col3, FROM adb_hdfs_import_source;
  • 方式二：HDFS外表不支持定义主键，因此REPLACE INTO PARTITION的写入表现与INSERT INTO PARTITION一致。
    说明 写入的外表必须保持列个数的完整，不允许用户指定只写入一部分的列；REPLACE INTO PARTITION为增量写入，会额外产生新的文件，不会覆盖旧的历史文件。

    语法如下：

    • 全静态分区
      语法如下。
      REPLACE INTO <target_table> PARTITION(par1=val1,par2=val2,...) 
SELECT <col_name> FROM <source_table>;
      示例如下。
      REPLACE INTO hdfs_import_external_par PARTITION(p1='2021-05-06',p2=1,p3='test') 
SELECT col1, col2, col3, FROM adb_hdfs_import_source;
    • 半静态半动态分区
      说明 静态列必须位于动态列的前面，不允许穿插使用。
      语法如下。
      REPLACE INTO <target_table> PARTITION(par1=val1,par2,...) 
SELECT <col_name> FROM <source_table>;
      示例如下。
      REPLACE INTO hdfs_import_external_par PARTITION(p1='2021-05-06',p2,p3)  
SELECT col1, col2, col3, FROM adb_hdfs_import_source;
    • 全动态分区（即不需要PARTITION字段）
      语法如下。
      REPLACE INTO <target_table> 
SELECT <col_name> FROM <source_table>;
      示例如下。
      REPLACE INTO hdfs_import_external_par  
SELECT col1, col2, col3, FROM adb_hdfs_import_source;
  • 方式三：INSERT OVERWRITE [INTO] PARTITION与INSERT INTO PARTITION使用方法相同，但使用INSERT OVERWRITE [INTO] PARTITION时，会覆盖掉本次执行中涉及到的目标分区中之前已有的数据文件，对于没有新数据写入的分区，则不会清除其中的数据文件。
    语法如下。
    INSERT OVERWRITE [INTO] <target_table> PARTITION(par1=val1,par2=val2,...)[IF NOT EXISTS] 
SELECT <col_name> FROM <source_table>;
    重要
    • 写入的外表必须保持列个数的完整，不允许用户指定只写入一部分的列；INSERT OVERWRITE [INTO] PARTITION为覆盖写入，会覆盖导出目录内已有的历史数据，谨慎使用。
    • IF NOT EXISTS：表示如果外表分区已存在，则不会导出到这个分区。
    示例如下。
    INSERT OVERWRITE INTO hdfs_import_external_par PARTITION(p1='2021-05-06',p2=1,p3='test') IF NOT EXISTS 
SELECT col1, col2, col3 FROM adb_hdfs_import_source;
  • 方式四：异步执行INSERT OVERWRITE INTO导出数据，语法如下。
    SUBMIT JOB INSERT OVERWRITE INTO <target_table> 
SELECT <col_name> FROM <source_table>;
    示例如下。
    SUBMIT JOB INSERT OVERWRITE INTO hdfs_import_external_par PARTITION(p1='2021-05-06',p2=1,p3='test') IF NOT EXISTS
SELECT col1, col2, col3 FROM adb_hdfs_import_source;
    返回结果如下。
    +---------------------------------------+
| job_id                                |
+---------------------------------------+
| 2020112122202917203100908203303****** |
+---------------------------------------+

    您还可以根据上述job_id查看异步任务的状态。更多详情，请参见异步提交导入导出任务。