Getting Started with the Warehouse

The data warehouse is a read-only SQL database that tracks assay data, biological entities, inventory data, notebook entries, and other records stored in Benchling. The warehouse centralizes the entire organization’s research output and makes it possible to run queries that would historically require parsing multiple data sources, such as “find all batches that had an OD > 1000”.

The warehouse is well-suited to complex analytics, where the expressiveness of SQL matters. It can be securely connected to third-party analytics tools such as Tableau and Spotfire to create charts and dashboards backed by Benchling data. Configurable permissions ensure appropriate data access control.

All the data the warehouse stores, and the tables it is stored in, are outlined in Warehouse Tables.

How it works

The warehouse ingests both user-generated data about entities from the registry and assay results from networked instruments. To enable this aggregation, it works as follows:

• Researchers register new samples in the registry, specifying properties about the sample. A T cell receptor, for example, may capture information about the Alpha chain, Beta chain, and Specificity.
• They initiate a new run, specifying the assay to be run, the sample IDs, and parameters such as the adapter used.
• Results from the assay are uploaded as structured data and blobs to the warehouse.
• Third-party analysis and visualization tools connect to the warehouse to consume the data.

Obtaining credentials

Warehouse credentials are obtained from the Account Settings page in Benchling. Generate new credentials by clicking “Create credentials” under the Warehouse credentials section. If you don’t see this option, contact your Benchling Customer Success Manager to enable access on your account.

Warehouse configuration

The Benchling Data Warehouse is a hosted PostgreSQL solution. Any client, tool, or system that can connect to a PostgreSQL server can connect to your warehouse. Connections must be made with SSL/TLS; plaintext (unencrypted) connections are not supported.

Configuring PostgreSQL clients for security

The instructions here set the per-user default, and work with Python’s psycopg2 and the JDBC PostgreSQL driver in addition to psql. If they don’t work for you, or you need to connect to many PostgreSQL servers, refer to the official psql and libpq SSL documentation.

Choosing the correct certificate bundle

The following instructions set up your warehouse connection to trust the global AWS RDS certificate bundle. If you prefer, you can use one of the more specific RDS Root CA alternatives. These certificates rotate every few years, so they may need an update if you start getting a connection error.

On macOS and Linux

Set up the ~/.postgresql/root.crt file to hold the RDS certificate bundle. Open a terminal and run the following (this assumes you’re using bash as your shell and don’t already have a root.crt file):

# Download the Aurora RDS root certificate bundle to the default location. See
# https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.SSL.html
# for official bundle URLs.
curl https://truststore.pki.rds.amazonaws.com/global/global-bundle.pem \
  --create-dirs -o ~/.postgresql/root.crt
# Require root CA verification by default, and make the setting apply to new
# shells. Use .zshrc for zsh.
export PGSSLMODE=verify-ca
echo 'export PGSSLMODE=verify-ca' >> ~/.bashrc

On Windows

Set up the %APPDATA%\postgresql\root.crt file to hold the RDS certificate bundle.

• Download the global RDS certificate bundle to your Downloads folder and open that folder. You should see a new file named, for example, global-bundle.pem.
• Open a second File Explorer window (Ctrl+N, or File > New Window) and type %APPDATA% in its address bar.
• If you see a folder called postgresql, open it. If not, create a new folder named exactly that and open it. If you can’t see or create this folder, contact your IT team.
• Drag the downloaded global-bundle.pem from Downloads into the postgresql folder.
• Rename global-bundle.pem to root.crt to set it as the default (recommended). If root.crt already exists, append global-bundle.pem to it instead.

We also highly recommend setting the PGSSLMODE environment variable to verify-ca (Win+X > System > Advanced System Settings > Advanced tab > Environment Variables). If you are a Windows IT administrator with Group Policy access, we highly recommend setting this variable and PGSSLROOTCERT to the location of a shared CA certificate file for all users.

Advanced configuration and considerations

Secure connections must set at least the verify-ca SSL mode. Use either the PGSSLMODE environment variable or the equivalent sslmode parameter. Without it, a network attacker could spoof the identity of the warehouse PostgreSQL server with a fake certificate and steal everything you have access to.

To set an alternative location for the root CA certificate, set the PGSSLROOTCERT environment variable or the equivalent sslrootcert parameter to the path of the file. Note that ~/.postgresql/root.crt (and its Windows equivalent) can contain multiple certificates if you need to connect to and trust multiple PostgreSQL servers; just append the additional PEM-formatted certificates to the file.

The verify-full mode is supported for extra security, but you may need to adjust your connection hostname to “pin” it to the actual RDS Aurora PostgreSQL instance. To find the real instance name behind your friendly name, for example postgres-warehouse.examplename.benchling.com, use DNS tools or a site like nslookup.io to find the CNAME for the alias Benchling provided. Be aware that Benchling may need to migrate your data to meet certain performance guarantees, so this pinned hostname may change. AWS’s SSL/TLS configuration advice for Aurora PostgreSQL has more detail.

Connecting with psql

To connect to your warehouse, run the following, replacing yourusername and yourdeployname with your individual settings:

psql 'postgresql://u$yourusername@postgres-warehouse.yourdeployname.benchling.com:5432/warehouse?sslmode=verify-ca'
# Or, equivalently, in multiple lines. (psql has no command-line option for
# sslmode, so it goes in the environment.)
PGSSLMODE=verify-ca psql \
  -U 'u$yourusername' \
  -h postgres-warehouse.yourdeployname.benchling.com \
  -d warehouse

This prompts for your password. Be sure to use single quotes so the $ isn’t interpolated as a shell variable.

Connecting using JDBC

The connection string for psql also works for Java JDBC connections:

postgresql://u$yourusername@postgres-warehouse.yourdeployname.benchling.com:5432/warehouse?sslmode=verify-ca

Replace yourusername and yourdeployname with your individual settings.

The PostgreSQL JDBC connector SslMode documentation has more on the SSL/TLS settings. You will need to set up the correct RDS root certificates, just as with the other tools above, possibly using Java’s keytool.

Connect using a third-party tool or SaaS

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

• Host name: postgres-warehouse.yourdeployname.benchling.com, replacing yourdeployname with your enterprise-specific domain (the part before benchling.com in your enterprise’s URL)
• Port: 5432 (the default)
• Database: warehouse
• Username: the username you generated
• Password: the password you generated
• SSL or TLS mode: enabled. This is very important for your data security
• Root Certificate Authority / trusted certificate: download and upload the global RDS certificate bundle

Where possible, use the verify-ca SSL/TLS connection mode; it might be called “check server certificate”. If the tool also asks for an SSL/TLS client certificate, you can generate your own.

Warehouse limits and best practices

For integrations that connect directly to the data warehouse, Benchling has established limits around concurrent connections and transaction duration. These limits ensure that updates can sync in a timely manner and that the warehouse is able to reliably serve queries.

They include limits on the number of concurrent connections per user and per tenant, as well as guidelines around polling frequency and transaction duration. The warehouse limits and best practices section of the Limits page has the specifics.

Example

Runs store parameters about the assay that will be performed, such as the instrument ID. Results capture results generated by the assay and associated with the samples, such as the cell count.

Scenario: capturing results from a flow cytometry run

Researchers configure schemas for the flow cytometer assay in the Benchling UI. They set up fields and specify each data type, such as string or float. Fields can be links to blobs, which handle raw data and images.

FlowCytometryRun: Run
  containerId         container_link
  instrument          text
  rawData             blob_link

FlowGatingResult: Result
  flowRun             assay_run_link
  CD3+                float
  CD4+                float
  parentResult        assay_result_link

They then initiate the assay on the flow cytometer and upload parameters about the run, such as the instrument ID, to Benchling. An example run:

POST /blobs
Response:
{"blobId": ["65da6215-a889-49d3-a6da-b5cc0ac60d75"]}
POST /blobs/65da6215-a889-49d3-a6da-b5cc0ac60d75/parts
POST /blobs/65da6215-a889-49d3-a6da-b5cc0ac60d75:complete-upload
POST /assay-runs
Parameters:
[{
  "schema": "FlowCytometryRun",
  "fields": {
    "instrument": "My Instrument",
    "rawData": "65da6215-a889-49d3-a6da-b5cc0ac60d75"
  }
}]
Response:
{"assayRuns": ["9c6da62a-0a9e-4b88-b057-1adabfd31e2b"]}

After the run completes, a script on the instrument uploads results to Benchling, specifying the sample and container they are associated with along with results such as the CD3+ values. An example result:

POST /assay-results
Parameters:
{"assayResults": [
  {
    "schemaId": "assaysch_123456",
    "fields": {
      "flowRun": "9c6da62a-0a9e-4b88-b057-1adabfd31e2b",
      "CD3+": 0.4,
      "CD4+": 0.5
    }
  }
]}
Response:
{"assayResults": ["77af3205-65af-457f-87f5-75462b85075a"]}

The run is attached directly to an ELN entry in Benchling:

When researchers want to analyze results across multiple runs, they query the warehouse using third-party analytics tools or SQL directly:

-- Get all batches with CD3plus > 0.5
SELECT batch.id FROM batch
JOIN container ON container.batch_id = batch.id
JOIN flow_cytometry_run ON flow_cytometry_run.container_id = container.id
JOIN flow_gating_result
  ON flow_gating_result.flow_run = flow_cytometry_run.id
WHERE flow_gating_result.CD3plus > 0.5
  AND flow_gating_result.created_at > '2017-01-01';