Configure Oracle in Redash - Help!


#1

Hi all,
i’m new here and very excited to begin exploring Redash. My main datasource is an Oracle database 12c. I’m not very experienced with linux so i had choosed Linux Mint 64, ubuntu based with i’m more familiar, and installed Redash, made the update to 1.0.3, and Oracle instant client.
Acessing with sqlplus everything ok, no problems.
Installed the cx_Oracle python package and from that point i’m trying everything, but oracle never become available as datatype source.
Also tryed on .env to add:
export REDASH_ADDITIONAL_QUERY_RUNNERS=redash.query_runner.oracle
and restart the services, with no success either…
Am I missing some documentation? I’m not lazy to read manuals, but i can’t find any more information.
Hopping anyone can help me…
Regrets,
Fayol


Oracle query is waiting and isn't providing result
#2

Assuming you installed it using our script, run sudo -u redash bin/run ./manage.py shell from /opt/redash/current. In the shell that will open, try running import cx_Oracle.

I assume this will end up with an error, and it should help you understand why Oracle doesn’t show up in the menu.

If this doesn’t end up with an error, then make sure you restarted the web server after changing the .env file.


#3

Hi fayol,

Make sure you have the Instant client basic AND the SDK installed. It needs both packages.
Also, have you properly declared the path? It should be something similar to this:
export LD_LIBRARY_PATH=/usr/lib/oracle/12.1/client/lib:$LD_LIBRARY_PATH (make sure to adjust it to your version of the client and your proper path)

Also, in your .env file, in case you have multiple additional runners, they should be separated by a “,”, in my example, I have Python as anoter runner:
export REDASH_ADDITIONAL_QUERY_RUNNERS=redash.query_runner.oracle,redash.query_runner.python

Hope it helps!


#4

Thank you Mat and thank you Arik!

@Arik, of course i used your scripts!

Your assumption is correct. It ended with an error.

import cx_Oracle
Traceback (most recent call last):
File “”, line 1, in
File “build/bdist.linux-x86_64/egg/cx_Oracle.py”, line 7, in
File “build/bdist.linux-x86_64/egg/cx_Oracle.py”, line 6, in bootstrap
DatabaseError: DPI-1047: Oracle Client library cannot be loaded: libclntsh.so: cannot open shared object file: No such file or directory. See https://oracle.github.io/odpi/doc/installation.html for help

but i’m stuck in here…

I’d installed this Oracle packages:
oracle-instantclient12.2-devel-12.2.0.1.0-1.x86_64.rpm
oracle-instantclient12.2-sqlplus-12.2.0.1.0-1.x86_64.rpm
oracle-instantclient12.2-basic-12.2.0.1.0-1.x86_64.rpm

and added this lines to my environment file /etc/bash.bashrc

set PATH so it includes user’s private bin directories

PATH="$HOME/bin:$HOME/.local/bin:$PATH"
export ORACLE_HOME=/usr/lib/oracle/12.2/client64
export LD_LIBRARY_PATH=/usr/lib/oracle/12.2/client64/lib/:$LD_LIBRARY_PATH
export PATH=$PATH:$ORACLE_HOME/bin

this note seems important:
when i add the line:
export REDASH_ADDITIONAL_QUERY_RUNNERS=redash.query_runner.oracle
to .env
and restart the services, i’m getting 502 Bad Gateway nginx
commenting the line, redash starts ok.

my full .env is:
export REDASH_LOG_LEVEL=“INFO”
export REDASH_REDIS_URL=redis://localhost:6379/0
export REDASH_DATABASE_URL=“postgresql:///redash”
export REDASH_COOKIE_SECRET=dfLEvzLiLtDOLh1OuzJOfV5wvmOv8U8D
#export REDASH_ADDITIONAL_QUERY_RUNNERS=redash.query_runner.oracle

thanks for your support.

best regards,
fayol


#5

Hi Fayol,
It seems that your cx_oracle is not properly installed.
Just to be sure, at this stage, I would test my oracle client, see if it’s properly installed, and even go further by testing a query with the sqlplus client, but that’s another challenge by itself, as you will have to create a DB connection.
Then find a way to validate the proper environment path for the Oracle client. This is a very tricky part, user and OS dependent.
Finally, re-install cx_Oracle or find alternate ways to manually install it.
Good luck!


#6

Hi fayol,
have you solved the problem?

I have the same. If I try to import cx_Oracle from the basic system Python interpreter – it is fine.
But in case of the redash shell it cause the same mistake as you described.

If you have any idea how to solve that, please, give some advice.


#7

Hey fayol and others who have stumbled upon this thread trying to configure Oracle for Redash but not having any luck. After using this post as a guideline and doing some troubleshooting I finally got redash working with oracle! Here is what I did below so that you can replicate it/use it to get Oracle working with Redash yourself.

I’m going to make some general assumptions that you are using a redash AMI and that you have properly installed the oracle client on the AMI (I personally recommend using the rpm, but the zip file works as well as long as you reference the correct path). For my case, I installed oracle client 11.2.0.4 with the basic, SDK, and SQL plus packages.

Here’s where it can get tricky, as of my post now, Redash uses the cx_Oracle module 5.2 which if you run the command “import cx_Oracle” after using “python pip -m install cx_Oracle” you will get the latest version which is cx_Oracle 6.0. Personally, I could not get this version to work with my environment and got the error “DPI-1054: connection cannot be closed when open statements or LOBs exist” which is specific to cx_Oracle release 6.0. The workaround is to download cx_oracle version 5.2 from the link I provide here: https://pypi.python.org/packages/46/a3/59c2510c3c3440db528b74887ed869b557fb6e17a8398ca4e5caee64f377/cx_Oracle-5.2.tar.gz#md5=6a49e1aa0e5b48589f8edfe5884ff5a5

Then once downloaded, use the command tar -zxvf cx_Oracle-5.2.tar.gz to access the cx_Oracle repository.

cd to cx_Oracle-5.2/ and run the command sudo python setup.py build && sudo python setup.py install (Ignore any warning messages or other messages generated here). Also, note that if you have already installed the cx_Oracle package it will give you an error when trying to install the new package on top. My advice is to uninstall cx_Oracle and reinstall with 5.2.

Another interesting thing to note is that building the cx_Oracle 5.2 version will require the use of a python egg cache folder for redash to use to store metadata. I created one in the /tmp/ directory using sudo mkdir python-eggs. NOTE* You must also give this directory the correct permissions for redash to have write permissions. Use chmod for this.

Now you are almost finished configuring your environment to use Oracle! cd to the /opt/redash directory and use sudo nano .env to edit the environment variables. You will need to export the LD_LIBRARY_PATH, PYTHON_EGG_CACHE directory and the REDASH_ADDITIONAL_QUERY_RUNNERS. Below is what I added in my .env file.

export LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib/
export PYTHON_EGG_CACHE=/tmp/python-eggs
export REDASH_ADDITIONAL_QUERY_RUNNERS=redash.query_runner.oracle

Now simply restart the service using sudo supervisorctl restart all and log into your redash AMI. Put in the correct database information for your Oracle database and viola! You should now be able to test connection, and it should be successful!

If it is not successful, or if you are getting a 502 bad gateway error, check the log files using sudo tail -F /var/log/supervisor/*.log for further info.


#8

thanks to all above.

I have solved my “import cx_Oracle” problem inspired by this post.

  1. run
    pip install cx_Oracle==5.2.1
    to install an older version of cx_Oracle.

  2. LD_LIBRARY_PATH doesn’t work on my ubuntu server unreasonably, so I run
    echo /usr/lib/oracle/12.2/client64/lib > /etc/ld.so.conf.d/oracle.conf
    (adjust the path to your environment)