Prerequisites for SQL Integration¶
DSS Admin permission is required in order to configure a database connection as described below.
Tip
The instructions below refer to a local installation of PostgreSQL. If you’re using Dataiku Cloud, in addition to bringing your own PostgreSQL database, another option is starting a free trial using Snowflake Partner Connect, following these instructions.
Install PostgreSQL¶
If it isn’t already available, install PostgreSQL version 9 or above. Take note of the host on which PostgreSQL is installed.
Note
Tip: On macOS, we recommend using 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) 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 dku
database.