SkyScraper - Apache Camel Data Transformation Integration Series - 1

Project overview

The client would like to implement some inter-connect routes in Apache Camel to move data between systems, initiated via REST calls.  Note that this is the first of many integration challenges that will be upcoming.  This is your chance to get in early and get up to speed.

Technology

• Java 11
• Apache Camel 3.4.0
• SpringBoot 2.3.1
• Maven 3.8.8
• Kafka 2.6.0
• Oracle 18

Flow

The caller (System A) will make an HTTP REST call to the Spring Boot application (your submission).  This call will be routed to Apache Camel.  You should implement Camel endpoints, routing, and processing to:

• Handle an HTTP REST request that initiates processing to:
• Make a JDBC connection to a configured Oracle server
• Query records from the FUTURES_OPTIONS_CONTRACT_MASTER table
• Parse the data
• Convert the data to JSON
• Route the data to a Kafka endpoint in Camel
• The final message will be put onto a configured Kafka topic
• A general HTTP response and message will be sent to the HTTP REST caller

Request

The request to me REST API should look like:

GET /cs_01/?LAST_UPDATED_DATE=<date>

JDBC data

The table format for the Oracle table will be found in the forum.  We will make a simple SQL query to select all records from FUTURES_OPTIONS_CONTRACT_MASTER that have an UPDATED_DATE_TIME value that is the same or later than the LAST_UPDATED_DATE URL parameter given to the REST GET API endpoint above.

For each record in the database, we'll convert to JSON following the "Current mapping logic" column in the XLSX and will post a message to the Kafka queue.  One message per record.

You will need to create a "FUTURE_OPTIONS_CONTRACT_MASTER" table schema yourself, with test data, based on the information in the XLSX posted in the forum.  The client will not be providing a schema SQL statement or sample data.

JSON output to Kafka

The JSON message to Kafka will be a raw JSON object, with fields matching column "K" (Source Data Field) in the spreadsheet in the forum, like:

{
    "CTR_TKN_NO":123,
    "CTR_INSTRMNT_TYPE":"ASDF1",
    "CTR_SMBL":"SDFG123",
    ...
}

Any field marked "NULL" in the "Source Data Field" column can be left out of the JSON message

Apache Camel

Apache camel should be used, and route, endpoint, and processing should all be properly registered with the CamelContext in the SpringBoot app

REST response

The response to the REST call should look like:

Success

{
"status": 200,
"message": "Request is Successful ",
}

With an HTTP status code of 200

Error:

{
"timestamp": "2020-09-27T02:50:24.400+00:00",
"status": 500,
"errorCode”: “ERR_002”
"error": "Internal Server Error",
"message": " Internal Server Error",
}

• HTTP status code of 400 and errorCode "ERR_001" for when an error has occurred. Most likely whenever wrong or incomplete DB data has been received.
• HTTP status code of 500 and errorCode "ERR_002" for when an error occurred when publishing to Kafka
• HTTP status code of 503 and errorCode "ERR_003" for when Kafka is unavailable

Example database errors:

• Database connection timeout
• Table or schema does not exists: SQL Exception

Example Kafka errors:

• Unable to connect to Kafka Bootstrap server
• No such topic found or invalid topic
• Key not found or invalid key in Kafka
• NotFoundException : Indicates that an operation attempted to modify or delete a connector or task that is not present on the worker.
• RetriableException: An exception that indicates the operation can be reattempted.
• IllegalWorkerStateException: Indicates that a method has been invoked illegally or at an invalid time by a connector or task.

Example processing errors:

• Invalid input message
• Data conversion exception like converting data into Json and binary.
• If any mandatory input or value is null: NullPointerException

Retries

If there is a failure, we want to ensure that the process of pulling, parsing, and publishing is retried.  The number of times to retry should be configurable, defaulting to 3.  If the process failed too many times, we'll report the last error that caused a failure

Logging

Every request and response should be logged in INFO level for successful and failure case.
Develop a Spring AOP library that will log at:

• The entry and exit of every call invocation.
• Also, all errors need to be logged in ERROR level.
• Input request.
• Exception
• Start of method notification
• End of method notification
• Conditions if applied

The logs to be produced in a format matching the attributes published in the forum.

Configuration

These items must be easily configurable in the submission:

• Logging details
• Database configuration:
  • Host
  • Port
  • Authentication details
  • Database name
• Kafka Configuration:
  • Kafka details
  • Kafka topic name
  • Bootstrap-server host and port

Links

• https://www.baeldung.com/apache-camel-spring-boot
• https://camel.apache.org/components/latest/kafka-component.html (recommendation)
• https://camel.apache.org/components/latest/netty-component.html (recommendation)

 

Scoring guidelines

Major requirements

• REST endpoint, TCP processing, and Kafka publishing work

Minor requirements

• Documentation is complete

Submission

Please submit:

• Code for the SpringBoot app that meets the requirements above
• README.md that describes how to deploy and configure the app
• Validation.md that describes how reviewers and the client can verify the app is functioning properly

Final Submission Guidelines

Please see above