Demo Presentation Website

Data Warehouse for ClickHouse® examples

Basic commands

Query descriptionQuery command
Show databasesshow databases;
Show tablesshow tables;
Describe tabledescribe table noaa;
Table sizeselect count() from noaa;
Select tableselect * from noaa limit 10;

Basic aggregations

Query descriptionQuery command
Row count and date rangeSELECT
count() AS total_rows,
min(date) AS earliest_date,
max(date) AS latest_date
FROM noaa;
Weather type distributionSELECT
weatherType,
count() AS occurrences,
round(avg(tempAvg) / 10.0, 2) AS avg_temp_celcius
FROM noaa
GROUP BY weatherType
ORDER BY occurrences DESC;
Daily precipitation sum across all stations in the last 10 daysSELECT
date,
sum(precipitation) / 10.0 AS total_precipitation_mm,
count() AS stations_reporting
FROM noaa
GROUP BY date
ORDER BY date DESC
LIMIT 10;
Max wind speed by elevation bracketSELECT
floor(elevation / 500) * 500 AS elevation_bracket,
count() AS readings,
max(maxWindSpeed) / 10.0 AS max_wind_ms,
avg(averageWindSpeed) / 10.0 AS avg_wind_ms
FROM noaa
GROUP BY elevation_bracket
ORDER BY elevation_bracket;

Cluster configuration

Query descriptionQuery command
Show table storage sizeSELECT
disk_name,
formatReadableSize(sum(data_compressed_bytes) AS size) AS compressed,
formatReadableSize(sum(data_uncompressed_bytes) AS usize) AS uncompressed,
round(usize / size, 2) AS compr_rate,
sum(rows) AS rows
FROM system.parts
WHERE (active = 1) AND (table = ‘noaa’)
GROUP BY
disk_name
ORDER BY size DESC;
Check cluster configurationSELECT
cluster,
host_name,
shard_num,
replica_num
FROM system.clusters
WHERE cluster = ‘scw’;

Query federation

S3 (Parquet)

Query descriptionQuery command
Describe the tableDESCRIBE TABLE
(
SELECT *
FROM s3(‘https://dtwh-demo.s3.fr-par.scw.cloud/flights-1m.parquet’)
LIMIT 10
);
Simple SELECT querySELECT *
FROM s3(‘https://dtwh-demo.s3.fr-par.scw.cloud/flights-1m.parquet’)
LIMIT 10;
Count number of rowsSELECT formatReadableQuantity(count(*))
FROM s3(‘https://dtwh-demo.s3.fr-par.scw.cloud/flights-1m.parquet’);
Perform basic aggregationsSELECT avg(DISTANCE)
FROM s3(‘https://dtwh-demo.s3.fr-par.scw.cloud/flights-1m.parquet’);
Create a remote table and perform aggregationsCREATE TABLE remote_s3_flights
(
`FL_DATE` Nullable(Date32),
`DEP_DELAY` Nullable(Int16),
`ARR_DELAY` Nullable(Int16),
`AIR_TIME` Nullable(Int16),
`DISTANCE` Nullable(Int16),
`DEP_TIME` Nullable(Float32),
`ARR_TIME` Nullable(Float32)
)
ENGINE = S3(‘https://dtwh-demo.s3.fr-par.scw.cloud/flights-1m.parquet’, Parquet);

SELECT min(DISTANCE), max(DISTANCE), avg(DISTANCE)
FROM remote_s3_flights;

PostgreSQL

Query descriptionQuery command
Describe the tableDESCRIBE TABLE
(
SELECT *
FROM postgresql(‘51.158.113.88:7118’, ‘rdb’, ‘weather_aus’, ‘scwadmin’, ‘2t4zYjxkWWdDVZv-‘)
LIMIT 10
);
Simple SELECT querySELECT *
FROM postgresql(‘51.158.113.88:7118’, ‘rdb’, ‘weather_aus’, ‘scwadmin’, ‘2t4zYjxkWWdDVZv-‘)
LIMIT 10;
Perform basic aggregationsSELECT avg(sunshine)
FROM postgresql(‘51.158.113.88:7118’, ‘rdb’, ‘weather_aus’, ‘scwadmin’, ‘2t4zYjxkWWdDVZv-‘);
Create a remote table and perform basic operationsCREATE TABLE remote_postgres_weather_aus
(
date Date,
location String,
mintemp Float32,
maxtemp Float32,
rainfall Float32,
evaporation Float32,
sunshine Float32,
windgustdir String,
windgustspeed Int32,
winddir9am String,
winddir3pm String,
windspeed9am Int32,
windspeed3pm Int32,
humidity9am Int32,
humidity3pm Int32,
pressure9am Float32,
pressure3pm Float32,
cloud9am Float32,
cloud3pm Float32,
temp9am Float32,
temp3pm Float32,
raintoday String,
raintomorrow String
) ENGINE = PostgreSQL(‘51.158.113.88:7118’, ‘rdb’, ‘weather_aus’, ‘scwadmin’, ‘2t4zYjxkWWdDVZv-‘);

SHOW tables;

SELECT max(evaporation)
FROM remote_postgres_weather_aus;

MongoDB®

ClickHouse can connect directly to MongoDB using the `-0` hostname from the SRV record.

Query descriptionQuery command
Simple SELECT querySELECT *
FROM mongodb(‘mongodb://scwadmin:8U493ngasFd5X4gP@77ce4aac-f1dc-4545-8e02-f67d6c38be2c-0.mgdb.fr-par.scw.cloud/shipwrecks?authSource=admin&authMechanism=SCRAM-SHA-256&tlsAllowInvalidCertificates=true’,
‘shipwrecks’,
‘recrd String,
vesslterms String,
feature_type String,
chart String,
latdec Float64,
londec Float64,
gp_quality String,
depth String,
sounding_type String,
history String,
quasou String,
watlev String,
coordinates Array(Float64)’
)
LIMIT 10;
Count documentsSELECT count()
FROM mongodb(‘mongodb://scwadmin:8U493ngasFd5X4gP@77ce4aac-f1dc-4545-8e02-f67d6c38be2c-0.mgdb.fr-par.scw.cloud/shipwrecks?authSource=admin&authMechanism=SCRAM-SHA-256&tlsAllowInvalidCertificates=true’,
‘shipwrecks’,
‘recrd String,
vesslterms String,
feature_type String,
chart String,
latdec Float64,
londec Float64,
gp_quality String,
depth String,
sounding_type String,
history String,
quasou String,
watlev String,
coordinates Array(Float64)’
);
Create a remote table and perform basic operations and aggregationsCREATE TABLE remote_mongodb_shipwrecks
(
recrd String,
vesslterms String,
feature_type String,
chart String,
latdec Float64,
londec Float64,
gp_quality String,
depth String,
sounding_type String,
history String,
quasou String,
watlev String,
coordinates Array(Float64)
) ENGINE = MongoDB(‘mongodb://scwadmin:8U493ngasFd5X4gP@77ce4aac-f1dc-4545-8e02-f67d6c38be2c-0.mgdb.fr-par.scw.cloud/shipwrecks?authSource=admin&authMechanism=SCRAM-SHA-256&tlsAllowInvalidCertificates=true’, ‘shipwrecks’);

SELECT count() FROM remote_mongodb_shipwrecks;

SELECT
feature_type,
count() AS occurrences
FROM remote_mongodb_shipwrecks
GROUP BY feature_type
ORDER BY occurrences DESC
LIMIT 10
SETTINGS mongodb_throw_on_unsupported_query=0;