Trafodion UNLOAD导出数据

介绍
Trafodion中使用UNLOAD语句把Trafodion表里面的数据导出到指定的HDFS目录，导出的数据既可以是压缩数据，也可以是未压缩数据。

语法

UNLOAD [WITH [option]...] INTO 'targe-location' SELECT ...FROM source-table ...

语法描述

• option

  • DELIMITER { ‘delimitor-string’ | delimiter-ascii-value}
  • RECORD_SEPARATOR {‘separator-literal’ | separator-ascii-value}
  • NULL_STRING ‘string-literal’
  • PURGEDATA FROM TARGET
  • COMPRESSION GZIP
  • MERGE FILE merged_file_path [OVERWRITE]
  • NO OUTPUT
  • {NEW | EXISTING} SNAPSHOT HAVING SUFFIX ‘string’

• target-location

  • 抽取数据被写入的HDFS目标文件夹的路径，执行UNLOAD用户需要对文件夹有写入权限，若是并行运行UNLOAD，则目标文件夹将产生多个文件。

• DELIMITER { ‘delimitor-string’ | delimiter-ascii-value}

  • 指定列分隔符，默认为”|”，分隔符也可以是一个ASCII值

• RECORD_SEPARATOR {‘separator-literal’ | separator-ascii-value}

  • 指定行间分隔符，默认为换行符”\n”，分隔符也可以是ASCII值

• NULL_STRING ‘string-literal’

  • 指定一个用于表示空值的字符串，默认为空字符串

• PURGEDATA FROM TARGET

  • 导出前将目标HDFS文件夹清空

• COMPRESSION GZIP

  • 导出数据使用GZIP压缩并写入磁盘

• MERGE FILE merged_file_path [OVERWRITE]

  • 将导出的数据合并到指定的路径下面的一个文件中

• NO OUTPUT

  • 不打印UNLODAD过程中的状态消息，默认打印

• {NEW | EXISTING} SNAPSHOT HAVING SUFFIX ‘string’

  • 导出数据期间发起HBase快照扫描，扫描期间，Bulk Unloader从查询计划中获得一份Trafodion表清单，然后将为这些表创建和验证快照

样例

SQL>UNLOAD WITH RECORD_SEPARATOR '\n'
                           DELIMITER '|'
               PURGEDATA FROM TARGET
                           NULL_STRING ' '
     INTO '/bulkload/TESTDATA'
   SELECT EID,
          CAST(CTIME AS VARCHAR(19)),
+>+>              MAC,
                  ADDR,
                  TITLE,
                  CAST(O_C AS VARCHAR(10)),
                  CAST(ENABLE_NET_CTRL AS VARCHAR(10)),
                  CAST(ALARM AS VARCHAR(10)),
                  MODEL,
                  SPECIFICATION,
                  VERSION,
                  CAST(A_A AS NUMERIC(10,2)),
                  CAST(A_LD AS NUMERIC(10,2)),
                  CAST(A_T AS NUMERIC(10,2)),
                  CAST(A_V AS NUMERIC(10,2)),
                  CAST(A_W AS NUMERIC(10,2)),
                  CAST(POWER AS NUMERIC(10,2)),
                  CAST(MXDW AS NUMERIC(10,2)),
                  CAST(MXGG AS NUMERIC(10,2)),
                  CAST(MXGL AS NUMERIC(10,2)),
                  CAST(MXGW AS NUMERIC(10,2)),
                  CAST(MXGY AS NUMERIC(10,2)),
                  CAST(MXLD AS NUMERIC(10,2)),
                  CAST(MXQY AS NUMERIC(10,2)),
                  CONTROL,
                  VISIBILITY
   FROM seabase.test_table;+>+>+>+>+>+>+>+>+>+>+>+>+>+>+>+>+>+>+>+>+>+>+>+>+>+>+>+>+>

UTIL_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------
Task: UNLOAD           Status: Started
Task:  EMPTY TARGET    Status: Started
Task:  EMPTY TARGET    Status: Ended      ET: 00:00:00.022
Task:  EXTRACT         Status: Started
       Rows Processed: 30000000
Task:  EXTRACT         Status: Ended      ET: 00:00:26.283

--- SQL operation complete.