Данные экосенсоров

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

к сведению

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

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

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

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

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

Этот запрос займет некоторое время — это примерно 1.67T данных в не сжатом виде:

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

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

1.67T сжато до 310 GiB, и там 20.69 миллиардов строк:

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

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

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

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