Database dump to HDFS Sync application

Summary

Ingest records from a PostgreSQL Database table to hadoop HDFS. This application reads messages from configured PostgreSQL table and writes each record as a comma-separated line in HDFS files.

The source code is available at: https://github.com/DataTorrent/app-templates/tree/master/database-to-hdfs.

Please send feedback or feature requests to: feedback@datatorrent.com

This document has a step-by-step guide to configure, customize, and launch this application.

Steps to launch application

Click on the AppHub tab from the top navigation bar.
Page listing the applications available on AppHub is displayed. Search for Database to see all applications related to Database.

Click on import button for Database dump to HDFS Sync App
Notification is displayed on the top right corner after application package is successfully imported.
Click on the link in the notification which navigates to the page for this application package.

Detailed information about the application package like version, last modified time, and short description is available on this page. Click on launch button for Database-to-HDFS-Sync application.
Launch Database-to-HDFS-Sync dialogue is displayed. One can configure the name of this instance of the application from this dialogue.
Select Use saved configuration option to display a list of pre-saved configurations. Please select sandbox-memory-conf.xml or cluster-memory-conf.xml depending on whether your environment is the DataTorrent sandbox, or other cluster.
Select Specify custom properties option. Click on add default properties button.

This expands a key-value editor pre-populated with mandatory properties for this application. Change values as needed. Properties editor

For example, suppose we wish to process all rows from the table test_event_table in a PostgreSQL database named testdb accessible at database-node.com port 5432 with credentials username=postgres, password=postgres, and we wish to write the output to the HDFS file /user/appuser/output/output.txt. Properties should be set as follows:

Name	Value
dt.operator.JdbcPoller.prop.store.databaseUrl	jdbc:postgresql://database-node.com:5432/testdb
dt.operator.JdbcPoller.prop.store.password	postgres
dt.operator.JdbcPoller.prop.store.userName	postgres
dt.operator.JdbcPoller.prop.tableName	test_event_table
dt.operator.JdbcPoller.prop.whereCondition
dt.operator.fileOutput.prop.filePath	/user/appuser/output
dt.operator.fileOutput.prop.outputFileName	output.txt

Details about configuration options are available in Configuration options section.

Click on the Launch button at the lower right corner of the dialog to launch the application. A notification is displayed on the top right corner after the application is launched successfully and includes the Application ID which can be used to monitor this instance and to find its logs.
Click on the Monitor tab from the top navigation bar.
A page listing all running applications is displayed. Search for the current instance based on name or application id or any other relevant field. Click on the application name or id to navigate to the details page.
Application instance details page shows key metrics for monitoring the application status. logical tab shows application DAG, StrAM events, operator status based on logical operators, stream status, and a chart with key metrics.
Click on the physical tab to look at the status of physical instances of operators, containers etc.

Configuration options

Mandatory properties

End user must specify the values for these properties.

Property	Description	Type	Example
dt.operator.JdbcPoller.prop.store.databaseDriver	JDBC driver class. This has to be on CLASSPATH. PostgreSQL driver is added as a dependency.	String	org.postgresql.Driver
dt.operator.JdbcPoller.prop.store.databaseUrl	JDBC connection URL	String	jdbc:postgresql://database-node.com:5432/testdb
dt.operator.JdbcPoller.prop.store.password	Password for Database credentials	String	postgres
dt.operator.JdbcPoller.prop.store.userName	Username for Database credentials	String	postgres
dt.operator.JdbcPoller.prop.tableName	Table name for input records	String	test_event_table
dt.operator.JdbcPoller.prop.whereCondition	Where clause condition (if any) for input records. Keep blank to fetch all records.	String
dt.operator.fileOutput.prop.filePath	Output path for HDFS	String	/user/appuser/output
dt.operator.fileOutput.prop.outputFileName	Output file name	String	output.txt

Advanced properties

There are pre-saved configurations based on the application environment. Recommended settings for datatorrent sandbox edition are in sandbox-memory-conf.xml and for a cluster environment in cluster-memory-conf.xml (the first 2 are integers and the rest are strings). The messages or records emitted are specified by the value of the TUPLE_CLASS attribute in the configuration file namely PojoEvent in this case.

Property	Description	Default for cluster -memory - conf.xml	Default for sandbox -memory -conf.xml
dt.operator.JdbcPoller.prop.partitionCount	Number of JDBC input partitions for parallel reading.	4	1
dt.operator.JdbcPoller.prop.batchSize	Batch size to read data from JDBC.	300	300
dt.operator.JdbcPoller.prop.key	Key column for the table. This will be used for partitoning of rows.	ACCOUNT_NO	ACCOUNT_NO
dt.operator.JdbcPoller.prop.columnsExpression	Key column for the table. This will be used for partitoning of rows.	ACCOUNT_NO, NAME, AMOUNT	ACCOUNT_NO, NAME, AMOUNT
dt.operator.JdbcPoller.port.outputPort.attr.TUPLE_CLASS	Fully qualified class name for the tuple class POJO(Plain old java objects) emitted by JDBC input	com.datatorrent.apps.PojoEvent	com.datatorrent.apps.PojoEvent
dt.operator.JdbcPoller.prop.pollInterval	Poll interval for scanning new records in milisec	1000	1000
dt.operator.formatter.prop.schema	Schema for CSV formatter	{"separator": "\|", "quoteChar": "\"", "lineDelimiter": "", "fields": [ { "name": "accountNumber", "type": "Integer" }, { "name": "name", "type": "String" }, { "name": "amount", "type": "Integer" } ]}	{"separator": "\|", "quoteChar": "\"", "lineDelimiter": "", "fields": [ { "name": "accountNumber", "type": "Integer" }, { "name": "name", "type": "String" }, { "name": "amount", "type": "Integer" } ]}
dt.operator.formatter. port.in.attr.TUPLE_CLASS	Fully qualified class name for the tuple class POJO(Plain old java objects) input to CSV formatter	com.datatorrent.apps.PojoEvent	com.datatorrent.apps.PojoEvent

You can override default values for advanced properties by specifying custom values for these properties in the step specify custom property step mentioned in steps to launch an application.

Steps to customize the application

Make sure you have following utilities installed on your machine and available on PATH in environment variable:
- Java : 1.7.x
- maven : 3.0 +
- git : 1.7 +
- Hadoop (Apache-2.2)+
Use following command to clone the examples repository:

git clone git@github.com:DataTorrent/app-templates.git
Change directory to examples/tutorials/database-to-hdfs:

cd examples/tutorials/database-to-hdfs
Import this maven project in your favorite IDE (e.g. eclipse).
Change the source code as per your requirements. Some tips are given as commented blocks in the Application.java for this project.
Make respective changes in the test case and properties.xml based on your environment.
Compile this project using maven:

mvn clean package

This will generate the application package file with .apa extension in the target directory.
Go to DataTorrent UI Management console on web browser. Click on the Develop tab from the top navigation bar.
Click on upload package button and upload the generated .apa file.
Application package page is shown with the listing of all packages. Click on the Launch button for the uploaded application package. Follow the steps for launching an application.