Hitachi Vantara Pentaho Community Forums
Results 1 to 9 of 9

Thread: Error retrieving next row on connecting to hive table through Pentaho Report Designer

  1. #1

    Question Error retrieving next row on connecting to hive table through Pentaho Report Designer

    Summary

    I am now evaluating Pentaho for our business solution.

    When I used 'Report Designer' and tried to connect to hive table for preview in report wizard,
    following 'Error retrieving next row' SQLException occured.


    Also, When I skipped the preview in the wizard and saved the report and previewed in the file->preview->print-preview,
    following error occured.


    If anyone know how to resolve this error, could you tell me any information?


    I have already seen next url, but, I could not resolve the error.


    http://wiki.pentaho.com/display/BAD/Reporting+on+Hive+Data


    Stack Trace

    org.pentaho.reporting.engine.classic.core.ReportDataFactoryException: Failed at query: select id, name from test
    at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SimpleSQLReportDataFactory.queryData(SimpleSQLReportDataFactory.java:258)
    at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SQLReportDataFactory.queryData(SQLReportDataFactory.java:171)
    at org.pentaho.reporting.ui.datasources.jdbc.ui.JdbcPreviewWorker.run(JdbcPreviewWorker.java:103)
    at java.lang.Thread.run(Unknown Source)

    ParentException:
    java.sql.SQLException: Error retrieving next row
    at org.apache.hadoop.hive.jdbc.HiveQueryResultSet.next(HiveQueryResultSet.java:173)
    at org.pentaho.reporting.engine.classic.core.modules.misc.tablemodel.ResultSetTableModelFactory.generateDefaultTableModel(ResultSetTableModelFactory.java:338)
    at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SimpleSQLReportDataFactory.parametrizeAndQuery(SimpleSQLReportDataFactory.java:441)
    at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SimpleSQLReportDataFactory.queryData(SimpleSQLReportDataFactory.java:254)
    at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SQLReportDataFactory.queryData(SQLReportDataFactory.java:171)
    at org.pentaho.reporting.ui.datasources.jdbc.ui.JdbcPreviewWorker.run(JdbcPreviewWorker.java:103)
    at java.lang.Thread.run(Unknown Source)


    What I did

    create hive table like this.
    Code:
    create table test (
      id int,
      name string
    ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE;
    and create csv file like this.
    Code:
    1,setoyama
    2,setoyama
    3,setoyama
    4,setoyama
    the last line of csv file is empty line.

    And loaded the csv file to hive table, like this.
    Code:
    LOAD DATA LOCAL INPATH '/tmp/hivetest.csv' OVERWRITE INTO TABLE test;
    And then, I downloaded and installed Pentaho Report Designer from http://wiki.pentaho.com/display/BAD/Downloads .
    then, I tried to make a report.

    Environment

    Windows - prd-ce-win-bigdata-preview-3.8 ( http://wiki.pentaho.com/display/BAD/Downloads )
    Hadoop : cdh3u1
    Hive : 0.7.1-cdh3u1

    complementation

    In Pentaho Report Designer, Users can set Max Preview Row.
    So I set the max preview row number smaller than the actual row number the hive table had,
    then the error did'nt occur.
    Name:  maxpreviewrow.jpg
Views: 1778
Size:  13.7 KB

    But when I didn't set the max preview row number, always the error occured.
    I think the HiveQueryResultSet class's 'next' method can't recognize EOF.


    When error occured, HiveServer's log was like next.
    Code:
    Hive history file=/tmp/root/hive_job_log_root_201202201624_1424972500.txt
    Code:
    FAILED: Parse Error: line 1:7 mismatched input '<EOF>' expecting FROM near '1' in from clause
    Total MapReduce jobs = 1
    Launching Job 1 out of 1
    Number of reduce tasks is set to 0 since there's no reduce operator
    Starting Job = job_201202071747_0114, Tracking URL = http://xxxxxxxxx:50030/jobdetails.jsp?jobid=job_201202071747_0114
    Kill Command = /usr/lib/hadoop/bin/hadoop job  -Dmapred.job.tracker=xxxxxxxxxxxxx:9001 -kill job_201202071747_0114
    2012-02-20 16:24:44,007 Stage-1 map = 0%,  reduce = 0%
    2012-02-20 16:24:45,017 Stage-1 map = 100%,  reduce = 0%
    2012-02-20 16:24:46,024 Stage-1 map = 100%,  reduce = 100%
    Ended Job = job_201202071747_0114
    OK
    
    But, this log was also written, when I set the Max Preview Row and the error did not occur.

    error occuring point in HiveQueryResultSet.java

    Code:
    } catch (HiveServerException e) {
        if (e.getErrorCode() == 0) { // error code == 0 means reached the EOF 
            return false; 
        } else { 
            throw new SQLException("Error retrieving next row", e); 
        }
    }
    
    I saw next url.
    http://svn.apache.org/repos/asf/hive...ResultSet.java


    Last edited by setoyama60jp; 02-20-2012 at 05:47 AM.

  2. #2
    Join Date
    Nov 2011
    Posts
    18

    Default

    There is a Jira for this issue http://jira.pentaho.com/browse/PDI-6709. It looks like you should be able to fix this by compiling the JDBC driver from the github repository mentioned in the Jira and replacing the one in the $PRD_HOME/lib/jdbc directory.

  3. #3

    Default

    hello, cdeptula.
    I'll try what you mentioned and then report the result here.

    Thank you so much!

  4. #4

    Default

    thank you, cdeptula.

    I tried and succeeded previewing and executing the report.

    following is for imformation sharing.

    complimentation

    what I did.

    1. downloaded the source code
    2. tried to build with ant at the top directory /src
    3. following error occured, although I set the path for mvn and ant.


    Code:
    C:\pentaho-hive-9d2a9a9\src>ant -autoproxy
    Buildfile: build.xml
    BUILD FAILED
    C:\pentaho-hive-9d2a9a9\src\build.xml:36: Execute failed: java.io.IOException: C
    annot run program "mvn": CreateProcess error=2, ?w?????t?@?
    Total time: 0 seconds
    4. So I looked into the top directory's build.xml and manually executed ant tasks one by one.
    5. succeeded building the jdbc driver.


    Last edited by setoyama60jp; 02-21-2012 at 08:49 PM.

  5. #5
    Join Date
    Aug 2009
    Posts
    13

    Default

    We are running into the same problem with the Hive JDBC driver. It is possible to preview records in Pentaho Report Designer up to the maximum row by setting a limit in the query designer. However, executing the query in PRD results in an execution error.

    I have downloaded the source from Github and I'm trying to build the JDBC driver using Ant. But how can I manually execute the Ant tasks one by one ?

    Would it also be an option to load a Hive JDBC jar from a different project like Cloudera or Mapr ?

  6. #6
    Join Date
    Nov 2011
    Posts
    18

    Default

    It looks like continuous integration is building this JDBC driver and you can download the jar file already built from Pentaho's CI http://ci.pentaho.com/job/apache-hive-0.7.0/.

    Usual CI warning applies. Bleeding edge, untested, unsupported, and do not use in production.

  7. #7
    Join Date
    Aug 2010
    Posts
    87

    Default

    You can find a patched Hive JDBC driver here: hive-jdbc-0.7.0-pentaho-SNAPSHOT.jar. This will be included in our next release and contains more PreparedStatement fixes than are currently included in the official Apache Hive project. We're actively working on having these changes committed back to Apache Hive and hope to no longer maintain our own version of the driver in the near future.

  8. #8
    Join Date
    Aug 2009
    Posts
    13

    Default

    Just to let you know: our solution to the Hive JDBC problem was to use the source from Pentaho's CI repository and build the Hive JDBC driver ourselves and swap out the existing generic Hive JDBC drivers with the custom-made driver.

  9. #9
    Join Date
    Aug 2010
    Posts
    87

    Default

    Glad to hear that worked for you!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.