Query's uitvoeren op opslagbestanden met behulp van een serverloze SQL-pool

2025-02-04

Met een serverloze SQL-pool kunt u query's uitvoeren op gegevens in uw data lake. Het biedt een Transact-SQL-queryoppervlak (T-SQL) dat geschikt is voor semi-gestructureerde en ongestructureerde gegevensquery's. Voor het uitvoeren van query's worden de volgende T-SQL-aspecten ondersteund:

Volledig SELECT-oppervlakgebied , inclusief de meeste SQL-functies en -operators.
CREATE EXTERNAL TABLE AS SELECT (CETAS) maakt een externe tabel en exporteert vervolgens, parallel, de resultaten van een T-SQL SELECT-instructie naar Azure Storage.

Lees het overzicht van de serverloze SQL-pool of de volgende artikelen voor meer informatie over wat momenteel wel of niet wordt ondersteund:

Ontwikkel opslagtoegang waar u externe tabellen en de functie OPENROWSET kunt gebruiken om gegevens uit de opslag te lezen.
Toegang tot opslag beheren, waar u kunt leren hoe u Synapse SQL toegang kunt geven tot opslag met behulp van SAS-verificatie of de beheerde identiteit van de werkruimte.

Overzicht

Ter ondersteuning van een soepele ervaring voor het uitvoeren van in-place query's op gegevens die zich in Azure Storage-bestanden bevinden, maakt serverloze SQL-pool gebruik van de functie OPENROWSET met meer mogelijkheden:

PARQUET-bestanden opvragen
Doorzoek CSV-bestanden en tekst met scheidingstekens (veldeindteken, rijeindteken, escape-teken)
Delta Lake-formaat opvragen
Een gekozen subset van kolommen lezen
Schema-afleiding
Meerdere bestanden of mappen doorzoeken
Bestandsnaamfunctie
Bestandspadfunctie
Werken met complexe typen en geneste of herhaalde gegevensstructuren

Query's uitvoeren op PARQUET-bestanden

Als u query's wilt uitvoeren op Parquet-brongegevens, gebruikt u FORMAT = 'PARQUET':

SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder/data.parquet', FORMAT = 'PARQUET') 
WITH (C1 int, C2 varchar(20), C3 varchar(max)) as rows

Zie Parquet-bestanden opvragen voor gebruiksvoorbeelden.

Query's uitvoeren op CSV-bestanden

Als u een query wilt uitvoeren op CSV-brongegevens, gebruikt u FORMAT = 'CSV'. U kunt het schema van het CSV-bestand opgeven als onderdeel van de OPENROWSET functie wanneer u een query uitvoert op CSV-bestanden:

SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder/data.csv', FORMAT = 'CSV', PARSER_VERSION='2.0') 
WITH (C1 int, C2 varchar(20), C3 varchar(max)) as rows

Er zijn enkele extra opties die kunnen worden gebruikt om parseringsregels aan te passen aan aangepaste CSV-indeling:

ESCAPE_CHAR = 'char' Hiermee specificeert u het teken in het bestand dat wordt gebruikt om zichzelf te escapen en alle scheidingstekens in het bestand. Als het escape-teken wordt gevolgd door een andere waarde dan het teken zelf, of een van de scheidingstekens, wordt het escape-teken genegeerd bij het lezen van de waarde. De ESCAPE_CHAR parameter wordt toegepast, ongeacht of de FIELDQUOTE parameter wel of niet is ingeschakeld. Het wordt niet gebruikt om het aanhalingsteken te ontduiken. Het aanhalingsteken moet worden voorafgegaan door een ander aanhalingsteken. Een aanhalingsteken kan alleen in een kolomwaarde verschijnen als de waarde tussen aanhalingstekens is geplaatst.
FIELDTERMINATOR ='field_terminator' Specificeert het veldeindteken dat moet worden gebruikt. Het standaardveldeindteken is een komma (,).
ROWTERMINATOR ='row_terminator' Specifieert het rijeindteken dat moet worden gebruikt. Het standaardrijeindteken is een nieuw regelteken (\r\n).

Delta Lake-indeling opvragen

Als u query's wilt uitvoeren op delta lake-brongegevens, gebruikt FORMAT = 'DELTA' en verwijst u naar de hoofdmap met uw Delta Lake-bestanden.

SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder', FORMAT = 'DELTA') 
WITH (C1 int, C2 varchar(20), C3 varchar(max)) as rows

De hoofdmap moet een submap bevatten met de naam _delta_log. Zie Query Delta Lake-bestanden (v1) voor gebruiksvoorbeelden.

Bestandsschema

Met de SQL-taal in Synapse SQL kunt u het schema van het bestand definiëren als onderdeel van de OPENROWSET functie en alle of subset van kolommen lezen, of wordt geprobeerd om automatisch kolomtypen uit het bestand te bepalen met behulp van schemadeductie.

Een gekozen subset van kolommen lezen

Als u kolommen wilt opgeven die u wilt lezen, kunt u een optionele WITH component opgeven in uw OPENROWSET instructie.

Als er CSV-gegevensbestanden zijn, geeft u kolomnamen en de bijbehorende gegevenstypen op om alle kolommen te lezen. Als u een subset van kolommen wilt opvragen, gebruikt u ordinale nummers om de kolommen uit de oorspronkelijke gegevensbestanden te selecteren. Kolommen zijn gebonden aan de ordinale aanduiding.
Als het Parquet-gegevensbestanden betreft, geeft u kolomnamen op die overeenkomen met de kolomnamen in de oorspronkelijke gegevensbestanden. Kolommen zijn gebonden aan de naam.

SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder/data.parquet', FORMAT = 'PARQUET') 
WITH (
      C1 int, 
      C2 varchar(20),
      C3 varchar(max)
) as rows;

Voor elke kolom moet u de naam van de kolom opgeven en de WITH-component typen. Zie CSV-bestanden lezen zonder alle kolommen op te geven voor voorbeelden.

Schema-afleiding

Door de WITH component uit de OPENROWSET instructie weg te laten, kunt u de service instrueren om het schema automatisch te detecteren (afleiden) uit onderliggende bestanden.

SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder/data.parquet', FORMAT = 'PARQUET')

Zorg ervoor dat de juiste gededuceerde datatypes worden gebruikt voor de beste prestatie.

Meerdere bestanden of mappen doorzoeken

Geef een pad naar een map of een patroon (met jokertekens) op voor een verzameling bestanden of mappen om een T-SQL-query uit te voeren over deze verzameling en ze te behandelen als een enkele entiteit of rijenset.

De volgende regels zijn van toepassing:

Patronen kunnen voorkomen in een deel van een mappad of in een bestandsnaam.
Verschillende patronen kunnen in dezelfde mapstap of bestandsnaam verschijnen.
Als er meerdere jokertekens zijn, worden bestanden binnen alle overeenkomende paden opgenomen in de resulterende bestandsset.

SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/myroot/*/mysubfolder/*.parquet', FORMAT = 'PARQUET' ) as rows

Zie Querymappen en meerdere bestanden voor gebruiksvoorbeelden.

Bestandsmetagegevensfuncties

Bestandsnaamfunctie

Deze functie retourneert de bestandsnaam waaruit de rij afkomstig is.

Lees de sectie Bestandsnaam in het artikel Query's uitvoeren op specifieke bestandenom een query uit te voeren op specifieke bestanden.

Het retourgegevenstype is nvarchar(1024). Cast het resultaat van de bestandsnaamfunctie altijd naar het juiste gegevenstype voor optimale prestaties. Als u een tekengegevenstype gebruikt, zorg er dan voor dat de juiste lengte wordt gebruikt.

Bestandspadfunctie

Deze functie retourneert een volledig pad of een deel van een pad:

Wanneer de functie wordt aangeroepen zonder parameter, wordt het volledige bestandspad geretourneerd waaruit een rij afkomstig is.
Wanneer de functie wordt aangeroepen met parameter, wordt het deel van het pad geretourneerd dat overeenkomt met het jokerteken op de positie die is gespecificeerd in de parameter. Parameterwaarde 1 zou bijvoorbeeld het deel van het pad retourneren dat overeenkomt met het eerste jokerteken.

Voor meer informatie leest u de sectie Bestandspad van het artikel Query uitvoeren op specifieke bestanden.

Retourgegevenstype is nvarchar(1024). Voor optimale prestaties cast u altijd het resultaat van de bestandspadfunctie naar het juiste gegevenstype. Als u een tekengegevenstype gebruikt, zorg er dan voor dat de juiste lengte wordt gebruikt.

Werken met complexe typen en geneste of herhaalde gegevensstructuren

Voor een soepele ervaring met gegevens die zijn opgeslagen in geneste of herhaalde gegevenstypen, zoals in Parquet-bestanden , heeft de serverloze SQL-pool de volgende extensies toegevoegd.

Geneste of herhaalde gegevens projecteren

Als u gegevens wilt projecteren, voert u een SELECT instructie uit over het Parquet-bestand dat kolommen met geneste gegevenstypen bevat. Bij uitvoer worden geneste waarden geserialiseerd in JSON en geretourneerd als een varchar(8000) SQL-gegevenstype.

    SELECT * FROM
    OPENROWSET
    (   BULK 'unstructured_data_path' ,
        FORMAT = 'PARQUET' )
    [AS alias]

Zie het gedeelte Geneste of herhaalde gegevens in het artikel Query geneste typen in Parquet voor meer informatie.

Elementen benaderen vanuit geneste kolommen

Gebruik puntnotatie om veldnamen samen te voegen tot het pad als u toegang wilt krijgen tot geneste elementen vanuit een geneste kolom, zoals Struct. Geef het pad op zoals column_name in de WITH component van de OPENROWSET functie.

Het voorbeeld van het syntaxisfragment ziet er als volgt uit:

    OPENROWSET
    (   BULK 'unstructured_data_path' ,
        FORMAT = 'PARQUET' )
    WITH ('column_name' 'column_type')
    [AS alias]
    'column_name' ::= '[field_name.] field_name'

De functie komt standaard OPENROWSET overeen met de naam en het pad van het bronveld met de kolomnamen in de WITH clausule. Elementen op verschillende nestniveaus binnen hetzelfde Parquet-bestand kunnen worden benaderd met behulp van de WITH clausule.

Retourwaarden

Functie retourneert een scalaire waarde, zoals int, decimalen varchar, van het opgegeven element en op het opgegeven pad, voor alle Parquet-typen die zich niet in de groep Genest type bevinden.
Als het pad verwijst naar een element van een genest type, retourneert de functie een JSON-fragment dat begint vanaf het bovenste element op het opgegeven pad. Het JSON-fragment is van het type varchar(8000).
Als de eigenschap niet kan worden gevonden op de opgegeven column_name, retourneert de functie een fout.
Als de eigenschap niet kan worden gevonden op de opgegeven column_path, retourneert de functie, afhankelijk van de padmodus, een fout in de strikte modus, of null in de laxmodus.

Zie de sectie Leeseigenschappen uit geneste objectkolommen in het artikel Met Query Parquet geneste typen voor queryvoorbeelden.

Elementen benaderen vanuit herhaalde kolommen

Als u toegang wilt krijgen tot elementen uit een herhaalde kolom, zoals een element van een matrix of kaart, gebruikt u de functie JSON_VALUE voor elk scalaire element dat u moet projecteren en opgeven:

Geneste of herhaalde kolom als eerste parameter
als tweede parameter een JSON-pad dat de elementen of de eigenschappen opgeeft die moeten worden benaderd

Als u toegang wilt krijgen tot niet-calaire elementen uit een herhaalde kolom, gebruikt u de functie JSON_QUERY voor elk niet-schalend element dat u moet projecteren en opgeeft:

Geneste of herhaalde kolom, als eerste parameter
als tweede parameter een JSON-pad dat de elementen of de eigenschappen opgeeft die moeten worden benaderd

Zie het volgende syntaxisfragment:

    SELECT
       JSON_VALUE (column_name, path_to_sub_element),
       JSON_QUERY (column_name [ , path_to_sub_element ])
    FROM
    OPENROWSET
    (   BULK 'unstructured_data_path' ,
        FORMAT = 'PARQUET' )
    [AS alias]

U kunt in het artikel Query Parquet geneste typen voorbeelden van query's vinden voor het benaderen van elementen uit herhaalde kolommen.

Zie de volgende artikelen voor meer informatie over het uitvoeren van query's op verschillende bestandstypes en het maken en gebruiken van weergaven:

Feedback

Is deze pagina nuttig?