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 hanterar halvstrukturerade och ostrukturerade datafrågor. Följande T-SQL-aspekter stöds för frågor:

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

Översikt

För att ge en smidig upplevelse för frågor om data som finns i Azure Storage-filer använder den serverlösa SQL-poolen funktionen OPENROWSET med ytterligare funktioner:

Fråga PARQUET-filer

Om du vill fråga 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

Se artikeln Query Parquet files (Frågeparquet-filer ) för användningsexempel.

Köra frågor mot CSV-filer

Om du vill fråga 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 fly från 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 undgå citattecknet. Citattecknet måste vara undantaget med ett annat citattecken. Citattecken kan endast visas i kolumnvärdet om värdet kapslas in med citattecken.
  • FIELDTERMINATOR ='field_terminator' Anger vilken fältavgränsare som ska användas. Standardfältavgränsaren är ett kommatecken (",")
  • ROWTERMINATOR ='row_terminator' Anger radavgränsaren som ska användas. Standardradsavgränsaren ä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. Se 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 kolumner eller delmängder av kolumner, eller så försöker det 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 - OPENROWSET instruktionen.

  • 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 kolumnerna 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 binds med 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.

Köra frågor mot 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 i 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 specifika filer läser du avsnittet Filnamn i artikeln Fråga specifika filer .

Returdatatypen är nvarchar(1024). För optimala prestanda ska du alltid omvandla resultatet av filnamnsfunktionen till lämplig datatyp. Om du använder teckendatatypen kontrollerar du att rätt 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 returneras den fullständiga filsökvägen som en rad kommer från.
  • När den anropas med parametern 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 Filsökväg 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 teckendatatypen kontrollerar du att rätt 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 serverlös SQL-pool 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 (Kapslade eller upprepade data) i artikeln Query Parquet nested types (Kapslade typer av frågeparquet ).

Komma åt 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 sökvägen. Ange sökvägen som column_name i WITH-satsen i OPENROWSET funktionen.

Syntaxfragmentexemplet ä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.

Returvä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 returnerar funktionen, beroende på sökvägsläge, ett fel när den är i strikt läge eller null när den är i laxläge.

Frågeexempel finns i avsnittet Access-element från kapslade kolumner i artikeln Query Parquet nested types (Kapslade typer av frågor ).

Komma åt 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 projicera och ange:

  • Kapslad eller upprepad kolumn som den första parametern
  • En JSON-sökväg som anger vilket element eller vilken 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 vilket element eller vilken 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 att komma åt element från upprepade kolumner i artikeln Query Parquet nested types (Kapslade frågetyper).

Exempel på frågor

Du kan lära dig mer om att fråga olika typer av data med hjälp av exempelfrågorna.

Verktyg

De verktyg du behöver för att köra frågor: – Azure Synapse Studio – Azure Data Studio – SQL Server Management Studio

Demoinstallation

Det första steget är att skapa en databas där du kör frågorna. Sedan initierar du objekten genom att köra installationsskriptet på databasen.

Det här installationsskriptet skapar datakällor, databasbegränsade autentiseringsuppgifter och externa filformat som används för att läsa data i dessa exempel.

Anteckning

Databaser används bara för att visa metadata, inte för faktiska data. Skriv ned det databasnamn som du använder. Du behöver det senare.

CREATE DATABASE mydbname;

Tillhandahållna demodata

Demodata innehåller följande datamängder:

  • NYC Taxi – Yellow Taxi Trip Records – en del av offentliga NYC-datauppsättningar i CSV- och Parquet-format
  • Populationens datauppsättning i CSV-format
  • Parquet-exempelfiler med kapslade kolumner
  • Böcker i JSON-format
Mappsökväg Beskrivning
/Csv/ Överordnad mapp för data i CSV-format
/csv/population/
/csv/population-unix/
/csv/population-unix-hdr/
/csv/population-unix-hdr-escape
/csv/population-unix-hdr-quoted		 Mappar med populationsdatafiler i olika CSV-format.
/csv/taxi/ Mapp med offentliga NYC-datafiler i CSV-format
/Parkett/ Överordnad mapp för data i Parquet-format
/parquet/taxi Nyc offentliga datafiler i Parquet-format, partitionerade efter år och månad med hive/Hadoop-partitioneringsschema.
/parquet/nested/ Parquet-exempelfiler med kapslade kolumner
/Json/ Överordnad mapp för data i JSON-format
/json/books/ JSON-filer med bokdata

Nästa steg

Mer information om hur du kör frågor mot olika filtyper och hur du skapar och använder vyer finns i följande artiklar: