CREATE EXTERNAL TABLE IF NOT EXISTS PhoneSalesTable
	(orderID STRING, brandName STRING, userName STRING, quantity INT,
	 version STRING) STORED BY
	'org.apache.hadoop.hive.cassandra.CassandraStorageHandler'
	WITH SERDEPROPERTIES (
 	"cassandra.host" = "127.0.0.1" ,
	"cassandra.port" = "9160" ,
	"cassandra.ks.name" = "EVENT_KS" ,
	"cassandra.ks.username" = "admin" ,
	"cassandra.ks.password" = "admin" ,
	"cassandra.cf.name" = "org_wso2_bam_phone_retail_store_kpi" ,
	"cassandra.columns.mapping" =
	":key,payload_brand, payload_user, payload_quantity, Version" );
CREATE EXTERNAL TABLE IF NOT EXISTS PhonebrandTable(brand STRING,
	totalOrders INT, totalQuantity INT) STORED BY
	'org.wso2.carbon.hadoop.hive.jdbc.storage.JDBCStorageHandler'
	TBLPROPERTIES (
    'wso2.carbon.datasource.name'='WSO2BAM_DATASOURCE',
	'hive.jdbc.update.on.duplicate' = 'true' ,
	'hive.jdbc.primary.key.fields' = 'brand' ,
	'hive.jdbc.table.create.query' =
	'CREATE TABLE brandSummary (brand VARCHAR(100) NOT NULL PRIMARY KEY,
	totalOrders  INT, totalQuantity INT)' );
insert overwrite table PhonebrandTable
	select brandName, count(DISTINCT orderID),
	sum(quantity) from PhoneSalesTable
	where version= "1.0.0" group by brandName;