Hi All,
It seems that master timeSeries DATAMAP does not work well as 1.4.1, could you please have a look? Demo data: |market_code|device_code| date|country_code|category_id|product_id|revenue| +-----------+-----------+-------------------+------------+-----------+----------+-------+ |apple-store| ios-phone|2018-02-01 00:00:00| CA| 1|1000000000| 73481| |apple-store| ios-phone|2018-02-01 00:00:00| CA| 1|1000000001| 713316| |apple-store| ios-phone|2018-02-01 00:00:00| CA| 1|1000000002| 657503| |apple-store| ios-phone|2018-02-01 00:00:00| CA| 1|1000000003| 764930| |apple-store| ios-phone|2018-02-01 00:00:00| CA| 1|1000000004| 835665| |apple-store| ios-phone|2018-02-01 00:00:00| CA| 1|1000000005| 599234| |apple-store| ios-phone|2018-02-01 00:00:00| CA| 1|1000000006| 22451| |apple-store| ios-phone|2018-02-01 00:00:00| CA| 1|1000000007| 17284| |apple-store| ios-phone|2018-02-01 00:00:00| CA| 1|1000000008| 118846| |apple-store| ios-phone|2018-02-01 00:00:00| CA| 1|1000000009| 735783| |apple-store| ios-phone|2018-02-01 00:00:00| CA| 1|1000000010| 698596| |apple-store| ios-phone|2018-02-01 00:00:00| CA| 1|1000000011| 788919| |apple-store| ios-phone|2018-02-01 00:00:00| CA| 1|1000000012| 817443| |apple-store| ios-phone|2018-02-01 00:00:00| CA| 1|1000000013| 839801| |apple-store| ios-phone|2018-02-01 00:00:00| CA| 1|1000000014| 880020| |apple-store| ios-phone|2018-02-01 00:00:00| CA| 1|1000000015| 808019| |apple-store| ios-phone|2018-02-01 00:00:00| CA| 1|1000000016| 740226| |apple-store| ios-phone|2018-02-01 00:00:00| CA| 1|1000000017| 473469| |apple-store| ios-phone|2018-02-01 00:00:00| CA| 1|1000000018| 322765| SQL: carbon.sql("DROP TABLE IF EXISTS test_store_int") val createMainTableSql = s""" | CREATE TABLE test_store_int( | market_code VARCHAR(50), | device_code VARCHAR(50), | date TIMESTAMP, | country_code CHAR(2), | category_id INTEGER, | product_id LONG, | revenue INTEGER | ) | STORED BY 'carbondata' | TBLPROPERTIES( | 'SORT_COLUMNS'='market_code, device_code, country_code, category_id, date', | 'DICTIONARY_INCLUDE'='market_code, device_code, country_code, category_id, date, product_id', | 'NO_INVERTED_INDEX'='revenue', | 'SORT_SCOPE'='GLOBAL_SORT' | ) """.stripMargin print(createMainTableSql) carbon.sql(createMainTableSql) carbon.sql("DROP DATAMAP test_store_int_agg_by_month ON TABLE test_store_int") val createTimeSeriesTableSql = s""" | CREATE DATAMAP test_store_int_agg_by_month ON TABLE test_store_int | USING 'timeSeries' | DMPROPERTIES ( | 'EVENT_TIME'='date', | 'MONTH_GRANULARITY'='1') | AS SELECT date, market_code, device_code, country_code, category_id, product_id, sum(revenue), count(revenue), min(revenue), max(revenue) FROM test_store_int | GROUP BY date, market_code, device_code, country_code, category_id, product_id """.stripMargin print(createTimeSeriesTableSql) carbon.sql(createTimeSeriesTableSql) Query plan: 1. By month, work carbon.sql(s"""explain select market_code, device_code, country_code, category_id, product_id, sum(revenue), timeseries(date, 'month') from test_store_int group by timeseries(date, 'month'), market_code, device_code, country_code, category_id, product_id""".stripMargin).show(200, truncate=false) |== CarbonData Profiler == Query rewrite based on DataMap: - test_store_int_agg_by_month (timeseries) Table Scan on test_store_int_test_store_int_agg_by_month - total blocklets: 4 - filter: none - pruned by Main DataMap - skipped blocklets: 0 2. By year, not work carbon.sql(s"""explain select market_code, device_code, country_code, category_id, product_id, sum(revenue), timeseries(date, 'year') from test_store_int group by timeseries(date, 'year'), market_code, device_code, country_code, category_id, product_id""".stripMargin).show(200, truncate=false) |== CarbonData Profiler == Table Scan on test_store_int - total blocklets: 16 - filter: none - pruned by Main DataMap - skipped blocklets: 0 Thanks Aaron -- Sent from: http://apache-carbondata-dev-mailing-list-archive.1130556.n5.nabble.com/ |
My mistake. MONTH and YEAR should roll up from DAY
-- Sent from: http://apache-carbondata-dev-mailing-list-archive.1130556.n5.nabble.com/ |
Free forum by Nabble | Edit this page |