tpcds.create_tables.sql Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of druid Show documentation
Show all versions of druid Show documentation
An JDBC datasource implementation.
drop table if exists call_center_text;
create table call_center_text
(
cc_call_center_sk int,
cc_call_center_id string,
cc_rec_start_date string,
cc_rec_end_date string,
cc_closed_date_sk int,
cc_open_date_sk int,
cc_name string,
cc_class string,
cc_employees int,
cc_sq_ft int,
cc_hours string,
cc_manager string,
cc_mkt_id int,
cc_mkt_class string,
cc_mkt_desc string,
cc_market_manager string,
cc_division int,
cc_division_name string,
cc_company int,
cc_company_name string,
cc_street_number string,
cc_street_name string,
cc_street_type string,
cc_suite_number string,
cc_city string,
cc_county string,
cc_state string,
cc_zip string,
cc_country string,
cc_gmt_offset double,
cc_tax_percentage double
)
;
drop table if exists call_center;
create table call_center
as (select *
from call_center_text)
;
drop table if exists call_center_text;
drop table if exists catalog_page_text;
create table catalog_page_text
(
cp_catalog_page_sk int,
cp_catalog_page_id string,
cp_start_date_sk int,
cp_end_date_sk int,
cp_department string,
cp_catalog_number int,
cp_catalog_page_number int,
cp_description string,
cp_type string
)
;
drop table if exists catalog_page;
create table catalog_page
as (select *
from catalog_page_text)
;
drop table if exists catalog_page_text;
drop table if exists catalog_returns_text;
create table catalog_returns_text
(
cr_returned_date_sk int,
cr_returned_time_sk int,
cr_item_sk int,
cr_refunded_customer_sk int,
cr_refunded_cdemo_sk int,
cr_refunded_hdemo_sk int,
cr_refunded_addr_sk int,
cr_returning_customer_sk int,
cr_returning_cdemo_sk int,
cr_returning_hdemo_sk int,
cr_returning_addr_sk int,
cr_call_center_sk int,
cr_catalog_page_sk int,
cr_ship_mode_sk int,
cr_warehouse_sk int,
cr_reason_sk int,
cr_order_number int,
cr_return_quantity int,
cr_return_amount double,
cr_return_tax double,
cr_return_amt_inc_tax double,
cr_fee double,
cr_return_ship_cost double,
cr_refunded_cash double,
cr_reversed_charge double,
cr_store_credit double,
cr_net_loss double
)
;
drop table if exists catalog_returns;
create table catalog_returns
as (select *
from catalog_returns_text)
;
drop table if exists catalog_returns_text;
drop table if exists catalog_sales_text;
create table catalog_sales_text
(
cs_sold_date_sk int,
cs_sold_time_sk int,
cs_ship_date_sk int,
cs_bill_customer_sk int,
cs_bill_cdemo_sk int,
cs_bill_hdemo_sk int,
cs_bill_addr_sk int,
cs_ship_customer_sk int,
cs_ship_cdemo_sk int,
cs_ship_hdemo_sk int,
cs_ship_addr_sk int,
cs_call_center_sk int,
cs_catalog_page_sk int,
cs_ship_mode_sk int,
cs_warehouse_sk int,
cs_item_sk int,
cs_promo_sk int,
cs_order_number int,
cs_quantity int,
cs_wholesale_cost double,
cs_list_price double,
cs_sales_price double,
cs_ext_discount_amt double,
cs_ext_sales_price double,
cs_ext_wholesale_cost double,
cs_ext_list_price double,
cs_ext_tax double,
cs_coupon_amt double,
cs_ext_ship_cost double,
cs_net_paid double,
cs_net_paid_inc_tax double,
cs_net_paid_inc_ship double,
cs_net_paid_inc_ship_tax double,
cs_net_profit double
)
;
drop table if exists catalog_sales;
create table catalog_sales
as (select *
from catalog_sales_text)
;
drop table if exists catalog_sales_text;
drop table if exists customer_text;
create table customer_text
(
c_customer_sk int,
c_customer_id string,
c_current_cdemo_sk int,
c_current_hdemo_sk int,
c_current_addr_sk int,
c_first_shipto_date_sk int,
c_first_sales_date_sk int,
c_salutation string,
c_first_name string,
c_last_name string,
c_preferred_cust_flag string,
c_birth_day int,
c_birth_month int,
c_birth_year int,
c_birth_country string,
c_login string,
c_email_address string,
c_last_review_date string
)
;
drop table if exists customer;
create table customer
as (select *
from customer_text)
;
drop table if exists customer_text;
drop table if exists customer_address_text;
create table customer_address_text
(
ca_address_sk int,
ca_address_id string,
ca_street_number string,
ca_street_name string,
ca_street_type string,
ca_suite_number string,
ca_city string,
ca_county string,
ca_state string,
ca_zip string,
ca_country string,
ca_gmt_offset double,
ca_location_type string
)
;
drop table if exists customer_address;
create table customer_address
as (select *
from customer_address_text)
;
drop table if exists customer_address_text;
drop table if exists customer_demographics_text;
create table customer_demographics_text
(
cd_demo_sk int,
cd_gender string,
cd_marital_status string,
cd_education_status string,
cd_purchase_estimate int,
cd_credit_rating string,
cd_dep_count int,
cd_dep_employed_count int,
cd_dep_college_count int
)
;
drop table if exists customer_demographics;
create table customer_demographics
as (select *
from customer_demographics_text)
;
drop table if exists customer_demographics_text;
drop table if exists date_dim_text;
create table date_dim_text
(
d_date_sk int,
d_date_id string,
d_date string,
d_month_seq int,
d_week_seq int,
d_quarter_seq int,
d_year int,
d_dow int,
d_moy int,
d_dom int,
d_qoy int,
d_fy_year int,
d_fy_quarter_seq int,
d_fy_week_seq int,
d_day_name string,
d_quarter_name string,
d_holiday string,
d_weekend string,
d_following_holiday string,
d_first_dom int,
d_last_dom int,
d_same_day_ly int,
d_same_day_lq int,
d_current_day string,
d_current_week string,
d_current_month string,
d_current_quarter string,
d_current_year string
)
;
drop table if exists date_dim;
create table date_dim
as (select *
from date_dim_text)
;
drop table if exists date_dim_text;
drop table if exists household_demographics_text;
create table household_demographics_text
(
hd_demo_sk int,
hd_income_band_sk int,
hd_buy_potential string,
hd_dep_count int,
hd_vehicle_count int
)
;
drop table if exists household_demographics;
create table household_demographics
as (select *
from household_demographics_text)
;
drop table if exists household_demographics_text;
drop table if exists income_band_text;
create table income_band_text
(
ib_income_band_sk int,
ib_lower_bound int,
ib_upper_bound int
)
;
drop table if exists income_band;
create table income_band
as (select *
from income_band_text)
;
drop table if exists income_band_text;
drop table if exists inventory_text;
create table inventory_text
(
inv_date_sk int,
inv_item_sk int,
inv_warehouse_sk int,
inv_quantity_on_hand bigint
)
;
drop table if exists inventory;
create table inventory
as (select *
from inventory_text)
;
drop table if exists inventory_text;
drop table if exists item_text;
create table item_text
(
i_item_sk int,
i_item_id string,
i_rec_start_date string,
i_rec_end_date string,
i_item_desc string,
i_current_price double,
i_wholesale_cost double,
i_brand_id int,
i_brand string,
i_class_id int,
i_class string,
i_category_id int,
i_category string,
i_manufact_id int,
i_manufact string,
i_size string,
i_formulation string,
i_color string,
i_units string,
i_container string,
i_manager_id int,
i_product_name string
)
;
drop table if exists item;
create table item
as (select *
from item_text)
;
drop table if exists item_text;
drop table if exists promotion_text;
create table promotion_text
(
p_promo_sk int,
p_promo_id string,
p_start_date_sk int,
p_end_date_sk int,
p_item_sk int,
p_cost double,
p_response_target int,
p_promo_name string,
p_channel_dmail string,
p_channel_email string,
p_channel_catalog string,
p_channel_tv string,
p_channel_radio string,
p_channel_press string,
p_channel_event string,
p_channel_demo string,
p_channel_details string,
p_purpose string,
p_discount_active string
)
;
drop table if exists promotion;
create table promotion
as (select *
from promotion_text)
;
drop table if exists promotion_text;
drop table if exists reason_text;
create table reason_text
(
r_reason_sk int,
r_reason_id string,
r_reason_desc string
)
;
drop table if exists reason;
create table reason
as (select *
from reason_text)
;
drop table if exists reason_text;
drop table if exists ship_mode_text;
create table ship_mode_text
(
sm_ship_mode_sk int,
sm_ship_mode_id string,
sm_type string,
sm_code string,
sm_carrier string,
sm_contract string
)
;
drop table if exists ship_mode;
create table ship_mode
as (select *
from ship_mode_text)
;
drop table if exists ship_mode_text;
drop table if exists store_text;
create table store_text
(
s_store_sk int,
s_store_id string,
s_rec_start_date string,
s_rec_end_date string,
s_closed_date_sk int,
s_store_name string,
s_number_employees int,
s_floor_space int,
s_hours string,
s_manager string,
s_market_id int,
s_geography_class string,
s_market_desc string,
s_market_manager string,
s_division_id int,
s_division_name string,
s_company_id int,
s_company_name string,
s_street_number string,
s_street_name string,
s_street_type string,
s_suite_number string,
s_city string,
s_county string,
s_state string,
s_zip string,
s_country string,
s_gmt_offset double,
s_tax_precentage double
)
;
drop table if exists store;
create table store
as (select *
from store_text)
;
drop table if exists store_text;
drop table if exists store_returns_text;
create table store_returns_text
(
sr_returned_date_sk int,
sr_return_time_sk int,
sr_item_sk int,
sr_customer_sk int,
sr_cdemo_sk int,
sr_hdemo_sk int,
sr_addr_sk int,
sr_store_sk int,
sr_reason_sk int,
sr_ticket_number int,
sr_return_quantity int,
sr_return_amt double,
sr_return_tax double,
sr_return_amt_inc_tax double,
sr_fee double,
sr_return_ship_cost double,
sr_refunded_cash double,
sr_reversed_charge double,
sr_store_credit double,
sr_net_loss double
)
;
drop table if exists store_returns;
create table store_returns
as (select *
from store_returns_text)
;
drop table if exists store_returns_text;
drop table if exists store_sales_text;
create table store_sales_text
(
ss_sold_date_sk int,
ss_sold_time_sk int,
ss_item_sk int,
ss_customer_sk int,
ss_cdemo_sk int,
ss_hdemo_sk int,
ss_addr_sk int,
ss_store_sk int,
ss_promo_sk int,
ss_ticket_number int,
ss_quantity int,
ss_wholesale_cost double,
ss_list_price double,
ss_sales_price double,
ss_ext_discount_amt double,
ss_ext_sales_price double,
ss_ext_wholesale_cost double,
ss_ext_list_price double,
ss_ext_tax double,
ss_coupon_amt double,
ss_net_paid double,
ss_net_paid_inc_tax double,
ss_net_profit double
)
;
drop table if exists store_sales;
create table store_sales
as (select *
from store_sales_text)
;
drop table if exists store_sales_text;
drop table if exists time_dim_text;
create table time_dim_text
(
t_time_sk int,
t_time_id string,
t_time int,
t_hour int,
t_minute int,
t_second int,
t_am_pm string,
t_shift string,
t_sub_shift string,
t_meal_time string
)
;
drop table if exists time_dim;
create table time_dim
as (select *
from time_dim_text)
;
drop table if exists time_dim_text;
drop table if exists warehouse_text;
create table warehouse_text
(
w_warehouse_sk int,
w_warehouse_id string,
w_warehouse_name string,
w_warehouse_sq_ft int,
w_street_number string,
w_street_name string,
w_street_type string,
w_suite_number string,
w_city string,
w_county string,
w_state string,
w_zip string,
w_country string,
w_gmt_offset double
)
;
drop table if exists warehouse;
create table warehouse
as (select *
from warehouse_text)
;
drop table if exists warehouse_text;
drop table if exists web_page_text;
create table web_page_text
(
wp_web_page_sk int,
wp_web_page_id string,
wp_rec_start_date string,
wp_rec_end_date string,
wp_creation_date_sk int,
wp_access_date_sk int,
wp_autogen_flag string,
wp_customer_sk int,
wp_url string,
wp_type string,
wp_char_count int,
wp_link_count int,
wp_image_count int,
wp_max_ad_count int
)
;
drop table if exists web_page;
create table web_page
as (select *
from web_page_text)
;
drop table if exists web_page_text;
drop table if exists web_returns_text;
create table web_returns_text
(
wr_returned_date_sk int,
wr_returned_time_sk int,
wr_item_sk int,
wr_refunded_customer_sk int,
wr_refunded_cdemo_sk int,
wr_refunded_hdemo_sk int,
wr_refunded_addr_sk int,
wr_returning_customer_sk int,
wr_returning_cdemo_sk int,
wr_returning_hdemo_sk int,
wr_returning_addr_sk int,
wr_web_page_sk int,
wr_reason_sk int,
wr_order_number int,
wr_return_quantity int,
wr_return_amt double,
wr_return_tax double,
wr_return_amt_inc_tax double,
wr_fee double,
wr_return_ship_cost double,
wr_refunded_cash double,
wr_reversed_charge double,
wr_account_credit double,
wr_net_loss double
)
;
drop table if exists web_returns;
create table web_returns
as (select *
from web_returns_text)
;
drop table if exists web_returns_text;
drop table if exists web_sales_text;
create table web_sales_text
(
ws_sold_date_sk int,
ws_sold_time_sk int,
ws_ship_date_sk int,
ws_item_sk int,
ws_bill_customer_sk int,
ws_bill_cdemo_sk int,
ws_bill_hdemo_sk int,
ws_bill_addr_sk int,
ws_ship_customer_sk int,
ws_ship_cdemo_sk int,
ws_ship_hdemo_sk int,
ws_ship_addr_sk int,
ws_web_page_sk int,
ws_web_site_sk int,
ws_ship_mode_sk int,
ws_warehouse_sk int,
ws_promo_sk int,
ws_order_number int,
ws_quantity int,
ws_wholesale_cost double,
ws_list_price double,
ws_sales_price double,
ws_ext_discount_amt double,
ws_ext_sales_price double,
ws_ext_wholesale_cost double,
ws_ext_list_price double,
ws_ext_tax double,
ws_coupon_amt double,
ws_ext_ship_cost double,
ws_net_paid double,
ws_net_paid_inc_tax double,
ws_net_paid_inc_ship double,
ws_net_paid_inc_ship_tax double,
ws_net_profit double
)
;
drop table if exists web_sales;
create table web_sales
as (select *
from web_sales_text)
;
drop table if exists web_sales_text;
drop table if exists web_site_text;
create table web_site_text
(
web_site_sk int,
web_site_id string,
web_rec_start_date string,
web_rec_end_date string,
web_name string,
web_open_date_sk int,
web_close_date_sk int,
web_class string,
web_manager string,
web_mkt_id int,
web_mkt_class string,
web_mkt_desc string,
web_market_manager string,
web_company_id int,
web_company_name string,
web_street_number string,
web_street_name string,
web_street_type string,
web_suite_number string,
web_city string,
web_county string,
web_state string,
web_zip string,
web_country string,
web_gmt_offset double,
web_tax_percentage double
)
;
drop table if exists web_site;
create table web_site
as (select *
from web_site_text)
;
drop table if exists web_site_text;