Differences

This shows you the differences between two versions of the page.

--- public:tpc-ds_benchmark [2012/12/14 16:25] – created kohofer
+++ public:tpc-ds_benchmark [2019/01/16 10:03] (current) – external edit 127.0.0.1
@@ Line 3: / Line 3: @@
 Commands:
-Create Oracle user tpcds
+Create Oracle user TPCDS
-  SQL> create user tpcds with password 'ffgfh';
+   SQL> CREATE USER "TPCDS" PROFILE "DEFAULT" IDENTIFIED BY "*******" DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP" QUOTA UNLIMITED ON "USERS" ACCOUNT UNLOCK GRANT CREATE TABLE TO "TPCDS" GRANT "CONNECT" TO "TPCDS";
+Get DSGen and unpack
+  * mkdir /tmp/dsgen
+  * cd /tmp/dsgen
+  * wget https://www.inf.unibz.it/wiki/_media/public/dsgen_v1.1.0.zip
+  * unzip dsgen_v1.1.0.zip
+Compile/build binaries
+  * cp tools/Makefile.suite tools/Makefile
+  * cat tools/Makefile | grep ^OS
+  * Result should be: OS	=	LINUX
+  * cd tools
+  * make
 Generate load data
   * mkdir /tmp/tpc
-  * dsdgen -scale 1 -dir /tmp/tpc
+  * ./dsdgen -scale 1 -dir /tmp/tpc
+Import tables into Oracle
+  * su - oracle
+  * cd /tmp/dsgen/tools
+  * sqlplus tpcds/* @tpcds.sql
+  * sqlplus tpcds/* @tpcds_source.sql
 Load data
   * su - oracle
-  * mkdir tpcds
+  * mkdir tpc-ds
+  * cd tpc-ds
+Prepare control files:
+  * vi call_center.ctl
+{(xssnipper>,1, call_center.ctl slide,
+load data
+infile '/tmp/tpc/call_center.dat'
+into table call_center
+fields terminated by '|'
+( CC_CALL_CENTER_SK, CC_CALL_CENTER_ID, CC_REC_START_DATE date "YYYY-MM-DD", CC_
+REC_END_DATE date "YYYY-MM-DD", CC_CLOSED_DATE_SK, CC_OPEN_DATE_SK, CC_NAME, CC_
+CLASS, CC_EMPLOYEES, CC_SQ_FT, CC_HOURS, CC_MANAGER, CC_MKT_ID, CC_MKT_CLASS, CC
+_MKT_DESC, CC_MARKET_MANAGER, CC_DIVISION, CC_DIVISION_NAME, CC_COMPANY, CC_COMP
+ANY_NAME, CC_STREET_NUMBER, CC_STREET_NAME, CC_STREET_TYPE, CC_SUITE_NUMBER, CC_
+CITY, CC_COUNTY, CC_STATE, CC_ZIP, CC_COUNTRY, CC_GMT_OFFSET, CC_TAX_PERCENTAGE
+)
+)}
+  * vi catalog_page.ctl
+{(xssnipper>,1, catalog_page.ctl slide,
+load data
+infile '/tmp/tpc/catalog_page.dat'
+into table catalog_page
+fields terminated by '|'
+( CP_CATALOG_PAGE_SK, CP_CATALOG_PAGE_ID, CP_START_DATE_SK, CP_END_DATE_SK, CP_D
+EPARTMENT, CP_CATALOG_NUMBER, CP_CATALOG_PAGE_NUMBER, CP_DESCRIPTION,
+CP_TYPE )
+)}
+  * vi catalog_returns.ctl
+{(xssnipper>,1, catalog_page.ctl slide,
+load data
+infile '/tmp/tpc/catalog_returns.dat'
+into table catalog_returns
+fields terminated by '|'
+( CR_RETURNED_DATE_SK, CR_RETURNED_TIME_SK, CR_ITEM_SK, CR_REFUNDED_CUSTOMER_SK, CR_REFUNDED_CDEMO_SK, CR_REFUNDED_HDEMO_SK, CR_REFUNDED_ADDR_SK, CR_RETURNING_CUSTOMER_SK, CR_RETURNING_CDEMO_SK, CR_RETURNING_HDEMO_SK, CR_RETURNING_ADDR_SK, CR_CALL_CENTER_SK, CR_CATALOG_PAGE_SK, CR_SHIP_MODE_SK, CR_WAREHOUSE_SK, CR_REASON_SK, CR_ORDER_NUMBER, CR_RETURN_QUANTITY, CR_RETURN_AMOUNT, CR_RETURN_TAX, CR_RETURN_AMT_INC_TAX, CR_FEE, CR_RETURN_SHIP_COST, CR_REFUNDED_CASH, CR_REVERSED_CHARGE, CR_STORE_CREDIT, CR_NET_LOSS )
+)}
+  * vi catalog_sales.ctl
+{(xssnipper>,1, catalog_sales.ctl slide,
+load data
+infile '/tmp/tpc/catalog_sales.dat'
+into table catalog_sales
+fields terminated by '|'
+( CS_SOLD_DATE_SK, CS_SOLD_TIME_SK, CS_SHIP_DATE_SK, CS_BILL_CUSTOMER_SK, CS_BILL_CDEMO_SK, CS_BILL_HDEMO_SK, CS_BILL_ADDR_SK, CS_SHIP_CUSTOMER_SK, CS_SHIP_CDEMO_SK, CS_SHIP_HDEMO_SK, CS_SHIP_ADDR_SK, CS_CALL_CENTER_SK, CS_CATALOG_PAGE_SK, CS_SHIP_MODE_SK, CS_WAREHOUSE_SK, CS_ITEM_SK, CS_PROMO_SK, CS_ORDER_NUMBER, CS_QUANTITY, CS_WHOLESALE_COST, CS_LIST_PRICE, CS_SALES_PRICE, CS_EXT_DISCOUNT_AMT, CS_EXT_SALES_PRICE, CS_EXT_WHOLESALE_COST, CS_EXT_LIST_PRICE, CS_EXT_TAX, CS_COUPON_AMT, CS_EXT_SHIP_COST, CS_NET_PAID, CS_NET_PAID_INC_TAX, CS_NET_PAID_INC_SHIP, CS_NET_PAID_INC_SHIP_TAX, CS_NET_PROFIT )
+)}
+  * vi customer_address.ctl
+{(xssnipper>,1, customer_address.ctl slide,
+load data
+infile '/tmp/tpc/customer_address.dat'
+into table customer_address
+fields terminated by '|'
+( CA_ADDRESS_SK,CA_ADDRESS_ID,CA_STREET_NUMBER,CA_STREET_NAME,CA_STREET_TYPE,CA_SUITE_NUMBER,CA_CITY,CA_COUNTY,CA_STATE,CA_ZIP,CA_COUNTRY,CA_GMT_OFFSET,CA_LOCATION_TYPE )
+)}
+  * vi customer.ctl
+{(xssnipper>,1, customer.ctl slide,
+load data
+infile '/tmp/tpc/customer.dat'
+into table customer
+fields terminated by '|'
+( C_CUSTOMER_SK, C_CUSTOMER_ID, C_CURRENT_CDEMO_SK, C_CURRENT_HDEMO_SK, C_CURRENT_ADDR_SK, C_FIRST_SHIPTO_DATE_SK, C_FIRST_SALES_DATE_SK, C_SALUTATION, C_FIRST_NAME, C_LAST_NAME, C_PREFERRED_CUST_FLAG, C_BIRTH_DAY, C_BIRTH_MONTH, C_BIRTH_YEAR, C_BIRTH_COUNTRY, C_LOGIN, C_EMAIL_ADDRESS, C_LAST_REVIEW_DATE )
+)}
+  * vi customer_demographics.ctl
+{(xssnipper>,1, customer_demographics.ctl slide,
+load data
+infile '/tmp/tpc/customer_demographics.dat'
+into table customer_demographics
+fields terminated by '|'
+( CD_DEMO_SK,CD_GENDER,CD_MARITAL_STATUS,CD_EDUCATION_STATUS,CD_PURCHASE_ESTIMATE,CD_CREDIT_RATING,CD_DEP_COUNT,CD_DEP_EMPLOYED_COUNT,CD_DEP_COLLEGE_COUNT )
+)}
+  * vi date_dim.ctl
+{(xssnipper>,1, date_dim.ctl slide,
+load data
+infile '/tmp/tpc/date_dim.dat'
+into table date_dim
+fields terminated by '|'
+( D_DATE_SK,D_DATE_ID,D_DATE date "YYYY-MM-DD",D_MONTH_SEQ,D_WEEK_SEQ,D_QUARTER_SEQ,D_YEAR,D_DOW,D_MOY,D_DOM,D_QOY,D_FY_YEAR,D_FY_QUARTER_SEQ,D_FY_WEEK_SEQ,D_DAY_NAME,D_QUARTER_NAME,D_HOLIDAY,D_WEEKEND,D_FOLLOWING_HOLIDAY,D_FIRST_DOM,D_LAST_DOM,D_SAME_DAY_LY,D_SAME_DAY_LQ,D_CURRENT_DAY,D_CURRENT_WEEK,D_CURRENT_MONTH,D_CURRENT_QUARTER,D_CURRENT_YEAR )
+)}
+  * vi dbgen_version.ctl
+{(xssnipper>,1, dbgen_version.ctl slide,
+load data
+infile '/tmp/tpc/dbgen_version.dat'
+into table dbgen_version
+fields terminated by '|'
+( DV_VERSION, DV_CREATE_DATE, DV_CREATE_TIME, DV_CMDLINE_ARGS )
+)}
+  * vi household_demographics.ctl
+{(xssnipper>,1, household_demographics.ctl slide,
+load data
+infile '/tmp/tpc/household_demographics.dat'
+into table household_demographics
+fields terminated by '|'
+( HD_DEMO_SK, HD_INCOME_BAND_SK, HD_BUY_POTENTIAL, HD_DEP_COUNT, HD_VEHICLE_COUNT )
+)}
+  * vi income_band.ctl
+{(xssnipper>,1, income_band.ctl slide,
+load data
+infile '/tmp/tpc/income_band.dat'
+into table income_band
+fields terminated by '|'
+(IB_INCOME_BAND_SK, IB_LOWER_BOUND, IB_UPPER_BOUND )
+)}
+  * vi inventory.ctl
+{(xssnipper>,1, inventory.ctl slide,
+load data
+infile '/tmp/tpc/inventory.dat'
+into table inventory
+fields terminated by '|'
+( INV_DATE_SK, INV_ITEM_SK, INV_WAREHOUSE_SK, INV_QUANTITY_ON_HAND )
+load data
+infile '/tmp/tpc/item.dat'
+into table item
+fields terminated by '|'
+( I_ITEM_SK, I_ITEM_ID, I_REC_START_DATE date "YYYY-MM-DD", I_REC_END_DATE date "YYYY-MM-DD", I_ITEM_DESC, I_CURRENT_PRICE, I_WHOLESALE_COST, I_BRAND_ID, I_BRAND, I_CLASS_ID, I_CLASS, I_CATEGORY_ID, I_CATEGORY, I_MANUFACT_ID, I_MANUFACT, I_SIZE, I_FORMULATION, I_COLOR, I_UNITS, I_CONTAINER, I_MANAGER_ID, I_PRODUCT_NAME )
+)}
+  * vi item.ctl
+{(xssnipper>,1, item.ctl slide,
+load data
+infile '/tmp/tpc/item.dat'
+into table item
+fields terminated by '|'
+( I_ITEM_SK, I_ITEM_ID, I_REC_START_DATE date "YYYY-MM-DD", I_REC_END_DATE date "YYYY-MM-DD", I_ITEM_DESC, I_CURRENT_PRICE, I_WHOLESALE_COST, I_BRAND_ID, I_BRAND, I_CLASS_ID, I_CLASS, I_CATEGORY_ID, I_CATEGORY, I_MANUFACT_ID, I_MANUFACT, I_SIZE, I_FORMULATION, I_COLOR, I_UNITS, I_CONTAINER, I_MANAGER_ID, I_PRODUCT_NAME )
+)}
+  * vi promotion.ctl
+{(xssnipper>,1, promotion.ctl slide,
+load data
+infile '/tmp/tpc/promotion.dat'
+into table promotion
+fields terminated by '|'
+( P_PROMO_SK, P_PROMO_ID, P_START_DATE_SK, P_END_DATE_SK, P_ITEM_SK, P_COST, P_RESPONSE_TARGET, P_PROMO_NAME, P_CHANNEL_DMAIL, P_CHANNEL_EMAIL, P_CHANNEL_CATALOG, P_CHANNEL_TV, P_CHANNEL_RADIO, P_CHANNEL_PRESS, P_CHANNEL_EVENT, P_CHANNEL_DEMO, P_CHANNEL_DETAILS, P_PURPOSE, P_DISCOUNT_ACTIVE )
+)}
+  * vi reason.ctl
+{(xssnipper>,1, reason.ctl slide,
+load data
+infile '/tmp/tpc/reason.dat'
+into table reason
+fields terminated by '|'
+( R_REASON_SK, R_REASON_ID, R_REASON_DESC )
+)}
+  * vi ship_mode.ctl
+{(xssnipper>,1, ship_mode.ctl slide,
+load data
+infile '/tmp/tpc/ship_mode.dat'
+into table ship_mode
+fields terminated by '|'
+( SM_SHIP_MODE_SK,SM_SHIP_MODE_ID,SM_TYPE,SM_CODE,SM_CARRIER,SM_CONTRACT )
+)}
+  * vi store.ctl
+{(xssnipper>,1, store.ctl slide,
+load data
+infile '/tmp/tpc/store.dat'
+into table store
+fields terminated by '|'
+( S_STORE_SK, S_STORE_ID, S_REC_START_DATE date "YYYY-MM-DD", S_REC_END_DATE date "YYYY-MM-DD", S_CLOSED_DATE_SK, S_STORE_NAME, S_NUMBER_EMPLOYEES, S_FLOOR_SPACE, S_HOURS, S_MANAGER, S_MARKET_ID, S_GEOGRAPHY_CLASS, S_MARKET_DESC, S_MARKET_MANAGER, S_DIVISION_ID, S_DIVISION_NAME, S_COMPANY_ID, S_COMPANY_NAME, S_STREET_NUMBER, S_STREET_NAME, S_STREET_TYPE, S_SUITE_NUMBER, S_CITY, S_COUNTY, S_STATE, S_ZIP, S_COUNTRY, S_GMT_OFFSET, S_TAX_PRECENTAGE )
+)}
+  * vi store_returns.ctl
+{(xssnipper>,1, store_returns.ctl slide,
+load data
+infile '/tmp/tpc/store_returns.dat'
+into table store_returns
+fields terminated by '|'
+( SR_RETURNED_DATE_SK, SR_RETURN_TIME_SK, SR_ITEM_SK, SR_CUSTOMER_SK, SR_CDEMO_SK, SR_HDEMO_SK, SR_ADDR_SK, SR_STORE_SK, SR_REASON_SK, SR_TICKET_NUMBER, SR_RETURN_QUANTITY, SR_RETURN_AMT, SR_RETURN_TAX, SR_RETURN_AMT_INC_TAX, SR_FEE, SR_RETURN_SHIP_COST, SR_REFUNDED_CASH, SR_REVERSED_CHARGE, SR_STORE_CREDIT, SR_NET_LOSS )
+)}
+  * vi store_sales.ctl
+{(xssnipper>,1, store_sales.ctl slide,
+load data
+infile '/tmp/tpc/store_sales.dat'
+into table store_sales
+fields terminated by '|'
+( SS_SOLD_DATE_SK, SS_SOLD_TIME_SK, SS_ITEM_SK, SS_CUSTOMER_SK, SS_CDEMO_SK, SS_HDEMO_SK, SS_ADDR_SK, SS_STORE_SK, SS_PROMO_SK, SS_TICKET_NUMBER, SS_QUANTITY, SS_WHOLESALE_COST, SS_LIST_PRICE, SS_SALES_PRICE, SS_EXT_DISCOUNT_AMT, SS_EXT_SALES_PRICE, SS_EXT_WHOLESALE_COST, SS_EXT_LIST_PRICE, SS_EXT_TAX, SS_COUPON_AMT, SS_NET_PAID, SS_NET_PAID_INC_TAX, SS_NET_PROFIT )
+)}
+  * vi time_dim.ctl
+{(xssnipper>,1, time_dim.ctl slide,
+load data
+infile '/tmp/tpc/time_dim.dat'
+into table time_dim
+fields terminated by '|'
+( T_TIME_SK,T_TIME_ID,T_TIME,T_HOUR,T_MINUTE,T_SECOND,T_AM_PM,T_SHIFT,T_SUB_SHIFT,T_MEAL_TIME )
+)}
+  * vi warehouse.ctl
+{(xssnipper>,1, warehouse.ctl slide,
+load data
+infile '/tmp/tpc/warehouse.dat'
+into table warehouse
+fields terminated by '|'
+( W_WAREHOUSE_SK,W_WAREHOUSE_ID,W_WAREHOUSE_NAME,W_WAREHOUSE_SQ_FT,W_STREET_NUMBER,W_STREET_NAME,W_STREET_TYPE,W_SUITE_NUMBER,W_CITY,W_COUNTY,W_STATE,W_ZIP,W_COUNTRY,W_GMT_OFFSET )
+)}
+  * vi web_page.ctl
+{(xssnipper>,1, web_page.ctl slide,
+load data
+infile '/tmp/tpc/web_page.dat'
+into table web_page
+fields terminated by '|'
+( WP_WEB_PAGE_SK, WP_WEB_PAGE_ID, WP_REC_START_DATE date "YYYY-MM-DD", WP_REC_END_DATE date "YYYY-MM-DD", WP_CREATION_DATE_SK, WP_ACCESS_DATE_SK, WP_AUTOGEN_FLAG, WP_CUSTOMER_SK, WP_URL, WP_TYPE, WP_CHAR_COUNT, WP_LINK_COUNT, WP_IMAGE_COUNT, WP_MAX_AD_COUNT )
+)}
+  * vi web_returns.ctl
+{(xssnipper>,1, web_returns.ctl slide,
+load data
+infile '/tmp/tpc/web_returns.dat'
+into table web_returns
+fields terminated by '|'
+( WR_RETURNED_DATE_SK, WR_RETURNED_TIME_SK, WR_ITEM_SK, WR_REFUNDED_CUSTOMER_SK, WR_REFUNDED_CDEMO_SK, WR_REFUNDED_HDEMO_SK, WR_REFUNDED_ADDR_SK, WR_RETURNING_CUSTOMER_SK, WR_RETURNING_CDEMO_SK, WR_RETURNING_HDEMO_SK, WR_RETURNING_ADDR_SK, WR_WEB_PAGE_SK, WR_REASON_SK, WR_ORDER_NUMBER, WR_RETURN_QUANTITY, WR_RETURN_AMT, WR_RETURN_TAX, WR_RETURN_AMT_INC_TAX, WR_FEE, WR_RETURN_SHIP_COST, WR_REFUNDED_CASH, WR_REVERSED_CHARGE, WR_ACCOUNT_CREDIT, WR_NET_LOSS )
+)}
+  * vi web_sales.ctl
+{(xssnipper>,1, web_sales.ctl slide,
+load data
+infile '/tmp/tpc/web_sales.dat'
+into table web_sales
+fields terminated by '|'
+( WS_SOLD_DATE_SK, WS_SOLD_TIME_SK, WS_SHIP_DATE_SK, WS_ITEM_SK, WS_BILL_CUSTOMER_SK, WS_BILL_CDEMO_SK, WS_BILL_HDEMO_SK, WS_BILL_ADDR_SK, WS_SHIP_CUSTOMER_SK, WS_SHIP_CDEMO_SK, WS_SHIP_HDEMO_SK, WS_SHIP_ADDR_SK, WS_WEB_PAGE_SK, WS_WEB_SITE_SK, WS_SHIP_MODE_SK, WS_WAREHOUSE_SK, WS_PROMO_SK, WS_ORDER_NUMBER, WS_QUANTITY, WS_WHOLESALE_COST, WS_LIST_PRICE, WS_SALES_PRICE, WS_EXT_DISCOUNT_AMT, WS_EXT_SALES_PRICE, WS_EXT_WHOLESALE_COST, WS_EXT_LIST_PRICE, WS_EXT_TAX, WS_COUPON_AMT, WS_EXT_SHIP_COST, WS_NET_PAID, WS_NET_PAID_INC_TAX, WS_NET_PAID_INC_SHIP, WS_NET_PAID_INC_SHIP_TAX, WS_NET_PROFIT )
+)}
-Create control file:
   * vi web_site.ctl
-<code>
+{(xssnipper>,1, web_site.ctl slide,
 load data
 infile '/tmp/tpc/web_site.dat'
@@ Line 30: / Line 339: @@
 AGER, 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 )
-</code>
+)}
-Import the data using Oracle Loader
-  * sqlldr tpcds/tpcds_1234 control=web_site.ctl
+Import the data using Oracle Loader
+  * sqlldr tpcds/* control=call_center.ctl
+  * sqlldr tpcds/* control=catalog_page.ctl
+  * sqlldr tpcds/* control=catalog_returns.ctl
+  * sqlldr tpcds/* control=catalog_sales.ctl
+  * sqlldr tpcds/* control=customer_address.ctl
+  * sqlldr tpcds/* control=customer.ctl
+  * sqlldr tpcds/* control=customer_demographics.ctl
+  * sqlldr tpcds/* control=date_dim.ctl
+  * sqlldr tpcds/* control=dbgen_version.ctl
+  * sqlldr tpcds/* control=household_demographics.ctl
+  * sqlldr tpcds/* control=income_band.ctl
+  * sqlldr tpcds/* control=inventory.ctl
+  * sqlldr tpcds/* control=item.ctl
+  * sqlldr tpcds/* control=promotion.ctl
+  * sqlldr tpcds/* control=reason.ctl
+  * sqlldr tpcds/* control=ship_mode.ctl
+  * sqlldr tpcds/* control=store.ctl
+  * sqlldr tpcds/* control=store_returns.ctl
+  * sqlldr tpcds/* control=store_sales.ctl
+  * sqlldr tpcds/* control=time_dim.ctl
+  * sqlldr tpcds/* control=warehouse.ctl
+  * sqlldr tpcds/* control=web_page.ctl
+  * sqlldr tpcds/* control=web_returns.ctl
+  * sqlldr tpcds/* control=web_sales.ctl
+  * sqlldr tpcds/* control=web_site.ctl
 ===== Links =====