Database integration

Last modified: 28 February 2023

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.

  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 two 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

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

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

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

  1. Create an SQL cell:

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

      Opening an SQL cell from Attached data

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

      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 tabular data object (for example, DataFrame).

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

    Trial notification

  • Click Start trial on the banner that you can see when you open Attached data.

    Database and SQL notification banner

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

Trial period notification

note

SQL cell usage restrictions for Community plan users

  • After the trial period expires, Community plan users can keep working on notebooks containing SQL cells; but to keep using the feature, it is required to subscribe to the Professional plan.

  • Community plan users cannot execute SQL cells in notebooks shared by Professional plan users, unless the feature is available in trial mode.

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.