Share via

Query's op opslagbestanden uitvoeren met een serverloze SQL-pool in Azure Synapse Analytics

  • Artikel

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

  • Volledige SELECT-surface area, met inbegrip van de meeste SQL-functies en -operatoren.
  • Met CREATE EXTERNAL TABLE AS SELECT (CETAS) wordt een externe tabel gemaakt en parallel de resultaten van een Transact-SQL SELECT-instructie geëxporteerd naar Azure Storage.

Lees het artikel Overzicht van serverloze SQL-pools voor meer informatie over wat momenteel niet wordt ondersteund, of lees de volgende artikelen:

Overzicht

Om een soepele ervaring te kunnen bieden voor het lokaal zoeken naar gegevens die zich bevinden in Azure Storage-bestanden, gebruikt een serverloze SQL-pool de functie OPENROWSET met aanvullende mogelijkheden:

Query's uitvoeren op PARQUET-bestanden

Gebruik FORMAT = PARQUET om query's uit te voeren op Parquet-brongegevens:

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

Lees het artikel Query uitvoeren op Parquet-bestanden voor gebruiksvoorbeelden.

Query's uitvoeren op CSV-bestanden

Gebruik FORMAT = 'CSV' om een query uit te voeren op CSV-brongegevens. U kunt het schema van het CSV-bestand opgeven als onderdeel van de OPENROWSET-functie bij het opvragen van 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 een aantal extra opties die kunnen worden gebruikt om het parseren van af te stemmen op een aangepaste CSV-indeling:

  • ESCAPE_CHAR = 'char' Het teken in het bestand dat wordt gebruikt om zichzelf te escapen evenals 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 parameter ESCAPE_CHAR wordt toegepast ongeacht of FIELDQUOTE al dan niet is ingeschakeld. De parameter wordt niet gebruikt om het aanhalingsteken te escapen. 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 standaardeindteken voor velden 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 u 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. Raadpleeg het artikel over de delta lake-indeling voor query's voor gebruiksvoorbeelden.

Bestandsschema

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

Een gekozen subset van kolommen lezen

Om aan te geven welke kolommen u wilt lezen, kunt u een optionele WITH-clausule opgeven binnen uw OPENROWSET-opdracht.

  • Als u wilt dat alle kolommen worden gelezen uit CSV-gegevensbestanden, geeft u de kolomnamen en de bijbehorende gegevenstypen op. Als u een subset van deze kolommen wilt opvragen, gebruikt u rangtelwoorden om de kolommen uit de oorspronkelijke gegevensbestanden te kiezen op rangtelwoord. Kolommen worden gebonden op aanduiding via rangnummer.
  • Als het Parquet-gegevensbestanden betreft, geeft u kolomnamen op die overeenkomen met de kolomnamen in de oorspronkelijke gegevensbestanden. Kolommen worden op naam gebonden.
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. Raadpleeg CSV-bestanden lezen zonder alle kolommen op te geven voor voorbeelden.

Schema-deductie

Door de WITH-clausule weg te laten in de OPENROWSET-opdracht, kunt u de service de opdracht geven automatisch het schema te detecteren (afleiden) van de 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 gebruikt, worden alle bestanden in overeenkomende paden inbegrepen in de resulterende bestandsverzameling.
SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/myroot/*/mysubfolder/*.parquet', FORMAT = 'PARQUET' ) as rows

Raadpleeg Query uitvoeren op mappen 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.

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

Werken met complexe typen en geneste of herhaalde gegevensstructuren

Aan de serverloze SQL-pool zijn onderstaande extensies toegevoegd om een soepele ervaring te kunnen bieden bij gegevens die zijn opgeslagen in geneste of herhaalde gegevenstypes, zoals in Parquet-bestanden.

Geneste of herhaalde gegevens projecteren

Voer een SELECT-opdracht uit op het Parquet-bestand dat kolommen of geneste gegevenstypes bevat om gegevens te projecteren. Geneste waarden worden met serienummer in JSON geplaatst en geretourneerd als een SQL-gegevenstype varchar(8000).

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

Raadpleeg de sectie Geneste of herhaalde gegevens projecteren van het artikel Query uitvoeren op met Parquet geneste typen voor meer informatie.

Elementen benaderen vanuit geneste kolommen

Gebruik 'puntnotatie' om veldnamen samen te voegen in het pad om geneste elementen te benaderen van een geneste kolom, zoals Struct. Geef het pad op als kolomnaam in de WITH-clausule van de functie OPENROWSET.

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 OPENROWSET zoekt standaard naar overeenkomsten van de bronveldnaam en het bronveldpad met de kolomnamen die zijn opgegeven in de WITH-clausule. Elementen binnen verschillende nestniveaus binnen hetzelfde Parquet-bronbestand kunnen worden benaderd via de WITH-clausule.

Retourwaarden

  • Voor alle Parquet-types die zich niet in de groep Genest type bevinden, retourneert deze functie een scalaire waarde, zoals integer, decimaal en varchar van het opgegeven element en op het opgegeven pad.
  • Als het pad wijst naar een element van het Geneste type, retourneert de functie een JSON-fragment dat begint met het hoogste element in het opgegeven pad. Het JSON-fragment is van het type varchar(8000).
  • Als de eigenschap niet kan worden gevonden in de opgegeven kolomnaam, retourneert de functie een fout.
  • Als de eigenschap niet kan worden gevonden in de opgegeven kolomnaam, retourneert de functie, afhankelijk van Padmodus, een fout in de strict-modus of null in de lax-modus.

Raadpleeg de sectie Elementen benaderen vanuit geneste kolommen in het artikel Query uitvoeren op met Parquet geneste typen voor voorbeelden van query's.

Elementen benaderen vanuit herhaalde kolommen

Gebruik de functie JSON_VALUE voor elk scalair element dat u wilt projecteren om elementen van een herhaalde kolom te benaderen, zoals een element van een Matrix of Map en geef het volgende op:

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

Gebruik de functie JSON_QUERY voor elk niet-scalair element dat u wilt projecteren om niet-scalaire elementen van een herhaalde kolom te benaderen en geef het volgende op:

  • 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 hieronder een syntaxis-fragment:

    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.

Volgende stappen

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