Condividi tramite

Eseguire query su file esterni del data lake

Si applica a:✅ endpoint di analisi SQL e magazzino dati in Microsoft Fabric

Fabric Data Warehouse e l'endpoint di analisi SQL consentono di eseguire query sui dati archiviati in file all'interno di un data lake usando codice Transact-SQL (T-SQL).

Forniscono un'area di superficie di query T-SQL familiare che supporta le query su dati strutturati, semistrutturati e non strutturati. In Fabric Data Warehouse e nell'endpoint di analisi SQL è possibile eseguire query su diversi formati di file, ad esempio Parquet, CSV e JSONL.

OPENROWSET

La sintassi semplificata della OPENROWSET funzione è:

OPENROWSET( BULK '{file path}', [ options...] )
   [ WITH ( {result set column schema} ) ]

OPENROWSET supporta tre modi per specificare il percorso del file:

  • Percorso assoluto contenente l'URL completo del file da leggere. Questo è l'approccio più semplice quando si conosce il percorso esatto del file.
  • Percorso relativo con un'origine dati : il percorso relativo viene aggiunto alla posizione radice definita nell'origine dati per formare il percorso completo. L'origine dati può puntare a un URL di archiviazione esterno (ad esempio BLOB di Azure, ADLS Gen2) o all'URL radice di una Lakehouse in Fabric.
  • Percorso relativo che inizia con /Files : questa opzione funziona solo quando si eseguono query su un lakehouse tramite l'endpoint di analisi SQL. La /Files cartella rappresenta l'area dei file Lakehouse ed è possibile usare percorsi relativi senza definire un'origine dati.

I file possono essere inseriti in una delle opzioni di archiviazione seguenti:

  • Azure Data Lake Storage (ADLS): un servizio di archiviazione cloud scalabile e gerarchico ottimizzato per i carichi di lavoro di analisi dei Big Data.
  • Archiviazione BLOB di Azure : un servizio di archiviazione oggetti per utilizzo generico per l'archiviazione di grandi quantità di dati non strutturati, ad esempio file, immagini e log.
  • Fabric OneLake - l'archiviazione nativa del data lake per Microsoft Fabric che fornisce un data lake unificato e logico per tutti i carichi di lavoro di Fabric. OneLake consente anche l'accesso indiretto ai dati archiviati in posizioni esterne, tra cui:
    • Amazon S3 - Servizio di archiviazione oggetti fornito da Amazon Web Services.
    • Google Cloud Storage (GCS) - Servizio di archiviazione oggetti per Google Cloud Platform.
    • SharePoint : servizio di archiviazione di documenti e file collaborativi in Microsoft 365.
    • OneDrive - Servizio di archiviazione cloud personale per i file in Microsoft 365.

Queste funzionalità consentono l'esecuzione di query flessibili e basate su T-SQL tra i dati archiviati in un'ampia gamma di sistemi di archiviazione Cloud e SaaS, eliminando la necessità di prima dell'inserimento o della trasformazione dei dati prima dell'analisi.

Opzioni OPENROWSET pertinenti

Usare le opzioni in OPENROWSET per descrivere il formato di file sottostante e controllare la modalità di analisi dei dati. Queste impostazioni sono particolarmente importanti per i file di testo delimitati, in cui è necessario definire la modalità di separazione delle righe e delle colonne.

Le opzioni più comuni includono:

  • FIELDTERMINATOR, ROWTERMINATORe FIELDQUOTE - Specificare i caratteri che separano campi e righe. Queste opzioni garantiscono un'analisi accurata delle colonne e dei record. Gestiscono anche i valori racchiusi tra virgolette nei file delimitati per mantenere il testo che contiene delimitatori.
  • HEADER_ROW e FIRSTROW - Indicare se il file contiene una riga di intestazione e definire quale riga deve essere considerata come la prima riga di dati.
  • CODEPAGE - Impostare la codifica dei caratteri per interpretare correttamente caratteri speciali e testo non ASCII.

Queste opzioni offrono flessibilità per l'uso di vari formati di file e assicurano che i dati vengano letti correttamente indipendentemente dalle differenze nella struttura o nella codifica.

La clausola facoltativa WITH in OPENROWSET consente di definire uno schema esplicito per i dati. Fornire uno schema è utile quando non si vuole OPENROWSET dedurre automaticamente lo schema dai file sottostanti. Usare la clausola per eseguire l'override WITH dell'inferenza automatica dello schema e definire esattamente il modo in cui i dati devono essere proiettati in formato tabulare.

Casi d'uso per OPENROWSET

Per offrire un'esperienza di query sul posto perfetta per i dati archiviati in un data lake, Fabric Data Warehouse e l'endpoint di analisi SQL usano la funzione OPENROWSET per fare riferimento ai file e leggerne il contenuto.

La OPENROWSET funzione offre funzionalità avanzate per l'esecuzione di query sui file, tra cui:

Eseguire query su file PARQUET con OPENROWSET

Parquet è un formato di file a colonne ottimizzato per i carichi di lavoro di analisi. Archivia i dati in base alle colonne anziché alle righe, consentendo una compressione efficiente, una riduzione delle I/O e prestazioni delle query più veloci, soprattutto quando si esegue una query su un subset di colonne.

La OPENROWSET funzione consente l'accesso semplice e intuitivo ai file Parquet direttamente dal codice T-SQL.

Per eseguire query su un file Parquet, specificare l'URL del file Parquet usando la OPENROWSET funzione :

SELECT * FROM
OPENROWSET( BULK 'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder/data.parquet');

La OPENROWSET funzione restituisce ogni riga del file Parquet come riga nel set di risultati. Le colonne nel set di risultati corrispondono allo schema del file Parquet.

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

Eseguire query su file delimitati con OPENROWSET

Il formato testuale delimitato è un formato di file basato su testo e orientato alle righe comunemente usato per lo scambio di dati e l'archiviazione dati leggera.

  • I dati sono organizzati in righe separate da un terminatore di riga.
  • Più celle all'interno di ogni riga sono separate da un separatore di campo.
  • I valori nelle celle sono separati da delimitatori, ad esempio virgole o tabulazioni.

I file delimitati sono ampiamente supportati e semplici da produrre in molti sistemi e strumenti.

Il formato delimitato più comunemente usato è formato di valori delimitati da virgole (CSV). In un file CSV le righe sono separate da una nuova riga e i valori per virgola, facoltativamente racchiusi tra virgolette doppie. Esistono tuttavia molte varianti, come ad esempio valori delimitati da tabulazioni (TSV), e altri formati delimitati personalizzati.

OPENROWSETUsando , è possibile accedere ai file delimitati direttamente da T-SQL in modo semplice e flessibile. Questo approccio consente di eseguire prima query sul posto senza richiedere il caricamento dei dati nelle tabelle di database.

Per eseguire query su un file delimitato, specificare l'URL del file e definire le opzioni di analisi appropriate quando si usa la OPENROWSET funzione :

SELECT * FROM
OPENROWSET( BULK '/Files/mysubfolder/data.csv');

La OPENROWSET funzione restituisce ogni riga dal file delimitato come riga nel set di risultati. Le colonne nel set di risultati seguono la struttura del file delimitato.

È possibile personalizzare la modalità di analisi dei file di testo delimitati(ad esempio CSV, TSV o altre varianti) specificando opzioni come caratteri di terminazione dei campi, caratteri di terminazione delle righe, caratteri di escape e altre impostazioni relative al formato in modo che corrispondano alla struttura del file.

Per esempi di utilizzo, vedere Eseguire query su file di testo delimitati.

Eseguire query su file JSONL con OPENROWSET

Json Lines (JSONL) è un formato di file semistrutturato delimitato da righe in cui ogni riga contiene un oggetto JSON valido. Questa struttura rende JSONL particolarmente adatto per lo streaming, i dati degli eventi e i carichi di lavoro di sola aggiunta, in quanto i nuovi record possono essere scritti in modo efficiente senza riscrivere l'intero file.

Usando la OPENROWSET funzione , è possibile eseguire query sui file JSONL direttamente da T-SQL. È possibile analizzare i dati in streaming e generati continuamente, senza richiedere l'inserimento precedente nelle tabelle di database.

Per eseguire query su un file JSONL, specificare l'URL del file quando si usa la OPENROWSET funzione :

SELECT * FROM
OPENROWSET( BULK '/mysubfolder/data.jsonl', DATA_SOURCE='MyStorage');

Quando si eseguono query su file JSON Lines (JSONL), ogni oggetto JSON nel file viene considerato come una riga separata nel set di risultati.

Ogni proprietà all'interno dell'oggetto JSON viene restituita come singola colonna, consentendo una visualizzazione relazionale naturale dei dati JSON delimitati da righe.

Per esempi di utilizzo, vedere Eseguire query su file JSONL.

Schema al momento della lettura

Fabric Data Warehouse consente agli sviluppatori SQL di applicare uno schema in fase di query durante la lettura dei dati direttamente dai file archiviati in un data lake.

Questo approccio basato su schema in lettura consente ai dati di rimanere nel formato originale mentre la relativa struttura viene definita in modo dinamico durante l'esecuzione di query. È possibile scegliere tra due modelli di schema-on-read:

  • Inferenza automatica dello schema, in cui Fabric analizza il contenuto del file e determina automaticamente i nomi di colonna e i tipi di dati.
  • Definizione dello schema esplicita, in cui lo schema è completamente definito nella query per controllare i nomi delle colonne e i tipi di dati.

Inferenza automatica dello schema

L'inferenza automatica dello schema consente di eseguire query sui file senza fornire una definizione di schema esplicita.

Fabric Data Warehouse e l'endpoint di analisi SQL esaminano automaticamente i file di origine per identificare i nomi di colonna e i tipi di dati. Usano metadati a livello di file, ad esempio intestazioni di file Parquet, o analizzano esempi di dati rappresentativi per formati come CSV e JSONL.

Quando si omette la clausola WITH dall'istruzione OPENROWSET, Fabric Data Warehouse analizza automaticamente i file sottostanti e deriva i nomi di colonna e i tipi di dati al momento della query.

SELECT * FROM
OPENROWSET( BULK 'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder/data.parquet');

Questo approccio è particolarmente utile per l'esplorazione rapida, l'evoluzione di schemi o scenari in cui la struttura di file viene gestita all'esterno del data warehouse. Dedurndo lo schema in modo dinamico, è possibile concentrarsi sull'esecuzione di query sui dati senza prima definire o mantenere uno schema fisso.

Definizione esplicita dello schema

Con una definizione di schema esplicita, gli sviluppatori SQL controllano la modalità di mapping dei dati dei file alle colonne relazionali specificando nomi di colonna, tipi di dati e, se applicabile, posizioni di colonna all'interno dei file di origine.

Questo approccio fornisce mapping preciso e prevedibile durante l'esecuzione di query sui file tramite OPENROWSET.

Per definire lo schema, aggiungere una clausola facoltativa WITH all'istruzione OPENROWSET .

SELECT * FROM
OPENROWSET( BULK '/Files/mysubfolder/data.parquet') 
WITH (
      Column1 int, 
      Column2 varchar(20),
      Column3 varchar(max)
);

Usa uno schema definito esplicitamente quando è necessario un controllo rigoroso sul mapping delle colonne e sui tipi di dati, oppure quando si lavora con file di origine in evoluzione o con struttura debole.

Eseguire query su più file o cartelle con OPENROWSET

Per eseguire query sui dati in più file o cartelle, specificare un percorso di file che includa uno o più caratteri jolly (*).

Usando caratteri jolly, una singola query T-SQL può operare su un set dinamico di file che corrispondano a un modello di denominazione o di directory.

Quando si usano caratteri jolly nei percorsi di file, si applicano le regole seguenti:

  • Il * carattere jolly (wildcard) rappresenta uno o più caratteri ed è possibile usarlo nei percorsi di directory e nei nomi di file.
  • È possibile specificare più * caratteri jolly all'interno dello stesso percorso o nome file in modo che corrispondano a modelli complessi.
  • Quando il percorso termina con caratteri jolly ricorsivi (ad esempio, /**), l'insieme di risultati della query include tutti i file che si trovano nella cartella radice specificata e nelle relative sottocartelle.

L'esempio seguente illustra come usare i modelli con caratteri jolly nel percorso del file per eseguire query su più file Parquet tra le cartelle in una singola query:

SELECT * FROM
OPENROWSET( BULK '/myroot/*/mysubfolder/*.parquet', DATA_SOURCE='MyStorage');

Funzioni per i metadati dei file

OPENROWSET include funzioni di metadati che è possibile usare per accedere alle informazioni sui file su cui si esegue una query:

  • La filename() funzione restituisce il nome del file da cui ha origine ogni riga. Usare questa funzione per filtrare o eseguire query su file specifici. Per prestazioni migliori, convertire il risultato in un tipo di dati e una lunghezza appropriati.
  • La filepath() funzione restituisce il percorso del file da cui ha origine ogni riga.
    • Senza parametri, restituisce il percorso/URI completo del file.
    • Con un parametro, restituisce il segmento di percorso che corrisponde alla posizione del carattere jolly specificata.

Nell'esempio seguente viene illustrata una query che recupera il contenuto del file insieme all'URI completo e al nome file per ogni file, restituendo solo i file dalla /year=2025/month=10 cartella.

SELECT 
 rows.filepath(),
 rows.filename(),
 rows.filepath(2) AS [month],
 rows.*
FROM 
OPENROWSET(
  BULK 'https://myaccount.dfs.core.windows.net/myroot/year=2025/month=*/*.parquet'
  ) AS rows
WHERE rows.filepath(1) = '2025'

Per altre informazioni su filepath() e filename(), vedere Funzioni di metadati dei file.

Contenuti correlati

  • Last updated on