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或TEXT的数据。
  • AnalyticDB MySQL版集群不支持自定义行级写入的INSERT语法,如INSERT INTO VALUESREPLACE INTO VALUES
  • 不支持通过分区外表导出单个文件至HDFS。
  • 通过分区外表导出数据时,数据文件内不包含分区列的数据,分区列的数据信息以HDFS目录的形式展现。

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

    那么当p1=1p2=a的外表分区导出数据时,数据文件相对路径目录为adb_data/p1=1/p2=a/,且外表CSV或Parquet数据文件内不包含p1p2这两列,只包含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),需要同时在创建语句中定义普通列(如uidother)和分区列(如p1p2p3),语法如下。
      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"
      }';
    说明
  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] PARTITIONINSERT 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查看异步任务的状态。更多详情,请参见异步提交导入导出任务