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.

Monday, December 18, 2017

Query Teradata Presto from Oracle using ODBC Heterogeneous Gateway [RHEL 7]


Presto is a distributed SQL query engine designed to query large data sets distributed over one or more heterogeneous data sources. In Italian, “Presto” means fast. In the tech world, it means an open-source distributed SQL query engine for Apache™ Hadoop® that runs interactive analytic queries against data sources of all sizes. Through a single query, data is accessed where it resides. Typically, this means data in a Hadoop Distributed File System (HDFS). However, unlike other SQL on Hadoop engines, Presto can query data sources such as Apache Cassandra™, relational databases or even proprietary data stores. 


Starting with 11g Oracle now provides a Database Gateway for ODBC for the 64bit Unix operating systems to connect to any 3rd party database using a suitable 3rd party ODBC driver. In this post we are going to configure the Presto driver to be used from Oracle (12c) to query our hadoop data through Oracle's db link.


ODBC is one the most established and widely supported APIs for connecting to and

working with databases. At the heart of the technology is the ODBC driver, which

connects an application to the database.

Setup/Configuration

Download Presto Client Package



Download Teradata Presto driver (presto_client_package) from below location and extract on your desired location. The Teradata Presto ODBC Driver lets organizations connect their BI tools to Presto. Presto provides an ANSI SQL query layer and also exposes the metadata information through an ANSI SQL standard metadata database called INFORMATION_SCHEMA. The Teradata Presto ODBC Driver leverages  INFORMATION_SCHEMA to expose Presto’s metadata to BI tools as needed.
http://www.teradata.com/products-and-services/Presto/Presto-Download

[root@en01 odbc]# pwd
/usr/hadoopsw/presto_client_pkg.0.167-t.0.2/odbc

[root@en01 odbc]# ll


total 75748-rw-r--r-- 1 root root 373152 Dec 12 2016 TeradataODBCDriverPrestoInstallGuide_1_1_8.pdf
-rw-r--r-- 1 root root 23104978 Dec 13 2016 TeradataPrestoODBC-1.1.8.1016-1.dmg
-rw-r--r-- 1 root root 10809344 Dec 13 2016 TeradataPrestoODBC-32bit-1.1.8.1016-1.msi
-rw-r--r-- 1 root root 15836747 Dec 13 2016 TeradataPrestoODBC-32bit-1.1.8.1016-1.rpm
-rw-r--r-- 1 root root 11354112 Dec 13 2016 TeradataPrestoODBC-64bit-1.1.8.1016-1.msi
-rw-r--r-- 1 root root 16070878 Dec 13 2016
TeradataPrestoODBC-64bit-1.1.8.1016-1.rpm-rw-r--r-- 1 root root 4758 Dec 13 2016 Teradata_Presto_ODBC_Driver.tdc

Installing the Driver

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

/opt/teradata/prestoodbc contains the release notes and the Teradata ODBC Driver for Presto Installation and Configuration Guide.

/opt/teradata/prestoodbc/ErrorMessages contains error message files required by the driver.

/opt/teradata/prestoodbc/Setup contains sample configuration files named odbc.ini and odbcinst.ini.

/opt/teradata/prestoodbc/lib/32 contains the 32-bit shared libraries and the teradata.prestoodbc.ini configuration file.

/opt/teradata/prestoodbc/lib/64 contains the 64-bit shared libraries and the teradata.prestoodbc.ini configuration file.


[root@en01 odbc]# yum --nogpgcheck localinstall TeradataPrestoODBC-64bit-1.1.8.1016-1.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
Repodata is over 2 weeks old. Install yum-cron? Or run: yum makecache fast
Examining TeradataPrestoODBC-64bit-1.1.8.1016-1.rpm: TeradataPrestoODBC-64bit-1.1.8-1.x86_64
Marking TeradataPrestoODBC-64bit-1.1.8.1016-1.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package TeradataPrestoODBC-64bit.x86_64 0:1.1.8-1 will be installed
--> Finished Dependency Resolution
ambari-2.5.0.3                                                                                                            | 2.9 kB  00:00:00
epel/x86_64/metalink                                                                                                      | 7.3 kB  00:00:00
epel/x86_64                                                                                                               | 4.7 kB  00:00:00
epel/x86_64/group_gz                                                                                                      | 266 kB  00:00:01
epel/x86_64/updateinfo                                                                                                    | 860 kB  00:00:02
epel/x86_64/primary_db                                                                                                    | 6.1 MB  00:00:17
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-connectors-community/x86_64/primary_db                                                                              |  16 kB  00:00:00
mysql-tools-community/x86_64                                                                                              | 2.5 kB  00:00:00
mysql-tools-community/x86_64/primary_db                                                                                   |  37 kB  00:00:00
mysql57-community/x86_64                                                                                                  | 2.5 kB  00:00:00
mysql57-community/x86_64/primary_db                                                                                       | 124 kB  00:00:00

Dependencies Resolved

=================================================================================================================================================
 Package                                Arch                 Version                  Repository                                            Size
=================================================================================================================================================
Installing:
 TeradataPrestoODBC-64bit               x86_64               1.1.8-1                  /TeradataPrestoODBC-64bit-1.1.8.1016-1                46 M

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

Total size: 46 M
Installed size: 46 M
Is this ok [y/d/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : TeradataPrestoODBC-64bit-1.1.8-1.x86_64                                                                                       1/1
google-chrome                                                                                                                                3/3
  Verifying  : TeradataPrestoODBC-64bit-1.1.8-1.x86_64                                                                                       1/1

Installed:
  TeradataPrestoODBC-64bit.x86_64 0:1.1.8-1

Complete!


The driver is installed on the below location by default.

/opt/teradata/prestoodbc

Verifying the Version Number

[root@en01 odbc]# rpm -qa | grep TeradataPrestoODBC
TeradataPrestoODBC-64bit-1.1.8-1.x86_64

[hdpclient@en01 ~]$ odbcinst --version
unixODBC 2.3.1


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 Teradata Presto ODBC Driver is configured using the teradata.prestoodbc.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:

• /opt/teradata/prestoodbc/lib/32 for the 32-bit driver on Linux.
• /opt/teradata/prestoodbc/lib/64 for the 64-bit driver on Linux.


Configuring the Environment

Use three environment variables, ODBCINI, ODBCSYSINI, and TERADATAPRESTOINI, to specify different locations for the odbc.ini, odbcinst.ini, and teradata.prestoodbc.ini configuration files.

[root@en01 odbc]# export ODBCINI=/opt/teradata/prestoodbc/Setup/odbc.ini
[root@en01 odbc]# export ODBCSYSINI=/opt/teradata/prestoodbc/Setup/odbcinst.ini
[root@en01 odbc]# export TERADATAPRESTOINI=/opt/teradata/prestoodbc/lib/64/teradata.prestoodbc.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 /opt/teradata/prestoodbc/Setup/odbc.ini

[ODBC]
Trace=no

[ODBC Data Sources]
Teradata Presto DSN 32=Teradata Presto ODBC Driver 32-bit
Teradata Presto DSN 64=Teradata Presto ODBC Driver 64-bit
# add a new entry for Presto Data Source Name (DSN),
PrestoDSN=Teradata Presto ODBC Driver 64-bit

# Add a new section to the file, with a section name that matches the DSN you specified #above, and then add configuration options to the section.

[PrestoDSN]
Description=My Presto DSN
# Driver: The location where the ODBC driver is installed to.
Driver=/opt/teradata/prestoodbc/lib/64/libteradataprestoodbc64.so
# Host name of he Presto server.
Host=en01
# The listen port of the Presto server.
Port=6060
# The presto catalog for the connection. (Optional)
Catalog=hive
# The time zone use for the connection. (Optional. Default: Current time zone of the OS)
TimeZoneID=Asia/Riyadh
#   Kerberos Authentication
AuthenticationType=No Authentication
schema=scott
Characterset=UTF16
# The user ID to use for the connection. (Optional)
UID=presto 


[Teradata Presto DSN 32]
# This key is not necessary and is only to give a description of the data source.
Description=Teradata Presto ODBC Driver (32-bit) DSN

# Driver: The location where the ODBC driver is installed to.
Driver=/opt/teradata/prestoodbc/lib/32/libteradataprestoodbc32.so

# Required: These values can also be specified in the connection string.
# Host name of the Presto server.
Host=
# The listen port of the Presto server.
Port=8080

# Optional: These values can also be specified in the connection string.
# The user ID to use for the connection. (Optional)
UID=

# The authentication type to use for the connection. (Optional. Default: No Authentication)
#   No Authentication
#   Kerberos Authentication
AuthenticationType=No Authentication

# The presto catalog for the connection. (Optional)
Catalog=

# The time zone use for the connection. (Optional. Default: Current time zone of the OS)
TimeZoneID=

# Set to 1 to enable SSL. Set to 0 to disable. (Optional. Default: 0)
#SSL=0

# Set to 1 to allow the common name in the server's SSL certificate to not match the hostname 
# of the Presto Server. Set to 0 to require common name in the server's SSL 
# certificate to match the hostname of the Presto Server during SSL verification.
#
# Only used when SSL is enabled.
#AllowHostNameCNMismatch=0

# Set to 1 to allow the server's SSL certificate to be self-signed. Set to 0 to require the
# server's SSL certificate to be signed by a certificate authority.
#
# Only used when SSL is enabled.
#AllowSelfSignedServerCert=0

# Used to specify the full path of the PEM formatted file containing trusted SSL CA certificates.
# If an empty string is passed in for the configuration the driver expectes the trusted SSL CA
# certificates can be found in the file named cacerts.pem located in the same directory as the
# driver's shared library.
#
# Only used when SSL is enabled.
#TrustedCerts=


[Teradata Presto DSN 64]
# This key is not necessary and is only to give a description of the data source.
Description=Teradata Presto ODBC Driver (64-bit) DSN

# Driver: The location where the ODBC driver is installed to.
Driver=/opt/teradata/prestoodbc/lib/64/libteradataprestoodbc64.so

# Required: These values can also be specified in the connection string.
# Host name of the Presto server.
Host=
# The listen port of the Presto server.
#Port=8080
Port=6060


# Optional: These values can also be specified in the connection string.
# The user ID to use for the connection. (Optional)
UID=

# The authentication type to use for the connection. (Optional. Default: No Authentication)
#   No Authentication
#   Kerberos Authentication
AuthenticationType=No Authentication

# The presto catalog for the connection. (Optional)
Catalog=

# The time zone use for the connection. (Optional. Default: Current time zone of the OS)
TimeZoneID=

# Set to 1 to enable SSL. Set to 0 to disable. (Optional. Default: 0)
#SSL=0

# Set to 1 to allow the common name in the server's SSL certificate to not match the hostname 
# of the Presto Server. Set to 0 to require common name in the server's SSL 
# certificate to match the hostname of the Presto Server during SSL verification.
#
# Only used when SSL is enabled.
#AllowHostNameCNMismatch=0

# Set to 1 to allow the server's SSL certificate to be self-signed. Set to 0 to require the
# server's SSL certificate to be signed by a certificate authority.
#
# Only used when SSL is enabled.
#AllowSelfSignedServerCert=0

# Used to specify the full path of the PEM formatted file containing trusted SSL CA certificates.
# If an empty string is passed in for the configuration the driver expectes the trusted SSL CA
# certificates can be found in the file named cacerts.pem located in the same directory as the
# driver's shared library.
#
# Only used when SSL is enabled.
#TrustedCerts=

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 /opt/teradata/prestoodbc/Setup/odbcinst.ini

[ODBC Drivers]
Teradata Presto ODBC Driver 32-bit=Installed
Teradata Presto ODBC Driver 64-bit=Installed
[Teradata Presto ODBC Driver 32-bit]
Description=Teradata Presto ODBC Driver(32-bit)
Driver=/opt/teradata/prestoodbc/lib/32/libteradataprestoodbc32.so

[Teradata Presto ODBC Driver 64-bit]
Description=Teradata Presto ODBC Driver(64-bit)
Driver=/opt/teradata/prestoodbc/lib/64/libteradataprestoodbc64.so


Configuring Driver Settings in teradata.prestoodbc.ini

The teradata.prestoodbc.ini file contains configuration settings for the Teradata Presto 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 teradata.prestoodbc.ini file to use the driver and connect to your data source. However, to help troubleshoot issues, you can configure the teradata.prestoodbc.ini file to enable logging in the driver.

vi /opt/teradata/prestoodbc/lib/64/teradata.prestoodbc.ini

[Driver]
ODBCInstLib=/usr/lib64/libodbcinst.so
ErrorMessagesPath=/opt/teradata/prestoodbc/ErrorMessages
LogLevel=0
LogPath=[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 ~]# export ODBCINI=/opt/teradata/prestoodbc/Setup/odbc.ini
[root@en01 ~]# export ODBCSYSINI=/opt/teradata/prestoodbc/Setup/odbcinst.ini
[root@en01 ~]# export TERADATAPRESTOINI=/opt/teradata/prestoodbc/lib/64/teradata.prestoodbc.ini


[root@en01 ~]# iodbctest

iODBC Demonstration program
This program shows an interactive SQL processor
Driver Manager: 03.52.0709.0909

Enter ODBC connect string (? shows list):

DSN                              | Driver
------------------------------------------------------------------------------
PrestoDSN                        | Teradata Presto ODBC Driver 64-bit
Teradata Presto DSN 32           | Teradata Presto ODBC Driver 32-bit
Teradata Presto DSN 64           | Teradata Presto ODBC Driver 64-bit

Enter ODBC connect string (? shows list): DSN=PrestoDSN
Driver: 1.1.8.1016 (libteradataprestoodbc64.so)

SQL>




[root@en01 ~]# isql PrestoDSN
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

Test below query to count the employees in scott schema of hive catalog.

SQL>  select count(*) from hive.scott.emp
+---------------------+
| _col0               |
+---------------------+
| 15                  |
+---------------------+
SQLRowCount returns -1

1 rows fetched


SQL> show tables from hive.scott


SQL> quit;

If the connection is successful, then the SQL> prompt appears.

You could test like below also without isql prompt.

echo "SELECT * FROM scott.DEPT" | isql  PrestoDSN -v


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




[oracle@en01 ~]$ source .bash_profile




You have to set LD_PRELOAD variable else you may get below error when querying from Oracle to non-Oracle 

SQL> select * from flume.timezone@prestolnk;
select * from flume.timezone@prestolnk
                             *
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Teradata][ODBC] (11560) Unable to locate SQLGetPrivateProfileString function.
{HY000,NativeErr = 11560}
ORA-02063: preceding 2 lines from PRESTOLNK

The SQLGetPrivateProfileString function is contained and exported from libodbcinst.so shared library so you have to add it in your configurations.



Verify that libodbcinst.so is loaded as follows:
    
  [oracle@en01 ~]$ ldd /opt/teradata/prestoodbc/lib/64/libteradataprestoodbc64.so
        linux-vdso.so.1 =>  (0x00007ffed82fa000)
        /usr/lib64/libodbcinst.so (0x00007f0754604000)
        libdl.so.2 => /lib64/libdl.so.2 (0x00007f07543e2000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f07541c6000)
        librt.so.1 => /lib64/librt.so.1 (0x00007f0753fbe000)
        libgssapi_krb5.so.2 => /lib64/libgssapi_krb5.so.2 (0x00007f0753d71000)
        libkrb5.so.3 => /lib64/libkrb5.so.3 (0x00007f0753a8c000)
        libstdc++.so.6 => /lib64/libstdc++.so.6 (0x00007f0753783000)
        libm.so.6 => /lib64/libm.so.6 (0x00007f0753480000)
        libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007f075326a000)
        libc.so.6 => /lib64/libc.so.6 (0x00007f0752ea9000)
        libltdl.so.7 => /lib64/libltdl.so.7 (0x00007f0752c9e000)
        /lib64/ld-linux-x86-64.so.2 (0x00007f07570cd000)
        libk5crypto.so.3 => /lib64/libk5crypto.so.3 (0x00007f0752a6c000)
        libcom_err.so.2 => /lib64/libcom_err.so.2 (0x00007f0752868000)
        libkrb5support.so.0 => /lib64/libkrb5support.so.0 (0x00007f0752658000)
        libkeyutils.so.1 => /lib64/libkeyutils.so.1 (0x00007f0752454000)
        libresolv.so.2 => /lib64/libresolv.so.2 (0x00007f075223a000)
        libselinux.so.1 => /lib64/libselinux.so.1 (0x00007f0752014000)
        libpcre.so.1 => /lib64/libpcre.so.1 (0x00007f0751db3000)
        liblzma.so.5 => /lib64/liblzma.so.5 (0x00007f0751b8d000)



The odbcinst command is a simple way to verify the location of the ODBC Driver Manager configuration files (mentioned in .bash_profile). This will help ensure the correct files (odbcinst.ini and odbc.ini) are being configured.

[oracle@en01 ~]$ odbcinst -j

unixODBC 2.3.1
DRIVERS............: /opt/teradata/prestoodbc/Setup/odbcinst.ini/odbcinst.ini
SYSTEM DATA SOURCES: /opt/teradata/prestoodbc/Setup/odbcinst.ini/odbc.ini
FILE DATA SOURCES..: /opt/teradata/prestoodbc/Setup/odbcinst.ini/ODBCDataSources
USER DATA SOURCES..: /opt/teradata/prestoodbc/Setup/odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8




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; Presto 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)
  )
)

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 14-DEC-2017 13:21:16

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 13:10:22
Uptime                    0 days 0 hr. 10 min. 53 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 "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
[oracle@en01 ~]$



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.

HADOOP =
  (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)
  )

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

[oracle@en01 ~]$ tnsping prestocon

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 14-DEC-2017 13:27:41

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 = PrestoDSN)) (HS = OK))
OK (0 msec)
[oracle@en01 ~]$


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/initPrestoDSN.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 = "PrestoDSN"
HS_FDS_TRACE_LEVEL = DEBUG
HS_FDS_SHAREABLE_NAME=/opt/teradata/prestoodbc/lib/64/libteradataprestoodbc64.so
HS_FDS_SQLLEN_INTERPRETATION=64
HS_TRANSACTION_MODEL=READ_ONLY_AUTOCOMMIT
HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1
HS_FDS_SUPPORT_STATISTICS = FALSE
HS_KEEP_REMOTE_COLUMN_SIZE=ALL
#
# ODBC specific environment variables
#
set ODBCINI=/opt/teradata/prestoodbc/Setup/odbc.ini
#set ODBCSYSINI=/opt/teradata/prestoodbc/Setup/odbcinst.ini
#set TERADATAPRESTOINI=/opt/teradata/prestoodbc/lib/64/teradata.prestoodbc.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:

[oracle@en01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Dec 14 13:30:20 2017

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production


SQL> create database link prestolnk connect to presto identified by presto using 'PRESTOCON';

Database link created.


Query via DBLink


SQL> select sysdate from scott.dual@prestolnk;

SYSDATE
------------------
18-DEC-17

Using DBMS_HS_PASSTHROUGH


Below just an example to show how can you use the pass-through SQL of Heterogeneous Services. The DBMS_HS_PASSTHROUGH PL/SQL package allows you to send a statement directly to a non-Oracle system without being interpreted by the Oracle server. This can be useful if the non-Oracle system allows operations in statements for which there is no equivalent in Oracle.


declare 
v_cursor binary_integer; 
v_ret binary_integer; 
v_id integer; 
v_empno varchar2(30); 
v_ename varchar2(30); 
v_job varchar2(30);

begin 
     v_cursor:=dbms_hs_passthrough.open_cursor@prestolnk; 
     dbms_hs_passthrough.parse@prestolnk(v_cursor,'select * from scott.emp'); 
     
     begin 
        v_ret:=0; 
        while (true) 
        loop 
            v_ret:=dbms_hs_passthrough.fetch_row@prestolnk(v_cursor,false); 
            dbms_hs_passthrough.get_value@prestolnk(v_cursor,1,v_empno); 
            dbms_hs_passthrough.get_value@prestolnk (v_cursor,2,v_ename); 
            dbms_hs_passthrough.get_value@prestolnk (v_cursor,3,v_job);
dbms_output.put_line('empno='||v_empno||' ename='||v_ename||' job='||v_job);
end loop; 
exception 
when no_data_found then 
begin 
dbms_output.put_line('no more rows found!'); 
dbms_hs_passthrough.close_cursor@prestolnk(v_cursor); 
end;  
end;

end; 
/



Congrats! You have successfully communicated with Presto from Oracle database.

1 comment:

Srinu Vasu said...

Hi, I really loved reading this article. By this article i have learnt many things about OBIEE QAs, please keep me updating if there is any update.
Teradata Online Training
Teradata Training
Teradata Online Course keep updating.........