http://git-wip-us.apache.org/repos/asf/carbondata/blob/ffddba70/datamap/mv/plan/src/main/scala/org/apache/carbondata/mv/testutil/Tpcds_1_4_Tables.scala ---------------------------------------------------------------------- diff --git a/datamap/mv/plan/src/main/scala/org/apache/carbondata/mv/testutil/Tpcds_1_4_Tables.scala b/datamap/mv/plan/src/main/scala/org/apache/carbondata/mv/testutil/Tpcds_1_4_Tables.scala new file mode 100644 index 0000000..97772c7 --- /dev/null +++ b/datamap/mv/plan/src/main/scala/org/apache/carbondata/mv/testutil/Tpcds_1_4_Tables.scala @@ -0,0 +1,819 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one or more + * contributor license agreements. See the NOTICE file distributed with + * this work for additional information regarding copyright ownership. + * The ASF licenses this file to You under the Apache License, Version 2.0 + * (the "License"); you may not use this file except in compliance with + * the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ + +package org.apache.carbondata.mv.testutil + +object Tpcds_1_4_Tables { + val tpcds1_4Tables = Seq[String]( + s""" + |CREATE TABLE catalog_sales ( + | `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` bigint, + | `cs_quantity` int, + | `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) + |) + |STORED BY 'org.apache.carbondata.format' + """.stripMargin.trim, + s""" + |CREATE TABLE catalog_returns ( + | `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` bigint, + | `cr_return_quantity` int, + | `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) + |) + |STORED BY 'org.apache.carbondata.format' + """.stripMargin.trim, + s""" + |CREATE TABLE inventory ( + | `inv_date_sk` int, + | `inv_item_sk` int, + | `inv_warehouse_sk` int, + | `inv_quantity_on_hand` int + |) + |STORED BY 'org.apache.carbondata.format' + """.stripMargin.trim, + s""" + |CREATE TABLE store_sales ( + | `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` bigint, + | `ss_quantity` int, + | `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) + |) + |STORED BY 'org.apache.carbondata.format' + """.stripMargin.trim, + s""" + |CREATE TABLE store_returns ( + | `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` bigint, + | `sr_return_quantity` int, + | `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) + |) + |STORED BY 'org.apache.carbondata.format' + """.stripMargin.trim, + s""" + |CREATE TABLE web_sales ( + | `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` bigint, + | `ws_quantity` int, + | `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) + |) + |STORED BY 'org.apache.carbondata.format' + """.stripMargin.trim, + s""" + |CREATE TABLE web_returns ( + | `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` bigint, + | `wr_return_quantity` int, + | `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) + |) + |STORED BY 'org.apache.carbondata.format' + """.stripMargin.trim, + s""" + |CREATE TABLE call_center ( + | `cc_call_center_sk` int, + | `cc_call_center_id` string, + | `cc_rec_start_date` date, + | `cc_rec_end_date` date, + | `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` decimal(5,2), + | `cc_tax_percentage` decimal(5,2) + |) + |STORED BY 'org.apache.carbondata.format' + """.stripMargin.trim, + s""" + |CREATE TABLE catalog_page ( + | `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 + |) + |STORED BY 'org.apache.carbondata.format' + """.stripMargin.trim, + s""" + |CREATE TABLE customer ( + | `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 + |) + |STORED BY 'org.apache.carbondata.format' + """.stripMargin.trim, + s""" + |CREATE TABLE customer_address ( + | `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` decimal(5,2), + | `ca_location_type` string + |) + |STORED BY 'org.apache.carbondata.format' + """.stripMargin.trim, + s""" + |CREATE TABLE customer_demographics ( + | `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 + |) + |STORED BY 'org.apache.carbondata.format' + """.stripMargin.trim, + s""" + |CREATE TABLE date_dim ( + | `d_date_sk` int, + | `d_date_id` string, + | `d_date` date, + | `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 + |) + |STORED BY 'org.apache.carbondata.format' + """.stripMargin.trim, + s""" + |CREATE TABLE household_demographics ( + | `hd_demo_sk` int, + | `hd_income_band_sk` int, + | `hd_buy_potential` string, + | `hd_dep_count` int, + | `hd_vehicle_count` int + |) + |STORED BY 'org.apache.carbondata.format' + """.stripMargin.trim, + s""" + |CREATE TABLE income_band ( + | `ib_income_band_sk` int, + | `ib_lower_bound` int, + | `ib_upper_bound` int + |) + |STORED BY 'org.apache.carbondata.format' + """.stripMargin.trim, + s""" + |CREATE TABLE item ( + | `i_item_sk` int, + | `i_item_id` string, + | `i_rec_start_date` date, + | `i_rec_end_date` date, + | `i_item_desc` string, + | `i_current_price` decimal(7,2), + | `i_wholesale_cost` decimal(7,2), + | `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 + |) + |STORED BY 'org.apache.carbondata.format' + """.stripMargin.trim, + s""" + |CREATE TABLE promotion ( + | `p_promo_sk` int, + | `p_promo_id` string, + | `p_start_date_sk` int, + | `p_end_date_sk` int, + | `p_item_sk` int, + | `p_cost` decimal(15,2), + | `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 + |) + |STORED BY 'org.apache.carbondata.format' + """.stripMargin.trim, + s""" + |CREATE TABLE reason ( + | `r_reason_sk` int, + | `r_reason_id` string, + | `r_reason_desc` string + |) + |STORED BY 'org.apache.carbondata.format' + """.stripMargin.trim, + s""" + |CREATE TABLE ship_mode ( + | `sm_ship_mode_sk` int, + | `sm_ship_mode_id` string, + | `sm_type` string, + | `sm_code` string, + | `sm_carrier` string, + | `sm_contract` string + |) + |STORED BY 'org.apache.carbondata.format' + """.stripMargin.trim, + s""" + |CREATE TABLE store ( + | `s_store_sk` int, + | `s_store_id` string, + | `s_rec_start_date` date, + | `s_rec_end_date` date, + | `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` decimal(5,2), + | `s_tax_precentage` decimal(5,2) + |) + |STORED BY 'org.apache.carbondata.format' + """.stripMargin.trim, + s""" + |CREATE TABLE time_dim ( + | `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 + |) + |STORED BY 'org.apache.carbondata.format' + """.stripMargin.trim, + s""" + |CREATE TABLE warehouse ( + | `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` decimal(5,2) + |) + |STORED BY 'org.apache.carbondata.format' + """.stripMargin.trim, + s""" + |CREATE TABLE web_page ( + | `wp_web_page_sk` int, + | `wp_web_page_id` string, + | `wp_rec_start_date` date, + | `wp_rec_end_date` date, + | `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 + |) + |STORED BY 'org.apache.carbondata.format' + """.stripMargin.trim, + s""" + |CREATE TABLE web_site ( + | `web_site_sk` int, + | `web_site_id` string, + | `web_rec_start_date` date, + | `web_rec_end_date` date, + | `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` decimal(5,2), + | `web_tax_percentage` decimal(5,2) + |) + |STORED BY 'org.apache.carbondata.format' + """.stripMargin.trim, + s""" + |CREATE TABLE sdr_dyn_seq_custer_iot_all_hour_60min + |( + | `dim_1` String, + | `dim_51` String, + | `starttime` String, + | `dim_2` String, + | `dim_3` String, + | `dim_4` String, + | `dim_5` String, + | `dim_6` String, + | `dim_7` String, + | `dim_8` String, + | `dim_9` String, + | `dim_10` String, + | `dim_11` String, + | `dim_12` String, + | `dim_13` String, + | `dim_14` String, + | `dim_15` String, + | `dim_16` String, + | `dim_17` String, + | `dim_18` String, + | `dim_19` String, + | `dim_20` String, + | `dim_21` String, + | `dim_22` String, + | `dim_23` String, + | `dim_24` String, + | `dim_25` String, + | `dim_26` String, + | `dim_27` String, + | `dim_28` String, + | `dim_29` String, + | `dim_30` String, + | `dim_31` String, + | `dim_32` String, + | `dim_33` String, + | `dim_34` String, + | `dim_35` String, + | `dim_36` String, + | `dim_37` String, + | `dim_38` String, + | `dim_39` String, + | `dim_40` String, + | `dim_41` String, + | `dim_42` String, + | `dim_43` String, + | `dim_44` String, + | `dim_45` String, + | `dim_46` String, + | `dim_47` String, + | `dim_48` String, + | `dim_49` String, + | `dim_50` String, + | `dim_52` String, + | `dim_53` String, + | `dim_54` String, + | `dim_55` String, + | `dim_56` String, + | `dim_57` String, + | `dim_58` String, + | `dim_59` String, + | `dim_60` String, + | `dim_61` String, + | `dim_62` String, + | `dim_63` String, + | `dim_64` String, + | `dim_65` String, + | `dim_66` String, + | `dim_67` String, + | `dim_68` String, + | `dim_69` String, + | `dim_70` String, + | `dim_71` String, + | `dim_72` String, + | `dim_73` String, + | `dim_74` String, + | `dim_75` String, + | `dim_76` String, + | `dim_77` String, + | `dim_78` String, + | `dim_79` String, + | `dim_80` String, + | `dim_81` String, + | `dim_82` String, + | `dim_83` String, + | `dim_84` String, + | `dim_85` String, + | `dim_86` String, + | `dim_87` String, + | `dim_88` String, + | `dim_89` String, + | `dim_90` String, + | `dim_91` String, + | `dim_92` String, + | `dim_93` String, + | `dim_94` String, + | `dim_95` String, + | `dim_96` String, + | `dim_97` String, + | `dim_98` String, + | `dim_99` String, + | `dim_100` String, + | `counter_1` double, + | `counter_2` double, + | `counter_3` double, + | `counter_4` double, + | `counter_5` double, + | `counter_6` double, + | `counter_7` double, + | `counter_8` double, + | `counter_9` double, + | `counter_10` double, + | `counter_11` double, + | `counter_12` double, + | `counter_13` double, + | `counter_14` double, + | `counter_15` double, + | `counter_16` double, + | `counter_17` double, + | `counter_18` double, + | `counter_19` double, + | `counter_20` double, + | `counter_21` double, + | `counter_22` double, + | `counter_23` double, + | `counter_24` double, + | `counter_25` double, + | `counter_26` double, + | `counter_27` double, + | `counter_28` double, + | `counter_29` double, + | `counter_30` double, + | `counter_31` double, + | `counter_32` double, + | `counter_33` double, + | `counter_34` double, + | `counter_35` double, + | `counter_36` double, + | `counter_37` double, + | `counter_38` double, + | `counter_39` double, + | `counter_40` double, + | `counter_41` double, + | `counter_42` double, + | `counter_43` double, + | `counter_44` double, + | `counter_45` double, + | `counter_46` double, + | `counter_47` double, + | `counter_48` double, + | `counter_49` double, + | `counter_50` double, + | `counter_51` double, + | `counter_52` double, + | `counter_53` double, + | `counter_54` double, + | `counter_55` double, + | `counter_56` double, + | `counter_57` double, + | `counter_58` double, + | `counter_59` double, + | `counter_60` double, + | `counter_61` double, + | `counter_62` double, + | `counter_63` double, + | `counter_64` double, + | `counter_65` double, + | `counter_66` double, + | `counter_67` double, + | `counter_68` double, + | `counter_69` double, + | `counter_70` double, + | `counter_71` double, + | `counter_72` double, + | `counter_73` double, + | `counter_74` double, + | `counter_75` double, + | `counter_76` double, + | `counter_77` double, + | `counter_78` double, + | `counter_79` double, + | `counter_80` double, + | `counter_81` double, + | `counter_82` double, + | `counter_83` double, + | `counter_84` double, + | `counter_85` double, + | `counter_86` double, + | `counter_87` double, + | `counter_88` double, + | `counter_89` double, + | `counter_90` double, + | `counter_91` double, + | `counter_92` double, + | `counter_93` double, + | `counter_94` double, + | `counter_95` double, + | `counter_96` double, + | `counter_97` double, + | `counter_98` double, + | `counter_99` double, + | `counter_100` double, + | `batchno` double + |) + |STORED BY 'org.apache.carbondata.format' + """.stripMargin.trim, + s""" + |CREATE TABLE dim_apn_iot + |( + | `city_ascription` String, + | `industry` String, + | `apn_name` String, + | `service_level` String, + | `customer_name` String, + | `id` bigint + |) + |STORED BY 'org.apache.carbondata.format' + """.stripMargin.trim, + s""" + |CREATE TABLE tradeflow_all ( + | m_month smallint, + | hs_code string , + | country smallint, + | dollar_value double , + | quantity double , + | unit smallint, + | b_country smallint, + | imex smallint, + | y_year smallint) + |STORED BY 'org.apache.carbondata.format' + """.stripMargin.trim, + s""" + |CREATE TABLE country ( + | countryid smallint , + | country_en string , + | country_cn string ) + |STORED BY 'org.apache.carbondata.format' + """.stripMargin.trim, + s""" + |CREATE TABLE updatetime ( + | countryid smallint , + | imex smallint , + | hs_len smallint , + | minstartdate string , + | startdate string , + | newdate string , + | minnewdate string ) + |STORED BY 'org.apache.carbondata.format' + """.stripMargin.trim + ) +}
http://git-wip-us.apache.org/repos/asf/carbondata/blob/ffddba70/datamap/mv/plan/src/test/scala/org/apache/carbondata/mv/TestSQLBatch.scala ---------------------------------------------------------------------- diff --git a/datamap/mv/plan/src/test/scala/org/apache/carbondata/mv/TestSQLBatch.scala b/datamap/mv/plan/src/test/scala/org/apache/carbondata/mv/TestSQLBatch.scala new file mode 100644 index 0000000..3806dac --- /dev/null +++ b/datamap/mv/plan/src/test/scala/org/apache/carbondata/mv/TestSQLBatch.scala @@ -0,0 +1,584 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one or more + * contributor license agreements. See the NOTICE file distributed with + * this work for additional information regarding copyright ownership. + * The ASF licenses this file to You under the Apache License, Version 2.0 + * (the "License"); you may not use this file except in compliance with + * the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ + +package org.apache.carbondata.mv + +object TestSQLBatch { + + val testSQLBatch = Seq[String]( + s""" + |SELECT f1.A,COUNT(*) AS B + |FROM + | fact f1 + | JOIN dim d1 ON (f1.K = d1.K) + |WHERE f1.E IS NULL AND (f1.C > d1.E OR d1.E = 3) + |GROUP BY f1.A + """.stripMargin.trim, + s""" + |SELECT f1.A,COUNT(*) AS B + |FROM + | fact f1 + | JOIN dim d1 ON (f1.K = d1.K) + | JOIN dim1 d2 ON (f1.K = d2.K AND d2.G > 0) + |WHERE f1.E IS NULL AND f1.C > d1.E + |GROUP BY f1.A + """.stripMargin.trim, + s""" + |SELECT substr(item.i_item_desc,1,30) itemdesc, item.i_item_sk item_sk, date_dim.d_date solddate, count(*) cnt + |FROM date_dim, store_sales, item + |WHERE date_dim.d_date_sk = store_sales.ss_sold_date_sk + | AND store_sales.ss_item_sk = item.i_item_sk + | AND date_dim.d_year in (2000, 2000+1, 2000+2, 2000+3) + |GROUP BY substr(item.i_item_desc,1,30), item.i_item_sk,date_dim.d_date + """.stripMargin.trim, + s""" + |SELECT item.i_item_desc, item.i_category, item.i_class, item.i_current_price, + | SUM(store_sales.ss_ext_sales_price) as itemrevenue, + | SUM(store_sales.ss_ext_sales_price)*100/sum(sum(store_sales.ss_ext_sales_price)) over (partition by item.i_class) as revenueratio + |FROM date_dim, store_sales, item + |WHERE date_dim.d_date_sk = store_sales.ss_sold_date_sk + | AND store_sales.ss_item_sk = item.i_item_sk + | AND item.i_category in ('Sport', 'Books', 'Home') + | AND date_dim.d_date between cast('1999-02-22' as date) AND (cast('1999-02-22' as date) + interval 30 days) + |GROUP BY item.i_item_id, item.i_item_desc, item.i_category, item.i_class, item.i_current_price + """.stripMargin.trim, + s""" + |SELECT 'store' channel, store_sales.ss_store_sk col_name, date_dim.d_year, date_dim.d_qoy, + | item.i_category, SUM(store_sales.ss_ext_sales_price) ext_sales_price + |FROM date_dim, store_sales, item + |WHERE store_sales.ss_store_sk IS NULL + | AND store_sales.ss_sold_date_sk = date_dim.d_date_sk + | AND store_sales.ss_item_sk = item.i_item_sk + |GROUP BY channel, store_sales.ss_store_sk, date_dim.d_year, date_dim.d_qoy, item.i_category + """.stripMargin.trim, + s""" + |SELECT 'store' channel, store_sales.ss_store_sk col_name, date_dim.d_year, date_dim.d_qoy, + | item.i_category, SUM(store_sales.ss_ext_sales_price) ext_sales_price + |FROM date_dim, store_sales, item + |WHERE store_sales.ss_store_sk IS NULL + | AND store_sales.ss_sold_date_sk = date_dim.d_date_sk + | AND store_sales.ss_item_sk = item.i_item_sk + |GROUP BY store_sales.ss_store_sk, date_dim.d_year, date_dim.d_qoy, item.i_category + """.stripMargin.trim, + s""" + |SELECT item.i_brand_id brand_id, item.i_brand brand, SUM(ss_ext_sales_price) ext_price + |FROM date_dim, store_sales, item + |WHERE store_sales.ss_sold_date_sk = date_dim.d_date_sk + | AND store_sales.ss_item_sk = item.i_item_sk + | AND item.i_manager_id = 28 + | AND date_dim.d_year = 1999 + | AND date_dim.d_moy = 11 + |GROUP BY item.i_brand_id, item.i_brand + """.stripMargin.trim, + s""" + |SELECT item.i_brand_id brand_id, item.i_brand_id brand, SUM(ss_ext_sales_price) ext_price + |FROM date_dim, store_sales, item + |WHERE store_sales.ss_sold_date_sk = date_dim.d_date_sk + | AND store_sales.ss_item_sk = item.i_item_sk + | AND item.i_manager_id = 28 + | AND date_dim.d_year = 1999 + | AND date_dim.d_moy = 11 + |GROUP BY item.i_brand_id, item.i_class_id,item.i_category_id + """.stripMargin.trim, + s""" + |SELECT 'store' channel, item.i_brand_id, item.i_class_id, item.i_category_id, + | SUM(store_sales.ss_quantity*store_sales.ss_list_price) sales, count(*) number_sales + |FROM date_dim, store_sales, item + |WHERE date_dim.d_date_sk = store_sales.ss_sold_date_sk + | AND store_sales.ss_item_sk = item.i_item_sk + | AND date_dim.d_year = 1999 + 2 + | AND date_dim.d_moy = 11 + |GROUP BY item.i_brand_id, item.i_class_id,item.i_category_id + """.stripMargin.trim, + s""" + |SELECT substr(item.i_item_desc,1,30) itemdesc, item.i_item_sk item_sk, dt.d_date solddate, count(*) cnt + |FROM date_dim dt, store_sales, item + |WHERE dt.d_date_sk = store_sales.ss_sold_date_sk + | AND store_sales.ss_item_sk = item.i_item_sk + | AND dt.d_year in (2000, 2000+1, 2000+2, 2000+3) + |GROUP BY substr(item.i_item_desc,1,30), item.i_item_sk,dt.d_date + """.stripMargin.trim, + s""" + |SELECT fact.B + |FROM + | fact + |UNION ALL + |SELECT fact.B + |FROM + | fact + |UNION ALL + |SELECT fact.B + |FROM + | fact + """.stripMargin.trim, + s""" + |SELECT fact.A + |FROM + | fact + | JOIN dim ON (fact.K = dim.K) + | JOIN dim1 ON (fact.K = dim1.K AND dim1.G > 0) + |WHERE fact.C > dim.E AND dim.E IS NULL + |UNION ALL + |SELECT fact.A + |FROM + | fact + | JOIN dim ON (fact.K = dim.K) + | JOIN dim1 ON (fact.K = dim1.K AND dim1.G > 0) + |WHERE fact.C > dim.E AND dim.E IS NULL + |UNION ALL + |SELECT fact.B + |FROM + | fact + """.stripMargin.trim, + s""" + |SELECT fact.A + |FROM + | fact + | JOIN dim ON (fact.K = dim.K) + | JOIN dim1 ON (fact.K = dim1.K AND dim1.G > 0) + |WHERE fact.C > dim.E AND dim.E IS NULL + |UNION ALL + |SELECT fact.A + |FROM + | fact + | JOIN dim ON (fact.K = dim.K) + | JOIN dim1 ON (fact.K = dim1.K AND dim1.G > 0) + |WHERE fact.C > dim.E AND dim.E IS NULL + """.stripMargin.trim, + s""" + |SELECT fact.A + |FROM + | fact + | JOIN dim ON (fact.K = dim.K) + | JOIN dim1 ON (fact.K = dim1.K AND dim1.G > 0) + |WHERE fact.C > dim.E AND dim.E IS NULL + |UNION ALL + |SELECT fact.B + |FROM + | fact + """.stripMargin.trim, + s""" + |SELECT fact.A + |FROM + | fact + | JOIN dim ON (fact.K = dim.K) + | JOIN dim1 ON (fact.K = dim1.K AND dim1.G > 0) + |WHERE fact.C > dim.E AND dim.E IS NULL + |UNION ALL + |SELECT fact.A + |FROM + | fact + """.stripMargin.trim, + s""" + |SELECT f1.A,f1.B,COUNT(*) AS A + |FROM + | fact f1 + | JOIN dim d1 ON (f1.K = d1.K) + |GROUP BY f1.A,f1.B + """.stripMargin.trim, + s""" + |SELECT f1.A,f1.B,COUNT(*) AS A + |FROM + | fact f1 + | JOIN dim d1 ON (f1.K = d1.K) + |WHERE f1.E IS NULL AND f1.C > d1.E AND f1.B = 2 + |GROUP BY f1.A,f1.B + """.stripMargin.trim, + s""" + |SELECT f1.A,f1.B,COUNT(*) AS A + |FROM + | fact f1 + | JOIN dim d1 ON (f1.K = d1.K) + |WHERE f1.E IS NULL AND f1.C > d1.E AND d1.E = 3 + |GROUP BY f1.A,f1.B + """.stripMargin.trim, + s""" + |SELECT f1.A,f1.B,COUNT(*) AS A + |FROM + | fact f1 + | JOIN dim d1 ON (f1.K = d1.K) + |WHERE f1.E IS NULL AND f1.C > d1.E + |GROUP BY f1.A,f1.B + """.stripMargin.trim, + s""" + |SELECT f1.A,f1.B,COUNT(*) AS A + |FROM + | fact f1 + | JOIN dim d1 ON (f1.K = d1.K) + | JOIN dim d2 ON (f1.K = d2.K AND d2.E > 0) + |WHERE f1.E IS NULL AND f1.C > d1.E + |GROUP BY f1.A,f1.B + """.stripMargin.trim, + s""" + |SELECT fact.A,fact.B,COUNT(*) AS A + |FROM + | fact + | JOIN dim d1 ON (fact.K = d1.K) + | JOIN dim d2 ON (fact.K = d2.K AND d2.E > 0) + |WHERE fact.E IS NULL AND fact.C > d1.E + |GROUP BY fact.A,fact.B + """.stripMargin.trim, + s""" + |SELECT fact.A + |FROM + | fact + | JOIN dim ON (fact.K = dim.K) + | JOIN dim1 ON (fact.K = dim1.K) + |WHERE fact.C > dim.E AND (dim.E IS NULL OR dim1.G IS NULL) + """.stripMargin.trim, + s""" + |SELECT fact.A + |FROM + | fact + | JOIN dim ON (fact.K = dim.K) + | JOIN dim1 ON (fact.K = dim1.K) + |WHERE fact.C > dim.E OR dim1.G IS NULL + """.stripMargin.trim, + s""" + |SELECT fact.A + |FROM + | fact + | JOIN dim ON (fact.K = dim.K) + | JOIN dim1 ON (fact.K = dim1.K AND dim1.G > 0) + |WHERE fact.C > dim.E OR dim.E IS NULL + """.stripMargin.trim, + s""" + |SELECT fact.A + |FROM + | fact + | JOIN dim ON (fact.K = dim.K) + | JOIN dim1 ON (fact.K = dim1.K AND dim1.G > 0) + |WHERE fact.C > dim.E AND dim.E IS NULL + """.stripMargin.trim, + s""" + |SELECT fact.A + |FROM + | fact + | JOIN dim ON (fact.K = dim.K) + | JOIN dim1 ON (fact.K = dim1.K AND dim1.G > 0) + |WHERE fact.C > dim.E + """.stripMargin.trim, + s""" + |SELECT fact.A + |FROM + | fact + | JOIN dim ON (fact.K = dim.K) + | JOIN dim1 ON (fact.K = dim1.K AND dim1.G > 0) + """.stripMargin.trim, + s""" + |SELECT fact.A,fact.B,COUNT(*) AS A + |FROM + | fact + | JOIN dim ON (fact.K = dim.K AND fact.K IS NOT NULL) + | JOIN dim1 ON (fact.K = dim1.K AND dim1.G > 0 AND dim1.K IS NOT NULL) + |WHERE fact.E IS NULL AND fact.C > dim.E + |GROUP BY fact.A,fact.B + """.stripMargin.trim, + s""" + |SELECT fact.A,fact.B,COUNT(*) AS A + |FROM + | fact + | JOIN dim ON (fact.K = dim.K AND fact.K IS NOT NULL) + | JOIN dim1 ON (fact.K = dim1.K AND dim1.G > 0) + |WHERE fact.E IS NULL AND fact.C > dim.E + |GROUP BY fact.A,fact.B + """.stripMargin.trim, + s""" + |SELECT fact.A,fact.B,COUNT(*) AS A + |FROM + | fact + | JOIN dim ON (fact.K = dim.K) + | JOIN dim1 ON (fact.K = dim1.K AND dim1.G > 0) + |WHERE fact.E IS NULL AND fact.C > dim.E + |GROUP BY fact.A,fact.B + """.stripMargin.trim, + s""" + |SELECT fact.A,fact.B,COUNT(*) AS A + |FROM + | fact + | JOIN dim ON (fact.K = dim.K) + | JOIN dim1 ON (fact.K = dim1.K AND dim1.G > 0) + |WHERE fact.C > fact.E AND fact.C > dim.E + |GROUP BY fact.A,fact.B + """.stripMargin.trim, + s""" + |SELECT fact.A,fact.B,COUNT(*) AS A + |FROM + | fact + | JOIN dim ON (fact.K = dim.K) + | JOIN dim1 ON (fact.K = dim1.K) + |WHERE fact.C > fact.E AND (fact.C > dim.E OR dim1.G > 0) + |GROUP BY fact.A,fact.B + """.stripMargin.trim, + s""" + |SELECT fact.A,fact.B,COUNT(*) AS A + |FROM + | fact + | JOIN dim ON (fact.K = dim.K) + | JOIN dim1 ON (fact.K = dim1.K) + |WHERE fact.C > fact.E AND fact.C > dim.E OR dim1.G > 0 + |GROUP BY fact.A,fact.B + """.stripMargin.trim, + s""" + |SELECT fact.A,fact.B,COUNT(*) AS A + |FROM + | fact + | JOIN dim ON (fact.K = dim.K) + | JOIN dim1 ON (fact.K = dim1.K) + |WHERE fact.C > fact.E AND fact.C > dim.E + |GROUP BY fact.A,fact.B + """.stripMargin.trim, + s""" + |SELECT fact.A,fact.B,COUNT(*) AS A + |FROM + | fact + | JOIN dim ON (fact.K = dim.K) + | JOIN dim1 ON (fact.K = dim1.K) + |WHERE fact.C > fact.E OR fact.C > dim.E + |GROUP BY fact.A,fact.B + """.stripMargin.trim, + s""" + |SELECT fact.A,fact.B,COUNT(*) AS A + |FROM + | fact + | JOIN dim ON (fact.K = dim.K) + | JOIN dim1 ON (fact.K = dim1.K) + |WHERE fact.C > fact.E + |GROUP BY fact.A,fact.B + """.stripMargin.trim, + s""" + |SELECT fact.A,fact.B,COUNT(*) AS A + |FROM + | fact + | JOIN dim ON (fact.K = dim.K) + | JOIN dim1 ON (fact.K = dim1.K) + |GROUP BY fact.A,fact.B + """.stripMargin.trim, + s""" + |SELECT fact.A,COUNT(*) AS S1 + |FROM + | fact + | JOIN dim ON (fact.K = dim.K) + | JOIN dim1 ON (fact.K = dim1.K) + |GROUP BY fact.A + |--HAVING COUNT(*) > 5 + """.stripMargin.trim, + s""" + |SELECT fact.A,COUNT(*)--, my_fun(3) AS S1 + |FROM + | fact + | JOIN dim ON (fact.K = dim.K) + | JOIN dim1 ON (fact.K = dim1.K) + |GROUP BY fact.A + """.stripMargin.trim, + s""" + |SELECT fact.A,COUNT(*) AS S1 + |FROM + | fact + | JOIN dim ON (fact.K = dim.K) + | JOIN dim1 ON (fact.K = dim1.K) + |GROUP BY fact.A + """.stripMargin.trim, + s""" + |SELECT fact.A,sum(cast(dim.D as bigint)) AS S1 + |FROM + | fact + | JOIN dim ON (fact.K = dim.K) + | JOIN dim1 ON (fact.K = dim1.K) + |GROUP BY fact.A + """.stripMargin.trim, + s""" + |SELECT FOO.A, sum(cast(FOO.B as bigint)) AS S + |FROM (SELECT fact.A, fact.B + | FROM + | fact + | JOIN dim ON (fact.K = dim.K)) FOO + |GROUP BY FOO.A + """.stripMargin.trim, + s""" + |SELECT FOO.A, sum(cast(FOO.B as bigint)) AS S + |FROM (SELECT fact.A, fact.B + | FROM + | fact + | JOIN dim ON (fact.K = dim.K)) FOO + |GROUP BY FOO.A + """.stripMargin.trim, + s""" + |SELECT f1.A,f1.B,COUNT(*) + |FROM + | fact f1 + | JOIN fact f2 ON (f1.K = f2.K) + | JOIN fact f3 ON (f1.K = f3.K) + |GROUP BY f1.A,f1.B + """.stripMargin.trim, + s""" + |SELECT fact.A,fact.B,sum(cast(dim.D as bigint)) AS S1 + |FROM + | fact + | LEFT OUTER JOIN dim ON (fact.K = dim.K) + |GROUP BY fact.A,fact.B + """.stripMargin.trim, + s""" + |SELECT fact.A,fact.B,fact.C,sum(cast(dim.D as bigint)) AS S1 + |FROM + | fact + | LEFT OUTER JOIN dim ON (fact.K = dim.K) + |GROUP BY fact.A,fact.B,fact.C + """.stripMargin.trim, +// s""" +// |SELECT * +// |FROM fact, dim +// """.stripMargin.trim, + s""" + |SELECT store_sales.ss_store_sk,date_dim.d_year, + | COUNT(*) numsales + |FROM date_dim, store_sales + |WHERE date_dim.d_date_sk = store_sales.ss_sold_date_sk + |GROUP BY store_sales.ss_store_sk,date_dim.d_year GROUPING SETS (store_sales.ss_store_sk,date_dim.d_year) + """.stripMargin.trim, + s""" + |SELECT store_sales.ss_store_sk,date_dim.d_year, + | SUM(store_sales.ss_ext_sales_price) as itemrevenue + |FROM date_dim, store_sales + |WHERE date_dim.d_date_sk = store_sales.ss_sold_date_sk + |GROUP BY CUBE(store_sales.ss_store_sk,date_dim.d_year) + """.stripMargin.trim, + s""" + |SELECT date_dim.d_moy,date_dim.d_qoy, date_dim.d_year, + | SUM(store_sales.ss_ext_sales_price) as itemrevenue + |FROM date_dim, store_sales + |WHERE date_dim.d_date_sk = store_sales.ss_sold_date_sk + |GROUP BY ROLLUP(date_dim.d_moy,date_dim.d_qoy, date_dim.d_year) + """.stripMargin.trim + ) + val testSQLBatch2 = Seq[String]( + s""" + |SELECT f1.A,COUNT(*) AS B + |FROM + | fact f1 + | JOIN dim d1 ON (f1.K = d1.K) + |WHERE f1.E IS NULL AND (f1.C > d1.E OR d1.E = 3) + |GROUP BY f1.A + """.stripMargin.trim, + s""" + |SELECT f1.A,COUNT(*) AS B + |FROM + | fact f1 + | JOIN dim d1 ON (f1.K = d1.K) + | JOIN dim1 d2 ON (f1.K = d2.K AND d2.G > 0) + |WHERE f1.E IS NULL AND f1.C > d1.E + |GROUP BY f1.A + """.stripMargin.trim, + s""" + |SELECT substr(item.i_item_desc,1,30) itemdesc, item.i_item_sk item_sk, date_dim.d_date solddate, count(*) cnt + |FROM date_dim, store_sales, item + |WHERE date_dim.d_date_sk = store_sales.ss_sold_date_sk + | AND store_sales.ss_item_sk = item.i_item_sk + | AND date_dim.d_year in (2000, 2000+1, 2000+2, 2000+3) + |GROUP BY substr(item.i_item_desc,1,30), item.i_item_sk,date_dim.d_date + """.stripMargin.trim, + s""" + |SELECT item.i_item_desc, item.i_category, item.i_class, item.i_current_price, + | SUM(store_sales.ss_ext_sales_price) as itemrevenue, + | SUM(store_sales.ss_ext_sales_price)*100/sum(sum(store_sales.ss_ext_sales_price)) over (partition by item.i_class) as revenueratio + |FROM date_dim, store_sales, item + |WHERE date_dim.d_date_sk = store_sales.ss_sold_date_sk + | AND store_sales.ss_item_sk = item.i_item_sk + | AND item.i_category in ('Sport', 'Books', 'Home') + | AND date_dim.d_date between cast('1999-02-22' as date) AND (cast('1999-02-22' as date) + interval 30 days) + |GROUP BY item.i_item_id, item.i_item_desc, item.i_category, item.i_class, item.i_current_price + """.stripMargin.trim, + s""" + |SELECT 'store' channel, store_sales.ss_store_sk col_name, date_dim.d_year, date_dim.d_qoy, + | item.i_category, SUM(store_sales.ss_ext_sales_price) ext_sales_price + |FROM date_dim, store_sales, item + |WHERE store_sales.ss_store_sk IS NULL + | AND store_sales.ss_sold_date_sk = date_dim.d_date_sk + | AND store_sales.ss_item_sk = item.i_item_sk + |GROUP BY channel, store_sales.ss_store_sk, date_dim.d_year, date_dim.d_qoy, item.i_category + """.stripMargin.trim, + s""" + |SELECT 'store' channel, store_sales.ss_store_sk col_name, date_dim.d_year, date_dim.d_qoy, + | item.i_category, SUM(store_sales.ss_ext_sales_price) ext_sales_price + |FROM date_dim, store_sales, item + |WHERE store_sales.ss_store_sk IS NULL + | AND store_sales.ss_sold_date_sk = date_dim.d_date_sk + | AND store_sales.ss_item_sk = item.i_item_sk + |GROUP BY store_sales.ss_store_sk, date_dim.d_year, date_dim.d_qoy, item.i_category + """.stripMargin.trim, + s""" + |SELECT item.i_brand_id brand_id, item.i_brand brand, SUM(ss_ext_sales_price) ext_price + |FROM date_dim, store_sales, item + |WHERE store_sales.ss_sold_date_sk = date_dim.d_date_sk + | AND store_sales.ss_item_sk = item.i_item_sk + | AND item.i_manager_id = 28 + | AND date_dim.d_year = 1999 + | AND date_dim.d_moy = 11 + |GROUP BY item.i_brand_id, item.i_brand + """.stripMargin.trim, + s""" + |SELECT item.i_brand_id brand_id, item.i_brand_id brand, SUM(ss_ext_sales_price) ext_price + |FROM date_dim, store_sales, item + |WHERE store_sales.ss_sold_date_sk = date_dim.d_date_sk + | AND store_sales.ss_item_sk = item.i_item_sk + | AND item.i_manager_id = 28 + | AND date_dim.d_year = 1999 + | AND date_dim.d_moy = 11 + |GROUP BY item.i_brand_id, item.i_class_id,item.i_category_id + """.stripMargin.trim, + s""" + |SELECT 'store' channel, item.i_brand_id, item.i_class_id, item.i_category_id, + | SUM(store_sales.ss_quantity*store_sales.ss_list_price) sales, count(*) number_sales + |FROM date_dim, store_sales, item + |WHERE date_dim.d_date_sk = store_sales.ss_sold_date_sk + | AND store_sales.ss_item_sk = item.i_item_sk + | AND date_dim.d_year = 1999 + 2 + | AND date_dim.d_moy = 11 + |GROUP BY item.i_brand_id, item.i_class_id,item.i_category_id + """.stripMargin.trim, + s""" + |SELECT substr(item.i_item_desc,1,30) itemdesc, item.i_item_sk item_sk, dt.d_date solddate, count(*) cnt + |FROM date_dim dt, store_sales, item + |WHERE dt.d_date_sk = store_sales.ss_sold_date_sk + | AND store_sales.ss_item_sk = item.i_item_sk + | AND dt.d_year in (2000, 2000+1, 2000+2, 2000+3) + |GROUP BY substr(item.i_item_desc,1,30), item.i_item_sk,dt.d_date + """.stripMargin.trim, + + s""" + |SELECT store_sales.ss_store_sk,date_dim.d_year, + | COUNT(*) numsales + |FROM date_dim, store_sales + |WHERE date_dim.d_date_sk = store_sales.ss_sold_date_sk + |GROUP BY store_sales.ss_store_sk,date_dim.d_year GROUPING SETS (store_sales.ss_store_sk,date_dim.d_year) + """.stripMargin.trim, + s""" + |SELECT store_sales.ss_store_sk,date_dim.d_year, + | SUM(store_sales.ss_ext_sales_price) as itemrevenue + |FROM date_dim, store_sales + |WHERE date_dim.d_date_sk = store_sales.ss_sold_date_sk + |GROUP BY CUBE(store_sales.ss_store_sk,date_dim.d_year) + """.stripMargin.trim, + s""" + |SELECT date_dim.d_moy,date_dim.d_qoy, date_dim.d_year, + | SUM(store_sales.ss_ext_sales_price) as itemrevenue + |FROM date_dim, store_sales + |WHERE date_dim.d_date_sk = store_sales.ss_sold_date_sk + |GROUP BY ROLLUP(date_dim.d_moy,date_dim.d_qoy, date_dim.d_year) + """.stripMargin.trim + ) +} \ No newline at end of file http://git-wip-us.apache.org/repos/asf/carbondata/blob/ffddba70/datamap/mv/plan/src/test/scala/org/apache/carbondata/mv/plans/ExtractJoinConditionsSuite.scala ---------------------------------------------------------------------- diff --git a/datamap/mv/plan/src/test/scala/org/apache/carbondata/mv/plans/ExtractJoinConditionsSuite.scala b/datamap/mv/plan/src/test/scala/org/apache/carbondata/mv/plans/ExtractJoinConditionsSuite.scala new file mode 100644 index 0000000..2e91e80 --- /dev/null +++ b/datamap/mv/plan/src/test/scala/org/apache/carbondata/mv/plans/ExtractJoinConditionsSuite.scala @@ -0,0 +1,67 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one or more + * contributor license agreements. See the NOTICE file distributed with + * this work for additional information regarding copyright ownership. + * The ASF licenses this file to You under the Apache License, Version 2.0 + * (the "License"); you may not use this file except in compliance with + * the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ + +package org.apache.carbondata.mv.plans + +import org.apache.spark.sql.catalyst.dsl.expressions._ +import org.apache.spark.sql.catalyst.dsl.plans._ +import org.apache.spark.sql.catalyst.plans.logical.LocalRelation +import org.apache.spark.sql.catalyst.plans.{Inner, _} + +import org.apache.carbondata.mv.dsl._ +import org.apache.carbondata.mv.testutil.ModularPlanTest + +class ExtractJoinConditionsSuite extends ModularPlanTest { + val testRelation0 = LocalRelation('a.int, 'b.int, 'c.int) + val testRelation1 = LocalRelation('d.int) + val testRelation2 = LocalRelation('b.int,'c.int,'e.int) + + test("join only") { + val left = testRelation0.where('a === 1) + val right = testRelation1 + val originalQuery = + left.join(right, condition = Some("d".attr === "b".attr || "d".attr === "c".attr)).analyze + val modularPlan = originalQuery.modularize + val extracted = modularPlan.extractJoinConditions(modularPlan.children(0),modularPlan.children(1)) + + val correctAnswer = originalQuery match { + case logical.Join(logical.Filter(cond1,logical.LocalRelation(tbl1,_)),logical.LocalRelation(tbl2,_),Inner,Some(cond2)) => + Seq(cond2) + } + + compareExpressions(correctAnswer, extracted) + } + + test("join and filter") { + val left = testRelation0.where('b === 2).subquery('l) + val right = testRelation2.where('b === 2).subquery('r) + val originalQuery = + left.join(right,condition = Some("r.b".attr === 2 && "l.c".attr === "r.c".attr)).analyze + val modularPlan = originalQuery.modularize + val extracted = modularPlan.extractJoinConditions(modularPlan.children(0),modularPlan.children(1)) + + val originalQuery1 = + left.join(right,condition = Some("l.c".attr === "r.c".attr)).analyze + + val correctAnswer = originalQuery1 match { + case logical.Join(logical.Filter(cond1,logical.LocalRelation(tbl1,_)),logical.Filter(cond2,logical.LocalRelation(tbl2,_)),Inner,Some(cond3)) => + Seq(cond3) + } + + compareExpressions(correctAnswer, extracted) + } +} \ No newline at end of file http://git-wip-us.apache.org/repos/asf/carbondata/blob/ffddba70/datamap/mv/plan/src/test/scala/org/apache/carbondata/mv/plans/IsSPJGHSuite.scala ---------------------------------------------------------------------- diff --git a/datamap/mv/plan/src/test/scala/org/apache/carbondata/mv/plans/IsSPJGHSuite.scala b/datamap/mv/plan/src/test/scala/org/apache/carbondata/mv/plans/IsSPJGHSuite.scala new file mode 100644 index 0000000..e80a0cb --- /dev/null +++ b/datamap/mv/plan/src/test/scala/org/apache/carbondata/mv/plans/IsSPJGHSuite.scala @@ -0,0 +1,59 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one or more + * contributor license agreements. See the NOTICE file distributed with + * this work for additional information regarding copyright ownership. + * The ASF licenses this file to You under the Apache License, Version 2.0 + * (the "License"); you may not use this file except in compliance with + * the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ + +package org.apache.carbondata.mv.plans + +import org.apache.spark.sql.catalyst.dsl.expressions._ +import org.apache.spark.sql.catalyst.dsl.plans._ +import org.apache.spark.sql.catalyst.plans.logical.LocalRelation + +import org.apache.carbondata.mv.dsl._ +import org.apache.carbondata.mv.plans.modular.ModularPlan +import org.apache.carbondata.mv.testutil.ModularPlanTest + +/** + * Tests for the isSPJGH function of [[ModularPlan]]. + */ +class IsSPJGHSuite extends ModularPlanTest { + val testRelation0 = LocalRelation('a.int, 'b.int, 'c.int) + val testRelation1 = LocalRelation('d.int, 'e.int) + + def assertIsSPJGH(plan: ModularPlan, result: Boolean = true): Unit = { + if (plan.isSPJGH != result) { + val ps = plan.toString + println(s"Plans should return sameResult = $result\n$ps") + } + } + + test("project only") { + assertIsSPJGH(testRelation0.select('a).analyze.modularize) + assertIsSPJGH(testRelation0.select('a,'b).analyze.modularize) + } + + test("groupby-project") { + assertIsSPJGH(testRelation0.select('a).groupBy('a)('a).select('a).analyze.modularize) + assertIsSPJGH(testRelation0.select('a,'b).groupBy('a,'b)('a,'b).select('a).analyze.modularize) + } + + test("groupby-project-filter") { + assertIsSPJGH(testRelation0.where('a === 1).select('a,'b).groupBy('a,'b)('a,'b).select('a).analyze.modularize) + } + + test("groupby-project-filter-join") { + assertIsSPJGH(testRelation0.where('b === 1).join(testRelation1.where('d === 1),condition = Some("d".attr === "b".attr || "d".attr === "c".attr)).groupBy('b,'c)('b,'c).select('b).analyze.modularize) + } +} http://git-wip-us.apache.org/repos/asf/carbondata/blob/ffddba70/datamap/mv/plan/src/test/scala/org/apache/carbondata/mv/plans/LogicalToModularPlanSuite.scala ---------------------------------------------------------------------- diff --git a/datamap/mv/plan/src/test/scala/org/apache/carbondata/mv/plans/LogicalToModularPlanSuite.scala b/datamap/mv/plan/src/test/scala/org/apache/carbondata/mv/plans/LogicalToModularPlanSuite.scala new file mode 100644 index 0000000..e5b6ca5 --- /dev/null +++ b/datamap/mv/plan/src/test/scala/org/apache/carbondata/mv/plans/LogicalToModularPlanSuite.scala @@ -0,0 +1,196 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one or more + * contributor license agreements. See the NOTICE file distributed with + * this work for additional information regarding copyright ownership. + * The ASF licenses this file to You under the Apache License, Version 2.0 + * (the "License"); you may not use this file except in compliance with + * the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ + +package org.apache.carbondata.mv.plans + +import org.apache.spark.sql.catalyst._ +import org.apache.spark.sql.catalyst.dsl.expressions._ +import org.apache.spark.sql.catalyst.dsl.plans._ +import org.apache.spark.sql.catalyst.expressions.aggregate.Count +import org.apache.spark.sql.catalyst.plans.logical._ +import org.apache.spark.sql.catalyst.plans.{LeftOuter, RightOuter, _} + +import org.apache.carbondata.mv.dsl._ +import org.apache.carbondata.mv.plans.modular.Flags._ +import org.apache.carbondata.mv.plans.modular.{JoinEdge, ModularRelation} +import org.apache.carbondata.mv.testutil.ModularPlanTest + +class LogicalToModularPlanSuite extends ModularPlanTest { + + val testRelation0 = LocalRelation('a.int, 'b.int, 'c.int) + + val testRelation1 = LocalRelation('d.int) + + val testRelation2 = LocalRelation('c.int, 'd.int) + + test("select only") { + val originalQuery = + testRelation0 + .select('a.attr) + .analyze + val modularized = originalQuery.modularize + val correctAnswer = originalQuery match { + case logical.Project(proj,logical.LocalRelation(tbl,_)) => + ModularRelation(null,null,tbl,NoFlags,Seq.empty).select(proj:_*)(tbl:_*)()(Map.empty)() + } + comparePlans(modularized, correctAnswer) + } + + test("select-project-groupby grouping without aggregate function") { + val originalQuery = + testRelation0 + .select('a) + .groupBy('a)('a) + .select('a).analyze + + val modularized = originalQuery.modularize + val correctAnswer = originalQuery match { + case logical.Project(proj1,logical.Aggregate(grp,agg,logical.Project(proj2,logical.LocalRelation(tbl,_)))) => + ModularRelation(null,null,tbl,NoFlags,Seq.empty).select(proj2:_*)(tbl:_*)()(Map.empty)().groupBy(agg:_*)(proj2:_*)(grp:_*).select(proj1:_*)(proj1:_*)()(Map.empty)() + } + comparePlans(modularized, correctAnswer) + } + + test("select-project with filter") { + val originalQuery = + testRelation0 + .where('a + 'b === 1) + .select('a + 'b as 'e) + .analyze + + val modularized = originalQuery.modularize + val correctAnswer = originalQuery match { + case logical.Project(proj,logical.Filter(cond,logical.LocalRelation(tbl,_))) => + ModularRelation(null,null,tbl,NoFlags,Seq.empty).select(proj:_*)(tbl:_*)(cond)(Map.empty)() + } + comparePlans(modularized, correctAnswer) + } + + test("join") { + val left = testRelation0.where('a === 1) + val right = testRelation1 + val originalQuery = + left.join(right, condition = Some("d".attr === "b".attr || "d".attr === "c".attr)).analyze + + val modularized = originalQuery.modularize + val correctAnswer = originalQuery match { + case logical.Join(logical.Filter(cond1,logical.LocalRelation(tbl1,_)),logical.LocalRelation(tbl2,_),Inner,Some(cond2)) => + Seq(ModularRelation(null,null,tbl1,NoFlags,Seq.empty),ModularRelation(null,null,tbl2,NoFlags,Seq.empty)).select(tbl1++tbl2:_*)(tbl1++tbl2:_*)(Seq(cond1,cond2):_*)(Map.empty)(JoinEdge(0,1,Inner)) + } + comparePlans(modularized, correctAnswer) + } + + test("left outer join") { + val left = testRelation0.where('a === 1) + val right = testRelation1 + val originalQuery = + left.join(right, LeftOuter, condition = Some("d".attr === "b".attr || "d".attr === "c".attr)).analyze + + val modularized = originalQuery.modularize + val correctAnswer = originalQuery match { + case logical.Join(logical.Filter(cond1,logical.LocalRelation(tbl1,_)),logical.LocalRelation(tbl2,_),LeftOuter,Some(cond2)) => + Seq(ModularRelation(null,null,tbl1,NoFlags,Seq.empty),ModularRelation(null,null,tbl2,NoFlags,Seq.empty)).select(tbl1++tbl2:_*)(tbl1++tbl2:_*)(Seq(cond1,cond2):_*)(Map.empty)(JoinEdge(0,1,LeftOuter)) + } + comparePlans(modularized, correctAnswer) + } + + test("right outer join") { + val left = testRelation0.where('a === 1) + val right = testRelation1 + val originalQuery = + left.join(right, RightOuter, condition = Some("d".attr === "b".attr || "d".attr === "c".attr)).analyze + + val modularized = originalQuery.modularize + val correctAnswer = originalQuery match { + case logical.Join(logical.Filter(cond1,logical.LocalRelation(tbl1,_)),logical.LocalRelation(tbl2,_),RightOuter,Some(cond2)) => + Seq(ModularRelation(null,null,tbl1,NoFlags,Seq.empty),ModularRelation(null,null,tbl2,NoFlags,Seq.empty)).select(tbl1++tbl2:_*)(tbl1++tbl2:_*)(Seq(cond1,cond2):_*)(Map.empty)(JoinEdge(0,1,RightOuter)) + } + comparePlans(modularized, correctAnswer) + } + + test("joins: conjunctive predicates #1 with alias") { + val left = testRelation0.where('a === 1).subquery('x) + val right = testRelation1.subquery('y) + val originalQuery = + left.join(right, condition = Some("x.b".attr === "y.d".attr)).analyze + + val modularized = analysis.EliminateSubqueryAliases(originalQuery).modularize + val correctAnswer = originalQuery match { + case logical.Join(logical.Filter(cond1,logical.LocalRelation(tbl1,_)),logical.LocalRelation(tbl2,_),Inner,Some(cond2)) => + Seq(ModularRelation(null,null,tbl1,NoFlags,Seq.empty),ModularRelation(null,null,tbl2,NoFlags,Seq.empty)).select(tbl1++tbl2:_*)(tbl1++tbl2:_*)(Seq(cond1,cond2):_*)(Map.empty)(JoinEdge(0,1,Inner)) + } + comparePlans(modularized, correctAnswer) + } + + test("joins: conjunctive predicates #2 with alias") { + val lleft = testRelation0.where('a >= 3).subquery('z) + val left = testRelation0.where('a === 1).subquery('x) + val right = testRelation0.subquery('y) + val originalQuery = + lleft.join( + left.join(right, condition = Some("x.b".attr === "y.b".attr)), + condition = Some("z.a".attr === "x.b".attr)) + .analyze + + val modularized = originalQuery.modularize + val correctAnswer = originalQuery match { + case logical.Join(logical.Filter(cond1,logical.LocalRelation(tbl1,_)),logical.Join(logical.Filter(cond2,logical.LocalRelation(tbl2,_)),logical.LocalRelation(tbl3,_),Inner,Some(cond3)),Inner,Some(cond4)) => + Seq(ModularRelation(null,null,tbl1,NoFlags,Seq.empty),ModularRelation(null,null,tbl2,NoFlags,Seq.empty),ModularRelation(null,null,tbl3,NoFlags,Seq.empty)).select(tbl1++tbl2++tbl3:_*)(tbl1++tbl2++tbl3:_*)(Seq(cond1,cond2,cond3,cond4):_*)(Map.empty)(JoinEdge(0,1,Inner),JoinEdge(1,2,Inner)) + } + comparePlans(modularized, correctAnswer) + } + + test("SPJGH query") { + val left = testRelation0.where('b >= 1).subquery('x) + val right = testRelation2.where('d >= 2).subquery('y) + + val originalQuery = + left.join(right, Inner, Option("x.c".attr ==="y.c".attr)) + .groupBy("x.a".attr)("x.a".attr as 'f, Count("x.b") as 'g) + .select('f) + .where('g > 1).analyze + + val modularized = originalQuery.modularize + val correctAnswer = originalQuery match { + case logical.Project(proj0, logical.Filter(cond1, logical.Project(proj1, logical.Aggregate(grp,agg,logical.Join(logical.Filter(cond2,logical.LocalRelation(tbl1,_)),logical.Filter(cond3,logical.LocalRelation(tbl2,_)),Inner,Some(cond4)))))) => + Seq(ModularRelation(null,null,tbl1,NoFlags,Seq.empty),ModularRelation(null,null,tbl2,NoFlags,Seq.empty)).select(tbl1++tbl2:_*)(tbl1++tbl2:_*)(Seq(cond2,cond3,cond4):_*)(Map.empty)(JoinEdge(0,1,Inner)).groupBy(agg:_*)(tbl1++tbl2:_*)(grp:_*).select(proj0:_*)(proj1:_*)(cond1)(Map.empty)() + } + comparePlans(modularized, correctAnswer) + } + + test("non-SPJGH query") { + val mqoAnswer = try testRelation0.where('b > 2).select('a).orderBy('a.asc).analyze.modularize catch { + case e: Exception => + s""" + |Exception thrown while modularizing query: + |== Exception == + |$e + """.stripMargin.trim + } + val correctAnswer = + s""" + |Exception thrown while modularizing query: + |== Exception == + |java.lang.UnsupportedOperationException: unsupported operation: No modular plan for + |Sort [a#0 ASC NULLS FIRST], true + |+- Project [a#0] + | +- Filter (b#1 > 2) + | +- LocalRelation <empty>, [a#0, b#1, c#2] + """.stripMargin.trim + compareMessages(mqoAnswer.toString,correctAnswer) + } +} \ No newline at end of file http://git-wip-us.apache.org/repos/asf/carbondata/blob/ffddba70/datamap/mv/plan/src/test/scala/org/apache/carbondata/mv/plans/ModularToSQLSuite.scala ---------------------------------------------------------------------- diff --git a/datamap/mv/plan/src/test/scala/org/apache/carbondata/mv/plans/ModularToSQLSuite.scala b/datamap/mv/plan/src/test/scala/org/apache/carbondata/mv/plans/ModularToSQLSuite.scala new file mode 100644 index 0000000..26f68fe --- /dev/null +++ b/datamap/mv/plan/src/test/scala/org/apache/carbondata/mv/plans/ModularToSQLSuite.scala @@ -0,0 +1,164 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one or more + * contributor license agreements. See the NOTICE file distributed with + * this work for additional information regarding copyright ownership. + * The ASF licenses this file to You under the Apache License, Version 2.0 + * (the "License"); you may not use this file except in compliance with + * the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ + +package org.apache.carbondata.mv.plans + +import org.scalatest.BeforeAndAfterAll + +import org.apache.carbondata.mv.dsl._ +import org.apache.carbondata.mv.testutil.ModularPlanTest + +class ModularToSQLSuite extends ModularPlanTest with BeforeAndAfterAll { + import org.apache.carbondata.mv.TestSQLBatch._ + + override protected def beforeAll(): Unit = { + drop + + sql( + s""" + |CREATE TABLE Fact ( + | `A` int, + | `B` int, + | `C` int, + | `E` int, + | `K` int + |) + |ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' + |STORED AS TEXTFILE + """.stripMargin.trim + ) + + sql( + s""" + |CREATE TABLE Dim ( + | `D` int, + | `E` int, + | `K` int + |) + |ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' + |STORED AS TEXTFILE + """.stripMargin.trim + ) + + sql( + s""" + |CREATE TABLE Dim1 ( + | `F` int, + | `G` int, + | `K` int + |) + |ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' + |STORED AS TEXTFILE + """.stripMargin.trim + ) + + sql( + s""" + |CREATE TABLE store_sales ( + | `ss_sold_date_sk` int, + | `ss_item_sk` int, + | `ss_quantity` int, + | `ss_list_price` decimal(7,2), + | `ss_ext_sales_price` decimal(7,2), + | `ss_store_sk` int + |) + |ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' + |STORED AS TEXTFILE + """.stripMargin.trim + ) + + sql( + s""" + |CREATE TABLE date_dim ( + | `d_date_sk` int, + | `d_date` date, + | `d_year` int, + | `d_moy` int, + | `d_qoy` int + |) + |ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' + |STORED AS TEXTFILE + """.stripMargin.trim + ) + + sql( + s""" + |CREATE TABLE item ( + | `i_item_sk` int, + | `i_item_id` string, + | `i_brand` string, + | `i_brand_id` int, + | `i_item_desc` string, + | `i_class_id` int, + | `i_class` string, + | `i_category` string, + | `i_category_id` int, + | `i_manager_id` int, + | `i_current_price` decimal(7,2) + |) + |ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' + |STORED AS TEXTFILE + """.stripMargin.trim + ) + + sqlContext.udf.register("my_fun", (s: Integer) => s) + } + + + private def drop = { + sql(s"drop table if exists Fact") + sql(s"drop table if exists Dim") + sql(s"drop table if exists Dim1") + sql(s"drop table if exists store_sales") + sql(s"drop table if exists date_dim") + sql(s"drop table if exists item") + } + + test("convert modular plans to sqls") { + testSQLBatch.foreach { query => + testPlan(query) + } + } + + private def testPlan(query: String) = { + val analyzed = sql(query).queryExecution.analyzed + val optimized = analyzed.optimize + val modularPlan = analyzed.optimize.modularize + + println(s"\n\n===== ACTUAL QUERY =====\n\n${ query } \n") + + println(s"\n\n===== MODULAR PLAN =====\n\n${ modularPlan.treeString } \n") + + val compactSql = modularPlan.asCompactSQL + val convertedSql = modularPlan.asOneLineSQL + + println(s"\n\n===== CONVERTED SQL =====\n\n$compactSql \n") + + val analyzed1 = sql(convertedSql).queryExecution.analyzed + val modularPlan1 = analyzed1.optimize.modularize + + println(s"\n\n===== CONVERTED SQL =====\n\n$compactSql \n") + + println(s"\n\n===== MODULAR PLAN1 =====\n\n${ modularPlan1.treeString } \n") + + comparePlans(modularPlan, modularPlan1) + } + + override protected def afterAll(): Unit = { + drop + } +} \ No newline at end of file http://git-wip-us.apache.org/repos/asf/carbondata/blob/ffddba70/datamap/mv/plan/src/test/scala/org/apache/carbondata/mv/plans/SignatureSuite.scala ---------------------------------------------------------------------- diff --git a/datamap/mv/plan/src/test/scala/org/apache/carbondata/mv/plans/SignatureSuite.scala b/datamap/mv/plan/src/test/scala/org/apache/carbondata/mv/plans/SignatureSuite.scala new file mode 100644 index 0000000..c64826f --- /dev/null +++ b/datamap/mv/plan/src/test/scala/org/apache/carbondata/mv/plans/SignatureSuite.scala @@ -0,0 +1,104 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one or more + * contributor license agreements. See the NOTICE file distributed with + * this work for additional information regarding copyright ownership. + * The ASF licenses this file to You under the Apache License, Version 2.0 + * (the "License"); you may not use this file except in compliance with + * the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ + +package org.apache.carbondata.mv.plans + +import org.apache.spark.sql.catalyst.util._ +import org.scalatest.BeforeAndAfterAll + +import org.apache.carbondata.mv.dsl._ +import org.apache.carbondata.mv.plans.modular.ModularPlanSignatureGenerator +import org.apache.carbondata.mv.testutil.ModularPlanTest +import org.apache.carbondata.mv.testutil.Tpcds_1_4_Tables.tpcds1_4Tables + +class SignatureSuite extends ModularPlanTest with BeforeAndAfterAll { + import org.apache.carbondata.mv.TestSQLBatch._ + + override protected def beforeAll(): Unit = { + sql("drop database if exists tpcds1 cascade") + sql("create database tpcds1") + sql("use tpcds1") + tpcds1_4Tables.foreach { create_table => + sql(create_table) + } + + sql( + s""" + |CREATE TABLE Fact ( + | `A` int, + | `B` int, + | `C` int, + | `E` int, + | `K` int + |) + |ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' + |STORED AS TEXTFILE + """.stripMargin.trim + ) + + sql( + s""" + |CREATE TABLE Dim ( + | `D` int, + | `E` int, + | `K` int + |) + |ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' + |STORED AS TEXTFILE + """.stripMargin.trim + ) + + sql( + s""" + |CREATE TABLE Dim1 ( + | `F` int, + | `G` int, + | `K` int + |) + |ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' + |STORED AS TEXTFILE + """.stripMargin.trim + ) + + sqlContext.udf.register("my_fun", (s: Integer) => s) + } + + + test("test signature computing") { + + testSQLBatch.foreach { query => + val analyzed = sql(query).queryExecution.analyzed + val modularPlan = analyzed.optimize.modularize + val sig = ModularPlanSignatureGenerator.generate(modularPlan) + sig match { + case Some(s) if (s.groupby != true || s.datasets != Set("default.fact","default.dim")) => + println( + s""" + |=== FAIL: signature do not match === + |${sideBySide(s.groupby.toString, true.toString).mkString("\n")} + |${sideBySide(s.datasets.toString, Set("Fact","Dim").toString).mkString("\n")} + """.stripMargin) + case _ => + } + } + } + + override protected def afterAll(): Unit = { + sql("use default") + sql("drop database if exists tpcds1 cascade") + } +} \ No newline at end of file http://git-wip-us.apache.org/repos/asf/carbondata/blob/ffddba70/integration/spark-common/src/main/scala/org/apache/carbondata/spark/util/CarbonScalaUtil.scala ---------------------------------------------------------------------- diff --git a/integration/spark-common/src/main/scala/org/apache/carbondata/spark/util/CarbonScalaUtil.scala b/integration/spark-common/src/main/scala/org/apache/carbondata/spark/util/CarbonScalaUtil.scala index b851599..b43ae3f 100644 --- a/integration/spark-common/src/main/scala/org/apache/carbondata/spark/util/CarbonScalaUtil.scala +++ b/integration/spark-common/src/main/scala/org/apache/carbondata/spark/util/CarbonScalaUtil.scala @@ -44,7 +44,7 @@ import org.apache.carbondata.core.keygenerator.directdictionary.DirectDictionary import org.apache.carbondata.core.metadata.ColumnIdentifier import org.apache.carbondata.core.metadata.datatype.{DataType => CarbonDataType, DataTypes => CarbonDataTypes, StructField => CarbonStructField} import org.apache.carbondata.core.metadata.encoder.Encoding -import org.apache.carbondata.core.metadata.schema.table.{CarbonTable, DataMapSchemaStorageProvider} +import org.apache.carbondata.core.metadata.schema.table.{CarbonTable, DataMapSchema, DataMapSchemaStorageProvider} import org.apache.carbondata.core.metadata.schema.table.column.{CarbonColumn, ColumnSchema} import org.apache.carbondata.core.util.DataTypeUtil import org.apache.carbondata.processing.exception.DataLoadingException @@ -601,11 +601,15 @@ object CarbonScalaUtil { /** * Create datamap provider using class name */ - def createDataMapProvider(className: String, sparkSession: SparkSession, - storageProvider: DataMapSchemaStorageProvider): Object = { + def createDataMapProvider( + className: String, + sparkSession: SparkSession, + table: CarbonTable, + schema: DataMapSchema): Object = { CarbonReflectionUtils.createObject( className, + table, sparkSession, - storageProvider)._1.asInstanceOf[Object] + schema)._1.asInstanceOf[Object] } } http://git-wip-us.apache.org/repos/asf/carbondata/blob/ffddba70/integration/spark-common/src/main/scala/org/apache/spark/sql/util/SparkSQLUtil.scala ---------------------------------------------------------------------- diff --git a/integration/spark-common/src/main/scala/org/apache/spark/sql/util/SparkSQLUtil.scala b/integration/spark-common/src/main/scala/org/apache/spark/sql/util/SparkSQLUtil.scala index 370f80c..2e31a82 100644 --- a/integration/spark-common/src/main/scala/org/apache/spark/sql/util/SparkSQLUtil.scala +++ b/integration/spark-common/src/main/scala/org/apache/spark/sql/util/SparkSQLUtil.scala @@ -17,9 +17,14 @@ package org.apache.spark.sql.util -import org.apache.spark.sql.SparkSession +import org.apache.spark.sql.{DataFrame, Dataset, SparkSession} +import org.apache.spark.sql.catalyst.plans.logical.LogicalPlan import org.apache.spark.sql.internal.SessionState object SparkSQLUtil { def sessionState(sparkSession: SparkSession): SessionState = sparkSession.sessionState + + def execute(logicalPlan: LogicalPlan, sparkSession: SparkSession): DataFrame = { + Dataset.ofRows(sparkSession, logicalPlan) + } } http://git-wip-us.apache.org/repos/asf/carbondata/blob/ffddba70/integration/spark2/src/main/java/org/apache/carbondata/datamap/DataMapManager.java ---------------------------------------------------------------------- diff --git a/integration/spark2/src/main/java/org/apache/carbondata/datamap/DataMapManager.java b/integration/spark2/src/main/java/org/apache/carbondata/datamap/DataMapManager.java index 3fe1f0d..1126b2e 100644 --- a/integration/spark2/src/main/java/org/apache/carbondata/datamap/DataMapManager.java +++ b/integration/spark2/src/main/java/org/apache/carbondata/datamap/DataMapManager.java @@ -21,12 +21,15 @@ import org.apache.carbondata.common.exceptions.sql.MalformedDataMapCommandExcept import org.apache.carbondata.core.datamap.DataMapProvider; import org.apache.carbondata.core.metadata.schema.table.CarbonTable; import org.apache.carbondata.core.metadata.schema.table.DataMapSchema; +import org.apache.carbondata.spark.util.CarbonScalaUtil; +import static org.apache.carbondata.core.metadata.schema.datamap.DataMapClassProvider.MV; import static org.apache.carbondata.core.metadata.schema.datamap.DataMapClassProvider.PREAGGREGATE; import static org.apache.carbondata.core.metadata.schema.datamap.DataMapClassProvider.TIMESERIES; import org.apache.spark.sql.SparkSession; + public class DataMapManager { private static DataMapManager INSTANCE; @@ -50,6 +53,12 @@ public class DataMapManager { provider = new PreAggregateDataMapProvider(mainTable, dataMapSchema, sparkSession); } else if (dataMapSchema.getProviderName().equalsIgnoreCase(TIMESERIES.toString())) { provider = new TimeseriesDataMapProvider(mainTable, dataMapSchema, sparkSession); + } else if (dataMapSchema.getProviderName().equalsIgnoreCase(MV.toString())) { + provider = (DataMapProvider) CarbonScalaUtil.createDataMapProvider( + "org.apache.carbondata.mv.datamap.MVDataMapProvider", + sparkSession, + mainTable, + dataMapSchema); } else { provider = new IndexDataMapProvider(mainTable, dataMapSchema, sparkSession); } http://git-wip-us.apache.org/repos/asf/carbondata/blob/ffddba70/integration/spark2/src/main/scala/org/apache/spark/sql/execution/command/datamap/CarbonDropDataMapCommand.scala ---------------------------------------------------------------------- diff --git a/integration/spark2/src/main/scala/org/apache/spark/sql/execution/command/datamap/CarbonDropDataMapCommand.scala b/integration/spark2/src/main/scala/org/apache/spark/sql/execution/command/datamap/CarbonDropDataMapCommand.scala index cf5a4ae..a27b694 100644 --- a/integration/spark2/src/main/scala/org/apache/spark/sql/execution/command/datamap/CarbonDropDataMapCommand.scala +++ b/integration/spark2/src/main/scala/org/apache/spark/sql/execution/command/datamap/CarbonDropDataMapCommand.scala @@ -176,7 +176,14 @@ case class CarbonDropDataMapCommand( } } } else { - dropDataMapFromSystemFolder(sparkSession) + try { + dropDataMapFromSystemFolder(sparkSession) + } catch { + case e: Exception => + if (!ifExistsSet) { + throw e + } + } } Seq.empty http://git-wip-us.apache.org/repos/asf/carbondata/blob/ffddba70/integration/spark2/src/main/scala/org/apache/spark/sql/execution/command/table/CarbonDropTableCommand.scala ---------------------------------------------------------------------- diff --git a/integration/spark2/src/main/scala/org/apache/spark/sql/execution/command/table/CarbonDropTableCommand.scala b/integration/spark2/src/main/scala/org/apache/spark/sql/execution/command/table/CarbonDropTableCommand.scala index 61df9b1..9576fb1 100644 --- a/integration/spark2/src/main/scala/org/apache/spark/sql/execution/command/table/CarbonDropTableCommand.scala +++ b/integration/spark2/src/main/scala/org/apache/spark/sql/execution/command/table/CarbonDropTableCommand.scala @@ -29,6 +29,7 @@ import org.apache.spark.sql.execution.command.datamap.CarbonDropDataMapCommand import org.apache.carbondata.common.logging.{LogService, LogServiceFactory} import org.apache.carbondata.core.cache.dictionary.ManageDictionaryAndBTree import org.apache.carbondata.core.datamap.DataMapStoreManager +import org.apache.carbondata.core.datamap.status.DataMapStatusManager import org.apache.carbondata.core.datastore.impl.FileFactory import org.apache.carbondata.core.exception.ConcurrentOperationException import org.apache.carbondata.core.locks.{CarbonLockUtil, ICarbonLock, LockUsage} http://git-wip-us.apache.org/repos/asf/carbondata/blob/ffddba70/pom.xml ---------------------------------------------------------------------- diff --git a/pom.xml b/pom.xml index 7c460e2..7273c76 100644 --- a/pom.xml +++ b/pom.xml @@ -636,6 +636,12 @@ <script.exetension>.bat</script.exetension> </properties> </profile> + <profile> + <id>mv</id> + <modules> + <module>datamap/mv/plan</module> + </modules> + </profile> </profiles> </project>