CREATE TABLE IF NOT EXISTS store(
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, product_id',
'SORT_SCOPE'='GLOBAL_SORT',
'CACHE_LEVEL'='BLOCKLET',
'TABLE_BLOCKSIZE'='256',
'GLOBAL_SORT_PARTITIONS'='2'
);
CREATE DATAMAP IF NOT EXISTS agg_by_day ON TABLE store
USING 'timeSeries'
DMPROPERTIES (
'EVENT_TIME'='date',
'DAY_GRANULARITY'='1')
AS SELECT date, market_code, device_code, country_code, category_id,
COUNT(date), 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 store
GROUP BY date, market_code, device_code, country_code, category_id ;
CREATE DATAMAP IF NOT EXISTS bloomfilter_all_dimensions ON TABLE store
USING 'bloomfilter'
DMPROPERTIES (
'INDEX_COLUMNS'='market_code, device_code, country_code, category_id, date, product_id',
'BLOOM_SIZE'='640000',
'BLOOM_FPP'='0.000001',
'BLOOM_COMPRESS'='true'
);
SELECT date, market_code, device_code, country_code, category_id, sum(est_free_app_download)
FROM store
WHERE date BETWEEN '2016-09-01' AND '2016-09-03' AND
device_code='ios-phone' AND country_code='EE' AND category_id=100021 AND
product_id IN (590416158, 590437560)
GROUP BY date, market_code, device_code, country_code, category_id;
CREATE TABLE IF NOT EXISTS store( 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, product_id', 'SORT_SCOPE'='GLOBAL_SORT', 'CACHE_LEVEL'='BLOCKLET', 'TABLE_BLOCKSIZE'='256', 'GLOBAL_SORT_PARTITIONS'='2' );
CREATE DATAMAP IF NOT EXISTS agg_by_day ON TABLE store USING 'timeSeries' DMPROPERTIES ( 'EVENT_TIME'='date', 'DAY_GRANULARITY'='1') AS SELECT date, market_code, device_code, country_code, category_id, COUNT(date), 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 store GROUP BY date, market_code, device_code, country_code, category_id ;
CREATE DATAMAP IF NOT EXISTS bloomfilter_all_dimensions ON TABLE store USING 'bloomfilter' DMPROPERTIES ( 'INDEX_COLUMNS'='market_code, device_code, country_code, category_id, date, product_id', 'BLOOM_SIZE'='640000', 'BLOOM_FPP'='0.000001', 'BLOOM_COMPRESS'='true' );
INSERT INTO store VALUES ("market_code_str", "device_code_str", "country_code_str", 100000, 590416100, "2016-08-01 12:00:00", 101, 1001, 10001), ("market_code_str", "ios-phone", "CC", 100000, 590416100, "2016-09-02 12:00:00", 101, 1001, 10001), ("market_code_str", "ios-phone", "EE", 100001, 590416158, "2016-09-02 12:00:00", 101, 1001, 10001), ("market_code_str", "ios-phone", "EE", 100021, 590416158, "2016-09-02 12:00:00", 101, 1001, 10001), ("market_code_str", "ios-phone", "FF", 100031, 590437560, "2016-09-02 12:00:00", 101, 1001, 10001), ("market_code_str", "ios-phone", "EE", 100021, 590437560, "2016-09-02 12:00:00", 101, 1001, 10001);
SELECT date, market_code, device_code, country_code, category_id, sum(est_free_app_download) FROM store WHERE date BETWEEN '2016-09-01' AND '2016-09-03' AND device_code='ios-phone' AND country_code='EE' AND category_id=100021 AND product_id IN (590416158, 590437560) GROUP BY date, market_code, device_code, country_code, category_id;