Configure Hue with DB Query
Hue's DB Query application provides a way to view data in these four database formats:
- SQLite
- MySQL
- PostgreSQL
- Oracle
To configure Hue to work with DB Query, edit
the [librdbms]
section of
the hue.ini
file and change the parameters
related to the particular database you are using.
To disable the DB Query application, add rdbms
to the blacklist as
shown in Disabling an
Application.
Default librdbms Settings in hue.ini
Database settings are contained in
the [librdbms]
section of
the hue.ini
file. Two databases (SQLite and
MySQL) are already configured in their own sections under
the [[databases]]
heading. Note that
PostgreSQL and Oracle work similarly to MySQL, so
the [[[mysql]]]
section can be treated as a
template that can be edited for these two databases.
The DB Query application supports four types of databases, which
are configured in
the [[databases]]
section.
The default version of
the librdbms
section of
the hue.ini
file looks like this:
#################################################################
# Settings for the RDBMS application
#################################################################
[librdbms]
# The RDBMS app can have any number of databases configured in the databases
# section. A database is known by its section name
# (i.e. sqlite, mysql, psql, and oracle in the list below).
[[databases]]
# sqlite configuration.
## [[[sqlite]]]
# Name to show in the UI.
## nice_name=SQLite
# For SQLite, name defines the path to the database.
## name=/opt/mapr/hue/hue-<version>/desktop/desktop.db
# Database backend to use.
## engine=sqlite
# mysql, oracle, or postgresql configuration.
## [[[mysql]]]
# Name to show in the UI.
## nice_name="My SQL DB"
# For MySQL and PostgreSQL, name is the name of the database.
# For Oracle, Name is instance of the Oracle server. For express edition
# this is 'xe' by default.
## name=mysqldb
# Database backend to use. This can be:
# 1. mysql
# 2. postgresql
# 3. oracle
## engine=mysql
# IP or hostname of the database to connect to.
## host=localhost
# Port the database server is listening to. Defaults are:
# 1. MySQL: 3306
# 2. PostgreSQL: 5432
# 3. Oracle Express Edition: 1521
## port=3306
# Username to authenticate with when connecting to the database.
## user=example
# Password matching the username to authenticate with when
# connecting to the database.
## password=example
Using DB Query with SQLite
To configure Hue to work with SQLite, edit
the sqlite
section of
the hue.ini
file and replace default values
as necessary.
Default sqlite Section
The default version of the sqlite
section
is shown here.
# sqlite configuration
## [[[sqlite]]]
# Name to show in the UI.
## nice_name=SQLite
# For SQLite, name defines the path to the database.
## name=/tmp/sqlite.db
# Database backend to use.
## engine=sqlite
Modifying the sqlite Section
To configure Hue’s DB Query application to work with SQLite,
edit the sqlite
section of
the hue.ini
file as follows:
-
Remove the comment characters (##) in front of the [[[sqlite]]] statement.
-
Replace the default values for the following parameters as necessary.
Parameter Default Value New Value nice_name
SQLite
The name that you want to appear in the UI name
/tmp/sqlite.db
The path to the SQLite database -
Remove the comment characters (
##
) for the parameter values you changed. Otherwise, the default values are used.
Using DB Query with MySQL, PostgreSQL, or Oracle
Instructions for MySQL, PostgreSQL, and Oracle are similar. These instructions show how to configure Hue to work with MySQL. You can adapt the instructions to configure Hue to work with PostgreSQL or Oracle.
The next section shows the default mysql
section of the
hue.ini
file for illustration. For instructions on configuring Hue
so the DB Query application can display data from a MySQL database, see Modifying the mysql
Section.
Default mysql Section
The default version of the mysql
section
is shown here.
# mysql, oracle, or postgresql configuration.
## [[[mysql]]]
# Name to show in the UI.
## nice_name="My SQL DB"
# For MySQL and PostgreSQL, name is the name of the database.
# For Oracle, Name is the instance of the Oracle server. For express edition
# this is 'xe' by default.
## name=mysqldb
# Database backend to use. This can be:
# 1. mysql
# 2. postgresql
# 3. oracle
## engine=mysql
# IP or hostname of the database to connect to.
## host=localhost
# Port the database server is listening to. Defaults are:
# 1. MySQL: 3306
# 2. PostgreSQL: 5432
# 3. Oracle Express Edition: 1521
## port=3306
# Username to authenticate with when connecting to the database.
## user=example
# Password matching the username to authenticate with when
# connecting to the database.
## password=example
Modifying the mysql Section
To configure Hue so the DB
Query application can work with MySQL, edit
the mysql
section of
the hue.ini
file as follows:
-
Replace the default values for the following parameters.
Parameter Default Value New Value nice_name
“My SQL DB”
The name that you want to appear in the UI name
mysqldb
The name of the database
host
localhost
IP address or hostname of the database to connect to
user
example
The username to use for authentication when connecting to the database
password
example
The password to use with the username (above) for authentication when connecting to the database -
Remove the comment characters (
##
) for the parameter values you changed. Otherwise, the default values are used.
Activating DB Query with mySQL
To activate DBQuery with MySQL, follow these steps:
-
Move the mysql python library to a back-up location and regenerate it to match the version on the deployment machine.
mv /opt/mapr/hue/hue-<VERSION>/python2.6/site-packages/MySQL_python-1.2.3c1-py2.6-linux-x86_64.egg MySQL_python-1.2.3c1-py2.6-linux-x86_64.egg.bk -
Regenerate the mysql python library by running the following command:
/opt/mapr/hue/hue-<VERSION>/build/env/bin/pip install mysql-python
Modifying the mysql Section for PostgreSQL
If you are working with a
PostgreSQL database, edit the mysql
section of
the hue.ini
file and rename
it postgresql
. Next, replace the MySQL default values as
shown:
-
Replace the following MySQL default values with PostgreSQL values.
Parameter Default Value New Value nice_name
“My SQL DB”
The name that you want to appear in the UI
name
mysqldb
The name of the database
engine
mysql
postgresql (the database backend)
port
3306
5432 -
Replace the following default values with your host machine, username, and password.
Parameter Default Value New Value host
localhost
IP address or hostname of the database to connect to
user
example
The username to use for authentication when connecting to the database
password
example
The password to use with the username (above) for authentication when connecting to the database -
Remove the comment characters (
##
) at the beginning of each line where you changed parameter values. Otherwise, the MySQL default values are used.
Modifying the mysql Section for Oracle
If you are working with an
Oracle database, edit the mysql
section of
the hue.ini
file and rename
it oracle
. Next, replace the MySQL default values as
shown:
-
Replace the following MySQL default values with Oracle values.
Parameter Default Value New Value nice_name
“My SQL DB”
The name that you want to appear in the UI
name
mysqldb
The instance of the Oracle server (for express edition, this is xe by default)
engine
mysql
oracle (the database backend)
port
3306
1521 (for Oracle Express Edition) -
Replace the following default values with your host machine, username, and password.
Parameter Default Value New Value host
localhost
IP address or hostname of the database to connect to
user
example
The username to use for authentication when connecting to the database
password
example
The password to use with the username (above) for authentication when connecting to the database - Remove the comment characters (
##
) at the beginning of each line where you changed parameter values. Otherwise, the mysql default values are used.