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:
- Raw tables (e.g. my_assay_result$raw): contain both reviewed and unreviewed data
- 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 \
warehouseNote: replace the username and *yourdeployname* as needed.
Using a third-party visualization tool
Some accounts may require SSL to be turned on.
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:
- If manually entered, the permissions for the assay data is the same as the permissions of the entry in which it is recorded
- If uploaded via a script, the project id can be specified at time of upload.
- 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'
- Your client may not be connecting using SSL To fix, 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) Machine might not be able to validate RDS’s certificate
4) Benchling's warehouse requires your Postgres client to enable SSL by default. Please ensure that the “Enable SSL” is selected if presented the option when trying to connect.