Sybase IQ数据库之导出、导入研究

导读：本文介绍了Sybase IQ导出、导入数据的相关内容，Sybase IQ是一个强大的即席查询服务器，用户可以使用Sybase IQ来分离决策支持系统和在线事务处理系统。

【TechTarget中国原创】ASIQ一直以其导出导入性能佳而著称，如果能将其用好确实不易。最近本人对此研究了一番，总结如下，仅供参考。

　　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 -ic:\test.sql -o c:\testout.txt

　　test.sql:

　　select * from DSSD_TIME whereTIME_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工具生成批量导出和导入脚本。东软的相关工具使用方法本文不作赘述。