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

  • Artykuł

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 magazynu:

  • Magazyn publiczny, w którym użytkownicy uzyskują dostęp do plików magazynu publicznego.
  • Chroniony magazyn, w którym użytkownicy uzyskują dostęp do plików magazynu przy użyciu poświadczeń sygnatury dostępu współdzielonego, tożsamości Firmy Microsoft lub tożsamości zarządzanej obszaru roboczego usługi 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) Bezserwerowa pula SQL Dedykowana pula SQL
Parquet Tak (ogólna dostępność) Tak (publiczna wersja zapoznawcza)
CSV Tak Nie (alternatywnie użyj tabel zewnętrznych usługi Hadoop)
delta Tak Nie.
platforma Spark 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. Przed utworzeniem poświadczeń o zakresie bazy danych 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:

  • POŚWIADCZENIA sqlondemand O ZAKRESIE BAZY DANYCH, które umożliwiają dostęp do konta usługi Azure Storage chronionego przez https://sqlondemandstorage.blob.core.windows.net sygnaturę dostępu współdzielonego.

    CREATE DATABASE SCOPED CREDENTIAL [sqlondemand]
WITH IDENTITY='SHARED ACCESS SIGNATURE',  
SECRET = 'sv=2018-03-28&ss=bf&srt=sco&sp=rl&st=2019-10-14T12%3A10%3A25Z&se=2061-12-31T12%3A10%3A00Z&sig=KlSU2ullCscyTS0An0nozEpo4tO5JAgGBvw%2FJX2lguw%3D'

  • ZEWNĘTRZNE ŹRÓDŁO sqlondemanddemo DANYCH, które odwołuje się do demonstracyjnego konta magazynu chronionego za pomocą klucza SYGNATURy dostępu współdzielonego i zewnętrznego źródła nyctlc danych, które odwołuje 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://sqlondemandstorage.blob.core.windows.net',
    CREDENTIAL = sqlondemand
);
GO
CREATE EXTERNAL DATA SOURCE nyctlc
WITH ( LOCATION = 'https://azureopendatastorage.blob.core.windows.net/nyctlc/')
GO
CREATE EXTERNAL DATA SOURCE DeltaLakeStorage
WITH ( location = 'https://sqlondemandstorage.blob.core.windows.net/delta-lake/' );

  • Formaty QuotedCSVWithHeaderFormat plików i ParquetFormat opisujące 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 zostaną wykonane w przykładowej bazie danych i będą używane te obiekty.

Tabela zewnętrzna w pliku

Możesz tworzyć tabele zewnętrzne, które uzyskują dostęp do danych na koncie usługi Azure Storage, które umożliwiają dostęp do użytkowników przy użyciu niektórych tożsamości firmy Microsoft lub klucza 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 demonstracyjnego konta usługi Azure Storage usługi SynapseSQL, do którego odwołuje się źródło danych i jest chroniony przy użyciu sqlondemanddemo poświadczeń o określonym zakresie bazy danych o nazwie sqlondemand.

Poświadczenia o zakresie źródła danych i bazy danych są tworzone w skry skryptie konfiguracji.

Uwaga

Zmień pierwszy wiersz w zapytaniu, tj. [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 musi spełniać pliki, aby odwoływać się do tabeli zewnętrznej. Wzorzec 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 użyć eliminacji partycji. Jeśli chcesz uzyskać lepszą wydajność, pomijając pliki, które nie spełniają określonego kryterium (np. 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 spowoduje wyłączenie sprawdzania czasu modyfikacji pliku w cyklu życia zapytania i odczytanie dowolnego elementu dostępnego 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 obsługi błędów.

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. W plikach dołączanych wyniki mogą być nieprawidłowe, jeśli wymusisz wielokrotne odczytywanie źródłowych plików przez samołą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.

Tabela zewnętrzna usługi 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.

ECDC COVID-19 Delta Lake folder

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

Nie można utworzyć tabel zewnętrznych w folderze podzielonym na partycje. Przejrzyj inne znane problemy na stronie samodzielnej pomocy bezserwerowej puli SQL usługi Synapse.

Tabele różnicowe w folderach podzielonych na partycje

Tabele zewnętrzne w bezserwerowych pulach SQL nie obsługują partycjonowania w formacie usługi Delta Lake. Użyj widoków partycjonowanych różnic zamiast tabel, jeśli zestawy danych usługi Delta Lake zostały podzielone 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 różnicowych, może powodować problemy lub niestabilność puli bezserwerowej. pomoc techniczna platformy Azure nie będzie można rozwiązać żadnego problemu, jeśli używa tabel w folderach podzielonych na partycje. Przed kontynuowaniem rozwiązywania problemów zostanie wyświetlony monit o przejście do widoków podzielonych na partycje różnicowe 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 przy użyciu 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 w zapytaniu, tj. [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ępne kroki

Aby uzyskać informacje na temat przechowywania wyników zapytania do magazynu, zapoznaj się z artykułem Przechowywanie wyników zapytań w magazynie .