Database Reader
Returns data from a JDBC query against one or more tables in one of the following:
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
Note
Sources supported by Database Reader are also supported by Incremental Batch Reader.
If the connection to the database is interrupted, the application will halt.
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.
If you are using Database Reader to perform an initial load before running an application using a CDC reader, enable CDC on the source and perform the setup tasks for the CDC reader before starting the initial load.
Database Reader properties
property | type | default value | notes |
---|---|---|---|
Connection URL | String | The following databases are supported.
| |
Database Provider Type | String | Default | Controls which icon appears in the Flow Designer. |
Excluded Tables | String | If | |
Fetch Size | Integer | 100 | 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 the your JDBC driver) |
JAAS Configuration | String | This is not supported in Striim Cloud. | |
Password | encrypted password | The password for the specified user. See Encrypted passwords. | |
Query | String | SQL statement specifying the data to return. You may query tables, aliases, synonyms, and views. When If the query includes a synonym containing a period, it must be enclosed in escaped quotes. For example: If using a query when the output of a DatabaseReader source is the input of a DatabaseWriter target, specify the target table name as the value of DatabaseReader's Tables field. The query may not contain single-line quotes (of the format | |
Quiesce on IL Completion | Boolean | False | |
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). | |
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 If you are using the Query property, specify QUERY as the table name. | |
Username | String | 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. |
The output type is WAevent.
Note
To read from tables in both Oracle CDB and PDB databases, you must create two instances of DatabaseReader, one for each.
Database Reader sample code
The following example creates a cache of data retrieved from a MySQL table:
CREATE TYPE RackType( rack_id String KEY, datacenter_id String, rack_aisle java.lang.Integer, rack_row java.lang.Integer, slot_count java.lang.Integer ); CREATE CACHE ConfiguredRacks USING DatabaseReader ( ConnectionURL:'jdbc:mysql://10.1.10.149/datacenter', Username:'username', Password:'passwd', Query: "SELECT rack_id,datacenter_id,rack_aisle,rack_row,slot_count FROM RackList" ) QUERY (keytomap:'rack_id') OF RackType; The following example creates a cache of data retrieved from an Oracle table: CREATE TYPE CustomerType ( IPAddress String KEY, RouterId String, ConnectionMode String, CustomerId String, CustomerName String ); CREATE CACHE Customers USING DatabaseReader ( Password: 'password', Username: 'striim', ConnectionURL: 'jdbc:oracle:thin:@node05.example.com:1521:test5', Query: 'SELECT ip_address, router_id, connection_mode, customer_id, customer_name FROM customers', FetchSize: 1000 ) QUERY (keytomap:'IPAddress') OF CustomerType;
DatabaseReader data type support and correspondence
JDBC column type | TQL type | notes |
---|---|---|
Types.ARRAY | java.lang.String | |
Types.BIGINT | java.lang.Long | |
Types.BIT | java.lang.Boolean | |
Types.CHAR | java.lang.String | |
Types.DATE | org.joda.time.LocalDate | |
Types.DECIMAL | java.lang.String | |
Types.DOUBLE | java.lang.Double | |
Types.FLOAT | java.lang.Double | |
Types.INTEGER | java.lang.Integer | |
Types.NUMERIC | java.lang.String | |
Types.REAL | java.lang.Float | |
Types.SMALLINT | java.lang.Short | |
Types.TIMESTAMP | org.joda.time.DateTime | |
Types.TINYINT | java.lang.Short | For MySQL, if the source tables contain columns of this type, append |
Types.VARCHARCHAR | java.lang.String | |
other types | java.lang.String |
DatabaseReader can not read Oracle RAW or LONG RAW columns (Oracle Reader can).
Sample Database ReaderWAEvent
For the following row:
id first_name last_name phone street city state zip_code 1 Deborah Burks NULL 9273 Thorne AV Orchard Park NY 14127
The WAEvent would be similar to:
WAEvent{ data: [1,"Deborah","Burks",null,"9273 Thorne AV","Orchard Park","NY","14127"] metadata: {"TableName":"BikeStores.sales.customers","ColumnCount":8, "OperationName":"SELECT","OPERATION_TS":1681412863364} userdata: null before: null dataPresenceBitMap: "fwM=" beforePresenceBitMap: "AAA=" typeUUID: {"uuidstring":"01edda2e-77f7-9b21-83c2-8e859085da65"} };
The operation name for Database Reader WAEvents is always SELECT.