How to generate time-series data in QuestDB

Types of time series data

What is mocking and why is it useful?

Running QuestDB

docker run -p 9000:9000 \
-p 9009:9009 \
-p 8812:8812 \
questdb/questdb
brew install questdb
brew services start questdb

Mocking different types of data in QuestDB

SELECT <COLUMNS> FROM long_sequence(<NUMBER_OF_ROWS>[, <SEED1>, <SEED2>]]);
SELECT x, x*x FROM long_sequence(5);

Generator functions in QuestDB

SELECT round(a,2), a FROM (SELECT rnd_double() a FROM long_sequence(10));
-- creating the table first
CREATE TABLE test(val double);
-- populating the test table
INSERT INTO test SELECT * FROM (SELECT rnd_double() FROM long_sequence(10));
-- querying generated data
SELECT round(val,2) FROM test;

Creating realistic generative data

CREATE TABLE 'diagnostics' (
name STRING, -- identifier of the truck
fleet STRING, -- division where the truck belongs to
driver STRING, -- the name of the driver who drove the track on the given day
model STRING, -- the model of the truck
device_version STRING, -- version number of the data collector device
load_capacity INT, -- maximum load capacity of the truck at the beginning of the shift
fuel_capacity INT, -- fuel capacity of the truck at the beginning of the shift
nominal_fuel_consumption INT, -- fuel consumption at the given time
fuel_state DOUBLE, -- the current state of the fuel level: 1 is full, 0 is empty
current_load INT, -- the weight of the current load
status INT, -- status of the truck
ts TIMESTAMP -- timestamp the diagnostic entry got recorded
) timestamp(ts) PARTITION BY year;
INSERT INTO 'diagnostics' SELECT * FROM (
SELECT
rnd_str('truck_1234', 'truck_5678', 'truck_9123', 'truck_3210'),
rnd_str('North', 'East', 'South', 'West'),
rnd_str('Alice', 'Bob', 'John', 'Mike', 'Robert'),
rnd_str('G-0', 'H-2', 'I-1'),
rnd_str('v1.2', 'v1.3'),
rnd_int(1000, 1500, 0),
rnd_int(150, 200, 0),
rnd_int(0, 15, 0),
rnd_double(),
rnd_int(0, 1500, 0),
rnd_int(0, 4, 0),
rnd_timestamp(
to_timestamp('2022-01-01T00:00:00', 'yyyy-mm-ddTHH:mm:ss'),
to_timestamp('2022-02-28T23:00:00', 'yyyy-mm-ddTHH:mm:ss'),
0
)
FROM long_sequence(28)
);
SELECT
timestamp_sequence(
to_timestamp('2022-02-01T00:00:00', 'yyyy-mm-ddTHH:mm:ss'),
3600000000L
)
FROM long_sequence(10);
INSERT INTO 'diagnostics' SELECT * FROM (
SELECT
rnd_str('truck_1234', 'truck_5678', 'truck_9123', 'truck_3210'),
rnd_str('North', 'East', 'South', 'West'),
rnd_str('Alice', 'Bob', 'John', 'Mike', 'Robert'),
rnd_str('G-0', 'H-2', 'I-1'),
rnd_str('v1.2', 'v1.3'),
rnd_int(1000, 1500, 0),
rnd_int(150, 200, 0),
rnd_int(0, 15, 0),
rnd_double(),
rnd_int(0, 1500, 0),
rnd_int(0, 4, 0),
timestamp_sequence(
to_timestamp('2022-01-01T00:00:00', 'yyyy-mm-ddTHH:mm:ss'),
3600000000L
)
FROM long_sequence(28)
);

Summary

--

--

--

Passionate about web applications, clean code and architecture design.

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Growing Your Monolith

Easier, More Accurate Feature Timeline Estimations

Visual Studio Code custom settings to improve productivity

Gradual Deployment of Web Apps with CloudFront, S3, Lambda and Cookies

A detail guide to deploying Elasticsearch on Elastic Cloud on Kubernetes (ECK)

Regional Governance Models to Enable MaaS & Open Mobility Marketplaces

School Management Application

Pandas parallelize speed up process

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
Gábor Boros

Gábor Boros

Passionate about web applications, clean code and architecture design.

More from Medium

PostgreSQL Database Set-Up on MacOS: PGAdmin and Postegreapp

Lambda Function in Python

GraphQL using Python

Python Standard Library Part 2: Zip Files