Externe databaseobjecten maken

  • 6 minuten

U kunt de functie OPENROWSET gebruiken in SQL-query's die worden uitgevoerd in de standaardhoofddatabase van de ingebouwde serverloze SQL-pool om gegevens in de data lake te verkennen. Soms wilt u echter een aangepaste database maken die enkele objecten bevat waarmee u gemakkelijker kunt werken met externe gegevens in de Data Lake die u regelmatig moet opvragen.

Een database maken

U kunt een database maken in een serverloze SQL-pool, net zoals in een SQL Server-exemplaar. U kunt de grafische interface in Synapse Studio of een CREATE DATABASE-instructie gebruiken. Een overweging is het instellen van de sortering van uw database, zodat deze ondersteuning biedt voor de conversie van tekstgegevens in bestanden naar de juiste Transact-SQL-gegevenstypen.

Met de volgende voorbeeldcode wordt een database met de naam salesDB gemaakt met een sortering waarmee uTF-8 gecodeerde tekstgegevens gemakkelijker kunt importeren in VARCHAR-kolommen.

CREATE DATABASE SalesDB
    COLLATE Latin1_General_100_BIN2_UTF8

Een externe gegevensbron maken

U kunt de functie OPENROWSET gebruiken met een BULK-pad om bestandsgegevens uit uw eigen database op te vragen, net zoals in de hoofddatabase , maar als u van plan bent om regelmatig query's uit te voeren op gegevens op dezelfde locatie, is het efficiënter om een externe gegevensbron te definiëren die verwijst naar die locatie. Met de volgende code maakt u bijvoorbeeld een gegevensbron met de naam bestanden voor de hypothetische https://mydatalake.blob.core.windows.net/data/files/ map:

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

Een voordeel van een externe gegevensbron is dat u een OPENROWSET-query kunt vereenvoudigen voor het gebruik van de combinatie van de gegevensbron en het relatieve pad naar de mappen of bestanden waarop u een query wilt uitvoeren:

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

In dit voorbeeld wordt de parameter BULK gebruikt om het relatieve pad op te geven voor alle .csv bestanden in de map Orders . Dit is een submap van de map bestanden waarnaar wordt verwezen door de gegevensbron.

Een ander voordeel van het gebruik van een gegevensbron is dat u een referentie kunt toewijzen voor de gegevensbron die moet worden gebruikt bij het openen van de onderliggende opslag, zodat u via SQL toegang kunt verlenen tot gegevens zonder dat gebruikers rechtstreeks toegang hebben tot de gegevens in het opslagaccount. Met de volgende code wordt bijvoorbeeld een referentie gemaakt die gebruikmaakt van een SHARED Access Signature (SAS) om te verifiëren bij het onderliggende Azure-opslagaccount dat als host fungeert voor de 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

Tip

Naast SAS-verificatie kunt u referenties definiëren die gebruikmaken van een beheerde identiteit (de Microsoft Entra-identiteit die wordt gebruikt door uw Azure Synapse-werkruimte), een specifieke Microsoft Entra-principal of passthrough-verificatie op basis van de identiteit van de gebruiker die de query uitvoert (dit is het standaardtype verificatie). Voor meer informatie over het gebruik van referenties in een serverloze SQL-pool raadpleegt u het artikel Toegang tot het opslagaccount beheren voor een serverloze SQL-pool in Azure Synapse Analytics in de documentatie van Azure Synapse Analytics.

Een externe bestandsindeling maken

Hoewel een externe gegevensbron de code vereenvoudigt die nodig is voor toegang tot bestanden met de functie OPENROWSET, moet u nog steeds indelingsgegevens opgeven voor het bestand dat wordt geopend; dit kan meerdere instellingen voor tekstbestanden met scheidingstekens bevatten. U kunt deze instellingen in een externe bestandsindeling inkapselen, zoals deze:

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

Nadat u bestandsindelingen hebt gemaakt voor de specifieke gegevensbestanden waarmee u moet werken, kunt u de bestandsindeling gebruiken om externe tabellen te maken, zoals hierna wordt besproken.

Een externe tabel maken

Wanneer u veel analyses of rapportages wilt uitvoeren van bestanden in de data lake, kan het gebruik van de functie OPENROWSET leiden tot complexe code die gegevensbronnen en bestandspaden bevat. Om de toegang tot de gegevens te vereenvoudigen, kunt u de bestanden in een externe tabel inkapselen; welke gebruikers en rapportagetoepassingen query's kunnen uitvoeren met behulp van een standaard SQL SELECT-instructie, net als elke andere databasetabel. Als u een externe tabel wilt maken, gebruikt u de instructie CREATE EXTERNAL TABLE, waarbij u het kolomschema opgeeft als voor een standaardtabel, en inclusief een WITH-component die de externe gegevensbron, het relatieve pad en de externe bestandsindeling voor uw gegevens specificeert.

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;

Door een database te maken die de externe objecten bevat die in deze eenheid worden besproken, kunt u een relationele databaselaag bieden over bestanden in een data lake, waardoor het voor veel gegevensanalisten en rapportagehulpprogramma's gemakkelijker is om toegang te krijgen tot de gegevens met behulp van standaard semantiek van SQL-query's.