How to Query on Airbook

Querying across DBs, connectors and Local Dataframes.

1. Querying Connectors

Let's say we are querying HubSpot & Amplitude and the unique identifier to do a JOIN is over email.

On Hubspot, the property is contact.property_email. On Amplitude it is event.user_id

The query below shows how you can do the join.

NOTE: Only use the table_name.column_name while doing the JOIN.

Steps:

  1. Create a SQL Block using the '/ command ' menu

  2. Select the datasource you want to JOIN on from the select datasource option (e.g hubspot.contact)

  3. Write the JOIN query using the correct table names of the connectors you wish to join (e.g hubspot.contact, amplitude.event)

2. Querying within DBs

Let's say we are querying 2 different tables with different schemas on PostgreSQL

Where Auth.users and public.users_data are two tables with a unique identifier over email.

The query below shows how you can do the join.

Steps:

  1. Create a SQL Block using the '/ command ' menu

  2. Select the table within your Database you want to JOIN on from the select datasource option (e.g auth.users in PostgreSQL)

  3. Write the JOIN query using the correct table names of the connectors you wish to join (e.g auth.users, public.users_data are two different tables in PostgreSQL)

3. Querying two Local Dataframes

Every SQL Block created by you on Airbook is marked as a Local Dataframe which you can reference in subsequent SQL blocks to join data across various sources.

Let's say we are querying the above 2 local SQL blocks on the Airbook.

The above two blocks are named querying_connectors & querying_dbs where we are joining on email as defined above.

The query below shows how you can do the join.

Steps:

  1. Create a SQL Block using the '/ command ' menu

  2. Select 'Local Dataframes' from the drop-down options (this is applicable whenever you want to join subqueries you've already created on Airbook)

  3. Write the JOIN query using the correct table names of the connectors you wish to join (e.g querying_connectors & querying_dbs are two different local dataframes created on Airbook)

4. Querying across connectors & DBs

Let's say we want to JOIN data from connectors & DBs

On Airbook, this works by creating intermediate SQL Blocks for both and then joining the Local Dataframes on the subsequent query you create.

Steps:

  1. Create your subqueries using the SQL Blocks and give them a name to reference later.

  2. Select 'Local Dataframes' from the select datasource dropdown menu.

  3. Write your SQL to join the previously created subqueries referencing the name you gave them.

Last updated