Oracle-SQLLDR加载TPC-DS数据

一、测试环境

名称	描述
操作系统	Red Hat Enterprise Linux Server release 7.9 (Maipo)
oracle版本	Release 11.2.0.1.0 - Production
TPC-DS工具版本	V3.2.0

二、安装TPC-DS

1、TPC-DS工具下载地址
（1）TPC-DS工具官方下载地址
（2）百度云盘链接

链接：https://pan.baidu.com/s/1dkfoDX_D1p4dAQkWKcPoqA 
提取码：ucah 

2、解压工具包，进入到DSGen-software-code-3.2.0rc1/tools目录。

3、执行make命令。

三、生成测试数据

（1）创建数据文件目录/opt/tpcds_data。

（2）生成测试数据命令：

./dsdgen -sc 1 -dir /opt/tpcds_data -TERMINATE N

参数介绍：

General Options
===============
ABREVIATION =  <s>       -- build table with abreviation <s>
DIR =  <s>               -- generate tables in directory <s>
HELP =  <n>              -- display this message
PARAMS =  <s>            -- read parameters from file <s>
QUIET =  [Y|N]           -- disable all output to stdout/stderr
SCALE =  <n>             -- volume of data to generate in GB
TABLE =  <s>             -- build only table <s>
UPDATE =  <n>            -- generate update data set <n>
VERBOSE =  [Y|N]         -- enable verbose output
PARALLEL =  <n>          -- build data in <n> separate chunks
CHILD =  <n>             -- generate <n>th chunk of the parallelized data
RELEASE =  [Y|N]         -- display the release information
_FILTER =  [Y|N]         -- output data to stdout
VALIDATE =  [Y|N]        -- produce rows for data validation

Advanced Options
===============
DELIMITER =  <s>         -- use <s> as output field separator
DISTRIBUTIONS =  <s>     -- read distributions from file <s>
FORCE =  [Y|N]           -- over-write data files without prompting
SUFFIX =  <s>            -- use <s> as output file suffix
TERMINATE =  [Y|N]       -- end each record with a field delimiter
VCOUNT =  <n>            -- set number of validation rows to be produced
VSUFFIX =  <s>           -- set file suffix for data validation
RNGSEED =  <n>           -- set RNG seed

（3）查看/opt/tpcds_data下是否生成数据文件

-rwxrwxrwx 1 root root      1885 Apr  8 11:27 call_center.dat
-rwxrwxrwx 1 root root   1620074 Apr  8 11:27 catalog_page.dat
-rwxrwxrwx 1 root root  21234304 Apr  8 11:27 catalog_returns.dat
-rwxrwxrwx 1 root root 294468836 Apr  8 11:27 catalog_sales.dat
-rwxrwxrwx 1 root root   5452165 Apr  8 11:27 customer_address.dat
-rwxrwxrwx 1 root root  13109372 Apr  8 11:27 customer.dat
-rwxrwxrwx 1 root root  78739296 Apr  8 11:28 customer_demographics.dat
-rwxrwxrwx 1 root root  10244389 Apr  8 11:28 date_dim.dat
-rwxrwxrwx 1 root root        64 Apr  8 11:29 dbgen_version.dat
-rwxrwxrwx 1 root root    144453 Apr  8 11:28 household_demographics.dat
-rwxrwxrwx 1 root root       308 Apr  8 11:28 income_band.dat
-rwxrwxrwx 1 root root 224675139 Apr  8 11:28 inventory.dat
-rwxrwxrwx 1 root root   5033899 Apr  8 11:28 item.dat
-rwxrwxrwx 1 root root     36933 Apr  8 11:28 promotion.dat
-rwxrwxrwx 1 root root      1304 Apr  8 11:28 reason.dat
-rwxrwxrwx 1 root root      1093 Apr  8 11:28 ship_mode.dat
-rwxrwxrwx 1 root root      3143 Apr  8 11:28 store.dat
-rwxrwxrwx 1 root root  32422491 Apr  8 11:28 store_returns.dat
-rwxrwxrwx 1 root root 385565005 Apr  8 11:28 store_sales.dat
-rwxrwxrwx 1 root root   5021380 Apr  8 11:29 time_dim.dat
-rwxrwxrwx 1 root root       580 Apr  8 11:29 warehouse.dat
-rwxrwxrwx 1 root root      5716 Apr  8 11:29 web_page.dat
-rwxrwxrwx 1 root root   9734473 Apr  8 11:29 web_returns.dat
-rwxrwxrwx 1 root root 146158290 Apr  8 11:29 web_sales.dat
-rwxrwxrwx 1 root root      8741 Apr  8 15:28 web_site.dat

四、Oracle创建TPC-DS相关表

sqlplus czg/123456789 @/opt/DSGen-software-code-3.2.0rc1/tools/tpcds.sql

五、SQLLDR装载数据

由于数据表较多我们这里只演示web_site表的数据装载。

（1）编辑控制文件web_site.ctl

load data
infile '/opt/tpcds_data/web_site.dat'
INTO TABLE web_site
truncate
fields terminated by "|"
optionally enclosed by '"'
trailing nullcols
(WEB_SITE_SK,
 WEB_SITE_ID,
 WEB_REC_START_DATE "to_date(:WEB_REC_START_DATE,'YYYY-MM-DD')",
 WEB_REC_END_DATE "to_date(:WEB_REC_START_DATE,'YYYY-MM-DD')",
 WEB_NAME,
 WEB_OPEN_DATE_SK,
 WEB_CLOSE_DATE_SK,
 WEB_CLASS,
 WEB_MANAGER,
 WEB_MKT_ID,
 WEB_MKT_CLASS,
 WEB_MKT_DESC,
 WEB_MARKET_MANAGER,
 WEB_COMPANY_ID,
 WEB_COMPANY_NAME,
 WEB_STREET_NUMBER,
 WEB_STREET_NAME,
 WEB_STREET_TYPE,
 WEB_SUITE_NUMBER,
 WEB_CITY,
 WEB_COUNTY,
 WEB_STATE,
  WEB_ZIP,
 WEB_COUNTRY,
 WEB_GMT_OFFSET,
 WEB_TAX_PERCENTAGE)

（2）SQLLDR数据加载

[oracle@localhost ctl]$ sqlldr userid=czg/123456789 control=/home/oracle/ctl/web_site.ctl bad=/home/oracle/bad_file/bad.txt log=/home/oracle/log_file/log.txt


SQL*Loader: Release 11.2.0.1.0 - Production on Fri Apr 8 15:29:37 2022

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 30

（3）查看数据是否装载成功

SQL> select count(*) from web_site;                  

  COUNT(*)
----------
	30