当前位置: 代码迷 >> Sybase >> Sybase IQ 的导入跟导出
  详细解决方案

Sybase IQ 的导入跟导出

热度:10378   发布时间:2013-02-26 00:00:00.0
Sybase IQ 的导入和导出
    Sybase IQ一直以其导出导入性能佳而著称,如果能将其用好确实不易。最近本人对此研究了一番,总结如下,仅供参考。
  1. 前言

  Sybase IQ是一个强大的即席查询服务器。用Sybase IQ来分离决策支持系统(DSS,Decision Support System,READER)和在线事务处理系统(OLTP,OnLine Transaction Processing,WRITER)。目前Sybase IQ在SG186数据中心项目中作为数据仓库数据库得到广泛使用。

  2. 导出

  导出在Sybase IQ也称卸载,总结Sybase IQ卸载方式无外乎以下几种:

  2.1、使用BCP卸载数据

  SybaseIQ支持BCP,可以有两种方式,一种呢是通过OCS提供的BCP,语法和ASE类似,还有一种是通过iq_bcp,语法如下:



usage: iq_bcp [[database_name.]owner.]table_name {in | out} datafile
[-c] [-t field_terminator] [-r row_terminator]
[-U username] [-P password] [-I interfaces_file] [-S server]
[-v] [-A packet size] [-J client character set]
usage: bcp [[db_name.]owner.]table_name[:slice_num] [partition pname] {in | out} [filename]
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n] [-c] [-t field_terminator] [-r row_terminator]
[-U username] [-P password] [-I interfaces_file] [-S server]
[-a display_charset] [-z language] [-v]
[-A packet size] [-J client character set]
[-T text or image size] [-E] [-g id_start_value] [-N] [-X]
[-M LabelName LabelValue] [-labeled]
[-K keytab_file] [-R remote_server_principal] [-C]
[-V [security_options]] [-Z security_mechanism] [-Q] [-Y]
[-x trusted.txt_file]
[--maxconn maximum_connections] [--show-fi] [--hide-vcc]


  这两种方式都需要配置,open client 的接口文件UNIX下是interfaces,与数据库option方式的数据卸载相比较,特点是:一速度比后者慢,但是支持客户端数据卸载。以下给出一个具体的例子:



bcp cmcc.d_district out "D_BRAND.dat" -c -t"|" -T32000 -UDBA -PSQL -Stestiq -Jcp936   或者


  

iq_bcp cmcc.d_district out "D_BRAND.dat" -c -t"|" -UDBA -PSQL -Stestiq -Jcp936


  2.2文本数据方式

  2.2.1、Sqladv方式

  示例如下:

  在cmd状态下:

  c:\>sqladv -Sserver -Uuser -Ppassword -i c:\test.sql -o c:\testout.txt

  但是有几个问题:

  (1)、出来的东东格式不太标准:

  首先有字段名的表头,不知道怎么去掉。

  另外就是各个字段之间的数据采用N个空格分开,不是使用Tab,

  不过,这些可以通过编程解决。

  (2)、执行Bat处理时启动一次SQLADV之后,后面的就执行不过去了,必须先把SQLADV关闭才能执行。

  2.2.2、isql方式

  使用Isql可以实现同样的效果,这样就可直接使用批处理文件,不用Sqladv的方式了。

  c:\>isql -Sserver -Uuser -Ppassword -i c:\test.sql -o c:\testout.txt

  test.sql:

  select * from DSSD_TIME where TIME_YEAR=2001

  go

  2.2.3、output方式



OUTPUT TO filename
[ APPEND ]
[ VERBOSE ]
[ FORMAT output-format ]
[ ESCAPE CHARACTER character ]
[ DELIMITED BY string ]
[ QUOTE string [ ALL ] ]
[ COLUMN WIDTHS (integer , . . . ) ]
[ HEXADECIMAL { ON | OFF | ASIS } ]
[ ENCODING encoding ]
output-format :
ASCII | DBASEII | DBASEIII | EXCEL
| FIXED | FOXPRO | HTML | LOTUS | SQL | XML


  ? QUOTE

  ? ESCAPE CHARACTER character 指定转义字符

  ? 输出数据在当前运行端

  ? 可以指定分割符方式的数据输出,也可以指定每个字段的定长方式.。示

  例如下:



SELECT * FROM "DBA"."V_ICP_ID";
OUTPUT TO 'c:\temp\V_ICP_ID.out'
DELIMITED BY '|'
FORMAT ASCII
quote '';


  输出结果在命令执行端。

  > # <文件名> 输出执行结果

  > & <文件名> 输出到文件包括错误信息和执行信息,执行结果

  例如:

  SELECT *

  FROM employee

  >& empfile

  或

  select * from psdss_dm.AA_RESULT ># D:\tmpda\AA_RESULT.txt;

  2.3、option方式

  此方式可以导出二进制数据:



  set temporary option temp_extract_name1='/apps/performance/IAC' ; --设置输出路径
  set temporary option Temp_Extract_Column_Delimiter='|'; --设置分隔符
  commit
  select * from P_ABIS_IAC --执行查找
  commit
  set temporary option temp_extract_name1='' --重新设置到控制台
  commit


  经过研究,并在东软导出工具基础上加以改造实现了批量表数据导出,填补了东软工具不能批量表导出的不足。

  2.4导出方式比较

  (1)Option是二进制方式,此种方式速度最快,在导入时完全避免了分隔符和空值等问题。

  (2)iq_bcp方式支持客户端导出。

  (3)其他几种方式是文本方式,可以灵活定制文本导出格式,只能在服务器端导出。

  3.导入

  3.1、load

  load语句的格式:



  LOAD [ INTO ] TABLE [ owner.]table-name [ ( column-name, . . . ) ]
  FROM filename
  [ load-option . . . ]
  [ statistics-limitation-options ]
  load-option :
  CHECK CONSTRAINTS { ON | OFF }
  | COMPUTES { ON | OFF }
  | DEFAULTS { ON | OFF }
  | DELIMITED BY string
  | ESCAPE CHARACTER character
  | ESCAPES { ON | OFF }
  | FORMAT { ASCII | BCP }
  | HEXADECIMAL {ON | OFF}
  | ORDER {ON | OFF}
  | PCTFREE percent-free-space
  | QUOTES { ON | OFF }
  | SKIP integer
  | STRIP { ON | OFF }
  | WITH CHECKPOINT { ON | OFF }


  ? 如果字段名未出现在字段列表中,则填充NULL、0、空、或者DEFAULT;

  存在于输入文件中的字段可以用“filler()”.忽略

  ? DEFAULTS { ON | OFF } 为ON则字段取缺省值。否则取NULL

  ? QUOTES { ON | OFF } 缺省为ON ,字段定界符为‘’或者 “”

  ? DELIMITED BY 选项: 可以单个字符,最多255个字符,例如:制表符

  号作分割符号:

  ...DELIMITED BY ’nx09’

  ? SKIP n 忽略前n条记录;

  ? STRIP ON|OFF 尾空格插入前是否截取;

  ? WITH CHECKPOINT ON|OFF 缺省为 OFF, 如果设置为ON则,命令完成

  后,执行CHECKPOINT 操作。

  下面是从一个文本文件load到表F_INN_IA_DAILY_SUM中的语句:



  set temporary option date_order=YMD;
  Load Table F_INN_IA_DAILY_SUM
  (
  ORG_SID '+|+',
  DEAL_SID '+|+',
  ALL_TIME_SID '+|+',
  R_COUNT_DIM_SID '+|+',
  T_TAX_STOR_COST '+|+',
  T_STOR_COST '+|+',
  T_STOR_SUM '+|+',
  CREATED_DT 'X0A'
  )
  From '/load_data/F_Inn_IA_Daily_Sum.txt'
  ESCAPES OFF
  QUOTES OFF
  NOTIFY 100000
  WITH CHECKPOINT ON;
  COMMIT


  其中+|+是字段的分隔符,X0A是记录的分隔符,即回车(文本文件中)。

  load中注意的问题:

  1、load的文本文件要在iq同一台机子上。

  2、load的时候经常出现类型转换错误,所以要注意字段类型和长度一致。

  3、最后一个字段有空值,也会出现字符转换错误,但真正的原因是最后一个字段有空值。

  对最后一个字段空值的load语句:

  数据:

  256|59|水资源费费展x00费|5903|自来水厂自来水厂保险x00船x00船x00x00|0|2006-12-01 02:15:19.0|2006-12-02 02:15:19.0

  257|59|水资源费费展x00费|5904|自来水厂取地自来保险x00船x00船x00x00|0|2006-12-01 02:15:19.0|2006-12-02 02:15:19.0

  258|59|水资源费费展x00费|5905|工业生产取地表水保险x00船x00船x00x00|0|2006-12-01 02:15:19.0|为空

  259|59|水资源费费展x00费|5906|工业生产取地下水保险x00船x00船x00x00|0|2006-12-01 02:15:19.0|为空

  因为有最后是有空值的,在装载的时候装载不进去,要指出空值的类型。load语句如下:



  LOAD TABLE d_tax_kind
  (TAX_KIND_KEY '|',
  TAX_KIND_CODE '|',
  TAX_KIND_NAME '|',
  TAX_ITEM_CODE '|',
  TAX_ITEM_NAME '|',
  TAX_FLAG '|',
  EFFECTIVE_DATE '|' null(''),
  END_DATE 'x0dx0a' null('')
  )
  FROM 'c:d_tax_kind.out'
  ESCAPES OFF
  QUOTES Off
  format ASCII
  WITH CHECKPOINT ON;


  需要注意的问题:

  ASCII变长数据加载时,字段分割符号和行分隔符一般需要描述。

  3.2 insert 数据加载

  语法:



  INSERT [ INTO ] [ owner.]table-name [ ( column-name [, ...] ) ]
  ... VALUES ( expression ... )
  Syntax 2
  INSERT [ INTO ] [ owner.]table-name [ ( column-name [, ...] ) ]
  ... insert-load-options
  ... select-statement
  Syntax 3
  INSERT [ INTO ] [ owner.]table-name [ ( column-name [, ...] ) ]
  ... insert-load-options
  [ LOCATION 'servername.dbname '
  [ ENCRYPTED PASSWORD ][ PACKETSIZE packet-size ] ]
  ... {select-statement}


  3.3、特殊的INSERT

  支持部分加载,从其他远程服务器数据加载。



  INSERT INTO lineitem
  (l_shipdate, l_orderkey)
  LOCATION 'detroit.asiqdb'
  PACKETSIZE 512
  { SELECT l_shipdate, l_orderkey
  FROM lineitem }


  通过LOCATION 'detroit.asiqdb'指定Open Client配置的服务器名和数据

  库名称。

  3.4、管道方式的数据加载

  利用操作系统管道功能进行数据加载,减少磁盘I/O 使数据加载更加高效。

  3.4.1、管道数据加载命令脚本



  #!/bin/ksh
  FILE=$1
  if [ -z "${FILE}" ]
  then
  echo ""
  echo "USAGE: $0 SQL_file_to_load"
  echo ""
  exit
  fi
  if [ -r CdrPipe ]
  then
  rm CdrPipe
  fi
  mknod /work_tmp/test_pipe/CdrPipe p
  nohup dbisqlc -c
  "eng=iq126;uid=DBA;pwd=SQL;dbn=iq126;links=tcpip{host=127.0.0.1:2660}" -
  q ${FILE} > ${FILE}.out 2>&1 &
  cat /work_tmp/test_pipe/rtqd_split.aa > /work_tmp/test_pipe/CdrPipe &
  echo "Input from: ${FILE}"
  echo "Output to : ${FILE}.out"


  4、总结

  综上所述,导出方式建议使用option方式,导入方式使用load在进行批量导出许多表时,可以考虑使用东软的export和load工具生成批量导出和导入脚本。东软的相关工具使用方法本文不作赘述。