# SQL Transformations ## Introduction SQL Transformations give you the full power of ANSI SQL to transform your events. Any transformation you can express with SQL, you can perform with SQL transformations. When you write a SQL transformation you will have access to an `incoming_event` table. This table has a number of columns that correspond to the built-in data Freshpaint collects and you will have access to an additional JSON column called `props` where you will have access to any custom properties. The full schema of the `incoming_event` table [is described here](#schema). {% hint style="warning" %} SQL Transformations are part of the Freshpaint SQL add-on. Contact to request access. {% endhint %} {% hint style="warning" %} SQL Transformations are only available when the destination the event is being sent to is in server-side mode. To make the most use of SQL Transformations, we recommend configuring your destinations in server-side mode (the out of box default for Freshpaint) {% endhint %} ## Behavior When you write a SQL Transformation you are writing a SQL query against the `incoming_event` table. Your query should return either a single row or no rows. Any columns returned by your query will be attached to the event as custom properties. If your query returns zero rows, the event will filtered out and not sent to any destinations. As an example, the query: ```sql SELECT 10 AS example_prop FROM incoming_event; ``` Will create a new property `example_prop` with the value `10`. If you return `NULL` for the value of a property, that property will instead be deleted. There are two reserved column names that have special behavior when you return them: * `event_name` - When you return this from a query, the name of the event will become the provided value. * `user_id` - When you return this from a query, the user\_id of the event will become the provided value. ## Examples ### Prepend a string to your events This transformation will prepend all events with the prefix `SQL:` . If the incoming event name is `Login`, the transformed event will have the name `SQL: Login`. ```sql SELECT CONCAT('SQL: ', event_name) AS event_name FROM incoming_event; ``` ### Run a custom formula This example will double the value of the custom property `a`: ```sql SELECT 2 * JSON_EXTRACT_DOUBLE(props, 'a') AS a FROM incoming_event; ``` ### Perform a regular expression match This example extracts a URL parameter from the url of the event using regular expressions: ```sql SELECT REGEXP_REPLACE(url, '^.*param=(\\w+).*$', '\\1') AS param FROM incoming_event; ``` ### Add a constant property This example creates a new property called `new_property` an always sets it to the value `10`: ```sql SELECT 10 AS new_property FROM incoming_event; ``` ### Delete a property This example deletes the property `sensitive_identifier` from the event. This is done by setting the value of the property to `NULL`. ```sql SELECT NULL AS sensitive_identifier FROM incoming_event; ``` ### Filter events based on a regular expression This example filters out all events where the email is a gmail address: ```sql SELECT event_name FROM incoming_event WHERE JSON_EXTRACT_STRING(props, 'email') NOT RLIKE 'gmail.com$'; ``` ## Schema The `incoming_event` table has the following schema: | Property Name | Description | | -------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | event\_name | The name of the event definition matched by the event. | | time | The Unix time in seconds the event occurred at. | | pageview\_id |

The id of the pageview the event belongs to. All events performed in the same pageview will have the same pageview\_id.

| | session\_id | The id of the session the event belongs to. A session is a group of actions perform in the same time frame. A session ends after 30 minutes of inactivity. All events performed in the same session will have the same `session_id`. | | browser | The name of the web browser. | | browser\_version | The version of the web browser. | | device\_id | A Freshpaint generated unique identifier for the device the event was sent from. This is sometimes referred to as the `anonymous_id`. | | device\_type | Either "Desktop" or "Mobile" depending on the device type. | | host | The domain of the page the event occurred on. | | initial\_referrer | The full URL of the page that first referred the user to your site. | | initial\_referring\_domain | The domain of the page that first referred the user to your site. | | operating\_system | The name of the operating system the user was using. | | path | The URL path of the page the event was performed on. | | referrer | The URL of the page the user was on before the page they performed the event on. | | referring\_domain | The domain of the page the user was on before the page they perfomed the event on. It may be your own domain. | | screen\_height | The height of the user's screen. | | screen\_width | The width of the user's screen. | | target\_text | The text of the element that was clicked on. | | url | The full URL of the page the event was performed on. | | user\_id | The identity associated with the user through Identify. | | utm\_campaign | The most recent value of the utm\_campaign UTM property. | | utm\_content | The most recent value of the utm\_content UTM property. | | utm\_medium | The most recent value of the utm\_medium UTM property. | | utm\_source | The most recent value of the utm\_source UTM property. | | utm\_term | The most recent value of the utm\_term UTM property. |