600-GenPopulateDatabricks.js

const tables = {}

tables['call_center'] = {
  'cc_call_center_sk': 'long',
  'cc_call_center_id': 'string',
  'cc_rec_start_date': 'string',
  'cc_rec_end_date': 'string',
  'cc_closed_date_sk': 'long',
  'cc_open_date_sk': 'long',
  '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',
}

tables['catalog_page'] = {
  'cp_catalog_page_sk': 'long',
  'cp_catalog_page_id': 'string',
  'cp_start_date_sk': 'long',
  'cp_end_date_sk': 'long',
  'cp_department': 'string',
  'cp_catalog_number': 'int',
  'cp_catalog_page_number': 'int',
  'cp_description': 'string',
  'cp_type': 'string',
}

tables['catalog_returns'] = {
  'cr_returned_date_sk': 'long',
  'cr_returned_time_sk': 'long',
  'cr_item_sk': 'long',
  'cr_refunded_customer_sk': 'long',
  'cr_refunded_cdemo_sk': 'long',
  'cr_refunded_hdemo_sk': 'long',
  'cr_refunded_addr_sk': 'long',
  'cr_returning_customer_sk': 'long',
  'cr_returning_cdemo_sk': 'long',
  'cr_returning_hdemo_sk': 'long',
  'cr_returning_addr_sk': 'long',
  'cr_call_center_sk': 'long',
  'cr_catalog_page_sk': 'long',
  'cr_ship_mode_sk': 'long',
  'cr_warehouse_sk': 'long',
  'cr_reason_sk': 'long',
  'cr_order_number': 'long',
  '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',
}

tables['catalog_sales'] = {
  'cs_sold_date_sk': 'long',
  'cs_sold_time_sk': 'long',
  'cs_ship_date_sk': 'long',
  'cs_bill_customer_sk': 'long',
  'cs_bill_cdemo_sk': 'long',
  'cs_bill_hdemo_sk': 'long',
  'cs_bill_addr_sk': 'long',
  'cs_ship_customer_sk': 'long',
  'cs_ship_cdemo_sk': 'long',
  'cs_ship_hdemo_sk': 'long',
  'cs_ship_addr_sk': 'long',
  'cs_call_center_sk': 'long',
  'cs_catalog_page_sk': 'long',
  'cs_ship_mode_sk': 'long',
  'cs_warehouse_sk': 'long',
  'cs_item_sk': 'long',
  'cs_promo_sk': 'long',
  'cs_order_number': 'long',
  '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',
}

tables['customer_address'] = {
  'ca_address_sk': 'long',
  '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',
}

tables['customer_demographics'] = {
  'cd_demo_sk': 'long',
  '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',
}

tables['customer'] = {
  'c_customer_sk': 'long',
  'c_customer_id': 'string',
  'c_current_cdemo_sk': 'long',
  'c_current_hdemo_sk': 'long',
  'c_current_addr_sk': 'long',
  'c_first_shipto_date_sk': 'long',
  'c_first_sales_date_sk': 'long',
  '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',
}

tables['date_dim'] = {
  'd_date_sk': 'long',
  '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',
}

tables['household_demographics'] = {
  'hd_demo_sk': 'long',
  'hd_income_band_sk': 'long',
  'hd_buy_potential': 'string',
  'hd_dep_count': 'int',
  'hd_vehicle_count': 'int',
}

tables['income_band'] = {
  'ib_income_band_sk': 'long',
  'ib_lower_bound': 'int',
  'ib_upper_bound': 'int',
}

tables['inventory'] = {
  'inv_date_sk': 'long',
  'inv_item_sk': 'long',
  'inv_warehouse_sk': 'long',
  'inv_quantity_on_hand': 'int',
}

tables['item'] = {
  'i_item_sk': 'long',
  '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',
}

tables['promotion'] = {
  'p_promo_sk': 'long',
  'p_promo_id': 'string',
  'p_start_date_sk': 'long',
  'p_end_date_sk': 'long',
  'p_item_sk': 'long',
  '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',
}

tables['reason'] = {
  'r_reason_sk': 'long',
  'r_reason_id': 'string',
  'r_reason_desc': 'string',
}

tables['ship_mode'] = {
  'sm_ship_mode_sk': 'long',
  'sm_ship_mode_id': 'string',
  'sm_type': 'string',
  'sm_code': 'string',
  'sm_carrier': 'string',
  'sm_contract': 'string',
}

tables['store_returns'] = {
  'sr_returned_date_sk': 'long',
  'sr_return_time_sk': 'long',
  'sr_item_sk': 'long',
  'sr_customer_sk': 'long',
  'sr_cdemo_sk': 'long',
  'sr_hdemo_sk': 'long',
  'sr_addr_sk': 'long',
  'sr_store_sk': 'long',
  'sr_reason_sk': 'long',
  'sr_ticket_number': 'long',
  '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',
}

tables['store_sales'] = {
  'ss_sold_date_sk': 'long',
  'ss_sold_time_sk': 'long',
  'ss_item_sk': 'long',
  'ss_customer_sk': 'long',
  'ss_cdemo_sk': 'long',
  'ss_hdemo_sk': 'long',
  'ss_addr_sk': 'long',
  'ss_store_sk': 'long',
  'ss_promo_sk': 'long',
  'ss_ticket_number': 'long',
  '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',
}

tables['store'] = {
  's_store_sk': 'long',
  's_store_id': 'string',
  's_rec_start_date': 'string',
  's_rec_end_date': 'string',
  's_closed_date_sk': 'long',
  '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',
}

tables['time_dim'] = {
  't_time_sk': 'long',
  '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',
}

tables['warehouse'] = {
  'w_warehouse_sk': 'long',
  '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',
}

tables['web_page'] = {
  'wp_web_page_sk': 'long',
  'wp_web_page_id': 'string',
  'wp_rec_start_date': 'string',
  'wp_rec_end_date': 'string',
  'wp_creation_date_sk': 'long',
  'wp_access_date_sk': 'long',
  'wp_autogen_flag': 'string',
  'wp_customer_sk': 'long',
  'wp_url': 'string',
  'wp_type': 'string',
  'wp_char_count': 'int',
  'wp_link_count': 'int',
  'wp_image_count': 'int',
  'wp_max_ad_count': 'int',
}

tables['web_returns'] = {
  'wr_returned_date_sk': 'long',
  'wr_returned_time_sk': 'long',
  'wr_item_sk': 'long',
  'wr_refunded_customer_sk': 'long',
  'wr_refunded_cdemo_sk': 'long',
  'wr_refunded_hdemo_sk': 'long',
  'wr_refunded_addr_sk': 'long',
  'wr_returning_customer_sk': 'long',
  'wr_returning_cdemo_sk': 'long',
  'wr_returning_hdemo_sk': 'long',
  'wr_returning_addr_sk': 'long',
  'wr_web_page_sk': 'long',
  'wr_reason_sk': 'long',
  'wr_order_number': 'long',
  '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',
}

tables['web_sales'] = {
  'ws_sold_date_sk': 'long',
  'ws_sold_time_sk': 'long',
  'ws_ship_date_sk': 'long',
  'ws_item_sk': 'long',
  'ws_bill_customer_sk': 'long',
  'ws_bill_cdemo_sk': 'long',
  'ws_bill_hdemo_sk': 'long',
  'ws_bill_addr_sk': 'long',
  'ws_ship_customer_sk': 'long',
  'ws_ship_cdemo_sk': 'long',
  'ws_ship_hdemo_sk': 'long',
  'ws_ship_addr_sk': 'long',
  'ws_web_page_sk': 'long',
  'ws_web_site_sk': 'long',
  'ws_ship_mode_sk': 'long',
  'ws_warehouse_sk': 'long',
  'ws_promo_sk': 'long',
  'ws_order_number': 'long',
  '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',
}

tables['web_site'] = {
  'web_site_sk': 'long',
  'web_site_id': 'string',
  'web_rec_start_date': 'string',
  'web_rec_end_date': 'string',
  'web_name': 'string',
  'web_open_date_sk': 'long',
  'web_close_date_sk': 'long',
  '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',
}

console.log(`-- Configure a Databricks cluster with:
--   4 i3.8xlarge workers
--   1 i3.xlarge driver
--   Spark config: 'spark.sql.crossJoin.enable true'`)
console.log(
  `drop database if exists benchmark cascade;
create database benchmark;
use benchmark;`
);
console.log('\n')
for (const table of Object.keys(tables)) {
  const stagingColumns = Object.keys(tables[table]).map((_column, i) => `_c${i} string`)
  stagingColumns.push(`_c${stagingColumns.length} string`) // extra column for trailing |
  console.log(`create table ${table}_staging (${stagingColumns.join(', ')}) using delta;`)
}
console.log('\n')
for (const table of Object.keys(tables)) {
  const typedColumns = Object.keys(tables[table]).map(column => `${column} ${tables[table][column]}`).join(',\n\t')
  console.log(`create table ${table} (\n\t${typedColumns}) using delta;`)
}
console.log('\n')
for (const table of Object.keys(tables)) {
  console.log(`copy into ${table}_staging from 's3://fivetran-tpcds/tpcds_1000_dat/${table}/' fileformat = csv format_options ('sep' = '|');`)
}
console.log('\n')
for (const table of Object.keys(tables)) {
  const selectCast = Object.keys(tables[table]).map((column, i) => `cast(_c${i} as ${tables[table][column]}) as ${column}`).join(',\n\t')
  console.log(`insert into ${table}\nselect\n\t${selectCast}\nfrom ${table}_staging;`)
}
console.log('\n')