환경 센서 데이터

기여자 중심의 글로벌 센서 네트워크인 Sensor.Community가 구축한 개방형 환경 데이터 200억 건 이상을 제공합니다.

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

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

1. 데이터는 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 │
└───────────┴─────────────┴──────────┴────────┴────────┴─────────────────────┴───────────┴──────────┴───────────────────┴─────────────┘

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);

3. ClickHouse Cloud 서비스에는 default라는 클러스터가 있습니다. 클러스터의 노드에서 S3 파일을 병렬로 읽는 s3Cluster 테이블 함수를 사용하겠습니다. (클러스터가 없으면 s3 함수를 사용하고 클러스터 이름을 제거하십시오.)

이 쿼리는 시간이 다소 걸릴 수 있습니다. 비압축(uncompressed) 데이터 기준으로 약 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;

다음은 응답 결과입니다. 행(row) 수와 처리 속도를 확인할 수 있습니다. 초당 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.)

4. 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 │
└───────────┴────────────┴──────────────┴────────────┴─────────────┴────────────┘

5. 이제 데이터가 ClickHouse에 있으므로 분석해 보겠습니다. 더 많은 센서가 배포됨에 따라 시간이 지날수록 데이터 양이 증가하는 것을 확인할 수 있습니다:

SELECT
    date,
    count()
FROM sensors
GROUP BY date
ORDER BY date ASC;

SQL 콘솔에서 차트를 생성해 결과를 시각화할 수 있습니다:

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

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;

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