Job Reader: Database
This job extracts data from relational databases using a native driver, an ODBC data source, or an Enzo Server. If the database is SQL Server, the job can also read from Change Tracking or Change Data Capture feeds.
Using an SQL Command
To extract data using an SQL command, select Custom SQL Command from the Method dropdown box, then enter the SQL command in the native query language of the database engine or the ODBC driver. Although any command can be executed, it is recommended to use a simple SELECT operation whenever possible; doing so enables DataZen to perform internal optimizations to read data in sets of rows to minimize impact on memory.
Click on the preview link to refresh the output. If the SQL command is a SELECT operation, you can also limit the number of records dispayed by changing the Max Rows setting.
Multiple options available on other tabs require you to preview the data so that schema and sample data is available.
Using an SQL Command with XML/JSON Conversion
If the data returned by the SQL command contains a raw JSON/XML document, and this is the only information you need to extract, you can convert the document into rows and columns by selecting the Apply JSON/XML Column Transformation.
As an example, the SQL command shown here returns the content of the _raw column (index 0) which contains a JSON payload from previous tweets.
To convert this JSON output into rows and columns, click on the Apply JSON/XML Column Transformation checkbox.
Enter 0 in the Column Name or Index field.
Since we want to read from the root of the document, leave the Path field blank.
Optionally, specify the name of an extra column that should contain the source document.
In this example, the extra column name is _raw, but it could be anything.
Click the preview link to see the data transformed into rows and columns.
Data types are selected automatically by DataZen based on a sample of the data returned.
Using SQL Server CDC or Change Tracking
To choose a Change Tracking or CDC Table as the source of data for an existing SQL Server database, choose the appropriate option in the Method dropdown box.
Setting up Change Tracking and/or CDC in SQL Server requires advanced knowledge of the SQL Server engine. While DataZen provides options to quickly configure these settings it is recommended that a database administrator perform them instead for production systems.