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

Загрузка данных в Хранилище данных с использованием Transact-SQL

Область применения:✅ хранилище в Microsoft Fabric

Язык Transact-SQL предлагает варианты, позволяющие масштабно загружать данные из существующих таблиц в вашем lakehouse и хранилище данных в новые таблицы вашего хранилища. Эти параметры удобны, если необходимо создать новые версии таблицы с агрегированными данными, версиями таблиц с подмножеством строк или создать таблицу в результате сложного запроса. Рассмотрим некоторые примеры.

Создание таблицы с результатом запроса

Хранилище в Microsoft Fabric позволяет легко создавать новую таблицу на основе результата запроса T-SQL, используя следующие инструкции T-SQL:

  • CREATE TABLE AS SELECT Инструкция CTAS, позволяющая создать новую таблицу в хранилище из выходных данных инструкции SELECT.
  • SELECT INTO Предложение запроса, позволяющее выбрать результаты из любого источника таблицы и перенаправить результаты в новую таблицу. Это стандартная функция на языке T-SQL.

Эти две инструкции похожи, поэтому следующие примеры сосредоточены на инструкции CTAS.

Оператор CTAS выполняет операцию загрузки в новую таблицу параллельно, что делает его очень эффективным для преобразования данных и создания новых таблиц в вашем рабочем пространстве.

Для части инструкции CTAS можно использовать следующие параметры SELECT :

  • Чтение таблицы хранилища, например промежуточной таблицы.
  • Чтение папки Delta Lake в Lakehouse с использованием автоматически сгенерированной таблицы в SQL-аналитическом терминале для Lakehouse.
  • Чтение CSV-файлов или Parquet непосредственно из Azure Data Lake или хранилища блобов Azure с использованием функции OPENROWSET.

Примечание.

В примерах в этой статье используется пример набора данных Bing COVID-19. Чтобы загрузить образец набора данных, выполните действия, описанные в разделе "Загрузка данных в хранилище с использованием инструкции COPY", чтобы загрузить образец данных в хранилище.

Создание таблицы из таблицы Warehouse

В первом примере показано, как создать новую таблицу, которая является копией существующей dbo.bing_covid19_data_2023 таблицы, но отфильтровывалась по данным только в 2023 году:

CREATE TABLE dbo.bing_covid19_data_2023
AS
SELECT * 
FROM dbo.bing_covid19_data 
WHERE DATEPART(YEAR, updated) = '2023';

Вы также можете создать новую таблицу с новыми yearстолбцами, monthdayofmonth столбцами, значениями, полученными из updated столбца в исходной таблице. Это может быть полезно, если вы пытаетесь визуализировать данные инфекции по годам или увидеть месяцы, когда наблюдается большинство случаев COVID-19:

CREATE TABLE dbo.bing_covid19_data_with_year_month_day
AS
SELECT DATEPART(YEAR, updated) AS [year],
       DATEPART(MONTH, updated) AS [month],
       DATEPART(DAY, updated) AS [dayofmonth],
       * 
FROM dbo.bing_covid19_data;

В другом примере можно создать новую таблицу, которая суммирует количество случаев, наблюдаемых в каждом месяце, независимо от года, чтобы оценить, как сезонность влияет на распространение в заданной стране или регионе. Она использует таблицу, созданную в предыдущем примере, с новым month столбцом в качестве источника:

CREATE TABLE dbo.infections_by_month
AS
SELECT country_region, [month],
       SUM(CAST(confirmed as bigint)) AS confirmed_sum
FROM dbo.bing_covid19_data_with_year_month_day
GROUP BY country_region, [month];

На основе этой новой таблицы мы видим, что в США было зафиксировано больше подтвержденных случаев за все годы в месяце January, а затем в December и October. April — месяц с наименьшим числом случаев в целом:

SELECT * FROM dbo.infections_by_month
WHERE country_region = 'United States'
ORDER BY confirmed_sum DESC;

Создание таблицы из папки Delta Lake

Папки Delta Lake, сохраненные в OneLake, автоматически представляются в виде таблиц, если они хранятся в папке /Tables в lakehouse. Следующий код создает новую таблицу bing_covid19_data_2023 из папки Delta Lake /Table/bing_covid19_delta_lake в озере MyLakehouse :

CREATE TABLE dbo.bing_covid19_data_2023
AS
SELECT * 
FROM MyLakehouse.dbo.bing_covid19_delta_lake 
WHERE DATEPART(YEAR, updated) = '2023';

Вы можете ссылаться на папку Delta Lake с помощью трехчастной нотации, ссылающейся на lakehouse, где хранятся файлы. Все примеры, показанные в предыдущем разделе, применимы к папкам Delta Lake.

Создание таблицы из CSV/Parquet-файла

Вместо чтения данных из таблицы Warehouse bing_covid19_data можно также создать новую таблицу непосредственно из внешнего файла с помощью OPENROWSET функции:

CREATE TABLE dbo.bing_covid19_data_2022
AS
SELECT *
FROM OPENROWSET(BULK 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.parquet') AS data;
WHERE DATEPART(YEAR, updated) = '2022'

Вы также можете создать новую таблицу, преобразовав данные из внешнего CSV-файла:

CREATE TABLE dbo.bing_covid19_data_with_year_month_day
AS
SELECT DATEPART(YEAR, updated) AS [year], 
       DATEPART(MONTH, updated) AS [month],
       DATEPART(DAY, updated) AS [dayofmonth],
       *
FROM OPENROWSET(BULK 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.csv') AS data;

В другом примере можно создать новую таблицу, которая суммирует количество случаев, наблюдаемых в каждом месяце, независимо от года, чтобы оценить, как сезонность влияет на распространение в заданной стране или регионе. Она использует таблицу, созданную в предыдущем примере, с новым month столбцом в качестве источника:

CREATE TABLE dbo.infections_by_month_2022
AS
SELECT country_region,
       DATEPART(MONTH, updated) AS [month],
       SUM(CAST(confirmed as bigint)) AS [confirmed_sum]
FROM OPENROWSET(BULK 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.parquet') AS data
WHERE DATEPART(YEAR, updated) = '2022'
GROUP BY country_region, DATEPART(MONTH, updated);

На основе этой новой таблицы мы видим, что в США было зафиксировано больше подтвержденных случаев за все годы в месяце January, а затем в December и October. April — месяц с наименьшим числом случаев в целом:

SELECT * FROM dbo.infections_by_month_2022
WHERE country_region = 'United States'
ORDER BY confirmed_sum DESC;

Снимок экрана: результаты запроса, показывающие количество инфекций по месяцам в США, упорядоченных по месяцам в порядке убывания. Номер месяца 1 отображается сверху.

Дополнительные примеры и справочные материалы по синтаксису см. в статье CREATE TABLE AS SELECT (Transact-SQL).

Прием данных в существующие таблицы с помощью запросов T-SQL

В предыдущих примерах создаются новые таблицы на основе результата запроса. Чтобы воспроизвести примеры на существующих таблицах, можно использовать шаблон INSERT ... SELECT.

Загрузка данных из таблицы Хранилища

Следующий код возвращает новые данные из таблицы хранилища в существующую таблицу:

INSERT INTO dbo.bing_covid19_data_2023
SELECT *
FROM dbo.bing_covid19_data
WHERE DATEPART(YEAR, updated) = '2023';

Критерии запроса для SELECT инструкции могут быть любым допустимым запросом, если результирующий тип столбцов запроса соответствует столбцам в целевой таблице. Если имена столбцов указаны и включают только подмножество столбцов из целевой таблицы, все остальные столбцы загружаются как NULL. Дополнительные сведения см. в разделе Using INSERT INTO... SELECT для массового импорта данных с минимальным ведением журнала и параллелизмом.

Прием данных из папки Delta Lake

Папки Delta Lake, сохраненные в OneLake, автоматически представляются в виде таблиц, если они хранятся в папке /Tables в lakehouse. Следующий код возвращает новые данные из папки Delta Lake /Tables/bing_covid19_delta_lake раздела MyLakehouse lakehouse

INSERT INTO dbo.bing_covid19_data_2023
SELECT *
FROM MyLakehouse.dbo.bing_covid19_delta_lake 
WHERE DATEPART(YEAR, updated) = '2023';

Загрузка данных из CSV/Parquet-файла

Можно использовать функцию OPENROWSET в качестве источника для приема данных из Azure Data Lake или Azure Blob-хранилища:

INSERT INTO dbo.bing_covid19_data_2023
SELECT *
FROM OPENROWSET(BULK 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.parquet') AS data
WHERE DATEPART(YEAR, updated) = '2023';

Эти примеры аналогичны тем, которые используются в загрузке с помощью COPY INTO. Команда COPY INTO удобнее использовать, особенно для простых загрузок данных из источника в назначение. Однако если необходимо преобразовать исходные данные (например, преобразование значений или присоединение к другим таблицам), использование INSERT ... SELECT предоставляет гибкость для выполнения преобразований во время приема.

Прием данных из таблиц в разных хранилищах и озерах

CREATE TABLE AS SELECT INSERT ... SELECT SELECT В обоих случаях инструкция также может ссылаться на таблицы на хранилищах, отличающихся от хранилища, где хранится целевая таблица, с помощью запросов между хранилищами. Это можно достичь, используя трёхчастное соглашение об именовании [warehouse_or_lakehouse_name.][schema_name.]table_name. Например, предположим, что у вас есть следующие ресурсы рабочей области:

  • Лейкхаус под названием cases_lakehouse с последними данными по делу.
  • Склад с именем reference_warehouse, содержащий таблицы, используемые для эталонных данных.
  • Хранилище с именем research_warehouse , в котором создается целевая таблица.

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

CREATE TABLE research_warehouse.dbo.cases_by_continent
AS
SELECT *
FROM cases_lakehouse.dbo.bing_covid19_data AS cases
INNER JOIN reference_warehouse.dbo.bing_covid19_data AS reference
ON cases.iso3 = reference.countrycode;

Дополнительные сведения о межсайтовых запросах см. в статье "Написание запроса SQL между базами данных".

Связанный контент