Esercizio - CREATE EXTERNAL TABLE AS SELECT

10 minuti

In questo esercizio si usa CREATE EXTERNAL TABLE AS SELECT (CETAS) per:

Esportare una tabella come Parquet.
Spostare i dati ad accesso saltuario da un database all'archiviazione.
Creare una tabella esterna per accedere ai dati esterni esportati.
Usare le viste o la ricerca con caratteri jolly come strategie di query.
Limitare le query usando l'eliminazione delle cartelle e le informazioni sui metadati per migliorare le prestazioni.

Prerequisiti

Un'istanza di SQL Server 2025 con connettività Internet e la funzionalità PolyBase Query Service for External Data installata e abilitata come per gli esercizi precedenti.
Il database di esempio AdventureWorks2022 è stato ripristinato sul server per essere utilizzato come dati di esempio.
Un account di archiviazione di Azure con un contenitore di archiviazione BLOB denominato data creato. Per creare l'archiviazione, vedere Avvio rapido: Caricare, scaricare ed elencare BLOB con il portale di Azure.
Il ruolo Controllo degli accessi in base al ruolo di Azure Collaboratore ai dati dei BLOB di archiviazione assegnato in Azure. Per altre informazioni, vedere Assegnare un ruolo di Azure per l'accesso ai dati BLOB.
Token di firma di accesso condiviso del contenitore BLOB con LETTURA, SCRITTURA, ELENCHIe autorizzazioni CREAZIONE da usare per CETAS. Per creare il token SAS, consultare Create shared access signature (SAS) tokens for your storage containers.

Usare CETAS per esportare una tabella nel formato Parquet

Si supponga di lavorare con un team di analisi aziendale che vuole esportare i dati precedenti al 2012 da una tabella di SQL Server a un contenitore di Archiviazione BLOB di Azure. Vogliono eseguire query di report su questi dati esportati anziché eseguire query dirette su SQL Server.

Abilitare CETAS nell'istanza di SQL Server.

EXEC SP_CONFIGURE @CONFIGNAME = 'ALLOW POLYBASE EXPORT', @CONFIGVALUE = 1;

Screenshot della configurazione dell'opzione per consentire l'esportazione di PolyBase.

Eseguire la query di esplorazione dei dati seguente per comprendere i dati da esportare. In questo caso, si stanno cercando dati che provengono dal 2012 o versioni precedenti. Si vogliono esportare tutti i dati dal 2011 al 2012.
```
-- RECORDS BY YEARS
SELECT COUNT(*) AS QTY, DATEPART(YYYY, [DUEDATE]) AS [YEAR]
FROM  [PURCHASING].[PURCHASEORDERDETAIL] 
GROUP BY DATEPART(YYYY, [DUEDATE])
ORDER BY [YEAR]
```

Creare una chiave master del database per il database, come negli esercizi precedenti.

Use AdventureWorks2022

DECLARE @randomWord VARCHAR(64) = NEWID();
DECLARE @createMasterKey NVARCHAR(500) = N'
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE name = ''##MS_DatabaseMasterKey##'')
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = '  + QUOTENAME(@randomWord, '''')
EXEC sp_executesql @createMasterKey;

SELECT * FROM sys.symmetric_keys;

Creare le credenziali con ambito database e l'origine dati esterna. Sostituire i segnaposto <sas_token> e <storageccount> con l'account di archiviazione e il token di firma di accesso condiviso creati in Azure.

-- DATABASE SCOPED CREDENTIAL
CREATE DATABASE SCOPED CREDENTIAL blob_storage
      WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
      SECRET = '<sas_token>';

-- AZURE BLOB STORAGE DATA SOURCE
CREATE EXTERNAL DATA SOURCE ABS_Data
WITH
(
 LOCATION = 'abs://<storageaccount>.blob.core.windows.net/data/chapter3'
,CREDENTIAL = blob_storage
);

Creare un formato di file esterno per Parquet.

-- PARQUET FILE FORMAT
CREATE EXTERNAL FILE FORMAT ffParquet
WITH (FORMAT_TYPE = PARQUET);

Creare la tabella esterna usando CETAS. La query seguente crea una tabella esterna denominata ext_data_2011_2012 ed esporta tutti i dati dal 2011 al 2012 nel percorso specificato dall'origine dati ABS_Data.

CREATE EXTERNAL TABLE ex_data_2011_2012
WITH(
        LOCATION = 'data_2011_20122',
        DATA_SOURCE = ABS_Data,
        FILE_FORMAT = ffParquet
)AS
SELECT
	[PurchaseOrderID]
      ,[PurchaseOrderDetailID]
      ,[DueDate]
      ,[OrderQty]
      ,[ProductID]
      ,[UnitPrice]
      ,[LineTotal]
      ,[ReceivedQty]
      ,[RejectedQty]
      ,[StockedQty]
      ,[ModifiedDate]
FROM  [PURCHASING].[PURCHASEORDERDETAIL] 
WHERE YEAR([DUEDATE]) < 2013
GO

Controlla Azure Blob Storage nel portale di Azure. Verrà visualizzata la struttura seguente creata. SQL Server 2025 crea automaticamente il nome file in base alla quantità di dati esportata e al formato di file.
È ora possibile accedere alla tabella esterna, ad esempio una tabella normale.
```
SELECT * FROM ex_data_2011_2012
```

I dati vengono ora esportati in Parquet ed è facilmente accessibile tramite la tabella esterna. Il team di analisi aziendale può eseguire una query sulla tabella esterna o puntare lo strumento di creazione di report al file Parquet.

Usare CETAS per spostare i dati freddi dal database

Per mantenere i dati gestibili, l'azienda decide di spostare i dati precedenti al 2014 dal database di SQL Server. Tuttavia, tutti i dati devono essere ancora accessibili.

Per questo esempio, si esportano i dati tramite CETAS e si generano diverse tabelle esterne su cui è possibile eseguire query in un secondo momento. È possibile usare una vista con istruzioni UNION per eseguire query sui dati oppure creare una singola tabella esterna e usare un carattere jolly per eseguire ricerche nelle sottocartelle dei dati esportati.

Innanzitutto, clonare la tabella originale, perché si vuole simulare l'esportazione e la rimozione dei dati, ma non si vuole necessariamente eliminare l'origine dati corrente. Eseguire l'istruzione seguente:

-- CLONE TABLE
SELECT * INTO [PURCHASING].[PURCHASEORDERDETAIL_2] FROM [PURCHASING].[PURCHASEORDERDETAIL]

Dalla prima query di esplorazione dei dati risulta che ci sono 5551 record del 2014. Tutti gli elementi precedenti al 2014 devono essere esportati in una cartella identificata per anno. I dati del 2011 vengono inseriti in una cartella denominata 2011e così via.

Per creare le tabelle esterne, eseguire i comandi seguenti:

CREATE EXTERNAL TABLE ex_2011
WITH(
        LOCATION = '2011',
        DATA_SOURCE = ABS_Data,
        FILE_FORMAT = ffParquet
)AS
SELECT
    [PurchaseOrderID]
      ,[PurchaseOrderDetailID]
      ,[DueDate]
      ,[OrderQty]
      ,[ProductID]
      ,[UnitPrice]
      ,[LineTotal]
      ,[ReceivedQty]
      ,[RejectedQty]
      ,[StockedQty]
      ,[ModifiedDate]
FROM  [PURCHASING].[PURCHASEORDERDETAIL_2] 
WHERE YEAR([DUEDATE]) = 2011;

CREATE EXTERNAL TABLE ex_2012
WITH(
        LOCATION = '2012',
        DATA_SOURCE = ABS_Data,
        FILE_FORMAT = ffParquet
)AS
SELECT
    [PurchaseOrderID]
      ,[PurchaseOrderDetailID]
      ,[DueDate]
      ,[OrderQty]
      ,[ProductID]
      ,[UnitPrice]
      ,[LineTotal]
      ,[ReceivedQty]
      ,[RejectedQty]
      ,[StockedQty]
      ,[ModifiedDate]
FROM  [PURCHASING].[PURCHASEORDERDETAIL_2] 
WHERE YEAR([DUEDATE]) = 2012;

CREATE EXTERNAL TABLE ex_2013
WITH(
        LOCATION = '2013',
        DATA_SOURCE = ABS_Data,
        FILE_FORMAT = ffParquet
)AS
SELECT
    [PurchaseOrderID]
      ,[PurchaseOrderDetailID]
      ,[DueDate]
      ,[OrderQty]
      ,[ProductID]
      ,[UnitPrice]
      ,[LineTotal]
      ,[ReceivedQty]
      ,[RejectedQty]
      ,[StockedQty]
      ,[ModifiedDate]
FROM  [PURCHASING].[PURCHASEORDERDETAIL_2] 
WHERE YEAR([DUEDATE]) = 2013;

Dopo aver eseguito questi comandi, aggiornare Esplora oggetti di SSMS. Aprire quindi Database>AdventureWorks2022>Tabelle>Tabelle Esterne per visualizzare le tabelle esterne.
Verificare che le cartelle seguenti siano visualizzate nel contenitore di Archiviazione di Azure:
Dopo che i dati freddi sono stati esportati, è possibile eliminarli dalla posizione originale della tabella.
```
DELETE FROM [PURCHASING].[PURCHASEORDERDETAIL_2] 
WHERE YEAR([DUEDATE]) < 2014
```

Eseguire query sui dati che includono la tabella esterna

È possibile usare una vista o una ricerca con caratteri jolly per eseguire query sui dati esterni esportati. Ogni metodo presenta vantaggi e svantaggi. Il metodo di visualizzazione è consigliato per le richieste ripetitive perché in genere offre prestazioni migliori e può anche essere combinato con tabelle fisiche. Il metodo di ricerca con caratteri jolly è più flessibile e più facile da usare a scopo di esplorazione.

Usare una vista per interrogare i dati

Ora che i dati precedenti vengono esportati ed eliminati dal database, è possibile usare T-SQL per creare una vista che esegue query su tutte le tabelle esterne e i dati correnti nel database.

CREATE VIEW vw_purchaseorderdetail 
AS
SELECT * FROM ex_2011
UNION ALL
SELECT * FROM ex_2012
UNION ALL
SELECT * FROM ex_2013
UNION ALL
SELECT * FROM  [PURCHASING].[PURCHASEORDERDETAIL_2]

È possibile eseguire la query di esplorazione dei dati originale, questa volta usando la vista appena creata, per visualizzare gli stessi risultati.

SELECT  COUNT(*) AS QTY, DATEPART(YYYY, [DUEDATE]) AS [YEAR]
FROM vw_purchaseorderdetail 
GROUP BY DATEPART(YYYY, [DUEDATE])
ORDER BY [YEAR]

Usare una ricerca con caratteri jolly per eseguire una query sui dati

Nell'esempio precedente è stata usata una vista con istruzioni UNION per unire le tre tabelle esterne. Un altro modo per ottenere i risultati desiderati consiste nell'usare una ricerca con caratteri jolly per analizzare la struttura di cartelle, incluse le sottocartelle, per i dati di un particolare tipo.

Nell'esempio T-SQL seguente viene usato OPENROWSET per eseguire ricerche nell'origine dati ABS_Data, incluse le relative sottocartelle, per i file Parquet.

SELECT COUNT(*) AS QTY, DATEPART(YYYY, [DUEDATE]) AS [YEAR]
FROM OPENROWSET 
    (BULK '**'
    , FORMAT = 'PARQUET'
    , DATA_SOURCE = 'ABS_Data')
    AS [cc]
GROUP BY DATEPART(YYYY, [DUEDATE])
ORDER BY [YEAR]

Eliminazione di cartelle e informazioni sui metadati

Sia le tabelle esterne che OPENROWSET possono usare la filepath funzione per raccogliere e filtrare le informazioni in base ai metadati dei file. La filepath funzione restituisce percorsi completi, nomi di cartelle e nomi di file. È possibile usare tali informazioni per migliorare le funzionalità di ricerca sia della tabella esterna che dei comandi OPENROWSET.

SELECT
    r.filepath(1) 'folder_name'
    ,r.filepath() 'full_path'
    ,r.filepath(2) 'file_name'
FROM OPENROWSET(
        BULK '*/*.parquet',
        DATA_SOURCE = 'ABS_Data',
        FORMAT = 'parquet'
    ) as [r]
GROUP BY
    r.filepath(2),r.filepath(1), r.filepath()
ORDER BY 
    r.filepath(2)

Screenshot di SSMS che mostra la funzione filepath.

Se si desidera recuperare dati da una cartella specifica e usare comunque la funzionalità del metodo di ricerca con caratteri jolly, è possibile usare la query seguente:

SELECT  *
FROM OPENROWSET(
 BULK '*/*.parquet',
DATA_SOURCE = 'ABS_Data',
FORMAT = 'parquet'
 ) AS r
WHERE
 r.filepath(1) IN ('2011')

I risultati finali sono gli stessi, ma usando i metadati di eliminazione delle cartelle, la query accede solo alle cartelle necessarie anziché analizzare l'intera origine dati, producendo prestazioni di query migliori. Tenere presenti queste informazioni quando si progettano architetture di archiviazione per usare meglio le funzionalità di PolyBase.

Ad esempio, data l'architettura di cartella seguente:

Screenshot che mostra un esempio di architettura di cartelle in un contenitore di archiviazione.

È possibile usare la query seguente:

SELECT  *
FROM OPENROWSET(
 BULK 'year=*/month=*/*.parquet',
DATA_SOURCE = 'ABS_Data',
FORMAT = 'parquet'
 ) AS r
WHERE
 r.filepath(1) IN ('<year>')
 r.filepath(2) IN ('<month>')

Ai fini di questa query, non è importante quanto cresce l'origine dati. SQL Server carica, legge ed esegue query solo sui dati della cartella selezionata, ignorando tutti gli altri.

Poiché non vengono archiviati dati nel database, l'amministratore del database non deve progettare una strategia specifica per gestire questi dati. L'azienda deve comunque adottare tutte le precauzioni necessarie per mantenere i dati in modo sicuro, inclusi i backup, la disponibilità e le autorizzazioni.

Riassunto

In questo esercizio, hai usato CETAS per spostare dati freddi da un database in Azure Storage ed esportare una tabella nel formato file Parquet. Si sono appresi modi per eseguire query sui dati esterni per l'esplorazione e ottimizzare le prestazioni.

È possibile usare CETAS per combinare le funzioni OPENROWSET, tabelle esterne, viste, ricerca con caratteri jolly e percorso file. È possibile accedere ed esportare dati da altri database come SQL Server, Oracle, Teradata e MongoDB oppure da Archiviazione BLOB di Azure, Azure Data Lake Storage o qualsiasi archiviazione di oggetti compatibile con S3. CETAS consente di progettare soluzioni efficienti, durevoli e scalabili in tutte le origini dati supportate da PolyBase.

Commenti e suggerimenti

Questa pagina è stata utile?