환경 센서 데이터

Sensor.Community는 공개 환경 데이터(Open Environmental Data)를 생성하는, 커뮤니티 참여자들이 주도하는 글로벌 센서 네트워크입니다. 데이터는 전 세계 센서에서 수집됩니다. 누구나 센서를 구매해 원하는 곳에 설치할 수 있습니다. 데이터를 다운로드하기 위한 API는 GitHub에 있으며, 데이터는 Database Contents License (DbCL)에 따라 자유롭게 사용할 수 있습니다.

이 데이터셋에는 200억 개가 넘는 레코드가 있으므로, 리소스가 이 정도 규모를 처리할 수 없다면 아래 명령을 그대로 복사해 붙여 넣지 않도록 주의하십시오. 이 명령은 적절한 규모의 ClickHouse Cloud 서비스에서 실행되었습니다.

이 명령을 직접 실행해 보려면 무료 ClickHouse Cloud 체험판에 가입하거나 자체 호스팅 ClickHouse를 설정하세요.

데이터는 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);

s3Cluster 테이블 함수를 사용하겠습니다. 이 함수는 S3 파일을 클러스터의 노드에 분산해 병렬로 읽습니다. ClickHouse Cloud에서는 클러스터 이름이 default이므로 아래와 같이 실행할 수 있습니다. 자가 관리형 서버에서는 default를 클러스터 이름으로 바꾸십시오. 단일 서버를 실행 중이라면 대신 s3 테이블 함수를 사용하고 첫 번째 인수를 제거해 함수 호출이 URL로 시작하도록 하십시오.

이 쿼리는 시간이 다소 걸릴 수 있습니다. 비압축 데이터 기준으로 약 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 테이블(table)에 필요한 디스크 저장 공간이 얼마나 되는지 살펴보겠습니다:

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,000만 행이 있습니다:

┌─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 콘솔에서 차트를 생성해 결과를 시각화할 수 있습니다:

이 쿼리는 매우 덥고 습한 날의 수를 계산합니다:

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;

결과를 시각화하면 다음과 같습니다:

Get started

Setup

Sample datasets

Migrate