Incremental Batch Reader
You can use Incremental Batch Reader to get new data from a supported database or data warehouse. The most common use case for Incremental Batch Reader is to continuously update targets with new source data after initial load using Database Reader has completed, particularly when a CDC reader is not available for the source or when you choose not to use the available CDC reader.
Incremental Batch Reader supports the same data types as Database Reader (see DatabaseReader data type support and correspondence for supported data types).
HP NonStop SQL/MX (and SQL/MP via aliases in SQL/MX)
MariaDB (including Amazon RDS for MariaDB, MariaDB Galera Cluster, MariaDB Xpand, and SkySQL)
MySQL (including Amazon Aurora for MySQL, Amazon RDS for MySQL, Azure Database for MySQL, and Google Cloud SQL for MySQL)
Oracle (including Amazon RDS for Oracle)
PostgreSQL (including Amazon Aurora with PostgreSQL compatibility, Amazon RDS for PostgreSQL, Azure Database for PostgreSQL, Azure Database for PostgreSQL - Flexible Server, Google AlloyDB for PostgreSQL, and Google Cloud SQL for PostgreSQL)
SQL Server (including Amazon RDS for SQL Server and Azure SQL Database)
Sybase
Teradata
To read from Spanner, see Spanner Batch Reader.
Three properties control incremental reading:
Check Column specifies the column Incremental Batch Reader will use to identify which data is new.
Start Position specifies the Check Column value from which reading will start.
Polling Interval controls how often Incremental Batch Reader checks for new data. The first batch will begin with the Start Position value. Each successive batch will begin with the table rows immediately after the last row included in the previous batch.
Warning
For all databases, when this adapter is deployed to a Forwarding Agent, the appropriate JDBC driver must be installed as described in Installing third-party drivers in the Forwarding Agent.
Incremental Batch Reader properties
property | type | default value | notes |
---|---|---|---|
Check Column | String | Specify the name of the column containing the start position value. The column must have an integer or timestamp data type (such as the creation timestamp or an employee ID number) and the values must be unique and continuously increasing. MySQL and Oracle names are case-sensitive, SQL Server names are not. Use the syntax If you specify multiple tables in the Tables property, you may specify different check columns for the tables separated by semicolons. In this case, you may specify the check column for the remaining tables using wildcards: for example, When rows have the same Check Column value, they will be buffered in memory until the a row with a different Check Column value is read. To avoid memory usage spikes, do not set Check Column so that many rows will have the same value. For example, selecting a single Check Column containing the day's date (rather than a timestamp) might result in thousands or millions of rows with the same value. | |
Connection URL | String | See Connection URL notes in Database Reader. | |
Database Provider Type | String | Default | Controls which icon appears in the Flow Designer. |
Excluded Tables | String | Data for any tables specified here will not be returned. For example, if | |
Fetch Size | Integer | 100 | Sets the maximum number of records to be fetched from the database in a single JDBC method execution (see the discussion of fetchsize in the documentation for your JDBC driver). |
Password | encrypted password | The password for the specified user. See Encrypted passwords. | |
Polling Interval | String | 120sec | This property controls how often the adapter reads from the source. By default, it checks the source for new data every two minutes (120 seconds). If there is new data, the adapter reads it and sends it to the adapter's output stream. The value may be specified in seconds (as in the default) or milliseconds (for example, 500ms). |
Return DateTime As | String | Joda | Set to |
SSL Config | String | If the source is Oracle and it uses SSL, specify the required SSL properties (see the notes on SSL Config in Oracle Reader properties). | |
Start Position | String | The value in the specified check column from which Striim will start reading. Striim will read rows in which the check column's value is the same as or greater or later than this value and skip the other rows. Since Check Column may specify multiple tables you must specify the corresponding table name or wildcard for each value. With the Check Column example above, the Start Position value could be | |
Tables | String | The table(s) or view(s) to be read. MySQL, Oracle, and PostgreSQL names are case-sensitive, SQL Server names are not. Specify names as You may specify multiple tables and views as a list separated by semicolons or with the | |
Username | String | Specify the DBMS user name the adapter will use to log in to the server specified in ConnectionURL. For all databases, this user must have SELECT permission or privileges on the tables specified in the Tables property. For Oracle, this user must also have SELECT privileges on DBA_TAB_COLS and ALL_COLL_TYPES. |
Incremental Batch Reader sample code
The following would read rows from TABLE1 with UUID column values equal to or greater than 1234:
CREATE SOURCE OraSource USING IncrementalBatchReader ( Username: 'striim', Password: '********', ConnectionURL: '192.0.2.:1521:orcl', Tables: 'MYSCHEMA.TABLE1', CheckColumn: 'MYSCHEMA.TABLE1=UUID', StartPosition: 'MYSCHEMA.TABLE1=1234' ) OUTPUT TO OraSourceOutput;
If IncrementalBatchReader sends duplicate records to a DatabaseWriter target, by default the application will terminate. This can happen, for example, when recovery is enabled (see Recovering applications), there are multiple rows with the same CheckColumn timestamp, and only some of them were written before a system failure, To avoid this, specify the appropriate IgnorableException in the target: for example, for CosmosDBWriter, RESOURCE_ALREADY_EXISTS.