Condividi tramite

Eseguire query sui file di archiviazione usando il pool SQL serverless

  • Articolo

Il pool SQL serverless consente di eseguire query sui dati nel data lake. Offre un'area di attacco di query Transact-SQL (T-SQL) che supporta query di dati semistrutturate e non strutturate. Per l'esecuzione di query, sono supportati gli aspetti di T-SQL seguenti:

Per altre informazioni su ciò che è o non è attualmente supportato, leggere la panoramica del pool SQL serverless o gli articoli seguenti:

  • Sviluppare l'accesso alle risorse di archiviazione in cui è possibile usare tabelle esterne e la funzione OPENROWSET per leggere i dati dall'archiviazione.
  • Controllare l'accesso alle risorse di archiviazione in cui è possibile apprendere come abilitare Synapse SQL per accedere all'archiviazione usando l'autenticazione sas o l'identità gestita dell'area di lavoro.

Panoramica

Per supportare un'esperienza uniforme per l'esecuzione di query sul posto dei dati che si trovano in file Archiviazione di Azure, il pool SQL serverless usa la funzione OPENROWSET con altre funzionalità:

Eseguire query su file PARQUET

Per eseguire query sui dati di origine Parquet, usare 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

Per esempi di utilizzo, vedere Eseguire query sui file Parquet.

Eseguire query su file CSV

Per eseguire query sui dati di origine CSV, usare FORMAT = 'CSV'. È possibile specificare lo schema del file CSV come parte della OPENROWSET funzione quando si eseguono query su file CSV:

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

Esistono alcune opzioni aggiuntive che possono essere usate per regolare le regole di analisi in formato CSV personalizzato:

  • ESCAPE_CHAR = 'char' Specifica il carattere nel file utilizzato per eseguire l'escape e tutti i valori delimitatori nel file. Se seguito da un valore diverso da se stesso o da uno qualsiasi dei valori dei delimitatori, il carattere di escape viene eliminato durante la lettura del valore. Il ESCAPE_CHAR parametro viene applicato se FIELDQUOTE è o non è abilitato. Non viene usato per eseguire l'escape del carattere tra virgolette. Il carattere di virgolette deve essere impostato come escape con un altro carattere di virgolette. Il carattere di virgolette può essere visualizzato all'interno del valore della colonna solo se il valore è incapsulato con caratteri di virgolette.
  • FIELDTERMINATOR ='field_terminator' Specifica il carattere di terminazione del campo da utilizzare. Il carattere di terminazione del campo predefinito è una virgola (,).
  • ROWTERMINATOR ='row_terminator' Specifica il carattere di terminazione di riga da utilizzare. Il carattere di terminazione della riga predefinito è un carattere di nuova riga (\r\n).

Query in formato DELTA LAKE

Per eseguire query sui dati di origine Delta Lake, usare FORMAT = 'DELTA' e fare riferimento alla cartella radice contenente i file Delta Lake.

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

La cartella radice deve contenere una sottocartella denominata _delta_log. Per esempi di utilizzo, vedere Eseguire query sui file Delta Lake (v1).

Schema del file

Il linguaggio SQL in Synapse SQL consente di definire lo schema del file come parte della OPENROWSET funzione e di leggere tutto o sottoinsieme di colonne oppure tenta di determinare automaticamente i tipi di colonna dal file usando l'inferenza dello schema.

Lettura di un sottoinsieme selezionato di colonne

Per specificare le colonne da leggere, è possibile specificare una clausola facoltativa WITH all'interno dell'istruzione OPENROWSET .

  • Se sono presenti file di dati CSV, specificare i nomi delle colonne e i relativi tipi di dati per leggere tutte le colonne. Se si vuole specificare un sottoinsieme di colonne, selezionare le colonne dai file di dati di origine in base a numeri ordinali. Le colonne sono associate dalla designazione ordinale.
  • Nel caso di file di dati Parquet, specificare i nomi di colonna che corrispondono a quelli dei file di dati di origine. Le colonne sono associate in base al nome.
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;

Per ogni colonna è necessario specificare il nome e il tipo della colonna nella clausola WITH. Per esempi, vedere Leggere i file CSV senza specificare tutte le colonne.

Inferenza dello schema

Omettendo la WITH clausola dall'istruzione OPENROWSET , è possibile indicare al servizio di rilevare automaticamente (dedurre) lo schema dai file sottostanti.

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

Assicurarsi che vengano usati i tipi di dati derivati appropriati per ottenere prestazioni ottimali.

Esecuzione di query su più file o cartelle

Per eseguire una query T-SQL su un set di file all'interno di una cartella o di un set di cartelle, considerandoli come singola entità o singolo set di righe, fornire il percorso di una cartella o di un modello (usando caratteri jolly) su un set di file o cartelle.

Si applicano le seguenti regole:

  • I modelli possono essere presenti in parte di un percorso di directory o in un nome file.
  • Nello stesso percorso di directory o nome file possono essere presenti diversi modelli.
  • Se sono presenti più caratteri jolly, i file all'interno di tutti i percorsi corrispondenti vengono inclusi nel set di file risultante.
SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/myroot/*/mysubfolder/*.parquet', FORMAT = 'PARQUET' ) as rows

Per esempi di utilizzo, vedere Eseguire query su cartelle e più file.

Funzioni per i metadati dei file

Funzione filename

Questa funzione restituisce il nome del file da cui ha origine la riga.

Per eseguire query su file specifici, vedere la sezione Filename nell'articolo Eseguire query su file specifici.

Il tipo di dati restituito è nvarchar (1024). Per ottenere prestazioni ottimali, è sempre possibile eseguire il cast del risultato della funzione filename al tipo di dati appropriato. Se si usa il tipo di dati Char, assicurarsi che venga usata la lunghezza appropriata.

Funzione filepath

Questa funzione restituisce un percorso completo o parziale:

  • Se viene chiamata senza il parametro, restituisce il percorso completo del file da cui ha origine una riga.
  • Se viene chiamata con il parametro, viene restituita una parte del percorso che corrisponde al carattere jolly nella posizione specificata nel parametro. Ad esempio, il valore del parametro 1 restituisce la parte del percorso che corrisponde al primo carattere jolly.

Per altre informazioni, vedere la sezione Filepath dell'articolo Eseguire query su file specifici.

Il tipo di dati restituito è nvarchar (1024). Per prestazioni ottimali, eseguire sempre il cast del risultato della funzione filepath al tipo di dati appropriato. Se si usa il tipo di dati Char, assicurarsi che venga usata la lunghezza appropriata.

Uso di tipi complessi e strutture di dati annidate o ripetute

Per consentire un'esperienza uniforme con i dati archiviati in tipi di dati annidati o ripetuti, ad esempio nei file Parquet , il pool SQL serverless ha aggiunto le estensioni seguenti.

Proiettare dati annidati o ripetuti

Per proiettare i dati, eseguire un'istruzione SELECT sul file Parquet che contiene colonne di tipi di dati annidati. Nell'output, i valori annidati vengono serializzati in JSON e restituiti come tipo di dati SQL varchar(8000).

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

Per altre informazioni, vedere la sezione Progetto di dati annidati o ripetuti dell'articolo Query Parquet nested types (Tipi annidati di Query Parquet).

Accesso agli elementi di colonne annidate

Per accedere agli elementi annidati da una colonna nidificata, ad esempio Struct, usare la notazione punto per concatenare i nomi dei campi nel percorso. Specificare il percorso come column_name nella WITH clausola della OPENROWSET funzione.

Ecco un esempio di frammento di sintassi:

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

Per impostazione predefinita, la OPENROWSET funzione corrisponde al nome e al percorso del campo di origine con i nomi di colonna specificati nella WITH clausola . È possibile accedere agli elementi contenuti a livelli di annidamento diversi all'interno dello stesso file Parquet di origine usando la WITH clausola .

Valori restituiti

  • La funzione restituisce un valore scalare, ad esempio int, decimale varchar, dall'elemento specificato e nel percorso specificato per tutti i tipi Parquet che non si trovano nel gruppo Tipo annidato.
  • Se il percorso punta a un elemento di tipo annidato, la funzione restituisce un frammento JSON a partire dall'elemento superiore nel percorso specificato. Il frammento JSON è di tipo varchar(8000).
  • Se la proprietà non viene trovata nell'oggetto specificato column_name, la funzione restituisce un errore.
  • Se la proprietà non viene trovata in corrispondenza dell'oggetto specificato column_path, a seconda della modalità Path, la funzione restituisce un errore quando in modalità strict o Null in modalità lax.

Per esempi di query, vedere la sezione Read properties from nested object columns nell'articolo Query Parquet nested types (Eseguire query sui tipi annidati Parquet).

Accesso agli elementi di colonne ripetute

Per accedere agli elementi da una colonna ripetuta, ad esempio un elemento di una matrice o una mappa, usare la funzione JSON_VALUE per ogni elemento scalare necessario per proiettare e fornire:

  • Come primo parametro, una colonna annidata o ripetuta
  • Come secondo parametro, un percorso JSON che specifica l'elemento o la proprietà a cui accedere

Per accedere a elementi non scalabili da una colonna ripetuta, usare la funzione JSON_QUERY per ogni elemento non scalabile necessario per proiettare e fornire:

  • Come primo parametro, una colonna annidata o ripetuta
  • Come secondo parametro, un percorso JSON che specifica l'elemento o la proprietà a cui accedere

Vedere il frammento di sintassi seguente:

    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]

Per gli esempi di query per l'accesso agli elementi di colonne ripetute, vedere l'articolo Eseguire query su tipi annidati di Parquet.

Contenuto correlato

Per altre informazioni su come eseguire una query su tipi di file diversi e su come creare e usare le viste, vedere gli articoli seguenti: