Oracle 导出CSV工具-sqluldr2

2024年 8月 1日 105.7k 0

背景

最近需要协助运营部分定期出csv格式数据,每次用客户端导出很麻烦,于是研究了下sqluldr2工具,写了个定时导出的脚本。

下载:

sqluldr2_linux64_10204.zip

目录规划

[oracle@localhost~]$ tree csv_dat/
csv_dat/
|-- csv_data # 存放CSV文件
| `-- t2.csv
|-- csv_log # 存放日志
| `-- t2.log
|-- csv_sql # 存放SQL文本
| `-- t2.sql
`-- sqluldr2 # 存放:sqluldr2_linux64_10204.bin
`-- sqluldr2 # mv sqluldr2_linux64_10204.bin sqluldr2 & chmod 775 sqluldr2/sqluldr2

查看参数

[oracle@localhost sqluldr2]$ ./sqluldr2 -help

SQL*UnLoader: Fast Oracle Text Unloader (GZIP, Parallel), Release 4.0.1
(@) Copyright Lou Fangxin (AnySQL.net) 2004 - 2010, all rights reserved.

License: Free for non-commercial useage, else 100 USD per server.

Usage: SQLULDR2 keyword=value [,keyword=value,...]

Valid Keywords:
user = username/password@tnsname
sql = SQL file name
query = select statement
field = separator string between fields
record = separator string between records
rows = print progress for every given rows (default, 1000000)
file = output file name(default: uldrdata.txt)
log = log file name, prefix with + to append mode
fast = auto tuning the session level parameters(YES)
text = output type (MYSQL, CSV, MYSQLINS, ORACLEINS, FORM, SEARCH).
charset = character set name of the target database.
ncharset= national character set name of the target database.
parfile = read command option from parameter file

for field and record, you can use '0x' to specify hex character code,
\r=0x0d \n=0x0a |=0x7c ,=0x2c, \t=0x09, :=0x3a, #=0x23, "=0x22 '=0x27

参数介绍示例

field-指定分隔符

  • 默认是逗号分隔符

[oracle@localhost sqluldr2]$ ./sqluldr2 user=scott/tiger query="select * from t2" file=/home/oracle/csv_dat/csv_data/t2.csv log=/home/oracle/csv_dat/csv_log/t2.log
[oracle@localhost csv_data]$ cat t2.csv
53,B,100,status:B,2024-03-26 13:37:28.000000
54,B,123,status:B,2023-01-26 13:37:28.000000

  • 指定分隔符‘;’

[oracle@localhost sqluldr2]$ ./sqluldr2 user=scott/tiger query="select * from t2" field=';' file=/home/oracle/csv_dat/csv_data/t2.csv log=/home/oracle/csv_dat/csv_log/t2.log
[oracle@localhost csv_data]$ cat t2.csv
53;B;100;status:B;2024-03-26 13:37:28.000000
54;B;123;status:B;2023-01-26 13:37:28.000000

query-SQL调用

  • 直接写表名

[oracle@localhost sqluldr2]$ ./sqluldr2 user=scott/tiger query="t2" file=/home/oracle/csv_dat/csv_data/t2.csv log=/home/oracle/csv_dat/csv_log/t2.log
- 默认生成ctl 控制文件
[oracle@localhost sqluldr2]$ ls
sqluldr2 t2_sqlldr.ctl
[oracle@localhost sqluldr2]$ cat t2_sqlldr.ctl
--
-- SQL*UnLoader: Fast Oracle Text Unloader (GZIP), Release 3.0.1
-- (@) Copyright Lou Fangxin (AnySQL.net) 2004 - 2010, all rights reserved.
--
-- CREATE TABLE t2 (
-- ID NUMBER(16),
-- STATUS VARCHAR2(2),
-- AMT NUMBER(5,2),
-- COMMENTS VARCHAR2(1000),
-- CREATE_TIME TIMESTAMP
-- );
--
OPTIONS(BINDSIZE=2097152,READSIZE=2097152,ERRORS=-1,ROWS=50000)
LOAD DATA
INFILE '/home/oracle/csv_dat/csv_data/t2.csv' "STR X'0a'"
INSERT INTO TABLE t2
FIELDS TERMINATED BY X'2c' TRAILING NULLCOLS
(
"ID" CHAR(18) NULLIF "ID"=BLANKS,
"STATUS" CHAR(2) NULLIF "STATUS"=BLANKS,
"AMT" CHAR(8) NULLIF "AMT"=BLANKS,
"COMMENTS" CHAR(1000) NULLIF "COMMENTS"=BLANKS,
"CREATE_TIME" TIMESTAMP "YYYY-MM-DD HH24:MI:SSXFF" NULLIF "CREATE_TIME"=BLANKS
)

[oracle@localhost csv_data]$ cat t2.csv
53,B,100,status:B,2024-03-26 13:37:28.000000
54,B,123,status:B,2023-01-26 13:37:28.000000

  • 指定控制文件:control

[oracle@localhost sqluldr2]$ ./sqluldr2 user=scott/tiger query="t2" control=/home/oracle/csv_dat/csv_data/t2.ctl file=/home/oracle/csv_dat/csv_data/t2.csv log=/home/oracle/csv_dat/csv_log/t2.log

[oracle@localhost sqluldr2]$ ls
sqluldr2

[oracle@localhost csv_data]$ ls
t2.csv t2.ctl

  • 直接写SQL文件见field 示例,在此不演示

head=yes- 输出表头

[oracle@localhost sqluldr2]$ ./sqluldr2 user=scott/tiger query="select * from t2" head=yes field=';' file=/home/oracle/csv_dat/csv_data/t2.csv log=/home/oracle/csv_dat/csv_log/t2.log

[oracle@localhost csv_data]$ cat t2.csv
ID;STATUS;AMT;COMMENTS;CREATE_TIME
53;B;100;status:B;2024-03-26 13:37:28.000000
54;B;123;status:B;2023-01-26 13:37:28.000000

SQL - 指定SQL 文件本

[oracle@localhost sqluldr2]$ ./sqluldr2 user=scott/tiger sql=/home/oracle/csv_dat/csv_sql/t2.sql field=';' file=/home/oracle/csv_dat/csv_data/t2.csv log=/home/oracle/csv_dat/csv_log/t2.log

[oracle@localhost csv_sql]$ cat t2.sql
select * from t2

[oracle@localhost csv_data]$ cat t2.csv
53;B;100;status:B;2024-03-26 13:37:28.000000
54;B;123;status:B;2023-01-26 13:37:28.000000

log - 日志输出

  • 指定日志输出,在此不再演示;
  • log=+1.log 日志追加

[oracle@localhost sqluldr2]$ ./sqluldr2 user=scott/tiger query="select * from t2" head=yes field=';' file=/home/oracle/csv_dat/csv_data/t2.csv log=+/home/oracle/csv_dat/csv_log/t2.log

[oracle@localhost csv_log]$ cat t2.log
0 rows exported at 2024-07-23 15:38:46, size 0 MB.
2 rows exported at 2024-07-23 15:38:46, size 0 MB.
output file /home/oracle/csv_dat/csv_data/t2.csv closed at 2 rows, size 0 MB.
0 rows exported at 2024-07-23 15:41:16, size 0 MB.
2 rows exported at 2024-07-23 15:41:16, size 0 MB.
output file /home/oracle/csv_dat/csv_data/t2.csv closed at 2 rows, size 0 MB.

数据切片: rows 与 batch=yes 使用才会有效果

  • rows 单独使用

[oracle@localhost sqluldr2]$ ./sqluldr2 user=scott/tiger query="select * from t2 where rownum

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
社区版oceanbase安装
ETL数据集成丨快速将MySQL数据迁移至Doris数据库
27 期 | 死锁(3)解决死锁

发布评论