Delen via

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

  • Artikel

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:

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

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:

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 geeft u het teken in het bestand dat wordt gebruikt om zichzelf te ontsnappen 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 ontsnappen. Het aanhalingsteken moet worden a”gesloten door een ander aanhalingsteken. Een aanhalingsteken kan alleen in een kolomwaarde worden weer gegeven als de waarde tussen aanhalingstekens staat.
  • FIELDTERMINATOR ='field_terminator' Hiermee geeft u het veldeindteken dat moet worden gebruikt. Het standaardveldeindteken is een komma (,).
  • ROWTERMINATOR ='row_terminator' Hiermee geeft u 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 deze kolommen wilt opvragen, gebruikt u rangtelwoorden om de kolommen uit de oorspronkelijke gegevensbestanden te kiezen op rangtelwoord. Kolommen zijn afhankelijk van de rangschikkelijkheid.
  • Als het Parquet-gegevensbestanden betreft, geeft u kolomnamen op die overeenkomen met de kolomnamen in de oorspronkelijke gegevensbestanden. Kolommen zijn afhankelijk van 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-deductie

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 of een map of een patroon (met jokertekens) over een verzameling bestanden of mappen op om een T-SQL-query uit te voeren op een verzameling bestanden in een map of een verzameling mappen 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 functie bestandsnaam altijd naar het juiste gegevenstype voor de beste prestatie. 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.

Lees de sectie Bestandspad van het artikel Query uitvoeren op specifieke bestanden voor meer informatie.

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 de sectie Geneste of herhaalde gegevens van het artikel Met Parquet geneste typen query's voor meer informatie.

Elementen benaderen vanuit geneste kolommen

Als u toegang wilt krijgen tot geneste elementen vanuit een geneste kolom, zoals Struct, gebruikt u punt notatie om veldnamen samen te voegen in het pad. 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 component. Elementen in verschillende nestniveaus binnen hetzelfde Parquet-bronbestand kunnen worden geopend met behulp van de WITH component.

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 in de opgegeven column_pathmodus, afhankelijk van de padmodus, retourneert de functie een fout wanneer deze zich in de strikte modus of null bevindt 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:

  • als eerste parameter Geneste of herhaalde kolom
  • 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:

  • als eerste parameter Geneste of herhaalde kolom
  • 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 voorbeelden van query's voor het benaderen van elementen van herhaalde kolommen vinden in het artikel Query uitvoeren op met Parquet geneste typen.

Gerelateerde inhoud

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