Integrate Hue with Relational Databases

There are two options for integrating Hue with relational databases:
  • SQLAlchemy interpreter interface (recommended)
  • RDBMS Hue application (legacy)
The Hue UI can be integrated to browse and query the following databases:
  • MySQL/MariaDB (supported only with the SQLAlchemy interface)
  • Oracle
  • PostgreSQL
  • SQLite

Integrate with the SQLAlchemy Interpreter Interface

You can connect different databases by adding corresponding entries into the [notebook][[interpreters]] section of the hue.ini.

In the new section, you need to specify interface=sqlalchemy and the SQLAlchemy connection string in the url field of the options parameter.

Later on this page you can find examples of connection configurations for different relational databases. Additional information about the SQLAlchemy connection options can be found in the following SQLAlchemy documentation:

Engine Configuration — SQLAlchemy 1.3 Documentation

Integrate with MySQL / MariaDB

Here is an example of integrating the Hue UI with MySQL / MariaDB:
[notebook] 
# ... 
[[interpreters]] 
# ... 
[[[mysql]]] 
  name = MySQL 
  interface=sqlalchemy 
  ## https://docs.sqlalchemy.org/en/latest/dialects/mysql.html 
  options='{"url": "mysql+mysqlconnector://root:secret@database:3306/hue"}'
For information about SQLAlchemy MySQL/MariaDB connector options, see the following article:
MySQL / MySQL-Connector — SQLAlchemy 1.3 Documentation
NOTE
The default SQLAlchemy MySQL dialect is not available in Hue 4.11. Instead, you must use mysql+mysqlconnector.

Integrate with Oracle

Before integrating the Hue UI with an Oracle Database, you must install the cx_Oracle Python module in Hue. To do this, follow steps 1-3 in the Configure Hue to Store Data in Oracle Database documentation.

For more information about the SQLAlchemy Oracle connector options, see the following article:

Oracle / cx_Oracle – SQLAlchemy 1.3 Documentation

[notebook] 
# ... 
[[interpreters]] 
# ... 
[[[oracle]]] 
  name = Oracle 
  interface=sqlalchemy 
  options='{"url": "oracle://hue:hue@host:1521/hue"}'

Integrate with PostgreSQL

Before integrating Hue UI with the PostgreSQL RDBMS, install the psycopg2 Python module. To do this, follow steps 1-3 in the Configure Hue to Store Data in PostgreSQL documentation.

For more information about SQLAlchemy PostgreSQL connection options, see the following article:

PostgreSQL / psycopg2 – SQLAlchemy 1.3 Documentation

Here is an example of integrating the Hue UI with PostgreSQL:
[notebook]
# ...
[[interpreters]]
# ...
[[[postgresql]]]
 name = PostgreSQL
 interface=sqlalchemy
 options='{"url": "postgresql://hue:hue@host:5432/hue"}'

Integrate with SQLite

For information about SQLAlchemy SQLite connector options, see the following article:

SQLite / Pysqlite — SQLAlchemy 1.3 Documentation

Here's an example of integrating the Hue UI with SQLite:
[notebook] 
# … 
[[interpreters]] 
# … 
[[[sqlite]]] 
  name = SQLite 
  interface=sqlalchemy 
  options='{"url": "sqlite:///relative/path/to/database/file.db"}' 
  # options='{"url": "sqlite:////absolute/path/to/database/file.db"}'

Integrate with the RDBMS Hue Application

Beginning with Hue 4.11:
  • The following method is obsolete.
  • MySQL integration is not supported in the RDBMS Hue application.
To configure access to a relational database server through the Hue RDBMS application, you need to add your database configuration as an entry in the [librdbms][[databases]] section of hue.ini. You also need to add a corresponding entry to the [notebook][[interpreters]] section with the same section name and the interface parameter set to rdbms.
Table 1. Available Parameters for librdbms Entries
Parameter Used for SQLite? Value
nice_name Yes The nice_name of the current configuration entry.
engine No The database back end to use. Available values are:
  • oracle
  • postgresql
  • sqlite
host No The IP or hostname of the database server to connect to.
port No The ports that the database server is listening to. The default ports are:
  • PostgreSQL:5432
  • Oracle Express Edition:1521
name Yes The database name to connect to. Leave this property empty if you do not want to connect to a specific database on your database server. For SQLite, this is the path to the database file.
user No The user name to authenticate with when connecting to the database server.
password No The password for the user name that you will authenticate with when connecting to the database server.
password_script No As an alternative to specifying the password explicitly in the hue.ini, you can use the password_script parameter to specify the path to the executable file that will be invoked by Hue to read the password.
options Yes For additional options to send to the database server when connecting, see this page: https://docs.djangoproject.com/en/1.11/ref/databases/

Following are examples for connecting to different relational databases.

Integrate with Oracle

Before integrating Hue UI with an Oracle database, install the cx_Oracle Python module in Hue. To do this, follow steps 1-3 in the Configure Hue to Store Data in Oracle Database documentation.

Here is an example of integrating the Hue UI with Oracle:
[librdbms] 
# ... 
[[databases]] 
# ... 
[[[oracle]]] 
nice_name="Oracle DB" 
name=example_database 
engine=oracle 
host=example.host 
port=1521 
user=example_user 
password=example_password 

# ... 
[notebook] 
# ... 
[[interpreters]] 
# ... 
[[[oracle]]] 
  name = Oracle 
  interface=rdbms

Integrate with PostgreSQL

Before integrating Hue UI with the PostgreSQL RDBMS, install the psycopg2 Python module. To do this, follow steps 1-3 in the Configure Hue to Store Data in PostgreSQL documentation.

Here is an example of integrating the Hue UI with PostgreSQL:
[librdbms] 
# ... 
[[databases]] 
# ... 
[[[postgresql]]] 
nice_name="PostgreSQL DB" 
name=mysqldb 
engine=postgresql 
host=example.host 
port=5432 
user=example_user 
password=example_password 
  
# ... 
[notebook] 
# ... 
[[interpreters]] 
# ... 
[[[postgresql]]] 
  name = PostgreSQL 
  interface=rdbms

Integrate with SQLite

Here is an example of integrating the Hue UI with SQLite:
[librdbms] 
# ... 
[[databases]] 
# ... 
[[[sqlite]]] 
nice_name=SQLite 
name=/path/to/sqlite.db 
engine=sqlite 
  
# ... 
[notebook] 
# ... 
[[interpreters]] 
# ... 
[[[sqlite]]] 
  name = sqlite 
  interface=rdbms