We welcome Harry Glaser, CEO of our SQL partner Periscope, to the Segment blog! Harry’s talking cross-database joins on the heels of our Amazon Redshiftlaunch. If you want to analyze behavioral data across platforms, and Excel won’t cut it, here are some tips to level-up your analytics game.
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 more-advanced 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 to SQL 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.
You need to connect your transactional, web, and mobile in one table. To start, Let’s count our iPhone MAUs (monthy active users), with Segment’s SQL schema as an example.
SELECT DATE_TRUNC('month', created_at), COUNT(1) FROM iphone_production.session_started GROUP BY 1
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
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 GROUP BY 1, 2
Note that we now need to fully qualify the tables in the
JOIN clauses with their database names:
And our hard work pays off! Here we can see our iPhone MAUs sliced by payment plan:
How It Works
Cross-database joins, and in the case of Periscope – our 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 it 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 GROUP BY 1, 2
In this example, your
iphone_production schema is translated to the
db_1234_iphone_production schema in Periscope’s data cache. And
public schema is translated to the
db_1235_public schema. The rest of the query remains the same!
We hope you enjoyed this lesson in cross-data base joins. If you have any thoughts or questions, reach out to us on Twitter @PeriscopeData or hit up email@example.com. We’d love to discuss.