Skapa externa databasobjekt

Slutförd

Du kan använda funktionen OPENROWSET i SQL-frågor som körs i standarddatabasen för den inbyggda serverlösa SQL-poolen för att utforska data i datasjön. Ibland kanske du dock vill skapa en anpassad databas som innehåller vissa objekt som gör det enklare att arbeta med externa data i datasjön som du behöver fråga ofta.

Skapa en databas

Du kan skapa en databas i en serverlös SQL-pool precis som i en SQL Server-instans. Du kan använda det grafiska gränssnittet i Synapse Studio eller en CREATE DATABASE-instruktion. Ett övervägande är att ställa in sortering av databasen så att den stöder konvertering av textdata i filer till lämpliga Transact-SQL-datatyper.

I följande exempelkod skapas en databas med namnet salesDB med en sortering som gör det enklare att importera UTF-8-kodade textdata till VARCHAR-kolumner.

CREATE DATABASE SalesDB
    COLLATE Latin1_General_100_BIN2_UTF8

Skapa en extern datakälla

Du kan använda funktionen OPENROWSET med en BULK-sökväg för att fråga efter fildata från din egen databas, precis som du kan i huvuddatabasen, men om du planerar att köra frågor mot data på samma plats ofta är det mer effektivt att definiera en extern datakälla som refererar till den platsen. Följande kod skapar till exempel en datakälla med namnet filer för den hypotetiska https://mydatalake.blob.core.windows.net/data/files/ mappen:

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

En fördel med en extern datakälla är att du kan förenkla en OPENROWSET-fråga för att använda kombinationen av datakällan och den relativa sökvägen till de mappar eller filer som du vill fråga:

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

I det här exemplet används BULK-parametern för att ange den relativa sökvägen för alla .csv filer i mappen orders , som är en undermapp till den filmapp som datakällan refererar till.

En annan fördel med att använda en datakälla är att du kan tilldela en autentiseringsuppgift för datakällan som ska användas vid åtkomst till den underliggande lagringen, så att du kan ge åtkomst till data via SQL utan att tillåta användare att komma åt data direkt i lagringskontot. Följande kod skapar till exempel en autentiseringsuppgift som använder en signatur för delad åtkomst (SAS) för att autentisera mot det underliggande Azure-lagringskontot som är värd för datasjön.

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

Dricks

Förutom SAS-autentisering kan du definiera autentiseringsuppgifter som använder hanterad identitet (Microsoft Entra-identiteten som används av din Azure Synapse-arbetsyta), ett specifikt Microsoft Entra-huvudnamn eller genomströmningsautentisering baserat på identiteten för den användare som kör frågan (vilket är standardtypen för autentisering). Mer information om hur du använder autentiseringsuppgifter i en serverlös SQL-pool finns i artikeln Kontrollera åtkomst till lagringskonto för serverlös SQL-pool i Azure Synapse Analytics i Azure Synapse Analytics-dokumentationen.

Skapa ett externt filformat

Även om en extern datakälla förenklar den kod som behövs för att komma åt filer med funktionen OPENROWSET, måste du fortfarande ange formatinformation för den fil som har åtkomst. som kan innehålla flera inställningar för avgränsade textfiler. Du kan kapsla in de här inställningarna i ett externt filformat, så här:

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

När du har skapat filformat för de specifika datafiler som du behöver arbeta med kan du använda filformatet för att skapa externa tabeller, enligt beskrivningen nedan.

Skapa en extern tabell

När du behöver utföra en hel del analys eller rapportering från filer i datasjön kan användning av funktionen OPENROWSET resultera i komplex kod som innehåller datakällor och filsökvägar. För att förenkla åtkomsten till data kan du kapsla in filerna i en extern tabell. vilka användare och rapporteringsprogram som kan köra frågor med en SQL SELECT-standardsats precis som andra databastabeller. Om du vill skapa en extern tabell använder du instruktionen SKAPA EXTERN TABELL, anger kolumnschemat som för en standardtabell och inkluderar en WITH-sats som anger den externa datakällan, den relativa sökvägen och det externa filformatet för dina 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;

Genom att skapa en databas som innehåller de externa objekt som beskrivs i den här lektionen kan du tillhandahålla ett relationsdatabaslager över filer i en datasjö, vilket gör det enklare för många dataanalytiker och rapporteringsverktyg att komma åt data med hjälp av standard-SQL-frågesemantik.