Database Table Configuration Options
  • 3 Minutes to read
  • Dark
    Light
  • PDF

Database Table Configuration Options

  • Dark
    Light
  • PDF

Article summary

Introduction

This document provides a comprehensive walkthrough of the configuration options in Relational Database Management Systems (RDBMS). These options enable users to manage various settings within different extraction River modes, including Standard Extraction, CDC (Change Data Capture), and System Versioning.

Standard Extraction Configuration

Accessing Configuration Options

To access the configuration options, users can do the following:

Configuration Options

The configuration options are outlined below:

Show All:

Selecting this option presents all existing selected schema tables, including both checked and unchecked tables.

Show Selected Tables:

Choosing this option displays only the selected schema tables, i.e., checked tables.

Edit Time Period for All Tables:

This option allows users to set the same time period value for all tables using the Incremental (date/timestamp-based Increment) extract method. This ensures consistency across tables.

Example:

Suppose 3 different tables with an Incremental extract method have different Incremental date ranges. By selecting the "Edit Time Period for All Tables" option and configuring a start date, the change is reflected in all selected incremental (date) based tables.

Note:

The identical configuration options can be located within MariaDB under the System Versioning extraction mode, and the process remains unchanged; be sure to designate "System versioning" as your chosen extraction mode.

CDC Extraction Configuration

The CDC extraction configuration options can be accessed on certain RDBMSs that offer support for Change Data Capture, including MySQL, SQL Server, Oracle, and PostgreSQL.

Accessing Configuration Options

After configuring a CDC-based extraction River and selecting the desired schema(s), users can access the configuration options using the provided instructions:

Configuration Options

The configuration options are outlined below:

Show All:

This option displays all selected schema tables, as mentioned above.

Show Selected Tables:

Similar to the standard menu, this option shows only the selected schema tables.

Set Initial Migration for All Tables:

By selecting this option, all selected tables' statuses change to "Waiting For Migration." An initial migration process occurs when the river is triggered, fetching all table data from the database. Afterward, tables may have a "Streaming" status for CDC.

Remove Initial Migration for All Tables:

This option changes the status of all selected tables to "Streaming." With this option, an initial migration process is skipped, and changes are captured directly through CDC.

Remove Initial Migration

When the River is triggered, there's no need for an initial migration process, and table changes (CRUD commands) are fetched via CDC.

Example Use Case - Skipping the Initial Migration (CDC):
If a user already has all historical data on the Data Warehouse (DWH) and wants to skip the migration process, follow these steps:

  1. Check all desired tables from the relevant schemas on the UI.
  2. Click on "Remove Initial Migration for All Tables" to change table statuses to "Streaming".
  3. Ensure match key(s) are defined for each selected table (for Upsert-Merge loading mode).
  4. Enable the stream.
  5. After a successful River run, check the target table's data alignment.
This effectively skips the migration process, but please note:

Before affecting existing production Target table(s), it is recommended to perform the migration skipping process on each selected table using a Target table prefix (e.g., skip_migration_test_<table_name>) to create a temporary table. After data is pushed to the temporary target table, compare data types and data to the existing production table. If everything aligns, remove the prefix and write to the existing Target table.

Note:

The identical configuration options can be located within Microsoft SQL Server under the Change Tracking extraction mode. The only difference is that if you opt for the "Remove Initial Migration for All Tables" option, the table status will switch to "Tracked" instead of "Streaming" as it does in CDC.
Make sure to specify "Change Tracking" as your selected extraction mode.


Was this article helpful?