Hi Inosh, I checked the results with the UI and it worked well. As I saw all " *payload_packageName*" and "*payload_reportID*" had values in it. But I too think that there should be something similar has happened as only the MySQL insert fails.
*Maninda Edirisooriya* Software Engineer *WSO2, Inc.*lean.enterprise.middleware. *Blog* : http://maninda.blogspot.com/ *Phone* : +94 777603226 On Tue, Feb 11, 2014 at 7:46 PM, Inosh Goonewardena <[email protected]> wrote: > > Hi Aruna, > > It's seems like some of the rows in cassandra CF having null values for > 'payload_packageName' > column. That is why later query fails when trying to insert data to mysql > table, since you have use that column to create the primary key of that > particular table. > > On Tuesday, February 11, 2014, Aruna Karunarathna <[email protected]> wrote: > >> 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 >> >> > > > -- > Regards, > > Inosh Goonewardena > Associate Technical Lead- WSO2 Inc. > Mobile: +94779966317 > > > _______________________________________________ > Dev mailing list > [email protected] > http://wso2.org/cgi-bin/mailman/listinfo/dev > >
_______________________________________________ Dev mailing list [email protected] http://wso2.org/cgi-bin/mailman/listinfo/dev
