本文以 GPDB 6.13 为例。
TPC-DS采用星型、雪花型等多维数据模式。它包含7张事实表，17张纬度表平均每张表含有18列。其工作负载包含99个SQL查询，覆盖SQL99和2003的核心部分以及OLAP。这个测试集包含对大数据集的统计、报表生成、联机查询、数据挖掘等复杂应用，测试用的数据和值是有倾斜的，与真实数据一致。可以说TPC-DS是与真实场景非常接近的一个测试集，也是难度较大的一个测试集。
先使用TPC-DS tool生成数据与查询，请见 《TPC-DS工具使用》。
1000s数据量以下，key数据类型可以使用integer，1000s及以上建议使用bigint。
CREATE unlogged TABLE dbgen_version ( dv_version VARCHAR(16) , dv_create_date DATE , dv_create_time TIME , dv_cmdline_args VARCHAR(200) ) WITH (appendonly=TRUE, orientation=COLUMN) distributed BY (dv_create_date); CREATE unlogged TABLE customer_address ( ca_address_sk INTEGER NOT NULL, ca_address_id CHAR(16) NOT NULL, ca_street_number CHAR(10) , ca_street_name VARCHAR(60) , ca_street_type CHAR(15) , ca_suite_number CHAR(10) , ca_city VARCHAR(60) , ca_county VARCHAR(30) , ca_state CHAR(2) , ca_zip CHAR(10) , ca_country VARCHAR(20) , ca_gmt_offset DECIMAL(5,2) , ca_location_type CHAR(20) ) WITH (appendonly=TRUE, orientation=COLUMN) distributed BY (ca_address_sk); CREATE unlogged TABLE customer_demographics ( cd_demo_sk INTEGER NOT NULL, cd_gender CHAR(1) , cd_marital_status CHAR(1) , cd_education_status CHAR(20) , cd_purchase_estimate INTEGER , cd_credit_rating CHAR(10) , cd_dep_count INTEGER , cd_dep_employed_count INTEGER , cd_dep_college_count INTEGER ) WITH (appendonly=TRUE, orientation=COLUMN) distributed BY (cd_demo_sk); CREATE unlogged TABLE date_dim ( d_date_sk INTEGER NOT NULL, d_date_id CHAR(16) NOT NULL, d_date DATE , d_month_seq INTEGER , d_week_seq INTEGER , d_quarter_seq INTEGER , d_year INTEGER , d_dow INTEGER , d_moy INTEGER , d_dom INTEGER , d_qoy INTEGER , d_fy_year INTEGER , d_fy_quarter_seq INTEGER , d_fy_week_seq INTEGER , d_day_name CHAR(9) , d_quarter_name CHAR(6) , d_holiday CHAR(1) , d_weekend CHAR(1) , d_following_holiday CHAR(1) , d_first_dom INTEGER , d_last_dom INTEGER , d_same_day_ly INTEGER , d_same_day_lq INTEGER , d_current_day CHAR(1) , d_current_week CHAR(1) , d_current_month CHAR(1) , d_current_quarter CHAR(1) , d_current_year CHAR(1) ) WITH (appendonly=TRUE, orientation=COLUMN) distributed BY (d_date_sk); CREATE unlogged TABLE warehouse ( w_warehouse_sk INTEGER NOT NULL, w_warehouse_id CHAR(16) NOT NULL, w_warehouse_name VARCHAR(20) , w_warehouse_sq_ft INTEGER , w_street_number CHAR(10) , w_street_name VARCHAR(60) , w_street_type CHAR(15) , w_suite_number CHAR(10) , w_city VARCHAR(60) , w_county VARCHAR(30) , w_state CHAR(2) , w_zip CHAR(10) , w_country VARCHAR(20) , w_gmt_offset DECIMAL(5,2) ) WITH (appendonly=TRUE, orientation=COLUMN) distributed BY (w_warehouse_sk); CREATE unlogged TABLE ship_mode ( sm_ship_mode_sk INTEGER NOT NULL, sm_ship_mode_id CHAR(16) NOT NULL, sm_type CHAR(30) , sm_code CHAR(10) , sm_carrier CHAR(20) , sm_contract CHAR(20) ) WITH (appendonly=TRUE, orientation=COLUMN) distributed BY (sm_ship_mode_sk); CREATE unlogged TABLE time_dim ( t_time_sk INTEGER NOT NULL, t_time_id CHAR(16) NOT NULL, t_time INTEGER , t_hour INTEGER , t_minute INTEGER , t_second INTEGER , t_am_pm CHAR(2) , t_shift CHAR(20) , t_sub_shift CHAR(20) , t_meal_time CHAR(20) ) WITH (appendonly=TRUE, orientation=COLUMN) distributed BY (t_time_sk); CREATE unlogged TABLE reason ( r_reason_sk INTEGER NOT NULL, r_reason_id CHAR(16) NOT NULL, r_reason_desc CHAR(100) ) WITH (appendonly=TRUE, orientation=COLUMN) distributed BY (r_reason_sk); CREATE unlogged TABLE income_band ( ib_income_band_sk INTEGER NOT NULL, ib_lower_bound INTEGER , ib_upper_bound INTEGER ) WITH (appendonly=TRUE, orientation=COLUMN) distributed BY (ib_income_band_sk); CREATE unlogged TABLE item ( i_item_sk INTEGER NOT NULL, i_item_id CHAR(16) NOT NULL, i_rec_start_date DATE , i_rec_end_date DATE , i_item_desc VARCHAR(200) , i_current_price DECIMAL(7,2) , i_wholesale_cost DECIMAL(7,2) , i_brand_id INTEGER , i_brand CHAR(50) , i_class_id INTEGER , i_class CHAR(50) , i_category_id INTEGER , i_category CHAR(50) , i_manufact_id INTEGER , i_manufact CHAR(50) , i_size CHAR(20) , i_formulation CHAR(20) , i_color CHAR(20) , i_units CHAR(10) , i_container CHAR(10) , i_manager_id INTEGER , i_product_name CHAR(50) ) WITH (appendonly=TRUE, orientation=COLUMN) distributed BY (i_item_sk); CREATE unlogged TABLE store ( s_store_sk INTEGER NOT NULL, s_store_id CHAR(16) NOT NULL, s_rec_start_date DATE , s_rec_end_date DATE , s_closed_date_sk INTEGER , s_store_name VARCHAR(50) , s_number_employees INTEGER , s_floor_space INTEGER , s_hours CHAR(20) , s_manager VARCHAR(40) , s_market_id INTEGER , s_geography_class VARCHAR(100) , s_market_desc VARCHAR(100) , s_market_manager VARCHAR(40) , s_division_id INTEGER , s_division_name VARCHAR(50) , s_company_id INTEGER , s_company_name VARCHAR(50) , s_street_number VARCHAR(10) , s_street_name VARCHAR(60) , s_street_type CHAR(15) , s_suite_number CHAR(10) , s_city VARCHAR(60) , s_county VARCHAR(30) , s_state CHAR(2) , s_zip CHAR(10) , s_country VARCHAR(20) , s_gmt_offset DECIMAL(5,2) , s_tax_precentage DECIMAL(5,2) ) WITH (appendonly=TRUE, orientation=COLUMN) distributed BY (s_store_sk); CREATE unlogged TABLE call_center ( cc_call_center_sk INTEGER NOT NULL, cc_call_center_id CHAR(16) NOT NULL, cc_rec_start_date DATE , cc_rec_end_date DATE , cc_closed_date_sk INTEGER , cc_open_date_sk INTEGER , cc_name VARCHAR(50) , cc_class VARCHAR(50) , cc_employees INTEGER , cc_sq_ft INTEGER , cc_hours CHAR(20) , cc_manager VARCHAR(40) , cc_mkt_id INTEGER , cc_mkt_class CHAR(50) , cc_mkt_desc VARCHAR(100) , cc_market_manager VARCHAR(40) , cc_division INTEGER , cc_division_name VARCHAR(50) , cc_company INTEGER , cc_company_name CHAR(50) , cc_street_number CHAR(10) , cc_street_name VARCHAR(60) , cc_street_type CHAR(15) , cc_suite_number CHAR(10) , cc_city VARCHAR(60) , cc_county VARCHAR(30) , cc_state CHAR(2) , cc_zip CHAR(10) , cc_country VARCHAR(20) , cc_gmt_offset DECIMAL(5,2) , cc_tax_percentage DECIMAL(5,2) ) WITH (appendonly=TRUE, orientation=COLUMN) distributed BY (cc_call_center_sk); CREATE unlogged TABLE customer ( c_customer_sk INTEGER NOT NULL, c_customer_id CHAR(16) NOT NULL, c_current_cdemo_sk INTEGER , c_current_hdemo_sk INTEGER , c_current_addr_sk INTEGER , c_first_shipto_date_sk INTEGER , c_first_sales_date_sk INTEGER , c_salutation CHAR(10) , c_first_name CHAR(20) , c_last_name CHAR(30) , c_preferred_cust_flag CHAR(1) , c_birth_day INTEGER , c_birth_month INTEGER , c_birth_year INTEGER , c_birth_country VARCHAR(20) , c_login CHAR(13) , c_email_address CHAR(50) , c_last_review_date_sk INTEGER ) WITH (appendonly=TRUE, orientation=COLUMN) distributed BY (c_customer_sk); CREATE unlogged TABLE web_site ( web_site_sk INTEGER NOT NULL, web_site_id CHAR(16) NOT NULL, web_rec_start_date DATE , web_rec_end_date DATE , web_name VARCHAR(50) , web_open_date_sk INTEGER , web_close_date_sk INTEGER , web_class VARCHAR(50) , web_manager VARCHAR(40) , web_mkt_id INTEGER , web_mkt_class VARCHAR(50) , web_mkt_desc VARCHAR(100) , web_market_manager VARCHAR(40) , web_company_id INTEGER , web_company_name CHAR(50) , web_street_number CHAR(10) , web_street_name VARCHAR(60) , web_street_type CHAR(15) , web_suite_number CHAR(10) , web_city VARCHAR(60) , web_county VARCHAR(30) , web_state CHAR(2) , web_zip CHAR(10) , web_country VARCHAR(20) , web_gmt_offset DECIMAL(5,2) , web_tax_percentage DECIMAL(5,2) ) WITH (appendonly=TRUE, orientation=COLUMN) distributed BY (web_site_sk); CREATE unlogged TABLE store_returns ( sr_returned_date_sk INTEGER , sr_return_time_sk INTEGER , sr_item_sk INTEGER NOT NULL, sr_customer_sk INTEGER , sr_cdemo_sk INTEGER , sr_hdemo_sk INTEGER , sr_addr_sk INTEGER , sr_store_sk INTEGER , sr_reason_sk INTEGER , sr_ticket_number INTEGER NOT NULL, sr_return_quantity INTEGER , sr_return_amt DECIMAL(7,2) , sr_return_tax DECIMAL(7,2) , sr_return_amt_inc_tax DECIMAL(7,2) , sr_fee DECIMAL(7,2) , sr_return_ship_cost DECIMAL(7,2) , sr_refunded_cash DECIMAL(7,2) , sr_reversed_charge DECIMAL(7,2) , sr_store_credit DECIMAL(7,2) , sr_net_loss DECIMAL(7,2) ) WITH (appendonly=TRUE, orientation=COLUMN) distributed BY (sr_item_sk, sr_ticket_number); CREATE unlogged TABLE household_demographics ( hd_demo_sk INTEGER NOT NULL, hd_income_band_sk INTEGER , hd_buy_potential CHAR(15) , hd_dep_count INTEGER , hd_vehicle_count INTEGER ) WITH (appendonly=TRUE, orientation=COLUMN) distributed BY (hd_demo_sk); CREATE unlogged TABLE web_page ( wp_web_page_sk INTEGER NOT NULL, wp_web_page_id CHAR(16) NOT NULL, wp_rec_start_date DATE , wp_rec_end_date DATE , wp_creation_date_sk INTEGER , wp_access_date_sk INTEGER , wp_autogen_flag CHAR(1) , wp_customer_sk INTEGER , wp_url VARCHAR(100) , wp_type CHAR(50) , wp_char_count INTEGER , wp_link_count INTEGER , wp_image_count INTEGER , wp_max_ad_count INTEGER ) WITH (appendonly=TRUE, orientation=COLUMN) distributed BY (wp_web_page_sk); CREATE unlogged TABLE promotion ( p_promo_sk INTEGER NOT NULL, p_promo_id CHAR(16) NOT NULL, p_start_date_sk INTEGER , p_end_date_sk INTEGER , p_item_sk INTEGER , p_cost DECIMAL(15,2) , p_response_target INTEGER , p_promo_name CHAR(50) , p_channel_dmail CHAR(1) , p_channel_email CHAR(1) , p_channel_catalog CHAR(1) , p_channel_tv CHAR(1) , p_channel_radio CHAR(1) , p_channel_press CHAR(1) , p_channel_event CHAR(1) , p_channel_demo CHAR(1) , p_channel_details VARCHAR(100) , p_purpose CHAR(15) , p_discount_active CHAR(1) ) WITH (appendonly=TRUE, orientation=COLUMN) distributed BY (p_promo_sk); CREATE unlogged TABLE catalog_page ( cp_catalog_page_sk INTEGER NOT NULL, cp_catalog_page_id CHAR(16) NOT NULL, cp_start_date_sk INTEGER , cp_end_date_sk INTEGER , cp_department VARCHAR(50) , cp_catalog_number INTEGER , cp_catalog_page_number INTEGER , cp_description VARCHAR(100) , cp_type VARCHAR(100) ) WITH (appendonly=TRUE, orientation=COLUMN) distributed BY (cp_catalog_page_sk); CREATE unlogged TABLE inventory ( inv_date_sk INTEGER NOT NULL, inv_item_sk INTEGER NOT NULL, inv_warehouse_sk INTEGER NOT NULL, inv_quantity_on_hand INTEGER ) WITH (appendonly=TRUE, orientation=COLUMN) distributed BY (inv_date_sk, inv_item_sk, inv_warehouse_sk); CREATE unlogged TABLE catalog_returns ( cr_returned_date_sk INTEGER , cr_returned_time_sk INTEGER , cr_item_sk INTEGER NOT NULL, cr_refunded_customer_sk INTEGER , cr_refunded_cdemo_sk INTEGER , cr_refunded_hdemo_sk INTEGER , cr_refunded_addr_sk INTEGER , cr_returning_customer_sk INTEGER , cr_returning_cdemo_sk INTEGER , cr_returning_hdemo_sk INTEGER , cr_returning_addr_sk INTEGER , cr_call_center_sk INTEGER , cr_catalog_page_sk INTEGER , cr_ship_mode_sk INTEGER , cr_warehouse_sk INTEGER , cr_reason_sk INTEGER , cr_order_number INTEGER NOT NULL, cr_return_quantity INTEGER , cr_return_amount DECIMAL(7,2) , cr_return_tax DECIMAL(7,2) , cr_return_amt_inc_tax DECIMAL(7,2) , cr_fee DECIMAL(7,2) , cr_return_ship_cost DECIMAL(7,2) , cr_refunded_cash DECIMAL(7,2) , cr_reversed_charge DECIMAL(7,2) , cr_store_credit DECIMAL(7,2) , cr_net_loss DECIMAL(7,2) ) WITH (appendonly=TRUE, orientation=COLUMN) distributed BY (cr_item_sk, cr_order_number); CREATE unlogged TABLE web_returns ( wr_returned_date_sk INTEGER , wr_returned_time_sk INTEGER , wr_item_sk INTEGER NOT NULL, wr_refunded_customer_sk INTEGER , wr_refunded_cdemo_sk INTEGER , wr_refunded_hdemo_sk INTEGER , wr_refunded_addr_sk INTEGER , wr_returning_customer_sk INTEGER , wr_returning_cdemo_sk INTEGER , wr_returning_hdemo_sk INTEGER , wr_returning_addr_sk INTEGER , wr_web_page_sk INTEGER , wr_reason_sk INTEGER , wr_order_number INTEGER NOT NULL, wr_return_quantity INTEGER , wr_return_amt DECIMAL(7,2) , wr_return_tax DECIMAL(7,2) , wr_return_amt_inc_tax DECIMAL(7,2) , wr_fee DECIMAL(7,2) , wr_return_ship_cost DECIMAL(7,2) , wr_refunded_cash DECIMAL(7,2) , wr_reversed_charge DECIMAL(7,2) , wr_account_credit DECIMAL(7,2) , wr_net_loss DECIMAL(7,2) ) WITH (appendonly=TRUE, orientation=COLUMN) distributed BY (wr_item_sk, wr_order_number); CREATE unlogged TABLE web_sales ( ws_sold_date_sk INTEGER , ws_sold_time_sk INTEGER , ws_ship_date_sk INTEGER , ws_item_sk INTEGER NOT NULL, ws_bill_customer_sk INTEGER , ws_bill_cdemo_sk INTEGER , ws_bill_hdemo_sk INTEGER , ws_bill_addr_sk INTEGER , ws_ship_customer_sk INTEGER , ws_ship_cdemo_sk INTEGER , ws_ship_hdemo_sk INTEGER , ws_ship_addr_sk INTEGER , ws_web_page_sk INTEGER , ws_web_site_sk INTEGER , ws_ship_mode_sk INTEGER , ws_warehouse_sk INTEGER , ws_promo_sk INTEGER , ws_order_number INTEGER NOT NULL, ws_quantity INTEGER , ws_wholesale_cost DECIMAL(7,2) , ws_list_price DECIMAL(7,2) , ws_sales_price DECIMAL(7,2) , ws_ext_discount_amt DECIMAL(7,2) , ws_ext_sales_price DECIMAL(7,2) , ws_ext_wholesale_cost DECIMAL(7,2) , ws_ext_list_price DECIMAL(7,2) , ws_ext_tax DECIMAL(7,2) , ws_coupon_amt DECIMAL(7,2) , ws_ext_ship_cost DECIMAL(7,2) , ws_net_paid DECIMAL(7,2) , ws_net_paid_inc_tax DECIMAL(7,2) , ws_net_paid_inc_ship DECIMAL(7,2) , ws_net_paid_inc_ship_tax DECIMAL(7,2) , ws_net_profit DECIMAL(7,2) ) WITH (appendonly=TRUE, orientation=COLUMN) distributed BY (ws_item_sk, ws_order_number); CREATE unlogged TABLE catalog_sales ( cs_sold_date_sk INTEGER , cs_sold_time_sk INTEGER , cs_ship_date_sk INTEGER , cs_bill_customer_sk INTEGER , cs_bill_cdemo_sk INTEGER , cs_bill_hdemo_sk INTEGER , cs_bill_addr_sk INTEGER , cs_ship_customer_sk INTEGER , cs_ship_cdemo_sk INTEGER , cs_ship_hdemo_sk INTEGER , cs_ship_addr_sk INTEGER , cs_call_center_sk INTEGER , cs_catalog_page_sk INTEGER , cs_ship_mode_sk INTEGER , cs_warehouse_sk INTEGER , cs_item_sk INTEGER NOT NULL, cs_promo_sk INTEGER , cs_order_number INTEGER NOT NULL, cs_quantity INTEGER , cs_wholesale_cost DECIMAL(7,2) , cs_list_price DECIMAL(7,2) , cs_sales_price DECIMAL(7,2) , cs_ext_discount_amt DECIMAL(7,2) , cs_ext_sales_price DECIMAL(7,2) , cs_ext_wholesale_cost DECIMAL(7,2) , cs_ext_list_price DECIMAL(7,2) , cs_ext_tax DECIMAL(7,2) , cs_coupon_amt DECIMAL(7,2) , cs_ext_ship_cost DECIMAL(7,2) , cs_net_paid DECIMAL(7,2) , cs_net_paid_inc_tax DECIMAL(7,2) , cs_net_paid_inc_ship DECIMAL(7,2) , cs_net_paid_inc_ship_tax DECIMAL(7,2) , cs_net_profit DECIMAL(7,2) ) WITH (appendonly=TRUE, orientation=COLUMN) distributed BY (cs_item_sk, cs_order_number); CREATE unlogged TABLE store_sales ( ss_sold_date_sk INTEGER , ss_sold_time_sk INTEGER , ss_item_sk INTEGER NOT NULL, ss_customer_sk INTEGER , ss_cdemo_sk INTEGER , ss_hdemo_sk INTEGER , ss_addr_sk INTEGER , ss_store_sk INTEGER , ss_promo_sk INTEGER , ss_ticket_number INTEGER NOT NULL, ss_quantity INTEGER , ss_wholesale_cost DECIMAL(7,2) , ss_list_price DECIMAL(7,2) , ss_sales_price DECIMAL(7,2) , ss_ext_discount_amt DECIMAL(7,2) , ss_ext_sales_price DECIMAL(7,2) , ss_ext_wholesale_cost DECIMAL(7,2) , ss_ext_list_price DECIMAL(7,2) , ss_ext_tax DECIMAL(7,2) , ss_coupon_amt DECIMAL(7,2) , ss_net_paid DECIMAL(7,2) , ss_net_paid_inc_tax DECIMAL(7,2) , ss_net_profit DECIMAL(7,2) ) WITH (appendonly=TRUE, orientation=COLUMN) distributed BY (ss_item_sk, ss_ticket_number);
CREATE readable external TABLE dbgen_version_ext (LIKE dbgen_version) location ('gpfdist://localhost:9898/tpcds/10s/dbgen_version.dat') format 'text' (delimiter '|' NULL AS ''); CREATE readable external TABLE customer_address_ext (LIKE customer_address) location ('gpfdist://localhost:9898/tpcds/10s/customer_address.dat') format 'text' (delimiter '|' NULL AS ''); CREATE readable external TABLE customer_demographics_ext (LIKE customer_demographics) location ('gpfdist://localhost:9898/tpcds/10s/customer_demographics.dat') format 'text' (delimiter '|' NULL AS ''); CREATE readable external TABLE date_dim_ext (LIKE date_dim) location ('gpfdist://localhost:9898/tpcds/10s/date_dim.dat') format 'text' (delimiter '|' NULL AS ''); CREATE readable external TABLE warehouse_ext (LIKE warehouse) location ('gpfdist://localhost:9898/tpcds/10s/warehouse.dat') format 'text' (delimiter '|' NULL AS ''); CREATE readable external TABLE ship_mode_ext (LIKE ship_mode) location ('gpfdist://localhost:9898/tpcds/10s/ship_mode.dat') format 'text' (delimiter '|' NULL AS ''); CREATE readable external TABLE time_dim_ext (LIKE time_dim) location ('gpfdist://localhost:9898/tpcds/10s/time_dim.dat') format 'text' (delimiter '|' NULL AS ''); CREATE readable external TABLE reason_ext (LIKE reason) location ('gpfdist://localhost:9898/tpcds/10s/reason.dat') format 'text' (delimiter '|' NULL AS ''); CREATE readable external TABLE income_band_ext (LIKE income_band) location ('gpfdist://localhost:9898/tpcds/10s/income_band.dat') format 'text' (delimiter '|' NULL AS ''); CREATE readable external TABLE item_ext (LIKE item) location ('gpfdist://localhost:9898/tpcds/10s/item.dat') format 'text' (delimiter '|' NULL AS ''); CREATE readable external TABLE store_ext (LIKE store) location ('gpfdist://localhost:9898/tpcds/10s/store.dat') format 'text' (delimiter '|' NULL AS ''); CREATE readable external TABLE call_center_ext (LIKE call_center) location ('gpfdist://localhost:9898/tpcds/10s/call_center.dat') format 'text' (delimiter '|' NULL AS ''); CREATE readable external TABLE customer_ext (LIKE customer) location ('gpfdist://localhost:9898/tpcds/10s/customer.dat') format 'text' (delimiter '|' NULL AS ''); CREATE readable external TABLE web_site_ext (LIKE web_site) location ('gpfdist://localhost:9898/tpcds/10s/web_site.dat') format 'text' (delimiter '|' NULL AS ''); CREATE readable external TABLE store_returns_ext (LIKE store_returns) location ('gpfdist://localhost:9898/tpcds/10s/store_returns.dat') format 'text' (delimiter '|' NULL AS ''); CREATE readable external TABLE household_demographics_ext (LIKE household_demographics) location ('gpfdist://localhost:9898/tpcds/10s/household_demographics.dat') format 'text' (delimiter '|' NULL AS ''); CREATE readable external TABLE web_page_ext (LIKE web_page) location ('gpfdist://localhost:9898/tpcds/10s/web_page.dat') format 'text' (delimiter '|' NULL AS ''); CREATE readable external TABLE promotion_ext (LIKE promotion) location ('gpfdist://localhost:9898/tpcds/10s/promotion.dat') format 'text' (delimiter '|' NULL AS ''); CREATE readable external TABLE catalog_page_ext (LIKE catalog_page) location ('gpfdist://localhost:9898/tpcds/10s/catalog_page.dat') format 'text' (delimiter '|' NULL AS ''); CREATE readable external TABLE inventory_ext (LIKE inventory) location ('gpfdist://localhost:9898/tpcds/10s/inventory.dat') format 'text' (delimiter '|' NULL AS ''); CREATE readable external TABLE catalog_returns_ext (LIKE catalog_returns) location ('gpfdist://localhost:9898/tpcds/10s/catalog_returns.dat') format 'text' (delimiter '|' NULL AS ''); CREATE readable external TABLE web_returns_ext (LIKE web_returns) location ('gpfdist://localhost:9898/tpcds/10s/web_returns.dat') format 'text' (delimiter '|' NULL AS ''); CREATE readable external TABLE web_sales_ext (LIKE web_sales) location ('gpfdist://localhost:9898/tpcds/10s/web_sales.dat') format 'text' (delimiter '|' NULL AS ''); CREATE readable external TABLE catalog_sales_ext (LIKE catalog_sales) location ('gpfdist://localhost:9898/tpcds/10s/catalog_sales.dat') format 'text' (delimiter '|' NULL AS ''); CREATE readable external TABLE store_sales_ext (LIKE store_sales) location ('gpfdist://localhost:9898/tpcds/10s/store_sales.dat') format 'text' (delimiter '|' NULL AS ''); INSERT INTO dbgen_version SELECT * FROM dbgen_version_ext; DROP external TABLE dbgen_version_ext; INSERT INTO customer_address SELECT * FROM customer_address_ext; DROP external TABLE customer_address_ext; INSERT INTO customer_demographics SELECT * FROM customer_demographics_ext; DROP external TABLE customer_demographics_ext; INSERT INTO date_dim SELECT * FROM date_dim_ext; DROP external TABLE date_dim_ext; INSERT INTO warehouse SELECT * FROM warehouse_ext; DROP external TABLE warehouse_ext; INSERT INTO ship_mode SELECT * FROM ship_mode_ext; DROP external TABLE ship_mode_ext; INSERT INTO time_dim SELECT * FROM time_dim_ext; DROP external TABLE time_dim_ext; INSERT INTO reason SELECT * FROM reason_ext; DROP external TABLE reason_ext; INSERT INTO income_band SELECT * FROM income_band_ext; DROP external TABLE income_band_ext; INSERT INTO item SELECT * FROM item_ext; DROP external TABLE item_ext; INSERT INTO store SELECT * FROM store_ext; DROP external TABLE store_ext; INSERT INTO call_center SELECT * FROM call_center_ext; DROP external TABLE call_center_ext; INSERT INTO customer SELECT * FROM customer_ext; DROP external TABLE customer_ext; INSERT INTO web_site SELECT * FROM web_site_ext; DROP external TABLE web_site_ext; INSERT INTO store_returns SELECT * FROM store_returns_ext; DROP external TABLE store_returns_ext; INSERT INTO household_demographics SELECT * FROM household_demographics_ext; DROP external TABLE household_demographics_ext; INSERT INTO web_page SELECT * FROM web_page_ext; DROP external TABLE web_page_ext; INSERT INTO promotion SELECT * FROM promotion_ext; DROP external TABLE promotion_ext; INSERT INTO catalog_page SELECT * FROM catalog_page_ext; DROP external TABLE catalog_page_ext; INSERT INTO inventory SELECT * FROM inventory_ext; DROP external TABLE inventory_ext; INSERT INTO catalog_returns SELECT * FROM catalog_returns_ext; DROP external TABLE catalog_returns_ext; INSERT INTO web_returns SELECT * FROM web_returns_ext; DROP external TABLE web_returns_ext; INSERT INTO web_sales SELECT * FROM web_sales_ext; DROP external TABLE web_sales_ext; INSERT INTO catalog_sales SELECT * FROM catalog_sales_ext; DROP external TABLE catalog_sales_ext; INSERT INTO store_sales SELECT * FROM store_sales_ext; DROP external TABLE store_sales_ext;
analyze dbgen_version; analyze customer_address; analyze customer_demographics; analyze date_dim; analyze warehouse; analyze ship_mode; analyze time_dim; analyze reason; analyze income_band; analyze item; analyze store; analyze call_center; analyze customer; analyze web_site; analyze store_returns; analyze household_demographics; analyze web_page; analyze promotion; analyze catalog_page; analyze inventory; analyze catalog_returns; analyze web_returns; analyze web_sales; analyze catalog_sales; analyze store_sales;