Database integration

Datalore provides a UI that allows you to create database connections, attach them to a notebook, and retrieve their data for your code.

Add a database connection to a notebook

This procedure creates a database connection and attaches it to a specific notebook.

Go to Main menu | Tools | Attached data or click the Attached data icon on the left-hand sidebar.
Click Select data to attach and select New database connection.
In the New database connection dialog, use one of the two options:
- Select a database type and configure it using the respective interface.
  Read the procedure below to use this method.
- If you have a pre-built connection URL, switch to the Enter URL tab, provide the URL in the text field and click Next. This will open the connection dialog for the respective database type with all the fields pre-filled.
  Use this method to pass additional parameters. For example, add &SSL=true to the URL string to enable SSL.

Configure a connection using database-specific interface

In the New database connection dialog, select a database type.
In the New [database_type_name] connection dialog, fill in the following fields:
- On the General tab:
  1. Select the connection type.
    The actual list of options will depend on the selected database type.
    - Default: to connect by specify the Host, Port, and Database.
    - IAM cluster/region: to connect by using Database, Region, and Cluster.
    - URL only: to connect by providing the URL of a pre-built connection.
      Use this method to pass additional parameters. For example, add &SSL=true to the URL string to enable SSL.
    - SID: to connect by using a unique name of an Oracle instance.
    - Service name: to connect by using an alias to an Oracle instance.
  2. Select whether to use authorization.
    For some database types, you can select their specific authorization methods. For example, for Amazon Redshift or Athena, you can select AWS Profile (more details here), or for Snowflake, you can select Authenticator (more details here).
  3. Provide your user credentials if required by the connection type.
  4. Specify the database if required by the connection type.
  5. Select whether to use the IAM database authorization (where applicable).
- On the SSH tab:
  1. Select the Enable SSH checkbox.
  2. Specify the host and the port.
  3. Specify the username.
  4. Select the authentication method:
    - Password: to access the host with a password.
    - Key pair: to use SSH authentication with a key pair. You can select one of the key pairs you added under SSH keys in your account settings menu.
- (Optional) Configure the database introspection scope on the Schemas tab:
  1. Click Refresh to get the list of the database schemas.
  2. Select or deselect schemas for introspection.
  Find more details about changing introspection scope in this procedure.
Click the Test connection button at the bottom of the dialog.
Once successfully tested, click the Create connection button.

Once the database connection is attached to the notebook, you can do the following in Attached data:

Click New SQL cell to query the database
Click the database to expand the detail view and browse the schema
Click the vertical ellipsis to refresh the schema, edit the connection, or detach it from the notebook

The new database connection is attached to the notebook and added to the Databases list on the home page of the respective workspace.

Edit database introspection scope

You can always edit the list of introspected schemas for an established connection in Attached data:

Click the connection to open the detail view.
Hover over the introspected schema indicator next to the database name and click Edit introspection scope.
Select or deselect the list items in the Edit introspection scope dialog and click Save and close to finish the procedure.

Add a database connection to a workspace

You can open the database connection interface from your Home page. In this case, the new database connection will be added to the workspace without being attached to any specific notebook.

On the Home page, select the workspace where you want to create the connection.
Select Databases from the menu under the workspace name.
Click the Add connection button in the upper right corner. The New database connection dialog will open.
Perform steps 3 to 7 from the procedure above to create the database connection.

The newly created connection is added to the Databases list on the home page of the respective workspace.

Attach a database connection to a notebook

You can attach to a notebook a database connection you previously created for the respective workspace.

Go to Main menu | Tools | Attached data or click the Attached data icon on the left-hand sidebar.
Click Select data to attach to expand the list.
From the list, select the database connection you want to attach to the notebook.

Use the demo database

If you are new to the feature, you can attach our demo database to check how databases and SQL cells work in Datalore.

In the editor, open the Attached data tool.
Click Take a tour on the banner.
In the Attach a database connection popup, click Connect demo database.
In the attached data sources, click the added database to view the details and browse the schema.

If you chose not to take a tour and want to add the demo database later on, use the following credentials:

driver: postgresql
host: demo-database.private.datalore.io
port: 5432
database: datalore
user: datalore
password: datalore

SQL cells

Use SQL cells to query attached databases and retrieve data as tabular data objects. We recommend that you watch our video on SQL cells.

Query a database in an SQL cell

This procedure explains how to retrieve data from an attached database for further use in your code.

Create an SQL cell:
- Go to Tools | Attached data and click New SQL cell for the attached database that you want to use.
- Hover over the bottom border of a cell and select More cell types | SQL cell. You will be prompted to select the required database if you you have more than one database attached.
Enter an SQL statement.
The cell provides code completion based on the introspected database schema.
Run the query (Run icon or Control, +, Enter shortcut). The result set will be shown in the output and saved to a tabular data object (for example, DataFrame).
(Optional) To rename the data object, click the object name next to RESULT SAVED TO on the SQL toolbar.

SQL cells in trial mode

For Community plan users, SQL cells are available in trial mode for a period of 30 days. Within the trial period, you can create and attach an unlimited number of database connections and use an unlimited number of SQL cells to query them.

You can start the trial in one of the following ways:

Click Start trial in the notification window that you can see when you choose to add an SQL cell.
Click Start trial on the banner that you can see when you open Attached data.

In the Attached data pane, you are notified when your trial period expires.

SQL cell usage example

Below is an example of two SQL cells used to work with a Snowflake database.

Parameterized SQL queries

You can use Python variables in your SQL queries in Datalore. Such queries can be reused repeatedly with different values, which helps make your reports more interactive.

Supported variable types are: strings, numbers, booleans, and lists. Make sure you place your variable inside {} brackets.

Parameterized SQL query example

In the image below, you can see two cells:

Dropdown interactive control cell using the method variable.
SQL cell where the value in payment column equals the method variable value selected from the dropdown list.

Database sharing options

Shared workspace

Editors can create and edit database connections.
Editors can create and run SQL cells to access the connected databases.
Viewers can view the connection list and respective database schemas

Home workspace notebook

Editors can create and execute SQL cells to access connected databases.
Viewers can only view the currently attached database connections.

Last modified: 05 September 2022