Практическая работа. Работа с данными из объектного хранилища

В интернете выложено множество датасетов —  структурированных наборов данных, связанных общей темой. Например в репозитории проекта Our World in Data находится около тысячи разнообразных датасетов: от численности населения государств до сведений об употреблении алкоголя в США с 1850 года.Датасеты часто выкладывают в виде CSV- или TSV-файлов. В них значения разделены запятой (comma separated values, CSV) или табуляцией (tab separated values, TSV).Сохраняйте датасеты в объектное хранилище и анализируйте данные с помощью ClickHouse. При этом не требуется создавать БД и копировать в нее данные из датасета. Отправляйте запросы к ClickHouse — а ClickHouse сходит за данными напрямую в объектное хранилище.В качестве примера возьмем датасет с историей метеонаблюдений за 10 лет и попробуем развеять мифы о разнице погоды в Москве и Санкт-Петербурге. Датасет содержит примерно 50 тысяч записей, он выложен в объектном хранилище Yandex.Cloud и доступен всем.Воспользуемся кластером БД, который мы создали на предыдущем уроке. Откройте его в консоли управления. Запросы к датасету будем делать через SQL-консоль. На панели слева выберите SQL и введите пароль пользователя. В правом поле открывшейся консоли мы и станем вводить SQL-запросы.

Как вы думаете, где зарегистрирована самая низкая температура? Наверняка в Санкт-Петербурге! Давайте проверим.Выполните запрос:

Скопировать кодSELECT
    City,
    LocalDate,
    TempC
FROM s3(
        'https://storage.yandexcloud.net/arhipov/weather_data.tsv',
        'TSV',
        'LocalDateTime DateTime, LocalDate Date, Month Int8, Day Int8, TempC Float32,Pressure Float32, RelHumidity Int32, WindSpeed10MinAvg Int32, VisibilityKm Float32, City String')
ORDER BY TempC ASC
LIMIT 1 

Все-таки наши интуитивные представления не всегда верны и могут опровергаться данными.А что насчет самой высокой температуры, скорости ветра и влажности? Проверьте сами, изменив поля в запросе (средняя скорость ветра за 10 минут — WindSpeed10MinAvg, относительная влажность — RelHumidity; сортировка по возрастанию — ASC, по убыванию — DESC). Увеличив количество выводимых данных, вы получите более точное представление (измените параметр LIMIT c 1 до 10).Проверьте себя

Но это были крайние значения. Давайте проверим, насколько в этих городах отличается климат в целом. Узнаем, например, разницу среднегодовых температур.

Скопировать кодSELECT
    Year,
    msk.t - spb.t
FROM
(
    SELECT
        toYear(LocalDate) AS Year,
        avg(TempC) AS t
    FROM s3(
        'https://storage.yandexcloud.net/arhipov/weather_data.tsv',
        'TSV',
        'LocalDateTime DateTime, LocalDate Date, Month Int8, Day Int8, TempC Float32,Pressure Float32, RelHumidity Int32, WindSpeed10MinAvg Int32, VisibilityKm Float32, City String')
    WHERE City = 'Moscow'
    GROUP BY Year
    ORDER BY Year ASC
) AS msk
INNER JOIN
(
    SELECT
        toYear(LocalDate) AS Year,
        avg(TempC) AS t
    FROM s3(
        'https://storage.yandexcloud.net/arhipov/weather_data.tsv',
        'TSV',
        'LocalDateTime DateTime, LocalDate Date, Month Int8, Day Int8, TempC Float32,Pressure Float32, RelHumidity Int32, WindSpeed10MinAvg Int32, VisibilityKm Float32, City String')
    WHERE City = 'Saint-Petersburg'
    GROUP BY Year
    ORDER BY Year ASC
) AS spb ON msk.Year = spb.Year 

Измените поля в запросе, чтобы проверить разницу относительной влажности.Давайте теперь рассчитаем, где раньше начинается лето. Будем считать началом лета день, начиная с которого температура поднималась выше +15 °С хотя бы пять раз в течение 10-дневного периода (864 тысячи секунд).

Скопировать кодSELECT
    City,
    toYear(LocalDate) AS year,
    MIN(LocalDate)
FROM
(
    SELECT
        City,
        LocalDate,
        windowFunnel(864000)(LocalDateTime, TempC >= 15, TempC >= 15, TempC >= 15, TempC >= 15, TempC >= 15) AS warmdays
    FROM s3(
        'https://storage.yandexcloud.net/arhipov/weather_data.tsv',
        'TSV',
        'LocalDateTime DateTime, LocalDate Date, Month Int8, Day Int8, TempC Float32,Pressure Float32, RelHumidity Int32, WindSpeed10MinAvg Int32, VisibilityKm Float32, City String')
    GROUP BY
        City,
        LocalDate
)
WHERE warmdays = 5
GROUP BY
    year,
    City
ORDER BY
    year ASC,
    City ASC