Vytváření externích databázových objektů

  • 6 min

Funkci OPENROWSET můžete použít v dotazech SQL, které běží ve výchozí hlavní databázi integrovaného bezserverového fondu SQL k prozkoumání dat v datovém jezeře. Někdy ale můžete chtít vytvořit vlastní databázi, která obsahuje některé objekty, které usnadňují práci s externími daty v datovém jezeře, kterou potřebujete často dotazovat.

Vytvoření databáze

Databázi můžete vytvořit v bezserverovém fondu SQL stejně jako v instanci SQL Serveru. Grafické rozhraní můžete použít v nástroji Synapse Studio nebo příkaz CREATE DATABASE. Jednou z důležitých aspektů je nastavení kolace databáze tak, aby podporovala převod textových dat v souborech na příslušné datové typy Transact-SQL.

Následující příklad kódu vytvoří databázi s názvem salesDB s kolací, která usnadňuje import textových dat kódování UTF-8 do sloupců VARCHAR.

CREATE DATABASE SalesDB
    COLLATE Latin1_General_100_BIN2_UTF8

Vytvoření externího zdroje dat

Funkci OPENROWSET s hromadnou cestou můžete použít k dotazování dat souboru z vlastní databáze, stejně jako v hlavní databázi. Pokud ale plánujete dotazovat data ve stejném umístění často, je efektivnější definovat externí zdroj dat, který odkazuje na dané umístění. Například následující kód vytvoří zdroj dat pojmenovaný soubory pro hypotetickou https://mydatalake.blob.core.windows.net/data/files/ složku:

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

Jednou z výhod externího zdroje dat je, že můžete zjednodušit dotaz OPENROWSET tak, aby používal kombinaci zdroje dat a relativní cestu ke složkám nebo souborům, které chcete dotazovat:

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

V tomto příkladu se parametr BULK používá k určení relativní cesty pro všechny soubory .csv ve složce orders , což je podsložka složky souborů odkazované zdrojem dat.

Další výhodou použití zdroje dat je, že pro zdroj dat můžete přiřadit přihlašovací údaje, které se mají použít při přístupu k podkladovému úložišti, což vám umožní poskytnout přístup k datům prostřednictvím SQL, aniž byste uživatelům umožnili přístup k datům přímo v účtu úložiště. Následující kód například vytvoří přihlašovací údaje, které používají sdílený přístupový podpis (SAS) k ověření vůči podkladovému účtu úložiště Azure, který je hostitelem datového jezera.

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

Tip

Kromě ověřování SAS můžete definovat přihlašovací údaje, které používají spravovanou identitu (identitu Microsoft Entra používanou vaším pracovním prostorem Azure Synapse), konkrétní instanční objekt Microsoft Entra nebo předávací ověřování na základě identity uživatele, který spouští dotaz (což je výchozí typ ověřování). Další informace o používání přihlašovacích údajů v bezserverovém fondu SQL najdete v článku Řízení přístupu k účtu úložiště pro bezserverový fond SQL ve službě Azure Synapse Analytics v dokumentaci k Azure Synapse Analytics.

Vytvoření formátu externího souboru

I když externí zdroj dat zjednodušuje kód potřebný pro přístup k souborům pomocí funkce OPENROWSET, stále potřebujete zadat podrobnosti formátu pro přístup k souboru; které mohou obsahovat více nastavení pro textové soubory s oddělovači. Tato nastavení můžete zapouzdřovat ve formátu externího souboru, například takto:

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

Po vytvoření formátů souborů pro konkrétní datové soubory, se kterými potřebujete pracovat, můžete pomocí formátu souboru vytvořit externí tabulky, jak je popsáno dále.

Vytvoření externí tabulky

Pokud potřebujete provádět velké množství analýz nebo generování sestav ze souborů v datovém jezeře, může použití funkce OPENROWSET vést ke komplexnímu kódu, který zahrnuje zdroje dat a cesty k souborům. Chcete-li zjednodušit přístup k datům, můžete zapouzdření souborů v externí tabulce; kteří uživatelé a aplikace pro vytváření sestav mohou dotazovat pomocí standardního příkazu SQL SELECT stejně jako jakékoli jiné databázové tabulky. Pokud chcete vytvořit externí tabulku, použijte příkaz CREATE EXTERNAL TABLE, zadejte schéma sloupců jako pro standardní tabulku a včetně klauzule WITH určující externí zdroj dat, relativní cestu a formát externího souboru pro vaše data.

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;

Vytvořením databáze, která obsahuje externí objekty probírané v této lekci, můžete poskytnout vrstvu relační databáze nad soubory v datovém jezeře, což usnadňuje mnoha datovým analytikům a nástrojům pro vytváření sestav přístup k datům pomocí standardní sémantiky dotazů SQL.