Query Modes

Hevo queries the Source to ingest data. Querying can differ depending on the type of Source. Read the following sections to know how Hevo performs queries in case of SaaS, and Relational database Sources.

Relational Database Sources

Hevo performs SQL queries on the Source database to get data. You can select different modes to query for the data to be ingested from relational databases such as Amazon Aurora MySQL, Amazon Redshift, SQL Server, MySQL, and PostgreSQL. Hevo allows you to choose the query mode for every object as shown in the image below:

Querying options are particularly useful where you do not want to or do not have the option to set up the database replication logs.

If you change the query mode:

• The ingestion job restarts from the beginning. For example, if you change the query mode from Unique Incrementing Append Only to Change Data Capture, the entire data is ingested again from the beginning.

• The re-ingested Events are considered to be historical data and are not billed.

Hevo supports the following query modes:

Full Load

In Full Load, Hevo ingests all the data from the Source table on each ingestion run and loads it to the Destination. Once the entire table is loaded, Hevo waits six hours to ingest data from it again.

This mode is used when the table has up to 5 Million rows. If your Source table contains more than 5 Million rows, you should consider using a different query mode.

Note: Changing the query mode for a Source table causes the ingestion job to restart from the beginning.

The following query is used to fetch the Full Load data:

SELECT * FROM table
LIMIT 5000001 OFFSET 0;

Note: From Release 2.19 onwards, the ingestion limit for Pipelines created with any variant of the PostgreSQL Source has been increased to 25 Million rows. You need to contact Hevo Support to change the default limit from 5 Million to 25 Million rows.

In log-based Pipelines, the query mode is used only to ingest the historical data. If any index or timestamp column is available for a table, that is used to fetch the data. Hence, the Full Load mode is not made available for selection even if you try to edit a table’s query mode post-Pipeline creation.

If change tracking is enabled for a table in the SQL Server Source, the Full Load mode is not shown.

Unique Incrementing Append Only

This mode is useful when the rows in the table are immutable and the data is only being appended to the table.

Unique Column Names: Hevo uses one or more auto-incrementing columns to keep track of the last ingested ID that it recorded. These columns should individually or together uniquely identify a record. While configuring objects, you can either select the auto-incrementing columns suggested by Hevo or define them yourself. You can choose a combination of the Hevo-suggested and custom columns from the drop-down, or specify them as a comma-separated list. The selected columns jointly act as a unique identifier.

Note: These columns should not contain any null values.

Hevo runs the respective queries at the configured frequency for the following scenarios:

• If only one auto-incrementing column is specified:

  SELECT * FROM table
      WHERE `id_column` > last_polled_id
  ORDER BY `id_column` ASC
  LIMIT 1000000;
  

  Example:

  Consider the following snapshot of a Source object with two records:

  

  Here, User ID is the unique incrementing column. Once these records are ingested, Hevo stores the highest value of User ID as the last_polled_id, which in this case is 2.

  

  Now, suppose record 1 is changed, and record 3 is added in the Source. When Hevo performs the SQL query, record 1 is ignored, record 3 is picked up and the value of last_polled_id is modified to 3.

  

  Note: If no new records are added to the Source object since the last fetch, then the UIAO query mode does not return a result.

• If two or more auto-incrementing columns are specified:

  SELECT * FROM table
      WHERE (`id_column1` > last_polled_id1) OR
            (`id_column1` = last_polled_id1 AND
             `id_column2` > last_polled_id2)
  ORDER BY `id_column1` ASC, `id_column2` ASC
  LIMIT 500;
  

  Example:

  Consider the following snapshot of a Source object with four records:

  

  Here, Dept ID and Employee ID are both incrementing columns. While these columns may contain duplicate values, together they uniquely identify a record. The OR condition defined in the query above ensures that the last record fetched has the highest value in both the columns. So, once these four records are ingested, Hevo stores the highest values of Dept ID and Employee ID as last_polled_id1 and last_polled_id2, which in this case is 2 and E02, respectively.

  

  Now, suppose the record corresponding to Dept ID 1 and Employee ID E01 is changed, and a new record with Dept ID 2 and Employee ID E03 is added in the Source. When Hevo performs the SQL query, it first checks whether there are any records with Dept ID > the last_polled_id1, which is 2. If it does not find any, it checks within all the records with Dept ID 2 to locate any with Employee ID > last_polled_id2, which is E02. So, while the modified record gets ignored, the new record is picked up because its value for the Employee ID column (E03) is higher than the last_polled_id2. The value of last_polled_id2 is now modified to E03.

  

  Note: While using the UIAO query mode, ensure that the incrementing columns you specify can identify all new records according to the conditions defined in the SQL query. In the above example, suppose a new record with Dept ID 1 and Employee ID E03 is added in the Source. It will not be ingested because the last_polled_id1 is set to 2. As a result, only records with Dept ID 2 or higher are considered for ingestion.

  If no new records are added to the Source object since the last fetch, then the UIAO query mode does not return a result.

Delta - Timestamp

This mode is similar to Unique Incrementing Append Only. However, instead of an incrementing ID, Hevo asks you for a Timestamp Column and queries your Source table based on it.

This mode is useful when:

• The table is appended with new records and a timestamp is maintained.

• The data in the table is updated and the updated timestamp is maintained.

The conditions mentioned above are required to ensure data integrity between your Source and Destination data.

Note: If your tables do not have a timestamp column, you can use Log-based ingestion mode to track updates to existing records.

You must specify the following when configuring this query mode in your Pipeline:

• Timestamp Column Name: Hevo uses this column to query the Source table and track the value of the updated_timestamp_column.

• Timestamp Column Delay: Hevo uses this value to accommodate any lag caused by long-running transactions and or out-of-order updates.

Hevo runs the following query to ingest the data from your Source at the configured frequency:

SELECT * FROM table
  WHERE `updated_timestamp_column` > last_polled_time AND
        `updated_timestamp_column` < now() - delay
ORDER BY `time_column` ASC
LIMIT 1000000;

In the query above, Hevo identifies the data to be ingested using the last_polled_time as the lower limit and the current time, given by the now() function, as the upper limit.

Hevo sets the value of last_polled_time as follows:

• In the first run of the Pipeline, Hevo sets the last_polled_time to the Unix Epoch, 1970-01-01 00:00:00.0 UTC.

• Once data is ingested, Hevo updates the last_polled_time to the latest timestamp from the records ingested in the previous batch.

Example:

Consider the following snapshot of a Source object with two records:

Here, Update TS is the updated_timestamp_column. Once these records are ingested, Hevo stores the latest timestamp of Update TS as the updated_timestamp_column value, which is Fri, 16 Apr 2021 06:10:51 GMT.

Now, suppose record 1 is changed and record 3 is added. When Hevo runs the SQL query, record 1 is picked up, record 3 is ignored, and the value of updated_timestamp_column is modified to Fri, 16 Apr 2021 07:10:51 GMT.

Change Data Capture

This mode is a mixture of both Unique Incrementing Append Only and Delta - Timestamp modes. The mode is useful for capturing both inserts and updates.

Auto-Incrementing Column: Hevo uses this column to keep a track of the last ingested ID it recorded.

Update Timestamp Column: Hevo uses this column to query the Source table and to keep track of updated_timestamp_column value.

Timestamp Column Delay: This configuration can be used when you want to replicate data with a delay which is useful when there are long-running transactions or it is a design requirement. Enter the delay (in milliseconds) you want to introduce to the query.

Hevo runs the following query at the configured frequency.

SELECT * FROM table
    WHERE (`updated_timestamp_column` < now() - delay AND
          ((`updated_timestamp_column` = last_polled_time AND
            `id_column` > last_polled_id) OR
            `updated_timestamp_column` > last_polled_time)) OR
            (`updated_timestamp_column` is NULL AND
             `id_column` > last_polled_null_id)
ORDER BY `updated_timestamp_column`, `id_column` ASC
LIMIT 500;

Example:

Consider the following snapshot of a Source object with two records:

Here, User Id and Update TS are the last_polled_id and updated_timestamp_column respectively. Once these records are ingested, Hevo stores the latest values of User Id and Update TS as the last_polled_id and updated_timestamp_column value, which in this case are 2 and Fri, 16 Apr 2021 06:10:51 GMT respectively.

Now, suppose record 1 is changed, and record 3 is added in the Source. When Hevo performs the SQL query, records 1 and 3 are picked up and the values of last_polled_id and updated_timestamp_column are modified to 3 and Fri, 16 Apr 2021 07:10:51 GMT respectively.

Change Tracking

This mode makes use of the Change Tracking feature provided by SQL Server. Change Tracking should be enabled for the database objects in order to use it. You may follow this guide to enable change tracking for the Source objects. The change retention period should be more than the replication frequency of your Hevo Pipeline to avoid any data loss.

Unlike other query modes, Change Tracking can also be enabled/disabled at the Table level.

Note: Use an SQL client tool to enable change tracking for the objects you want to track, and subsequently select Change Tracking in the Query Mode drop-down.

Hevo uses Change Tracking (CT) instead of Change Data Capture (CDC) for some Sources as CT requires lesser data to be managed and stored at the Source. Change Tracking replicates the historical data changes and Hevo pushes these changes to the Destination in near real-time, compared to CDC which maintains all the historical changes to the data at the Source.

SaaS Sources

To extract data from a SaaS Source, Hevo uses the respective Source’s API. Therefore, selection of query mode does not apply to a SaaS Source. The specifications of the Source API affect how data is queried for different objects in the Source.

For example, in case of certain objects, the Source API may allow ingestion of incremental data, that is, any new and updated Event. For other objects, it may allow Hevo to perform only a Full Load of the object, which means that the entire data in the object is replicated on each ingestion. The objects Videos, Channels, and Playlists in YouTube Analytics, and Audience in AdRoll are examples of Full Load objects.

For a few SaaS Sources, to reduce the Events quota consumption of Full Load objects, Hevo filters the data and loads only the new and updated data of the object to the Destination.

See Also

• Optimizing Query Modes for Objects

• Query Modes and Events Quota Consumption

• Comparing Change Tracking and Change Data Capture (CDC)

Revision History