Re: [ISSUE] carbondata1.5.0 and spark 2.3.2 query plan issue

Posted by aaron on Sep 30, 2018; 11:49pm
URL: http://apache-carbondata-dev-mailing-list-archive.168.s1.nabble.com/Issue-Query-plan-issue-tp64371p64390.html


Hi xm_zzc,

Thanks for you response. I test based on 2.3.2, not test 2.2.2. And I have
merged the fix come from ISSUE
http://apache-carbondata-dev-mailing-list-archive.1130556.n5.nabble.com/Issue-Dictionary-and-S3-td63106.html
and
http://apache-carbondata-dev-mailing-list-archive.1130556.n5.nabble.com/Serious-Issue-Query-get-inconsistent-result-on-carbon1-5-0-td63691.html

Create query:
    val createStoreTableSql = s"""
                                 | CREATE TABLE IF NOT EXISTS storev2(
                                   | market_code STRING,
                                   | device_code STRING,
                                   | country_code STRING,
                                   | category_id INTEGER,
                                   | product_id LONG,
                                   | date TIMESTAMP,
                                   | est_free_app_download LONG,
                                   | est_paid_app_download LONG,
                                   | est_revenue LONG
                                 | )
                                 | STORED BY 'carbondata'
                                 | TBLPROPERTIES(
                                   | 'SORT_COLUMNS'='market_code,
device_code, country_code, category_id, date, product_id',
                                   |
'NO_INVERTED_INDEX'='est_free_app_download, est_paid_app_download,
est_revenue',
                                   | 'DICTIONARY_INCLUDE'='market_code,
device_code, country_code, category_id',
                                   | 'SORT_SCOPE'='GLOBAL_SORT',
                                   | 'CACHE_LEVEL'='BLOCKLET',
                                   | 'TABLE_BLOCKSIZE'='256',
                                 |   'GLOBAL_SORT_PARTITIONS'='3'
                                 | )
      """.stripMargin
 

val createTimeSeriesDayNoProductTableSql = s"""
                                                    | CREATE DATAMAP IF NOT
EXISTS agg_by_day ON TABLE storev2
                                                    | USING 'timeSeries'
                                                    | DMPROPERTIES (
                                                    | 'EVENT_TIME'='date',
                                                    | 'DAY_GRANULARITY'='1')
                                                    | AS SELECT date,
market_code, device_code, country_code, category_id,
                                                    | COUNT(product_id),
COUNT(est_free_app_download), COUNT(est_free_app_download),
COUNT(est_revenue),
                                                    |
SUM(est_free_app_download), MIN(est_free_app_download),
MAX(est_free_app_download),
                                                    |
SUM(est_paid_app_download), MIN(est_paid_app_download),
MAX(est_paid_app_download),
                                                    | SUM(est_revenue),
MIN(est_revenue), MAX(est_revenue)
                                                    | FROM storev2
                                                    | GROUP BY date,
market_code, device_code, country_code, category_id
      """.stripMargin
    carbon.sql(createTimeSeriesDayNoProductTableSql)

   

One of the query:

SELECT timeseries(date, 'DAY') as day, market_code, device_code,
country_code, category_id,
      COUNT(product_id), COUNT(est_free_app_download),
COUNT(est_free_app_download), COUNT(est_revenue),
      sum(est_free_app_download), min(est_free_app_download),
max(est_free_app_download),
      sum(est_paid_app_download), min(est_paid_app_download),
max(est_paid_app_download),
      sum(est_revenue), min(est_revenue), max(est_revenue)
      FROM storev2 WHERE market_code='apple-store' AND
device_code='ios-phone' AND country_code IN ('US', 'CN')
      GROUP BY timeseries(date, 'DAY'), market_code, device_code,
country_code, category_id LIMIT 10;





--
Sent from: http://apache-carbondata-dev-mailing-list-archive.1130556.n5.nabble.com/