You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@carbondata.apache.org by ja...@apache.org on 2018/03/09 08:44:23 UTC
[05/12] carbondata git commit: [CARBONDATA-2242] Add Materialized
View modules
http://git-wip-us.apache.org/repos/asf/carbondata/blob/02fd7873/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..fe99829
--- /dev/null
+++ b/datamap/mv/plan/src/main/scala/org/apache/carbondata/mv/testutil/Tpcds_1_4_Tables.scala
@@ -0,0 +1,845 @@
+/*
+ * 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)
+ |)
+ |ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
+ |STORED AS TEXTFILE
+ """.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)
+ |)
+ |ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
+ |STORED AS TEXTFILE
+ """.stripMargin.trim,
+ s"""
+ |CREATE TABLE inventory (
+ | `inv_date_sk` int,
+ | `inv_item_sk` int,
+ | `inv_warehouse_sk` int,
+ | `inv_quantity_on_hand` int
+ |)
+ |ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
+ |STORED AS TEXTFILE
+ """.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)
+ |)
+ |ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
+ |STORED AS TEXTFILE
+ """.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)
+ |)
+ |ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
+ |STORED AS TEXTFILE
+ """.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)
+ |)
+ |ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
+ |STORED AS TEXTFILE
+ """.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)
+ |)
+ |ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
+ |STORED AS TEXTFILE
+ """.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)
+ |)
+ |ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
+ |STORED AS TEXTFILE
+ """.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
+ |)
+ |ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
+ |STORED AS TEXTFILE
+ """.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
+ |)
+ |ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
+ |STORED AS TEXTFILE
+ """.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
+ |)
+ |ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
+ |STORED AS TEXTFILE
+ """.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
+ |)
+ |ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
+ |STORED AS TEXTFILE
+ """.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
+ |)
+ |ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
+ |STORED AS TEXTFILE
+ """.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
+ |)
+ |ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
+ |STORED AS TEXTFILE
+ """.stripMargin.trim,
+ s"""
+ |CREATE TABLE income_band (
+ | `ib_income_band_sk` int,
+ | `ib_lower_bound` int,
+ | `ib_upper_bound` int
+ |)
+ |ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
+ |STORED AS TEXTFILE
+ """.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
+ |)
+ |ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
+ |STORED AS TEXTFILE
+ """.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
+ |)
+ |ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
+ |STORED AS TEXTFILE
+ """.stripMargin.trim,
+ s"""
+ |CREATE TABLE reason (
+ | `r_reason_sk` int,
+ | `r_reason_id` string,
+ | `r_reason_desc` string
+ |)
+ |ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
+ |STORED AS TEXTFILE
+ """.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
+ |)
+ |ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
+ |STORED AS TEXTFILE
+ """.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)
+ |)
+ |ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
+ |STORED AS TEXTFILE
+ """.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
+ |)
+ |ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
+ |STORED AS TEXTFILE
+ """.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)
+ |)
+ |ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
+ |STORED AS TEXTFILE
+ """.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
+ |)
+ |ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
+ |STORED AS TEXTFILE
+ """.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)
+ |)
+ |ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
+ |STORED AS TEXTFILE
+ """.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
+ |)
+ |ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'
+ |STORED AS TEXTFILE
+ """.stripMargin.trim,
+ s"""
+ |CREATE TABLE dim_apn_iot
+ |(
+ | `city_ascription` String,
+ | `industry` String,
+ | `apn_name` String,
+ | `service_level` String,
+ | `customer_name` String,
+ | `id` bigint
+ |)
+ |ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'
+ |STORED AS TEXTFILE
+ """.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 AS parquet
+ """.stripMargin.trim,
+ s"""
+ |CREATE TABLE country (
+ | countryid smallint ,
+ | country_en string ,
+ | country_cn string )
+ |STORED AS parquet
+ """.stripMargin.trim,
+ s"""
+ |CREATE TABLE updatetime (
+ | countryid smallint ,
+ | imex smallint ,
+ | hs_len smallint ,
+ | minstartdate string ,
+ | startdate string ,
+ | newdate string ,
+ | minnewdate string )
+ |STORED AS parquet
+ """.stripMargin.trim
+ )
+}
http://git-wip-us.apache.org/repos/asf/carbondata/blob/02fd7873/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..85aceaf
--- /dev/null
+++ b/datamap/mv/plan/src/test/scala/org/apache/carbondata/mv/TestSQLBatch.scala
@@ -0,0 +1,468 @@
+/*
+ * 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
+ )
+}
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/carbondata/blob/02fd7873/datamap/mv/plan/src/test/scala/org/apache/carbondata/mv/TestSQLBatch2.scala
----------------------------------------------------------------------
diff --git a/datamap/mv/plan/src/test/scala/org/apache/carbondata/mv/TestSQLBatch2.scala b/datamap/mv/plan/src/test/scala/org/apache/carbondata/mv/TestSQLBatch2.scala
new file mode 100644
index 0000000..b413461
--- /dev/null
+++ b/datamap/mv/plan/src/test/scala/org/apache/carbondata/mv/TestSQLBatch2.scala
@@ -0,0 +1,138 @@
+/*
+ * 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 TestSQLBatch2 {
+
+ 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/02fd7873/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..b4bda30
--- /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.{Inner, _}
+import org.apache.spark.sql.catalyst.plans.logical.LocalRelation
+
+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/02fd7873/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/02fd7873/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/02fd7873/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..c874289
--- /dev/null
+++ b/datamap/mv/plan/src/test/scala/org/apache/carbondata/mv/plans/ModularToSQLSuite.scala
@@ -0,0 +1,148 @@
+/*
+ * 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.SparkSession
+import org.scalatest.BeforeAndAfter
+
+import org.apache.carbondata.mv.dsl._
+import org.apache.carbondata.mv.testutil.ModularPlanTest
+
+class ModularToSQLSuite extends ModularPlanTest with BeforeAndAfter {
+ import org.apache.carbondata.mv.TestSQLBatch._
+
+ val spark = SparkSession.builder().master("local").enableHiveSupport().getOrCreate()
+ val testHive = new org.apache.spark.sql.hive.test.TestHiveContext(spark.sparkContext, false)
+ val hiveClient = testHive.sparkSession.metadataHive
+
+ test("convert modular plans to sqls") {
+
+ hiveClient.runSqlHive(
+ 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
+ )
+
+ hiveClient.runSqlHive(
+ s"""
+ |CREATE TABLE Dim (
+ | `D` int,
+ | `E` int,
+ | `K` int
+ |)
+ |ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
+ |STORED AS TEXTFILE
+ """.stripMargin.trim
+ )
+
+ hiveClient.runSqlHive(
+ s"""
+ |CREATE TABLE Dim1 (
+ | `F` int,
+ | `G` int,
+ | `K` int
+ |)
+ |ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
+ |STORED AS TEXTFILE
+ """.stripMargin.trim
+ )
+
+ hiveClient.runSqlHive(
+ 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
+ )
+
+ hiveClient.runSqlHive(
+ 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
+ )
+
+ hiveClient.runSqlHive(
+ 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
+ )
+
+ testHive.udf.register("my_fun", (s: Integer) => s)
+
+ testSQLBatch.foreach { query =>
+ val analyzed = testHive.sql(query).queryExecution.analyzed
+ val optimized = analyzed.optimize
+ val modularPlan = analyzed.optimize.modularize
+
+ 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 = testHive.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)
+ }
+
+ }
+
+}
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/carbondata/blob/02fd7873/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..ee3dd6e
--- /dev/null
+++ b/datamap/mv/plan/src/test/scala/org/apache/carbondata/mv/plans/SignatureSuite.scala
@@ -0,0 +1,97 @@
+/*
+ * 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.SparkSession
+import org.apache.spark.sql.catalyst.util._
+import org.scalatest.BeforeAndAfter
+
+import org.apache.carbondata.mv.testutil.Tpcds_1_4_Tables.tpcds1_4Tables
+import org.apache.carbondata.mv.dsl._
+import org.apache.carbondata.mv.plans.modular.ModularPlanSignatureGenerator
+import org.apache.carbondata.mv.testutil.ModularPlanTest
+
+class SignatureSuite extends ModularPlanTest with BeforeAndAfter {
+ import org.apache.carbondata.mv.TestSQLBatch._
+
+ val spark = SparkSession.builder().master("local").enableHiveSupport().getOrCreate()
+ val testHive = new org.apache.spark.sql.hive.test.TestHiveContext(spark.sparkContext, false)
+ val hiveClient = testHive.sparkSession.metadataHive
+
+ test("test signature computing") {
+
+ tpcds1_4Tables.foreach { create_table =>
+ hiveClient.runSqlHive(create_table)
+ }
+
+ hiveClient.runSqlHive(
+ 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
+ )
+
+ hiveClient.runSqlHive(
+ s"""
+ |CREATE TABLE Dim (
+ | `D` int,
+ | `E` int,
+ | `K` int
+ |)
+ |ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
+ |STORED AS TEXTFILE
+ """.stripMargin.trim
+ )
+
+ hiveClient.runSqlHive(
+ s"""
+ |CREATE TABLE Dim1 (
+ | `F` int,
+ | `G` int,
+ | `K` int
+ |)
+ |ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
+ |STORED AS TEXTFILE
+ """.stripMargin.trim
+ )
+
+ testSQLBatch.foreach { query =>
+ val analyzed = testHive.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 _ =>
+ }
+ }
+ }
+ testHive.sparkSession.cloneSession()
+}
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/carbondata/blob/02fd7873/datamap/mv/plan/src/test/scala/org/apache/carbondata/mv/plans/Tpcds_1_4_BenchmarkSuite.scala
----------------------------------------------------------------------
diff --git a/datamap/mv/plan/src/test/scala/org/apache/carbondata/mv/plans/Tpcds_1_4_BenchmarkSuite.scala b/datamap/mv/plan/src/test/scala/org/apache/carbondata/mv/plans/Tpcds_1_4_BenchmarkSuite.scala
new file mode 100644
index 0000000..c5a0a63
--- /dev/null
+++ b/datamap/mv/plan/src/test/scala/org/apache/carbondata/mv/plans/Tpcds_1_4_BenchmarkSuite.scala
@@ -0,0 +1,88 @@
+/*
+ * 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 scala.util.{Failure, Success, Try}
+
+import org.apache.spark.sql.SparkSession
+import org.scalatest.BeforeAndAfter
+
+import org.apache.carbondata.mv.dsl._
+import org.apache.carbondata.mv.testutil.ModularPlanTest
+
+// scalastyle:off println
+class Tpcds_1_4_BenchmarkSuite extends ModularPlanTest with BeforeAndAfter {
+ import org.apache.carbondata.mv.testutil.Tpcds_1_4_QueryBatch._
+ import org.apache.carbondata.mv.testutil.Tpcds_1_4_Tables._
+
+ val spark = SparkSession.builder().master("local").enableHiveSupport().getOrCreate()
+ // spark.conf.set("spark.sql.crossJoin.enabled", true)
+ val testHive = new org.apache.spark.sql.hive.test.TestHiveContext(spark.sparkContext, false)
+ val hiveClient = testHive.sparkSession.metadataHive
+
+ test("test SQLBuilder using tpc-ds queries") {
+
+ tpcds1_4Tables.foreach { create_table =>
+ hiveClient.runSqlHive(create_table)
+ }
+
+// val dest = "qTradeflow" // this line is for development, comment it out once done
+ val dest = "qSEQ"
+// val dest = "qAggPushDown" // this line is for development, comment it out once done
+// val dest = "q10"
+
+ tpcds1_4Queries.foreach { query =>
+ if (query._1 == dest) { // this line is for development, comment it out once done
+ val analyzed = testHive.sql(query._2).queryExecution.analyzed
+ println(s"""\n\n===== Analyzed Logical Plan for ${query._1} =====\n\n$analyzed \n""")
+
+// val cnonicalizedPlan = new SQLBuilder(analyzed).Canonicalizer.execute(analyzed)
+//
+// Try(new SQLBuilder(analyzed).toSQL) match {
+// case Success(s) => logInfo(s"""\n\n===== CONVERTED back ${query._1} USING SQLBuilder =====\n\n$s \n""")
+// case Failure(e) => logInfo(s"""Cannot convert the logical query plan of ${query._1} back to SQL""")
+// }
+
+ // this Try is for development, comment it out once done
+ Try(analyzed.optimize) match {
+ case Success(o) => {
+ println(s"""\n\n===== Optimized Logical Plan for ${query._1} =====\n\n$o \n""")
+ }
+ case Failure(e) =>
+ }
+
+ val o = analyzed.optimize
+ val o1 = o.modularize
+
+ Try(o.modularize.harmonize) match {
+ case Success(m) => {
+ println(s"""\n\n===== MODULAR PLAN for ${query._1} =====\n\n$m \n""")
+
+ Try(m.asCompactSQL) match {
+ case Success(s) => println(s"\n\n===== CONVERTED SQL for ${query._1} =====\n\n${s}\n")
+ case Failure(e) => println(s"""\n\n===== CONVERTED SQL for ${query._1} failed =====\n\n${e.toString}""")
+ }
+ }
+ case Failure(e) => println(s"""\n\n==== MODULARIZE the logical query plan for ${query._1} failed =====\n\n${e.toString}""")
+ }
+ }
+ }
+
+ }
+}
+// scalastyle:on println
\ No newline at end of file