Dela via

Köra frågor mot lagringsfiler med hjälp av en serverlös SQL-pool

Med en serverlös SQL-pool kan du fråga efter data i din datasjö. Den erbjuder en T-SQL-frågeyta (Transact-SQL) som rymmer halvstrukturerade och ostrukturerade datafrågor. Följande T-SQL-aspekter stöds för frågor:

Mer information om vad som stöds eller inte stöds för närvarande finns i översikten över den serverlösa SQL-poolen eller följande artiklar:

Översikt

För att stödja en smidig upplevelse för frågor på plats av data som finns i Azure Storage-filer använder serverlös SQL-pool funktionen OPENROWSET med fler funktioner:

Utför frågor på PARQUET-filer

Om du vill ställa frågor mot Parquet-källdata använder du 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

Användningsexempel finns i Sök i Parquet-filer.

Fråga CSV-filer

Om du vill köra frågor mot CSV-källdata använder du FORMAT = 'CSV'. Du kan ange schemat för CSV-filen som en del av OPENROWSET funktionen när du kör frågor mot CSV-filer:

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

Det finns några extra alternativ som kan användas för att justera parsningsregler till anpassat CSV-format:

  • ESCAPE_CHAR = 'char' Anger tecknet i filen som används för att undkomma sig själv och alla avgränsarvärden i filen. Om escape-tecknet följs av ett annat värde än sig självt, eller något av avgränsarvärdena, tas escape-tecknet bort när värdet läss. Parametern ESCAPE_CHAR tillämpas oavsett om FIELDQUOTE är eller inte är aktiverat. Den används inte för att undkomma citattecknet. Citattecknet måste vara undantaget med ett annat citattecken. Citattecken kan endast visas inom kolumnvärdet om värdet kapslas in med citattecken.
  • FIELDTERMINATOR ='field_terminator' Anger den fältavgränsare som ska användas. Standardfältets avslutare är ett kommatecken (,).
  • ROWTERMINATOR ='row_terminator' Anger radavgränsaren som ska användas. Standardradsavgränsaren är ett nytt radtecken (\r\n).

Sök DELTA LAKE-format

För att köra frågor mot källdata i Delta Lake, använd FORMAT = 'DELTA' och referera till rotmappen som innehåller dina Delta Lake-filer.

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

Rotmappen måste innehålla en undermapp med namnet _delta_log. Användningsexempel finns i Query Delta Lake (v1)-filer.

Filschema

Med SQL-språket i Synapse SQL kan du definiera schemat för filen som en del av funktionen och läsa alla eller delmängder av kolumner, eller så försöker den automatiskt fastställa kolumntyper från filen med hjälp av OPENROWSET schemainferens.

Läsa en vald delmängd av kolumner

Om du vill ange kolumner som du vill läsa kan du ange en valfri WITH sats i instruktionen OPENROWSET .

  • Om det finns CSV-datafiler anger du kolumnnamn och deras datatyper för att läsa alla kolumner. Om du vill ha en delmängd av kolumner, använd ordningstal för att välja kolumnerna från de ursprungliga datafilerna. Kolumnerna är bundna till ordinalbeteckningen.
  • Om det finns Parquet-datafiler anger du kolumnnamn som matchar kolumnnamnen i de ursprungliga datafilerna. Kolumner är kopplade till namn.
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;

För varje kolumn måste du ange kolumnnamn och ange en WITH sats. Exempel finns i Läsa CSV-filer utan att ange alla kolumner.

Schemahärledning

Genom att utelämna WITH-klausulen från OPENROWSET-instruktionen kan du instruera tjänsten att automatiskt detektera (härleda) schemat från underliggande filer.

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

Se till att lämpliga härledda datatyper används för optimala prestanda.

Fråga flera filer eller mappar

Om du vill köra en T-SQL-fråga över en uppsättning filer i en mapp eller uppsättning mappar medan du behandlar dem som en enda entitet eller raduppsättning, anger du en sökväg till en mapp eller ett mönster (med jokertecken) över en uppsättning filer eller mappar.

Följande regler gäller:

  • Mönster kan visas antingen i en del av en katalogsökväg eller i ett filnamn.
  • Flera mönster kan visas i samma katalogsteg eller filnamn.
  • Om det finns flera jokertecken inkluderas filer inom alla matchande sökvägar i den resulterande filuppsättningen.
SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/myroot/*/mysubfolder/*.parquet', FORMAT = 'PARQUET' ) as rows

Användningsexempel finns i Frågemappar och flera filer.

Filmetadata funktioner

Filnamnsfunktion

Den här funktionen returnerar filnamnet som raden kommer från.

Om du vill fråga efter specifika filer läser du avsnittet Filnamn i artikeln Fråga efter specifika filer .

Returdatatypen är nvarchar(1024). För optimal prestanda ska du alltid omvandla resultatet av filnamnsfunktionen till lämplig datatyp. Om du använder teckendatatyp kontrollerar du att lämplig längd används.

Filepath-funktion

Den här funktionen returnerar en fullständig sökväg eller en del av sökvägen:

  • När den anropas utan parameter returnerar den fullständiga filsökvägen som en rad kommer från.
  • När funktionen anropas med en parameter returneras den del av sökvägen som matchar jokertecknet på den position som anges i parametern. Parametervärdet 1 returnerar till exempel en del av sökvägen som matchar det första jokertecknet.

Mer information finns i avsnittet Filepath i artikeln Fråga specifika filer .

Returdatatypen är nvarchar(1024). För optimala prestanda ska du alltid omvandla resultatet av filsökvägsfunktionen till lämplig datatyp. Om du använder teckendatatyp kontrollerar du att lämplig längd används.

Arbeta med komplexa typer och kapslade eller upprepade datastrukturer

För att ge en smidig upplevelse av data som lagras i kapslade eller upprepade datatyper, till exempel i Parquet-filer , har den serverlösa SQL-poolen lagt till följande tillägg.

Project kapslade eller upprepade data

Om du vill projicera data kör du en SELECT instruktion över Parquet-filen som innehåller kolumner med kapslade datatyper. Vid utdata serialiseras kapslade värden till JSON och returneras som sql-datatypen varchar(8000).

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

Mer information finns i avsnittet Projektera kapslade eller upprepade data i artikeln Fråga Parquet-kapslade typer.

Få åtkomst till element från kapslade kolumner

Om du vill komma åt kapslade element från en kapslad kolumn, till exempel Struct, använder du punktnotation för att sammanfoga fältnamn till vägen. Ange sökvägen som column_name i WITH -satsen för OPENROWSET funktionen.

Exempel på syntaxfragment är följande:

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

Funktionen OPENROWSET matchar som standard källfältets namn och sökväg med kolumnnamnen som anges i WITH villkoret. Element som finns på olika kapslingsnivåer i samma parquet-källfil kan nås med hjälp av WITH -satsen.

Returnera värden

  • Funktionen returnerar ett skalärt värde, till exempel int, decimaloch varchar, från det angivna elementet och på den angivna sökvägen, för alla Parquet-typer som inte finns i gruppen Kapslad typ .
  • Om sökvägen pekar på ett element som är av kapslad typ returnerar funktionen ett JSON-fragment som börjar från det översta elementet på den angivna sökvägen. JSON-fragmentet är av typen varchar(8000).
  • Om egenskapen inte kan hittas på den angivna column_namereturnerar funktionen ett fel.
  • Om egenskapen inte kan hittas i angivet column_path, beroende på sökvägsläge, returnerar funktionen ett fel när den är i strikt läge eller null när den är i släpphänt läge.

För exempel på frågor, se avsnittet om att läsa egenskaper från kolumner med kapslade objekt i artikeln om att fråga Parquet kapslade typer.

Få åtkomst till element från upprepade kolumner

Om du vill komma åt element från en upprepad kolumn, till exempel ett element i en matris eller karta, använder du funktionen JSON_VALUE för varje skalärt element som du behöver för att projicera och tillhandahålla:

  • Kapslad eller upprepad kolumn som den första parametern
  • En JSON-sökväg som anger det element eller den egenskap som ska kommas åt, som en andra parameter

Om du vill komma åt icke-skalära element från en upprepad kolumn använder du funktionen JSON_QUERY för varje icke-skalära element som du behöver för att projicera och tillhandahålla:

  • Kapslad eller upprepad kolumn som den första parametern
  • En JSON-sökväg som anger det element eller den egenskap som ska kommas åt, som en andra parameter

Se följande syntaxfragment:

    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]

Du kan hitta exempel på frågor för att komma åt element från upprepade kolumner i artikeln Query Parquet nested types.

Relaterat innehåll

Mer information om hur du frågar efter olika filtyper och om du vill skapa och använda vyer finns i följande artiklar: