Managing database connections

Overview

You can connect as many private databases to your Analyst Studio Workspace as you like. Analyst Studio supports connecting to most popular types of relational databases. When you connect a database to your Analyst Studio Workspace, by default all members of your Workspace will be able to run queries against that data connection.

Private data connections

Connect a new database

To connect your database to your Analyst Studio organization:

  1. Click the app switcher icon in the top navigation bar and select Analyst Studio.

  2. Click on your name in the upper left corner of the screen.

  3. Click Connect a Database.

  4. Follow the instructions to connect your database.

Learn more about how Analyst Studio connects to private databases.

Any active member of an Analyst Studio Workspace can create a new database connection. By default, new database connections may be queried by all members of the Workspace. To restrict non-admin access to a data source, you can limit access to it to specific users.

Modify or disconnect a connected database

You can only delete or modify the settings and credentials for a database connection if you’re an admin or if you were the member who connected it to the Workspace.

  1. Click the app switcher icon in the top navigation bar and select Analyst Studio.

  2. Click on your name in the upper left, and click Workspace Settings.

  3. In the Data section, click Connections.

  4. All the database connections for your Workspace are listed on the right.

  5. Click on the connection and then go to the Connection Properties tab to adjust the database credentials and configuration. Click Delete to disconnect this database from Analyst Studio.

    Deleting a database connection from Analyst Studio will prevent all Reports built using that connection from running until a new connection is selected. Deleting a database connection will not, however, have any effect on the database itself.

Update preferred JDBC driver

To update the driver Analyst Studio uses to connect to one of your data sources, you must have the manage data source permission or higher.

  1. Open the menu beneath your name in the upper left corner and select Workspace Settings.

    Organization Settings

  2. Scroll down to the Data section and select Manage Connections. In the main panel, select the connection you’d like to update from the list.

    Manage Connections

  3. Review and update the selected driver. Details on all driver versions and known issues are documented for each database technology below.

    Update Driver

  4. Re-enter the password of the Analyst Studio database user or otherwise re-authenticate the credentials, then Save changes.

    Enter Password

  5. You should see a blue banner at the top of the screen indicating the connection properties have been updated.

    Data Source Saved

  6. An error like the following indicates the username and password (or equivalent) must be re-entered before saving the driver changes.

    Data Source Auth Fail

Disable or enable automatic schema refresh

For admins and users with "manage" permission:

  1. In the Connections section, select a database connection to navigate to the settings.

  2. Toggle Automatic Schema Refresh off or on.

  3. Confirm and save changes to the connection properties. Schema refresh will now be disabled or enabled.

  4. Manually refresh the schema with the button at the top of the page.

Trigger a schema refresh

Analyst Studio’s schema update frequency is daily at 3:05pm UTC (7:05am PST / 8:05am PDT based on daylight savings). However, you can trigger a schema refresh at any time.

  1. In the SQL editor, click on the kebab menu next to your data source and select Refresh.

    Schema Browser Refresh

  2. You can also go to Workspace Settings in the menu beneath your name.

    Workplace Settings
    Then go to Manage Connections under the Data section to select the data source. Select the Refresh schema button in the top right corner.

    Manage Connections Refresh

Controlling access

Database user permissions

Most databases provide robust permission systems, which let you manage user roles to set detailed schema- and table-level permissions. In Analyst Studio, your Workspace will access each data connection through a single user, which we recommend you or your database admin create expressly for use with Analyst Studio.

Members of your Workspace who use this data connection will be able to access the data source according to the permissions granted to this user in your database. You can connect Analyst Studio to the same database multiple times via different database users, with varying levels of permissions.

There are various ways you can control the data that members of your Workspace can query in Analyst Studio. One way is to leverage the permissions granted to the single user your Workspace relies upon to connect to a given database. Limiting that user’s permissions on the database side will, in turn, restrict the access of all members of your Analyst Studio Workspace for that data connection.

Limit access to a data connection in Analyst Studio

Admins can also limit access to a data connection to specific non-admin users or user groups directly in Analyst Studio. If a non-admin user does not have access to a connected database, that user will not be able to:

  • Query the database or create, duplicate, edit, or schedule any Reports that query that database.

  • Write or view any of that data connection’s Definitions.

    Admins in an Analyst Studio Workspace always have query access to all data connections in that Workspace.

Setting the default connection access policy

Connection admins can set the default connection access policy for everyone in their Workspace to have View or Query access to the connection, or choose to set up access to None.

Connection Access Policy Illustration

  • The access type View allows all members to view content built on this connection.

  • The access type Query allows all members to view and query this connection.

Granting access type to groups and individuals

In addition, you can grant Query, View, or Manage access to groups and individuals.

Access Menu Illustration

To set up permissions to a connected database in Analyst Studio:

  1. Click the app switcher icon in the top navigation bar and select Analyst Studio.

  2. Click on your name in the upper left, and click Workspace Settings.

  3. In the Data section, click Manage Connections.

  4. Click on the data connection you want to limit.

  5. Click the Permissions tab.

  6. Click on Connection Access Policy, to set the default access type to None, View, or Query.

  7. To manually add users and user groups, click Add members. In the pop-up box, click on each user or user group that you want to grant access to this connection.

  8. Once you click on the user or user group, you will have the option to grant Query, View, or Manage access.

  9. If you change your mind and want to remove that member, click on the user or user group, then click on the Access Type and choose Remove Access. Then confirm Remove Access.

How database permissions and Analyst Studio permissions interact

Here’s an example of how this setup might look for a company with a single database, connected to Analyst Studio via two different database user roles.

Note that these permissions only determine query access. Users in your Workspace will be able to see a Report created with one of these data connections unless the Report is in a private Collection.

Database roles with query access

  1. In this simplified example, the company’s database contains two tables: a marketing table and a finance table containing sensitive information.

  2. There are two user roles (managed at the database level):

    • User Role #1 only has access to the marketing table.

    • User Role #2 has access to both the marketing table and the finance table.

  3. When you connect a database to Analyst Studio, you do so using database user roles. Each connection will appear as a different database in the schema browser. Note that the default name will be the name of the database (for example, Redshift), but you can also name it based on a description of the access level or user role.

  4. Workspaces can also specify which Analyst Studio users have access to each connection.

    • A user with query access to “Marketing” will ONLY be able to query the marketing table. They will not have access to any sensitive financial information.

    • A user with query access to “All” will be able to query the marketing and finance tables.

Query headers and footers

Analyst Studio enables customers to annotate queries in two ways:

  • Analyst Studio allows customers to define, using Liquid parameters and SQL, metadata attributes to be sent about each query. This metadata can include Analyst Studio user or query attributes (for example, username, query runner group, or timestamp of query run). Analyst Studio calls these query headers.

  • Analyst Studio automatically appends a SQL comment to each query with the following static Analyst Studio user attributes: email, timestamp of query run, Analyst Studio query run URL, and a boolean value indicating whether the query was executed manually or scheduled. Analyst Studio calls these query footers.

Query headers

Custom query headers allow admins to prepend queries with code that executes every time a query is run against the data connection. Using custom query headers you can:

  • Manage database load by giving services downstream of Analyst Studio context about who is running the query or why it is being run.

  • Automatically set environment variables, such as default schemas and time zones.

  • Add custom logging to each query that will appear in your database’s logs.

    Headers are injected only once per Analyst Studio SQL query, even if that query is split into multiple statements. Query headers appear as part of the syntax executed by the database. Query headers are not visible in Analyst Studio’s SQL Editor, but are viewable in a historical query run.

Modify a data connection’s query header

You must be the creator of a data connection or a Workspace admin to modify its query header.

  1. Click the app switcher icon in the top navigation bar and select Analyst Studio.

  2. Click on your name in the upper left, and click Workspace Settings.

  3. In the Data section, click Connections.

  4. Click on the data connection you would like to modify.

  5. Go to the Query Header tab.

  6. In the space provided, add or modify any valid SQL code (including comments and valid Liquid code) that you want Analyst Studio to prepend to all queries run against this connection.

  7. When you are finished, click Save.

Variables

Each time a query is executed, Analyst Studio automatically defines a number of Liquid variables containing useful audit information. You can reference these variables in your custom query headers to add audit information that will appear in your database’s logs. Both SQL and Liquid parameters are supported.

Variable Output

{{ query_runner_email }}

Email address of the user running the query.

{{ query_runner_username }}

Username of the user running the query.

{{ query_runner_groups }}

Array of group tokens* for groups the runner is a part of.

{{ run_at }}

A Unix timestamp of when the query was run.

{{ is_scheduled }}

True if the query was run as part of a schedule; false otherwise.

{{ query_run_url }}

URL containing the query token.

{{ report_run_url }}

URL containing the run token.

*Find a group’s token by going to Settings > Groups > click a group. The token is a 12-character string at the end of the URL.

Examples

Assign scheduled queries to a different group in Redshift
{% if is_scheduled == true %}
SET query_group TO scheduled_queries;
{% endif %}
Set a default schema
SET SEARCH_PATH TO 'webapp';
Add comments with run date, type, and running user

This example is illustrated with a multi-statement query to show how headers and footers interact.

-- Run by {{ query_runner_email }}
-- Run at {{ run_at }}
{% if is_scheduled == true %}
-- Scheduled run
{% else %}
-- Manual run
{% endif %}

Query in SQL Editor (Analyst Studio):

SELECT 1;
SELECT 2

Renders to:

-- Executed by hi@modeanalytics.com
-- Query executed at 1591031089
-- Manual run
SELECT 1;
SELECT 2

In Postgres, we’ll see two distinct statement executions. The header appears on the first statement only.

-- Executed by hi@modeanalytics.com
-- Query executed at 1591031089
-- Manual run
SELECT 1

The footers only appears on the second statement (unless per-statement footers are enabled).

SELECT 2
-- {"user":"@jane_doe","email":"hi@modeanalytics.com","url":"https://modeanalytics.com/modeqa/reports/1820851ba5a0/runs/a85b6da6912d/queries/d7f43f015d70","scheduled":false}

Query footers

Analyst Studio always appends a non-customizable SQL comment annotation at the end of a query. By default, this annotation is made per query. For a multi-statement query (see example), the footer is only appended to the second statement by default. Analyst Studio is able to enable per-statement footers if the Workspace uses multi-statement queries.

Query footers are not visible in Analyst Studio’s SQL Editor to the user while editing a query, and query footers do not display in Analyst Studio’s UI as a part of the query syntax for a past query run. This annotation does appear as part of the syntax executed by the database.

The annotation always includes:

  • user: query runner’s username.

  • email: email address.

  • url: complete query run url.

  • scheduled: true/false.

Examples

In Analyst Studio:

SELECT 1;
SELECT 2

In Snowflake (two distinct statement executions; without per-statement footers, only the second statement show the footer comment):

SELECT 1
SELECT 2
-- {"user":"@jane_doe","email":"hi@modeanalytics.com","url":"https://modeanalytics.com/modeqa/reports/1820851ba5a0/runs/a85b6da6912d/queries/d7f43f015d70","scheduled":false}

In Snowflake (two distinct statement executions; with per-statement footers, both statements show the footer comment):

SELECT 1
-- {"user":"@jane_doe","email":"hi@modeanalytics.com","url":"https://modeanalytics.com/modeqa/reports/1820851ba5a0/runs/a85b6da6912d/queries/d7f43f015d70","scheduled":false}
SELECT 2
-- {"user":"@jane_doe","email":"hi@modeanalytics.com"","url":"https://modeanalytics.com/modeqa/reports/1820851ba5a0/runs/a85b6da6912d/queries/d7f43f015d70","scheduled":false}
Was this page helpful?
×