Database Table Source

DaDatabase Table Source

Centerprise Database Source provides the functionality to retrieve data from a database table. Additionally, Database Table Source provides change data capture functionality to perform incremental reads. Database Table sources supports multi-way partitioning which partition database table into multiple chunks and reads these chunks in parallel. This features brings about major performance increase for database reads. Database source enables you to specify where clause and sort order to control the result set.

Video

Key Features

Incremental Read

Database Table Source provides incremental read functionality based in the concept of audit fields. Incremental read is one of the three change data capture approaches supported by Centerprise. Audit fields are fields that are updated when a record is created or modified. Examples of audit fields include created date time, modified date time, and version number.

Incremental read works by keeping track of the highest value for the specified audit field. On the next run, only the records with value higher than the saved value are retrieved. This feature is useful in situations where two applications need to be kept in sync and the source table maintains audit field values for rows.

Database Table Partitioning

Database table partitioning feature partitions database table in a number of user-specified chunks. These chunks are then retrieved in parallel to improve the overall throughput for the job. When combined with the highly parallel Centerprise engine, this feature substantially improves the performance of large data movement jobs. Partitioning is done by selecting a field and defining value ranges for each partition. At runtime, Centerprise generates and runs multiple queries against the source table and processes the result set in parallel.

Usage

Adding a database table source object allows you to transfer data from a database table. An example of what a database table source object looks like is shown below.

To add a Database Table Source on your flow, locate the Database Table Source on the toolbox under the Sources group and drag-and-drop it into your flow.

You can also add a Database Table Source using the Data Source Browser. Locate the desired table from the database tree and drag-and-drop it into your flow.

To configure the properties of a Database Table Source object after it was added to the dataflow, right-click on it and select Properties from the context menu. The following properties are available:

 

Source Connection screen

Allows you to enter the connection information for your source, such as server name, database and schema, as well as credentials for connecting to the selected source.

A Database Connection resource can also be used as a shared connection. This can be found under the Resources group in the flow toolbox.

Pick Source Table screen

Select a source table using the Pick Table dropdown.

• Select Full Load if you want to read the entire table.

• Select Incremental Load Based on Audit Fields to perform an incremental read starting at a record where the previous read left off.

Incremental load is based around the concept of Change Data Capture (CDC), which is a set of reading and writing patterns designed to optimize large-scale data transfers by minimizing database writing  in order to improve performance. CDC is implemented in Centerprise using Audit Fields pattern. The Audit Fields pattern uses create time or last update time to determine the records that have been inserted or updated since the last transfer and transfers only those records.

Advantages

• Most efficient of CDC patterns. Only records that were modified since the last transfer are retrieved by the query thereby putting little stress on the source database and network bandwidth

Disadvantages

• Requires update date time and/or create date time fields to be present and correctly populated

• Does not capture deletes

• Requires index on the audit field(s) for efficient performance

To use the Audit Fields strategy, select the Audit Field and an optional Alternate Audit Field from the appropriate dropdown menus. Also, specify the path to the file that will store incremental transfer information.

Layout Builder screen

The layout builder shows the layout of the database. This is automatically built. The layout builder provides the option to rename the header for easy identification on Centerprise, as well as change the datatype for each field. It also shows the Db type that was used in the database and the length.

WHERE Clause screen

You can enter an optional SQL expression serving as a filter for the incoming records. The expression should start with the WHERE word followed by the filter you wish to apply.

For example, WHERE CreatedDtTm >= ‘2001/01/05’

General Options screen

The Comments input allows you to enter comments associated with this object.

Database Table Options

Right-clicking on the Database Table Source will also display options for the Database Table.

• View Table Data - Builds a Query and displays all of the data from the table
• View Table Schema - Displays the schema of the database table
• Create Table - Creates a table on a database based on the schema