Поделиться через

Руководство по исследованию и анализу озера данных с помощью бессерверного пула SQL

  • Статья

В этом учебнике вы узнаете, как выполнять исследовательский анализ данных. Вы научитесь объединять различные Открытые наборы данных Azure с помощью бессерверного пула SQL. Затем вы сможете визуализировать результаты в Synapse Studio для Azure Synapse Analytics.

Функция OPENROWSET(BULK...) позволяет получить доступ к файлам в службе хранилища Azure. [OPENROWSET](develop-openrowset.md) считывает содержимое удаленного источника данных (например, файла) и возвращает содержимое в виде набора строк.

Автоматический вывод схемы

Данные хранятся в файлах формата Parquet, поэтому вам доступен автоматический вывод схемы. Таким образом, вы можете запрашивать информацию без перечисления типов данных для всех столбцов в файлах. Кроме того, вы можете отфильтровать определенное подмножество файлов, используя механизм виртуальных столбцов и функцию filepath.

Примечание

Параметры сортировки по умолчанию — SQL_Latin1_General_CP1_CI_ASIf. Для параметров сортировки, отличных от параметров сортировки по умолчанию, учитывайте чувствительность к регистру.

Если вы создаете базу данных с параметрами сортировки с учетом регистра, то при указании столбцов следите за правильностью имен столбцов.

Например, имя столбца tpepPickupDateTime будет правильным, а имя столбца tpeppickupdatetime не будет работать с параметрами сортировки, отличными от параметров по умолчанию.

В этом руководстве используется набор данных о такси Нью-Йорка:

  • Даты и время посадки и высадки пассажиров
  • Пункты посадки и высадки
  • Расстояния поездок
  • Детализированные пассажирские тарифы
  • Виды тарифов
  • Виды оплаты
  • Отчеты водителей о количестве пассажиров

Чтобы ознакомиться с данными о такси Нью-Йорка, выполните следующий запрос:

SELECT TOP 100 * FROM
    OPENROWSET(
        BULK 'https://azureopendatastorage.blob.core.windows.net/nyctlc/yellow/puYear=*/puMonth=*/*.parquet',
        FORMAT='PARQUET'
    ) AS [nyc]

Аналогичным образом можно запросить набор данных о государственных праздниках:

SELECT TOP 100 * FROM
    OPENROWSET(
        BULK 'https://azureopendatastorage.blob.core.windows.net/holidaydatacontainer/Processed/*.parquet',
        FORMAT='PARQUET'
    ) AS [holidays]

Также можно запросить набор данных о погоде, выполнив следующий запрос:

SELECT
    TOP 100 *
FROM  
    OPENROWSET(
        BULK 'https://azureopendatastorage.blob.core.windows.net/isdweatherdatacontainer/ISDWeather/year=*/month=*/*.parquet',
        FORMAT='PARQUET'
    ) AS [weather]

Вы можете получить дополнительные сведения о значении отдельных столбцов в описаниях наборов данных:

Анализ временных рядов, сезонности и выбросов

Ежегодное количество поездок на такси можно подсчитать с помощью следующего запроса:

SELECT
    YEAR(tpepPickupDateTime) AS current_year,
    COUNT(*) AS rides_per_year
FROM
    OPENROWSET(
        BULK 'https://azureopendatastorage.blob.core.windows.net/nyctlc/yellow/puYear=*/puMonth=*/*.parquet',
        FORMAT='PARQUET'
    ) AS [nyc]
WHERE nyc.filepath(1) >= '2009' AND nyc.filepath(1) <= '2019'
GROUP BY YEAR(tpepPickupDateTime)
ORDER BY 1 ASC

Результаты запроса данных о ежегодном количестве поездок на такси:

Снимок экрана: таблица с ежегодным количеством поездок на такси.

Данные можно визуализировать в Synapse Studio. Для этого переключитесь из представления Таблица в представление Диаграмма. В нем доступны разные типы диаграмм: С областями, Линейчатая, Гистограмма, Линейная, Круговая, Точечная. В этом случае построим гистограмму, у которой в столбце Категория задано значение current_year (текущий_год):

Снимок экрана: гистограмма, на котором отображаются поездки в год.

В этой визуализации можно увидеть тенденцию уменьшения числа поездок в течении нескольких лет. Предположительно, причина заключается в недавно возросшей популярности сервисов совместных поездок.

Примечание

На момент написания этого руководства данные за 2019 год были неполными. Поэтому для этого года суммарное количество поездок на такси значительно меньше, чем для других.

Вы можете выполнить анализ для определенного года, например, 2016-го. Это запрос, возвращающий количество поездок за каждый день этого года:

SELECT
    CAST([tpepPickupDateTime] AS DATE) AS [current_day],
    COUNT(*) as rides_per_day
FROM
    OPENROWSET(
        BULK 'https://azureopendatastorage.blob.core.windows.net/nyctlc/yellow/puYear=*/puMonth=*/*.parquet',
        FORMAT='PARQUET'
    ) AS [nyc]
WHERE nyc.filepath(1) = '2016'
GROUP BY CAST([tpepPickupDateTime] AS DATE)
ORDER BY 1 ASC

Результаты этого запроса:

Снимок экрана: таблица с ежедневным числом поездок для результата 2016 года.

Вы также можете визуализировать данные, построив гистограмму. В столбце Категория задайте значение current_day (текущий день), а в столбце Условные обозначения (ряды) — rides_per_day (поездок в день).

Снимок экрана: гистограмма, на котором отображается ежедневное количество поездок за 2016 год.

По графику можно наблюдать недельную периодичность колебаний количества поездок с пиком в субботу. В летние месяцы в связи с периодом отпусков число поездок уменьшается. Кроме того, обратите внимание на ряд значительных спадов без периодичности, причина которых непонятна.

Теперь посмотрим, коррелирует ли падение количества поездок с государственными праздниками. Чтобы определить наличие корреляции, объединим набор данных о поездках такси Нью-Йорка с набором данных о государственных праздниках:

WITH taxi_rides AS (
SELECT
    CAST([tpepPickupDateTime] AS DATE) AS [current_day],
    COUNT(*) as rides_per_day
FROM
    OPENROWSET(
        BULK 'https://azureopendatastorage.blob.core.windows.net/nyctlc/yellow/puYear=*/puMonth=*/*.parquet',
        FORMAT='PARQUET'
    ) AS [nyc]
WHERE nyc.filepath(1) = '2016'
GROUP BY CAST([tpepPickupDateTime] AS DATE)
),
public_holidays AS (
SELECT
    holidayname as holiday,
    date
FROM
    OPENROWSET(
        BULK 'https://azureopendatastorage.blob.core.windows.net/holidaydatacontainer/Processed/*.parquet',
        FORMAT='PARQUET'
    ) AS [holidays]
WHERE countryorregion = 'United States' AND YEAR(date) = 2016
),
joined_data AS (
SELECT
    *
FROM taxi_rides t
LEFT OUTER JOIN public_holidays p on t.current_day = p.date
)

SELECT 
    *,
    holiday_rides = 
    CASE   
      WHEN holiday is null THEN 0   
      WHEN holiday is not null THEN rides_per_day
    END   
FROM joined_data
ORDER BY current_day ASC

Снимок экрана: таблица с результатами поездок на такси NY C и наборов данных

Выделим число поездок на такси в дни государственных праздников. Для этого выберем для столбца Категория значение current_day (текущий день), а для столбцов Условные обозначения (ряды) — rides_per_day (поездок в день) и holiday_rides (поездок в праздники).

Снимок экрана: количество поездок на такси во время государственных праздников в виде диаграммы диаграммы.

По графику видно, что в дни государственных праздников количество поездок на такси уменьшается. Но по-прежнему остается необъясненным огромный спад 23 января. Давайте проверим погоду в Нью-Йорке в этот день, запросив набор данных о погоде:

SELECT
    AVG(windspeed) AS avg_windspeed,
    MIN(windspeed) AS min_windspeed,
    MAX(windspeed) AS max_windspeed,
    AVG(temperature) AS avg_temperature,
    MIN(temperature) AS min_temperature,
    MAX(temperature) AS max_temperature,
    AVG(sealvlpressure) AS avg_sealvlpressure,
    MIN(sealvlpressure) AS min_sealvlpressure,
    MAX(sealvlpressure) AS max_sealvlpressure,
    AVG(precipdepth) AS avg_precipdepth,
    MIN(precipdepth) AS min_precipdepth,
    MAX(precipdepth) AS max_precipdepth,
    AVG(snowdepth) AS avg_snowdepth,
    MIN(snowdepth) AS min_snowdepth,
    MAX(snowdepth) AS max_snowdepth
FROM
    OPENROWSET(
        BULK 'https://azureopendatastorage.blob.core.windows.net/isdweatherdatacontainer/ISDWeather/year=*/month=*/*.parquet',
        FORMAT='PARQUET'
    ) AS [weather]
WHERE countryorregion = 'US' AND CAST([datetime] AS DATE) = '2016-01-23' AND stationname = 'JOHN F KENNEDY INTERNATIONAL AIRPORT'

Снимок экрана: визуализация результатов набора данных о погоде.

Результаты запроса указывают на такие причины уменьшения количества поездок:

  • В этот день в Нью-Йорке была метель и выпало много снега (около 30 см).
  • Было холодно (ниже нуля по Цельсию).
  • Было ветрено (скорость ветра около 10 м/с).

В этом руководстве показано, как специалист по анализу данных может быстро выполнить исследовательский анализ данных. Вы можете объединить различные наборы данных с помощью бессерверного пула SQL и визуализировать результаты с помощью Azure Synapse Studio.

Дальнейшие действия

Узнайте, как подключить бессерверный пул SQL к Power BI Desktop и создавать отчеты.

Сведения об использовании внешних таблиц в бессерверном пуле SQL см. в статье Использование внешних таблиц с Synapse SQL