환경 센서 데이터
환경 센서 데이터 (Environmental sensors data)
Sensor.Community가 수집한 전 세계 센서 네트워크의 환경 데이터를 ClickHouse로 다루는 문서입니다. 200억 개가 넘는 레코드를 S3 테이블 함수로 로드하고 분석합니다. 몇 줄의 쿼리로 대규모 데이터 수집(ingestion)을 경험해 볼 수 있어요.
출처: 문서
본문
Sensor.Community는 오픈 환경 데이터(Open Environmental Data)를 만드는 기여자 중심의 글로벌 센서 네트워크입니다. 데이터는 전 세계의 센서에서 수집됩니다. 누구나 센서를 구매해 원하는 곳에 설치할 수 있습니다. 데이터 다운로드 API는 GitHub에 있으며, 데이터는 Database Contents License (DbCL) 하에 자유롭게 사용할 수 있습니다.
이 데이터셋은 200억 개가 넘는 레코드를 가지므로, 리소스가 그 규모를 처리할 수 없다면 아래 명령을 그대로 복사·붙여넣기 할 때 주의하세요. 아래 명령들은 ClickHouse Cloud의 프로덕션 인스턴스에서 실행되었습니다.
- 데이터는 S3에 있으므로
s3테이블 함수로 파일로부터 테이블을 만들 수 있습니다. 데이터를 제자리에서 바로 쿼리할 수도 있습니다. ClickHouse에 삽입하기 전에 몇 행을 먼저 살펴봅시다:
SELECT *
FROM s3(
'https://clickhouse-public-datasets.s3.eu-central-1.amazonaws.com/sensors/monthly/2019-06_bmp180.csv.zst',
'CSVWithNames'
)
LIMIT 10
SETTINGS format_csv_delimiter = ';';
데이터는 CSV 파일이지만 구분자로 세미콜론을 사용합니다. 행은 다음과 같습니다:
┌─sensor_id─┬─sensor_type─┬─location─┬────lat─┬────lon─┬─timestamp───────────┬──pressure─┬─altitude─┬─pressure_sealevel─┬─temperature─┐
│ 9119 │ BMP180 │ 4594 │ 50.994 │ 7.126 │ 2019-06-01T00:00:00 │ 101471 │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ 19.9 │
│ 21210 │ BMP180 │ 10762 │ 42.206 │ 25.326 │ 2019-06-01T00:00:00 │ 99525 │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ 19.3 │
│ 19660 │ BMP180 │ 9978 │ 52.434 │ 17.056 │ 2019-06-01T00:00:04 │ 101570 │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ 15.3 │
│ 12126 │ BMP180 │ 6126 │ 57.908 │ 16.49 │ 2019-06-01T00:00:05 │ 101802.56 │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ 8.07 │
│ 15845 │ BMP180 │ 8022 │ 52.498 │ 13.466 │ 2019-06-01T00:00:05 │ 101878 │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ 23 │
│ 16415 │ BMP180 │ 8316 │ 49.312 │ 6.744 │ 2019-06-01T00:00:06 │ 100176 │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ 14.7 │
│ 7389 │ BMP180 │ 3735 │ 50.136 │ 11.062 │ 2019-06-01T00:00:06 │ 98905 │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ 12.1 │
│ 13199 │ BMP180 │ 6664 │ 52.514 │ 13.44 │ 2019-06-01T00:00:07 │ 101855.54 │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ 19.74 │
│ 12753 │ BMP180 │ 6440 │ 44.616 │ 2.032 │ 2019-06-01T00:00:07 │ 99475 │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ 17 │
│ 16956 │ BMP180 │ 8594 │ 52.052 │ 8.354 │ 2019-06-01T00:00:08 │ 101322 │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ 17.2 │
└───────────┴─────────────┴──────────┴────────┴────────┴─────────────────────┴───────────┴──────────┴───────────────────┴─────────────┘
- 데이터를 ClickHouse에 저장하기 위해 다음
MergeTree테이블을 사용합니다:
CREATE TABLE sensors
(
sensor_id UInt16,
sensor_type Enum('BME280', 'BMP180', 'BMP280', 'DHT22', 'DS18B20', 'HPM', 'HTU21D', 'PMS1003', 'PMS3003', 'PMS5003', 'PMS6003', 'PMS7003', 'PPD42NS', 'SDS011'),
location UInt32,
lat Float32,
lon Float32,
timestamp DateTime,
P1 Float32,
P2 Float32,
P0 Float32,
durP1 Float32,
ratioP1 Float32,
durP2 Float32,
ratioP2 Float32,
pressure Float32,
altitude Float32,
pressure_sealevel Float32,
temperature Float32,
humidity Float32,
date Date MATERIALIZED toDate(timestamp)
)
ENGINE = MergeTree
ORDER BY (timestamp, sensor_id);
- ClickHouse Cloud 서비스에는
default라는 이름의 클러스터가 있습니다. 클러스터의 노드들이 S3 파일을 병렬로 읽는s3Cluster테이블 함수를 사용할 것입니다. (클러스터가 없다면s3함수를 사용하고 클러스터 이름을 제거하세요.)
이 쿼리는 꽤 오래 걸립니다 — 압축되지 않은 데이터 약 1.67T입니다:
INSERT INTO sensors
SELECT *
FROM s3Cluster(
'default',
'https://clickhouse-public-datasets.s3.amazonaws.com/sensors/monthly/*.csv.zst',
'CSVWithNames',
$$ sensor_id UInt16,
sensor_type String,
location UInt32,
lat Float32,
lon Float32,
timestamp DateTime,
P1 Float32,
P2 Float32,
P0 Float32,
durP1 Float32,
ratioP1 Float32,
durP2 Float32,
ratioP2 Float32,
pressure Float32,
altitude Float32,
pressure_sealevel Float32,
temperature Float32,
humidity Float32 $$
)
SETTINGS
format_csv_delimiter = ';',
input_format_allow_errors_ratio = '0.5',
input_format_allow_errors_num = 10000,
input_format_parallel_parsing = 0,
date_time_input_format = 'best_effort',
max_insert_threads = 32,
parallel_distributed_insert_select = 1;
다음은 행 수와 처리 속도를 보여주는 응답입니다. 초당 600만 행이 넘는 속도로 입력됩니다!
0 rows in set. Elapsed: 3419.330 sec. Processed 20.69 billion rows, 1.67 TB (6.05 million rows/s., 488.52 MB/s.)
sensors테이블에 필요한 저장 디스크 용량을 확인해 봅시다:
SELECT
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,
count() AS part_count
FROM system.parts
WHERE (active = 1) AND (table = 'sensors')
GROUP BY
disk_name
ORDER BY size DESC;
1.67T가 310 GiB로 압축되었고, 206.9억 개의 행이 있습니다:
┌─disk_name─┬─compressed─┬─uncompressed─┬─compr_rate─┬────────rows─┬─part_count─┐
│ s3disk │ 310.21 GiB │ 1.30 TiB │ 4.29 │ 20693971809 │ 472 │
└───────────┴────────────┴──────────────┴────────────┴─────────────┴────────────┘
- 이제 ClickHouse에 들어간 데이터를 분석해 봅시다. 더 많은 센서가 배치됨에 따라 시간이 지날수록 데이터 양이 증가하는 것을 주목하세요:
SELECT
date,
count()
FROM sensors
GROUP BY date
ORDER BY date ASC;
SQL Console에서 차트를 만들어 결과를 시각화할 수 있습니다.
- 이 쿼리는 지나치게 덥고 습한 날의 수를 셉니다:
WITH
toYYYYMMDD(timestamp) AS day
SELECT day, count() FROM sensors
WHERE temperature >= 40 AND temperature <= 50 AND humidity >= 90
GROUP BY day
ORDER BY day ASC;
결과의 시각화는 아래와 같습니다.