Hi all,
I am trying to execute a hive script to extract data from BAM to MySQL
database. Following is the hive script I am using.
drop table BambooEmmaTable;
drop table SummarizedBambooEmmaTable;
CREATE EXTERNAL TABLE IF NOT EXISTS BambooEmmaTable (key STRING,
payload_reportID STRING ,payload_packageName STRING,
payload_numberOfPackages STRING , payload_numberOfClasses STRING,
payload_numberOfMethods STRING , payload_numberOfBlocks STRING ,
payload_numberOfLines STRING,
payload_numberOfFiles STRING, payload_allClassCoverage STRING,
payload_allMethodCoverage STRING , payload_allBlockCoverage STRING,
payload_allLineCoverage STRING,
payload_classCoverage STRING, payload_methodCoverage STRING,
payload_blockCoverage STRING, payload_lineCoverage STRING) STORED BY
'org.apache.hadoop.hive.cassandra.CassandraStorageHandler' WITH
SERDEPROPERTIES (
"wso2.carbon.datasource.name" = "WSO2BAM_CASSANDRA_DATASOURCE",
"cassandra.cf.name" = "Bamboo_Emma_Stream",
"cassandra.columns.mapping" = ":key,payload_reportID ,
payload_packageName , payload_numberOfPackages ,
payload_numberOfClasses , payload_numberOfMethods ,
payload_numberOfBlocks , payload_numberOfLines ,
payload_numberOfFiles , payload_allClassCoverage ,
payload_allMethodCoverage , payload_allBlockCoverage ,
payload_allLineCoverage ,
payload_classCoverage , payload_methodCoverage , payload_blockCoverage ,
payload_lineCoverage" );
CREATE EXTERNAL TABLE IF NOT EXISTS
SummarizedBambooEmmaTable( payload_reportID STRING , payload_packageName
STRING, payload_numberOfPackages STRING , payload_numberOfClasses STRING,
payload_numberOfMethods STRING , payload_numberOfBlocks STRING ,
payload_numberOfLines STRING,
payload_numberOfFiles STRING, payload_allClassCoverage STRING,
payload_allMethodCoverage STRING , payload_allBlockCoverage STRING,
payload_allLineCoverage STRING,
payload_classCoverage STRING, payload_methodCoverage STRING,
payload_blockCoverage STRING, payload_lineCoverage STRING )
STORED BY 'org.wso2.carbon.hadoop.hive.jdbc.storage.JDBCStorageHandler'
TBLPROPERTIES (
'mapred.jdbc.driver.class' = 'com.mysql.jdbc.Driver',
'mapred.jdbc.url' = 'jdbc:mysql://localhost/greg',
'mapred.jdbc.username' = 'root',
'mapred.jdbc.password' = '123',
'hive.jdbc.update.on.duplicate'= 'true',
'hive.jdbc.primary.key.fields'='payload_reportID ,
payload_packageName',
'hive.jdbc.table.create.query' = 'CREATE TABLE
BambooEmmaSummary ( payload_reportID varchar(50) , payload_packageName
varchar(200) ,
payload_numberOfPackages varchar(50) ,
payload_numberOfClasses varchar(50) ,
payload_numberOfMethods varchar(50) , payload_numberOfBlocks varchar(50)
, payload_numberOfLines varchar(50) ,
payload_numberOfFiles varchar(50) , payload_allClassCoverage varchar(50) ,
payload_allMethodCoverage varchar(50) , payload_allBlockCoverage
varchar(50) ,
payload_allLineCoverage varchar(50) , payload_classCoverage
varchar(50) , payload_methodCoverage varchar(50) , payload_blockCoverage
varchar(50) ,
payload_lineCoverage varchar(50), PRIMARY KEY(payload_reportID,
payload_packageName ) )');
insert overwrite table SummarizedBambooEmmaTable SELECT payload_reportID
, payload_packageName , payload_numberOfPackages ,
payload_numberOfClasses , payload_numberOfMethods ,
payload_numberOfBlocks
, payload_numberOfLines , payload_numberOfFiles ,
payload_allClassCoverage , payload_allMethodCoverage ,
payload_allBlockCoverage , payload_allLineCoverage ,
payload_classCoverage , payload_methodCoverage , payload_blockCoverage ,
payload_lineCoverage FROM BambooEmmaTable
How ever I am getting the following exception When I ran the hive script.
[2014-02-11 18:37:33,279] ERROR
{org.wso2.carbon.hadoop.hive.jdbc.storage.db.DBOperation} - Failed to
write data to database
java.sql.SQLException: No value specified for parameter 2
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1086)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:920)
at
com.mysql.jdbc.PreparedStatement.checkAllParametersSet(PreparedStatement.java:2594)
at
com.mysql.jdbc.PreparedStatement.fillSendPacket(PreparedStatement.java:2569)
at
com.mysql.jdbc.PreparedStatement.fillSendPacket(PreparedStatement.java:2493)
at
com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2260)
at
org.wso2.carbon.hadoop.hive.jdbc.storage.db.DBOperation.isRowExisting(DBOperation.java:149)
at
org.wso2.carbon.hadoop.hive.jdbc.storage.db.DBOperation.writeToDB(DBOperation.java:59)
at
org.wso2.carbon.hadoop.hive.jdbc.storage.db.DBRecordWriter.write(DBRecordWriter.java:35)
at
org.apache.hadoop.hive.ql.exec.FileSinkOperator.processOp(FileSinkOperator.java:589)
at org.apache.hadoop.hive.ql.exec.Operator.process(Operator.java:471)
at org.apache.hadoop.hive.ql.exec.Operator.forward(Operator.java:762)
at
org.apache.hadoop.hive.ql.exec.SelectOperator.processOp(SelectOperator.java:84)
at org.apache.hadoop.hive.ql.exec.Operator.process(Operator.java:471)
at org.apache.hadoop.hive.ql.exec.Operator.forward(Operator.java:762)
at
org.apache.hadoop.hive.ql.exec.TableScanOperator.processOp(TableScanOperator.java:83)
at org.apache.hadoop.hive.ql.exec.Operator.process(Operator.java:471)
at org.apache.hadoop.hive.ql.exec.Operator.forward(Operator.java:762)
at
org.apache.hadoop.hive.ql.exec.MapOperator.process(MapOperator.java:531)
at org.apache.hadoop.hive.ql.exec.ExecMapper.map(ExecMapper.java:143)
at org.apache.hadoop.mapred.MapRunner.run(MapRunner.java:50)
at org.apache.hadoop.mapred.MapTask.runOldMapper(MapTask.java:435)
at org.apache.hadoop.mapred.MapTask.run(MapTask.java:371)
at
org.apache.hadoop.mapred.LocalJobRunner$Job.run(LocalJobRunner.java:211)
However, If I change the * 'hive.jdbc.primary.key.fields'='**payload_reportID
, payload_packageName', *to a single key value, script seems to work fine.
Any ideas what I'm doing wrong here?.. Any help suggestion is highly
appreciated.
Regards,
--
*Aruna Sujith Karunarathna* | Software Engineer
WSO2, Inc | lean. enterprise. middleware.
#20, Palm Grove, Colombo 03, Sri Lanka
Mobile: +94 71 9040362 | Work: +94 112145345
Email: [email protected] | Web: www.wso2.com
_______________________________________________
Dev mailing list
[email protected]
http://wso2.org/cgi-bin/mailman/listinfo/dev
