Ingestování dat do vašeho skladu pomocí jazyka Transact-SQL

Platí pro:✅ Warehouse v Microsoft Fabric

Jazyk Transact-SQL nabízí možnosti, které můžete použít k načtení dat ve velkém rozsahu z existujících tabulek ve vašem datovém jezeře a skladu do nových tabulek ve vašem skladu. Tyto možnosti jsou vhodné, pokud potřebujete vytvořit nové verze tabulky s agregovanými daty, verzemi tabulek s podmnožinou řádků nebo vytvořit tabulku v důsledku složitého dotazu. Pojďme se podívat na některé příklady.

Vytvoření nové tabulky s výsledkem dotazu

Sklad v Microsoft Fabric umožňuje snadno vytvořit novou tabulku na základě výsledku dotazu T-SQL pomocí následujících příkazů T-SQL:

• CREATE TABLE AS SELECT Příkaz (CTAS), který umožňuje vytvořit novou tabulku ve skladu z výstupu SELECT příkazu.
• SELECT INTO klauzule dotazu, která umožňuje vybrat výsledky z libovolného zdroje tabulky a přesměrovat výsledky do nové tabulky. Toto je standardní funkce v jazyce T-SQL.

Tyto dva příkazy jsou podobné, takže následující příklady se zaměřují na příkaz CTAS.

Příkaz CTAS spouští operaci příjmu dat do nové tabulky paralelně, takže je vysoce efektivní pro transformaci dat a vytváření nových tabulek ve vašem pracovním prostoru.

Pro SELECT část příkazu CTAS můžete použít následující možnosti:

• Čtení tabulky skladu, například pracovní tabulky
• Čtení složky Lakehouse Delta Lake pomocí automaticky generované tabulky v koncovém bodu analýzy SQL pro Lakehouse
• Čtení souborů CSV nebo Parquet přímo ze služby Azure Data Lake nebo Azure Blob Storage pomocí OPENROWSET funkce

Poznámka:

Příklady v tomto článku používají ukázkovou datovou sadu Covid-19 Bingu. Pokud chcete načíst ukázkovou datovou sadu, postupujte podle kroků v Ingestování dat do vašeho skladu pomocí příkazu COPY a vytvořte ukázková data do vašeho skladu.

Vytvoření tabulky z tabulky Warehouse

První příklad ukazuje, jak vytvořit novou tabulku, která je kopií existující dbo.bing_covid19_data_2023 tabulky, ale filtrovaná na data pouze z roku 2023:

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

Můžete také vytvořit novou tabulku s novými yearmonthdayofmonth , sloupci a hodnotami získanými ze updated sloupce ve zdrojové tabulce. To může být užitečné, pokud se pokoušíte vizualizovat data o infekcích podle roku nebo zobrazit měsíce, kdy se pozorují případy 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;

V dalším příkladu můžete vytvořit novou tabulku, která shrnuje počet případů pozorovaných v jednotlivých měsících bez ohledu na rok, abyste zjistili, jak sezónnost ovlivňuje rozpětí v dané zemi/oblasti. Používá tabulku vytvořenou v předchozím příkladu s novým month sloupcem jako zdrojem:

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];

Na základě této nové tabulky vidíme, že USA zaznamenal více potvrzených případů za všechny roky v měsíci January, po kterém následuje December a October. April je měsíc s nejnižším celkovým počtem případů:

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

Vytvoření tabulky ze složky Delta Lake

Složky Delta Lake uložené v OneLake se automaticky reprezentují jako tabulky, pokud jsou uložené ve složce /Tables v lakehouse. Následující kód vytvoří novou tabulku bing_covid19_data_2023 ze složky Delta Lake /Tables/bing_covid19_delta_lake v MyLakehouse lakehouse:

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

Na složku Delta Lake můžete odkazovat pomocí notace se třemi částmi názvu, která odkazuje na lakehouse, ve kterém jsou soubory uloženy. Všechny příklady uvedené v předchozí části platí pro složky Delta Lake.

Vytvoření tabulky ze souboru CSV/Parquet

Místo čtení dat z tabulky Warehouse bing_covid19_data můžete také vytvořit novou tabulku přímo z externího souboru pomocí OPENROWSET funkce:

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'

Novou tabulku můžete vytvořit také transformací dat z externího souboru 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;

V dalším příkladu můžete vytvořit novou tabulku, která shrnuje počet případů pozorovaných v jednotlivých měsících bez ohledu na rok, abyste zjistili, jak sezónnost ovlivňuje rozpětí v dané zemi/oblasti. Používá tabulku vytvořenou v předchozím příkladu s novým month sloupcem jako zdrojem:

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);

Na základě této nové tabulky vidíme, že USA zaznamenal více potvrzených případů za všechny roky v měsíci January, po kterém následuje December a October. April je měsíc s nejnižším celkovým počtem případů:

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

Další příklady a odkazy na syntaxi najdete v tématu CREATE TABLE AS SELECT (Transact-SQL).

Ingestování dat do existujících tabulek pomocí dotazů T-SQL

Předchozí příklady vytvářejí nové tabulky na základě výsledku dotazu. K replikaci příkladů na existující tabulky lze použít vzor INSERT ... SELECT.

Příjem dat z tabulky Warehouse

Následující kód ingestuje nová data z tabulky skladu do existující tabulky:

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

Kritéria dotazu pro příkaz SELECT mohou být libovolný platný dotaz, pokud výsledné typy sloupců dotazu odpovídají sloupcům v cílové tabulce. Pokud jsou zadány názvy sloupců a obsahují pouze podmnožinu sloupců z cílové tabulky, všechny ostatní sloupce se načtou jako NULL. Další informace naleznete v tématu Použití funkce INSERT INTO... Select to Bulk Import data with minimal logging and parallelism.

Příjem dat ze složky Delta Lake

Složky Delta Lake uložené v OneLake se automaticky reprezentují jako tabulky, pokud jsou uložené ve složce /Tables v lakehousu. Následující kód načítá nová data ze složky Delta Lake /Tables/bing_covid19_delta_lake v lakehouse MyLakehouse.

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

Příjem dat ze souboru CSV/Parquet

Funkci můžete použít OPENROWSET jako zdroj, abyste mohli ingestovat data z Azure Data Lake nebo Azure Blob Storage:

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';

Tento příklad je podobný těm, které se používají při příjmu dat pomocí funkce COPY INTO. Příkaz COPY INTO se snadněji používá, zejména pro přímé načtení dat ze zdroje do cíle. Pokud však potřebujete transformovat zdrojová data (například převod hodnot nebo spojení s jinými tabulkami), použijte insert ... SELECT vám dává flexibilitu při provádění transformací během příjmu dat.

Příjem dat z tabulek v různých skladech a v jezeře

Pro oba CREATE TABLE AS SELECT i INSERT ... SELECT může příkaz SELECT také odkazovat na tabulky ve skladech, které se liší od skladu, ve kterém je uložená cílová tabulka, pomocí dotazů mezi sklady. Toho lze dosáhnout pomocí třídílné konvence [warehouse_or_lakehouse_name.][schema_name.]table_namepojmenování . Předpokládejme například, že máte následující prostředky pracovního prostoru:

• Jezero s názvem cases_lakehouse s nejnovějšími daty případu.
• Sklad jménem reference_warehouse, který obsahuje tabulky používané pro referenční data.
• Sklad s názvem research_warehouse , ve kterém se vytvoří cílová tabulka.

Můžete vytvořit novou tabulku, která používá třídílné pojmenování ke kombinování dat z tabulek v těchto prostředcích pracovního prostoru:

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;

Další informace o dotazech napříč sklady najdete v tématu Zápis dotazu SQL napříč databázemi.

Související obsah

• Příjem dat do skladu
• Ingestování dat do vašeho skladu pomocí příkazu COPY
• Ingestování dat do skladu pomocí datových kanálů
• Zápis dotazu SQL napříč databázemi