Köra frågor mot lagringsfiler med en serverlös SQL-pool i Azure Synapse Analytics
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:
- Fullständig SELECT-yta , inklusive de flesta SQL-funktioner och -operatorer.
- SKAPA EXTERN TABELL SOM SELECT (CETAS) skapar en extern tabell och exporterar sedan parallellt resultatet av en Transact-SQL SELECT-instruktion till Azure Storage.
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:
- Utveckla lagringsåtkomst där du kan lära dig hur du använder funktionen Extern tabell och OPENROWSET för att läsa data från lagring.
- Kontrollera lagringsåtkomsten där du kan lära dig hur du aktiverar Synapse SQL för åtkomst till lagring med hjälp av SAS-autentisering eller hanterad identitet för arbetsytan.
Ö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:
- Köra frågor mot flera filer eller mappar
- PARQUET-filformat
- Fråga CSV och avgränsad text (fältavgränsare, radavgränsare, escape char)
- DELTA LAKE-format
- Läsa en vald delmängd av kolumner
- Schemahärledning
- filnamnsfunktion
- filepath-funktion
- Arbeta med komplexa typer och kapslade eller upprepade datastrukturer
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:
Feedback
https://aka.ms/ContentUserFeedback.
Kommer snart: Under hela 2024 kommer vi att fasa ut GitHub-problem som feedbackmekanism för innehåll och ersätta det med ett nytt feedbacksystem. Mer information finns i:Skicka och visa feedback för