drop database 1_4_1 cascade;
create database 1_4_1;
use 1_4_1;
create table uniqdata_base(cust_id int,cust_name string,active_emui_version string, dob timestamp, doj timestamp, bigint_column1 bigint,bigint_column2 bigint,decimal_column1 decimal(30,10), decimal_column2 decimal(36,10),double_column1 double, double_column2 double,integer_column1 int) stored by 'org.apache.carbondata.format' tblproperties('sort_columns'='cust_name,active_emui_version,dob,doj','dictionary_include'='cust_id,cust_name,dob,bigint_column1,decimal_column1,double_column1','dictionary_exclude'='active_emui_version,doj,bigint_column2,integer_column1');
load data inpath 'hdfs://ip:9000/input/unique_2000/2000_UniqData.csv' into table uniqdata_base options('delimiter'=',', 'quotechar'='"','bad_records_action'='force','fileheader'='cust_id,cust_name,active_emui_version,dob,doj,bigint_column1,bigint_column2,decimal_column1,decimal_column2,double_column1,double_column2,integer_column1');
-- create the first dm
create datamap dm_uniqdata_base_all_dicinc on table uniqdata_base using 'bloomfilter' dmproperties ('index_columns' = 'cust_name,cust_id,bigint_column1,decimal_column1,double_column1', 'bloom_size'='640000', 'bloom_fpp'='0.00001');
-- query OK
select count(cust_id) from uniqdata_base where cust_id =10999;
select cust_name,cust_id from uniqdata_base where cust_name='cust_name_00013' limit 1;
select * from uniqdata_base where cust_id =10999 limit 1;
-- create the second dm
create datamap dm_uniqdata_base_all_dicex1 on table uniqdata_base using 'bloomfilter' dmproperties ('index_columns' = 'active_emui_version,doj,bigint_column2,integer_column1', 'bloom_size'='640000', 'bloom_fpp'='0.00001');
-- query OK
select count(cust_id) from uniqdata_base where cust_id =10999;
select cust_name,cust_id from uniqdata_base where cust_name='cust_name_00013' limit 1;
select * from uniqdata_base where cust_id =10999 limit 1;
-- drop all the datamaps
show datamap on table uniqdata_base;
drop datamap dm_uniqdata_base_all_dicinc on table uniqdata_base;
drop datamap dm_uniqdata_base_all_dicex1 on table uniqdata_base;
-- create 2 datamaps
create datamap dm_uniqdata_base_all_dicinc on table uniqdata_base using 'bloomfilter' dmproperties ('index_columns' = 'cust_name,cust_id,bigint_column1,decimal_column1,double_column1', 'bloom_size'='640000', 'bloom_fpp'='0.00001');
create datamap dm_uniqdata_base_all_dicex1 on table uniqdata_base using 'bloomfilter' dmproperties ('index_columns' = 'active_emui_version,doj,bigint_column2,integer_column1', 'bloom_size'='640000', 'bloom_fpp'='0.00001');
-- query fail
select count(cust_id) from uniqdata_base where cust_id =10999;
select cust_name,cust_id from uniqdata_base where cust_name='cust_name_00013' limit 1;
select * from uniqdata_base where cust_id =10999 limit 1;
Steps: