Connecting to PostgreSQL in Dataiku DSS

This brief tutorial walks through the process of configuring a connection between Dataiku DSS and a PostgreSQL server.

Prerequisites

  • DSS Admin permission

Install PostgreSQL

If it isn’t already available, install PostgreSQL version 9. Take note of the host on which PostgreSQL is installed.

Note

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 reference 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.

../../_images/gotoadmin.png

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

../../_images/screen-newconnection.png

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

You can then test and create your PostgreSQL connection.

../../_images/screen-connection-ok.png

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.

  • Click Import tables to datasets.
  • Select the tables you want to import.
  • Click Import tables.
  • Select the project in which the datasets associated with these tables should appear.
../../_images/db-mass-import-tables.png

What’s next

Once you have successfully configured a connection between Dataiku DSS and a PostgreSQL server, be sure to move on to the first tutorial for working with SQL in DSS.