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.
- 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 usermyuser
and passwordmyuser
to login.
- create
- 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
- 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
- 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
- create folder
- Verify that the created data source works
- navigate to the
Database
perspective by clicking on theDatabase
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;
- navigate to the
- Define a table in our application which will hold the replicated orders
- create file
orders.table
in folderdb
- 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 theDefaultDB
data source
You may need to click on theRefresh
button to see the newly created table.
- create file
- Implement ETL using JDBC
- create folder named
sync
- create file named
sync-orders-jdbc.camel
in the created folder - open the file
- add cron to trigger the execution regularly
- select the route and update the description to
Sync orders from OpenCart
- add step which logs that the synchronization has started
- set property for the USD to EUR exchange rate
- next, we have to select all orders from the OpenCart data source
- now, the camel body will contain all orders
- split the body into single orders
- 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. - execute the merge statement
- add log step for completed
- now the JDBC implementation is done
- publish the project from the
Publish All
button - 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 tableORDERS
inDefaultDB
data source - it should contain the replicated orders with converted total in EUR Note: if you had problems to model thesync-orders-jdbc.camel
, you can get a working content of the file from here
- check the console for output from the log steps which we added
- create folder named
- Implement ETL using TypeScript
- create file
sync-orders-typescript.camel
in directorysync
- open the created file
- add cron to trigger the execution regularly
- select the route and update the description to
Sync orders from OpenCart
- add step which logs that the synchronization has started
- 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
insync
folder using this content - in
sync-orders-typescript.camel
addSet Property
step - add step
Class
- create a folder named
- now, the camel body will contain all orders
- split the body into single orders
- merge order using TypeScript
- create file
merger-order.ts
in foldersync
- set this file content
- in
sync-orders-typescript.camel
addSet Property
step - add step
Class
- create file
- add log step for completed
- now the TypeScript implementation is done
- publish the project from the
Publish All
button - 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 tableORDERS
inDefaultDB
data source - it should contain the replicated orders with converted total in EUR Note: if you had problems to model thesync-orders-typescript.camel
, you can get a working content of the file from here
- check the console for output from the log steps which we added
- create file
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.