Tutorial | Configure a connection between Dataiku and a SQL database#
Data projects must begin with connecting to data sources.
In this tutorial, you will:
Create a local SQL connection if you do not already have one available.
Create a connection to this source in Dataiku.
You will need admin permission on a Dataiku instance.
You may also want to review this tutorial’s associated concept article.
Create a SQL database#
To get started, you need to have a SQL database available. If you already have one, please skip to the next section.
For this example, we’ll use PostgreSQL, but you can follow a similar process for any supported SQL database.
If it isn’t already available, install PostgreSQL version 9 or above. Take note of the host on which PostgreSQL is installed.
On macOS, we recommend using the Postgres App.
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, the sample code below creates the user
matthieu, with password
Password, and grants this user all privileges (can create and delete tables) in 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
psql -h localhost
CREATE DATABASE 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;
Configure a SQL connection in Dataiku#
Now that we have credentials for a SQL database, we need to establish a connection between Dataiku and this database.
This walkthrough is for a self-managed Dataiku instance. If using Dataiku Cloud, see the how-to for adding a new connection.
Create a connection#
Log in as the Dataiku Administrator.
From the Applications menu in the top navigation bar, choose Administration.
Navigate to the Connections tab, and select New Connection > PostgreSQL.
Fill in the information required for the connection. Don’t forget to give a name to your connection!
You cannot change the connection name afterward.
Test and create your PostgreSQL connection.
Import datasets into Dataiku#
After creating your connection, if you are connecting to a pre-existing SQL database, you can create a Dataiku 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.
Congratulations! You now have a SQL connection to use underneath your Dataiku Flows.
Learn more about how to take advantage of this type of connection in Tutorial | Data transfer with visual recipes.