Köra frågor mot filer med en serverlös SQL-pool

Du kan använda en serverlös SQL-pool för att fråga efter datafiler i olika vanliga filformat, inklusive:

• Avgränsad text, till exempel CSV-filer (kommaavgränsade värden).
• JSON-filer (JavaScript Object Notation).
• Parquet-filer.

Den grundläggande syntaxen för frågor är densamma för alla dessa typer av filer och bygger på SQL-funktionen OPENROWSET. som genererar en tabellraduppsättning från data i en eller flera filer. Följande fråga kan till exempel användas för att extrahera data från CSV-filer.

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

Funktionen OPENROWSET innehåller fler parametrar som avgör faktorer som:

• Schemat för den resulterande raduppsättningen
• Ytterligare formateringsalternativ för avgränsade textfiler.

Utdata från OPENROWSET är en raduppsättning som ett alias måste tilldelas till. I föregående exempel används aliaset rader för att namnge den resulterande raduppsättningen.

Parametern BULK innehåller den fullständiga URL:en till platsen i datasjön som innehåller datafilerna. Det kan vara en enskild fil eller en mapp med ett jokerteckenuttryck för att filtrera de filtyper som ska ingå. Parametern FORMAT anger vilken typ av data som efterfrågas. Exemplet ovan läser avgränsad text från alla .csv filer i mappen filer.

Som du ser i föregående exempel kan du använda jokertecken i parametern BULK för att inkludera eller exkludera filer i frågan. I följande lista visas några exempel på hur detta kan användas:

• https://mydatalake.blob.core.windows.net/data/files/file1.csv: Inkludera endast file1.csv i mappen filer.
• https://mydatalake.blob.core.windows.net/data/files/file*.csv: Alla .csv filer i filer mapp med namn som börjar med "fil".
• https://mydatalake.blob.core.windows.net/data/files/*: Alla filer i mappen .
• https://mydatalake.blob.core.windows.net/data/files/**: Alla filer i mappen och rekursivt dess undermappar.

Du kan också ange flera filsökvägar i parametern BULK och avgränsa varje sökväg med kommatecken.

Köra frågor mot avgränsade textfiler

Avgränsade textfiler är ett vanligt filformat inom många företag. Den specifika formatering som används i avgränsade filer kan variera, till exempel:

• Med och utan rubrikrad.
• Komma- och tabbavgränsade värden.
• Radslut i Windows- och Unix-format.
• Värden som inte citeras och citeras och som undflyende tecken.

Oavsett vilken typ av avgränsad fil du använder kan du läsa data från dem med hjälp av funktionen OPENROWSET med parametern csv FORMAT och andra parametrar som krävs för att hantera den specifika formateringsinformationen för dina data. Till exempel:

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

PARSER_VERSION används för att avgöra hur frågan tolkar den textkodning som används i filerna. Version 1.0 är standard och stöder en mängd olika filkodningar, medan version 2.0 stöder färre kodningar men ger bättre prestanda. Parametern FIRSTROW används för att hoppa över rader i textfilen, för att eliminera ostrukturerad ingresstext eller för att ignorera en rad som innehåller kolumnrubriker.

Ytterligare parametrar som du kan behöva när du arbetar med avgränsade textfiler är:

• FIELDTERMINATOR – tecknet som används för att avgränsa fältvärden i varje rad. En flikavgränsad fil separerar till exempel fält med ett TAB-tecken (\t). Standardfältets avslutare är ett kommatecken (,).
• ROWTERMINATOR – tecknet som används för att ange slutet på en rad med data. En Windows-standardtextfil använder till exempel en kombination av en vagnretur (CR) och radmatning (LF), vilket anges av koden \n; medan UNIX-format textfiler använder ett enda radmatningstecken, vilket kan anges med hjälp av koden 0x0a.
• FIELDQUOTE – tecknet som används för att omsluta citerade strängvärden. Om du till exempel vill se till att kommatecknet i adressfältets värde 126 Main St, apt 2 inte tolkas som en fältavgränsare, kan du omsluta hela fältvärdet inom citattecken så här: "126 Main St, apt 2". Det dubbla citattecknet (") är standardtecknet för fältcitat.

Ange raduppsättningsschemat

Det är vanligt att avgränsade textfiler inkluderar kolumnnamnen på den första raden. Funktionen OPENROWSET kan använda detta för att definiera schemat för den resulterande raduppsättningen och automatiskt härleda datatyperna för kolumnerna baserat på de värden de innehåller. Tänk till exempel på följande avgränsade text:

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

Data består av följande tre kolumner:

• product_id (heltalsnummer)
• product_name (sträng)
• list_price (decimaltal)

Du kan använda följande fråga för att extrahera data med rätt kolumnnamn och korrekt härledda SQL Server-datatyper (i det här fallet INT, NVARCHAR och 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

Parametern HEADER_ROW (som endast är tillgänglig när du använder parser version 2.0) instruerar frågemotorn att använda den första dataraden i varje fil som kolumnnamn, så här:

product_id	product_name	listpris
123	Manick	12.9900
124	Mojäng	3.9900

Tänk nu på följande data:

123,Widget,12.99
124,Gadget,3.99

Den här gången innehåller filen inte kolumnnamnen på en rubrikrad. Så även om datatyperna fortfarande kan härledas anges kolumnnamnen till C1, C2, C3och så vidare.

C1	C2	C3
123	Manick	12.9900
124	Mojäng	3.9900

Om du vill ange explicita kolumnnamn och datatyper kan du åsidosätta standardkolumnnamnen och härledda datatyper genom att ange en schemadefinition i en WITH-sats, så här:

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

Den här frågan genererar förväntade resultat:

product_id	product_name	listpris
123	Manick	12,99
124	Mojäng	3.99

Köra frågor mot JSON-filer

JSON är ett populärt format för webbprogram som utbyter data via REST-gränssnitt eller använder NoSQL-datalager som Azure Cosmos DB. Därför är det inte ovanligt att spara data som JSON-dokument i filer i en datasjö för analys.

En JSON-fil som definierar en enskild produkt kan till exempel se ut så här:

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

Om du vill returnera produktdata från en mapp som innehåller flera JSON-filer i det här formatet kan du använda följande SQL-fråga:

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 har inget specifikt format för JSON-filer, så du måste använda csv- format med FIELDTERMINATOR, FIELDQUOTEoch ROWTERMINATOR inställd på 0x0boch ett schema som innehåller en enda NVARCHAR(MAX). Resultatet av den här frågan är en raduppsättning som innehåller en enda kolumn med JSON-dokument, så här:

Doc
{"product_id":123,"product_name":"Widget","list_price": 12.99}
{"product_id":124,"product_name":"Gadget","list_price": 3.99}

Om du vill extrahera enskilda värden från JSON kan du använda funktionen JSON_VALUE i SELECT-instruktionen enligt följande:

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

Den här frågan returnerar en raduppsättning som liknar följande resultat:

produkt	pris
Manick	12,99
Mojäng	3.99

Köra frågor mot Parquet-filer

Parquet är ett vanligt format för bearbetning av stordata på distribuerad fillagring. Det är ett effektivt dataformat som är optimerat för komprimering och analytiska frågor.

I de flesta fall bäddas schemat för data in i Parquet-filen, så du behöver bara ange parametern BULK med en sökväg till de filer som du vill läsa och en FORMAT- parameter för parquet; Gillar det här:

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

Fråga partitionerade data

Det är vanligt i en datasjö att partitionera data genom att dela upp flera filer i undermappar som återspeglar partitioneringskriterier. Detta gör det möjligt för distribuerade bearbetningssystem att fungera parallellt på flera partitioner av data, eller för att enkelt eliminera dataläsningar från specifika mappar baserat på filtreringsvillkor. Anta till exempel att du effektivt behöver bearbeta försäljningsorderdata och ofta behöver filtrera baserat på det år och den månad då beställningarna gjordes. Du kan partitionering av data med hjälp av mappar, så här:

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

Om du vill skapa en fråga som filtrerar resultatet så att endast order för januari och februari 2020 inkluderas kan du använda följande kod:

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 numrerade filsökvägsparametrarna i WHERE-satsen refererar till jokertecken i mappnamnen i BULK-sökvägen -so parametern 1 är * i year=* mappnamn och parameter 2 är * i month=* mappnamn.