Query's uitvoeren op bestanden met een serverloze SQL-pool

  • 10 minuten

U kunt een serverloze SQL-pool gebruiken om query's uit te voeren op gegevensbestanden in verschillende algemene bestandsindelingen, waaronder:

  • Tekst met scheidingstekens, zoals csv-bestanden (door komma's gescheiden waarden).
  • JSON-bestanden (JavaScript Object Notation).
  • Parquet-bestanden.

De basissyntaxis voor het uitvoeren van query's is hetzelfde voor al deze typen bestanden en is gebaseerd op de FUNCTIE OPENROWSET SQL; waarmee een tabellaire rijenset wordt gegenereerd op basis van gegevens in een of meer bestanden. De volgende query kan bijvoorbeeld worden gebruikt om gegevens uit CSV-bestanden te extraheren.

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/files/*.csv',
    FORMAT = 'csv') AS rows

De functie OPENROWSET bevat meer parameters die factoren bepalen, zoals:

  • Het schema van de resulterende rijenset
  • Aanvullende opmaakopties voor tekstbestanden met scheidingstekens.

Tip

U vindt de volledige syntaxis voor de functie OPENROWSET in de documentatie van Azure Synapse Analytics.

De uitvoer van OPENROWSET is een rijenset waaraan een alias moet worden toegewezen. In het vorige voorbeeld worden de aliasrijen gebruikt om de resulterende rijenset een naam te geven.

De parameter BULK bevat de volledige URL naar de locatie in de data lake met de gegevensbestanden. Dit kan een afzonderlijk bestand zijn of een map met een jokertekenexpressie om de bestandstypen te filteren die moeten worden opgenomen. De parameter FORMAT geeft het type gegevens op dat wordt opgevraagd. In het bovenstaande voorbeeld worden tekst met scheidingstekens uit alle .csv bestanden in de map bestanden gelezen.

Notitie

In dit voorbeeld wordt ervan uitgegaan dat de gebruiker toegang heeft tot de bestanden in het onderliggende archief. Als de bestanden zijn beveiligd met een SAS-sleutel of aangepaste identiteit, moet u een referentie met serverbereik maken.

Zoals u in het vorige voorbeeld kunt zien, kunt u jokertekens in de parameter BULK gebruiken om bestanden in de query op te nemen of uit te sluiten. In de volgende lijst ziet u enkele voorbeelden van hoe dit kan worden gebruikt:

  • https://mydatalake.blob.core.windows.net/data/files/file1.csv: neem alleen file1.csv op in de map bestanden .
  • https://mydatalake.blob.core.windows.net/data/files/file*.csv: Alle .csv bestanden in de map bestanden met namen die beginnen met 'bestand'.
  • https://mydatalake.blob.core.windows.net/data/files/*: Alle bestanden in de map bestanden .
  • https://mydatalake.blob.core.windows.net/data/files/**: Alle bestanden in de map bestanden en recursief de submappen.

U kunt ook meerdere bestandspaden opgeven in de parameter BULK , waarbij elk pad wordt gescheiden door een komma.

Query's uitvoeren op tekstbestanden met scheidingstekens

Tekstbestanden met scheidingstekens zijn een algemene bestandsindeling binnen veel bedrijven. De specifieke opmaak die wordt gebruikt in bestanden met scheidingstekens, kan variëren, bijvoorbeeld:

  • Met en zonder veldnamenrij.
  • Door komma's en tabs gescheiden waarden.
  • Einden van lijnen in Windows- en Unix-stijlen.
  • Niet-aanhalingstekens en waarden tussen aanhalingstekens en escapetekens.

Ongeacht het type bestand met scheidingstekens dat u gebruikt, kunt u er gegevens van lezen met behulp van de functie OPENROWSET met de parameter CSV FORMAT en andere parameters, zoals vereist voor het afhandelen van de specifieke opmaakdetails voor uw gegevens. Voorbeeld:

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/files/*.csv',
    FORMAT = 'csv',
    PARSER_VERSION = '2.0',
    FIRSTROW = 2) AS rows

De PARSER_VERSION wordt gebruikt om te bepalen hoe de query de tekstcodering interpreteert die in de bestanden wordt gebruikt. Versie 1.0 is de standaardinstelling en ondersteunt een breed scala aan bestandscoderingen, terwijl versie 2.0 minder coderingen ondersteunt, maar betere prestaties biedt. De parameter FIRSTROW wordt gebruikt om rijen in het tekstbestand over te slaan, om ongestructureerde preambule tekst te elimineren of een rij met kolomkoppen te negeren.

Aanvullende parameters die u mogelijk nodig hebt bij het werken met tekstbestanden met scheidingstekens, zijn onder andere:

  • FIELDTERMINATOR: het teken dat wordt gebruikt om veldwaarden in elke rij te scheiden. Een bestand met tabscheidingstekens scheidt bijvoorbeeld velden met een TAB-teken (\t). Het standaardveldeindteken is een komma (,).
  • ROWTERMINATOR: het teken dat wordt gebruikt om het einde van een rij met gegevens aan te duiden. Een standaard Windows-tekstbestand maakt bijvoorbeeld gebruik van een combinatie van een regelterugloop (CR) en regelfeed (LF), die wordt aangegeven door de code \n; terwijl UNIX-stijl tekstbestanden één regelinvoerteken gebruiken, dat kan worden aangegeven met behulp van de code 0x0a.
  • FIELDQUOTE: het teken dat wordt gebruikt om tekenreekswaarden tussen aanhalingstekens te sluiten. Als u er bijvoorbeeld voor wilt zorgen dat de komma in de adresveldwaarde 126 Main St, apt 2 niet wordt geïnterpreteerd als een veldscheidingsteken, kunt u de hele veldwaarde tussen aanhalingstekens plaatsen, zoals deze: "126 Main St, apt 2". Het dubbele aanhalingsteken (") is het standaardteken voor het aanhalingsteken voor velden.

Tip

Raadpleeg de documentatie van Azure Synapse Analytics voor meer informatie over aanvullende parameters bij het werken met tekstbestanden met scheidingstekens.

Het rijsetschema opgeven

Het is gebruikelijk dat tekstbestanden met scheidingstekens de kolomnamen in de eerste rij bevatten. De functie OPENROWSET kan dit gebruiken om het schema voor de resulterende rijenset te definiëren en automatisch de gegevenstypen van de kolommen af te leiden op basis van de waarden die ze bevatten. Denk bijvoorbeeld aan de volgende tekst met scheidingstekens:

product_id,product_name,list_price
123,Widget,12.99
124,Gadget,3.99

De gegevens bestaan uit de volgende drie kolommen:

  • product_id (geheel getal)
  • product_name (tekenreeks)
  • list_price (decimaal getal)

U kunt de volgende query gebruiken om de gegevens te extraheren met de juiste kolomnamen en correct uitgestelde SQL Server-gegevenstypen (in dit geval INT, NVARCHAR en DECIMAL)

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/files/*.csv',
    FORMAT = 'csv',
    PARSER_VERSION = '2.0',
    HEADER_ROW = TRUE) AS rows

De parameter HEADER_ROW (die alleen beschikbaar is wanneer u parserversie 2.0) gebruikt, geeft de query-engine de opdracht om de eerste rij met gegevens in elk bestand te gebruiken als kolomnamen, zoals deze:

product_id product_name list_price
123 Widget 12.9900
124 Gadget 3.9900

Overweeg nu de volgende gegevens:

123,Widget,12.99
124,Gadget,3.99

Dit keer bevat het bestand niet de kolomnamen in een veldnamenrij; Dus terwijl de gegevenstypen nog steeds kunnen worden afgeleid, worden de kolomnamen ingesteld op C1, C2, C3 enzovoort.

C1 C2 C3
123 Widget 12.9900
124 Gadget 3.9900

Als u expliciete kolomnamen en gegevenstypen wilt opgeven, kunt u de standaardkolomnamen en uitgestelde gegevenstypen overschrijven door een schemadefinitie op te geven in een WITH-component , zoals hieronder:

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/files/*.csv',
    FORMAT = 'csv',
    PARSER_VERSION = '2.0')
WITH (
    product_id INT,
    product_name VARCHAR(20) COLLATE Latin1_General_100_BIN2_UTF8,
    list_price DECIMAL(5,2)
) AS rows

Deze query produceert de verwachte resultaten:

product_id product_name list_price
123 Widget 12.99
124 Gadget 3.99

Tip

Wanneer u met tekstbestanden werkt, kunnen er incompatibiliteit optreden met UTF-8 gecodeerde gegevens en de sortering die wordt gebruikt in de hoofddatabase voor de serverloze SQL-pool. U kunt dit oplossen door een compatibele sortering op te geven voor afzonderlijke VARCHAR-kolommen in het schema. Zie de richtlijnen voor probleemoplossing voor meer informatie.

Query's uitvoeren op JSON-bestanden

JSON is een populaire indeling voor webtoepassingen die gegevens uitwisselen via REST-interfaces of NoSQL-gegevensarchieven gebruiken, zoals Azure Cosmos DB. Het is dus niet ongebruikelijk om gegevens op te slaan als JSON-documenten in bestanden in een data lake voor analyse.

Een JSON-bestand dat een afzonderlijk product definieert, kan er bijvoorbeeld als volgt uitzien:

{
    "product_id": 123,
    "product_name": "Widget",
    "list_price": 12.99
}

Als u productgegevens wilt retourneren uit een map met meerdere JSON-bestanden in deze indeling, kunt u de volgende SQL-query gebruiken:

SELECT doc
FROM
    OPENROWSET(
        BULK 'https://mydatalake.blob.core.windows.net/data/files/*.json',
        FORMAT = 'csv',
        FIELDTERMINATOR ='0x0b',
        FIELDQUOTE = '0x0b',
        ROWTERMINATOR = '0x0b'
    ) WITH (doc NVARCHAR(MAX)) as rows

OPENROWSET heeft geen specifieke indeling voor JSON-bestanden, dus u moet csv-indeling gebruiken met FIELDTERMINATOR, FIELDQUOTE en ROWTERMINATOR ingesteld op 0x0b, en een schema dat één NVARCHAR(MAX)-kolom bevat. Het resultaat van deze query is een rijenset met één kolom met JSON-documenten, zoals:

Doc
{"product_id":123,"product_name":"Widget","list_price": 12,99}
{"product_id":124,"product_name":"Gadget","list_price": 3,99}

Als u afzonderlijke waarden uit de JSON wilt extraheren, kunt u de functie JSON_VALUE in de SELECT-instructie gebruiken, zoals hier wordt weergegeven:

SELECT JSON_VALUE(doc, '$.product_name') AS product,
           JSON_VALUE(doc, '$.list_price') AS price
FROM
    OPENROWSET(
        BULK 'https://mydatalake.blob.core.windows.net/data/files/*.json',
        FORMAT = 'csv',
        FIELDTERMINATOR ='0x0b',
        FIELDQUOTE = '0x0b',
        ROWTERMINATOR = '0x0b'
    ) WITH (doc NVARCHAR(MAX)) as rows

Deze query retourneert een rijenset die vergelijkbaar is met de volgende resultaten:

product price
Widget 12.99
Gadget 3.99

Query's uitvoeren op Parquet-bestanden

Parquet is een veelgebruikte indeling voor verwerking van big data op gedistribueerde bestandsopslag. Het is een efficiënte gegevensindeling die is geoptimaliseerd voor compressie en analytische query's.

In de meeste gevallen wordt het schema van de gegevens ingesloten in het Parquet-bestand, dus u hoeft alleen de PARAMETER BULK op te geven met een pad naar de bestanden die u wilt lezen en een FORMAT-parameter van Parquet, zoals:

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/files/*.*',
    FORMAT = 'parquet') AS rows

Gepartitioneerde gegevens opvragen

Het is gebruikelijk in een data lake om gegevens te partitioneren door meerdere bestanden in submappen te splitsen die de partitioneringscriteria weerspiegelen. Hierdoor kunnen gedistribueerde verwerkingssystemen parallel werken op meerdere partities van de gegevens, of om gegevensleesbewerkingen uit specifieke mappen eenvoudig te elimineren op basis van filtercriteria. Stel dat u de verkoopordergegevens efficiënt moet verwerken en vaak moet filteren op basis van het jaar en de maand waarin orders zijn geplaatst. U kunt de gegevens partitioneren met behulp van mappen, zoals deze:

  • /Orders
    • /year=2020
      • /month=1
        • /01012020.parquet
        • /02012020.parquet
        • ...
      • /month=2
        • /01022020.parquet
        • /02022020.parquet
        • ...
      • ...
    • /year=2021
      • /month=1
        • /01012021.parquet
        • /02012021.parquet
        • ...
      • ...

Als u een query wilt maken waarmee de resultaten alleen de orders voor januari en februari 2020 worden opgenomen, kunt u de volgende code gebruiken:

SELECT *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/orders/year=*/month=*/*.*',
    FORMAT = 'parquet') AS orders
WHERE orders.filepath(1) = '2020'
    AND orders.filepath(2) IN ('1','2');

De genummerde bestandspadparameters in de WHERE-component verwijzen naar de jokertekens in de mapnamen in het BULK-pad. De parameter 1 is dus de * in de mapnaam year=* en parameter 2 is de * in de mapnaam maand=* .