Hortonworks Sandbox Forum

Executing queries on the default database using the HortonWorks ODBC driver

  • #56749
    Jack
    Participant

    I have successfully configured an ODBC DSN.
    The ODBC DSN connection is configured with Database as: default
    There are no settings enabled on the Advanced tab. All options are “off”.
    The authentication is Hive System 2, User Hue

    Test connection returns these results:
    ————————————–

    Driver Version: V1.4.5.1005

    Running connectivity tests…

    Attempting connection
    Connection established
    Disconnecting from server

    TESTS COMPLETED SUCCESSFULLY!
    ————————————–

    This query executes successfully and returns results:
    select * from hive_system.hive_system

    This query fails:
    select * from sample_07

    The error message is: SELECT Failed. 35: ‘.

    Qualifying the query provides no relief:
    select * from default.sample_07
    or
    select * from default.sample_07

    The error message for both tries is the same: [Hortonworks][SQLEngine] (31740) Table or view not found: HIVE..default.sample_07

    When I browse to /tmp/hue I see a zero byte .pipeout file generated at the exact time the query is executed.

    Where are ODBC driver return codes documented? Where is a meaningful log to be found that enables one to troubleshoot this? How does someone author a query that works against the sample_07 in database “default”?

to create new topics or reply. | New User Registration

  • Author
    Replies
  • #56817
    iandr413
    Moderator

    Hi Jack,
    Please make sure this table does in fact exist. Are you able to query this table in the same manner through the HIVE Cli?

    Ian

    #56818
    Jack
    Participant

    Hi Ian,
    I’m using the sandbox provided tables to clarify this issue as much as possible. I should have included a query through cli works as well:
    hive -e ‘select * from sample_07′

    The only place I can’t get the ODBC driver to work is on hive tables that are not system. As I noted, select * from hive_system.hive_system works – which eliminates the ODBC driver as the issue from a connectivity perspective. How to properly reference a hive table through the ODBC driver is the $64 question.

    #56819
    iandr413
    Moderator

    Hi Jack,
    As a reference point, I went through the tutorial to configure and run ODBC for excel on a Mac and I am not having issues executing agains the sample tables. Maybe reviewing that will reveal the reason.

    http://hortonworks.com/hadoop-tutorial/how-to-install-and-configure-the-hortonworks-odbc-driver-on-mac-os-x/

    What OS are you running your sandbox on? How are you using the ODBC driver, through excel or some other application?

    Ian

    #56821
    Jack
    Participant

    Here is the issue/solution.

    I am using Server 2008 and the ODBC driver doesn’t return the error code/message properly. The same ODBC driver on Windows Server 2008R2 O/S emits error message ‘SELECT Failed. 35: ‘. This means that the user doesn’t have select privileges on the table. The cool thing is that I am clairvoyant after eating my Wheaties in the AM and just knew that 35 had a higher meaning. Once I granted the simple ‘grant select on table sample_07 to user hue’ enabled Select * from sample_07 to work as expected.

    It is not intuitive that a grant select is necessary if the user accesses hive via ODBC but not needed if they use any other interface in the sandbox (cli, web), especially when the hive query editor web page says I am logged in as user hue in the upper right hand corner. The ODBC driver has a bug if it returns 35 on Windows Server 2008 R2 and the full, useful, informative text in Windows 7 Professional that enables the user to resolve the problem.

    #56822
    Jack
    Participant

    (P.S., thank you for responding! Your O/S question made me try Windows 7 as I’d been on Windows Server 2008 R2. It’s been so long since I’ve seen an incompatibility I hadn’t thought to do Win 7).

You must be to reply to this topic. | Create Account

Support from the Experts

A HDP Support Subscription connects you experts with deep experience running Apache Hadoop in production, at-scale on the most demanding workloads.

Enterprise Support »

Become HDP Certified

Real world training designed by the core architects of Hadoop. Scenario-based training courses are available in-classroom or online from anywhere in the world

Training »

Hortonworks Data Platform
The Hortonworks Data Platform is a 100% open source distribution of Apache Hadoop that is truly enterprise grade having been built, tested and hardened with enterprise rigor.
Get started with Sandbox
Hortonworks Sandbox is a self-contained virtual machine with Apache Hadoop pre-configured alongside a set of hands-on, step-by-step Hadoop tutorials.
Modern Data Architecture
Tackle the challenges of big data. Hadoop integrates with existing EDW, RDBMS and MPP systems to deliver lower cost, higher capacity infrastructure.