Writing Hive Script as Tenant

Hive script can be written for tenant as you write in standalone BAM. Only difference here is when you are accessing the cassandra event column families, you should include you tenant specific credentials which basically access the data from tenant space of cassandra.

In this topic the following sections are covered,

Writing script in WSO2 Stratos Live
Writing script in WSO2 local BAM MT setup

Writing script in WSO2 local BAM MT Setup

As mentioned above, by changing the cassandra data store credentials, we can access run the tenant based hive script. In WSO2 BAM we have separated the tenant space within Hive also, such that it has different logical space to execute the tenant separated queries.

The following is the example of changing the KPI sample hive query:

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;

Note the cassandra storage handler properties, which states the username and the password. This summarizes and store the in the JDBC table as mentioned in the script.