This article aims at explaining how Bridge looks in practice and how to interact with the database. You should be able to get started with some first queries against your data.

Authentication

After we set up your Bridge instance, we will provide you with the following information:

  • Hostname: The URL of the database server, e.g. <organisation>.dbs.i.onmercury.io Port: The port used by the database (usually 5432 )
  • Database name: the name of the database that contains your Bridge objects, e.g. metrics_<organisation>
  • Username: database user
  • Password: password for database user (is provided to you separately over a secure channel) 
  • Schema: The current database schema you can work with, e.g. mercury_v2

The connection to the database is always SSL encrypted, which is explicit when starting a connection. It is likely, that you will need to have the host name and port safe listed in your companies' firewall.

Bridge is a read-only database. You cannot create database objects like tables or views, nor can you update or change the available data in any way.

Bridge data model

The Bridge database contains versioned schemas containing the same data in slightly altered structures. This approach is necessary, as MMT Mercury is constantly being developed further, which can imply changes to the underlying data model. Every time the data model changes on a structural level, a new schema version containing the changes is created.

Every schema contains a view for every MMT Mercury entity:

agency_proposal_runtimes
agency_proposals
audience_segments
campaigns
categories
channels
creative_performance_metrics
media
plan_items
plan_items_audience_segments
plan_items_by_month
publishers
runtimes

The above displayed views are an example for schema mercury_v2. The views in schemas of following versions may be different.

For a more in depth look at the Bridge data model, consult the data model documentation.

Connecting to MMT Bridge

We specifically chose to provide Bridge as a PostgreSQL database, as it's an industry standard that is compatible with nearly all BI tools and programming languages. The following examples are usually easily transferable to other systems you may decide to use.

psql

The simplest way to connect to your Bridge instance is to use a shell (e.g. bash or powershell). The command line programm psql allows you to connect to your database directly from shell.

psql "postgresql://<username>:<password>@<hostname>/<dbname>?ssl=true&sslmode=require"

List schemas

metrics=# \dn
          List of schemas
      Name       | Owner
-----------------+-------------------------
 extensions      | mercury
 mercury         | mercury_application
 mercury_source  | mercury_application
 mercury_v1      | mercury_application
 mercury_v2      | mercury_application
 mercury_v3      | mercury_application
 metric_helpers  | postgres
 public          | postgres
 user_management | postgres

(9 rows)

List views in schema

metrics=# \dv mercury_v2.*
                          List of relations
  Schema    |             Name             | Type |        Owner
------------+------------------------------+------+---------------------
mercury_v2  | agency_proposal_runtimes     | view | mercury_application
mercury_v2  | agency_proposals             | view | mercury_application
mercury_v2  | audience_segments            | view | mercury_application
mercury_v2  | campaigns                    | view | mercury_application
mercury_v2  | categories                   | view | mercury_application
mercury_v2  | channels                     | view | mercury_application
mercury_v2  | creative_performance_metrics | view | mercury_application
mercury_v2  | media                        | view | mercury_application
mercury_v2  | plan_items                   | view | mercury_application
mercury_v2  | plan_items_audience_segments | view | mercury_application
mercury_v2  | plan_items_by_month          | view | mercury_application 
mercury_v2  | publishers                   | view | mercury_application
mercury_v2  | runtimes                     | view | mercury_application

(13 rows)

Query a table

SELECT * FROM mercury_v2.plan_items;

Using psql allows for quick easy access to your data and to get a first overview, but is not suitable for database development and more complex analysis.

DBeaver SQL client

An SQL client like DBeaver has an optimized user interface and allows for easier scripting of

database queries.

Create connection

In the toolbar, click on Database > New database connection , look for PostgreSQL, select next and fill in the connection details:


Then click on OK and you should see your connection appear in the left most panel. You can then unfold the connection tree to look at the Bridge database objects:

You can then create SQL scripts to develop more complex queries and save them to disk. If your use case is to run complex analytics and machine learning methods on this data, you will need to access the Bridge database using a data science programming language.

Data science programming languages

The most popular data science programming languages currently are R and python. The following examples show a very basic way of accessing Bridge data using these programming languages. Other methods exist and can be more efficient for specific use cases.

Accessing Bridge data with other programming languages usually works similarly. In our examples, we use libraries ( RPostgres, psycopg2 ) to connect to the PostgreSQL database. Most programming languages will provide such libraries.

The examples show the connection details, including the credentials as hard coded variables. We strongly advise against this, as this represents a security risk. We only do this here to keep the examples as simple as possible.
R

To access the Bridge PostgreSQL database, you can use the package  RPostgres , which is a  DBI  interface.

library(RPostgres)
pg_host <- '<hostname>'
pg_port <- 5432
pg_db <- '<database-name>'
pg_user <- '<user_name>'
pg_password <- '<password>'
con <- dbConnect(
  RPostgres::Postgres(),
  host = pg_host,
  dbname = pg_db,
  port = pg_port,
  user = pg_user,
  password = pg_password,
  sslmode = 'require'
)
# Query schema names in database
dbGetQuery(
  con,
  "SELECT DISTINCT table_schema FROM information_schema.tables ORDER BY
table_schema;"
)
# Query table names in schema `mercury_v2`
dbGetQuery(
  con,
  "SELECT DISTINCT table_schema, table_name FROM information_schema.tables WHERE
table_schema = 'mercury_v2' ORDER BY table_schema, table_name;"
)
# Write 100 first rows of the `plan_items` table to a dataframe
query <- 'SELECT * FROM mercury_v2.plan_items LIMIT 100;'
plan_items <- dbGetQuery(
con,
query
)

python

For python, you can use  psycopg2  and  sqlalchemy  to access the Bridge data and directly transfer query results to DataFrames.

import psycopg2
import pandas as pd
from sqlalchemy import create_engine

pg_host = '<hostname>'
pg_port = 5432
pg_db = '<database-name>'
pg_user = '<user_name>'
pg_password = '<password>'

con_str = 'postgresql+psycopg2://{0}:{1}@{2}:{3}/{4}'.format(
  pg_user,
  pg_password,
  pg_host,
pg_port,
pg_db
)

engine = create_engine(con_str);
con = engine.connect()

# Query schema names in database
pd.read_sql(
  'SELECT DISTINCT table_schema FROM information_schema.tables ORDER BY
table_schema;',
  con
)

# Query table names in schema `mercury_v2`
pd.read_sql(
  "SELECT DISTINCT table_schema, table_name FROM information_schema.tables WHERE
table_schema = 'mercury_v2' ORDER BY table_schema, table_name;",
  con
)

# Write 100 first rows of the `plan_items` table to a dataframe
query = 'SELECT * FROM mercury_v2.plan_items LIMIT 100;'
plan_items = pd.read_sql(
query,
con
)