ETL end to end testing with docker, NoSQL, RDBMS and Big Query.

End to end testing

ETL

Docker

ETL application

curl -d "@transfer.json" -X POST http://localhost:8080/v1/api/transfer
{

"Mode": "insert",
"Source": {
"DriverName": "mysql", ... other connection details
"Query": "SELECT * FROM source_table"
},


"Dest": {
"DriverName": "bigquery", ... other connection details
"Table": "target_table"
}

}
curl http://127.0.0.1:8080/v1/api/tasks
cd /tmp/ && git clone https://github.com/adrianwit/dstransfer 
cd dstransfer
docker build -t adrianwit/dstransfer:0.1.0 .
cd /tmp/dstransfer/config/ && docker-compose up  -d
https://endly-external.appspot.com/
https://github.com/adrianwit/dstransfer-e2e/tree/master/initial
cd ~/Download/dstransfer/e2e
endly -t='init,test'
docker ps
docker exec -it endly_db1  aql
show sets
SELECT * FROM dummy_type
SELECT * FROM db2.dummy_type
docker exec -it endly_db3 mysql -uroot -p 
USE db3;
SELECT * FROM dummy_type
curl http://127.0.0.1:8080/status
https://github.com/adrianwit/dstransfer-e2e/tree/master/initial/dstransfer/e2e/regression
{
"id": 1,
"name": "user 1",
"visited":"2018-01-15 08:02:23 UTC",
"perf": {
"rank": 100,
"score": 6.5
},
"quiz": {
"1": {
"id": 1,
"score": 10,
"taken": "2018-01-10 16:02:01 UTC"
},
"2": {
"id": 2,
"score": 3,
"taken": "2018-01-15 08:02:23 UTC"
}
}
}
CREATE OR REPLACE TABLE users (
id INT64 NOT NULL,
name STRING NOT NULL,
visited TIMESTAMP,
perf STRUCT<
rank INT64,
score FLOAT64
>,
quiz ARRAY<STRUCT<
key STRING,
value STRUCT<
id INT64,
score INT64,
taken TIMESTAMP
>>>
);
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
perf_rank INT,
perf_score DECIMAL(7, 2),
quiz TEXT,
visited TIMESTAMP
);
{
"Method": "post",
"Request": "$request",
"URL": "http://localhost:8080/v1/api/transfer",
"Expect": {
"Status": "ok"
}
]
}
{
"Mode": "insert",
"Source": {
"DriverName": "bigquery",
"Query": "SELECT * FROM users"
},
"Dest": {
"DriverName": "mysql",
"Table": "users"
}
}
{
"Method": "get",
"URL": "http://localhost:8080//v1/api/task/${taskId}",
"Expect": {
"Status": "done"
},
"Variables": [
{
"Name": "status",
"From": "Status"
}
],
"Repeat": 10,
"SleepTimeMs": 3000,
"Exit": "$status:!/running/"
}

Use cases

https://github.com/adrianwit/dstransfer-e2e/tree/master/final/dstransfer/e2e/regression/use_cases
SELECT id, name, visited, perf, ARRAY(quiz) AS quiz FROM users
SELECT id, name, TIMESTAMP(visited) AS visited, perf.rank AS perf_rank, perf.score AS perf_score, TO_JSON_STRING(quiz) AS quiz  FROM users
SELECT id, name, FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', visited) AS visited, perf.score AS perf_score, perf.rank AS perf_rank, TO_JSON_STRING(quiz) AS quiz FROM users

Working with endly

endly 
endly -t=init
endly -t=test
endly -t=test -i=rdbms_basic
endly -t=test -d=true -i=nosql_to_bigquery
Endly runner output

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store