Running oracle queries through azure databricks

0
votes

Hi I created a notebook in azure databricks to extract a table from an oracle database. I get the error DPI-1047 cannot locate a 64 bit oracle client library. I guess I have to have an oracle client library running on my cluster. I tried to research the issue and found many posts where people said I needed an init script for the oracle client. I couldnt find how to make that init script however. I also found on this website

https://medium.com/@srijansahay/connecting-sql-server-oracle-mysql-and-postgresql-from-azure-services-using-python-789e93d879b4

the following code:

%sh
mkdir -p /opt/oracle
cd /opt/oracle
wget https://download.oracle.com/otn_software/linux/instantclient/193000/instantclient-basic-linux.x64-19.3.0.0.0dbru.zip
unzip instantclient-basic-linux.x64–19.3.0.0.0dbru.zip
sudo sh -c “echo /opt/oracle/instantclient_19_3 > /etc/ld.so.conf.d/oracle-instantclient.conf”
sudo ldconfig
export LD_LIBRARY_PATH=/opt/oracle/instantclient_19_3:$LD_LIBRARY_PATH

This code is however for Linux. How do I modify it for windows? Here is what I have so far:

%sh
mkdir -p /opt/oracle
cd /opt/oracle
wget https://download.oracle.com/otn_software/nt/instantclient/19800/instantclient-basic-windows.x64-19.8.0.0.0dbru.zip
unzip instantclient-basic-windows.x64-19.8.0.0.0dbru.zip
sudo sh -c “echo /opt/oracle/instantclient_19_8 > /etc/ld.so.conf.d/oracle-instantclient.conf”
sudo ldconfig
export LD_LIBRARY_PATH=/opt/oracle/instantclient_19_8:$LD_LIBRARY_PATH

I think everything past the unzip is incorrect. Thanks for the help in advance I'm very new to databricks.

1
oracleazure-databricks

1 Answers

1
votes

Since there are no library path and path Separation in Windows we need to use PATH for both instead. At the end of the script you place the python stuff.

Rem ic.cmd
C:
Set ORABAS = "\opt\oracle"
cd %ORABAS%
wget https://download.oracle.com/otn_software/nt/instantclient/19600/instantclient-basic-windows.x64-19.6.0.0.0dbru.zip
unzip instantclient-basic-windows.x64-19.6.0.0.0dbru.zip
set ORACLE_HOME=%ORABAS%\instantclient_19_3
set TNS_ADMIN=%ORACLE_HOME%
set PATH=%ORACLE_HOME%;%PATH%