howto

Connecting to PostgreSQL in Dataiku DSS

May 01, 2017

Connecting to PostgreSQL server in Dataiku DSS is easy!

Install PostgreSQL

If it isn’t already available, install PostgreSQL version 9. Take note of the host on which PostgreSQL is installed. Tip: On macOS, we recommend the Postgres App

Create and configure your PostgreSQL database

At a minimum you need a user and a database, as explained in the PostgreSQL guide. As a best practice, we recommend using schemas in order to administer multiple projects within a database. For example:

psql -h localhost
CREATE DATABASE dku;
\c dku
CREATE SCHEMA dku_churn;
CREATE USER matthieu WITH PASSWORD 'Password';
GRANT ALL PRIVILEGES ON SCHEMA dku_churn TO matthieu;
CREATE SCHEMA dku_tshirt;
CREATE USER dku_tshirt_admin WITH PASSWORD 'Password';
GRANT ALL PRIVILEGES ON SCHEMA dku_tshirt TO dku_tshirt_admin;
\q

This sample code creates the user matthieu, with password Password, and grants this user all privileges (can create and delete tables) on the dku_churn schema in the dku database.

Similarly, user dku_tshirt_admin has been granted all privileges on the dku_tshirt schema in the dku database.

Configure the connection between Dataiku DSS and PostgreSQL

Finally you need to establish a connection between Dataiku DSS and your PostgreSQL database, following the instructions given in the documentation. However, note that only the Dataiku DSS Administrator has permissions to do this. If you do not have this role, you should contact the person in charge!

Log in as the Dataiku DSS Administrator, and from the Admin Tools menu in the top navigation bar, choose Administration.

Navigate to the Connections tab and click New Connection > PostgreSQL.

Administration panel screenshot with New Connection > PostgreSQL selected

Finally, fill in the information required for the connection. Don’t forget to give a name to your connection! but beware, you can’t change it afterwards.

You can then test and create your PostgreSQL connection.

PostgreSQL connection page

After creating your connection, if you are connecting to a pre-existing PostgreSQL database, you can create a Dataiku DSS dataset for every table in the database. Simply select a target project and click Import tables.