Akmal ChaudhriAbstract
During the 1970s, the three-step process to Extract, Transform, and Load (ETL) became popular when working with data warehouses. As processing power has significantly improved and cloud-based systems have become more powerful and cost-effective, Extract, Load and Transform (ELT) has become possible. This article will demonstrate using SingleStoreDB with one popular open-source ELT tool, Airbyte.
Introduction
Airbyte provides source and destination connectors that enable the construction of pipelines between different systems. In this article, we'll use Airbyte's SingleStore source connector and send the data to the End-to-End Testing (/dev/null) destination connector.
Create a SingleStoreDB Cloud account
A previous article showed the steps required to create a free SingleStoreDB Cloud account. We'll use Airbyte Demo Group as our Workspace Group Name and airbyte-demo as our Workspace Name. We'll make a note of our password and host name.
Create the database and table
We'll use a subset of the inventory system example from a previous article, as it provides a combination of both Relational and JSON data. In the SQL Editor in SingleStoreDB Cloud, we'll create a database and a table:
CREATE DATABASE IF NOT EXISTS e_store;
USE e_store;
DROP TABLE IF EXISTS products;
CREATE TABLE IF NOT EXISTS products (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(250) NOT NULL,
brand_id INT UNSIGNED NOT NULL,
category_id INT UNSIGNED NOT NULL,
attributes JSON NOT NULL,
PRIMARY KEY(id),
INDEX CATEGORY_ID(category_id ASC),
INDEX BRAND_ID(brand_id ASC)
);
Populate the database table
Let's now populate the products table:
-- Televisions
INSERT INTO products (name, brand_id, category_id, attributes) VALUES
('Prime', '1', '1', '{"screen" : "50 inch", "resolution" : "2048 x 1152 pixels", "ports" : {"hdmi" : 1, "usb" : 3}, "speakers" : {"left" : "10 watt", "right" : "10 watt"}}'),
('Octoview', '1', '1', '{"screen" : "40 inch", "resolution" : "1920 x 1080 pixels", "ports" : {"hdmi" : 1, "usb" : 2}, "speakers" : {"left" : "10 watt", "right" : "10 watt"}}'),
('Dreamer', '1', '1', '{"screen" : "30 inch", "resolution" : "1600 x 900 pixels", "ports" : {"hdmi" : 1, "usb" : 1}, "speakers" : {"left" : "10 watt", "right" : "10 watt"}}'),
('Bravia', '1', '1', '{"screen" : "25 inch", "resolution" : "1366 x 768 pixels", "ports" : {"hdmi" : 1, "usb" : 0}, "speakers" : {"left" : "5 watt", "right" : "5 watt"}}'),
('Proton', '1', '1', '{"screen" : "20 inch", "resolution" : "1280 x 720 pixels", "ports" : {"hdmi" : 0, "usb" : 0}, "speakers" : {"left" : "5 watt", "right" : "5 watt"}}');
-- Mobile Phones
INSERT INTO products (name, brand_id, category_id, attributes) VALUES
('Desire', '2', '2', JSON_BUILD_OBJECT("network",
JSON_ARRAY_PUSH_STRING('["GSM", "CDMA", "HSPA"]', 'EVDO'),
"body",
"5.11 x 2.59 x 0.46 inches",
"weight",
"143 grams",
"sim",
"Micro-SIM",
"display",
"4.5 inches",
"resolution",
"720 x 1280 pixels",
"os",
"Android Jellybean v4.3"
)
),
('Passion', '2', '2', JSON_BUILD_OBJECT("network",
JSON_ARRAY_PUSH_STRING('["GSM", "CDMA"]', 'HSPA'),
"body",
"6.11 x 3.59 x 0.46 inches",
"weight",
"145 grams",
"sim",
"Micro-SIM",
"display",
"4.5 inches",
"resolution",
"720 x 1280 pixels",
"os",
"Android Jellybean v4.3"
)
),
('Emotion', '2', '2', JSON_BUILD_OBJECT("network" ,
JSON_ARRAY_PUSH_STRING('["GSM", "CDMA"]', 'EVDO'),
"body",
"5.50 x 2.50 x 0.50 inches",
"weight",
"125 grams",
"sim",
"Micro-SIM",
"display",
"5.00 inches",
"resolution",
"720 x 1280 pixels",
"os",
"Android KitKat v4.3"
)
),
('Sensation', '2', '2', JSON_BUILD_OBJECT("network",
JSON_ARRAY_PUSH_STRING('["GSM", "HSPA"]', 'EVDO'),
"body",
"4.00 x 2.00 x 0.75 inches",
"weight",
"150 grams",
"sim",
"Micro-SIM",
"display",
"3.5 inches",
"resolution",
"720 x 1280 pixels",
"os",
"Android Lollipop v4.3"
)
),
('Joy', '2', '2', JSON_BUILD_OBJECT("network",
JSON_ARRAY_PUSH_STRING('["CDMA", "HSPA"]', 'EVDO'),
"body",
"7.00 x 3.50 x 0.25 inches",
"weight",
"250 grams",
"sim",
"Micro-SIM",
"display",
"6.5 inches",
"resolution",
"1920 x 1080 pixels",
"os",
"Android Marshmallow v4.3"
)
);
-- Cameras
INSERT INTO products (name, brand_id, category_id, attributes) VALUES
('Explorer', '3', '3', '{"sensor_type" : "CMOS", "processor" : "Digic DV III", "scanning_system" : "progressive", "mount_type" : "PL", "monitor_type" : "LCD"}'),
('Runner', '3', '3', '{"sensor_type" : "CMOS", "processor" : "Digic DV II", "scanning_system" : "progressive", "mount_type" : "PL", "monitor_type" : "LED"}'),
('Traveler', '3', '3', '{"sensor_type" : "CMOS", "processor" : "Digic DV II", "scanning_system" : "progressive", "mount_type" : "PL", "monitor_type" : "LCD"}'),
('Walker', '3', '3', '{"sensor_type" : "CMOS", "processor" : "Digic DV I", "scanning_system" : "progressive", "mount_type" : "PL", "monitor_type" : "LED"}'),
('Jumper', '3', '3', '{"sensor_type" : "CMOS", "processor" : "Digic DV I", "scanning_system" : "progressive", "mount_type" : "PL", "monitor_type" : "LCD"}');
Install and configure Airbyte
We'll install open-source Airbyte using the instructions on the website:
After the deployment is complete, we'll launch a web browser and enter http://localhost:8000.
Create Source
Once logged in, we'll select Sources from the left-hand navigation pane and create a source.
We'll choose SingleStore from the Marketplace.
On the Create a source page, here is what we need to enter:
- Source name: SingleStore
- Host: <host>
- Port: 3306
- Database: e_store
- Username: admin
- Optional fields > Password: <password>
We'll replace the <host> and <password> with the values from our SingleStoreDB Cloud account.
Next, we'll click the Set up source button. The connection should be successful.
Create Destination
We'll select Destinations from the left-hand navigation pane and create a destination.
We'll choose End-to-End Testing (/dev/null) from the Marketplace.
On the Create a destination page, we'll click the Set up destination button. The connection should be successful.
Create Connection
If not already done so, we'll link our source to our destination, using the Create a connection button.
We'll take the recommended Replicate Source option and choose Full Refresh | Overwrite for the sync mode. Then we'll click Next.
Under Configure connection, we have several options that can be changed, such as the Replication frequency. When ready, we'll click Finish & Sync. The sync should start immediately. Shortly afterwards, we should see that the sync successfully completed.
We can also test many other destinations.
Summary
In this article, we have seen that we can easily use SingleStoreDB as a source in Airbyte. We successfully replicated the data from a SingleStoreDB Cloud database to End-to-End testing. Further tests could include other destinations available in Airbyte.
