云原生数据仓库AnalyticDB：导出至HDFS

前提条件

• AnalyticDB for MySQL集群需为V3.1.4.4或以上版本。

  说明

  查看湖仓版集群的内核版本，请执行SELECT adb_version();。如需升级内核版本，请联系技术支持。

• 已创建HDFS集群，并在HDFS集群中创建了一个新的文件夹（本示例中文件夹名为hdfs_output_test_csv_data），用于保存导入的AnalyticDB for MySQL数据。

  说明

  使用INSERT OVERWRITE进行导入时，系统会覆盖目标文件夹下的原始文件。为避免原始文件被覆盖，建议在导出时创建一个新的目标文件夹。

• 已在HDFS集群中为AnalyticDB for MySQL集群配置如下服务访问端口：

  • namenode：用于读写文件系统元信息。您可以在fs.defaultFS参数中配置端口号，默认端口号为8020。

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

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

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

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

注意事项

• AnalyticDB for MySQL集群仅支持导出文件格式为CSV和Parquet的数据至HDFS。不支持导出文件格式为ORC的数据。

• AnalyticDB for 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 for MySQL集群。详细操作步骤，请参见连接集群。

2. 创建源数据库。详细操作步骤，请参见创建数据库。

   本示例中，AnalyticDB for 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 for 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 for MySQL集群仅支持导出文件格式为CSV和Parquet的数据至HDFS。不支持导出文件格式为ORC的数据。

   • 创建外表的详细语法说明，请参见创建HDFS外表和创建带分区的HDFS外表。

5. 将源AnalyticDB for MySQL集群中的数据导出至目标HDFS中。

   • 如需通过普通外表导出数据，具体语法，请参见附录1：数据导出语法（普通外表）。

   • 如需通过分区外表导出数据，具体语法，请参见附录2：数据导出语法（分区外表）。

后续步骤

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语法向外表中批量插入数据。如果目标外表中已存在数据，每次写入会先删除外表路径下的全部数据文件，再产生新的HDFS数据文件。

  重要

  • 写入的外表必须保持列个数的完整，不允许指定只写入部分的列。

  • INSERT OVERWRITE为覆盖写入，会覆盖导出目录内已有的历史数据，谨慎使用。

  语法如下。

  INSERT OVERWRITE <target_table> 
  SELECT <col_name> FROM <source_table>;

  示例如下。

  INSERT OVERWRITE hdfs_import_external 
  SELECT col1, col2, col3 FROM adb_hdfs_import_source;

• 方式四：异步执行INSERT OVERWRITE导出数据，语法如下。

  SUBMIT job INSERT OVERWRITE <target_table> 
  SELECT <col_name> FROM <source_table>;

  示例如下。

  SUBMIT JOB INSERT OVERWRITE 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 PARTITION与INSERT INTO PARTITION使用方法相同，但使用INSERT OVERWRITE PARTITION时，会覆盖掉本次执行中涉及到的目标分区中之前已有的数据文件，对于没有新数据写入的分区，则不会清除其中的数据文件。

  语法如下。

  INSERT OVERWRITE <target_table> PARTITION(par1=val1,par2=val2,...)[IF NOT EXISTS] 
  SELECT <col_name> FROM <source_table>;

  重要

  • 写入的外表必须保持列个数的完整，不允许用户指定只写入一部分的列；INSERT OVERWRITE PARTITION为覆盖写入，会覆盖导出目录内已有的历史数据，谨慎使用。

  • IF NOT EXISTS：表示如果外表分区已存在，则不会导出到这个分区。

  示例如下。

  INSERT OVERWRITE 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导出数据，语法如下。

  SUBMIT JOB INSERT OVERWRITE <target_table> 
  SELECT <col_name> FROM <source_table>;

  示例如下。

  SUBMIT JOB INSERT OVERWRITE 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查看异步任务的状态。更多详情，请参见异步提交导入任务。