Database integration

Last modified: 18 September 2023

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

Edit your database ACL

Unless you're using Datalore Enterprise (in a private cloud or on-premises) or SSH tunneling, whitelist Datalore's 63.33.83.29 IP address for your database connections to grant access.

Procedures

Add a database connection to a notebook

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

  1. Go to Main menu | Tools | Attached data or click the Attached data icon on the left-hand sidebar.

  2. Click New connection and select New database connection.

  3. In the New database connection dialog, use one of the three options:

    • Select a database type and configure it using the respective interface.

      Read the procedure below to use this method.

      New database connection dialog

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

      tip

      Use this method to pass additional parameters. For example, add &SSL=true to the URL string to enable SSL.

      Enter URL tab

    • To import an XML file with a database configuration, copy the file from your file system, switch to the Import XML tab and click .

Configure a connection using database-specific interface

  1. In the New database connection dialog, select a database type.

  2. In the New [database_type_name] connection dialog, fill in the following fields:

    • On the General tab:

      1. Select the connection type.

        note

        The actual list of options will depend on the selected database type.

        • Default: to connect by specifying 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.

          note

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

        note

        For some database types, you can select their specific authentication 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).

      Integration with PostgreSQL, General tab

    • On the SSH tab:

      1. Select the Enable SSH checkbox.

      2. Specify the host and the port.

      3. Specify the username.

      4. Select the authorization type:

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

      tip

      Find more details about changing introspection scope in this procedure.

  3. Click the Test connection button at the bottom of the dialog.

  4. Once successfully tested, click the Create connection button.

Configure a BigQuery database connection

The dialog for a BigQuery database connection has specific fields to fill in.

  1. In the New database connection dialog, select Google BigQuery.

  2. In the New BigQuery connection dialog, do the following:

    • Expand the Authorization list and select your option.

    • In the Project ID field, type the project identifier. Usually, it is a part of the service account email identifier that goes after the at sign (@). In our case, it is bigqueryproject-322409.

    • Fill in the Service account email field.

    • Under Key file, click the Select file button and select the required file using the browser window.

    • Click the Test connection button at the bottom of the dialog.

Actions after attaching a database connection

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

tip

Schema refreshing is automatic when a database is being attached to the notebook. Refresh the schema to make sure your data is updated after a possible change to the respective database. Refreshing a schema may take some time.

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

note

The connection credentials for databases used in shared notebooks remain unavailable to collaborators.

Edit database introspection scope

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

  1. Click the connection to open the detail view.

  2. Hover over the introspected schema indicator next to the database name and click Edit introspection scope.

    Introspected schemas

  3. Select or deselect the list items in the Edit introspection scope dialog and click Save and close to finish the procedure.

    Selecting schemas for introspection

Manage additional parameters for your connection

Use the Advanced tab of the database connection dialog to manage additional parameters. For example, you can manage your JDBC driver custom options here.

  1. Switch to the Advanced tab of the dialog.

  2. Click Add key-value pair to add a parameter and its value.

  3. Repeat the previous step for each parameter required for your connection.

  4. Click Save and close to finish your work in the dialog or switch to another tab to continue creating the connection.

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.

  1. On the Home page, select the workspace where you want to create the connection.

    Selecting a workspace

  2. Select Databases from the menu under the workspace name.

  3. Click the Add connection button in the upper right corner. The New database connection dialog will open.

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

Datababse list on Home page

Attach a database connection to a notebook

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

  1. Go to Main menu | Tools | Attached data or click the Attached data icon on the left-hand sidebar.

  2. Click Select data to attach to expand the list.

    Selecting a database to attach

  3. From the list, select the database connection you want to attach to the notebook.

tip

In case you have trouble connecting to a database via the cloud platform, consider using the Datalore Enterprise product version.

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.

  1. In the editor, open the Attached data tool.

  2. Click Take a tour on the banner.

    Database banner

  3. In the Attach a database connection popup, click Connect demo database.

    Database tour popup

  4. In the attached data sources, click the added database to view the details and browse the schema.

    Viewing the demo database 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.

note

Reactive mode does not work properly with SQL directly from Python code. Use SQL cells when activating this mode.

Query a database in an SQL cell

  1. Add an SQL cell:

    • Go to Tools | Attached data and click Connect SQL cell for the attached database that you want to query.

      Opening an SQL cell from Attached data

    • Hover over the bottom border of a cell and select More cell types | SQL. If there are several attached databases, in the cell, click Select database and select the required data source.

      Selecting a database for querying

  2. Enter an SQL statement.

    note

    The cell provides code completion based on the introspected database schema.

  3. Run the query (Run icon or shortcut). The result set will be shown in the output and saved to a DataFrame. The resulting DataFrame name is shown in the cell toolbar.

    tip

    By clicking the Stop button or interrupting/restarting the kernel, you explicitly stop querying the database.

  4. (Optional) To rename the data object, click the object name next to RESULT SAVED TO on the SQL toolbar.

tip

To get a visual representation of the retrieved data, switch to the Visualize tab. Find more details in Automatic plotting.

SQL cell usage example

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

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

Using a parameterized SQL query

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.

Clone database connections to other workspaces

Clone database connections to other workspaces without associated credentials.

  1. On the Home page, select Databases from the left-hand menu.

  2. On the Databases list, click the ellipsis icon for the database connection you want to clone.

  3. From the expanded menu, select if you want to clone the database connection to this or other workspaces. If you chose the current workspace, the connection will be added to the list, which will conclude the procedure. Read the further steps if you selected cloning to other workspaces.

  4. In the Clone [database_connection_name] to other workspaces dialog, expand the Workspaces dropdown list.

  5. Select the workspaces where you want to clone the database connection and click anywhere outside the dropdown.

  6. Click the Clone button. This will close the dialog, followed by a success notification.