NOAA Global Historical Climatology Network

2.5 مليار صف من بيانات المناخ على مدى الـ120 عامًا الماضية

تحتوي مجموعة البيانات هذه على قياسات للطقس تغطي آخر 120 عامًا. ويمثل كل صف قياسًا لنقطة زمنية ومحطة رصد.

وبصورة أدق، ووفقًا لمصدر هذه البيانات:

GHCN-Daily هي مجموعة بيانات تتضمن رصودًا يومية عبر المناطق البرية حول العالم. وهي تحتوي على قياسات قائمة على محطات رصد برية من مختلف أنحاء العالم، ويقتصر نحو ثلثيها على قياسات الهطول فقط (Menne et al., 2012). وتُعد GHCN-Daily تجميعًا لسجلات مناخية من مصادر متعددة دُمجت معًا وخضعت لمجموعة موحدة من مراجعات ضمان الجودة (Durre et al., 2010). ويشمل الأرشيف العناصر الجوية التالية:

• درجة الحرارة العظمى اليومية
  • درجة الحرارة الصغرى اليومية
  • درجة الحرارة وقت الرصد
  • الهطول (أي المطر والثلج الذائب)
  • تساقط الثلوج
  • عمق الثلج
  • عناصر أخرى عند توفرها

تقدم الأقسام أدناه لمحة موجزة عن الخطوات المتبعة لإدخال مجموعة البيانات هذه إلى ClickHouse. وإذا كنت مهتمًا بالاطلاع على تفاصيل أوفى حول كل خطوة، فنوصي بقراءة تدوينة مدونتنا بعنوان “Exploring massive, real-world data sets: 100+ Years of Weather Records in ClickHouse”.

​

تنزيل البيانات

• نسخة مُعدّة مسبقًا من البيانات لـ ClickHouse، وقد نُقِّيت وأُعيدت هيكلتها وأُثرِيت. تغطي هذه البيانات السنوات من 1900 إلى 2022.
• نزّل البيانات الأصلية وحوّلها إلى الصيغة المطلوبة لـ ClickHouse. قد يرغب المستخدمون الذين يريدون إضافة أعمدة خاصة بهم في استكشاف هذا النهج.

​

بيانات مُعدّة مسبقًا

وبشكل أكثر تحديدًا، أُزيلت الصفوف التي لم ترسب في أيٍّ من فحوصات ضمان الجودة لدى NOAA. كما أُعيد تنظيم البيانات من قياس واحد لكل سطر إلى صف واحد لكل معرّف محطة وتاريخ، أي:

"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

هذا أسهل في الاستعلام عنه ويضمن أن يكون الجدول الناتج أقل تناثرًا. وأخيرًا، أُثريت البيانات أيضًا بخطّي العرض والطول.

هذه البيانات متاحة في موقع S3 التالي. إما نزّل البيانات إلى نظام الملفات المحلي لديك (ثم أدرِجها باستخدام عميل ClickHouse) أو أدرِجها مباشرةً في ClickHouse (راجع الإدراج من S3).

للتنزيل:

wget https://datasets-documentation.s3.eu-west-3.amazonaws.com/noaa/noaa_enriched.parquet

​

البيانات الأصلية

يوضح ما يلي خطوات تنزيل البيانات الأصلية وتحويلها تمهيدًا لتحميلها إلى ClickHouse.

​

تنزيل

لتنزيل البيانات الأصلية:

for i in {1900..2023}; do wget https://noaa-ghcn-pds.s3.amazonaws.com/csv.gz/${i}.csv.gz; done

​

أخذ عينات من البيانات

$ 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 = تاريخ من 8 أحرف بتنسيق YYYYMMDD (مثل 19860529 = 29 مايو 1986)
• ELEMENT = مؤشر من 4 أحرف لنوع العنصر، وهو عمليًا نوع القياس. ورغم توفر العديد من القياسات، فإننا نختار ما يلي:
  • PRCP - الهطول (أعشار المليمتر)
  • SNOW - تساقط الثلوج (مم)
  • SNWD - عمق الثلوج (مم)
  • TMAX - درجة الحرارة العظمى (أعشار الدرجة المئوية)
  • TAVG - متوسط درجة الحرارة (أعشار الدرجة المئوية)
  • TMIN - درجة الحرارة الصغرى (أعشار الدرجة المئوية)
  • PSUN - النسبة اليومية لمدة سطوع الشمس الممكنة (بالمئة)
  • AWND - متوسط سرعة الرياح اليومية (أعشار المتر في الثانية)
  • WSFG - أقصى سرعة لهبّات الرياح (أعشار المتر في الثانية)
  • WT** = نوع الطقس، حيث يحدد ** نوع الطقس. القائمة الكاملة لأنواع الطقس هنا.
  • DATA VALUE = قيمة بيانات من 5 أحرف لـ ELEMENT، أي قيمة القياس.
  • M-FLAG = علامة قياس من حرف واحد. لها 10 قيم ممكنة. تشير بعض هذه القيم إلى أن دقة البيانات محل شك. نقبل البيانات عندما تكون هذه القيمة مضبوطة على “P” — أي مُعرَّفة على أنها مفقودة ويُفترض أنها صفر — لأن ذلك لا ينطبق إلا على قياسات PRCP وSNOW وSNWD.
• Q-FLAG هي علامة جودة القياس، ولها 14 قيمة ممكنة. ما يهمنا فقط هو البيانات ذات القيمة الفارغة، أي إنها لم تُخفِق في أي من فحوصات ضمان الجودة.
• S-FLAG هي علامة مصدر الرصد. وهي غير مفيدة لتحليلنا، لذا نتجاهلها.
• OBS-TIME = وقت الرصد مكوّن من 4 أحرف بتنسيق الساعة-الدقيقة (أي 0700 = 7:00 صباحًا). وعادةً لا يكون موجودًا في البيانات الأقدم. نتجاهله لأغراضنا.

سيؤدي وجود قياس واحد في كل سطر إلى بنية جدول sparse في ClickHouse. لذا ينبغي تحويل البيانات إلى صف واحد لكل وقت ومحطة، مع وضع القياسات في أعمدة. أولًا، نقيّد مجموعة البيانات بالصفوف التي لا تحتوي على مشكلات، أي حيث تكون qFlag مساويةً لسلسلة فارغة.

​

تنظيف البيانات

باستخدام 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

مع أكثر من 2.6 مليار صف، لا يُعد هذا استعلامًا سريعًا لأنه يتطلب تحليل جميع الملفات. وعلى جهازنا المزود بـ8 أنوية، يستغرق ذلك نحو 160 ثانية.

​

تحويل البيانات

مع أن بنية وجود قياس واحد في كل سطر يمكن استخدامها مع ClickHouse، فإنها ستزيد من تعقيد الاستعلامات المستقبلية دون داعٍ. ومن الناحية المثالية، نحتاج إلى صف لكل معرّف محطة ولكل تاريخ، بحيث يكون كل نوع من القياسات والقيمة المرتبطة به في عمود، أي:

"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

ينتج هذا الاستعلام ملفًا واحدًا بحجم 50 جيجابايت باسم noaa.csv.

​

إثراء البيانات

لا تتضمن البيانات أي دلالة على الموقع سوى معرّف المحطة، الذي يتضمن بادئة لرمز البلد. ومن الناحية المثالية، ينبغي أن يرتبط بكل محطة خط عرض وخط طول. ولتحقيق ذلك، توفّر NOAA تفاصيل كل محطة، بشكل مناسب، في ملف منفصل هو ghcnd-stations.txt. يحتوي هذا الملف على عدة أعمدة، خمسةٌ منها مفيدة لتحليلنا لاحقًا: 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.

​

إنشاء جدول

أنشئ جدول MergeTree في ClickHouse (باستخدام عميل ClickHouse).

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

​

الإدراج من ملف محلي

يمكن إدراج البيانات من ملف محلي على النحو التالي (باستخدام عميل ClickHouse):

INSERT INTO noaa FROM INFILE '<path>/noaa_enriched.parquet'

حيث يمثّل <path> المسار الكامل للملف المحلي على القرص.

اطّلع هنا على كيفية تسريع عملية التحميل هذه.

​

الإدراج من S3

INSERT INTO noaa SELECT *
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/noaa/noaa_enriched.parquet')

للتعرّف على كيفية تسريع هذه العملية، راجع منشور مدونتنا حول ضبط عمليات تحميل البيانات الضخمة.

​

استعلامات نموذجية

​

أعلى درجة حرارة مُسجَّلة على الإطلاق

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

وبما يبعث على الاطمئنان، يتوافق ذلك مع السجل الموثق في Furnace Creek اعتبارًا من عام 2023.

​

أفضل منتجعات التزلج

باستخدام قائمة منتجعات التزلج في الولايات المتحدة ومواقعها، نربط هذه البيانات بأفضل 1000 محطة أرصاد جوية سجّلت أكبر عدد من القراءات في أي شهر خلال السنوات الخمس الماضية. وبعد فرز هذا الربط حسب geoDistance وقصر النتائج على الحالات التي تقل فيها المسافة عن 20 كم، نختار أعلى نتيجة لكل منتجع ثم نفرزها حسب إجمالي تساقط الثلوج. ونقصر المنتجعات أيضًا على تلك التي يزيد ارتفاعها على 1800 متر، باعتبار ذلك مؤشرًا عامًا على جودة ظروف التزلج.

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.

​

الشكر والتقدير

نود أن نتقدم بالشكر إلى شبكة 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), الإصدار 3. [يُرجى توضيح المجموعة الفرعية المستخدمة بعد الرقم العشري، على سبيل المثال الإصدار 3.25]. NOAA National Centers for Environmental Information. http://doi.org/10.7289/V5D21VHZ [17/08/2020]