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] <javascript:_e(%7B%7D,'cvml','[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
