Hi Lochana,
This error is due to your mySql table are not having the "tenantId" column.
This will be solved by modifying the Hive script to have the tenantId in
all the mySql tables.
"tenantId" was added with the latest updates. It seems the tenantId is
missing in the hive script.
Please find the modified hive script attached here with.
Thanks,
Jayanga.
*Jayanga Dissanayake*
Senior Software Engineer
WSO2 Inc. - http://wso2.com/
lean . enterprise . middleware
email: jaya...@wso2.com
mobile: +94772207259
On Fri, Feb 12, 2016 at 9:02 AM, Lochana Ranaweera <locha...@wso2.com>
wrote:
> Hi Kalpa,
>
> Please find the toolbox here
> https://drive.google.com/a/wso2.com/file/d/0B2SWP2RvcxJjUFNYVHpvazhFSU0/view?usp=sharing
>
> I will do so and update this thread.
>
>
> On Fri, Feb 12, 2016 at 1:20 AM, Kalpa Welivitigoda <kal...@wso2.com>
> wrote:
>
>> Hi Lochana,
>>
>> Would you please share the toolbox? It seems that there is a mismatch
>> between the fields in the SQL query with those in the db.
>>
>> The exception is thrown from db.jag and it is a common place where all
>> the SQL statements are executed. Shall we isolate the request that fails
>> (you can do so by loading the dashboard with developer tools and see what
>> request to as-data.jag fails)?
>>
>>
>> On Thu, Feb 11, 2016 at 4:42 PM, Lochana Ranaweera <locha...@wso2.com>
>> wrote:
>>
>>> Adding Jayanga and KalpaW
>>>
>>> On Thu, Feb 11, 2016 at 3:37 PM, Lochana Ranaweera <locha...@wso2.com>
>>> wrote:
>>>
>>>> Adding dev
>>>>
>>>> On Thu, Feb 11, 2016 at 3:35 PM, Lochana Ranaweera <locha...@wso2.com>
>>>> wrote:
>>>>
>>>>> Hi all,
>>>>>
>>>>> When going through the previous implementation of the AS Statistics
>>>>> Monitoring feature in [1] which is applicable for AS 5.3.0 and BAM
>>>>> 2.5.0, it was noted that regardless of the stream definition given
>>>>> through
>>>>> the toolbox, the events are published to the BAM following a specific
>>>>> stream definition at every instance. The events thus published to BAM are
>>>>> analyzed through a hive script which summarizes results at a mysql
>>>>> database. Since the events published to the BAM are incomplete and missing
>>>>> a number of fields, several tables in the database do not get populated as
>>>>> expected. Because of this reason, in order to deploy the HTTP monitoring
>>>>> dashboard, I manually populated the database with test data as required.
>>>>>
>>>>> Although I have created the tables with the relevant descriptions to
>>>>> exactly match the hive script included in the tool box attached herewith,
>>>>> I'm getting the error given below.
>>>>>
>>>>> ERROR {org.jaggeryjs.jaggery.core.manager.WebAppManager} -
>>>>> org.mozilla.javascript.WrappedException: Wrapped
>>>>> org.jaggeryjs.scriptengine.exceptions.ScriptException:
>>>>> com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Unknown column
>>>>> 'tenantId' in 'where clause' (/webapp-analytics/api/db.jag#47)
>>>>> org.jaggeryjs.scriptengine.exceptions.ScriptException:
>>>>> org.mozilla.javascript.WrappedException: Wrapped
>>>>> org.jaggeryjs.scriptengine.exceptions.ScriptException:
>>>>> com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Unknown column
>>>>> 'tenantId' in 'where clause' (/webapp-analytics/api/db.jag#47)
>>>>>
>>>>> Appreciate your ideas as regarding this issue.
>>>>>
>>>>> [1]
>>>>> https://github.com/jsdjayanga/carbon-deployment/tree/as_analytics/components/analytics
>>>>>
>>>>> Thanks and regards,
>>>>>
>>>>> Lochana Ranaweera
>>>>> Intern Software Engineer
>>>>> WSO2 Inc: http://wso2.com
>>>>> Blog: https://lochanaranaweera.wordpress.com/
>>>>> Mobile: +94716487055 <http://tel%2B716487055>
>>>>>
>>>>>
>>>>
>>>>
>>>> --
>>>> Lochana Ranaweera
>>>> Intern Software Engineer
>>>> WSO2 Inc: http://wso2.com
>>>> Blog: https://lochanaranaweera.wordpress.com/
>>>> Mobile: +94716487055 <http://tel%2B716487055>
>>>>
>>>>
>>>
>>>
>>> --
>>> Lochana Ranaweera
>>> Intern Software Engineer
>>> WSO2 Inc: http://wso2.com
>>> Blog: https://lochanaranaweera.wordpress.com/
>>> Mobile: +94716487055 <http://tel%2B716487055>
>>>
>>>
>>
>>
>> --
>> Best Regards,
>>
>> Kalpa Welivitigoda
>> Software Engineer, WSO2 Inc. http://wso2.com
>> Email: kal...@wso2.com
>> Mobile: +94776509215
>>
>
>
>
> --
> Lochana Ranaweera
> Intern Software Engineer
> WSO2 Inc: http://wso2.com
> Blog: https://lochanaranaweera.wordpress.com/
> Mobile: +94716487055 <http://tel%2B716487055>
>
>
CREATE EXTERNAL TABLE IF NOT EXISTS AsHttpStat(key STRING, webappName STRING,
webappType STRING, serverName STRING, requestTimestamp BIGINT, responseTime
BIGINT, responseHttpStatusCode INT, requestSizeBytes BIGINT, responseSizeBytes
BIGINT, contentType STRING, responseContentType STRING, country STRING,
userAgentFamily STRING, operatingSystem STRING, language STRING, deviceCategory
STRING, sessionId STRING, referrer STRING, webappContext STRING,
webappDisplayName STRING, tenantId INT) STORED BY
'org.apache.hadoop.hive.cassandra.CassandraStorageHandler' WITH SERDEPROPERTIES
( "wso2.carbon.datasource.name" = "WSO2BAM_CASSANDRA_DATASOURCE",
"cassandra.cf.name" = "monitoring_webapp_calls",
"cassandra.columns.mapping" = ":key,payload_webappName, payload_webappType,
meta_serverName, payload_timestamp, payload_responseTime,
payload_responseHttpStatusCode, payload_requestSizeBytes,
payload_responseSizeBytes, payload_contentType, payload_responseContentType,
payload_country, payload_userAgentFamily, payload_operatingSystem,
payload_language, payload_deviceCategory, payload_sessionId, payload_referrer,
payload_webappContext, payload_webappDisplayName, meta_tenantId");
CREATE EXTERNAL TABLE IF NOT EXISTS RequestsPerMinute (webappName STRING,
webappType STRING, serverName STRING, averageRequestCount BIGINT,
averageResponseTime BIGINT, httpSuccessCount BIGINT, httpErrorCount BIGINT,
sessionCount BIGINT, time STRING, tenantId INT) STORED BY
'org.wso2.carbon.hadoop.hive.jdbc.storage.JDBCStorageHandler' TBLPROPERTIES (
'wso2.carbon.datasource.name'='WSO2AS_MONITORING_DB',
'hive.jdbc.update.on.duplicate' = 'true',
'hive.jdbc.primary.key.fields' = 'webappName,serverName,time,tenantId',
'hive.jdbc.table.create.query' = 'CREATE TABLE REQUESTS_SUMMARY_PER_MINUTE
(webappName VARCHAR(100) NOT NULL,
webappType VARCHAR(15), serverName VARCHAR(45), averageRequestCount BIGINT,
averageResponseTime BIGINT, httpSuccessCount BIGINT, httpErrorCount BIGINT,
sessionCount BIGINT, time VARCHAR(22), tenantId INT, PRIMARY
KEY(webappName,serverName,time,tenantId))' );
@Incremental(name="RequestsPerMinuteIncremental", tables="RequestsPerMinute",
hasNonIndexedData = "true" ,bufferTime="0")
insert overwrite table RequestsPerMinute select webappName, webappType,
serverName, count(key) as averageRequestCount, avg(responseTime) as
averageResponseTime, sum(if(responseHttpStatusCode<400, 1, 0)) as
httpSuccessCount, sum(if(responseHttpStatusCode>=400, 1, 0)) as httpErrorCount,
count(distinct sessionId),
concat(substring(from_unixtime(round(requestTimestamp/1000), 'yyyy-MM-dd
HH:mm:ss'),0,16),':00') as time, tenantId from AsHttpStat GROUP BY
substring(from_unixtime(round(requestTimestamp/1000), 'yyyy-MM-dd
HH:mm:ss'),0,16), serverName, webappType, webappName, tenantId;
CREATE EXTERNAL TABLE IF NOT EXISTS HttpStatus (webappName STRING, serverName
STRING, averageRequestCount BIGINT, responseHttpStatusCode INT, time STRING,
tenantId INT) STORED BY
'org.wso2.carbon.hadoop.hive.jdbc.storage.JDBCStorageHandler' TBLPROPERTIES (
'wso2.carbon.datasource.name'='WSO2AS_MONITORING_DB',
'hive.jdbc.update.on.duplicate' = 'true',
'hive.jdbc.primary.key.fields' =
'webappName,serverName,responseHttpStatusCode,time,tenantId',
'hive.jdbc.table.create.query' = 'CREATE TABLE HTTP_STATUS (webappName
VARCHAR(100) NOT NULL, serverName VARCHAR(45), averageRequestCount BIGINT,
responseHttpStatusCode INT, time VARCHAR(22), tenantId INT, PRIMARY
KEY(webappName,serverName,responseHttpStatusCode,time,tenantId))' );
@Incremental(name="HttpStatusIncremental", tables="HttpStatus",
hasNonIndexedData = "true" ,bufferTime="0")
insert overwrite table HttpStatus select webappName, serverName, count(key) as
averageRequestCount, responseHttpStatusCode,
concat(substring(from_unixtime(round(requestTimestamp/1000) , 'yyyy-MM-dd
HH:mm:ss'),0,16),':00') as time, tenantId from AsHttpStat GROUP BY
responseHttpStatusCode, substring(from_unixtime(round(requestTimestamp/1000),
'yyyy-MM-dd HH:mm:ss'),0,16), serverName, webappName, tenantId;
CREATE EXTERNAL TABLE IF NOT EXISTS Language (webappName STRING, serverName
STRING, averageRequestCount BIGINT, language STRING, time STRING, tenantId INT)
STORED BY 'org.wso2.carbon.hadoop.hive.jdbc.storage.JDBCStorageHandler'
TBLPROPERTIES (
'wso2.carbon.datasource.name'='WSO2AS_MONITORING_DB',
'hive.jdbc.update.on.duplicate' = 'true',
'hive.jdbc.primary.key.fields' = 'webappName,serverName,language,time,tenantId',
'hive.jdbc.table.create.query' = 'CREATE TABLE LANGUAGE (webappName
VARCHAR(100) NOT NULL, serverName VARCHAR(45), averageRequestCount BIGINT,
language VARCHAR(6), time VARCHAR(22), tenantId INT, PRIMARY
KEY(webappName,serverName,language,time,tenantId))' );
@Incremental(name="LanguageIncremental", tables="Language", hasNonIndexedData =
"true" ,bufferTime="0")
insert overwrite table Language select webappName, serverName, count(key) as
averageRequestCount, language,
concat(substring(from_unixtime(round(requestTimestamp/1000) , 'yyyy-MM-dd
HH:mm:ss'),0,16),':00') as time, tenantId from AsHttpStat WHERE
lower(webappType)='webapp' GROUP BY language,
substring(from_unixtime(round(requestTimestamp/1000), 'yyyy-MM-dd
HH:mm:ss'),0,16), serverName, webappName, tenantId;
CREATE EXTERNAL TABLE IF NOT EXISTS UserAgentFamily (webappName STRING,
serverName STRING, averageRequestCount BIGINT, userAgentFamily STRING, time
STRING, tenantId INT) STORED BY
'org.wso2.carbon.hadoop.hive.jdbc.storage.JDBCStorageHandler' TBLPROPERTIES (
'wso2.carbon.datasource.name'='WSO2AS_MONITORING_DB',
'hive.jdbc.update.on.duplicate' = 'true',
'hive.jdbc.primary.key.fields' =
'webappName,serverName,userAgentFamily,time,tenantId',
'hive.jdbc.table.create.query' = 'CREATE TABLE USER_AGENT_FAMILY (webappName
VARCHAR(100) NOT NULL, serverName VARCHAR(45), averageRequestCount BIGINT,
userAgentFamily VARCHAR(15), time VARCHAR(22), tenantId INT, PRIMARY
KEY(webappName,serverName,userAgentFamily,time,tenantId))' );
@Incremental(name="UserAgentFamilyIncremental", tables="UserAgentFamily",
hasNonIndexedData = "true" ,bufferTime="0")
insert overwrite table UserAgentFamily select webappName, serverName,
count(key) as averageRequestCount, userAgentFamily,
concat(substring(from_unixtime(round(requestTimestamp/1000), 'yyyy-MM-dd
HH:mm:ss'),0,16),':00') as time, tenantId from AsHttpStat WHERE
lower(webappType)='webapp' GROUP BY userAgentFamily,
substring(from_unixtime(round(requestTimestamp/1000), 'yyyy-MM-dd
HH:mm:ss'),0,16), webappName, serverName, tenantId;
CREATE EXTERNAL TABLE IF NOT EXISTS OperatingSystem (webappName STRING,
serverName STRING, averageRequestCount BIGINT, operatingSystem STRING, time
STRING, tenantId INT) STORED BY
'org.wso2.carbon.hadoop.hive.jdbc.storage.JDBCStorageHandler' TBLPROPERTIES (
'wso2.carbon.datasource.name'='WSO2AS_MONITORING_DB',
'hive.jdbc.update.on.duplicate' = 'true',
'hive.jdbc.primary.key.fields' =
'webappName,serverName,operatingSystem,time,tenantId',
'hive.jdbc.table.create.query' = 'CREATE TABLE OPERATING_SYSTEM (webappName
VARCHAR(100) NOT NULL, serverName VARCHAR(45), averageRequestCount BIGINT,
operatingSystem VARCHAR(15), time VARCHAR(22), tenantId INT, PRIMARY
KEY(webappName,serverName,operatingSystem,time,tenantId))' );
@Incremental(name="OperatingSystemIncremental", tables="OperatingSystem",
hasNonIndexedData = "true" ,bufferTime="0")
insert overwrite table OperatingSystem select webappName, serverName,
count(key) as averageRequestCount, operatingSystem,
concat(substring(from_unixtime(round(requestTimestamp/1000), 'yyyy-MM-dd
HH:mm:ss'),0,16),':00') as time, tenantId from AsHttpStat WHERE
lower(webappType)='webapp' GROUP BY operatingSystem,
substring(from_unixtime(round(requestTimestamp/1000), 'yyyy-MM-dd
HH:mm:ss'),0,16), serverName, webappName, tenantId;
CREATE EXTERNAL TABLE IF NOT EXISTS DeviceType (webappName STRING, serverName
STRING, averageRequestCount BIGINT, deviceCategory STRING, time STRING,
tenantId INT) STORED BY
'org.wso2.carbon.hadoop.hive.jdbc.storage.JDBCStorageHandler' TBLPROPERTIES (
'wso2.carbon.datasource.name'='WSO2AS_MONITORING_DB',
'hive.jdbc.update.on.duplicate' = 'true',
'hive.jdbc.primary.key.fields' =
'webappName,serverName,deviceCategory,time,tenantId',
'hive.jdbc.table.create.query' = 'CREATE TABLE DEVICE_TYPE (webappName
VARCHAR(100) NOT NULL, serverName VARCHAR(45), averageRequestCount BIGINT,
deviceCategory VARCHAR(100), time VARCHAR(22), tenantId INT, PRIMARY
KEY(webappName,serverName,deviceCategory,time,tenantId))' );
@Incremental(name="DeviceTypeIncremental", tables="DeviceType",
hasNonIndexedData = "true" ,bufferTime="0")
insert overwrite table DeviceType select webappName, serverName, count(key) as
averageRequestCount, deviceCategory,
concat(substring(from_unixtime(round(requestTimestamp/1000), 'yyyy-MM-dd
HH:mm:ss'),0,16),':00') as time, tenantId from AsHttpStat WHERE
lower(webappType)='webapp' GROUP BY deviceCategory,
substring(from_unixtime(round(requestTimestamp/1000), 'yyyy-MM-dd
HH:mm:ss'),0,16), serverName, webappName, tenantId;
CREATE EXTERNAL TABLE IF NOT EXISTS Country (webappName STRING, serverName
STRING, averageRequestCount BIGINT, country STRING, time STRING, tenantId INT)
STORED BY 'org.wso2.carbon.hadoop.hive.jdbc.storage.JDBCStorageHandler'
TBLPROPERTIES (
'wso2.carbon.datasource.name'='WSO2AS_MONITORING_DB',
'hive.jdbc.update.on.duplicate' = 'true',
'hive.jdbc.primary.key.fields' = 'webappName,serverName,country,time,tenantId',
'hive.jdbc.table.create.query' = 'CREATE TABLE COUNTRY (webappName VARCHAR(100)
NOT NULL, serverName VARCHAR(45), averageRequestCount BIGINT, country
VARCHAR(200), time VARCHAR(22), tenantId INT, PRIMARY
KEY(webappName,serverName,country,time,tenantId))' );
@Incremental(name="CountryIncremental", tables="Country", hasNonIndexedData =
"true" ,bufferTime="0")
insert overwrite table Country select webappName, serverName, count(key) as
averageRequestCount, country,
concat(substring(from_unixtime(round(requestTimestamp/1000), 'yyyy-MM-dd
HH:mm:ss'),0,16),':00') as time, tenantId from AsHttpStat WHERE
lower(webappType)='webapp' GROUP BY country,
substring(from_unixtime(round(requestTimestamp/1000), 'yyyy-MM-dd
HH:mm:ss'),0,16), serverName, webappName, tenantId;
CREATE EXTERNAL TABLE IF NOT EXISTS Referrer (webappName STRING, serverName
STRING, averageRequestCount BIGINT, referrer STRING, time STRING, tenantId INT)
STORED BY 'org.wso2.carbon.hadoop.hive.jdbc.storage.JDBCStorageHandler'
TBLPROPERTIES (
'wso2.carbon.datasource.name'='WSO2AS_MONITORING_DB',
'hive.jdbc.update.on.duplicate' = 'true',
'hive.jdbc.primary.key.fields' = 'webappName,serverName,referrer,time,tenantId',
'hive.jdbc.table.create.query' = 'CREATE TABLE REFERRER (webappName
VARCHAR(100) NOT NULL, serverName VARCHAR(45), averageRequestCount BIGINT,
referrer VARCHAR(2000), time VARCHAR(22), tenantId INT, PRIMARY
KEY(webappName,serverName,referrer,time,tenantId))' );
@Incremental(name="ReferrerIncremental", tables="Referrer", hasNonIndexedData =
"true" ,bufferTime="0")
insert overwrite table Referrer select webappName, serverName, count(key) as
averageRequestCount, referrer,
concat(substring(from_unixtime(round(requestTimestamp/1000), 'yyyy-MM-dd
HH:mm:ss'),0,16),':00') as time, tenantId from AsHttpStat WHERE
lower(webappType)='webapp' GROUP BY referrer,
substring(from_unixtime(round(requestTimestamp/1000), 'yyyy-MM-dd
HH:mm:ss'),0,16), serverName, webappName, tenantId;
CREATE EXTERNAL TABLE IF NOT EXISTS WebappContext (webappName STRING,
serverName STRING, averageRequestCount BIGINT, webappcontext STRING, time
STRING, tenantId INT) STORED BY
'org.wso2.carbon.hadoop.hive.jdbc.storage.JDBCStorageHandler' TBLPROPERTIES (
'wso2.carbon.datasource.name'='WSO2AS_MONITORING_DB',
'hive.jdbc.update.on.duplicate' = 'true',
'hive.jdbc.primary.key.fields' =
'webappName,serverName,webappcontext,time,tenantId',
'hive.jdbc.table.create.query' = 'CREATE TABLE WEBAPP_CONTEXT (webappName
VARCHAR(100) NOT NULL, serverName VARCHAR(45), averageRequestCount BIGINT,
webappcontext VARCHAR(200), time VARCHAR(22), tenantId INT, PRIMARY
KEY(webappName,serverName,webappContext,time,tenantId))' );
@Incremental(name="WebappContextIncremental", tables="WebappContext",
hasNonIndexedData = "true" ,bufferTime="0")
insert overwrite table WebappContext select webappName, serverName, count(key)
as averageRequestCount, webappcontext,
concat(substring(from_unixtime(round(requestTimestamp/1000), 'yyyy-MM-dd
HH:mm:ss'),0,16),':00') as time, tenantId from AsHttpStat WHERE
lower(webappType)='webapp' GROUP BY webappcontext,
substring(from_unixtime(round(requestTimestamp/1000), 'yyyy-MM-dd
HH:mm:ss'),0,16), serverName, webappName, tenantId;
_______________________________________________
Dev mailing list
Dev@wso2.org
http://wso2.org/cgi-bin/mailman/listinfo/dev
