（转载）IQ 16.0 SP02起支持从压缩文件直接装载数据到表中

参考文档：

http://m.blog.chinaunix.net/uid-16765068-id-4405877.html
http://www.cnblogs.com/lichmama/p/4103048.html

 

大致过程：

/**
创建测试视图
**/
CREATE VIEW BCPVIEW24 AS
SELECT
  TIMEID                   ,
  SYSTEM_ID                ,
  MSISDN                   ,
  CITY_ID                  ,
  RECORDTYPE               ,
  NETWORKINITIATION        ,
  SERVEDIMSI               ,
  SERVEDIMEI               ,
  SGSNADDRESS              ,
  ROUTINGAREA              ,
  LOCATIONAREACODE         ,
  CELLIDENTITY             ,
  CHARGINGID               ,
  GGSNADDRESSUSED          ,
  ACCESSPOINTNAMENI        ,
  PDPTYPE                  ,
  SERVEDPDPADDRESS         ,
  DATAVOLUMEGPRSUPLINK     ,
  DATAVOLUMEGPRSDOWNLINK   ,
  RECORDOPENINGTIME        ,
  DURATION                 ,
  SGSNCHANGE               ,
  CAUSEFORRECCLOSING       ,
  DIAGNOSTICS              ,
  RECORDSEQUENCENUMBER     ,
  NODEID                   ,
  ACCESSPOINTNAMEOI        ,
  SERVED_MSISDN            ,
  CHARGING_CHARACTERISTICS ,
  RATTYPE                  ,
  DYN_ADDR_FLAG            ,
  SGSN_PLMN_ID             ,
  FILE_NAME                ,
  FILE_CHANGE_TIME         ,
  SGSN_CITY                ,
  RATEVOLUMEGPRSUPLINK     ,
  RATEVOLUMEGPRSDOWNLINK   ,
  LOCALNUMBER              
FROM TABLE_CANNOT_SHOW_24;

/**
导出数据到文本，并压缩
**/
bcp datamark.eastcom.BCPVIEW24 out SGSN24.txt -t'|' -c -Ueastcom -PEastCom\!\$ -Sdatamark
gzip SGSN24.txt

/**
LOAD加载压缩文件
**/
LOAD TABLE TABLE_CANNOT_SHOW_31
(
  TIMEID                   ,
  SYSTEM_ID                ,
  MSISDN                   ,
  CITY_ID                  ,
  RECORDTYPE               ,
  NETWORKINITIATION        ,
  SERVEDIMSI               ,
  SERVEDIMEI               ,
  SGSNADDRESS              ,
  ROUTINGAREA              ,
  LOCATIONAREACODE         ,
  CELLIDENTITY             ,
  CHARGINGID               ,
  GGSNADDRESSUSED          ,
  ACCESSPOINTNAMENI        ,
  PDPTYPE                  ,
  SERVEDPDPADDRESS         ,
  DATAVOLUMEGPRSUPLINK     ,
  DATAVOLUMEGPRSDOWNLINK   ,
  RECORDOPENINGTIME        ,
  DURATION                 ,
  SGSNCHANGE               ,
  CAUSEFORRECCLOSING       ,
  DIAGNOSTICS              ,
  RECORDSEQUENCENUMBER     ,
  NODEID                   ,
  ACCESSPOINTNAMEOI        ,
  SERVED_MSISDN            ,
  CHARGING_CHARACTERISTICS ,
  RATTYPE                  ,
  DYN_ADDR_FLAG            ,
  SGSN_PLMN_ID             ,
  FILE_NAME                ,
  FILE_CHANGE_TIME         ,
  SGSN_CITY                ,
  RATEVOLUMEGPRSUPLINK     ,
  RATEVOLUMEGPRSDOWNLINK   ,
  LOCALNUMBER              
)
FROM '/srcdata/bcp_export/SGSN24.txt.gz'
FORMAT bcp
ESCAPES OFF
QUOTES OFF
DELIMITED BY '|'
WITH CHECKPOINT ON;
COMMIT;

 

使用须知：

LOAD的时候FORMAT要根据实际情况来，比如我使用bcp导出数据，那么LOAD语句里就指定FORMAT为bcp。如果使用dbisql或其他工具导出，则需要指定FORMAT为ASCII等。

另外，bcp导出默认以'\n'为换行符，所以LOAD时无需指定ROW DELIMITED。