CREATE EXTERNAL TABLE IF NOT EXISTS APIUsageSummaryTest (key STRING,
	api STRING,version STRING,userId STRING,consumerKey STRING,request BIGINT, fault INT, avg_response_time DOUBLE,max_response_time FLOAT,
	min_response_time FLOAT, is_api_name_added_today BOOLEAN, ip_address 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" = "jdbc_handler_integration_test_stream",
	"cassandra.columns.mapping" =":key,payload_api, payload_api_version,payload_user_id, payload_consumer_key,payload_request_count, payload_fault_count, payload_avg_response_time,payload_max_response_time,
	payload_min_response_time,payload_is_api_name_added_today,meta_ip_address" );
CREATE EXTERNAL TABLE IF NOT EXISTS APIRequestSummaryDataTest (api STRING, version STRING,
	userId STRING,consumerKey STRING, request_count INT,fault_count INT, avg_response_time DOUBLE, max_response_time FLOAT,
	min_response_time FLOAT) STORED BY 
	'org.wso2.carbon.hadoop.hive.jdbc.storage.JDBCStorageHandler' TBLPROPERTIES ( 
	'mapred.jdbc.driver.class' = 'org.h2.Driver',
	'mapred.jdbc.url' = 'jdbc:h2:repository/database/APIMGTSTATS_DB',
	'mapred.jdbc.username' = 'wso2carbon','mapred.jdbc.password' = 'wso2carbon',
	'hive.jdbc.update.on.duplicate' = 'true',
	'hive.jdbc.primary.key.fields'='api,version,userId,consumerKey',
	'hive.jdbc.table.create.query' = 'CREATE TABLE APIUsageSummaryTest ( api VARCHAR(40) NOT NULL, version VARCHAR(10) NOT NULL, userId VARCHAR(30) NOT NULL,consumerKey VARCHAR(200),request_count BIGINT,fault_count INT,avg_response_time DOUBLE, max_response_time FLOAT, min_response_time FLOAT,  PRIMARY KEY(api, version,userId,consumerKey))' );
insert overwrite table APIRequestSummaryDataTest select api,version,userId,consumerKey,sum(request) as request_count, sum(fault) as fault_count, sum(request*avg_response_time)/sum(request) as avg_response_time,
max(max_response_time) as max_response_time, min(min_response_time) as min_response_time from APIUsageSummaryTest group by api,version,userId,consumerKey;
CREATE EXTERNAL TABLE IF NOT EXISTS APIRequestSummaryDataTestFetcher (api_name STRING, api_version STRING,
	userId STRING,consumerKey STRING, total_request_count INT,total_fault_count INT, avg_response_time DOUBLE, max_response_time FLOAT,
	min_response_time FLOAT) STORED BY
	'org.wso2.carbon.hadoop.hive.jdbc.storage.JDBCStorageHandler' TBLPROPERTIES (
	'mapred.jdbc.driver.class' = 'org.h2.Driver',
	'mapred.jdbc.url' = 'jdbc:h2:repository/database/APIMGTSTATS_DB',
	'mapred.jdbc.username' = 'wso2carbon','mapred.jdbc.password' = 'wso2carbon',
	'hive.jdbc.input.columns.mapping' = 'api,version,userid,consumerkey,request_count,fault_count,avg_response_time,max_response_time,min_response_time',
	'mapred.jdbc.input.table.name' = 'APIUsageSummaryTest');
select api_name,api_version,sum(total_request_count) as total_request_count,sum(total_fault_count) as total_fault_count,avg(avg_response_time) as avg_response_time,max(max_response_time) as max_response_time,min(min_response_time) as min_response_time from  APIRequestSummaryDataTestFetcher where api_name='facebook' group by api_name,api_version;
CREATE EXTERNAL TABLE IF NOT EXISTS APIRequestSummaryDataTestUpdateUsingUpsert (api STRING, version STRING,
	total_request_count INT,total_fault_count INT, avg_response_time DOUBLE, max_response_time FLOAT,
	min_response_time FLOAT) STORED BY
	'org.wso2.carbon.hadoop.hive.jdbc.storage.JDBCStorageHandler' TBLPROPERTIES (
	'mapred.jdbc.driver.class' = 'org.h2.Driver',
	'mapred.jdbc.url' = 'jdbc:h2:repository/database/APIMGTSTATS_DB',
	'mapred.jdbc.username' = 'wso2carbon','mapred.jdbc.password' = 'wso2carbon',
	'hive.jdbc.update.on.duplicate' = 'true',
	'hive.jdbc.output.upsert.query' = 'MERGE INTO  APIUsageSummaryTestPerAPI (api, version,total_request_count, total_fault_count,avg_response_time,max_response_time,min_response_time)  key (api, version)  values (?,?,?,?,?,?,?)',
	'hive.jdbc.upsert.query.values.order'='api, version,total_request_count, total_fault_count,avg_response_time,max_response_time,min_response_time',
	'hive.jdbc.primary.key.fields'='api,version',
	'hive.jdbc.table.create.query' = 'CREATE TABLE APIUsageSummaryTestPerAPI ( api VARCHAR(40) NOT NULL, version VARCHAR(10) NOT NULL,total_request_count BIGINT,total_fault_count INT,avg_response_time DOUBLE, max_response_time FLOAT, min_response_time FLOAT,  PRIMARY KEY(api, version))' );
insert overwrite table APIRequestSummaryDataTestUpdateUsingUpsert select api_name,api_version,total_request_count,total_fault_count,avg_response_time,max_response_time,min_response_time from APIRequestSummaryDataTestFetcher where api_name='facebook';
CREATE EXTERNAL TABLE IF NOT EXISTS APIRequestFinalSummary (api STRING, version STRING,
	total_request_count INT,total_fault_count INT, avg_response_time DOUBLE, max_response_time FLOAT,
	min_response_time FLOAT) STORED BY
	'org.wso2.carbon.hadoop.hive.jdbc.storage.JDBCStorageHandler' TBLPROPERTIES (
	'mapred.jdbc.driver.class' = 'org.h2.Driver',
	'mapred.jdbc.url' = 'jdbc:h2:repository/database/APIMGTSTATS_DB',
	'mapred.jdbc.username' = 'wso2carbon','mapred.jdbc.password' = 'wso2carbon',
	'mapred.jdbc.input.table.name' = 'APIUsageSummaryTestPerAPI');
select api,version,total_request_count,total_fault_count,avg_response_time,max_response_time,min_response_time from APIRequestFinalSummary;