NOAA 전 지구 기후학 네트워크
NOAA 전 지구 기후학 네트워크 (NOAA Global Historical Climatology Network)
지난 120년간의 기상 측정 데이터를 담은 데이터셋입니다. 각 행은 특정 시점과 관측소의 측정값이며, 원시 데이터를 ClickHouse에 맞게 정리·재구성·풍부화(enrich)하는 과정을 단계별로 보여줍니다.
출처: 문서
본문
이 데이터셋은 지난 120년간의 기상 측정 데이터를 포함합니다. 각 행은 특정 시점과 관측소의 측정값입니다.
이 데이터의 원본에 따르면 더 정확히는:
GHCN-Daily는 전 세계 육지 지역에 대한 일일 관측값을 포함하는 데이터셋입니다. 전 세계 육지 기반 관측소의 측정값을 포함하며, 그중 약 3분의 2는 강수량 측정 전용입니다 (Menne et al., 2012). GHCN-Daily는 병합되고 공통의 품질 보증 검토(Durre et al., 2010)를 거친 수많은 출처의 기후 기록으로 구성된 복합체입니다. 이 아카이브에는 다음 기상 요소들이 포함됩니다:
- 일일 최고 기온
- 일일 최저 기온
- 관측 시점의 기온
- 강수량 (즉, 비, 녹은 눈)
- 적설량
- 적설 깊이
- 사용 가능한 경우 다른 요소들
아래 섹션들은 이 데이터셋을 ClickHouse로 가져오는 데 관련된 단계를 간략히 개요합니다. 각 단계에 대해 더 자세히 읽고 싶다면 "Exploring massive, real-world data sets: 100+ Years of Weather Records in ClickHouse"라는 블로그 게시물을 살펴보는 것을 권장합니다.
데이터 다운로드 (Downloading the data)
- ClickHouse용 데이터의 [사본]은 정리되고 재구성되었으며 풍부해졌습니다. 이 데이터는 1900년부터 2022년까지의 연도를 다룹니다.
- [변환]해 ClickHouse가 요구하는 형식으로 만듭니다. 자신만의 컬럼을 추가하려는 사용자는 이 접근 방식을 탐색할 수 있습니다.
사전 준비된 데이터 (Pre-prepared data)
더 정확히는, NOAA의 어떤 품질 보증 검사도 통과하지 못한 행들이 제거되었습니다. 데이터는 또한 라인당 하나의 측정값에서 관측소 id 및 날짜당 하나의 행으로 재구성되었습니다. 즉:
"station_id","date","tempAvg","tempMax","tempMin","precipitation","snowfall","snowDepth","percentDailySun","averageWindSpeed","maxWindSpeed","weatherType"
"AEM00041194","2022-07-30",347,0,308,0,0,0,0,0,0,0
"AEM00041194","2022-07-31",371,413,329,0,0,0,0,0,0,0
"AEM00041194","2022-08-01",384,427,357,0,0,0,0,0,0,0
"AEM00041194","2022-08-02",381,424,352,0,0,0,0,0,0,0
이것은 쿼리하기 더 단순하고 결과 테이블이 덜 희소(sparse)함을 보장합니다. 마지막으로 데이터는 위도·경도로도 풍부해졌습니다.
이 데이터는 다음 S3 위치에서 사용할 수 있습니다. 데이터를 로컬 파일시스템에 내려받아(ClickHouse 클라이언트로 삽입) 사용하거나, ClickHouse에 직접 삽입할 수 있습니다(참고).
다운로드 방법:
wget https://datasets-documentation.s3.eu-west-3.amazonaws.com/noaa/noaa_enriched.parquet
원본 데이터 (Original data)
다음은 ClickHouse에 로드하기 위한 준비로 원본 데이터를 내려받고 변환하는 단계입니다.
다운로드 (Download)
원본 데이터를 내려받으려면:
for i in {1900..2023}; do wget https://noaa-ghcn-pds.s3.amazonaws.com/csv.gz/${i}.csv.gz; done
데이터 샘플링 (Sampling the data)
$ clickhouse-local --query "SELECT * FROM '2021.csv.gz' LIMIT 10" --format PrettyCompact
┌─c1──────────┬───────c2─┬─c3───┬──c4─┬─c5───┬─c6───┬─c7─┬───c8─┐
│ AE000041196 │ 20210101 │ TMAX │ 278 │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ S │ ᴺᵁᴸᴸ │
│ AE000041196 │ 20210101 │ PRCP │ 0 │ D │ ᴺᵁᴸᴸ │ S │ ᴺᵁᴸᴸ │
│ AE000041196 │ 20210101 │ TAVG │ 214 │ H │ ᴺᵁᴸᴸ │ S │ ᴺᵁᴸᴸ │
│ AEM00041194 │ 20210101 │ TMAX │ 266 │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ S │ ᴺᵁᴸᴸ │
│ AEM00041194 │ 20210101 │ TMIN │ 178 │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ S │ ᴺᵁᴸᴸ │
│ AEM00041194 │ 20210101 │ PRCP │ 0 │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ S │ ᴺᵁᴸᴸ │
│ AEM00041194 │ 20210101 │ TAVG │ 217 │ H │ ᴺᵁᴸᴸ │ S │ ᴺᵁᴸᴸ │
│ AEM00041217 │ 20210101 │ TMAX │ 262 │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ S │ ᴺᵁᴸᴸ │
│ AEM00041217 │ 20210101 │ TMIN │ 155 │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ S │ ᴺᵁᴸᴸ │
│ AEM00041217 │ 20210101 │ TAVG │ 202 │ H │ ᴺᵁᴸᴸ │ S │ ᴺᵁᴸᴸ │
└─────────────┴──────────┴──────┴─────┴──────┴──────┴────┴──────┘
형식 문서를 요약하면:
형식 문서와 컬럼들을 순서대로 요약하면:
- 11자리 관측소 식별 코드. 이것 자체로 유용한 정보를 인코딩합니다
- YEAR/MONTH/DAY = YYYYMMDD 형식의 8자리 날짜 (예: 19860529 = 1986년 5월 29일)
- ELEMENT = 요소 유형을 나타내는 4자리 표시자. 실질적으로 측정 유형입니다. 많은 측정값이 있지만 우리는 다음을 선택합니다: - PRCP - 강수량 (0.1mm 단위) - SNOW - 적설량 (mm) - SNWD - 적설 깊이 (mm) - TMAX - 최고 기온 (0.1°C 단위) - TAVG - 평균 기온 (0.1°C 단위) - TMIN - 최저 기온 (0.1°C 단위) - PSUN - 일일 가능한 일조량 비율 (퍼센트) - AWND - 일일 평균 풍속 (0.1m/s 단위) - WSFG - 최대 돌풍 풍속 (0.1m/s 단위) - WT** = 기상 유형으로, **가 기상 유형을 정의합니다. 기상 유형 전체 목록은 여기에 있습니다. - DATA VALUE = ELEMENT에 대한 5자리 데이터 값, 즉 측정값입니다. - M-FLAG = 1자리 측정 플래그. 가능한 값이 10개 있습니다. 일부 값은 데이터 정확도가 의심됨을 나타냅니다. 우리는 이것이 누락으로 추정되는 0으로 식별되는 "P"로 설정된 데이터를 허용하는데, 이는 PRCP, SNOW, SNWD 측정에만 관련되기 때문입니다.
- Q-FLAG는 14개의 가능한 값을 가진 측정 품질 플래그입니다. 우리는 어떤 품질 보증 검사도 실패하지 않은, 즉 빈 값을 가진 데이터만 관심을 갖습니다.
- S-FLAG는 관측의 소스 플래그입니다. 우리 분석에는 유용하지 않아 무시합니다.
- OBS-TIME = 시-분 형식의 4자리 관측 시간 (즉 0700 = 오전 7:00). 일반적으로 오래된 데이터에는 없습니다. 우리 목적에서는 무시합니다.
라인당 하나의 측정값은 ClickHouse에서 희소한 테이블 구조를 초래합니다. 우리는 시간 및 관측소당 하나의 행으로, 측정값을 컬럼으로 변환해야 합니다. 먼저 문제가 없는 행, 즉 qFlag가 빈 문자열과 같은 행으로 데이터셋을 제한합니다.
데이터 정리 (Clean the data)
ClickHouse local을 사용해 관심 측정값을 나타내고 품질 요구사항을 통과하는 행을 필터링할 수 있습니다:
clickhouse local --query "SELECT count()
FROM file('*.csv.gz', CSV, 'station_id String, date String, measurement String, value Int64, mFlag String, qFlag String, sFlag String, obsTime String') WHERE qFlag = '' AND (measurement IN ('PRCP', 'SNOW', 'SNWD', 'TMAX', 'TAVG', 'TMIN', 'PSUN', 'AWND', 'WSFG') OR startsWith(measurement, 'WT'))"
2679264563
26억 개가 넘는 행이 있으므로 모든 파일을 파싱해야 하므로 이는 빠른 쿼리가 아닙니다. 8코어 머신에서는 약 160초가 걸립니다.
데이터 피벗 (Pivot data)
라인당 측정값 구조도 ClickHouse에서 사용할 수 있지만 향후 쿼리를 불필요하게 복잡하게 만들 것입니다. 이상적으로는 관측소 id 및 날짜당 하나의 행이 필요하며, 여기서 각 측정 유형과 관련 값이 컬럼입니다. 즉:
"station_id","date","tempAvg","tempMax","tempMin","precipitation","snowfall","snowDepth","percentDailySun","averageWindSpeed","maxWindSpeed","weatherType"
"AEM00041194","2022-07-30",347,0,308,0,0,0,0,0,0,0
"AEM00041194","2022-07-31",371,413,329,0,0,0,0,0,0,0
"AEM00041194","2022-08-01",384,427,357,0,0,0,0,0,0,0
"AEM00041194","2022-08-02",381,424,352,0,0,0,0,0,0,0
ClickHouse local과 간단한 GROUP BY를 사용해 데이터를 이 구조로 재피벗할 수 있습니다. 메모리 오버헤드를 제한하기 위해 한 번에 한 파일씩 처리합니다.
for i in {1900..2022}
do
clickhouse-local --query "SELECT station_id,
toDate32(date) as date,
anyIf(value, measurement = 'TAVG') as tempAvg,
anyIf(value, measurement = 'TMAX') as tempMax,
anyIf(value, measurement = 'TMIN') as tempMin,
anyIf(value, measurement = 'PRCP') as precipitation,
anyIf(value, measurement = 'SNOW') as snowfall,
anyIf(value, measurement = 'SNWD') as snowDepth,
anyIf(value, measurement = 'PSUN') as percentDailySun,
anyIf(value, measurement = 'AWND') as averageWindSpeed,
anyIf(value, measurement = 'WSFG') as maxWindSpeed,
toUInt8OrZero(replaceOne(anyIf(measurement, startsWith(measurement, 'WT') AND value = 1), 'WT', '')) as weatherType
FROM file('$i.csv.gz', CSV, 'station_id String, date String, measurement String, value Int64, mFlag String, qFlag String, sFlag String, obsTime String')
WHERE qFlag = '' AND (measurement IN ('PRCP', 'SNOW', 'SNWD', 'TMAX', 'TAVG', 'TMIN', 'PSUN', 'AWND', 'WSFG') OR startsWith(measurement, 'WT'))
GROUP BY station_id, date
ORDER BY station_id, date FORMAT CSV" >> "noaa.csv";
done
이 쿼리는 단일 50GB 파일 noaa.csv를 생성합니다.
데이터 풍부화 (Enriching the data)
데이터에는 관측소 id 외에는 위치 표시가 없으며, 관측소 id는 국가 코드 접두사를 포함합니다. 이상적으로 각 관측소에는 위도·경도가 연결되어야 합니다. 이를 위해 NOAA는 편리하게 각 관측소의 세부 정보를 별도의 ghcnd-stations.txt로 제공합니다. 이 파일에는 여러 컬럼이 있으며, 그중 5개가 향후 분석에 유용합니다: id, latitude, longitude, elevation, name.
wget http://noaa-ghcn-pds.s3.amazonaws.com/ghcnd-stations.txt
clickhouse local --query "WITH stations AS (SELECT id, lat, lon, elevation, splitByString(' GSN ',name)[1] as name FROM file('ghcnd-stations.txt', Regexp, 'id String, lat Float64, lon Float64, elevation Float32, name String'))
SELECT station_id,
date,
tempAvg,
tempMax,
tempMin,
precipitation,
snowfall,
snowDepth,
percentDailySun,
averageWindSpeed,
maxWindSpeed,
weatherType,
tuple(lon, lat) as location,
elevation,
name
FROM file('noaa.csv', CSV,
'station_id String, date Date32, tempAvg Int32, tempMax Int32, tempMin Int32, precipitation Int32, snowfall Int32, snowDepth Int32, percentDailySun Int8, averageWindSpeed Int32, maxWindSpeed Int32, weatherType UInt8') as noaa LEFT OUTER
JOIN stations ON noaa.station_id = stations.id INTO OUTFILE 'noaa_enriched.parquet' FORMAT Parquet SETTINGS format_regexp='^(.{11})\\s+(\\-?\\d{1,2}\\.\\d{4})\\s+(\\-?\\d{1,3}\\.\\d{1,4})\\s+(\\-?\\d*\\.\\d*)\\s+(.*)\\s+(?:[\\d]*)'"
이 쿼리는 실행하는 데 몇 분이 걸리며 6.4 GB 파일인 noaa_enriched.parquet를 생성합니다.
테이블 생성 (Create table)
(ClickHouse 클라이언트에서) ClickHouse에 MergeTree 테이블을 만듭니다.
CREATE TABLE noaa
(
`station_id` LowCardinality(String),
`date` Date32,
`tempAvg` Int32 COMMENT 'Average temperature (tenths of a degrees C)',
`tempMax` Int32 COMMENT 'Maximum temperature (tenths of degrees C)',
`tempMin` Int32 COMMENT 'Minimum temperature (tenths of degrees C)',
`precipitation` UInt32 COMMENT 'Precipitation (tenths of mm)',
`snowfall` UInt32 COMMENT 'Snowfall (mm)',
`snowDepth` UInt32 COMMENT 'Snow depth (mm)',
`percentDailySun` UInt8 COMMENT 'Daily percent of possible sunshine (percent)',
`averageWindSpeed` UInt32 COMMENT 'Average daily wind speed (tenths of meters per second)',
`maxWindSpeed` UInt32 COMMENT 'Peak gust wind speed (tenths of meters per second)',
`weatherType` Enum8('Normal' = 0, 'Fog' = 1, 'Heavy Fog' = 2, 'Thunder' = 3, 'Small Hail' = 4, 'Hail' = 5, 'Glaze' = 6, 'Dust/Ash' = 7, 'Smoke/Haze' = 8, 'Blowing/Drifting Snow' = 9, 'Tornado' = 10, 'High Winds' = 11, 'Blowing Spray' = 12, 'Mist' = 13, 'Drizzle' = 14, 'Freezing Drizzle' = 15, 'Rain' = 16, 'Freezing Rain' = 17, 'Snow' = 18, 'Unknown Precipitation' = 19, 'Ground Fog' = 21, 'Freezing Fog' = 22),
`location` Point,
`elevation` Float32,
`name` LowCardinality(String)
) ENGINE = MergeTree() ORDER BY (station_id, date);
ClickHouse에 삽입 (Inserting into ClickHouse)
로컬 파일에서 삽입 (Inserting from local file)
(ClickHouse 클라이언트에서) 로컬 파일에서 다음과 같이 삽입할 수 있습니다:
INSERT INTO noaa FROM INFILE '<path>/noaa_enriched.parquet'
여기서 <path>는 디스크의 로컬 파일에 대한 전체 경로를 나타냅니다.
이 로드를 가속화하는 방법은 여기를 참고하세요.
S3에서 삽입 (Inserting from S3)
INSERT INTO noaa SELECT *
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/noaa/noaa_enriched.parquet', NOSIGN)
가속화 방법은 대규모 데이터 로드 튜닝 블로그 게시물을 참고하세요.
샘플 쿼리 (Sample queries)
역대 최고 기온 (Highest temperature ever)
SELECT
tempMax / 10 AS maxTemp,
location,
name,
date
FROM blogs.noaa
WHERE tempMax > 500
ORDER BY
tempMax DESC,
date ASC
LIMIT 5
┌─maxTemp─┬─location──────────┬─name───────────────────────────────────────────┬───────date─┐
│ 56.7 │ (-116.8667,36.45) │ CA GREENLAND RCH │ 1913-07-10 │
│ 56.7 │ (-115.4667,32.55) │ MEXICALI (SMN) │ 1949-08-20 │
│ 56.7 │ (-115.4667,32.55) │ MEXICALI (SMN) │ 1949-09-18 │
│ 56.7 │ (-115.4667,32.55) │ MEXICALI (SMN) │ 1952-07-17 │
│ 56.7 │ (-115.4667,32.55) │ MEXICALI (SMN) │ 1952-09-04 │
└─────────┴───────────────────┴────────────────────────────────────────────────┴────────────┘
5 rows in set. Elapsed: 0.514 sec. Processed 1.06 billion rows, 4.27 GB (2.06 billion rows/s., 8.29 GB/s.)
2023년 기준 Furnace Creek의 기록된 기록과 안심할 만큼 일치합니다.
최고의 스키 리조트 (Best ski resorts)
미국의 스키 리조트 목록과 각 리조트의 위치를 사용해, 지난 5년 중 어느 달에 가장 많은 눈이 온 상위 1000개 기상 관측소와 조인합니다. 이 조인을 geoDistance로 정렬하고 거리가 20km 미만인 결과로 제한한 뒤, 리조트당 상위 결과를 선택하고 총 적설량으로 정렬합니다. 또한 좋은 스키 조건의 대략적인 지표로 리조트를 1800m 이상으로 제한합니다.
SELECT
resort_name,
total_snow / 1000 AS total_snow_m,
resort_location,
month_year
FROM
(
WITH resorts AS
(
SELECT
resort_name,
state,
(lon, lat) AS resort_location,
'US' AS code
FROM url('https://gist.githubusercontent.com/gingerwizard/dd022f754fd128fdaf270e58fa052e35/raw/622e03c37460f17ef72907afe554cb1c07f91f23/ski_resort_stats.csv', CSVWithNames)
)
SELECT
resort_name,
highest_snow.station_id,
geoDistance(resort_location.1, resort_location.2, station_location.1, station_location.2) / 1000 AS distance_km,
highest_snow.total_snow,
resort_location,
station_location,
month_year
FROM
(
SELECT
sum(snowfall) AS total_snow,
station_id,
any(location) AS station_location,
month_year,
substring(station_id, 1, 2) AS code
FROM noaa
WHERE (date > '2017-01-01') AND (code = 'US') AND (elevation > 1800)
GROUP BY
station_id,
toYYYYMM(date) AS month_year
ORDER BY total_snow DESC
LIMIT 1000
) AS highest_snow
INNER JOIN resorts ON highest_snow.code = resorts.code
WHERE distance_km < 20
ORDER BY
resort_name ASC,
total_snow DESC
LIMIT 1 BY
resort_name,
station_id
)
ORDER BY total_snow DESC
LIMIT 5
┌─resort_name──────────┬─total_snow_m─┬─resort_location─┬─month_year─┐
│ Sugar Bowl, CA │ 7.799 │ (-120.3,39.27) │ 201902 │
│ Donner Ski Ranch, CA │ 7.799 │ (-120.34,39.31) │ 201902 │
│ Boreal, CA │ 7.799 │ (-120.35,39.33) │ 201902 │
│ Homewood, CA │ 4.926 │ (-120.17,39.08) │ 201902 │
│ Alpine Meadows, CA │ 4.926 │ (-120.22,39.17) │ 201902 │
└──────────────────────┴──────────────┴─────────────────┴────────────┘
5 rows in set. Elapsed: 0.750 sec. Processed 689.10 million rows, 3.20 GB (918.20 million rows/s., 4.26 GB/s.)
Peak memory usage: 67.66 MiB.
감사의 말 (Credits)
이 데이터를 준비, 정리, 배포해 주신 Global Historical Climatology Network의 노력에 감사드립니다. 여러분의 노력에 감사드립니다.
Menne, M.J., I. Durre, B. Korzeniewski, S. McNeal, K. Thomas, X. Yin, S. Anthony, R. Ray, R.S. Vose, B.E.Gleason, and T.G. Houston, 2012: Global Historical Climatology Network - Daily (GHCN-Daily), Version 3. [indicate subset used following decimal, e.g. Version 3.25]. NOAA National Centers for Environmental Information. http://doi.org/10.7289/V5D21VHZ [17/08/2020]