Database Objects and Actions
On This Page
The following image illustrates the standard actions that are available for objects of RDBMS and Amazon Redshift Sources. For other database Sources such as Amazon DynamoDB, the Edit Config and Change Position actions are not available.
Some additional actions relating to historical load ingestion for log-based Pipelines are displayed:
The following sections explain each available action in detail.
You can use the Edit Config action to change the query mode for objects of an RDBMS Source in Table and log based Pipelines. Read Factors Affecting Event Usage - Query Modes to understand how the query mode impacts the consumption of your Events quota.
In log-based Pipelines, the query mode is applicable to historical data ingestion. In the case of table-based Pipelines, both historical and incremental data ingestion tasks use the same query mode configuration. Read Query Modes for Ingesting Data for more information.
To change the query mode of an object:
In the Pipeline List View, click on the Pipeline you want to modify, to open it in the Pipeline Overview page.
In the Pipeline Objects list, click the More () icon for the desired object and select Edit Config.
In the <Object> Configuration popup dialog, select a query mode from the available options, and provide the associated details. Any query mode change restarts the ingestion, as the offset determination mechanism changes. If any ingestion is currently in progress, it is halted and re-ingestion starts.
For example, for the Delta-Timestamp query mode, you must specify the parameters as shown below:
Refer to the following table for the information required for each query mode:
Query Mode Parameter Full Load None. Delta-Timestamp - Timestamp Column Name
- Milliseconds to wait before ingesting a row. (Optional.
Change Data Capture - Incrementing Column Name
- Timestamp Column Name
- Milliseconds to wait before ingesting a row. (Optional)
Unique Incrementing Append Only - One or more Unique Column Name(s). The selected columns must not contain any null values. XMIN (available only for PostgreSQL Sources) - XMIN column. (No input required)
Points to note while selecting the query mode:
The Unique Incrementing Append Only (UIAO) mode requires a primary key or an indexed column. This mode is not recommended for incremental data.
In the case of Delta - Timestamp mode, the column chosen for querying the data must be one that is updated on each create and update Event in a table row.
The Change Data Capture mode is a combination of the UIAO and Delta - Timestamp modes and has the same conditions. This mode is very performant for historical data as it allows Hevo to parallelize the ingestion based on the selected index column.
The Change Tracking mode is available for selection in the case of SQL Server Sources, but it works only if Change Tracking is enabled for the table or database in the SQL Server.
Click SAVE CHANGES.
For steps to change the query mode for multiple objects at once, read Bulk Actions for Objects.
In case you have selected Full Load as the query mode and the objects ingested in Full Load are higher than 10% of your billable Events, Hevo displays an alert on the UI and recommends you to optimize the query modes for the object.
Use the Change Position action to ingest data from a different offset than the value determined by Hevo. For example, suppose you want to apply a Transformation on only a part of the data. Then, you can use the change position action to re-ingest only the required data.
The position defines the last line read by Hevo from a database log file or table, and helps to identify the line from which the next ingestion must start. It comprises the timestamp when the last record was ingested, along with the log file name and the line number (offset). For example, if the position is File:mysql-bin.000004 and Offset is 157, it means the log file last read is mysql-bin.000004 and the last line read is 157. Alongside, the timestamp value indicates the time when this last line was read.
This option is provided for all Table mode and log-based Pipelines except for WAL mode in PostgreSQL and Streams in Amazon DynamoDB. For log based pipelines, as the position is based on the log file, which is common to all the objects, the position is applicable at the Pipeline level.
Once you change the position, ingestion restarts from the new position.
Changing the position for a log-based Pipeline
In MySQL (BinLog) change position is limited to the available logs which are governed by the
binlog_retention_period value. You can check the available binary log files by running the following command via any SQL editor, such as DBeaver:
show binary log
The same concept applies to Oracle. To view the archived redo log files in Oracle, you can use the following command. This command displays the list of all the archived redo log files that exist for the database. You can also use various options with this command to filter the results and get more information about the archived logs.
SELECT * FROM V$ARCHIVED_LOG;
In WAL-based PostgreSQL Pipelines, the change position action is not applicable.
To change the position:
Note: If you need help in changing the offset, reach out to Hevo Support
In the Pipeline List View, click on the Pipeline you want to modify, to open it in the Pipeline Overview page.
In the Pipeline Activity section, click the More () icon and select Change Position.
Specify the new BinLog file name and the Offset value in that file from where ingestion must start. You can get the log file name using the command:
show binary log
Changing the position for an object in a non-log-based Pipeline
For Pipelines created in Table and Custom SQL modes, you can change the position for individual objects and re-ingest the data as per the new position.
Click the More () icon for the object and then, click Change Position.
Specify the new position to start ingesting from. For example, in the image below, the position for the
countrylanguageobject is changed from country code, ZWE and language, Shona to country code, India and language, English.
The Source columns that determine the position are based on the query mode you selected for the object during Pipeline creation.
Tip: Click the Edit Config action to see these values. For example, the image below shows the query mode and columns selected for the
Click UPDATE. The object is queued for ingestion.
The Run Now action enables you to manually trigger the ingestion for an active Pipeline or object. The Pipeline or object is immediately queued for ingestion.
To manually trigger the ingestion for a Pipeline, click the More () icon in the Pipeline Summary bar and select Run Now.
To manually trigger the ingestion for an object, click the More () icon for the object in the Objects list and select Run Now.
This action is useful when you urgently need data from the Source and do not want to wait for the next scheduled ingestion.
Manually triggering the ingestion using the Run Now option does not affect the scheduled ingestion for the object. The ingestion occurs as per the frequency set for the Pipeline.
Suppose you create a Pipeline at 3 PM (UTC) and set 1 Hour as the ingestion frequency. If you trigger ingestion using the Run Now action at 3:15 PM, the Events are ingested once and the next ingestion happens at 4 PM, as per the defined schedule. It is not moved to 4:15 PM. Thus, the Events are ingested at 3:15 PM, 4 PM, 5 PM, and so on.
Use the Sample Events action to view a sample of the ingested Events as they will be loaded to the Destination.
For example, in the sample Event displayed in the image below, as the Merge Tables option was selected during Pipeline creation, the
__hevo__database_name field is added to the Event at the time of ingestion.
To view sample Events:
Click the More () icon for the object and select Sample Events.
Click PREVIOUS and NEXT to move between the sample Events.
Include and Skip Objects
Skip and Include are complementary actions. You can skip and include both active and paused Source objects. For log-based Pipelines, skipping or including the Source object means dropping or including the related Events, respectively, from the log-based ingestion.
Note: This feature is available for Amazon Redshift, and all variants of MySQL, Oracle, PostgreSQL, and SQL Server Sources only.
To include an object that you skipped during or post-Pipeline creation:
Click the More () icon corresponding to the object you want to include, and click Include Object from the drop-down.
In the configuration screen that appears, select the appropriate query mode and the corresponding columns, and click SAVE CHANGES.
To include multiple skipped objects:
Select the check boxes corresponding to the objects that you want to include.
In the bulk actions bar, click INCLUDE.
In the Configure Objects dialog, select the appropriate query mode and the corresponding columns for each object, and click UPDATE QUERY MODE AND INCLUDE. Only the objects you select in this popup dialog are included.
Pause and Resume Historical Load
The Pause Historical Load and Resume Historical Load actions allow you to pause and resume the ingestion of historical Events. You can resume the ingestion from the last position till which the historical data was successfully ingested. This action remains available only till the historical load ingestion is in progress.
This option is available in Pipelines created from Hevo Release 1.67 onwards.
To pause the historical load:
Click the More () icon for the object while its status is Historical Load in Progress and select Pause Historical Load.
The ingestion status for the object changes to Historical Load Paused.
Similarly, resume the historical data ingestion by selecting the Resume Historical Load action.
Restart Historical Load
The Restart Historical Load action enables you to ingest data for an object since the beginning (the beginning is defined per Source type). This action works on ACTIVE and SKIPPED objects and active Pipelines. If the historical data was previously ingested for a skipped object, you can restart the historical load for it. If an object was skipped during Pipeline creation itself, you can use the Include Historical Load action instead.
This feature is applicable to log-based Pipelines. For these Pipelines, the log-based incremental data ingestion cannot be restarted, due to probable log expiry. Therefore, to retrieve the data, the alternative is to restart the historical load for the object.
To restart the historical load:
Click the More () icon and select Restart Historical Load.
In the popup dialog that appears, select YES, RESTART HISTORICAL LOAD. The object is queued for ingestion.
Include and Skip Historical Load
Use the Skip Historical Load action to skip the historical Events and include them at a later point in time. This action is available only while the historical load is in progress. It is not shown when the historical tasks are not present, for example, in the case of Full load objects or if you do not select the Load Historical Data option during Source configuration.
Similarly, use the Include Historical Load action if you had previously skipped historical data ingestion. This action is also available for objects that were skipped during Pipeline creation. Ingesting historical data now changes the object’s status to ACTIVE.
These actions are available in Pipelines created from Hevo Release 1.67 onwards.
To include the historical load:
Click the More () icon for the object and select Include Historical Load.
In the <Object> Configuration popup, confirm or update the query mode settings for the object.
Click SAVE CHANGES. The ingestion status for the object changes to Historical Load in Progress.
To skip the historical load:
Click the More () icon for the object and select Skip Historical Load. The ingestion status for the object changes to Historical Load Skipped.
View Activity Log
The Activity Log option displays the log of all the activities performed by any member of your team for an object. This is accessible to all the users irrespective of their role.
To view the activity log:
Click the More () icon for the object and select Activity Log.
In the ACTIVITY LOG, view the list of actions performed on that object since Pipeline creation.
Optionally, click the Expand and Collapse icons next to an action to view and hide detailed information about it respectively.
Optionally, filter by Author to view the actions performed by a specific team member, or by Severity to view the actions of a specific severity level, such as, Critical, High, Medium, and Low. The severity levels are assigned by Hevo as follows:
Severity Level Description Low Assigned to updates relating to syncing of the records. It does not indicate any concern; it is just an audit trail for users to follow. Medium Assigned to successful creation and execution actions. For example, a change to a Source configuration, successful execution of Models, a create, update, or delete Event action in Schema Mapper, a pause, resume, or update Event for Pipelines or Models, and so on. High Assigned to temporary connection failures for Source or Destination, creation and deletion of a Pipeline or Model. Critical Assigned to permanent failure of a Source, Destination, or Model.
Refer to the following table for the list of key updates made to this page:
|Date||Release||Description of Change|
|Apr-07-2023||NA||Created as a new page with additional details for each object action.|