How to connect to Apache drill through JDBC/ODBC — Oracle Analytics

Oracle Analytics Cloud and Server

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

How to connect to Apache drill through JDBC/ODBC

Received Response
62
Views
4
Comments
Onkar Nath Tiwari
Onkar Nath Tiwari Rank 3 - Community Apprentice

I put a lot of efforts to connect to apche drill through JDBC/ODBC but none got successful.

I tried the links for

JDBC --> OBIEE tips and tricks: Using Apache Drill as a JDBC data source in OBIEE 12c

ODBC --> https://mapr.com/blog/essential-tips-using-apache-drill-obiee-12c/

but none worked.

After installing drill I am able to query data from command prompt but not through JDBC/ODBC driver.

pastedImage_3.png

I thought JDBC will be easier and tried JDBC but everytime I got the error

pastedImage_4.png

Any clue what's going wrong here ....

I looked in the log and found

<AdminServer> <[ACTIVE] ExecuteThread: '19' for queue: 'weblogic.kernel.Default (self-tuning)'> <weblogic> <> <565f7960-ce1d-4768-a5bd-5a84f46d0142-000005d0> <1537770284168> <[severity-value: 8] [rid: 0] [partition-id: 0] [partition-name: DOMAIN] > <BEA-240003> <Administration Console encountered the following error: java.lang.ClassNotFoundException: Cannot load driver: org.apache.drill.jdbc.Driver

But am not getting where exactly I need to modify the classpath to include the driverand get it loaded.

Please guide me  on this....

Thanks in advance

Onkar

Answers

  • Robert Angel
    Robert Angel Rank 8 - Analytics Strategist

    See ->

    https://docs.oracle.com/javase/tutorial/essential/environment/paths.html

    You will need to restart your system / services post reboot.

  • Onkar Nath Tiwari
    Onkar Nath Tiwari Rank 3 - Community Apprentice

    Thanks a lot Robert for the reply but I believe the classpath needs to be updated somewhere in weblogic server of obiee.

    Multiple times I started obiee server/services to resolve it but invain.

    Java path I already set to start drill in embedded mode and that worked.

    Need the make connection through jdbc/odbc to let it fetch data through obieew reports/dashboards

    Thanks

    Onkar

  • Joel
    Joel Rank 8 - Analytics Strategist

    Have you looked at @rmoff's RM blog which describes how you can hook Apache Drill up to OBIEE?

  • Onkar Nath Tiwari
    Onkar Nath Tiwari Rank 3 - Community Apprentice

    Thanks Joel...

    Yes I already refered the link...and in case of odbc connection I constantly used to get the error

    Odbc driver returned an error (SQLExecDirectW).

    State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. (HY000)

    State: HY000. Code: 43113. [nQSError: 43113] Message returned from OBIS. (HY000)

    State: HY000. Code: 43093. [nQSError: 43093] An error occurred while processing the EXECUTE PHYSICAL statement. (HY000)

    [nQSError: 16001] ODBC error state: 08S01 code: 1000 message: [MapR][Drill] (1000) Unknown error occurred while trying to connect.. (HY000)

    SQL Issued: {call NQSGetQueryColumnInfo('EXECUTE PHYSICAL CONNECTION POOL "Security"."SecurityDB" SELECT * FROM cp.`employee.json` LIMIT 3;')}

    In logs nothing other than the above I get although I set the log level to highest.

    What I analysed that after configuring as per the doc , the system is recognizing the driver but after that it gives the unknown general error for which I don't have any clue.

    Thanks

    Onkar