Tworzenie i używanie natywnych tabel zewnętrznych przy użyciu pul SQL w usłudze Azure Synapse Analytics

W tej sekcji dowiesz się, jak tworzyć i używać natywnych tabel zewnętrznych w pulach SQL usługi Synapse. Natywne tabele zewnętrzne mają lepszą wydajność w porównaniu z tabelami zewnętrznymi z definicją TYPE=HADOOP zewnętrznego źródła danych. Dzieje się tak, ponieważ natywne tabele zewnętrzne używają kodu natywnego do uzyskiwania dostępu do danych zewnętrznych.

Tabele zewnętrzne są przydatne, gdy chcesz kontrolować dostęp do danych zewnętrznych w puli SQL usługi Synapse. Tabele zewnętrzne są również przydatne, jeśli chcesz używać narzędzi, takich jak usługa Power BI, w połączeniu z pulą SQL usługi Synapse. Tabele zewnętrzne mogą uzyskiwać dostęp do dwóch typów pamięci:

• Publiczna przestrzeń dyskowa, w której użytkownicy mają dostęp do plików.
• Chroniony magazyn danych, gdzie użytkownicy uzyskują dostęp do plików z wykorzystaniem poświadczeń SAS, tożsamości Microsoft Entra lub tożsamości zarządzanej obszaru roboczego Synapse.

Uwaga

W dedykowanych pulach SQL można używać tylko natywnych tabel zewnętrznych z typem pliku Parquet, a ta funkcja jest dostępna w publicznej wersji zapoznawczej. Jeśli chcesz użyć ogólnie dostępnej funkcji czytnika Parquet w dedykowanych pulach SQL lub chcesz uzyskać dostęp do plików CSV lub ORC, użyj tabel zewnętrznych usługi Hadoop. Natywne tabele zewnętrzne są ogólnie dostępne w bezserwerowych pulach SQL. Dowiedz się więcej o różnicach między macierzystymi i zewnętrznymi tabelami usługi Hadoop w temacie Używanie tabel zewnętrznych z usługą Synapse SQL.

W poniższej tabeli wymieniono obsługiwane formaty danych:

Format danych (natywne tabele zewnętrzne)	Bezserwerowy pul SQL	Dedykowana pula SQL
Parkiet (Podłoga parkietowa)	Tak (GA - ogólna dostępność)	Tak (publiczna wersja zapoznawcza)
CSV	Tak	Nie (alternatywnie użyj tabel zewnętrznych usługi Hadoop)
delta	Tak	Nie.
Iskra	Tak	Nie.
Dataverse	Tak	Nie.
Formaty danych usługi Azure Cosmos DB (JSON, BSON itp.)	Nie (Alternatywnie utwórz widoki)	Nie.

Wymagania wstępne

Pierwszym krokiem jest utworzenie bazy danych, w której zostaną utworzone tabele. Baza danych musi mieć klucz główny, aby chronić poświadczenia. Aby uzyskać więcej informacji na ten temat, zobacz CREATE MASTER KEY (Transact-SQL). Następnie utwórz następujące obiekty, które są używane w tym przykładzie:

• ZEWNĘTRZNE ŹRÓDŁO sqlondemanddemo DANYCH odwołujące się do publicznego konta magazynu demonstracyjnego oraz ZEWNĘTRZNE ŹRÓDŁO nyctlc DANYCH odwołujące się do publicznie dostępnego konta usługi Azure Storage w lokalizacji https://azureopendatastorage.blob.core.windows.net/nyctlc/.

  CREATE EXTERNAL DATA SOURCE SqlOnDemandDemo WITH (LOCATION = 'https://fabrictutorialdata.blob.core.windows.net/sampledata/Synapse');
  GO
  CREATE EXTERNAL DATA SOURCE nyctlc
  WITH ( LOCATION = 'https://azureopendatastorage.blob.core.windows.net/nyctlc/')
  GO
  CREATE EXTERNAL DATA SOURCE DeltaLakeStorage
  WITH ( location = 'https://fabrictutorialdata.blob.core.windows.net/sampledata/Synapse/delta-lake' );
  

• Formaty plików QuotedCSVWithHeaderFormat i ParquetFormat, które opisują typy plików CSV i "parquet".

  CREATE EXTERNAL FILE FORMAT QuotedCsvWithHeaderFormat
  WITH (  
      FORMAT_TYPE = DELIMITEDTEXT,
      FORMAT_OPTIONS ( FIELD_TERMINATOR = ',', STRING_DELIMITER = '"', FIRST_ROW = 2   )
  );
  GO
  CREATE EXTERNAL FILE FORMAT ParquetFormat WITH (  FORMAT_TYPE = PARQUET );
  GO
  CREATE EXTERNAL FILE FORMAT DeltaLakeFormat WITH (  FORMAT_TYPE = DELTA );
  GO
  

Zapytania w tym artykule będą wykonywane na Twojej przykładowej bazie danych i będą używać tych obiektów.

Tabela zewnętrzna w pliku

Możesz tworzyć tabele zewnętrzne, które uzyskują dostęp do danych na koncie Azure Storage, umożliwiającym dostęp użytkownikom posiadającym tożsamość Microsoft Entra lub klucz SAS (Sygnatury Dostępu Współdzielonego). Tabele zewnętrzne można tworzyć w taki sam sposób, jak w przypadku zwykłych tabel zewnętrznych programu SQL Server.

Poniższe zapytanie tworzy tabelę zewnętrzną, która odczytuje plik population.csv z poziomu konta magazynu usługi Azure SynapseSQL, do którego odwołuje się sqlondemanddemo źródło danych.

Uwaga

Zmień pierwszy wiersz zapytania, czyli [mydbname], więc używasz utworzonej bazy danych.

USE [mydbname];
GO
CREATE EXTERNAL TABLE populationExternalTable
(
    [country_code] VARCHAR (5) COLLATE Latin1_General_BIN2,
    [country_name] VARCHAR (100) COLLATE Latin1_General_BIN2,
    [year] smallint,
    [population] bigint
)
WITH (
    LOCATION = 'csv/population/population.csv',
    DATA_SOURCE = sqlondemanddemo,
    FILE_FORMAT = QuotedCSVWithHeaderFormat
);

Natywne tabele CSV są obecnie dostępne tylko w bezserwerowych pulach SQL.

Tabela zewnętrzna w zestawie plików

Możesz utworzyć tabele zewnętrzne odczytujące dane z zestawu plików umieszczonych w usłudze Azure Storage:

CREATE EXTERNAL TABLE Taxi (
     vendor_id VARCHAR(100) COLLATE Latin1_General_BIN2, 
     pickup_datetime DATETIME2, 
     dropoff_datetime DATETIME2,
     passenger_count INT,
     trip_distance FLOAT,
     fare_amount FLOAT,
     tip_amount FLOAT,
     tolls_amount FLOAT,
     total_amount FLOAT
) WITH (
         LOCATION = 'yellow/puYear=*/puMonth=*/*.parquet',
         DATA_SOURCE = nyctlc,
         FILE_FORMAT = ParquetFormat
);

Można określić wzorzec, który muszą spełniać pliki, aby były uwzględniane w tabeli zewnętrznej. Wzór jest wymagany tylko dla tabel Parquet i CSV. Jeśli używasz formatu usługi Delta Lake, musisz określić tylko folder główny, a tabela zewnętrzna automatycznie znajdzie wzorzec.

Uwaga

Tabela jest tworzona w strukturze folderów partycjonowanych, ale nie można skorzystać z niektórych opcji eliminacji partycji. Jeśli chcesz uzyskać lepszą wydajność, pomijając pliki, które nie spełniają określonego kryterium (na przykład określonego roku lub miesiąca w tym przypadku), użyj widoków dla danych zewnętrznych.

Tabela zewnętrzna w dołączanych plikach

Pliki, do których odwołuje się tabela zewnętrzna, nie powinny być zmieniane podczas uruchamiania zapytania. W długotrwałym zapytaniu pula SQL może ponowić próbę odczytu, odczytu części plików, a nawet wielokrotnie odczytywać plik. Zmiany zawartości pliku spowodują nieprawidłowe wyniki. W związku z tym pula SQL kończy się niepowodzeniem zapytania, jeśli wykryje, że czas modyfikacji dowolnego pliku zostanie zmieniony podczas wykonywania zapytania. W niektórych scenariuszach możesz utworzyć tabelę w plikach, które są stale dołączane. Aby uniknąć błędów zapytań z powodu stale dołączanych plików, można określić, że tabela zewnętrzna powinna ignorować potencjalnie niespójne operacje odczytu przy użyciu TABLE_OPTIONS ustawienia .

CREATE EXTERNAL TABLE populationExternalTable
(
    [country_code] VARCHAR (5) COLLATE Latin1_General_BIN2,
    [country_name] VARCHAR (100) COLLATE Latin1_General_BIN2,
    [year] smallint,
    [population] bigint
)
WITH (
    LOCATION = 'csv/population/population.csv',
    DATA_SOURCE = sqlondemanddemo,
    FILE_FORMAT = QuotedCSVWithHeaderFormat,
    TABLE_OPTIONS = N'{"READ_OPTIONS":["ALLOW_INCONSISTENT_READS"]}'
);

Opcja ALLOW_INCONSISTENT_READS odczytu wyłącza sprawdzanie czasu modyfikacji pliku w cyklu życia zapytania i odczytuje informacje dostępne w plikach, do których odwołuje się tabela zewnętrzna. W dołączanych plikach istniejąca zawartość nie jest aktualizowana i dodawane są tylko nowe wiersze. W związku z tym prawdopodobieństwo nieprawidłowych wyników jest zminimalizowane w porównaniu z plikami z możliwością aktualizacji. Ta opcja może umożliwić odczytywanie często dołączanych plików bez radzenia sobie z błędami.

Ta opcja jest dostępna tylko w tabelach zewnętrznych utworzonych w formacie pliku CSV.

Uwaga

Jak wskazuje nazwa opcji, twórca tabeli akceptuje ryzyko, że wyniki mogą nie być spójne. In appendable plikach możesz uzyskać nieprawidłowe wyniki, jeśli wymusisz wielokrotne odczytanie plików bazowych poprzez samodzielne dołączenie tabeli. W większości zapytań "klasycznych" tabela zewnętrzna po prostu zignoruje niektóre wiersze, które są dołączane podczas uruchamiania zapytania.

Zewnętrzna tabela Delta Lake

Tabele zewnętrzne można tworzyć na podstawie folderu usługi Delta Lake. Jedyną różnicą między tabelami zewnętrznymi utworzonymi w jednym pliku lub zestawie plików oraz tabelami zewnętrznymi utworzonymi w formacie usługi Delta Lake jest to, że w tabeli zewnętrznej usługi Delta Lake należy odwołać się do folderu zawierającego strukturę usługi Delta Lake.

Przykładem definicji tabeli utworzonej w folderze usługi Delta Lake jest:

CREATE EXTERNAL TABLE Covid (
     date_rep date,
     cases int,
     geo_id varchar(6)
) WITH (
        LOCATION = 'covid', --> the root folder containing the Delta Lake files
        data_source = DeltaLakeStorage,
        FILE_FORMAT = DeltaLakeFormat
);

Tabele zewnętrzne nie mogą być tworzone w folderze podzielonym na partycje. Przejrzyj inne znane problemy na stronie pomocy bezserwerowej puli SQL Synapse.

Tabele delta w folderach partycjonowanych

Zewnętrzne tabele w pulach serwera SQL bezserwerowego nie obsługują podziału na partycje w formacie Delta Lake. Używaj partycyjnych widoków Delta zamiast tabel, jeśli masz zestawy danych Delta Lake podzielonych na partycje.

Ważne

Nie twórz tabel zewnętrznych w partycjonowanych folderach usługi Delta Lake, nawet jeśli zobaczysz, że mogą one działać w niektórych przypadkach. Korzystanie z nieobsługiwanych funkcji, takich jak tabele zewnętrzne w partycjonowanych folderach delta, może powodować problemy lub niestabilność puli bezserwerowej. Pomoc techniczna Azure nie będzie w stanie rozwiązać żadnego problemu, jeśli używa tabel w katalogach partycjonowanych. Przed kontynuowaniem rozwiązywania problemów, poprosi się o przejście do Delta z podziałem na partycje i przepisanie kodu w celu użycia tylko obsługiwanej funkcji.

Korzystanie z tabeli zewnętrznej

Tabele zewnętrzne można używać w zapytaniach tak samo jak w zapytaniach programu SQL Server.

Poniższe zapytanie demonstruje to za pomocą tabeli zewnętrznej populacji utworzonej w poprzedniej sekcji. Zwraca nazwy krajów/regionów z ich populacją w 2019 r. w kolejności malejącej.

Uwaga

Zmień pierwszy wiersz zapytania, czyli [mydbname], więc używasz utworzonej bazy danych.

USE [mydbname];
GO

SELECT
    country_name, population
FROM populationExternalTable
WHERE
    [year] = 2019
ORDER BY
    [population] DESC;

Wydajność tego zapytania może się różnić w zależności od regionu. Obszar roboczy może nie zostać umieszczony w tym samym regionie co konta usługi Azure Storage używane w tych przykładach. W przypadku obciążeń produkcyjnych umieść obszar roboczy usługi Synapse i usługę Azure Storage w tym samym regionie.

Następny krok

Zapisz wyniki zapytań w pamięci