We have a Chinese version(SAP HANA Smart Data Access（二）——SDA数据源驱动的安装与配置) of this blog.
Introduction
In the blog “SAP HANA Smart Data Access (1):A brief introduction to SDA”, we introduced the architecture of SDA and talked about how to add remote data source for SDA in SAP HANA Studio. Before adding remote data source for SDA, it is necessary to finish the installation and configuration the ODBC manager and ODBC driver for SDA date source in SAP HANA server side. For different SDA data sources, the process of installing and configuring ODBC driver is similar. In this article, we take the Oracle data source as an example to talk about how to install and configuration ODBC driver.
Installation of unixODBC driver manager
Since SAP HANA SDA communicates with remote data source using ODBC protocol, the ODBC driver manager must be installed in SAP HANA server side. Usually, the unixODBC is chosen as the driver manager for SAP HANA SDA. Software package of unixODBC can be downloaded from website: http://www.unixodbc.org/ . Please note that the version of unixODBC needs to be 2.3.0 for SQL Server data source, and 2.3.1 or newer version is required for other data sources. The process of installing unixODBC is below:
- Download corresponding version of unixODBC package, the name of package is unixODBC-x.x.x.tar.gz, x here stands for version number.
- Login in the SAP HANA server as the root user, decompress the unixODBC package to specified folder.
- Enter the folder specified in step 2, then execute the commands below in order:
./configure
make
make install
4. unixODBC should be installed by now, you can execute “isql --version” to check whether the unixODBC is installed successfully.
Installation of ODBC driver for data source
So far, the data sources supported by SAP HANA SDA includes: SAP HANA, SAP Sybase ASE, Teradata database, Oracle, MS SQL Server and Hadoop. The ODBC driver for the database productions, such as Sybase ASE and Oracle, can be downloaded from the official website of the database. For example, you can download ODBC driver for Oracle form website: http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html . As for Hadoop data source, SAP HANA SDA can communicate with it through Hive. More details about connection between SAP HANA SDA and Hadoop in subsequent blogs in this series. And the SAP official recommendation for Hive driver is the HiveODBC driver provided by Simba Technology. Simba HiveODBC can be achieved from the Simba website: http://www.simba.com/connectors/apache-hadoop-hive-odbc.
After downloading the ODBC driver, the driver can be installed according to relevant installation guide. Take Oracle as an example here, two zip packages should be downloaded: instantclient-basic-linux.x64-xx.x.x.x.x.zip和instantclient-odbc-linux.x64-xx.x.x.x.x.zip, x stands for version number. Then decompress the two packages to a same folder with unzip command. The default unzipped directory is instantclient_xx_x. if everything goes well, you can find the ODBC driver file for Oracle in the unzipped directory, the file name is libsqora.so.xx.x . By now, the ODBC driver for Oracle database is installed.
Configuration of ODBC data source
ODBC configuration file needs to be created after installing ODBC data source, and some environment variable should be set accordingly. Let’s also take Oracle as an example:
- Login SAP HANA Server as sidadm, sid is the ID of SAP HANA instance.
- Enter the home directory of sidadm, create the ODBC configuration file named “.odbc.ini”.
- Edit .odbc.ini with vim, the content should be like below:
[ORCL_DSN]
Driver=/path/to/driver/ libsqora.so.xx.x
ServerName=ORCL
ORCL_DSN is the name of ODBC data source, which is used by ODBC Manager to find the information for connection. Driver is a keywords used to specify the path of ODBC Driver of data source. For oracle, ServerName is the name of Oracle database which is defined in the file called “tnsnames.ora” located in home directory of sidadm. For other kinds of database, “ServerName” is replaced by other keywords, such as “ServerNode” for SAP HANA, “Server” and “Database” for MS SQL Server.
4. For Oracle data source, create a file called “tnsnames.ora” in the home directory of sidadm, then edit it with vim, the content should be like below:
ORCL=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = <host_ip>)(PORT = 1521))
(CONNECT_DATA =
(SERVICE = DEDICATED)
(SERVICE_NAME =orcl)
)
)
5. Set some environment variables in the shell script file “hdbenv.sh” of SAP HANA, this shell script file is located in the folder specified by environment variable “DIR_INSTANCE”. Add the commands below to this shell script file:
export LD_LIBRARY_PATH = $LD_LIBRARY_PATH:your_oracle_driver_dir/instantclient_12_1
export LD_LIBRARY_PATH = $LD_LIBRARY_PATH:/usr/local/lib
export ODBCINI = $HOME/.odbc.ini
export TNS_ADMIN=~/ (For Oracle only)
Here, please pay more attention to “LD_LIBRARY_PATH” variable, all the libraries depended by ODBC driver are searched orderly in the folder list specified in the “LD_LIBRARY_PATH” variable. If any folder which contains the depended library is not included in the folder list, ODBC Manager will say can’t find the library file.
6. Restart SAP HANA, login SAP HANA Server as sidadm, and execute the command “isql –v <DSN> ” to test the connection to data source. If connection succeeded, the configuration is finished. If connection failed, please analyze and process the error according to error message. Some tips for error processing would be introduced in next section.
Trouble shooting
1. Error message:
[08004][unixODBC][Oracle][ODBC][Ora]ORA-12154: TNS:could not resolve the connect identifier specified
Analysis:
This error is raised by ODBC Manager, it is easily misunderstood as an oracle error. However, this reason of this error is that the environment variable TNS_ADMIN is not set or set incorrectly. The TNS_NAME tells where is the file tnsnames.ora. So, if TNS_ADMIN is not set correctly, the ServerName specified in .odbc.ini can’t be resolved. Then the error is raised.
Solution:
Set the environment variable TNS_ADMIN to the home directory of sidadm in the hdbenv.sh.
2. Error message:
[01000][unixODBC][Driver Manager]Can't open lib '/path/to/driver/libsqora.so.12.1' : file not found [ISQL]ERROR: Could not SQLConnect
Analysis:
The error message says the file libsqora.so.12.1 can’t be found, but this file do exist. Let’s use command ldd to check the dependency of this file, we get:
We can see that the file libclntsh.so.12.1 is depended by libsqora.so.12.1, and it can’t be found. Although libclntsh.so.12.1 is in the same folder with libsqora.so.12.1, but the folder is not in the folder list specified by LD_LIBRARY_PATH, so it will not be searched.
Solution:
Add the folder which contains libsqora.so.12.1 to LD_LIBRARY_PATH in the hdbenv.sh.
Create remote data source
After all the configuration finished, create a remote data source following the procedure introduced in “SAP HANA Smart Data Access(1)——A brief introduction to SDA”. The tables in remote data source can be viewed through SAP HANA Studio after remote data source created, as shown below:
Conclusion
In this blog, we take oracle as an example to illustrate how to install and configure the ODBC manager and ODBC driver of remote data source, and simply discuss some error which may happen during the procedure of installation and configuration. The installation and configuration of other data sources supported by SAP HANA SDA is similar, some little difference would be introduced in subsequent blogs of this series.
Reference
- “SAP HANA Smart Data Access(1)——A brief introduction to SDA”
- Section 6.1.1 of SAP HANA Administrator Guide：
http://help.sap.com/hana/SAP_HANA_Administration_Guide_en.pdf
- SAP Managed Tags:
4 Comments
