Access your data warehouse [Enterprise]

The Benchling Warehouse is a database solution that tracks and allows you to view assay data, registry entities, and sample data using SQL queries. For an overview of the warehouse, check out the Warehouse Overview or Warehouse Schemas ERD.

Note: This article does not cover the API, which can be used to write data to Benchling.  To access the API, please refer to Write Data via the API [Enterprise].

Configure your warehouse

Set up warehouse credentials

Note: You will need to contact your Benchling Customer Success Manager to access the Warehouse on your account and enable creation of Warehouse Credentials.

Warehouse credentials are used to access data uploaded to the Benchling warehouse using third party tools such as Spotfire, Tableau etc.

Warehouse credentials are obtained via the Account Settings page on Benchling. From the settings page, users generate new credentials by clicking on “Create credentials” under the Warehouse credentials section.  If you do not see this option, contact your Benchling Customer Success Manager to enable access on your account.

Note: Be sure to copy the password when generating credentials, since you will not be able to access it again.

Connect to the warehouse

Connect third party visualization tools to the Postgres warehouse. For each run/result, you'll see:

1. Raw tables (e.g. my_assay_result$raw): contain both reviewed and unreviewed data
2. View (e.g. my_assay_result): contains only valid, reviewed data

Generally, you'll want to query the view when unsure. Query the raw table if you want to see unreviewed data. Note that each run/result is uploaded to a project, and you'll only be able to see runs/results in projects you can access. See this
Getting Started doc for more information.

To connect to the warehouse, use the following parameters in your data visualization tool:

• Host Name: postgres-warehouse.*yourdeployname*.benchling.com.  Be sure to replace *yourdeployname* with your enterprise specific domain (that means the part before your enterprise's benchling.com URL)
• Port: 5432
• Database: warehouse
• username: the username you generated above
• password: the password you generated above

Using psql

psql -h postgres-warehouse.*yourdeployname*.benchling.com \
  -U 'u$benchlingsupport' \
  -p 5432 \
  warehouse

Note: replace the username and *yourdeployname* as needed.

Using a third-party visualization tool

Some tools may require you to explicitly enable SSL to connect

Reading data from the Warehouse

You will need permissions to view and edit the data you are trying to access
Permission to view runs and results is derived from the project in which they are placed. Project for a result will be specified through:

1. If manually entered, the permissions for the assay data is the same as the permissions of the entry in which it is recorded
2. If uploaded via a script, the project id can be specified at time of upload.
3. When a run is included for review in a Notebook entry, we move the runs and results to the project for the runs/results

FAQ

Error trying to connect to database 'warehouse'

1) Your client or tool may not be connecting using SSL.  To fix this, you may try to:

• Append the following string to the end of the query: ?ssl=true&sslmode=require at the end of your query.
• For example, jdbc:postgresql://postgres-warehouse.*yourdeployname*.benchling.com:5432/warehouse?ssl=true&sslmode=require

2) Bash is interpreting the username as a variable.  To fix:

• Be sure to use psql -U 'u$benchlingsupport' instead of psql -U u$benchlingsupport (replace benchlingsupport with your user ID)

3) Benchling's warehouse requires your Postgres client to connect using SSL by default.  Please ensure that the “Enable SSL” is selected if presented the option when trying to connect.