Please see my other blog for Oracle EBusiness Suite Posts - EBMentors

Search This Blog

Note: All the posts are based on practical approach avoiding lengthy theory. All have been tested on some development servers. Please don’t test any post on production servers until you are sure.

Tuesday, December 19, 2017

Configure Hortonworks Hive ODBC Driver for Oracle HS


The Hortonworks Hive ODBC Driver is used for direct SQL and HiveQL access to Apache
Hadoop / Hive distributions, enabling Business Intelligence (BI), analytics, and reporting on
Hadoop / Hive-based data. The driver efficiently transforms an application’s SQL query into
the equivalent form in HiveQL.



Setup/Configuration

Download Hive Driver


Download Hive driver  from below location and extract on your desired location.

https://hortonworks.com/downloads/#data-platform
https://www.cloudera.com/downloads/connectors/hive/odbc/2-5-12.html
https://www.progress.com/download/thank-you?interface=odbc&ds=apache-hadoop-hive&os=linux-64

Installing the Driver

Make sure that you use the version of the driver that matches the bitness (32/64) of the client application.  The Hortonworks ODBC Driver files are installed in the following directories:

• /usr/lib/hive/lib/native/hiveodbc contains the release notes and the Hive ODBC Driver Installation and Configuration Guide.

• /usr/lib/hive/lib/native/hiveodbc/ErrorMessages contains error message files required by the driver.

• /usr/lib/hive/lib/native/hiveodbc/Setup contains sample configuration files named odbc.ini and odbcinst.ini.


• /usr/lib/hive/lib/native/Linux-amd64-64 contains the 64-bit shared libraries and the hortonworks.hiveodbc.ini configuration file.


[root@en01 hadoopsw]# yum --nogpgcheck localinstall hive-odbc-native-2.1.12.1017-1.el7.x86_64.rpm
Loaded plugins: langpacks, product-id, search-disabled-repos, subscription-manager
This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
Repository 'local' is missing name in configuration, using id
Examining hive-odbc-native-2.1.12.1017-1.el7.x86_64.rpm: hive-odbc-native-2.1.12.1017-1.x86_64
Marking hive-odbc-native-2.1.12.1017-1.el7.x86_64.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package hive-odbc-native.x86_64 0:2.1.12.1017-1 will be installed
--> Finished Dependency Resolution
ambari-2.5.0.3                                                                                                            | 2.9 kB  00:00:00
epel/x86_64/metalink                                                                                                      | 6.4 kB  00:00:00
epel/x86_64                                                                                                               | 4.7 kB  00:00:00
epel/x86_64/updateinfo                                                                                                    | 858 kB  00:00:28
https://ftp.yzu.edu.tw/Linux/Fedora-EPEL/7/x86_64/repodata/b2f68d3910acd4e6b1f2c96337129e288afaf2003daefdd78bb7c8a1da1ae45f-primary.sqlite.bz2: [Errno 14] curl#56 - "TCP connection reset by peer"
Trying other mirror.
https://mirrors.tongji.edu.cn/epel/7/x86_64/repodata/b2f68d3910acd4e6b1f2c96337129e288afaf2003daefdd78bb7c8a1da1ae45f-primary.sqlite.bz2: [Errno 12] Timeout on https://mirrors.tongji.edu.cn/epel/7/x86_64/repodata/b2f68d3910acd4e6b1f2c96337129e288afaf2003daefdd78bb7c8a1da1ae45f-primary.sqlite.bz2: (28, 'Connection timed out after 30000 milliseconds')
Trying other mirror.
epel/x86_64/primary_db                                                                                                    | 6.2 MB  00:00:30
google-chrome                                                                                                             |  951 B  00:00:00
google-chrome/primary                                                                                                     | 1.9 kB  00:00:00
local                                                                                                                     | 2.9 kB  00:00:00
mysql-connectors-community/x86_64                                                                                         | 2.5 kB  00:00:00
mysql-tools-community/x86_64                                                                                              | 2.5 kB  00:00:00
mysql57-community/x86_64                                                                                                  | 2.5 kB  00:00:00

Dependencies Resolved

=================================================================================================================================================
 Package                        Arch                 Version                      Repository                                                Size
=================================================================================================================================================
Installing:
 hive-odbc-native               x86_64               2.1.12.1017-1                /hive-odbc-native-2.1.12.1017-1.el7.x86_64                53 M

Transaction Summary
=================================================================================================================================================
Install  1 Package

Total size: 53 M
Installed size: 53 M
Is this ok [y/d/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : hive-odbc-native-2.1.12.1017-1.x86_64                                                                                         1/1
google-chrome                                                                                                                                3/3
  Verifying  : hive-odbc-native-2.1.12.1017-1.x86_64                                                                                         1/1

Installed:
  hive-odbc-native.x86_64 0:2.1.12.1017-1


Complete!


Verifying the Version Number


[root@en01 hadoopsw]# odbcinst --version
unixODBC 2.3.1

[root@en01 hadoopsw]# rpm -qa | grep hive-odbc-native
hive-odbc-native-2.1.12.1017-1.x86_64

[root@en01 hadoopsw]#  rpm -qlp  hive-odbc-native-2.1.12.1017-1.el7.x86_64.rpm

/usr/lib/hive
/usr/lib/hive/lib
/usr/lib/hive/lib/native
/usr/lib/hive/lib/native/Linux-amd64-64
/usr/lib/hive/lib/native/Linux-amd64-64/HiveODBC.did
/usr/lib/hive/lib/native/Linux-amd64-64/cacerts.pem
/usr/lib/hive/lib/native/Linux-amd64-64/hortonworks.hiveodbc.ini
/usr/lib/hive/lib/native/Linux-amd64-64/libhortonworkshiveodbc64.so
/usr/lib/hive/lib/native/hiveodbc
/usr/lib/hive/lib/native/hiveodbc/EULA.txt
/usr/lib/hive/lib/native/hiveodbc/ErrorMessages
/usr/lib/hive/lib/native/hiveodbc/ErrorMessages/en-US
/usr/lib/hive/lib/native/hiveodbc/ErrorMessages/en-US/DSMessages.xml
/usr/lib/hive/lib/native/hiveodbc/ErrorMessages/en-US/HiveODBCMessages.xml
/usr/lib/hive/lib/native/hiveodbc/ErrorMessages/en-US/ODBCMessages.xml
/usr/lib/hive/lib/native/hiveodbc/ErrorMessages/en-US/SQLEngineMessages.xml
/usr/lib/hive/lib/native/hiveodbc/ErrorMessages/en-US/ThriftExtensionMessages.xml
/usr/lib/hive/lib/native/hiveodbc/Hortonworks Hive ODBC Driver User Guide.pdf
/usr/lib/hive/lib/native/hiveodbc/Release Notes.txt
/usr/lib/hive/lib/native/hiveodbc/Setup
/usr/lib/hive/lib/native/hiveodbc/Setup/odbc.ini
/usr/lib/hive/lib/native/hiveodbc/Setup/odbcinst.ini


[root@en01 hadoopsw]# whereis hive

hive: /usr/lib/hive



Configuring ODBC Connection

Configuration Files


ODBC driver managers use configuration files to define and configure ODBC data sources and drivers. By default, the following configuration files are used:

• .odbc.ini is used to define ODBC data sources, and it is required for DSNs.
• .odbcinst.ini is used to define ODBC drivers, and it is optional.


The driver installation contains abobe configuration files in the Setup directory.

These files are located in the user's home directory. Also, by default the Hive ODBC Driver is configured using the hortonworks.hiveodbc.ini file, which is required. This file is located in one of the following directories depending on the version of the driver that you are using:

• /usr/lib/hive/lib/native/Linux-amd32-32 for the 32-bit driver on Linux.
• /usr/lib/hive/lib/native/Linux-amd64-64 for the 64-bit driver on Linux.



Configuring the Environment


Use three environment variables, ODBCINI, ODBCSYSINI, and HORTONWORKSHORTONWORKSHIVEODBCINI, to specify different locations for the odbc.ini, odbcinst.ini, and hortonworks.hiveodbc.ini configuration files.


export ODBCINI=/usr/lib/hive/lib/native/hiveodbc/Setup/odbc.ini
export ODBCSYSINI=/usr/lib/hive/lib/native/hiveodbc/Setup/odbcinst.ini
export HORTONWORKSHORTONWORKSHIVEODBCINI=/usr/lib/hive/lib/native/Linux-amd64-64/hortonworks.hiveodbc.ini



Defining DSNs in odbc.ini


ODBC Data Source Names (DSNs) are defined in the odbc.ini configuration file. This file is divided into several sections:

• [ODBC] is optional. This section is used to control global ODBC configuration, such as ODBC tracing.

• [ODBC Data Sources] is required. This section lists the DSNs and associates them with a driver. The driver can be specified by the name given in odbcinst.ini or by the path to the driver shared object file.

• A section having the same name as the data source specified in the [ODBC Data Sources] section is required to configure the data source.


vi /usr/lib/hive/lib/native/hiveodbc/Setup/odbc.ini

[ODBC]
# Specify any global ODBC configuration here such as ODBC tracing.

[ODBC Data Sources]
Hortonworks Hive 32-bit=Hortonworks Hive ODBC Driver 32-bit
Hortonworks Hive 64-bit=Hortonworks Hive ODBC Driver 64-bit
# add a new entry for Hive Data Source Name (DSN),
HiveDSN=Hortonworks Hive ODBC Driver 64-bit

[HiveDSN]

Description=Hortonworks Hive ODBC Driver (64-bit) DSN
Driver=/usr/lib/hive/lib/native/Linux-amd64-64/libhortonworkshiveodbc64.so
HOST=dn04
PORT=10000
HiveServerType=2
ThriftTransport=SASL

Schema=scott

....



Specifying ODBC Drivers in odbcinst.ini

ODBC drivers are defined in the odbcinst.ini configuration file. This configuration file is optional because drivers can be specified directly in the odbc.ini configuration file.

The odbcinst.ini file is divided into the following sections:

• [ODBC Drivers] lists the names of all the installed ODBC drivers.

• For each driver, a section having the same name as the driver name specified in the [ODBC Drivers] section lists the driver attributes and values.


vi /usr/lib/hive/lib/native/hiveodbc/Setup/odbcinst.ini

[ODBC Drivers]
Hortonworks Hive ODBC Driver 32-bit=Installed
Hortonworks Hive ODBC Driver 64-bit=Installed

[Hortonworks Hive ODBC Driver 32-bit]
Description=Hortonworks Hive ODBC Driver (32-bit)
Driver=/usr/lib/hive/lib/native/Linux-i386-32/libhortonworkshiveodbc32.so

[Hortonworks Hive ODBC Driver 64-bit]
Description=Hortonworks Hive ODBC Driver (64-bit)
Driver=/usr/lib/hive/lib/native/Linux-amd64-64/libhortonworkshiveodbc64.so

## The option below is for using unixODBC when compiled with -DSQL_WCHART_CONVERT.
## Execute 'odbc_config --cflags' to determine if you need to uncomment it.

# IconvEncoding=UCS-4LE




Configuring Driver Settings in hortonworks.hiveodbc.ini

The hortonworks.hiveodbc.ini file contains configuration settings for the hive ODBC Driver. Settings that you define in this file apply to all connections that use the driver.

You do not need to modify the settings in the hortonworks.hiveodbc.ini file to use the driver and connect to your data source. However, to help troubleshoot issues, you can configure the hortonworks.hiveodbc.ini file to enable logging in the driver.

vi /usr/lib/hive/lib/native/Linux-amd64-64/hortonworks.hiveodbc.ini


[Driver]

ODBCInstLib=/usr/lib64/libodbcinst.so
ErrorMessagesPath=/usr/lib/hive/lib/native/hiveodbc/ErrorMessages/
LogLevel=0
LogPath=
SwapFilePath=/tmp

Testing the Connection


To test the connection, you can use an ODBC-enabled client application. For a basic connection test, you can also use the test utilities that are packaged with your driver manager installation. For example, the iODBC driver manager includes simple utilities called iodbctest and iodbctestw. Similarly, the unixODBC driver manager includes simple utilities called isql and iusql.

root@en01 hadoopsw]# iodbctest DSN=HiveDSN

iODBC Demonstration program
This program shows an interactive SQL processor
Driver Manager: 03.52.0709.0909
Driver: 2.1.12.1017 (Hortonworks Hive ODBC Driver)


SQL>

root@en01 hadoopsw]# isql -v HiveDSN
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> show tables
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tab_name                                                                                                                                                                                                                                                       |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| dept                                                                                                                                                                                                                                                           |
| dual                                                                                                                                                                                                                                                           |
| emp                                                                                                                                                                                                                                                            |
| emptalend                                                                                                                                                                                                                                                      |
| hive_vw_syslogs_current_regex                                                                                                                                                                                                                                  |
| metadata                                                                                                                                                                                                                                                       |
| presto_view_syslogs_regex                                                                                                                                                                                                                                      |
| presto_vw_syslogs2                                                                                                                                                                                                                                             |
| scottmetadata                                                                                                                                                                                                                                                  |
| testorc                                                                                                                                                                                                                                                        |
| testtext                                                                                                                                                                                                                                                       |
| v1                                                                                                                                                                                                                                                             |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
SQLRowCount returns -1
12 rows fetched

SQL>


Configuring Oracle to use  ODBC Driver


Oracle Database supports heterogeneous connectivity for non-oracle database sources. To establish such heterogeneous connectivity Oracle database need Oracle Gateways and ODBC drivers. We have already configured ODBC driver and now we configure Oracle ODBC Gateway. We are using Oracle 12c for the purpose of this post and you can see the odbc gateway executable (dg3odbc) in ORACLE_HOME/bin. If you are using 11g then you can download and install and use the same way as we using for 12c.





Oracle needs system DSN to communicate from it, we have already configured the system DSN above. 



Configure Oracle OS User

vi /home/oracle/.bash_profile

# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/.local/bin:$HOME/bin

export PATH


# Oracle Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR

ORACLE_HOSTNAME=en01; export ORACLE_HOSTNAME
ORACLE_UNQNAME=hadob; export ORACLE_UNQNAME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/12.2.0.1/db_1; export ORACLE_HOME
ORACLE_SID=hadob; export ORACLE_SID
export NLS_LANG=American_America.AL32UTF8
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

##For Presto ODBC Settings
export ODBCINI=/opt/teradata/prestoodbc/Setup/odbc.ini
export ODBCSYSINI=/opt/teradata/prestoodbc/Setup/odbcinst.ini
export TERADATAPRESTOINI=/opt/teradata/prestoodbc/lib/64/teradata.prestoodbc.ini
export LD_PRELOAD=/usr/lib64/libodbcinst.so

##For Hive ODBC Settings
export ODBCINI=/usr/lib/hive/lib/native/hiveodbc/Setup/odbc.ini
export ODBCSYSINI=/usr/lib/hive/lib/native/hiveodbc/Setup/odbcinst.ini
export HORTONWORKSHORTONWORKSHIVEODBCINI=/usr/lib/hive/lib/native/Linux-amd64-64/hortonworks.hiveodbc.ini
export LD_PRELOAD=/usr/lib64/libodbcinst.so



[oracle@en01 ~]$ source .bash_profile


Configure Oracle Network


In Oracle Gateways home configure the listener.ora file, this file will be responsible to provide connection to non-oracle data sources (ie; Hive in our case)




Configure listener.ora file


# listener.ora Network Configuration File: /u01/app/oracle/product/12.2.0.1/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = en01)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )


SID_LIST_LISTENER=
 (SID_LIST =
  (SID_DESC =
   (SID_NAME = PrestoDSN)
   (ORACLE_HOME = /u01/app/oracle/product/12.2.0.1/db_1)
   (PROGRAM = dg4odbc)
   (ARGS = ENVS=LD_LIBRARY_PATH=/opt/teradata/prestoodbc/lib/64/libteradataprestoodbc64.so)
  )
  (SID_DESC =
   (SID_NAME = HiveDSN)
   (ORACLE_HOME = /u01/app/oracle/product/12.2.0.1/db_1)
   (PROGRAM = dg4odbc)
   (ARGS = ENVS=LD_LIBRARY_PATH=/usr/lib/hive/lib/native/Linux-amd64-64/libhortonworkshiveodbc64.so)
  )


 )

SID_NAME should be the same name as you have configured your DSN Name.

Now stop and start the listener to take change effect.

[oracle@en01 ~]$ lsnrctl stop
[oracle@en01 ~]$ lsnrctl start

[oracle@en01 ~]$ lsnrctl status

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 19-DEC-2017 13:47:22

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=en01)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                14-DEC-2017 14:34:02
Uptime                    4 days 23 hr. 13 min. 19 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.2.0.1/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/en01/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=en01)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=en01)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/hadob/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 "PrestoDSN" has 1 instance(s).
  Instance "PrestoDSN", status UNKNOWN, has 1 handler(s) for this service...
Service "hadob" has 1 instance(s).
  Instance "hadob", status READY, has 1 handler(s) for this service...
Service "hadobXDB" has 1 instance(s).
  Instance "hadob", status READY, has 1 handler(s) for this service...

The command completed successfully

Configure tnsnames.ora file


# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.2.0.1/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

HADOB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = en01)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = hadob)
    )
  )

LISTENER_HADOB =
  (ADDRESS = (PROTOCOL = TCP)(HOST = en01)(PORT = 1521))


PRESTOCON =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = en01)(PORT = 1521))
    (CONNECT_DATA =
      (SID = PrestoDSN)
    )
   (HS = OK)
  )

HIVECON =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = en01)(PORT = 1521))
    (CONNECT_DATA =
      (SID = HiveDSN)
    )
   (HS = OK)
  )



Make sure, you specify "HS=OK"  in tns entry then test your tns entry.

[oracle@en01 ~]$ tnsping hivecon

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 19-DEC-2017 13:48:10

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

Used parameter files:
/u01/app/oracle/product/12.2.0.1/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = en01)(PORT = 1521)) (CONNECT_DATA = (SID = HiveDSN)) (HS = OK))
OK (0 msec)


Configure Oracle HS/ODBC Gateway

Configure odbc gateway parameter file

This file contains information about the source database name which you want to connect and the location of driver which is responsible for communicating wit
h Oracle heterogeneous service and establishing connection to a non-oracle data source. File is created as init<SYSTEMDSN>.ora in the $ORACLE_HOME/hs/admin directory



vi /u01/app/oracle/product/12.2.0.1/db_1/hs/admin/initHiveDSN.ora


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

#
# HS init parameters
#
HS_FDS_CONNECT_INFO = "HiveDSN"
HS_FDS_TRACE_LEVEL = DEBUG
HS_FDS_SHAREABLE_NAME=/usr/lib/hive/lib/native/Linux-amd64-64/libhortonworkshiveodbc64.so
HS_FDS_SQLLEN_INTERPRETATION=64
HS_TRANSACTION_MODEL=READ_ONLY_AUTOCOMMIT
HS_LANGUAGE=AMERICAN_AMERICA.WE8MSWIN1252
HS_FDS_SUPPORT_STATISTICS = FALSE
#HS_KEEP_REMOTE_COLUMN_SIZE=ALL
#HS_NLS_NCHAR=UCS2

#
# ODBC specific environment variables
#
set ODBCINI=/usr/lib/hive/lib/native/hiveodbc/Setup/odbc.ini
set ODBCSYSINI=/usr/lib/hive/lib/native/hiveodbc/Setup/odbcinst.ini
set HORTONWORKSHORTONWORKSHIVEODBCINI=/usr/lib/hive/lib/native/Linux-amd64-64/hortonworks.hiveodbc.ini
set LD_LIBRARY_PATH=/usr/lib64/
#
# Environment variables required for the non-Oracle system
#

#set <envvar>=<value>



Configure Oracle Database

Create DBLink


Create a database link that points to the tnsnames entry created earlier:

 SQL> create public database link hivelnk connect to hive identified by hive using 'HIVECON';

Database link created.

Query via DBLink


SQL> select sysdate from scott.dual@hivelnk;

SYSDATE
------------------

19-DEC-17


Congrats! You have successfully communicated with Hivefrom Oracle database.




Updated on 20-Dec-2017
This update due to getting error while running aggregate function from Oracle

There are two types of queries run on Hive , query which runs without mapreduce and query which requires mapreduce. I ran the below query from SQPPlus and found the error.

SQL> select count(*) from dept@hivelnk;
select count(*) from dept@hivelnk
*
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Hortonworks][Hardy] (35) Error from server: error code: '1' error message:
'Error while processing statement: FAILED: Execution Error, return code 1 from
org.apache.hadoop.hive.ql.exec.tez.TezTask'. {HY000,NativeErr = 35}
ORA-02063: preceding 2 lines from HIVELNK


In order to investigate this error I checked the same query with iSQL and found the same issue as  below.

[oracle@te1-hdp-rp-en01 ~]$  isql -v HiveDSN
SQL> select count(*) from scott.dept
[S1000][Hortonworks][Hardy] (35) Error from server: error code: '1' error message: 'Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.tez.TezTask'.
[ISQL]ERROR: Could not SQLExecute

I enabled logging on the ODBC level and on Oracle HS level too but could not get any useful information.

With an idea I just changed the execution engine in Ambari from TEZ to MAPREDUCE and tried again from iSQL and observed that now error is changed and got that issue is occurring due to permission on HDFS location.

[oracle@te1-hdp-rp-en01 ~]$  isql -v HiveDSN
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select count(*) from scott.dept
[S1000][Hortonworks][Hardy] (35) Error from server: error code: '1' error message: 'Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.mr.MapRedTask. Permission denied: user=anonymous, access=WRITE, inode="/user/anonymous/.staging":hdfs:hdfs:drwxr-xr-x
        at org.apache.hadoop.hdfs.s'.
[ISQL]ERROR: Could not SQLExecute


After observing above error I performed (self expalnatory) the below steps on the HDFS side.


[hdfs@te1-hdp-rp-dn04 ~]$ hdfs dfs -mkdir /user/anonymous

[hdfs@te1-hdp-rp-dn04 ~]$ hdfs dfs  -ls /user
Found 11 items
drwxrwxrwx   - admin     hdfs          0 2017-08-01 11:47 /user/admin
drwxrwx---   - ambari-qa hdfs          0 2017-08-20 12:08 /user/ambari-qa
drwxr-xr-x   - hdfs      hdfs          0 2017-12-20 14:54 /user/anonymous

[hdfs@te1-hdp-rp-dn04 ~]$ hdfs dfs  -chmod -R 777  /user/anonymous

[hdfs@te1-hdp-rp-dn04 ~]$ hdfs dfs  -ls /user
Found 11 items
drwxrwxrwx   - admin     hdfs          0 2017-08-01 11:47 /user/admin
drwxrwx---   - ambari-qa hdfs          0 2017-08-20 12:08 /user/ambari-qa
drwxrwxrwx   - hdfs      hdfs          0 2017-12-20 14:54 /user/anonymous

Now tested again using iSQL and this time it went with success. 

[oracle@te1-hdp-rp-en01 ~]$  isql -v HiveDSN
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select count(*) from scott.dept
+---------------------+
| EXPR_1              |
+---------------------+
| 4                   |
+---------------------+
SQLRowCount returns -1
1 rows fetched
SQL>


As above with success, it means now in Oracle there should not be any problem, so tested in Oracle but got another error which quiet self explanatory.

SQL> select count(1) from scott.dept@hivelnk
  2  /
select count(1) from scott.dept@hivelnk
*
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Hortonworks][Hardy] (35) Error from server: error code: '1' error message:
'Error while processing statement: FAILED: Execution Error, return code 1 from
org.apache.hadoop.hive.ql.exec.mr.MapRedTask. Permission denied: user=HIVE,
access=WRITE, inode="/user/HIVE/.staging":hdfs:hdfs:drwxr-xr-x
at org.apache.hadoop.hdfs.server.name'. {HY000,NativeErr = 35}

ORA-02063: preceding 3 lines from HIVELNK


Observe the error it is because of the user configured in dblnik definition so I made it correct.


SQL> drop public database link hivelnk;


Database link dropped.

SQL> Create public database link hivelnk connect to "anonymous" identified by anonymous using 'HIVECON';


Database link created.



SQL> select count(1) from scott.dept@hivelnk;

  COUNT(1)
----------

         4

Now this time got success :)

I again changed the execution engine from MAPREDUCE to TEZ and ran the above query and this time it went with success also. So changing from TEZ to MAPREDUCE just helped us to diagnose the real issue behind failure.

After this I did some test to run the query from SQLPlus  with Presto (already DSN configured in other post), Hive MapReduce and Hive with Tez and below are findings.

-- With Map Reduce

SQL> set timing on;
SQL> select count(1) from scott.dept@hivelnk;

  COUNT(1)
----------
         4


Elapsed: 00:00:22.19


-- With Presto

SQL> select count(1) from scott.dept@prestolnk;

  COUNT(1)
----------
         4


Elapsed: 00:00:00.87

--Witn TEZ

SQL> select count(1) from scott.dept@hivelnk;

  COUNT(1)
----------
         4


Elapsed: 00:00:05.74


Test Join - Hadoop and Oracle


select d."dname", e.name from dept@hivelnk d,scott.employee e where d."deptno"=e.deptno



No comments: