Overview

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

All organizations are also automatically connected to the Mode Public Warehouse.

Private data connections

Connect a new database

To connect your database to your Mode organization:

  1. Navigate to your Mode homepage and sign in.
  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 Mode connects to private databases.

Modify or disconnect a connected database

IMPORTANT
Deleting a data connection in Mode will also delete all reports built on top of that connection. Deleting a connection will not, however, have any effect on the database itself. You can modify the settings and credentials for a data connection only if you’re an admin or if you were the member who connected it to the organization.

  1. Navigate to the Mode home page and sign in.
  2. Click on your name in the upper left, and click Organization Settings
  3. In the Data section, click Connections.
  4. All the database connections for your organization will be listed on the right.

If you are an organization admin or the creator of a data connection, you can modify or disconnect it. Click on the connection and then click Settings to adjust the database credentials and configuration. Click Delete to disconnect this database from Mode.

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 Mode, your organization will access each data connection through a single user, which we recommend you or your database admin create expressly for use with Mode.

Members of your organization who use this data connection will be able to execute queries according to the permissions granted to this user in your database. You can connect Mode to the same database multiple times via different database users, with different levels of permissions.

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

Limit access to a data connection in Mode

Admins in Mode Business organizations can also limit access to a data connection to specific users or user groups directly in Mode. If a user does not have access to a connected database, that user will not be able to:

  • Query the database or create, clone, or edit any reports that query that database.
  • Write or view any of that data connection’s Definitions.

To limit access in Mode to a connected database:

  1. Navigate to the Mode home page and sign in.
  2. Click on your name in the upper left, and click Organization Settings
  3. In the Data section, click Connections.
  4. Click on the data connection you want to limit.
  5. Click the Permissions tab.
  6. Click Limit access.
  7. In the pop-up box, click Add next to each user or user group that you want to grant access to this connection.
  8. If you change your mind and want to remove that member, hover over Added and click Remove.
  9. Click Finished when you’re done.

How database permissions and Mode permissions interact

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

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

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 Mode, 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 (e.g. Redshift), but you can also name it based on a description of the access level or user role.

  4. Mode Business organizations can also specify which Mode 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

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

Modify a data connection’s query header

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

  1. Navigate to the Mode home page and sign in.
  2. Click on your name in the upper left, and click Organization Settings
  3. In the Data section, click Connections.
  4. Click on the data connection you would like to modify.
  5. Click Edit Query Header.
  6. In the space provided, add or modify any valid SQL code (including comments and valid Liquid template code) that you want prepended to all queries run against this connection.
  7. When you are finished, click Save.

Variables

Each time a query is executed, Mode 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:

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

*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 different query groups 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 the run date, type, and running user

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

Renders to:

-- Run by ann@octangroup.com
-- Run at 1501113638
-- Manual run

Mode Public Warehouse

Mode hosts a public PostgreSQL data warehouse so you can share data and analysis with the entire Mode community. The Mode Public Warehouse is a great tool that you can use to:

  • Learn SQL by completing Mode’s SQL school.
  • Share analysis with or learn from anyone in the Mode community.
  • Experiment with all of Mode’s features.

Anyone with a Mode account has access to the Mode Public Warehouse and can build reports on top of the data inside it.

Uploading data

IMPORTANT: All data uploaded into the Mode Public Warehouse is publicly accessible by anyone on the internet, so take care not to upload anything sensitive or confidential!

  1. Sign in to Mode. If you don’t have an account yet, create a free Mode user account here.
  2. Locate your name in the upper left corner of the window and ensure it says Mode Community above your name. (If not, click on your name, click Switch, scroll to the bottom of the pop-up and click the Switch button under Mode Community.)
  3. Click on your name in the upper left corner of the window and then click Contribute Public Data.
  4. Click Choose and Upload.
  5. Select a CSV (up to 500 MB) containing the data you’d like to upload.
  6. After the data uploads, you will have the opportunity to customize the name of the table and how the columns are stored. When querying the table, you will be required to type the namespace first. In the example below, the user’s account name is “tutorial,” so the uploaded table name will include the “tutorial.” prefix:

Adding Public Data

Tables names in the Mode Public Warehouse must:

  • Be 4-63 characters in length
  • Start with a lowercase letter
  • End with a number or letter
  • Not contain spaces or special characters (use underscores)

Next, you’ll set the column names and types. Columns follow the same naming rules as tables. If you are unsure about the data type for any particular column or would like a general overview of data types, check out the SQL School lesson on data types.

When you are finished, check the box next to “I understand this table will be public to all Mode users.” and then click Finish Import at the bottom of the Customize Columns screen. You can now query this data in Mode by selecting Mode Public Warehouse as the database.

Deleting data

To remove a table you’ve uploaded to Mode’s Public Warehouse:

  1. Navigate to your profile page, which is located at https://modeanalytics.com/{{mode_username}}.
  2. Click on the Public Table table-large-bg Created with Sketch. icon.
  3. Click on the name of the table you want to delete.
  4. Click the gear settings-mini-hover Created with Sketch. icon next to the green Query button and choose Delete table.

To find your Mode username:

  1. Click on your name in the upper left corner and click Switch.
  2. Scroll to the bottom of the list and find Mode Community. Click the Switch button.
  3. Your username is located in the URL of this page: https://modeanalytics.com/home/{{mode_username}}/search

Last updated May 17, 2018