codbex

Appearance

Iapetus - Implement ETL for Sales Orders ​

Iapetus, powered by Apache Camel, simplifies the implementation of various ETL (Extract, Transform, Load) scenarios.

In this tutorial, I'm going to show you how you can implement orders ETL from an instance of the free open source e-commerce OpenCart to your database.

Orders ETL scenario ​

Let's have the following use cases:

  • we have an online store (instance of OpenCart) which sells goods
  • we want to regularly replicate all orders from the store to our database for analytic purposes
  • OpenCart order amounts are stored in USD
  • we want to convert the total amounts from USD to EUR
  • only order details like total amount and date added are needed

Implementation steps ​

Follow the steps below or watch the recorded video.

  1. Start an OpenCart instance using Docker

    • create docker-compose.yml file with MariaDB and OpenCart images in a directory with this content.
    • start images
    shell
    # adjust the path to the docker-compose.yml file path
export DOCKER_COMPOSE_PATH='./docker-compose.yml' 
export OPENCART_USERNAME='myuser'
export OPENCART_PASSWORD='myuser'
export OPENCART_DATABASE_PORT_NUMBER='3306'
export OPENCART_DATABASE_USER='bn_opencart'
export OPENCART_DATABASE_PASSWORD='bitnami'
export OPENCART_DATABASE_NAME='bitnami_opencart'
docker-compose -f "$DOCKER_COMPOSE_PATH" up -d
    oc-homepage.png

  2. Start an Iapetus instance and connect it to the OpenCart's network

    shell
    WORKSPACE_DIR='/tmp/iapetus'
IMAGE_VERSION='1.0.1' # use version 1.0.1 or later

docker run --name codbex-iapetus --rm -p 8080:80 \
  --network opencart_network \
  -v "$WORKSPACE_DIR:/target/dirigible" \
  ghcr.io/codbex/codbex-iapetus:$IMAGE_VERSION

  3. Open Iapetus and create a project named orders-etl

  • open Iapetus at http://localhost:8080

  • login using the default user - username: admin, password: admin

  • right-click on workbench and click on New Project

    new-project.png

  • type orders-etl for project name

    set-project-name.png

  • click on Create button

  • a blank project will be created

    blank-project.png
  1. Create datasource to the OpenCart database which will be used to extract data
  • create folder datasources
  • create file OpenCartDB.datasource in the created folder
  • and add this content
  1. Verify that the created data source works

  • navigate to the Database perspective by clicking on the Database button

    db-perspective-button.png

  • select OpenCartDB data source

    oc-ds-btn.png

  • expand schema bitnami_opencart

  • expand Tables

  • you should be able to see all OpenCart tables

  • here you can find the table oc_order which contains all orders

  • verify that you are able to query its content

    sql
    select order_id, total, date_added from oc_order;
    oc_order_content.png
  1. Define a table in our application which will hold the replicated orders

    • create file orders.table in folder db

    • right-click on the file and select Open With -> Code Editor

    • paste this content

    • save the file

    • publish the project by clicking on Publish All button

      publish-all-button.png

    • after a few seconds, open the Database perspective and check whether the table is created in the DefaultDB data source

      orders-table.png


      You may need to click on the Refresh button to see the newly created table.

  2. Implement ETL using JDBC
    1. create folder named sync
    2. create file named sync-orders-jdbc.camel in the created folder
    3. open the file
    4. add cron to trigger the execution regularly

    • click on Create route button

      create-route-btn.png

    • search for cron in the Components tab and select it

      cron-component.png

    • set Trigger Orders Replication for description

    • under component properties set TriggerOrdersReplication for name and add schedule 0 * * ? * * (every minute at 0 seconds)

      cron-config.png

    • save the file with (cmd + s)

    1. select the route and update the description to Sync orders from OpenCart

      route-config.png

    2. add step which logs that the synchronization has started

      • add Log step after the cron using the arrow button

        add-step-after-cron.pnglog-step.png

      • set Log starting for description

      • set Replicating orders from OpenCart using JDBC... for message

      • log level to INFO

      • set OpenCartOrdersReplication for Log Name

        log-starting-config.png

      • save the file

    3. set property for the USD to EUR exchange rate

      • add Set Property step after the log step

        set-property-step.png

      • set expression 0.92

      • set Set USD to EUR exchange rate property for description

      • set currencyExchangeRate for name

        exchange-property-config.png

      • save the file

    4. next, we have to select all orders from the OpenCart data source

      • build the select query

        • add step Set Body

          set-body-step.png

        • set expression SELECT * FROM oc_order

        • set description Create orders SELECT statement

          select-orders-body-step-config.png

        • save the file

      • execute the select query

        • add Spring JDBC step

          spring-jdbc-step.png

        • set description Get all orders

        • set OpenCartDB for data source name

          spring-jdbc-execute-config.png

        • save the file

    5. now, the camel body will contain all orders

    6. split the body into single orders

      • add Split step

        split-step.png

      • set expression to ${body}

      • set Split to single order for description

        split-step-config.png

    7. build merge statement for each order
      We are using the extracted data from the OpenCart database to create the statement. Total is converted from USD to EUR using the defined exchange rate property.

      • add Set Body step

      • set the following value for expression

        sql
        MERGE INTO ORDERS
  (ID, TOTAL, DATEADDED)
KEY(ID)
VALUES
(
  ${body['order_id']},
  ${body['total']} * ${exchangeProperty.currencyExchangeRate},
  '${body['date_added']}'
);

      • set Create MERGE statement for description

        merge-statement-config.png

    8. execute the merge statement

      • add Spring JDBC step

      • set description Merge order

      • set DefaultDB for data source name

        execute-merge-config.png

    9. add log step for completed

      • add log step after the split

        step-after-split.png

      • set Log completed for description

      • set Successfully replicated orders from OpenCart using JDBC for message

      • set logging level to INFO

      • set OpenCartOrdersReplication for log name

        log-completed-config.png

      • save the file

    10. now the JDBC implementation is done

    11. publish the project from the Publish All button

    12. verify that the synchronization works

      • check the console for output from the log steps which we added

        jdbc-sync-console-logs.png

      • go to Database perspective and check the content of table ORDERS in DefaultDB data source - it should contain the replicated orders with converted total in EUR

        jdbc-orders-table.png

        Note: if you had problems to model the sync-orders-jdbc.camel, you can get a working content of the file from here

  3. Implement ETL using TypeScript

    1. create file sync-orders-typescript.camel in directory sync

    2. open the created file

    3. add cron to trigger the execution regularly

      • click on Create route button

      • search for Cron component and select it

      • set Trigger Orders Replication for description

      • under component properties set TriggerOrdersReplication for name and add schedule 30 * * ? * * (every minute at 30 seconds)

        cron-ts-config.png

      • save the file

    4. select the route and update the description to Sync orders from OpenCart

      ts-route-config.png

    5. add step which logs that the synchronization has started

      • add Log step after the cron

      • set Log starting for description

      • set Replicating orders from OpenCart using TypeScript... for message

      • log level to INFO

      • set OpenCartOrdersReplication for log name

        <img src="/images/2024-08-19-orders-etl/ts-log-starting-config.png" alt="ts-log-starting-config.png">

      • save the file

    6. get all orders from OpenCart and current exchange rate from the frankfurter API using TypeScript

      • create a folder named dao

      • create file oc_orderRepository.ts in the created folder

      • open the file

      • set this content
        Note: the code of this dao is automatically generated using another awesome codbex functionality, but I will give you more details about this in another blog post.

        ts-oc-dao-file.png

      • create file get-all-orders.ts in sync folder using this content

      • in sync-orders-typescript.camel add Set Property step

        • set expression orders-etl/sync/get-all-orders.ts

        • set description Set get-all-orders.ts file

        • set name resource

          ts-set-get-all-orders.png

      • add step Class

        class-step.png

        • set description Get all OpenCart orders

        • set bean name org.eclipse.dirigible.components.engine.camel.invoke.Invoker

          ts-get-all-stores-execution.png

    7. now, the camel body will contain all orders

    8. split the body into single orders

      • add Split step

      • set expression to ${body}

      • set Split to single order for description

        ts-split-step-config.png

    9. merge order using TypeScript

      • create file merger-order.ts in folder sync

      • set this file content

      • in sync-orders-typescript.camel add Set Property step

        • set expression orders-etl/sync/merger-order.ts

        • set description Set merger-order.ts file

        • set name resource

          ts-set-merge-ts-config.png

      • add step Class

        • set description Merge order

        • set bean name org.eclipse.dirigible.components.engine.camel.invoke.Invoker

          ts-merge-order-execution.png

    10. add log step for completed

      • add Log step after the split

      • set Log completed for description

      • set Successfully replicated orders from OpenCart using TypeScript for message

      • set logging level to INFO

      • set OpenCartOrdersReplication for log name

        ts-log-completed-config.png

    11. now the TypeScript implementation is done

    12. publish the project from the Publish All button

    13. verify that the synchronization works

      • check the console for output from the log steps which we added

        ts-sync-console-logs.png

      • go to Database perspective and check the content of table ORDERS in DefaultDB data source - it should contain the replicated orders with converted total in EUR

        ts-orders-table.png

        Note: if you had problems to model the sync-orders-typescript.camel, you can get a working content of the file from here


Congratulations, you have implemented the ETL scenario in two different ways!

Summary ​

Using Iapetus you can

The project we implemented can be found in this GitHub repository.

I hope you enjoyed this blog. Stay tuned for more great functionality by codbex!
If you have any questions, ideas or want to contribute, feel free to contact us.