Many resources are needed to download a project. Please understand that we have to compensate our server costs. Thank you in advance.
Project price only 1 $
You can buy this project and download/modify it how often you want.
tpcds.create_tables.sql Maven / Gradle / Ivy
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;
