Writing Hive Scripts as Tenant

Hive scripts can be written for tenants in the same manner as how you write them in standalone BAM. Only difference is that when you access Cassandra event column families, you should include your tenant specific credentials, which basically access the data from tenant space of Cassandra.

Writing script in local BAM multi-tenant setup

By changing the Cassandra data store credentials, you can access and execute a tenant based Hive script. WSO2 BAM has a separated tenant space within Hive. This is a different logical space to execute the tenant separated queries.

The following is an example of changing the Hive query of the KPI sample.

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" = "sinthuadmin@sinthu.bam.test.com" ,
"cassandra.ks.password" = "sinthuadmin" ,
"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 (
'mapred.jdbc.driver.class' = 'org.h2.Driver' ,
'mapred.jdbc.url' = 'jdbc:h2:repository/database/samples/WSO2CARBON_DB;AUTO_SERVER=TRUE' ,
'mapred.jdbc.username' = 'wso2carbon' ,
'mapred.jdbc.password' = 'wso2carbon' ,
'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;
CREATE EXTERNAL TABLE IF NOT EXISTS UserTable(
name STRING, totalOrders INT, totalQuantity INT)
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/samples/WSO2CARBON_DB;AUTO_SERVER=TRUE' ,
'mapred.jdbc.username' = 'wso2carbon' ,
'mapred.jdbc.password' = 'wso2carbon' ,
'hive.jdbc.update.on.duplicate' = 'true' ,
'hive.jdbc.primary.key.fields' = 'name' ,
'hive.jdbc.table.create.query' =
'CREATE TABLE UserSummary (name VARCHAR(100) NOT NULL PRIMARY KEY,
totalOrders INT, totalQuantity INT)' );

insert overwrite table UserTable
select userName, count(DISTINCT orderID),
sum(quantity) from PhoneSalesTable
where version= "1.0.0" group by userName;

The Cassandra storage handler properties state the username and the password. This summarizes and stores them in the JDBC table as mentioned in the above script.