Данные экологических датчиков

Sensor.Community — это глобальная сеть датчиков, управляемая участниками, создающая Открытые Экологические Данные. Данные собираются с датчиков по всему миру. Любой может купить датчик и установить его где угодно. API для загрузки данных доступны на GitHub, а данные свободно доступны по лицензии Database Contents License (DbCL).

к сведению

Набор данных содержит более 20 миллиардов записей, поэтому будьте осторожны при простом копировании и вставке команд ниже, если ваши ресурсы не могут справиться с таким объемом. Команды ниже были выполнены на Production экземпляре ClickHouse Cloud.

1. Данные находятся в S3, поэтому мы можем использовать функцию таблицы s3 для создания таблицы из файлов. Мы также можем выполнять запросы к данным непосредственно. Давайте посмотрим на несколько строк, прежде чем пытаться вставить их в ClickHouse:

Данные находятся в CSV-файлах, но для разделителей используется точка с запятой. Строки выглядят следующим образом:

2. Мы будем использовать следующую таблицу MergeTree для хранения данных в ClickHouse:

3. У ClickHouse Cloud есть кластер с именем default. Мы будем использовать функцию таблицы s3Cluster, которая читает S3-файлы параллельно с узлов в вашем кластере. (Если у вас нет кластера, просто используйте функцию s3 и уберите имя кластера.)

Этот запрос займет некоторое время – это примерно 1,67 Т данных в распакованном виде:

Вот ответ – показывающий количество строк и скорость обработки. Вводится со скоростью более 6 миллионов строк в секунду!

4. Давайте посмотрим, сколько дискового пространства потребуется для таблицы sensors:

1.67 Т сжат до 310 ГиБ, и в нем 20.69 миллиарда строк:

5. Давайте проанализируем данные, теперь когда они в ClickHouse. Обратите внимание, что количество данных увеличивается с течением времени по мере разворачивания новых датчиков:

Мы можем создать диаграмму в SQL Console, чтобы визуализировать результаты:

6. Этот запрос подсчитывает количество слишком жарких и влажных дней:

Вот визуализация результата: