Hi Maninda, Just try by adding null check to the query as modified below.
On Tue, Feb 11, 2014 at 8:14 PM, Maninda Edirisooriya <[email protected]>wrote: > 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 *where >>> * >>> * payload_packageName is not NULL AND **payload_reportID is not NULL*. >>> >>> >>> 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 >> >> > -- Regards, Inosh Goonewardena Associate Technical Lead- WSO2 Inc. Mobile: +94779966317
_______________________________________________ Dev mailing list [email protected] http://wso2.org/cgi-bin/mailman/listinfo/dev
