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.
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
-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
# 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
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
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
# 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
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.
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
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 ~]$
# 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)
)
[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 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
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
[oracle@en01 ~]$ sqlplus / as sysdba
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
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:
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.........
Post a Comment