Forum Stats

  • 3,757,538 Users
  • 2,251,243 Discussions
  • 7,869,860 Comments

Discussions

How To Query Hive and Impala from Oracle using ODBC Heterogeneous Gateway

Introduction

Big Data technologies have spread throughout organizations and the challenge of integration with legacy systems must be addressed somehow. There are several ways to connect Oracle or other RDBMS systems with the big data ecosystem, such as dedicated connectors, ETL, and custom ones. In the following article we will demonstrate one simple method to connect and query from Oracle directly to Apache Hive and Cloudera Impala using the ODBC heterogeneous gateway. Heterogeneous gateways were developed by Oracle to address the direct connectivity and type translations from non-Oracle to Oracle databases. There are specialized gateways, subjected to licensing and the generic ODBC gateway which is free. The ODBC heterogeneous gateway is a generic gateway that can be used practically with any data source that provides a functional ODBC driver.

Apache Hive is a data warehouse framework built on top of Hadoop used for mostly for analytics. It is using a query-like language called HiveQL. All queries are translated into map reduce jobs and executed further in hadoop. It lacks transaction support and comes also with several limitations. More about Hive at https://hive.apache.org.

Cloudera Impala is a more elaborated framework, starting to get wider acceptance and support by more and more vendors such as Amazon, Oracle, MapR, and others. It is implemented as a Massive Parallel Processing query engine with SQL ANSI compatibility, having low latency that completely bypasses map reduce operations. Also it has support for many different file formats.  More about Impala (http://www.cloudera.com/documentation/enterprise/latest/topics/impala.html). 

Environmental Setup

Oracle server

Operating system: Oracle Virtual Box guest running OEL 6.5

Database server: Oracle Enterprise Edition Ver. 12.1.0.2.0

Hostname: Node1

IP address: 192.168.1.20

Cloudera QuickStart

Operating system: Oracle Virtual Box guest running CentOS 6.7

Cloudera version: 5.7

Hostname: Cloudera

IP address: 192.168.1.88

To setup the Cloudera environment we used a Cloudera Quickstart image that can be downloaded from http://www.cloudera.com/downloads/quickstart_vms/5-7.html. The hive tables used in this article are created according to Cloudera tutorial that can be found here (http://www.cloudera.com/developers/get-started-with-hadoop-tutorial/exercise-1.html)

Installing Cloudera Hive and Impala ODBC driver

As a perquisite to install the Cloudera Hive and Impala ODBC drivers we need to have installed unixODBC or iODBC. We have opted for unixODBC and installed as follows:

[[email protected] etc]# yum install unixODBC

Loaded plugins: refresh-packagekit, security

..........................................................................................................

Setting up Install Process

Resolving Dependencies

--> Running transaction check

--> Package unixODBC.x86_64 0:2.2.14-14.el6 will be installed

--> Finished Dependency Resolution

Dependencies Resolved

=============================================================

Package                     Arch                     Version                                    Repository                               

Size

=============================================================

Installing:

unixODBC                x86_64                    2.2.14-14.el6                           ol6_latest                              

377 k

Transaction Summary

============================================================

Install       1 Package(s)

Total download size: 377 k

Installed size: 1.1 M

Is this ok [y/N]: y

Downloading Packages:

unixODBC-2.2.14-14.el6.x86_64.rpm 

….............................................................................................................................  

Next, download the Cloudera Hive ODBC driver from the following address:

http://www.cloudera.com/downloads/connectors/hive/odbc/2-5-12.html

As root execute the following command to install the Cloudera Hive ODBC driver :

[[email protected] kit]# rpm -Uhv ClouderaHiveODBC-2.5.19.1004-1.el6.x86_64.rpm

Preparing...                                    ########################################### [100%]

   1:ClouderaHiveODBC                ########################################### [100%]

[[email protected] kit]#

Download the Cloudera Impala ODBC driver form the following location:

(http://www.cloudera.com/downloads/connectors/impala/odbc/2-5-22.html)

As root execute the following command to install the odbc cloudera impala driver :

[[email protected] kit]# rpm -Uhv ClouderaImpalaODBC-2.5.33.1004-1.el6.x86_64.rpm

Preparing...                                                           ###################### [100%]

   1:ClouderaImpalaODBC                                   ###################### [100%]

[[email protected] kit]#

Configuring the Cloudera Hive ODBC driver

The driver installation directory will be located in /opt/cloudera/hiveodbc. In the /opt/cloudera/hiveodbc/Setup directory we will find sample configuration files that can be used with few modification for performing the setup.

Create a directory as root /usr/local/odbc.This directory will be used as placement for odbcinst.ini:

[[email protected] ~]# mkdir -p /usr/local/odbc

Copy odbc.ini in /home/oracle directory as follows:

[[email protected] Setup]# cp odbc.ini /home/oracle


Next copy cloudera.hiveodbc.ini from /opt/cloudera/hiveodbc/lib/64 to /etc directory as follows:

[[email protected] 64]# cp cloudera.hiveodbc.ini /etc

Next, we will configure the environment variables for Cloudera Hive ODBC driver.

Open /home/oracle/.bash_profile for editing and add the following variables (marked with bold) as follows:

Next add in the /home/oracle/.bash_profile the following variables.

[[email protected] ~]$ vi .bash_profile

# .bash_profile

# Get the aliases and functions

if [ -f ~/.bashrc ]; then

            . ~/.bashrc

fi

# User specific environment and startup programs

export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1

export ORACLE_SID=ORCL

export ODBCINI=~/odbc.ini

export ODBCSYSINI=/usr/local/odbc

export CLOUDERAHIVEINI=/etc/cloudera.hiveodbc.ini

PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin

export LD_LIBRARY_PATH=$LIBRARY_PATH:$ORACLE_HOME/lib

Source the profile to get the environment variables loaded as follows:

[[email protected] ~]$ source .bash_profile

The odbc.ini file copied under /home/oracle is containing two examples for configuring Data source names (DSN) for the 32 and 64 driver version. All parameters are explained with comments in the sample files, comments which are omitted from the current listing. If you are interested in learning more about these parameter then check the driver documentation at http://www.cloudera.com/documentation/other/connectors/hive-odbc/latest.html.

We will change only the host, port, and UID under [Cloudera ODBC Driver for Apache Hive (64-bit) DSN] and rename the data source name to HIVEDSN as follows:

[HiveDSN]

Description     = Cloudera ODBC Driver for Apache Hive (64-bit) DSN

Driver              = /opt/cloudera/hiveodbc/lib/64/libclouderahiveodbc64.so

HOST                        = 192.168.1.88

PORT             = 10000

Schema            = default

ServiceDiscoveryMode                      = 0

ZKNamespace =

HiveServerType          = 2

AuthMech                  = 2

ThriftTransport          = 1

UseNativeQuery         = 0

KrbHostFQDN          = [Hive Server 2 Host FQDN]

KrbServiceName         = [Hive Server 2 Kerberos service name]

KrbRealm                    = [Hive Server 2 Kerberos realm]

SSL                              = 0

TwoWaySSL               = 0

ClientCert                   =

ClientPrivateKey        =

ClientPrivateKeyPassword                =

UID=cloudera

Next, test the connection as follows:

[[email protected] ~]$ isql -v hivedsn

+---------------------------------------+

| Connected!                            |

|                                                |

| sql-statement                         |

| help [tablename]                   |

| quit                                        |

|                                                |

+---------------------------------------+

SQL> 

List the tables and try a count against the orders table to ensure that everything is working fine:

SQL> show tables;

+-----------------------------------+

| tab_name |+-----------------------------------------+

| categories || customers || departments || order_items || orders || products |+------------------------------------------+

SQLRowCount returns -1

6 rows fetched

SQL> select count(*) from orders;

+---------------------+

| EXPR_1              |

+---------------------+

| 68883               |

+---------------------+

SQLRowCount returns -1

1 rows fetched

The connection and data retrieval seems to work fine.

Oracle ODBC transparent gateway configuration for Cloudera Hive ODBC

Under the directory $ORACLE_HOME/hs/admin we created a initialization parameters file named initHIVEDSN.ora (here we used the dg4odbc.ini file as a template) with the following contents:

[[email protected] admin]$ vi initHIVEDSN.ora

# This is a sample agent init file that contains the HS parameters that are needed for the Database Gateway for ODBC

HS_FDS_CONNECT_INFO = "HIVEDSN"

HS_FDS_TRACE_LEVEL = 0

HS_FDS_SHAREABLE_NAME = /opt/cloudera/hiveodbc/lib/64/libclouderahiveodbc64.so

#

# ODBC specific environment variables

#

set ODBCINI=/home/oracle/odbc.ini

Note: The name of the init file must be init<HS_FDS_CONNECT_INFO value>.ora otherwise the connection will fail.

Listener configuration

Open the listener.ora file

[[email protected] admin]$ vi /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (PROGRAM = dg4odbc)

      (ARGS = ENVS=LD_LIBRARY_PATH=/opt/cloudera/hiveodbc/lib/64/libclouderahiveodbc64.so)

      (SID_NAME = HIVEDSN)

      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)

    )

  )

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1521))

    )

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

  )

ADR_BASE_LISTENER = /u01/app/oracle

Add a network service in tnsnames.ora, called HIVEDSN as follows :

HIVEDSN =

  (DESCRIPTION=

(ADDRESS=(PROTOCOL=tcp)(HOST=node1)(PORT=1521))

(CONNECT_DATA=(SID=HIVEDSN))

      (HS=OK)

    )

Reload the listener as follows:

[[email protected] admin]$ lsnrctl reload

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 20-JUN-2016 08:05:57

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=node1)(PORT=1521)))

The command completed successfully

  [[email protected] admin]$

Check the services availability:

[[email protected] admin]$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 20-JUN-2016 08:06:32

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=node1)(PORT=1521)))

STATUS of the LISTENER

-----------------------------------------------------------------------------------------------------------------------

Alias                            LISTENER

Version                        TNSLSNR for Linux: Version 12.1.0.2.0 - Production

Start Date                    19-JUN-2016 08:04:43

Uptime                        1 days 0 hr. 1 min. 49 sec

Trace Level                 off

Security                       ON: Local OS Authentication

SNMP                          OFF

Listener Parameter File   /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora

Listener Log File /u01/app/oracle/diag/tnslsnr/node1/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=node1)(PORT=1521)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=node1)(PORT=5500))

(Security=(my_wallet_directory=/u01/app/oracle/product/12.1.0/dbhome_1/admin/ORCL/xdb_wallet))(Presentation=HTTP)(Session=RAW))

Services Summary...

Service "HIVEDSN" has 1 instance(s).

  Instance "HIVEDSN", status UNKNOWN, has 1 handler(s) for this service...

Service "ORCL" has 1 instance(s).

  Instance "ORCL", status READY, has 1 handler(s) for this service...

Service "ORCLXDB" has 1 instance(s).

  Instance "ORCL", status READY, has 1 handler(s) for this service...

The command completed successfully

Ping the network service HIVEDSN to check its availability as follows:

[[email protected] admin]$ tnsping HIVEDSN

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 20-JUN-2016 08:07:56

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:

/u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=node1)(PORT=1521)) (CONNECT_DATA=(SID=HIVEDSN)) (HS=OK))

OK (10 msec)

Create a public database link called hivedsn as follows:

[[email protected] admin]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jun 20 08:09:35 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> create public database link hivedsn connect to cloudera identified by cloudera using 'HIVEDSN';

Database link created.

Next, try to fetch some data from hive using the database link:

SQL> select * from [email protected];

select * from [email protected]

                        *

ERROR at line 1:

ORA-28500: connection from ORACLE to a non-Oracle system returned this message:

[Cloudera][ODBC] (11560) Unable to locate SQLGetPrivateProfileString function.

{HY000,NativeErr = 11560}

ORA-02063: preceding 2 lines from HIVEDSN

The SQLGetPrivateProfileString function is contained and exported from libodbcinst.so shared library. To solve this issue we have to add the complete path (with bold) into /etc/cloudera.hiveodbc.ini driver configuration file as follows.:

[Driver]

ODBCInstLib=/usr/lib64/libodbcinst.so

ErrorMessagesPath=/opt/cloudera/hiveodbc/ErrorMessages/

LogLevel=0

LogPath=

SwapFilePath=/tmp

Also if the Hive ODBC driver is not linked with libodbcinst.so you should add the LD_PRELOAD variable to bash_profile or issue export LD_PRELOAD=/usr/lib64/libodbcinst.so.

[[email protected] ~]$ export LD_PRELOAD=/usr/lib64/libodbcinst.so

Verify that libodbcinst.so is loaded as follows:

[[email protected] ~]$ ldd /opt/cloudera/impalaodbc/lib/64/libclouderaimpalaodbc64.so

            linux-vdso.so.1 =>  (0x00007fffad5ff000)

            libdl.so.2 => /lib64/libdl.so.2 (0x00007fd1af2b2000)

            libpthread.so.0 => /lib64/libpthread.so.0 (0x00007fd1af094000)

            libsasl2.so.2 => /usr/lib64/libsasl2.so.2 (0x00007fd1aee7a000)

            librt.so.1 => /lib64/librt.so.1 (0x00007fd1aec72000)

            libresolv.so.2 => /lib64/libresolv.so.2 (0x00007fd1aea57000)

            libkeyutils.so.1 => /lib64/libkeyutils.so.1 (0x00007fd1ae854000)

            libstdc++.so.6 => /usr/lib64/libstdc++.so.6 (0x00007fd1ae54e000)

            libm.so.6 => /lib64/libm.so.6 (0x00007fd1ae2c9000)

            libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007fd1ae0b3000)

            libc.so.6 => /lib64/libc.so.6 (0x00007fd1add20000)

            /lib64/ld-linux-x86-64.so.2 (0x00000033a1a00000)

            libcrypt.so.1 => /lib64/libcrypt.so.1 (0x00007fd1adae8000)

            libfreebl3.so => /lib64/libfreebl3.so (0x00007fd1ad886000)

Reload the listener, connect again and reissue the select as follows:

[[email protected] admin]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jun 20 08:23:36 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select * from [email protected];

select * from [email protected]

                        *

ERROR at line 1:

ORA-28500: connection from ORACLE to a non-Oracle system returned this message:

[Cloudera][ODBC] (11470) Transactions are not supported. {HYC00,NativeErr =

11470}

ORA-02063: preceding 2 lines from HIVEDSN

The cause of this error is that  Hive is a non transactional/non logging data store, therefore we should issue our transactions in read only and non-logging mode. We can tweak this by using the heterogeneous service HS_TRANSACTION_MODEL initialization parameter changed to READ_ONLY_AUTOCOMMIT. More about initialization parameters used by ODBC heterogeneous gateway can be found at (https://docs.oracle.com/database/121/ODBCU/feature.htm#ODBCU763)

Open the initHIVEDSN.ora parameters file for editing and add the HS_TRANSACTION_MODEL parameter as follows:

# This is a sample agent init file that contains the HS parameters that are

# needed for the Database Gateway for ODBC

HS_FDS_CONNECT_INFO = "HIVEDSN"

HS_FDS_TRACE_LEVEL = 0

HS_FDS_SHAREABLE_NAME = /opt/cloudera/hiveodbc/lib/64/libclouderahiveodbc64.so

HS_TRANSACTION_MODEL=READ_ONLY_AUTOCOMMIT

#

# ODBC specific environment variables

#

set ODBCINI=/home/oracle/odbc.ini

Save, connect again and reissue the statement as follows:

SQL> select * from [email protected]

-------------------------------------------------------------------------------------------

1530 David        Smith           XXXXXXXXX         XXXXXXXXX                 

The connection and data retrieval is fully functional.

Configuring the Cloudera Impala ODBC driver

The driver installation directory will be located in /opt/cloudera/impalaodbc. In the /opt/cloudera/impalaodbc/Setup directory we will find sample configuration files that can be used with few modification for performing the setup.

Create a directory as root /usr/local/odbc as follows:

[[email protected] ~]# mkdir -p /usr/local/odbc

This directory will be used as placement for odbcinst.ini.

Copy odbc.ini in /home/oracle directory as follows:

[[email protected] Setup]# cp odbc.ini /home/oracle

Next, copy cloudera.impalaodbc.ini from /opt/cloudera/hiveodbc/lib/64

[[email protected] 64]# cp cloudera.impalaodbc.ini /etc

Next, we will configure the environment variables for Cloudera Impala ODBC driver. Open .bash_profile for editing and add the following variables (marked with bold) as follows:

[[email protected] ~]$ vi .bash_profile

# .bash_profile

# Get the aliases and functions

if [ -f ~/.bashrc ]; then

            . ~/.bashrc

fi

# User specific environment and startup programs

export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1

export ORACLE_SID=ORCL

export ODBCINI=~/odbc.ini

export ODBCSYSINI=/usr/local/odbc

export SIMBAINI=/etc/cloudera.impalaodbc.ini

PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin

  export LD_LIBRARY_PATH=$LIBRARY_PATH:$ORACLE_HOME/lib

Source the profile to get the environment variables loaded:

[[email protected] ~]$ source .bash_profile

Similarly with the Hive driver, the odbc.ini file copied under /home/oracle is containing two examples for configuring DSN for the 32 and 64 driver version. In our case we are going to use the 64 version. All parameters are explained with comments in the sample file, which are omitted from the listing. For more information about parameters you can consult the driver installation and configuration manual located at http://www.cloudera.com/documentation/other/connectors/impala-odbc/2-5-22.html.

We will change only the host, port, rename the data source to IMPLDSN as follows:

[ImplDSN]

# Description: DSN Description.

# This key is not necessary and is only to give a description of the data source.

Description=Cloudera ODBC Driver for Impala (64-bit) DSN

# Driver: The location where the ODBC driver is installed to.

Driver=/opt/cloudera/impalaodbc/lib/64/libclouderaimpalaodbc64.so

HOST=192.168.1.88

PORT=21050

Database=default

AuthMech=0

Test the connection as follows:

[[email protected] ~]$ isql -v ImplDSN

+---------------------------------------+

| Connected!                   |

|                                       |

| sql-statement                |

| help [tablename]          |

| quit                               |

|                                       |

+---------------------------------------+

SQL>

List the tables and try a count against one table to ensure that everything is working fine:

SQL> show tables;

+------------------+

|tab_name                                                                                                                                                                                                                                                 |+------------------+

| categories

|| customers

|| departments

|| order_items

|| orders

|| products

|+-------------------+

SQLRowCount returns -1

6 rows fetched

SQL> select count(*) from orders;

+---------------------+

| EXPR_1              |

+---------------------+

| 68883               |

+---------------------+

SQLRowCount returns -1

1 rows fetched

Oracle ODBC Transparent Gateway Configuration for Cloudera Impala ODBC Driver

Under directory $ORACLE_HOME/hs/admin we created an initialization parameters file named initIMPLDSN.ora (we used the dg4odbc.ini file as a template) with the following contents :

[[email protected] admin]$ vi initHIVEDSN.ora

# This is a sample agent init file that contains the HS parameters that are

# needed for the Database Gateway for ODBC

HS_FDS_CONNECT_INFO = "IMPLDSN"

HS_FDS_TRACE_LEVEL = 0

HS_FDS_SHAREABLE_NAME = /opt/cloudera/hiveodbc/lib/64/libclouderahiveodbc64.so

HS_TRANSACTION_MODEL=READ_ONLY_AUTOCOMMIT

#

# ODBC specific environment variables

#

set ODBCINI=/home/oracle/odbc.ini

  Important : The name of the init file must be init<HS_FDS_CONNECT_INFO value>.ora otherwise the connection will fail.

Add also libodbcinst.so path in /etc/cloudera.impalaodbc.ini driver configuration file as follows :

[Driver]

ODBCInstLib=/usr/lib64/libodbcinst.so

ErrorMessagesPath=/opt/cloudera/impalaodbc/ErrorMessages/

LogLevel=0

LogPath=

SwapFilePath=/tmp

Listener configuration

Open the listener.ora file for editing and add the following (marked with bold):

[[email protected] admin]$ vi /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (PROGRAM = dg4odbc)

      (ARGS = ENVS=LD_LIBRARY_PATH=/opt/cloudera/impalaodbc/lib/64/libclouderaimpalaodbc64.so)

      (SID_NAME = IMPLDSN)

      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)

    )

  )

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1521))

    )

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

  )

ADR_BASE_LISTENER = /u01/app/oracle

Add a network service in tnsnames.ora as follows:

IMPLDSN =

  (DESCRIPTION=

(ADDRESS=(PROTOCOL=tcp)(HOST=node1)(PORT=1521))

      (CONNECT_DATA=(SID=IMPLDSN))

      (HS=OK)

    )

Reload the listener as follows:

[[email protected] admin]$ lsnrctl reload

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 20-JUN-2016 08:05:57

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=node1)(PORT=1521)))

The command completed successfully

[[email protected] admin]$

Check the services availability:

[[email protected] admin]$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 20-JUN-2016 08:06:32

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=node1)(PORT=1521)))

STATUS of the LISTENER

------------------------------------------------------------------------------------------------------------------------

Alias                            LISTENER

Version                        TNSLSNR for Linux: Version 12.1.0.2.0 - Production

Start Date                    19-JUN-2016 08:04:43

Uptime                        1 days 0 hr. 1 min. 49 sec

Trace Level                 off

Security                       ON: Local OS Authentication

SNMP                          OFF

Listener Parameter File /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora

Listener Log File             /u01/app/oracle/diag/tnslsnr/node1/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=node1)(PORT=1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=node1)(PORT=5500))

(Security=(my_wallet_directory=/u01/app/oracle/product/12.1.0/dbhome_1/admin/ORCL/xdb_wallet))

(Presentation=HTTP)(Session=RAW))

Services Summary...

Service "IMPLDSN" has 1 instance(s).

  Instance "IMPLDSN", status UNKNOWN, has 1 handler(s) for this service...

Service "ORCL" has 1 instance(s).

  Instance "ORCL", status READY, has 1 handler(s) for this service...

Service "ORCLXDB" has 1 instance(s).

  Instance "ORCL", status READY, has 1 handler(s) for this service...

The command completed successfully

Ping the network service IMPLDSN to check its availability as follows:

[[email protected] admin]$ tnsping IMPLDSN

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 20-JUN-2016 08:07:56

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:

/u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION=

(ADDRESS=(PROTOCOL=tcp)(HOST=node1)(PORT=1521))

(CONNECT_DATA=(SID=IMPLDSN)) (HS=OK))

OK (10 msec)

Create a public database link to connect from oracle as follows:

[[email protected] admin]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jun 20 08:09:35 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> create public database link impldsn connect to cloudera identified by cloudera using 'IMPLDSN';

  Database link created.

Test the database link as follows:

SQL> select * from [email protected]

-----------------------------------------------------------------------------------

1530 David     Smith               XXXXXXXXX         XXXXXXXXX      

  Connection and data retrieval from Cloudera Impala seems fine.

Observations

Whenever possible, use Impala over Hive because of the several limitations in terms of speed and syntax. It is also possible to have several syntax differences when you query from Hive or Impala directly from Oracle. If some complex query should be executed that it is better to create a view locally in Hive or Impala and try to select that directly. Another benefit is that this will leverage query optimizations on the Impala or Hive side.

One example, if you try to run the following query, you will get a warning that the count inside this query is not a supported feature :

SQL> l

  1  select c."category_name", count("order_item_quantity") as count

  2  from [email protected] oi

  3  inner join [email protected] p on oi."order_item_product_id" = p."product_id"

  4  inner join [email protected] c on c."category_id" = p."product_category_id"

  5  group by c."category_name"

  6* order by count("order_item_quantity") desc

SQL> /

select c."category_name", count("order_item_quantity") as count

ERROR at line 1:

ORA-02070: database IMPALADSN does not support in this context

To solve this issue, create a view in Impala :

[[email protected] Desktop]$ impala-shell

Starting Impala Shell without Kerberos authentication

Connected to quickstart.cloudera:21000

Server version: impalad version 2.5.0-cdh5.7.0 RELEASE (build ad3f5adabedf56fe6bd9eea39147c067cc552703)

***********************************************************************************

Welcome to the Impala shell. Copyright (c) 2015 Cloudera, Inc. All rights reserved.

(Impala Shell v2.5.0-cdh5.7.0 (ad3f5ad) built on Wed Mar 23 11:33:33 PDT 2016)

After running a query, type SUMMARY to see a summary of where time was spent.

***********************************************************************************

[quickstart.cloudera:21000] >

create view summaryvw as -- Most popular product categories

                               > select c.category_name, count(order_item_quantity) as count

                               > from order_items oi

                               > inner join products p on oi.order_item_product_id = p.product_id

                               > inner join categories c on c.category_id = p.product_category_id

                               > group by c.category_name

                               > order by count desc

                               > limit 10;

Query: create view summaryvw as -- Most popular product categories

select c.category_name, count(order_item_quantity) as count

from order_items oi

inner join products p on oi.order_item_product_id = p.product_id

inner join categories c on c.category_id = p.product_category_id

group by c.category_name

order by count desc

limit 10

Reissue the query this time against the sumaryvw view created in Impala:

  1* select * from [email protected]

SQL> /

category_name count

-------------------------------------------------------------------------------- ----------

Cleats 24551

Men's Footwear 22246

Women's Apparel                                                                                 21035

Indoor/Outdoor Games 19298

Fishing                                                                                                 17325

Water Sports 15540

Camping & Hiking 13729

Cardio Equipment 12487

Shop By Sport 10984

Electronics 3156

10 rows selected.

Summary

In this article we demonstrated how to use Cloudera Hive and Impala ODBC drivers to connect from Oracle using ODBC heterogeneous gateway. This approach could be very helpful when we want to retrieve small amount of data using relative simple queries and need to setup the connection very quickly. Also the method can be used for archive or backup of sensitive data from Hive or Impala.

About the Authors

Y V Ravi Kumar is an Oracle ACE and Oracle Certified Master (OCM) with 17 years of experience in BFSI vertical. He is also OCP in Oracle 8i, 9i, 10g, 11g &12c and Certified in Golden Gate, RAC, Performance Tuning & Oracle Exadata. He continuously motivates many DBAs and helps the Oracle Community by publishing his tips/ideas/suggestions/solutions in his blog. He has written 40+ OTN articles on Oracle Exadata, Oracle RAC and Oracle GoldenGate for OTN for Spanish, Portuguese and English and 17 articles for TOAD World, 2 Articles for UKOUG, 3 Articles for OTech Magazine and 2 Articles for Redgate. He is a frequent Oracle speaker in @OTN, AIOUG, Sangam and IOUG. Learn more from his profile at @LaserSoft

Adrian Neagu has over 15 years of experience as a database administrator, having expertise in various RDBMS systems. He has experience in many areas such as financial industry, the pharmaceutical industry, telecom and aviation. He is an Oracle Certified Master 10g and 11g, Oracle Certified Professional 9i, 10g, and 11g, Cloudera Certified Administrator for Apache Hadoop, IBM DB2 Certified Administrator version 8.1.2 and 9, IBM DB2 9 Advanced Certified Administrator 9, and Sun Certified System Administrator Solaris 10. He is an expert in many areas of database administration, BigData and Operating systems including high-performance tuning, high availability, replication, backup, and recovery.

Hassan.Mkhlalati

Comments

  • User_FSTRL
    User_FSTRL Member Posts: 2 Red Ribbon

    Thank you for the detailed steps and it is very helpful. I am following  your post to create a dblink from oracle 12C to a Hive database, but ran into an unexpected error when 'tnsping HIVEDSN'.

    Here is the detailed steps I did, I would appreciate if you can give me clue about what I might have done wrong.

    Linda

    1. verified 'isql -v HIVEDSN' worked fine to confirm  hive data source is fine

    [lvdma01]$ isql -v HIVEDSN

    +---------------------------------------+

    | Connected!                            |

    |                                       |

    | sql-statement                         |

    | help [tablename]                      |

    | quit                                  |

    |                                       |

    +---------------------------------------+

    2. created initHIVEDSN.ora in $ORACLE_HOME/hs/admin with following  content:

    HS_FDS_CONNECT_INFO="HIVEDSN"

    HS_FDS_TRACE_LEVEL = 0

    HS_FDS_SHAREABLE_NAME=/opt/cloudera/hiveodbc/lib/64/libclouderahiveodbc64.so

    3. changed listener.ora to listen to the gateway:

    SID_LIST_LISTENER_12c=

       (SID_LIST=

          (SID_DESC=

             (SID_NAME=HIVEDSN)

             (ORACLE_HOME=/u01/app/oracle/product/12.1.0.2)

             (PROGRAM=dg4odbc)

             (ENVS=LD_LIBRARY_PATH=/opt/cloudera/hiveodbc/lib/64/libclouderahiveodbc64.so:$ORACLE_HOME/lib)

          )

       )

    4. reloaded the listener.ora and 'lsnrctl status LISTENER_12c' shows  instance 'HIVESN' as expected

    $ lsnrctl status LISTENER_12c

    ..

    Services Summary...

    Service "HIVEDSN" has 1 instance(s).

      Instance "HIVEDSN", status UNKNOWN, has 1 handler(s) for this service...

    Service "lvdma01.amgen.com" has 1 instance(s).

      Instance "lvdma01", status READY, has 1 handler(s) for this service...

    The command completed successfully

    5. added entry in tnsnames.ora:

    HIVEDSN=

       (DESCRIPTION=

          (ADDRESS=

             (PROTOCOL=TCP)

             (HOST=...)

             (PORT=1775)

          )

          (CONNECT_DATA=

             (SID=HIVEDSN))

          (HS=OK))

    6. when I 'tnsping' the entry, I ran into error:

    $ tnsping HIVEDSN

    /u04/oradmin/config/listener_12c/sqlnet.ora

    TNS-03505: Failed to resolve name

    [lvdma01]$ echo $TNS_ADMIN

    /u04/oradmin/config/listener_12c

    Parikshit Kumar Singh
  • ravikumar.sv
    ravikumar.sv Member Posts: 1,072

    Hi

    We have our Hadoop/Hive cluster behind Kerberos. Is it still possible to use Oracle ODBC gateway to connect Hive and be able access its data using Oracle's DB Link?

    Regards,

    RK

  • codiant
    codiant Member Posts: 131 Blue Ribbon

    Hi!

    Thanks for sharing wonderful content. It will help me to gain knowledge.

  • Hassan.Mkhlalati
    Hassan.Mkhlalati Member Posts: 2 Green Ribbon

    Many Many thanks wonderful explaining

  • User_KEPK5
    User_KEPK5 Member Posts: 1 Red Ribbon

    Is it possible to connect 2 different hadoop hosts?