Tworzenie obiektów zewnętrznej bazy danych

  • 6 min

Funkcja OPENROWSET w zapytaniach SQL uruchamianych w domyślnej bazie danych master wbudowanej bezserwerowej puli SQL umożliwia eksplorowanie danych w usłudze Data Lake. Czasami jednak warto utworzyć niestandardową bazę danych zawierającą niektóre obiekty, które ułatwiają pracę z danymi zewnętrznymi w usłudze Data Lake, które często są wykonywane względem nich zapytań.

Tworzenie bazy danych

Bazę danych można utworzyć w bezserwerowej puli SQL tak samo jak w wystąpieniu programu SQL Server. Możesz użyć interfejsu graficznego w programie Synapse Studio lub instrukcji CREATE DATABASE. Jedną z kwestii jest ustawienie sortowania bazy danych tak, aby obsługiwała konwersję danych tekstowych w plikach na odpowiednie typy danych Języka Transact-SQL.

Poniższy przykładowy kod tworzy bazę danych o nazwie salesDB z sortowaniem, które ułatwia importowanie zakodowanych danych tekstowych UTF-8 do kolumn VARCHAR.

CREATE DATABASE SalesDB
    COLLATE Latin1_General_100_BIN2_UTF8

Tworzenie zewnętrznego źródła danych

Możesz użyć funkcji OPENROWSET ze ścieżką BULK do wykonywania zapytań dotyczących danych plików z własnej bazy danych, tak jak w bazie danych master , ale jeśli planujesz często wykonywać zapytania o dane w tej samej lokalizacji, bardziej wydajne jest zdefiniowanie zewnętrznego źródła danych odwołującego się do tej lokalizacji. Na przykład poniższy kod tworzy źródło danych o nazwie files dla hipotetycznego https://mydatalake.blob.core.windows.net/data/files/ folderu:

CREATE EXTERNAL DATA SOURCE files
WITH (
    LOCATION = 'https://mydatalake.blob.core.windows.net/data/files/'
)

Jedną z zalet zewnętrznego źródła danych jest uproszczenie zapytania OPENROWSET w celu użycia kombinacji źródła danych i ścieżki względnej do folderów lub plików, które chcesz wykonać:

SELECT *
FROM
    OPENROWSET(
        BULK 'orders/*.csv',
        DATA_SOURCE = 'files',
        FORMAT = 'csv',
        PARSER_VERSION = '2.0'
    ) AS orders

W tym przykładzie parametr BULK służy do określania ścieżki względnej dla wszystkich plików .csv w folderze orders , który jest podfolderem folderu plików , do którego odwołuje się źródło danych.

Kolejną zaletą korzystania ze źródła danych jest możliwość przypisania poświadczeń dla źródła danych do użycia podczas uzyskiwania dostępu do bazowego magazynu, co umożliwia zapewnienie dostępu do danych za pośrednictwem programu SQL bez zezwolenia użytkownikom na dostęp do danych bezpośrednio na koncie magazynu. Na przykład poniższy kod tworzy poświadczenie, które używa sygnatury dostępu współdzielonego (SAS) do uwierzytelniania względem bazowego konta usługi Azure Storage hostujące usługę Data Lake.

CREATE DATABASE SCOPED CREDENTIAL sqlcred
WITH
    IDENTITY='SHARED ACCESS SIGNATURE',  
    SECRET = 'sv=xxx...';
GO

CREATE EXTERNAL DATA SOURCE secureFiles
WITH (
    LOCATION = 'https://mydatalake.blob.core.windows.net/data/secureFiles/'
    CREDENTIAL = sqlcred
);
GO

Napiwek

Oprócz uwierzytelniania za pomocą sygnatury dostępu współdzielonego można zdefiniować poświadczenia korzystające z tożsamości zarządzanej (tożsamość firmy Microsoft entra używana przez obszar roboczy usługi Azure Synapse), określony podmiot zabezpieczeń firmy Microsoft lub uwierzytelnianie przekazywane na podstawie tożsamości użytkownika uruchamiającego zapytanie (który jest domyślnym typem uwierzytelniania). Aby dowiedzieć się więcej na temat używania poświadczeń w bezserwerowej puli SQL, zobacz artykuł Kontrola dostępu do konta magazynu dla bezserwerowej puli SQL w usłudze Azure Synapse Analytics w dokumentacji usługi Azure Synapse Analytics .

Tworzenie formatu pliku zewnętrznego

Chociaż zewnętrzne źródło danych upraszcza kod wymagany do uzyskiwania dostępu do plików za pomocą funkcji OPENROWSET, nadal trzeba podać szczegóły formatu dla pliku, do którego jest uzyskiwany dostęp; które mogą zawierać wiele ustawień dla rozdzielanych plików tekstowych. Te ustawienia można hermetyzować w formacie pliku zewnętrznego w następujący sposób:

CREATE EXTERNAL FILE FORMAT CsvFormat
    WITH (
        FORMAT_TYPE = DELIMITEDTEXT,
        FORMAT_OPTIONS(
            FIELD_TERMINATOR = ',',
            STRING_DELIMITER = '"'
        )
    );
GO

Po utworzeniu formatów plików dla określonych plików danych, z których musisz pracować, możesz użyć formatu pliku, aby utworzyć tabele zewnętrzne zgodnie z opisem w dalszej części.

Tworzenie tabeli zewnętrznej

Jeśli musisz wykonać wiele analiz lub raportowania z plików w usłudze Data Lake, użycie funkcji OPENROWSET może spowodować utworzenie złożonego kodu zawierającego źródła danych i ścieżki plików. Aby uprościć dostęp do danych, można hermetyzować pliki w tabeli zewnętrznej; którzy użytkownicy i aplikacje raportowania mogą wykonywać zapytania przy użyciu standardowej instrukcji SQL SELECT, podobnie jak w przypadku każdej innej tabeli bazy danych. Aby utworzyć tabelę zewnętrzną, użyj instrukcji CREATE EXTERNAL TABLE, określając schemat kolumny jako tabelę standardową, a także klauzulę WITH określającą zewnętrzne źródło danych, ścieżkę względną i format pliku zewnętrznego dla danych.

CREATE EXTERNAL TABLE dbo.products
(
    product_id INT,
    product_name VARCHAR(20),
    list_price DECIMAL(5,2)
)
WITH
(
    DATA_SOURCE = files,
    LOCATION = 'products/*.csv',
    FILE_FORMAT = CsvFormat
);
GO

-- query the table
SELECT * FROM dbo.products;

Tworząc bazę danych zawierającą obiekty zewnętrzne omówione w tej lekcji, można udostępnić warstwę relacyjnej bazy danych na plikach w usłudze Data Lake, co ułatwia wielu analitykom danych i narzędziom raportowania uzyskiwanie dostępu do danych przy użyciu standardowych semantyki zapytań SQL.