Syncing Events
Configuring which data to import from y
After you've connected to your warehouse, the next step is to syncing Events is to configure the query that Vitally will run against your warehouse on each sync.
For syncing Events, the query must select or alias all three of the following column names:
event: the name or type of eventtimestamp: the time the event was generatedmessage_id: a unique ID for the event
And at least one of:
user_id: the ID of the user that 'owns' the eventaccount_id: the ID of the account that 'owns' the event
In order to to incrementally query your data each time, only fetching rows that have been updated since our last sync, you can add a clause to your query with the variable $LAST_SYNC_TIMESTAMP. We'll fill that in with the timestamp of the last sync each time we query the warehouse.
Incremental syncs is required for syncing Events! It will ensure we only read the data that needs an update from your warehouse and will also ensure your recurring data sync completes as soon as possible.
Incremental syncs should compare the $LAST_SYNC_TIMESTAMP with a timestamp that reflects when the data was synced to the data warehouse. This is particularly important when data is synced to the data warehouse in batches.
SELECT
'pageView' AS event,
user_id AS user_id,
created_at AS timestamp,
id AS message_id,
page,
application
FROM page_views
WHERE created_at > $LAST_SYNC_TIMESTAMP
Events can be configured to sync from multiple queries to different tables. In order to add a new source at the bottom of the list of queries:
In order to pull in additional metadata associated with the event, simply include those columns in your select and they will be added as properties.
For the above example query, the event properties pulled in will be:
{ "page": "...", "application": "..." }
What if my properties are all already on one column?
If your event properties are already contained in a single nested or JSON column and you've selected it:
SELECT
event_name AS event,
user_id AS user_id,
created_at AS timestamp,
id AS message_id,
meta_data
FROM all_events
WHERE created_at > $LAST_SYNC_TIMESTAMP
And the responses looks like:
{
"event": "pageView",
"user_id": "2dc30ead-2e64-4d87-8395-aad4be299698",
"timestamp": "2019-11-15T16:08:59.915Z",
"message_id": "7ca60dbd-0ad0-4ef6-b049-aac3fb5e0116",
"meta_data": {
"page": "home-page",
"duration_ms": 3253
},
}
Then you will want to set the optional "property field" to
meta_data in order to track those values.
If you have other features to sync continue configuring those.
Last modified 1yr ago