DROP TABLE IF EXISTS `account`;
CREATE TABLE `account` (
  `account_id` INT,
  `account_parent` INT,
  `account_description` STRING,
  `account_type` STRING,
  `account_rollup` STRING,
  `Custom_Members` STRING
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '{' STORED AS TEXTFILE;

DROP TABLE IF EXISTS `agg_c_10_sales_fact_1997`;
CREATE TABLE `agg_c_10_sales_fact_1997` (
  `month_of_year` INT,
  `quarter` STRING,
  `the_year` INT,
  `store_sales` DOUBLE,
  `store_cost` DOUBLE,
  `unit_sales` DOUBLE,
  `customer_count` INT,
  `fact_count` INT
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '{' STORED AS TEXTFILE;

DROP TABLE IF EXISTS `agg_c_14_sales_fact_1997`;
CREATE TABLE `agg_c_14_sales_fact_1997` (
  `product_id` INT,
  `customer_id` INT,
  `store_id` INT,
  `promotion_id` INT,
  `month_of_year` INT,
  `quarter` STRING,
  `the_year` INT,
  `store_sales` DOUBLE,
  `store_cost` DOUBLE,
  `unit_sales` DOUBLE,
  `fact_count` INT
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '{' STORED AS TEXTFILE;

DROP TABLE IF EXISTS `agg_c_special_sales_fact_1997`;
CREATE TABLE `agg_c_special_sales_fact_1997` (
  `product_id` INT,
  `promotion_id` INT,
  `customer_id` INT,
  `store_id` INT,
  `time_month` INT,
  `time_quarter` STRING,
  `time_year` INT,
  `store_sales_sum` DOUBLE,
  `store_cost_sum` DOUBLE,
  `unit_sales_sum` DOUBLE,
  `fact_count` INT
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '{' STORED AS TEXTFILE;

DROP TABLE IF EXISTS `agg_g_ms_pcat_sales_fact_1997`;
CREATE TABLE `agg_g_ms_pcat_sales_fact_1997` (
  `gender` STRING,
  `marital_status` STRING,
  `product_family` STRING,
  `product_department` STRING,
  `product_category` STRING,
  `month_of_year` INT,
  `quarter` STRING,
  `the_year` INT,
  `store_sales` DOUBLE,
  `store_cost` DOUBLE,
  `unit_sales` DOUBLE,
  `customer_count` INT,
  `fact_count` INT
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '{' STORED AS TEXTFILE;

DROP TABLE IF EXISTS `agg_l_03_sales_fact_1997`;
CREATE TABLE `agg_l_03_sales_fact_1997` (
  `time_id` INT,
  `customer_id` INT,
  `store_sales` DOUBLE,
  `store_cost` DOUBLE,
  `unit_sales` DOUBLE,
  `fact_count` INT
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '{' STORED AS TEXTFILE;

DROP TABLE IF EXISTS `agg_l_04_sales_fact_1997`;
CREATE TABLE `agg_l_04_sales_fact_1997` (
  `time_id` INT,
  `store_sales` DOUBLE,
  `store_cost` DOUBLE,
  `unit_sales` DOUBLE,
  `customer_count` INT,
  `fact_count` INT
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '{' STORED AS TEXTFILE;

DROP TABLE IF EXISTS `agg_l_05_sales_fact_1997`;
CREATE TABLE `agg_l_05_sales_fact_1997` (
  `product_id` INT,
  `customer_id` INT,
  `promotion_id` INT,
  `store_id` INT,
  `store_sales` DOUBLE,
  `store_cost` DOUBLE,
  `unit_sales` DOUBLE,
  `fact_count` INT
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '{' STORED AS TEXTFILE;

DROP TABLE IF EXISTS `agg_lc_06_sales_fact_1997`;
CREATE TABLE `agg_lc_06_sales_fact_1997` (
  `time_id` INT,
  `city` STRING,
  `state_province` STRING,
  `country` STRING,
  `store_sales` DOUBLE,
  `store_cost` DOUBLE,
  `unit_sales` DOUBLE,
  `fact_count` INT
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '{' STORED AS TEXTFILE;

DROP TABLE IF EXISTS `agg_lc_100_sales_fact_1997`;
CREATE TABLE `agg_lc_100_sales_fact_1997` (
  `product_id` INT,
  `customer_id` INT,
  `quarter` STRING,
  `the_year` INT,
  `store_sales` DOUBLE,
  `store_cost` DOUBLE,
  `unit_sales` DOUBLE,
  `fact_count` INT
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '{' STORED AS TEXTFILE;

DROP TABLE IF EXISTS `agg_ll_01_sales_fact_1997`;
CREATE TABLE `agg_ll_01_sales_fact_1997` (
  `product_id` INT,
  `time_id` INT,
  `customer_id` INT,
  `store_sales` DOUBLE,
  `store_cost` DOUBLE,
  `unit_sales` DOUBLE,
  `fact_count` INT
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '{' STORED AS TEXTFILE;

DROP TABLE IF EXISTS `agg_pl_01_sales_fact_1997`;
CREATE TABLE `agg_pl_01_sales_fact_1997` (
  `product_id` INT,
  `time_id` INT,
  `customer_id` INT,
  `store_sales_sum` DOUBLE,
  `store_cost_sum` DOUBLE,
  `unit_sales_sum` DOUBLE,
  `fact_count` INT
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '{' STORED AS TEXTFILE;

DROP TABLE IF EXISTS `category`;
CREATE TABLE `category` (
  `category_id` STRING,
  `category_parent` STRING,
  `category_description` STRING,
  `category_rollup` STRING
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '{' STORED AS TEXTFILE;

DROP TABLE IF EXISTS `currency`;
CREATE TABLE `currency` (
  `currency_id` INT,
  `date` STRING,
  `currency` STRING,
  `conversion_ratio` DOUBLE
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '{' STORED AS TEXTFILE;

DROP TABLE IF EXISTS `customer`;
CREATE TABLE `customer` (
  `customer_id` INT,
  `account_num` INT,
  `lname` STRING,
  `fname` STRING,
  `mi` STRING,
  `address1` STRING,
  `address2` STRING,
  `address3` STRING,
  `address4` STRING,
  `city` STRING,
  `state_province` STRING,
  `postal_code` STRING,
  `country` STRING,
  `customer_region_id` INT,
  `phone1` STRING,
  `phone2` STRING,
  `birthdate` STRING,
  `marital_status` STRING,
  `yearly_income` STRING,
  `gender` STRING,
  `total_children` INT,
  `num_children_at_home` INT,
  `education` STRING,
  `date_accnt_opened` STRING,
  `member_card` STRING,
  `occupation` STRING,
  `houseowner` STRING,
  `num_cars_owned` INT,
  `fullname` STRING
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '{' STORED AS TEXTFILE;

DROP TABLE IF EXISTS `days`;
CREATE TABLE `days` (
  `day` INT,
  `week_day` STRING
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '{' STORED AS TEXTFILE;

DROP TABLE IF EXISTS `department`;
CREATE TABLE `department` (
  `department_id` INT,
  `department_description` STRING
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '{' STORED AS TEXTFILE;

DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee` (
  `employee_id` INT,
  `full_name` STRING,
  `first_name` STRING,
  `last_name` STRING,
  `position_id` INT,
  `position_title` STRING,
  `store_id` INT,
  `department_id` INT,
  `birth_date` STRING,
  `hire_date` STRING,
  `end_date` STRING,
  `salary` DOUBLE,
  `supervisor_id` INT,
  `education_level` STRING,
  `marital_status` STRING,
  `gender` STRING,
  `management_role` STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '{' STORED AS TEXTFILE;

DROP TABLE IF EXISTS `employee_closure`;
CREATE TABLE `employee_closure` (
  `employee_id` INT,
  `supervisor_id` INT,
  `distance` INT) ROW FORMAT DELIMITED FIELDS TERMINATED BY '{' STORED AS TEXTFILE;

DROP TABLE IF EXISTS `expense_fact`;
CREATE TABLE `expense_fact` (
  `store_id` INT,
  `account_id` INT,
  `exp_date` STRING,
  `time_id` INT,
  `category_id` STRING,
  `currency_id` INT,
  `amount` DOUBLE
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '{' STORED AS TEXTFILE;

DROP TABLE IF EXISTS `inventory_fact_1997`;
CREATE TABLE `inventory_fact_1997` (
  `product_id` INT,
  `time_id` INT,
  `warehouse_id` INT,
  `store_id` INT,
  `units_ordered` INT,
  `units_shipped` INT,
  `warehouse_sales` DOUBLE,
  `warehouse_cost` DOUBLE,
  `supply_time` INT,
  `store_invoice` DOUBLE
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '{' STORED AS TEXTFILE;

DROP TABLE IF EXISTS `inventory_fact_1998`;
CREATE TABLE `inventory_fact_1998` (
  `product_id` INT,
  `time_id` INT,
  `warehouse_id` INT,
  `store_id` INT,
  `units_ordered` INT,
  `units_shipped` INT,
  `warehouse_sales` DOUBLE,
  `warehouse_cost` DOUBLE,
  `supply_time` INT,
  `store_invoice` DOUBLE
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '{' STORED AS TEXTFILE;

DROP TABLE IF EXISTS `position`;
CREATE TABLE `position` (
  `position_id` INT,
  `position_title` STRING,
  `pay_type` STRING,
  `min_scale` DOUBLE,
  `max_scale` DOUBLE,
  `management_role` STRING
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '{' STORED AS TEXTFILE;

DROP TABLE IF EXISTS `product`;
CREATE TABLE `product` (
  `product_class_id` INT,
  `product_id` INT,
  `brand_name` STRING,
  `product_name` STRING,
  `SKU` INT, --LONG
  `SRP` DOUBLE,
  `gross_weight` DOUBLE,
  `net_weight` DOUBLE,
  `recyclable_package` INT,
  `low_fat` INT,
  `units_per_case` INT,
  `cases_per_pallet` INT,
  `shelf_width` DOUBLE,
  `shelf_height` DOUBLE,
  `shelf_depth` DOUBLE
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '{' STORED AS TEXTFILE;

DROP TABLE IF EXISTS `product_class`;
CREATE TABLE `product_class` (
  `product_class_id` INT,
  `product_subcategory` STRING,
  `product_category` STRING,
  `product_department` STRING,
  `product_family` STRING
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '{' STORED AS TEXTFILE;

DROP TABLE IF EXISTS `promotion`;
CREATE TABLE `promotion` (
  `promotion_id` INT,
  `promotion_district_id` INT,
  `promotion_name` STRING,
  `media_type` STRING,
  `cost` DOUBLE,
  `start_date` STRING,
  `end_date` STRING
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '{' STORED AS TEXTFILE;

DROP TABLE IF EXISTS `region`;
CREATE TABLE `region` (
  `region_id` INT,
  `sales_city` STRING,
  `sales_state_province` STRING,
  `sales_district` STRING,
  `sales_region` STRING,
  `sales_country` STRING,
  `sales_district_id` INT
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '{' STORED AS TEXTFILE;

DROP TABLE IF EXISTS `reserve_employee`;
CREATE TABLE `reserve_employee` (
  `employee_id` INT,
  `full_name` STRING,
  `first_name` STRING,
  `last_name` STRING,
  `position_id` INT,
  `position_title` STRING,
  `store_id` INT,
  `department_id` INT,
  `birth_date` STRING,
  `hire_date` STRING,
  `end_date` STRING,
  `salary` DOUBLE,
  `supervisor_id` INT,
  `education_level` STRING,
  `marital_status` STRING,
  `gender` STRING
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '{' STORED AS TEXTFILE;

DROP TABLE IF EXISTS `salary`;
CREATE TABLE `salary` (
  `pay_date` STRING,
  `employee_id` INT,
  `department_id` INT,
  `currency_id` INT,
  `salary_paid` DOUBLE,
  `overtime_paid` DOUBLE,
  `vacation_accrued` DOUBLE,
  `vacation_used` DOUBLE
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '{' STORED AS TEXTFILE;

DROP TABLE IF EXISTS `sales_fact_1997`;
CREATE TABLE `sales_fact_1997` (
  `product_id` INT,
  `time_id` INT,
  `customer_id` INT,
  `promotion_id` INT,
  `store_id` INT,
  `store_sales` DOUBLE,
  `store_cost` DOUBLE,
  `unit_sales` DOUBLE
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '{' STORED AS TEXTFILE;

DROP TABLE IF EXISTS `sales_fact_1998`;
CREATE TABLE `sales_fact_1998` (
  `product_id` INT,
  `time_id` INT,
  `customer_id` INT,
  `promotion_id` INT,
  `store_id` INT,
  `store_sales` DOUBLE,
  `store_cost` DOUBLE,
  `unit_sales` DOUBLE
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '{' STORED AS TEXTFILE;

DROP TABLE IF EXISTS `sales_fact_dec_1998`;
CREATE TABLE `sales_fact_dec_1998` (
  `product_id` INT,
  `time_id` INT,
  `customer_id` INT,
  `promotion_id` INT,
  `store_id` INT,
  `store_sales` DOUBLE,
  `store_cost` DOUBLE,
  `unit_sales` DOUBLE
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '{' STORED AS TEXTFILE;

DROP TABLE IF EXISTS `store`;
CREATE TABLE `store` (
  `store_id` INT,
  `store_type` STRING,
  `region_id` INT,
  `store_name` STRING,
  `store_number` INT,
  `store_street_address` STRING,
  `store_city` STRING,
  `store_state` STRING,
  `store_postal_code` STRING,
  `store_country` STRING,
  `store_manager` STRING,
  `store_phone` STRING,
  `store_fax` STRING,
  `first_opened_date` STRING,
  `last_remodel_date` STRING,
  `store_sqft` INT,
  `grocery_sqft` INT,
  `frozen_sqft` INT,
  `meat_sqft` INT,
  `coffee_bar` INT,
  `video_store` INT,
  `salad_bar` INT,
  `prepared_food` INT,
  `florist` INT
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '{' STORED AS TEXTFILE;

DROP TABLE IF EXISTS `store_ragged`;
CREATE TABLE `store_ragged` (
  `store_id` INT,
  `store_type` STRING,
  `region_id` INT,
  `store_name` STRING,
  `store_number` INT,
  `store_street_address` STRING,
  `store_city` STRING,
  `store_state` STRING,
  `store_postal_code` STRING,
  `store_country` STRING,
  `store_manager` STRING,
  `store_phone` STRING,
  `store_fax` STRING,
  `first_opened_date` STRING,
  `last_remodel_date` STRING,
  `store_sqft` INT,
  `grocery_sqft` INT,
  `frozen_sqft` INT,
  `meat_sqft` INT,
  `coffee_bar` INT,
  `video_store` INT,
  `salad_bar` INT,
  `prepared_food` INT,
  `florist` INT
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '{' STORED AS TEXTFILE;

DROP TABLE IF EXISTS `time_by_day`;
CREATE TABLE `time_by_day` (
  `time_id` INT,
  `the_date` STRING,
  `the_day` STRING,
  `the_month` STRING,
  `the_year` INT,
  `day_of_month` INT,
  `week_of_year` INT,
  `month_of_year` INT,
  `quarter` STRING,
  `fiscal_period` STRING
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '{' STORED AS TEXTFILE;

DROP TABLE IF EXISTS `warehouse`;
CREATE TABLE `warehouse` (
  `warehouse_id` INT,
  `warehouse_class_id` INT,
  `stores_id` INT,
  `warehouse_name` STRING,
  `wa_address1` STRING,
  `wa_address2` STRING,
  `wa_address3` STRING,
  `wa_address4` STRING,
  `warehouse_city` STRING,
  `warehouse_state_province` STRING,
  `warehouse_postal_code` STRING,
  `warehouse_country` STRING,
  `warehouse_owner_name` STRING,
  `warehouse_phone` STRING,
  `warehouse_fax` STRING
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '{' STORED AS TEXTFILE;

DROP TABLE IF EXISTS `warehouse_class`;
CREATE TABLE `warehouse_class` (
  `warehouse_class_id` INT,
  `description` STRING
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '{' STORED AS TEXTFILE;