Overview

Database Source is a source component aimed at accessing a JDBC compliant database, execute a query and convert results into records to be processed in the pipeline.

FQCN

com.datumize.ddc.db.component.JDBCSource


The only input expected is a JDBC compliant database. The connections are pooled to improve performance.

Properties


Common properties that apply to all source and processor components of a pipeline.

PROPERTYIDDESCRIPTION

REQUIRED

TYPE

DEFAULT

EXAMPLES

Common
IdentifierIDComponent unique Identifier within the pipeline, read only, only useful for advanced mode.YesStringAuto

MyComponent_23423

This Identifier is automatically generated by the system and you can't change it. Might be helpful for advanced pipeline configuration.

DescriptiondescriptionA short description for the component, to be visualized inside the component representation, aimed at providing additional information to understand the pipeline at a glance.NoString

Extract customer id and loyalty number.

Short and sweet description.

TopictopicAll Source and Processor components support a topic to tag the output records. If the record does not have a tag applied, this topic will be automatically applied. The topic may be used by future components to route, group or classify records.NoString

foo

All output records will be tagged using "foo", unless they have not been tagged during the execution of the step.

PROPERTYIDDESCRIPTIONREQUIREDTYPEDEFAULTEXAMPLES
Default
URLurlDatabase URL connection in jdbc format. The actual format depends on the RDBMS.YesURL
jdbc:postgresql://host:port/database
Driver classdriverDatabase driver full class name. Check the Available Driver Classes here.YesClassname for the JDBC driver to use.
org.postgresql.Driver
UseruserDatabase user.YesString
datumize
PasswordpasswordDatabase password.YesString
datumize123
QueryquerySQL query.YesString in SQL format
select * from Employees;
Connection Pool





Wait timeoutconnection-timeoutMaximum number of milliseconds that a client will wait for a connection from the database connection pool.NoDuration3s

10s

Set connection timeout to 10 seconds.

Idle timeoutidle-timeoutMaximum amount of time that a connection is allowed to sit idle in the database connection pool.NoDuration10s
Max Lifetimemax-lifetimeMaximum lifetime of a connection in the poolNoDuration180s
Minimum idleminimum-idleMinimum number of idle connections to maintain in the database connection pool.NoInteger1
Maximum sizemaximum-pool-sizeMaximum number of connections to maintain in the database connection pool.NoIntegerNumber of threads







ConverterconverterConverter used to transform JDBC ResultSet into another object.YesSee Database Converters converters section.

Paginatorpaginator

Request with or without pagination.

NoPaginator SpecificationsNo Paginator
RecoverablerecoverableSave and restore the state between executions when using a strategy pagination.NoBoolean

Available Driver Classes

We currently support some driver classes listed here and we are working on support for external classes that would be attached inside this field:

String classClass version
org.postgresql.Driverorg.postgresql:postgresql:42.2.10
org.mariadb.jdbc.Driverorg.mariadb.jdbc:mariadb-java-client:2.5.4
com.mysql.jdbc.Drivermysql:mysql-connector-java:8.0.19
com.mysql.cj.jdbc.Drivermysql:mysql-connector-java:8.0.19

Paginator Specifications

No Paginator

It uses no pagination strategy and returns all rows returned by the database.

Offset Paginator

Paginate results by offset.

The page size is equal to the batch size of this Step.

To use this paginator the query must use two special placeholders: {{PAGE_SIZE}}  and {{PAGE_POSITION}}. It is possible to put these placeholders in any position of the query.

Example: SELECT ... FROM ... LIMIT {{PAGE_SIZE}} OFFSET {{PAGE_POSITION}}

PROPERTYIDDESCRIPTIONREQUIREDTYPEDEFAULTEXAMPLES
Initial offsetinitial-offsetInitial offsetNoInteger0


Id Paginator

Paginate results by id. Instead of a static offset, this strategy uses one column of the query as the identifier with which paginate the data.

The page size is equal to t he batch size of this Step.

To use this paginator the query must use two special placeholders: {{PAGE_POSITION}}  and {{PAGE_SIZE}} . It is possible to put these placeholders in any position of the query.

Example: SELECT ... FROM ... WHERE id_field > {{PAGE_POSITION}} ORDER BY id_field LIMIT {{PAGE_SIZE}}

PROPERTYIDDESCRIPTIONREQUIREDTYPEDEFAULTEXAMPLES
Id columnid-columnName of the returned column with the id used to paginated resultsYesString


Initial idinitial-idInitial idYesString

ParserparserParser that converts the initial id to the proper data type for the queryYesParser Specification

Parser Specification

When using Id Paginator with an initial id, it is necessary to define explicitly how to convert this value to the data type expected by the database.

To achieve that, it is necessary to specify one of the following parsers:

String Parser

Valid for CHAR, VARCHAR, LONGVARCHAR, TEXT ...

Integer Parser

Valid for INT, INTEGER ...

Long Parser

Valid for LONG, BIGINT ...

Short Parser

Valid for SHORT, SMALLINT ...

Byte Parser

Valid for BYTE, TINYINT ...

Float Parser

Valid for FLOAT, REAL ...

Double Parser

Valid for DOUBLE ...

Big Decimal Parser

Valid for NUMERIC, DECIMAL, MONEY ...

Boolean Parser

Valid for BIT, BOOL, BOOLEAN ...

Date Parser

Parse standard sql dates

PROPERTYIDDESCRIPTIONREQUIREDTYPEDEFAULTEXAMPLES
FormatformatDate formatNoStringdd-MM-yyyy


Time Parser

Parse standard sql time

PROPERTYIDDESCRIPTIONREQUIREDTYPEDEFAULTEXAMPLES
FormatformatTime formatNoStringHH:mm:ss


Timestamp Parser

Parse standard sql timestamps

PROPERTYIDDESCRIPTIONREQUIREDTYPEDEFAULTEXAMPLES
FormatformatTimestamp formatNoStringdd-MM-yyyy HH:mm:ss


LocalDate Parser

Parse LocalDate dates

PROPERTYIDDESCRIPTIONREQUIREDTYPEDEFAULTEXAMPLES
FormatformatDate formatNoStringdd-MM-yyyy


LocalDateTime Parser

Parse LocalDateTime dates

PROPERTYIDDESCRIPTIONREQUIREDTYPEDEFAULTEXAMPLES
FormatformatDate formatNoStringdd-MM-yyyy HH:mm:ss