Dela via

Köra frågor mot lagringsfiler med en serverlös SQL-pool i Azure Synapse Analytics

  • Artikel

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

Mer information om vad som är jämfört med vad som för närvarande inte stöds finns i översiktsartikeln för serverlös SQL-pool eller följande artiklar:

Översikt

För att ge stöd för en smidig upplevelse för att köra frågor mot data som finns i Azure Storage-filer använder serverlös SQL-pool funktionen OPENROWSET med ytterligare funktioner:

Fråga PARQUET-filer

Om du vill köra 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

I artikeln Query Parquet files (Fråga Parquet-filer) finns användningsexempel.

Köra frågor mot 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 frågar 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 ytterligare 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 aktiverat eller inte. Den kommer inte att användas 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. Standardradavslutaren är ett nytt radtecken: \r\n.

Fråga DELTA LAKE-format

Om du vill köra frågor mot Delta Lake-källdata använder du FORMAT = 'DELTA' och refererar 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. Läs artikeln om Delta Lake-format för användningsexempel.

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änder du ordningstal för att välja kolumnerna från de ursprungliga datafilerna efter ordningstal. Kolumner kommer att bindas av ordningstalsbeteckningen.
  • Om det finns Parquet-datafiler anger du kolumnnamn som matchar kolumnnamnen i de ursprungliga datafilerna. Kolumner kommer att bindas efter 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-satsen från -instruktionen OPENROWSET kan du instruera tjänsten att automatiskt identifiera (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

Se Frågemappar och flera filer för användningsexempel.

Filmetadatafunktioner

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 den anropas med parameter returneras en 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 optimal prestanda ska du alltid omvandla resultatet av filepath-funktionen 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 möjliggöra en smidig upplevelse med 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 detaljerad information finns i avsnittet Project nested or repeated data i artikeln Query Parquet nested types (Kapslade typer av frågeparquet).

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 "dot notation" för att sammanfoga fältnamn till sökvägen. Ange sökvägen som column_name i funktionens WITH-sats OPENROWSET .

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'

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

Returnera värden

  • Funktionen returnerar ett skalärt värde, till exempel int, decimal och 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_name returnerar funktionen ett fel.
  • Om egenskapen inte kan hittas på den angivna 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 frågeexempel läser du avsnittet Åtkomstelement från kapslade kolumner i artikeln Frågeparquet-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ä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

Se syntaxfragment nedan:

    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 hittar frågeexempel för åtkomst till element från upprepade kolumner i artikeln Frågeparquet kapslade typer .

Nästa steg

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