PDA

View Full Version : Error retrieving data from Hive DB type



rajpal
04-10-2012, 07:16 PM
Hi All,
I'm using "pdi-ce-big-data-4.3.0-preview" to query data from Hive DB. It was working fine so far for other translation purposes. Recently noticed that data read from Hive DB is incomplete and spoon is logging following error message:
"An error occurred, processing will be stopped:"
"Couldn't get row from result set"
"Error retrieving next row"
"HiveDB - Statement canceled!"

and underneath big error messages list for the errors while stopping the job.

I noticed that HQL is getting executed successfully and whole result set is getting printed in spoon logs. However, its getting broken midway before could be routed to output stream. When I try to write output to excel file, only a part is getting written. Same error is noticed even when output is not written into file or single string filed is fetched from hive db.

Any suggestions, what might be going wrong here?

Thanks in anticipation,
Raj

jganoff
04-11-2012, 09:30 AM
You could try replacing the Hive JDBC driver that came with the preview release with a recently built version from here: hive-jdbc-0.7.0-pentaho-SNAPSHOT.jar (http://ci.pentaho.com/job/apache-hive-0.7.0/lastSuccessfulBuild/artifact/src/build/jdbc/hive-jdbc-0.7.0-pentaho-SNAPSHOT.jar). This has a small but important fix that prevents the end an exception when of the result set is encountered.

rajpal
04-16-2012, 03:11 PM
Right on target. This new driver has solved the partial data fetch issue. Many thanks.

However, I'm not sure its related or unrelated, noticed some strange log message about auto commit option. Here are the logs:

"Connected to database.Hive - Can't turn auto commit off
Hive - java.sql.SQLException: Method not supported - setAutoCommit(false)

Is this of concern in any way, specially when job is not performing any update in HIve db?

jganoff
04-16-2012, 04:34 PM
That's not of any concern and should be a simple info logging message if it's not. The Hive JDBC driver always returns true for getAutoCommit() and does not support changing it.

Sushilgyl
07-18-2012, 02:45 AM
I am using pdi-ce-4.3 and hive-0.8.1. Also getting same error while reading data from hive table using 'table input' step. It shows all the tables in hive but not able to fetch data.

Could you please any one suggest me what the problem it is ?

jganoff
07-18-2012, 08:30 AM
Hi Sushilgyl,

You'll need to update the jars in the $KETTLE/libext/bigdata/hive directory match your Hive jars. You'll also need to update the Hive JDBC driver to use our latest that is compatible with Hive 0.7.x - 0.9.x: http://ci.pentaho.com/job/apache-hive/lastSuccessfulBuild/artifact/jdbc/dist/hive-jdbc-0.7.0-pentaho-SNAPSHOT.jar.

We're in the process of connecting to various versions of Hadoop and Hive easier but for now you'll need to update your Hive jars manually.

Sushilgyl
07-19-2012, 01:47 AM
Hi jganoff,

Thanks for your quick replay.
I have already update all jars in $KETTLE/libext/bigdata/hive directory. if I place 'hive-jdbc-0.7.0-pentaho-SNAPSHOT.jar' into $KETTLE/libext/JDBC directory then it's not connect to hadoop-hive database.
If I put 'hive-jdbc-0.8.1.jar' into $KETTLE/libext/JDBC directory the it is able to connect with hadoop-hive database but does not able to retrieve data from hive table and throws error 'Could not get field from [select * from tablename] Method not supported'.

Sushilgyl
07-20-2012, 12:01 AM
Pl answer the following........

jganoff
07-20-2012, 01:24 PM
What version of Hadoop are you connecting to? You'll need to make sure the hadoop*core.jar found in PDI matches your Hadoop cluster's. Also, you may want to update to the PDI 4.3.0 stable release as there have been many bug fixes and enhancements since the preview build you're using. You can find the PDI 4.3.0 stable build here: http://sourceforge.net/projects/pentaho/files/Data%20Integration/4.3.0-stable/

Sushilgyl
07-24-2012, 03:04 AM
Dear Jganoff

I am using apache hadoop-1.0.1 version. I have already copied hadoop*core.jar in PDI and kindly inform you that i am using stable release of PDI 4.3

jganoff
07-24-2012, 08:45 AM
Could you post any error logs you may have for Spoon?

Sushilgyl
07-25-2012, 01:46 AM
Hi

Pl find the following error message :

2012/07/25 11:04:51 - Spoon - Logging goes to file:///tmp/spoon_74fb37d1-d61a-11e1-9403-cf47d719b9bc.log
2012/07/25 11:04:54 - class org.pentaho.agilebi.platform.JettyServer - WebServer.Log.CreateListener localhost:10000
2012/07/25 11:05:02 - Spoon - Asking for repository
2012/07/25 11:08:52 - /home/etl/Desktop/test_hive/hive_to_msql.ktr : hive_to_msql - Dispatching started for transformation [/home/etl/Desktop/test_hive/hive_to_msql.ktr : hive_to_msql]
2012/07/25 11:08:52 - con_hive - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : ERROR executing query: org.pentaho.di.core.exception.KettleDatabaseException:
2012/07/25 11:08:52 - con_hive - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : Error getting row information from database:
2012/07/25 11:08:52 - con_hive - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : Method not supported
2012/07/25 11:08:52 - con_hive - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : ERROR in part: openQuery : get rowinfo
2012/07/25 11:08:52 - Table input.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : Unexpected error
2012/07/25 11:08:52 - Table input.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : org.pentaho.di.core.exception.KettleDatabaseException:
2012/07/25 11:08:52 - Table input.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : An error occurred executing SQL in part [openQuery : get rowinfo]:
2012/07/25 11:08:52 - Table input.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : SELECT *
2012/07/25 11:08:52 - Table input.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : FROM bhu_person_info
2012/07/25 11:08:52 - Table input.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) :
2012/07/25 11:08:52 - Table input.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) :
2012/07/25 11:08:52 - Table input.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : Error getting row information from database:
2012/07/25 11:08:52 - Table input.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : Method not supported
2012/07/25 11:08:52 - Table input.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) :
2012/07/25 11:08:52 - Table input.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) :
2012/07/25 11:08:52 - Table input.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at org.pentaho.di.core.database.Database.openQuery(Database.java:1929)
2012/07/25 11:08:52 - Table input.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at org.pentaho.di.trans.steps.tableinput.TableInput.doQuery(TableInput.java:233)
2012/07/25 11:08:52 - Table input.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at org.pentaho.di.trans.steps.tableinput.TableInput.processRow(TableInput.java:143)
2012/07/25 11:08:52 - Table input.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at org.pentaho.di.trans.step.RunThread.run(RunThread.java:50)
2012/07/25 11:08:52 - Table input.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at java.lang.Thread.run(Thread.java:619)
2012/07/25 11:08:52 - Table input.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : Caused by: org.pentaho.di.core.exception.KettleDatabaseException:
2012/07/25 11:08:52 - Table input.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : Error getting row information from database:
2012/07/25 11:08:52 - Table input.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : Method not supported
2012/07/25 11:08:52 - Table input.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) :
2012/07/25 11:08:52 - Table input.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at org.pentaho.di.core.database.Database.getRowInfo(Database.java:2462)
2012/07/25 11:08:52 - Table input.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at org.pentaho.di.core.database.Database.openQuery(Database.java:1916)
2012/07/25 11:08:52 - Table input.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : ... 4 more
2012/07/25 11:08:52 - Table input.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : Caused by: java.sql.SQLException: Method not supported
2012/07/25 11:08:52 - Table input.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at org.apache.hadoop.hive.jdbc.HiveResultSetMetaData.isSigned(HiveResultSetMetaData.java:177)
2012/07/25 11:08:52 - Table input.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at org.pentaho.di.core.database.Database.getValueFromSQLType(Database.java:2474)
2012/07/25 11:08:52 - Table input.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at org.pentaho.di.core.database.Database.getRowInfo(Database.java:2455)
2012/07/25 11:08:52 - Table input.0 - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : ... 5 more
2012/07/25 11:08:52 - /home/etl/Desktop/test_hive/hive_to_msql.ktr : hive_to_msql - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : Something went wrong while trying to stop the transformation: org.pentaho.di.core.exception.KettleDatabaseException:
2012/07/25 11:08:52 - /home/etl/Desktop/test_hive/hive_to_msql.ktr : hive_to_msql - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : Error cancelling statement
2012/07/25 11:08:52 - /home/etl/Desktop/test_hive/hive_to_msql.ktr : hive_to_msql - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : Method not supported
2012/07/25 11:08:52 - /home/etl/Desktop/test_hive/hive_to_msql.ktr : hive_to_msql - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : org.pentaho.di.core.exception.KettleDatabaseException:
2012/07/25 11:08:52 - /home/etl/Desktop/test_hive/hive_to_msql.ktr : hive_to_msql - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : Error cancelling statement
2012/07/25 11:08:52 - /home/etl/Desktop/test_hive/hive_to_msql.ktr : hive_to_msql - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : Method not supported
2012/07/25 11:08:52 - /home/etl/Desktop/test_hive/hive_to_msql.ktr : hive_to_msql - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) :
2012/07/25 11:08:52 - /home/etl/Desktop/test_hive/hive_to_msql.ktr : hive_to_msql - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at org.pentaho.di.core.database.Database.cancelStatement(Database.java:637)
2012/07/25 11:08:52 - /home/etl/Desktop/test_hive/hive_to_msql.ktr : hive_to_msql - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at org.pentaho.di.core.database.Database.cancelQuery(Database.java:617)
2012/07/25 11:08:52 - /home/etl/Desktop/test_hive/hive_to_msql.ktr : hive_to_msql - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at org.pentaho.di.trans.steps.tableinput.TableInput.stopRunning(TableInput.java:303)
2012/07/25 11:08:52 - /home/etl/Desktop/test_hive/hive_to_msql.ktr : hive_to_msql - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at org.pentaho.di.trans.Trans.stopAll(Trans.java:1383)
2012/07/25 11:08:52 - /home/etl/Desktop/test_hive/hive_to_msql.ktr : hive_to_msql - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at org.pentaho.di.trans.step.BaseStep.stopAll(BaseStep.java:2256)
2012/07/25 11:08:52 - /home/etl/Desktop/test_hive/hive_to_msql.ktr : hive_to_msql - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at org.pentaho.di.trans.step.RunThread.run(RunThread.java:74)
2012/07/25 11:08:52 - /home/etl/Desktop/test_hive/hive_to_msql.ktr : hive_to_msql - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at java.lang.Thread.run(Thread.java:619)
2012/07/25 11:08:52 - /home/etl/Desktop/test_hive/hive_to_msql.ktr : hive_to_msql - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : Caused by: java.sql.SQLException: Method not supported
2012/07/25 11:08:52 - /home/etl/Desktop/test_hive/hive_to_msql.ktr : hive_to_msql - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at org.apache.hadoop.hive.jdbc.HiveStatement.cancel(HiveStatement.java:85)
2012/07/25 11:08:52 - /home/etl/Desktop/test_hive/hive_to_msql.ktr : hive_to_msql - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : at org.pentaho.di.core.database.Database.cancelStatement(Database.java:631)
2012/07/25 11:08:52 - /home/etl/Desktop/test_hive/hive_to_msql.ktr : hive_to_msql - ERROR (version 4.3.0-stable, build 16786 from 2012-04-24 14.11.32 by buildguy) : ... 6 more
2012/07/25 11:08:52 - Table input.0 - Finished reading query, closing connection.
2012/07/25 11:08:52 - Table input.0 - Finished processing (I=0, O=0, R=0, W=0, U=0, E=1)
2012/07/25 11:08:52 - /home/etl/Desktop/test_hive/hive_to_msql.ktr : hive_to_msql - /home/etl/Desktop/test_hive/hive_to_msql.ktr : hive_to_msql
2012/07/25 11:08:52 - /home/etl/Desktop/test_hive/hive_to_msql.ktr : hive_to_msql - /home/etl/Desktop/test_hive/hive_to_msql.ktr : hive_to_msql

Regards,
Sushil

Sushilgyl
07-26-2012, 04:10 AM
Pls reply soon, i am getting frustated b'coz i am trying to resolve this from couple of days.

Ramya.toshniwal
01-09-2013, 02:14 AM
Hi jganoff,

I am also facing the similar issue. Getting "Method not supported" error in Table input step for HIVE.
Hadoop running version - 0.20.2
Hive running version - 0.8.0(tested with 0.9.0 also)

Any help in this issue would be highly appreciable.

Thanks,
Ramya