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

Руководство. Изучение и анализ озер данных с бессерверным пулом SQL

  • Статья

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

Изучив это руководство, вы:

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

Доступ к бессерверному пулу SQL

Каждая рабочая область поставляется с предварительно настроенным бессерверным пулом SQL для использования встроенного пула SQL. Чтобы получить доступ к нему, выполните приведенные далее действия.

  1. Откройте рабочую область и выберите центр разработки.
  2. Нажмите кнопку + "Добавить новый ресурс ".
  3. Выберите Сценарий SQL.

Этот скрипт можно использовать для изучения данных без необходимости резервировать емкость SQL.

Если у вас нет подписки Azure, создайте бесплатную учетную запись, прежде чем приступить к работе.

Доступ к данным руководства

Все данные, используемые в этом руководстве, хранятся в учетной записи хранения azureopendatastorage, которая содержит наборы данных Azure Open Dataset для открытого использования в руководствах, таких как этот. Все скрипты можно запускать непосредственно из рабочей области, пока ваша рабочая область может получить доступ к общедоступной сети.

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

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

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

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

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]

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

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

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

Примечание.

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

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

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

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

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

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

Снимок экрана: таблица n Y 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