Databases

Cross-Database Joins

In celebration of our friends at Segment launching Segment SQL last week, we’re writing on a topic of particular interest to our mutual customers: Cross-Database Joins.

All The King’s Databases

It began with the best of intentions: You launched your first web app for your customers, backed by a database full of transactional data to analyze. In time you added a read replica, and replaced Excel with an awesome visualization tool to go with it.

Now you’re launching your first mobile app. You want SQL access to the underlying data store, but building a server to receive pings is much too difficult. So you make great use of a fabulous event-tracking analytics solution.

But now your data is in two places. What if you want to know whether your iOS users are big spenders? You’d need to slice monthly iOS users in your mobile app database by payment plan information in your web app database.

Luckily, there is a solution: cross-database joins.

Cross-Database Joins

Let’s start by counting our iPhone MAUs, as measured on the Segment database:

select date_trunc('month', created_at), count(1)

from iphone_production.session_started

groupby1

We’re counting a user as active in a given month if they’ve started a session in that month. This query gives us a graph like this:

Now we just need to bring our payment plans into the chart. This is where the magic happens. We’ll join in the users table on our web database, and slice the query by users.payment_plan:

select

date_trunc('month', created_at),

payment_plan,

count(1)

from segment.iphone_production.session_started

join web_prod.users

on users.id = session_started.user_id

groupby1, 2

Note that we now need to fully qualify the tables in the from and join clauses with their database names: segment and web_prod.

And our hard work pays off! Here we can see our iPhone MAUs sliced by payment plan:

How It Works

Cross-database joins, as well as Periscope’s query speeds, are enabled by our Postgres-based data cache. Each customer’s data is stored in the same database, with one schema per (database, schema) pair.

This architecture allows us to run exactly the query you wrote, with some simple rewrites to make the query valid. Here’s the rewritten query:

select

date_trunc('month', created_at),

payment_plan,

count(1)

from db_1234_iphone_production.session_started

join db_1235_public.users

on users.id = session_started.user_id

groupby1, 2

In this example, your segment database’s iphone_production schema is translated to the db_1234_iphone_production schema in Periscope’s data cache. And web_prod’s (unspecified) public schema is translated to the db_1235_public schema. The rest of the query remains the same!

Start Exploring

If you want simple event tracking that gives you SQL access to the underlying data, Segment SQL is your answer. If you want to take it a step further and join that data to all your other databases, sign up for Periscope.