Teilen über

Erfassen von Daten in Ihrem Warehouse mithilfe von Transact-SQL

Gilt für:✅ Warehouse in Microsoft Fabric

Die Transact-SQL-Sprache bietet Optionen, mit denen Sie Daten im großen Stil aus vorhandenen Tabellen in Ihrem Lakehouse und Warehouse in neue Tabellen in Ihrem Warehouse laden können. Diese Optionen sind praktisch, wenn Sie neue Versionen einer Tabelle mit aggregierten Daten, Versionen von Tabellen mit einer Teilmenge der Zeilen oder eine Tabelle als Ergebnis einer komplexen Abfrage erstellen müssen. Lassen Sie uns einige Beispiele näher betrachten.

Erstellen einer neuen Tabelle mit dem Ergebnis einer Abfrage

Warehouse in Microsoft Fabric ermöglicht Ihnen das einfache Erstellen einer neuen Tabelle basierend auf einem Ergebnis einer T-SQL-Abfrage mithilfe der folgenden T-SQL-Anweisungen:

  • CREATE TABLE AS SELECT (CTAS)-Anweisung, mit der Sie aus der Ausgabe einer SELECT Anweisung eine neue Tabelle in Ihrem Lager erstellen können.
  • SELECT INTO Abfrageklausel, mit der Sie Ergebnisse aus einer beliebigen Tabellenquelle auswählen und die Ergebnisse in eine neue Tabelle umleiten können. Dies ist ein Standardfeature in der T-SQL-Sprache.

Diese beiden Anweisungen sind ähnlich, daher konzentrieren sich die folgenden Beispiele auf die CTAS-Anweisung.

Die CTAS-Anweisung führt den Aufnahmevorgang parallel in die neue Tabelle aus, wodurch sie für die Datentransformation und das Erstellen neuer Tabellen in Ihrem Arbeitsbereich sehr effizient ist.

Sie können die folgenden Optionen für den SELECT Abschnitt der CTAS-Anweisung verwenden:

  • Lesen einer Lagertabelle, z. B. einer Stagingtabelle.
  • Lesen eines Lakehouse Delta Lake-Ordners mithilfe einer automatisch generierten Tabelle im SQL-Analyseendpunkt für Lakehouse.
  • Lesen von CSV-, Parquet- oder JSONL-Dateien direkt aus Azure Data Lake oder Azure Blob Storage mithilfe der OPENROWSET Funktion.

Note

Für die Beispiele in diesem Artikel wird das Beispieldataset „Bing COVID-19“ verwendet. Führen Sie zum Laden des Beispieldatasets die Schritte unter Erfassen von Daten in Ihrem Warehouse mithilfe der COPY-Anweisung aus, um die Beispieldaten in Ihrem Warehouse zu erstellen.

Tabelle aus Lagertabelle erstellen

Im ersten Beispiel wird veranschaulicht, wie eine neue Tabelle erstellt wird, die eine Kopie der vorhandenen dbo.bing_covid19_data_2023 Tabelle ist, aber nur nach Daten aus dem Jahr 2023 gefiltert wurde:

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

Sie können auch eine neue Tabelle mit den neuen Spalten year, month und dayofmonth erstellen, die aus der updated-Spalte der Quelltabelle abgerufene Werte enthält. Das kann hilfreich sein, wenn Sie versuchen, Infektionsdaten nach Jahr zu visualisieren oder herauszufinden, in welchen Monaten die meisten COVID-19-Fälle beobachtet wurden:

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;

Weiteres Beispiel: Sie können eine neue Tabelle erstellen, in der die Anzahl der in jedem Monat beobachteten Fälle unabhängig vom Jahr zusammengefasst wird, um zu bewerten, wie sich die Saisonalität auf die Ausbreitung in einem bestimmten Land oder einer bestimmten Region auswirken kann. Hierfür wird die im vorherigen Beispiel erstellte Tabelle mit der neuen month-Spalte als Quelle verwendet:

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

Anhand dieser neuen Tabelle können wir sehen, dass in den USA in allen Jahren die meisten bestätigten Fälle im Monat January beobachtet wurden, gefolgt von December und October. April ist der Monat mit den insgesamt niedrigsten Fallzahlen:

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

Tabelle aus dem Ordner "Delta Lake" erstellen

Die In OneLake gespeicherten Delta Lake-Ordner werden automatisch als Tabellen dargestellt, wenn sie in einem Seehaus im Ordner "/Tables " gespeichert sind. Der folgende Code erstellt eine neue Tabelle bing_covid19_data_2023 aus dem Ordner "/Tables/bing_covid19_delta_lake " im MyLakehouse Lakehouse :

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

Sie können auf den Ordner Delta Lake verweisen, indem Sie die dreiteilige Schreibweise verwenden, die auf das Seehaus verweist, in dem die Dateien gespeichert sind. Alle Beispiele im vorherigen Abschnitt gelten für Delta Lake-Ordner.

Erstellen einer Tabelle aus der CSV-/Parkett-/JSONL-Datei

Anstatt Daten aus der Warehouse-Tabelle bing_covid19_data zu lesen, können Sie mithilfe der OPENROWSET Funktion auch eine neue Tabelle direkt aus einer externen Datei erstellen:

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

Sie können auch eine neue Tabelle erstellen, indem Sie Daten aus einer externen CSV-Datei transformieren:

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;

Weiteres Beispiel: Sie können eine neue Tabelle erstellen, in der die Anzahl der in jedem Monat beobachteten Fälle unabhängig vom Jahr zusammengefasst wird, um zu bewerten, wie sich die Saisonalität auf die Ausbreitung in einem bestimmten Land oder einer bestimmten Region auswirken kann. Hierfür wird die im vorherigen Beispiel erstellte Tabelle mit der neuen month-Spalte als Quelle verwendet:

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.jsonl') AS data
WHERE DATEPART(YEAR, updated) = '2022'
GROUP BY country_region, DATEPART(MONTH, updated);

Anhand dieser neuen Tabelle können wir sehen, dass in den USA in allen Jahren die meisten bestätigten Fälle im Monat January beobachtet wurden, gefolgt von December und October. April ist der Monat mit den insgesamt niedrigsten Fallzahlen:

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

Screenshot: Abfrageergebnisse mit den absteigenden Infektionszahlen nach Monat in den USA, nach Monat sortiert. Der Monat mit der Nummer 1 steht ganz oben.

Weitere Beispiele und Syntaxreferenzen finden Sie unter CREATE TABLE AS SELECT (Transact-SQL).

Erfassen Sie Daten in vorhandenen Tabellen mit T-SQL-Abfragen

In den vorherigen Beispielen werden neue Tabellen basierend auf dem Ergebnis einer Abfrage erstellt. Um die Beispiele, aber in vorhandenen Tabellen zu replizieren, kann das INSERT ... SELECT Muster verwendet werden.

Importieren von Daten aus der Lager-Tabelle

Mit dem folgenden Code werden neue Daten aus einer Lagertabelle in eine vorhandene Tabelle aufgenommen:

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

Die Abfragekriterien für die SELECT-Anweisung können eine beliebige gültige Abfrage sein, sofern die resultierenden Abfragespaltentypen mit den Spalten in der Zieltabelle übereinstimmen. Wenn Spaltennamen angegeben sind und nur eine Teilmenge der Spalten aus der Zieltabelle enthalten ist, werden alle anderen Spalten als NULL geladen. Weitere Informationen finden Sie unter Verwenden von INSERT INTO…SELECT für den Massenimport von Daten mit minimaler Protokollierung und Parallelität.

Daten aus dem Ordner "Delta Lake" einlesen

Die In OneLake gespeicherten Delta Lake-Ordner werden automatisch als Tabellen dargestellt, wenn sie in einem Lakehouse in /Tables einem Ordner gespeichert sind.

Der folgende Code erfasst neue Daten aus dem Ordnerabschnitt /Tables/bing_covid19_delta_lake Delta Lake im MyLakehouse* Lakehouse

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

Importieren von Daten aus einer CSV-/Parquet-/JSONL-Datei

Sie können die OPENROWSET Funktion als Quelle verwenden, um Parkett-, CSV- oder JSON-Dateien aus dem Speicher aufzunehmen:

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

Sie können mehrere Dateien mithilfe von Wildcards wie *.parquet, oder mithilfe von partitionierten Verzeichnissen wie /year=*/month=*. Um die Leistung zu optimieren, wenden Sie Filter in der WHERE-Klausel an, um unnötige Zeilen und Partitionen während der Abfrageausführung zu beseitigen.

Dieses Beispiel ähnelt denjenigen, die bei der Aufnahme mit COPY INTO verwendet werden. Der BEFEHL "KOPIEREN IN" ist einfacher zu verwenden, insbesondere für einfache Laden von Quell-zu-Ziel-Daten. Wenn Sie Jedoch Quelldaten transformieren müssen (z. B. Konvertieren von Werten oder Verknüpfen mit anderen Tabellen), bietet Ihnen die INSERT ... SELECT Verwendung der Flexibilität, Transformationen während der Aufnahme durchzuführen.

Aufnehmen von Daten aus OneLake

Sie können die OPENROWSET Funktion als Quelle verwenden, um Daten aus Fabric OneLake-Speicher aufzunehmen. Ersetzen Und {workspaceId}{lakehouseId} durch die entsprechenden Arbeitsbereichs- und Lakehouse-GUIDs im folgenden Beispiel:

INSERT INTO dbo.bing_covid19_data_2023
SELECT *
FROM OPENROWSET(BULK 'https://onelake.dfs.fabric.microsoft.com/{workspaceId}/{lakehouseId}/Files/year=*/month=*/*.parquet') AS data
WHERE data.filepath(1) = '2023'

Dieses Beispiel basiert auf dem vorherigen, das Daten aus Azure Data Lake Storage liest. Verwenden Sie diesen Ansatz, wenn Sie Quelldaten transformieren müssen, z. B. Werte konvertieren, mit anderen Tabellen verknüpfen oder bestimmte Partitionen lesen. In solchen Fällen bietet die Verwendung INSERT ... SELECT die Flexibilität, Transformationen während der Datenaufnahme anzuwenden.

Erfassen Sie Daten aus Tabellen in verschiedenen Warehouses und Lakehouses

Sowohl CREATE TABLE AS SELECT als auch INSERT ... SELECT können in der SELECT-Anweisung auf Tabellen in anderen Lagerhäusern als demjenigen verweisen, in dem Ihre Zieltabelle gespeichert ist, indem lagerübergreifende Abfragen verwendet werden. Das kann mithilfe der dreiteiligen Namenskonvention [warehouse_or_lakehouse_name.][schema_name.]table_name erreicht werden. Gehen wir beispielsweise von den folgenden Arbeitsbereichressourcen aus:

  • Ein Lakehouse namens cases_lakehouse mit den neuesten Falldaten
  • Ein Warehouse namens reference_warehouse mit Tabellen für Verweisdaten
  • Ein Warehouse namens research_warehouse mit der Zieltabelle

Eine neue Tabelle kann erstellt werden, die die dreiteilige Namenskonvention verwendet, um Daten aus Tabellen in diesen Arbeitsbereichsressourcen zu kombinieren:

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;

Weitere Informationen zu warehouseübergreifenden Abfragen finden Sie unter Schreiben einer datenbankübergreifenden SQL-Abfrage.

Prüfen und Überwachen der T-SQL-Erfassung

Sowohl CTAS als auch INSERT ... SELECT Vorgänge, die über T-SQL ausgeführt werden, erscheinen im Lagerabfrageverlauf/-aktivität und können zusammen mit anderen Lagervorgängen überwacht werden.

Optionen für die Datenerfassung

Weitere Möglichkeiten zum Aufnehmen von Daten in Ihr Lager sind:

Verwandte Inhalte

  • Last updated on