2 Replies Latest reply on Jul 26, 2018 11:53 PM by Amanda Holman

    Can you pass parameters in custom SQL for Hive?

    Amanda Holman

      Project is to do routine QA of data we bring into a Hive DB. I'd like to use Tableau to pull in the data  based on parameters for table name and partitions (year, month, day, etc.) This SQL statement works fine:

       

      select *

      from hivedb.table name

      where  app_group_name = 'prod_1' and year = 2018 and month = 6 and day = 10 and hour = 17

      And campaign_id is not null

      distribute by rand()

      limit 100

       

      But this statement doesn't seem to work when I try to use parameters for the table name

       

      select *

      from hivedb.<Parameters.Parameter Name>

      where  app_group_name = 'prod_1' and year = <Parameters.Parameter Name> and month = <Parameters.Parameter Name> and day = <Parameters.Parameter Name> and hour = <Parameters.Parameter Name>

      And campaign_id is not null

      distribute by rand()

      limit 100

       

      Is this a limitation of Hive or am I missing something?

       

      Thanks,

       

      Amanda

        • 1. Re: Can you pass parameters in custom SQL for Hive?
          Esther Aller

          Hi Amanda,

           

          I believe that the parameter value appears as a string literal in SQL query that gets sent to the database. So something like:

           

          select *

          from hivedb.'parameter value'

           

          I am not familiar with how Hive wants SQL queries to be formatted, but maybe you could try something like:

           

          select *

          from 'hivedb'.<Parameters.Parameter Name>

           

          so that the final query would be

           

          select *

          from 'hivedb'.'parameter value'

           

          To know exactly how the custom SQL query looks when it is sent to the Hive database, you can pull the queries from a performance recording or log files. See Viewing Underlying SQL Queries | Tableau Software

           

          Hope this helps!

          1 of 1 people found this helpful
          • 2. Re: Can you pass parameters in custom SQL for Hive?
            Amanda Holman

            Hi Esther,

             

            This query ran no problem:

             

            select *

            from braze_currents_raw.braze_user_behaviors_app_first_session

            where  app_group_name = 'prod_1' and year = <Parameters.Year> and month = <Parameters.Month> and day = <Parameters.Day>

            and hour = <Parameters.Hour>

            --AND canvas_id IS NOT NULL

            distribute by rand()

            limit <Parameters.Record Limit>

             

            But when I try a parameter to replace the table name (any portion of it) it doesn't work. I've added characters like ` or ' around different parts of the name as you suggested with no luck. I did do the performance recording and looked at the SQL but it didn't show me much related to how the table name is queried.

             

            When I try to replace 'braze_currents_raw.braze_user_behaviors_app_first_session' with a parameter, this is the error that I get:

             

            [Amazon][HiveODBC] (80) Syntax or semantic analysis error thrown in server while execurint query. Error message from server: Error while compiling statement: FAILED: ParseException line 3:2 cannot recognize input near '(' 'select' '*' in joinSource

            Unable to connect to the server. Check that the server is running and that you have access privileges to the requested database.

             

            Any other suggestions or ideas here?