Concept: Connections to SQL Databases

In the previous video, you learned about connecting Dataiku DSS with SQL databases and leveraging the connections in DSS projects. Let’s summarize the key points of the video before continuing on to the next lesson.

Supported Databases

Dataiku DSS provides varying levels of support for connections to SQL databases. These support levels include full support, limited support, and no support. For the latest details on database support, be sure to check the reference documentation .

../../../_images/connections-support-levels.png

For unsupported databases, connections from DSS are still possible by using the appropriate Java Database Connectivity (JDBC) drivers. In such cases, reading from the database often works, but it is rare that writing to the database would work without some further configuration.

In-Database Computation

Apart from reading and writing datasets in SQL databases, DSS can leverage in-database computation when rendering charts, executing visual recipes, and executing SQL code recipes. Leveraging the SQL execution engine can speed up computation!

../../../_images/in-db-computation.png

Defining a Connection

Setting up a connection to an SQL database involves two main steps. First, install the JDBC driver for your database into the DSS data directory by copying the driver’s JAR file (and any dependencies) to the DATA_DIR/lib/jdbc folder. Note that DATA_DIR refers to the data directory where DSS is installed.

Next, from the Administration section of the DSS homepage, a user with admin rights can create a new connection by going to the Connections tab, selecting the appropriate connection type (for the particular SQL database), and filling in parameter values such as the database name, host, port, user, and password.

The admin can also define other settings like authentication mode or permissions for user groups on this connection. For example, the admin can grant the data team permission to read only or to read and write from this connection.

Once permissions have been given, and the connection has been set up and tested, you can return to your project and import datasets from this SQL connection.

For more information about connecting to SQL databases, see SQL databases in the reference documentation.