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:
- 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 T-SQL SELECT-instruktion till Azure Storage.
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:
- Utveckla lagringsåtkomst där du kan använda externa tabeller och funktionen 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 arbetsytans hanterade identitet.
Ö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:
- Fråga PARQUET-filer
- Fråga CSV-filer och avgränsad text (fältavgränsare, radavgränsare, escape char)
- Fråga DELTA LAKE-format
- Läsa en vald delmängd av kolumner
- Schemainferens
- Fråga flera filer eller mappar
- Filnamnsfunktion
- Filepath-funktion
- Arbeta med komplexa typer och kapslade eller upprepade datastrukturer
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
Användningsexempel finns i Fråga Parquet-filer.
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 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. ParameternESCAPE_CHAR
tillämpas oavsett omFIELDQUOTE
ä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
).
Fråga DELTA LAKE-format
Om du vill köra frågor mot Delta Lake-källdata använder FORMAT = 'DELTA'
du 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
. 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änder du ordningstal för att välja kolumnerna från de ursprungliga datafilerna efter ordningstal. Kolumner är bundna av ordningstalsbeteckningen.
- Om det finns Parquet-datafiler anger du kolumnnamn som matchar kolumnnamnen i de ursprungliga datafilerna. Kolumner är bundna 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 WITH
utelämna 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
Användningsexempel finns i Frågemappar och flera filer.
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 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 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 punkt notation för att sammanfoga fältnamn till sökvä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'
Som standard OPENROWSET
matchar funktionen källfältets namn och sökväg med kolumnnamnen WITH
som anges i -satsen. 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
,decimal
ochvarchar
, 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 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.
Frågeexempel finns i avsnittet Läsa egenskaper från kapslade objektkolumner 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ä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 hittar frågeexempel för åtkomst till element från upprepade kolumner i artikeln Frågeparquet kapslade typer .
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: