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

   # 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 
   

   • open created OpenCart instance at http://localhost:80/
   • register a new store user from http://localhost:80/index.php?route=account/register
   • create a few orders with different products and amounts
   • you can check all store orders in the OpenCart admin UI.
     Use user myuser and password myuser to login.
2. Start an Iapetus instance and connect it to the OpenCart’s network
   

   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
   • type orders-etl for project name
   • click on Create button
     
   • a blank project will be created
     
4. 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
5. Verify that the created data source works
   • navigate to the Database perspective by clicking on the Database button
   • select OpenCartDB data source
   • 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

      select order_id, total, date_added from oc_order;
     

     

6. 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
   • after a few seconds, open the Database perspective and check whether the table is created in the DefaultDB data source
     
     You may need to click on the Refresh button to see the newly created table.
7. 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
      • search for cron in the Components tab and select it
      • set Trigger Orders Replication for description
      • under component properties set TriggerOrdersReplication for name and add schedule 0 * * ? * * (every minute at 0 seconds)
      • save the file with (cmd + s)
   5. select the route and update the description to Sync orders from OpenCart
   6. add step which logs that the synchronization has started
      • add Log step after the cron using the arrow button
      • set Log starting for description
      • set Replicating orders from OpenCart using JDBC... for message
      • log level to INFO
      • set OpenCartOrdersReplication for Log Name
      • save the file
   7. set property for the USD to EUR exchange rate
      • add Set Property step after the log step
      • set expression 0.92
      • set Set USD to EUR exchange rate property for description
      • set currencyExchangeRate for name
      • save the file
   8. next, we have to select all orders from the OpenCart data source
      • build the select query
        • add step Set Body
        • set expression SELECT * FROM oc_order
        • set description Create orders SELECT statement
        • save the file
      • execute the select query
        • add Spring JDBC step
        • set description Get all orders
        • set OpenCartDB for data source name
        • save the file
   9. now, the camel body will contain all orders
   10. split the body into single orders
       • add Split step
       • set expression to ${body}
       • set Split to single order for description
   11. 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

         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
   12. execute the merge statement
       • add Spring JDBC step
       • set description Merge order
       • set DefaultDB for data source name
   13. add log step for completed
       • add log step after the split
       • 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
       • save the file
   14. now the JDBC implementation is done
   15. publish the project from the Publish All button
   16. verify that the synchronization works
       • check the console for output from the log steps which we added
         
       • 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 Note: if you had problems to model the sync-orders-jdbc.camel, you can get a working content of the file from here
8. 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)
      • save the file
   4. select the route and update the description to Sync orders from OpenCart
   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
      • 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.
      • 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
      • add step Class
        • set description Get all OpenCart orders
        • set bean name org.eclipse.dirigible.components.engine.camel.invoke.Invoker
   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
   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
      • add step Class
        • set description Merge order
        • set bean name org.eclipse.dirigible.components.engine.camel.invoke.Invoker
   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
   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
         
       • 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 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

• easily implement ETL scenarios
• use all available Apache Camel functionalities
• benefit from the codbex platform, tooling and modules
• use the comprehensive codbex SDK which uses different modern open source projects for messaging, jobs scheduling, REST, OData, mails etc.

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.

Iliyan likes SUP, rifle shooting and fishing.

« Hyperion - Implement a BPM application for leave requests Deploy codbex products on Snowflake »