SQL Query Enrichment

Snowplow documentation

SQL Query Enrichment¶

Summary¶

The SQL Query Enrichment lets you perform dimension widening on a Snowplow event via your own internal relational database.

Overview¶

If you have data points that you’d like to use to enrich your event data collected with Snowplow that live in a data base, this enrichment will help you to query for the fields you want to add.

Currently supported database types:

• MySQL, plus variants which speak MySQL (e.g. MariaDB, Amazon Aurora)

• PostgreSQL, plus variants which speak PostgreSQL

We don’t recommend to use this enrichment with analytical databases which support minimal (50-100) concurrent queries (e.g. Redshift).

To read more detail on this enrichnment and its configuration, look here.

For help with configuring this enrichment and getting it live on your pipeline please contact us at [e-mail].

Example¶

Below you can see an example configuration using imaginary PostgreSQL database with CRM data, used to widen Snowplow event with context containing information about users.

{
  "enabled": true,
  "parameters": {
    "inputs": [
      {
        "placeholder": 1,
        "pojo": {
          "field": "user_id"
        }
      },
      {
        "placeholder": 1,
        "json": {
          "field": "contexts",
          "schemaCriterion": "iglu:com.snowplowanalytics.snowplow\/client_session\/jsonschema\/1-*-*",
          "jsonPath": "$.userId"
        }
      },
      {
        "placeholder": 2,
        "pojo": {
          "field": "app_id"
        }
      }
    ],
    "database": {
      "postgresql": {
        "host": "rdms.intra.acme.com",
        "port": 5439,
        "sslMode": true,
        "username": "snowplow_enrich_ro",
        "password": "1asIkJed",
        "database": "crm"
      }
    },
    "query": {
      "sql": "SELECT username, email_address, date_of_birth FROM tbl_users WHERE user = ? AND application = ? LIMIT 1"
    },
    "output": {
      "expectedRows": "AT_MOST_ONE",
      "json": {
        "schema": "iglu:com.acme\/user\/jsonschema\/1-0-0",
        "describes": "ALL_ROWS",
        "propertyNames": "CAMEL_CASE"
      }
    },
    "cache": {
      "size": 3000,
      "ttl": 60
    }
  }
}