Creating integration flows with the Reedelk Data Integration Platform

No Comments

The integration of data from systems of record or legacy systems is one of the elements of a software development project that does not start on a greenfield. In other words, it can help modernize software. Usually the question arises how to transfer the addressed data into the “new” world without much effort.

ESB, ETL and ELT are not dead

If you look at the use case schematically, it is basically about ETL (Extract-Transform-Load) or ELT (Extract-Load-Transform) processes. Nowadays, Kafka is very quickly taken as a super weapon or panacea, and this blog post will show that this is not always the best solution. The Enterprise Service Bus has a long tradition in software development.

And it is exactly this functionality that the Reedelk Data Integration Platform wants to map in a simple way. The product is relatively new on the market, more precisely as a community edition since the end of June 2020.

IntelliJ as a development environment provides the foundation and is extended with a plugin, the Reedelk Flow Designer (https://www.reedelk.com/documentation/intellijplugin). A similar model is offered by MuleSoft and RedHat, where both rely on Eclipse and also deliver a complete development environment. However, the Flow Designer is currently closed source for the platform (community edition), but it is available free of charge. The created runtimes can be deployed in the cloud in various ways. Detailed information can be found in the documentation (https://www.reedelk.com/documentation/runtime). The option to create a Docker container is delivered directly with each runtime project created and thus the different deployment types can be created via build pipeline mechanisms. Since version 1.0.1 OpenAPI Import Tool has also been integrated, thus the platform now supports the API-first approach.

An integration project

Now we want to take a look at the platform with the help of a demo project. The idea here is the concrete implementation of the use case mentioned at the beginning. Our fictitious customer has an old legacy application whose data should now be made available to newer applications with the help of APIs. The data model looks like this:

Database schema

The setting

The demo is based on the PostgreSQL example DB “Airlines”. The legacy system is an old booking system. The main entity is a booking (bookings). A booking can include several passengers, where a separate ticket (tickets) is issued for each passenger. A ticket has a unique number and contains information about the passenger. As such, the passenger is not a separate entity. Both the name of the passenger and the number of his or her ID card may change over time, making it impossible to uniquely identify all tickets for a particular person; for the sake of simplicity, we can assume that all passengers are unique. The ticket includes one or more flight segments (ticket_flights). Several flight segments may be included in a single ticket if there are no non-stop flights between the point of departure and destination (connecting flights) or if it is a round-trip ticket. Although there is no restriction in the scheme, it is assumed that all tickets in the booking have the same flight segments. Each flight (flights) goes from one airport (airports) to another. Flights with the same flight number have the same origin and destination, but differ in the departure date. At flight check-in, the passenger is issued a boarding pass on which the seat number is indicated. The passenger can only check in for the flight if this flight is included in the ticket. The combination of passenger and seat must be unique to avoid issuing two boarding passes for the same seat. The number of seats (seats) in the aircraft and their distribution among different classes depends on the model of the aircraft (aircrafts) making the flight. It is assumed that each aircraft model has only one cabin configuration.

Thus, the initial situation would be clear. The task now is to make the booking system easier to integrate with the help of Reedelk. In addition to the aforementioned integration platform, IntelliJ, OpenAPI and Docker are also used. In order to start, a Reedelk project is created in IntelliJ.

Create a Reedelk project

Within the project, an appropriate PostgreSQL database, which represents the legacy system, is made available via Docker and a Docker Compose file.

version: "3"
services:
  bookings:
    image: postgres:11.7-alpine
    environment:
      - POSTGRES_HOST_AUTH_METHOD=trust
    ports:
      - 5432:5432
    volumes:
      - ./db/10_init.sql:/docker-entrypoint-initdb.d/10_init.sql 

API first

Next, the OpenAPI specification for the bookings entity is created. The result is shown in the following codeblock.

openapi: 3.0.3
info:
  title: Bookings API
  description: API for Bookings
  version: 0.1.0
servers:
  - url: http://localhost
paths:
  /bookings:
    get:
      summary: Returns a list of bookings.
      description: A list of bookings.
      responses:
        '200':
          description: OK
          content:
            application/json:
              schema:
                type: array
                items:
                  $ref: '#/components/schemas/Booking'
              example:
                - book_ref: 00004A
                  book_date: 2016-10-13 18:57:00.000000
                  total_amount: 29000.00
                - book_ref: 00006A
                  book_date: 2016-11-05 02:02:00.000000
                  total_amount: 106100.00
        '404':
          description: Error
    post:
      summary: Creates new booking.
      description: A new booking is created.
      requestBody:
        content:
          application/json:
            schema:
              $ref: '#/components/schemas/Booking'
            example:
              book_ref: 12345A
              book_date: 2020-08-26 07:51:00.000000
              total_amount: 10000.00
      responses:
        '201':
          description: Booking created
        '404':
          description: Error
  /bookings/{booking_ref}:
    get:
      summary: Returns a booking by booking_ref.
      description: An unique booking by booking_ref.
      responses:
        '200':
          description: OK
          content:
            application/json:
              schema:
                type: array
                items:
                  $ref: '#/components/schemas/Booking'
              examples:
                bookingGETExample:
                  $ref: '#/components/examples/bookingGetExample'
        '404':
          description: Error
    put:
      summary: Updates an existing booking.
      description: An existing booking will be updated.
      requestBody:
        content:
          application/json:
            schema:
              $ref: '#/components/schemas/Booking'
            examples:
              bookingPUTExample:
                $ref: '#/components/examples/bookingPutExample'
      responses:
        '200':
          description: OK
        '404':
          description: Error
components:
  schemas:
    Booking:
      properties:
        book_ref:
          type: string
        book_date:
          type: string
          format: 'date-time'
        total_amount:
          type: number
          format: double
  examples:
    bookingGetExample:
      value:
        book_ref: 00006A
        book_date: 2016-11-05 02:02:00.000000
        total_amount: 106100.00
    bookingPutExample:
      value:
        book_ref: 00006A
        book_date: 2016-11-05 02:02:00.000000
        total_amount: 10600.00

The API import tool integrated in the Flow Designer is currently only able to process the sample data as inline samples.

Via Tools\Import Open API the following four flows can now be generated from the spec.

  • GETBookings
  • GETBookingsBooking_ref
  • POSTBookings
  • PUTBookingsBooking_ref
Import Open API Spec

By using the API-first approach, the corresponding examples from the specification were stored within the flow in addition to the flows, so that the integration service created can be made available directly to consumers of the service in the stage for development and testing, as a container using Docker, for example. This now also enables asynchronous and parallel development of clients and service.

Let the integration flow

At this point, the task is to create the connection to the database for GETBookings flow. This can be done by the Flow Designer or in a textual way. A flow is always based on an ‘event’. This can be a consumer, listener or scheduler. This ‘event’ then triggers a chain of further actions.

Generated flow - Designer view
Get all Bookings - SQL View Properties

First of all, access to the database is now required. For this purpose the SQL select component is dragged into the flow. In the next step the added component has to be configured. To do so, a global configuration file for the connection to the database is created in the Flow Designer interface. Afterwards only the appropriate SQL statement must be entered.

Get all Bookings - SQL Properties

As the data should be transferred to the client in JSON format, the Object-to-JSON component is required and must be placed behind the SQL component in the flow by means of drag-and-drop.

<img title="finalized Get all bookings flow" src="flow_get_bookings.png" />

The first flow for querying all bookings through an API is now complete. This is a simple flow that does not contain any business logic. It transforms the data into the JSON format.

By starting the Reedelk Runtime, the created flow can be tested locally. To test the API endpoint, Insomnia Designer will be used, as it allows importing the specification via a URL. The Reedelk Runtime provides such an endpoint for the created service under the URL http:/localhost:8484/v1/openapi.yaml.

Insomnia Designer - Documents view

When the GET endpoint is queried, the database is relatively big. The table bookings contains over 2 million entries. The specification already contains the endpoint bookings/{booking_ref}, which can be used to query the booking for a specific booking reference. We can replace the existing mock with two components (SQL Select and Object-to-JSON), so this flow also returns a desired JSON as response. The select statement in the SQL Select component has to be configured according to the screenshot.

Get Booking by booking_ref - SQL Properties

Two integration flows have already been created with the Reedelk Runtime and Platform, repsectively, based on an OpenAPI specification. The challenge of the large amount of data for the endpoint /bookings will be considered separately in an upcoming blog post.

Creating a service for the cloud

Finally the created integration service should be provided as a container. Based on Maven, this is not too much effort. With mvn clean package, a package is created. A Docker file is already created when the project is started.

FROM reedelk/reedelk-runtime-ce:1.0.5
COPY target/*.jar /opt/reedelk-runtime/modules
COPY reedelk-runtime/lib/*.jar /opt/reedelk-runtime/lib
CMD runtime-start

In the next step the image can be built and then started.

$ docker build -t booking-integration-service:0.1.0
$ docker run -p 9988:9988 -p 8484:8484 booking-integration-service:0.1.0

Thus, the use case is solved for two API endpoints. The integration service is available, but access is currently completely uncontrolled. A proper control can be achieved by using an API gateway. An upcoming blog post will show what this could look like for the described use case. The sources for demo project are available at GitHub.

Daniel Kocot

Daniel has been a member of the codecentric team in Solingen since October 2016. Since the beginning of the 2000s he has dedicated himself to the topic of Digital Transformation. In addition to his current focus on API Management, Application Lifecycle Management Tooling, Continuous Documentation and Voice UI, he is also an expert in the use of product information systems (PIM) and database publishing using rendering technologies.

Danijel Dragicevic

Danijel has been a member of the codecentric team in Bosnia since April 2014. His current focus is on Enterprise Integrations using the API-led connectivity approach and MuleSoft technologies.

Comment

Your email address will not be published. Required fields are marked *