Pozyskiwanie danych do magazynu przy użyciu języka Transact-SQL

Dotyczy:✅ Magazyn danych w usłudze Microsoft Fabric

Język Transact-SQL oferuje opcje, które pozwalają ładować dane na dużą skalę z istniejących tabel w Lakehouse i magazynie do nowych tabel w magazynie. Te opcje są wygodne, jeśli musisz utworzyć nowe wersje tabeli z zagregowanymi danymi, wersjami tabel z podzbiorem wierszy lub utworzyć tabelę w wyniku złożonego zapytania. Przyjrzyjmy się kilku przykładom.

Tworzenie nowej tabeli z wynikiem zapytania

Magazyn w usłudze Microsoft Fabric umożliwia łatwe tworzenie nowej tabeli na podstawie wyniku zapytania T-SQL przy użyciu następujących instrukcji języka T-SQL:

  • CREATE TABLE AS SELECT (CTAS) polecenie, które umożliwia utworzenie nowej tabeli w magazynie na podstawie danych wyjściowych polecenia SELECT.
  • SELECT INTO klauzula query, która umożliwia wybranie wyników z dowolnego źródła tabeli i przekierowanie wyników do nowej tabeli. Jest to standardowa funkcja w języku T-SQL.

Te dwie instrukcje są podobne, więc poniższe przykłady koncentrują się na instrukcji CTAS.

Instrukcja CTAS uruchamia proces ładowania w nowej tabeli równolegle, dzięki czemu jest wysoce wydajna w przypadku przekształcania danych i tworzenia nowych tabel w środowisku roboczym.

Dla SELECT części instrukcji CTAS można użyć następujących opcji:

  • Odczytywanie tabeli magazynu, na przykład tabeli przejściowej.
  • Odczytywanie folderu Delta Lake Lakehouse przy użyciu autogenerowanej tabeli w endpointzie analityki SQL dla Lakehouse.
  • Odczytywanie plików CSV, Parquet lub JSONL bezpośrednio z usługi Azure Data Lake lub Azure Blob Storage przy użyciu funkcji OPENROWSET.

Aby załadować przykładowy zestaw danych, wykonaj kroki opisane w temacie Ładowanie danych do swojego magazynu przy użyciu instrukcji COPY, aby załadować przykładowe dane do swojego magazynu.

Tworzenie tabeli na podstawie tabeli magazynu

W pierwszym przykładzie pokazano, jak utworzyć nową tabelę, która jest kopią istniejącej dbo.TaxiTrips tabeli, ale filtrowana tak, aby zawierała tylko dane z roku 2023:

CREATE TABLE dbo.TaxiTrips_2023
AS
SELECT * 
FROM dbo.TaxiTrips 
WHERE DATEPART(YEAR, tpep_pickup_datetime) = '2023';

Tworzenie tabeli z folderu usługi Delta Lake

Foldery Delta Lake, które są zapisywane w OneLake, są automatycznie reprezentowane jako tabele, jeśli są przechowywane w folderze /Tables w magazynie typu lakehouse. Poniższy kod tworzy nową tabelę TaxiTrips_2023 z folderu Delta Lake /Tables/TaxiTrips w jeziorze danych MyLakehouse:

CREATE TABLE dbo.TaxiTrips_2023
AS
SELECT * 
FROM MyLakehouse.dbo.TaxiTrips 
WHERE DATEPART(YEAR, tpep_pickup_datetime) = '2023';

Możesz odwołać się do folderu Delta Lake, korzystając z notacji trzyczęściowej, która odnosi się do lakehouse, gdzie są przechowywane pliki. Wszystkie przykłady pokazane w poprzedniej sekcji mają zastosowanie do folderów usługi Delta Lake.

Tworzenie tabeli na podstawie pliku CSV/Parquet/JSONL

Możesz również utworzyć nową tabelę bezpośrednio z pliku zewnętrznego przy użyciu OPENROWSET funkcji . Na przykład poniższy przykładowy kod T-SQL używa symboli zastępczych, aby zademonstrować sposób importowania publicznego pliku Parquet.

CREATE TABLE dbo.<table_name>
AS
SELECT *
FROM OPENROWSET(BULK 'https://<storage account>.blob.core.windows.net/public/<subfolder>/<file name>.parquet') AS data;

Nową tabelę można utworzyć, przekształcając dane z zewnętrznego, publicznie dostępnego pliku CSV:

CREATE TABLE dbo.<table name>
AS
SELECT *
FROM OPENROWSET(BULK 'https://<storage account>.blob.core.windows.net/public/<subfolder>/<file name>.csv') AS data;

Możesz też utworzyć nową tabelę, przekształcając dane z zewnętrznego, publicznie dostępnego pliku JSONL:

CREATE TABLE dbo.<table name>
AS
SELECT *
FROM OPENROWSET(BULK 'https://<storage account>.blob.core.windows.net/public/<subfolder>/<file name>.jsonl') AS data;

Pozyskiwanie danych do istniejących tabel za pomocą zapytań T-SQL

Poprzednie przykłady tworzą nowe tabele na podstawie wyniku zapytania. Aby replikować przykłady na istniejących tabelach, można użyć wzorca INSERT ... SELECT.

Pobierz dane z tabeli magazynowej

Poniższy kod pozyskuje nowe dane z tabeli magazynu do istniejącej tabeli:

INSERT INTO dbo.TaxiTrips_2023
SELECT *
FROM dbo.TaxiTrips
WHERE DATEPART(YEAR, tpep_pickup_datetime) = '2023';

Kryteria zapytania dla SELECT wyrażenia mogą być dowolnym prawidłowym zapytaniem, pod warunkiem że wynikowe typy kolumn zapytania są zgodne z kolumnami w docelowej tabeli. Jeśli nazwy kolumn są określone i zawierają tylko podzbiór kolumn z tabeli docelowej, wszystkie pozostałe kolumny są ładowane jako NULL. Aby uzyskać więcej informacji, zobacz Korzystanie z INSERT INTO...SELECT do zbiorczego importowania danych z minimalnym rejestrowaniem i przy równoległości.

Ingestuj dane z folderu Delta Lake

Foldery Delta Lake, które są przechowywane w OneLake, są automatycznie reprezentowane jako tabele, jeśli są przechowywane w /Tables katalogu w lakehouse.

Poniższy kod wczytuje nowe dane z sekcji folderów Delta Lake /Tables/TaxiTrips w lakehouse MyLakehouse*.

INSERT INTO dbo.TaxiTrips_2023
SELECT *
FROM MyLakehouse.dbo.TaxiTrips 
WHERE DATEPART(YEAR, tpep_pickup_datetime) = '2023';

Importowanie danych z pliku CSV/Parquet/JSONL

Możesz użyć funkcji OPENROWSET jako źródła w celu pozyskiwania plików Parquet, CSV lub JSON z magazynu.

INSERT INTO dbo.<table name>
SELECT *
FROM OPENROWSET(BULK 'https://<storage account>.blob.core.windows.net/public/<subfolder>/<file name>') AS data
WHERE DATEPART(YEAR, tpep_pickup_datetime) = '2023';

Wiele plików można odczytać przy użyciu symboli wieloznacznych, takich jak *.parquet, lub przez określanie wartości docelowych dla katalogów podzielonych na partycje, takich jak /year=*/month=*. Aby zoptymalizować wydajność, zastosuj filtry w klauzuli WHERE, aby wyeliminować niepotrzebne wiersze i partycje podczas wykonywania zapytania.

Te przykłady są podobne do tych używanych podczas importowania za pomocą funkcji COPY INTO. Polecenie COPY INTO jest łatwiejsze w użyciu, szczególnie w przypadku prostych obciążeń danych źródłowych do miejsca docelowego. Jeśli jednak musisz przekształcić dane źródłowe (takie jak konwertowanie wartości lub łączenie z innymi tabelami), użycie INSERT ... SELECT zapewnia elastyczność w wykonywaniu przekształceń podczas przyjmowania danych.

Pozyskiwanie danych z usługi OneLake

Możesz użyć funkcji OPENROWSET jako źródła w celu pozyskiwania danych z magazynu Fabric OneLake. Zastąp {workspaceId} i {lakehouseId} odpowiednimi identyfikatorami GUID obszaru roboczego i lakehouse w następującym przykładzie:

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

Ten przykład opiera się na poprzednim, który odczytuje dane z usługi Azure Data Lake Storage. Użyj tego podejścia, jeśli zachodzi potrzeba przekształcenia danych źródłowych, na przykład przez przekonwertowanie wartości, dołączenie do innych tabel lub odczytanie określonych partycji. W takich przypadkach użycie INSERT ... SELECT zapewnia elastyczność stosowania przekształceń podczas pozyskiwania danych.

Pobieranie danych z tabel znajdujących się w różnych magazynach danych oraz lakehouse'ach.

W przypadku instrukcji CREATE TABLE AS SELECT i INSERT ... SELECTinstrukcja SELECT może również odwoływać się do tabel w magazynach, które różnią się od magazynu, w którym jest przechowywana tabela docelowa, przy użyciu zapytań między magazynami. Można to osiągnąć za pomocą konwencji nazewnictwa [warehouse_or_lakehouse_name.][schema_name.]table_name trzyczęściowej. Załóżmy na przykład, że masz następujące zasoby obszaru roboczego:

  • Lakehouse o nazwie taxi_lakehouse z najnowszymi danymi.
  • Magazyn o nazwie reference_warehouse z tabelami wykorzystywanymi do danych referencyjnych.
  • Przechowalnia danych o nazwie research_warehouse , w której tworzona jest tabela docelowa.

Można utworzyć nową tabelę, która używa trzyczęściowego nazewnictwa do łączenia danych z tabel w tych zasobach obszaru roboczego:

CREATE TABLE research_warehouse.dbo.taxi_trips
AS
SELECT *
FROM taxi_lakehouse.dbo.TaxiTrips AS latest
INNER JOIN reference_warehouse.dbo.TaxiTrips AS reference
ON latest.vendorId_lpep = reference.vendorId_lpep;

Aby dowiedzieć się więcej na temat zapytań między hurtowniami danych, zobacz Jak napisać zapytanie SQL obejmujące wiele baz danych.

Inspekcja i monitorowanie ingestowania kodu T-SQL

Operacje CTAS i INSERT ... SELECT wykonywane za pomocą T-SQL pojawiają się w historii/aktywności zapytań magazynu i mogą być monitorowane wraz z innymi operacjami magazynu.

Opcje pozyskiwania danych

Inne sposoby pozyskiwania danych do magazynu obejmują:

Treści powiązane

  • Last updated on