Hitachi Vantara Pentaho Community Forums
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Query Error in Pentaho report designer while using HIVE database

  1. #1
    Join Date
    Aug 2013
    Posts
    132

    Default Query Error in Pentaho report designer while using HIVE database

    Hi All,

    I need to pull out the data from HIVE database on daily basis which has been partitioned by years,months and days.
    actually days will in the below format 20130904 for 4th september 2013.
    how can i add the parameter / mention it in my query to etract the todays/Previous days data using the parameter.

    Please help.
    Thanks in advance.

    Malibu

  2. #2
    Join Date
    Mar 2003
    Posts
    8,085

    Default

    Add a new parameter, hidden, with post-processing formula, of type "String".

    Give it this formula:

    =MESSAGE("{0,Date,yyyyMMdd}"; [dateParam])

    which gives you exactly the format you need for your filtering.
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  3. #3
    Join Date
    Aug 2013
    Posts
    132

    Default

    Hi Taqua,

    Thanks for the information and kind reply.

    Unfrtunately it didnt worked. it throows the error when i run that query in sql dialogue box or when i run the report.

    Please find error log below.

    org.pentaho.reporting.engine.classic.core.ReportDataFactoryException: Failed at query: select visits, city from ga_visitors where days=${dateParam}
    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(Thread.java:662)
    ParentException:
    java.sql.SQLException: Query returned non-zero code: 10004, cause: FAILED: SemanticException [Error 10004]: Line 1:43 Invalid table alias or column reference 'daysNULL': (possible column names are: visitdate, profile, visitortype, source, medium, campaign, city, category, visitors, visits, bounces, conversions, goal_completions_1, goal_completions_2, goal_completions_4, goal_completions_7, years, months, days)
    at org.apache.hadoop.hive.jdbc.HivePreparedStatement.executeImmediate(HivePreparedStatement.java:195)
    at org.apache.hadoop.hive.jdbc.HivePreparedStatement.executeQuery(HivePreparedStatement.java:152)
    at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SimpleSQLReportDataFactory.parametrizeAndQuery(SimpleSQLReportDataFactory.java:427)
    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(Thread.java:662)

    Please help.
    Waiting for your response and inputs.

    Thanks in advance.
    Malibu

  4. #4
    Join Date
    Aug 2013
    Posts
    132

    Default

    HI,

    Any help available..
    Please share your inputs/suggestion .
    It will be great help.

    Thanks in advance.
    Malibu

  5. #5

    Default

    Hello Taqua/malibu,

    Is it mandatory for the parameter to be mentioned with default value?

    I have a similar problem which I need to fetch a values based on days from HIVE table.which is been pending from past few weeks due to this parameterized query problem in hive.
    my query is select cityvalue,customerlistid,paymentname,paytypevalue from customerpayment where days=20130824;

    but when i use the post processing formula as u explained to malibu it throws the same error as shown by malibu

    I used the below syntax to insert parameter in my query

    select cityvalue,customerlistid,paymentname,paytypevalue from customerpayment where days=${dateparameter}

    please find the attached images which explain how I'm declaring the parameter and its usage in my query.Any help will be appreciated, Thanks in advance.
    Sripada
    Attached Images Attached Images   

  6. #6
    Join Date
    Mar 2003
    Posts
    8,085

    Default

    Yes the parameter must be set to mandatory, as obviously Hive is not willing or able to handle null values for prepared-statement parameters.
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  7. #7
    Join Date
    Aug 2013
    Posts
    132

    Default

    HI Taqua,

    Thanks for your reply and information.

    Still there is a problem.
    and can you please let me know how to use the below mentioned regular expression in my hive query.
    the same query runs fine in HIve command line/Hue query explorer [Hue is an interface for Hive database]

    select * from interval_base where days = regexp_replace(date_sub(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd'),1),"-","")


    also please let me know , in parameter winodow after specfying your formula in post processing formula , do i need to mention the date format as yyyyMMdd which is below value type drop downinput.

    Please help. Thanks in advance.
    Malibu

  8. #8
    Join Date
    Mar 2003
    Posts
    8,085

    Default

    Assuming that the date value for the parameter is actually there, then it may simply be that the Hive JDBC driver has problems with prepared statements. Does the query work when you hardcode a date?

    If the answer is yes, then you can (well, must) avoid the prepared statement parameters and need to use query-scripting to parametrize the query. (Luckily that can be done in the datasource editor itself.)
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  9. #9
    Join Date
    Aug 2013
    Posts
    132

    Default

    HI Taqua,

    Thanks a lot for your inputs.

    Yes, my query works fine in pentaho when I hardcode value in the query instead of using Parameter.
    Can you please explain me how exactly to "query-scripting to parametrize the query". or How can I achieve it in by editing the datasource editor.

    I'm Sorry for beinf stupid. Really I dont know how to solve it.

    Please explain it. It will be really great help.

    Thanks in advance.
    Malibu

  10. #10
    Join Date
    Mar 2003
    Posts
    8,085

    Default

    Let me produce a sample report - it will additionally look nicely in the "Advanced Samples" in PRD.
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

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.