Caricare dati dall’archiviazione BLOB di Azure in un SQL Data Warehouse (PolyBase)Load data from Azure blob storage into SQL Data Warehouse (PolyBase)

Usare i comandi PolyBase e T-SQL per caricare i dati dall'archivio BLOB di Azure in un Data Warehouse di Azure SQL.Use PolyBase and T-SQL commands to load data from Azure blob storage into Azure SQL Data Warehouse.

In parole brevi, in questa esercitazione vengono caricate due tabelle da un BLOB di Archiviazione di Azure pubblico nello schema del Data Warehouse di Contoso Retail.To keep it simple, this tutorial loads two tables from a public Azure Storage Blob into the Contoso Retail Data Warehouse schema. Per caricare il set di dati completo, eseguire l'esempio per caricare l'intero data warehouse di Contoso Retail dal repository di esempi di Microsoft SQL Server.To load the full data set, run the example Load the full Contoso Retail Data Warehouse from the Microsoft SQL Server Samples repository.

In questa esercitazione si apprenderà come:In this tutorial you will:

  1. Configurare PolyBase per caricare dall'archiviazione BLOB di AzureConfigure PolyBase to load from Azure blob storage
  2. Caricare dati pubblici nel databaseLoad public data into your database
  3. Una volta completato il caricamento, effettuare le ottimizzazioni.Perform optimizations after the load is finished.

Prima di iniziareBefore you begin

Per eseguire questa esercitazione, è necessario un account di Azure che già dispone di un database SQL Data Warehouse.To run this tutorial, you need an Azure account that already has a SQL Data Warehouse database. In caso contrario, vedere l'articolo su come creare un'istanza di SQL Data Warehouse.If you don't already have this, see Create a SQL Data Warehouse.

1. Configurare l'origine dati1. Configure the data source

PolyBase utilizza oggetti esterni T-SQL per definire il percorso e gli attributi dei dati esterni.PolyBase uses T-SQL external objects to define the location and attributes of the external data. Le definizioni degli oggetti esterni vengono archiviate in SQL Data Warehouse.The external object definitions are stored in SQL Data Warehouse. I dati vengano archiviati esternamente.The data itself is stored externally.

1.1.1.1. Creare una credenzialeCreate a credential

Ignorare questo passaggio se si desidera caricare i dati pubblici di Contoso.Skip this step if you are loading the Contoso public data. Poiché sono già accessibili a tutti, non è necessario un accesso sicuro ai dati pubblici.You don't need secure access to the public data since it is already accessible to anyone.

Non ignorare questo passaggio se si utilizza questa esercitazione come modello per il caricamento dei dati personali.Don't skip this step if you are using this tutorial as a template for loading your own data. Per accedere ai dati tramite una credenziale, utilizzare lo script seguente per creare una credenziale con ambito di database, quindi utilizzarla quando si definisce il percorso dell'origine dati.To access data through a credential, use the following script to create a database-scoped credential, and then use it when defining the location of the data source.

-- A: Create a master key.
-- Only necessary if one does not already exist.
-- Required to encrypt the credential secret in the next step.

CREATE MASTER KEY;


-- B: Create a database scoped credential
-- IDENTITY: Provide any string, it is not used for authentication to Azure storage.
-- SECRET: Provide your Azure storage account key.


CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredential
WITH
    IDENTITY = 'user',
    SECRET = '<azure_storage_account_key>'
;


-- C: Create an external data source
-- TYPE: HADOOP - PolyBase uses Hadoop APIs to access data in Azure blob storage.
-- LOCATION: Provide Azure storage account name and blob container name.
-- CREDENTIAL: Provide the credential created in the previous step.

CREATE EXTERNAL DATA SOURCE AzureStorage
WITH (
    TYPE = HADOOP,
    LOCATION = 'wasbs://<blob_container_name>@<azure_storage_account_name>.blob.core.windows.net',
    CREDENTIAL = AzureStorageCredential
);

Andare al passaggio 2.Skip to step 2.

1.2.1.2. Creare un'origine dati esterna.Create the external data source

Usare questo comando CREATE EXTERNAL DATA SOURCE per archiviare il percorso e il tipo di dati.Use this CREATE EXTERNAL DATA SOURCE command to store the location of the data, and the type of data.

CREATE EXTERNAL DATA SOURCE AzureStorage_west_public
WITH 
(  
    TYPE = Hadoop 
,   LOCATION = 'wasbs://contosoretaildw-tables@contosoretaildw.blob.core.windows.net/'
); 

Importante

Se si sceglie di rendere pubblici i contenitori di archiviazione BLOB di Azure, tenere presente che i costi per l’uscita dei dati dal data center verranno addebitati al proprietario dei dati.If you choose to make your azure blob storage containers public, remember that as the data owner you will be charged for data egress charges when data leaves the data center.

2. Configurare il formato dei dati2. Configure data format

I dati vengono archiviati in file di testo nell'archiviazione BLOB di Azure e ogni campo è separato con un delimitatore.The data is stored in text files in Azure blob storage, and each field is separated with a delimiter. Eseguire questo comando CREATE EXTERNAL FILE FORMAT per specificare il formato dei dati nei file di testo.Run this CREATE EXTERNAL FILE FORMAT command to specify the format of the data in the text files. I dati di Contoso sono delimitati da barre verticali e non sono compressi.The Contoso data is uncompressed and pipe delimited.

CREATE EXTERNAL FILE FORMAT TextFileFormat 
WITH 
(   FORMAT_TYPE = DELIMITEDTEXT
,    FORMAT_OPTIONS    (   FIELD_TERMINATOR = '|'
                    ,    STRING_DELIMITER = ''
                    ,    DATE_FORMAT         = 'yyyy-MM-dd HH:mm:ss.fff'
                    ,    USE_TYPE_DEFAULT = FALSE 
                    )
);

3. Creare le tabelle esterne.3. Create the external tables

Ora che sono stati specificati l’origine dei dati e il formato dei file, si è pronti per creare le tabelle esterne.Now that you have specified the data source and file format, you are ready to create the external tables.

3.1.3.1. Creare uno schema per i dati.Create a schema for the data.

Per creare un percorso in cui archiviare i dati di Contoso nel database, creare uno schema.To create a place to store the Contoso data in your database, create a schema.

CREATE SCHEMA [asb]
GO

3.2.3.2. Creare le tabelle esterne.Create the external tables.

Eseguire questo script per creare le tabelle esterne DimProduct e FactOnlineSales.Run this script to create the DimProduct and FactOnlineSales external tables. Tutto quello che stiamo facendo qui è definire i nomi delle colonne e i tipi di dati e associarli al percorso e al formato dei file dell’archiviazione BLOB di Azure.All we are doing here is defining column names and data types, and binding them to the location and format of the Azure blob storage files. La definizione viene archiviata in SQL Data Warehouse e i dati sono ancora nel BLOB di Archiviazione di Azure.The definition is stored in SQL Data Warehouse and the data is still in the Azure Storage Blob.

Il parametro LOCATION corrisponde alla cartella sotto la cartella radice nel BLOB di Archiviazione di Azure.The LOCATION parameter is the folder under the root folder in the Azure Storage Blob. Ogni tabella è in una cartella diversa.Each table is in a different folder.


--DimProduct
CREATE EXTERNAL TABLE [asb].DimProduct (
    [ProductKey] [int] NOT NULL,
    [ProductLabel] [nvarchar](255) NULL,
    [ProductName] [nvarchar](500) NULL,
    [ProductDescription] [nvarchar](400) NULL,
    [ProductSubcategoryKey] [int] NULL,
    [Manufacturer] [nvarchar](50) NULL,
    [BrandName] [nvarchar](50) NULL,
    [ClassID] [nvarchar](10) NULL,
    [ClassName] [nvarchar](20) NULL,
    [StyleID] [nvarchar](10) NULL,
    [StyleName] [nvarchar](20) NULL,
    [ColorID] [nvarchar](10) NULL,
    [ColorName] [nvarchar](20) NOT NULL,
    [Size] [nvarchar](50) NULL,
    [SizeRange] [nvarchar](50) NULL,
    [SizeUnitMeasureID] [nvarchar](20) NULL,
    [Weight] [float] NULL,
    [WeightUnitMeasureID] [nvarchar](20) NULL,
    [UnitOfMeasureID] [nvarchar](10) NULL,
    [UnitOfMeasureName] [nvarchar](40) NULL,
    [StockTypeID] [nvarchar](10) NULL,
    [StockTypeName] [nvarchar](40) NULL,
    [UnitCost] [money] NULL,
    [UnitPrice] [money] NULL,
    [AvailableForSaleDate] [datetime] NULL,
    [StopSaleDate] [datetime] NULL,
    [Status] [nvarchar](7) NULL,
    [ImageURL] [nvarchar](150) NULL,
    [ProductURL] [nvarchar](150) NULL,
    [ETLLoadID] [int] NULL,
    [LoadDate] [datetime] NULL,
    [UpdateDate] [datetime] NULL
)
WITH
(
    LOCATION='/DimProduct/' 
,   DATA_SOURCE = AzureStorage_west_public
,   FILE_FORMAT = TextFileFormat
,   REJECT_TYPE = VALUE
,   REJECT_VALUE = 0
)
;

--FactOnlineSales
CREATE EXTERNAL TABLE [asb].FactOnlineSales 
(
    [OnlineSalesKey] [int]  NOT NULL,
    [DateKey] [datetime] NOT NULL,
    [StoreKey] [int] NOT NULL,
    [ProductKey] [int] NOT NULL,
    [PromotionKey] [int] NOT NULL,
    [CurrencyKey] [int] NOT NULL,
    [CustomerKey] [int] NOT NULL,
    [SalesOrderNumber] [nvarchar](20) NOT NULL,
    [SalesOrderLineNumber] [int] NULL,
    [SalesQuantity] [int] NOT NULL,
    [SalesAmount] [money] NOT NULL,
    [ReturnQuantity] [int] NOT NULL,
    [ReturnAmount] [money] NULL,
    [DiscountQuantity] [int] NULL,
    [DiscountAmount] [money] NULL,
    [TotalCost] [money] NOT NULL,
    [UnitCost] [money] NULL,
    [UnitPrice] [money] NULL,
    [ETLLoadID] [int] NULL,
    [LoadDate] [datetime] NULL,
    [UpdateDate] [datetime] NULL
)
WITH
(
    LOCATION='/FactOnlineSales/' 
,   DATA_SOURCE = AzureStorage_west_public
,   FILE_FORMAT = TextFileFormat
,   REJECT_TYPE = VALUE
,   REJECT_VALUE = 0
)
;

4. Caricare i dati4. Load the data

È possibile accedere ai dati esterni in diversi modi.There's different ways to access external data. Si può eseguire una query sui dati direttamente dalla tabella esterna, caricare i dati in nuove tabelle di database o aggiungere dati esterni alle tabelle dei database esistenti.You can query data directly from the external table, load the data into new database tables, or add external data to existing database tables.

4.1.4.1. Crea un nuovo schemaCreate a new schema

CTAS crea una nuova tabella contenente i dati.CTAS creates a new table that contains data. Innanzitutto, creare uno schema per i dati di Contoso.First, create a schema for the contoso data.

CREATE SCHEMA [cso]
GO

4.2.4.2. Caricare i dati in nuove tabelleLoad the data into new tables

Per caricare i dati dall'archivio BLOB di Azure e salvarli in una tabella all'interno del database, usare l'istruzione CREATE TABLE AS SELECT (Transact-SQL).To load data from Azure blob storage and save it in a table inside of your database, use the CREATE TABLE AS SELECT (Transact-SQL) statement. Il caricamento con CTAS sfrutta le tabelle esterne fortemente tipizzate appena create. Per caricare i dati in nuove tabelle, usare un'istruzione CTAS per ogni tabella.Loading with CTAS leverages the strongly typed external tables you have just created.To load the data into new tables, use one CTAS statement per table.

CTAS crea una nuova tabella e la popola con i risultati di un'istruzione SELECT.CTAS creates a new table and populates it with the results of a select statement. CTAS definisce la nuova tabella in modo che abbia le stesse colonne e gli stessi tipi di dati dei risultati dell'istruzione SELECT.CTAS defines the new table to have the same columns and data types as the results of the select statement. Se si selezionano tutte le colonne da una tabella esterna, la nuova tabella sarà una replica delle colonne e dei tipi di dati della tabella esterna.If you select all the columns from an external table, the new table will be a replica of the columns and data types in the external table.

In questo esempio, creiamo sia la dimensione sia la tabella dei fatti come hash di tabelle distribuite.In this example, we create both the dimension and the fact table as hash distributed tables.

SELECT GETDATE();
GO

CREATE TABLE [cso].[DimProduct]            WITH (DISTRIBUTION = HASH([ProductKey]  ) ) AS SELECT * FROM [asb].[DimProduct]             OPTION (LABEL = 'CTAS : Load [cso].[DimProduct]             ');
CREATE TABLE [cso].[FactOnlineSales]       WITH (DISTRIBUTION = HASH([ProductKey]  ) ) AS SELECT * FROM [asb].[FactOnlineSales]        OPTION (LABEL = 'CTAS : Load [cso].[FactOnlineSales]        ');

4.3. Monitorare l'avanzamento del caricamento4.3 Track the load progress

È possibile monitorare l'avanzamento del caricamento con le viste a gestione dinamica (DMV).You can track the progress of your load using dynamic management views (DMVs).

-- To see all requests
SELECT * FROM sys.dm_pdw_exec_requests;

-- To see a particular request identified by its label
SELECT * FROM sys.dm_pdw_exec_requests as r
WHERE r.[label] = 'CTAS : Load [cso].[DimProduct]             '
      OR r.[label] = 'CTAS : Load [cso].[FactOnlineSales]        '
;

-- To track bytes and files
SELECT
    r.command,
    s.request_id,
    r.status,
    count(distinct input_name) as nbr_files, 
    sum(s.bytes_processed)/1024/1024/1024 as gb_processed
FROM
    sys.dm_pdw_exec_requests r
    inner join sys.dm_pdw_dms_external_work s
        on r.request_id = s.request_id
WHERE 
    r.[label] = 'CTAS : Load [cso].[DimProduct]             '
    OR r.[label] = 'CTAS : Load [cso].[FactOnlineSales]        '
GROUP BY
    r.command,
    s.request_id,
    r.status
ORDER BY
    nbr_files desc,
    gb_processed desc;

5. Ottimizzare la compressione columnstore5. Optimize columnstore compression

Per impostazione predefinita, SQL Data Warehouse archivia le tabelle come un indice columnstore cluster.By default, SQL Data Warehouse stores the table as a clustered columnstore index. Al termine di un caricamento, alcune delle righe di dati potrebbero non essere compresse nel columnstore.After a load completes, some of the data rows might not be compressed into the columnstore. Esiste una serie di motivi per cui questo può verificarsi.There's a variety of reasons why this can happen. Per altre informazioni, vedere l'articolo su come gestire gli indici columnstore.To learn more, see manage columnstore indexes.

Per ottimizzare le prestazioni delle query e la compressione columnstore dopo un'operazione di caricamento, ricompilare la tabella per forzare l'indice columnstore per comprimere tutte le righe.To optimize query performance and columnstore compression after a load, rebuild the table to force the columnstore index to compress all the rows.

SELECT GETDATE();
GO

ALTER INDEX ALL ON [cso].[DimProduct]               REBUILD;
ALTER INDEX ALL ON [cso].[FactOnlineSales]          REBUILD;

Per altre informazioni sulla gestione di tali indici, vedere l'articolo su come gestire gli indici columnstore.For more information on maintaining columnstore indexes, see the manage columnstore indexes article.

6. Ottimizzare le statistiche6. Optimize statistics

È consigliabile creare statistiche a colonna singola subito dopo un caricamento.It is best to create single-column statistics immediately after a load. Sono disponibili alcune opzioni per le statistiche.There are some choices for statistics. Ad esempio, se si creano statistiche a colonna singola su ogni colonna, ricompilare tutte le statistiche potrebbe richiedere molto tempo.For example, if you create single-column statistics on every column it might take a long time to rebuild all the statistics. Se si sa che alcune colonne non si aggiungeranno ai predicati di query, è possibile ignorare la creazione delle statistiche su tali colonne.If you know certain columns are not going to be in query predicates, you can skip creating statistics on those columns.

Per creare statistiche a colonna singola su ogni colonna di ogni tabella, è possibile usare l'esempio di codice di stored procedure prc_sqldw_create_stats riportato nell'articolo relativo alle statistiche.If you decide to create single-column statistics on every column of every table, you can use the stored procedure code sample prc_sqldw_create_stats in the statistics article.

L'esempio seguente è un buon punto di partenza per la creazione delle statistiche.The following example is a good starting point for creating statistics. Qui vengono create statistiche a colonna singola su ogni colonna nella tabella della dimensione e su ogni colonna di join nelle tabelle dei fatti.It creates single-column statistics on each column in the dimension table, and on each joining column in the fact tables. È sempre possibile aggiungere in un secondo momento statistiche a colonna singola o a più colonne per altre colonne delle tabelle dei fatti.You can always add single or multi-column statistics to other fact table columns later on.

CREATE STATISTICS [stat_cso_DimProduct_AvailableForSaleDate] ON [cso].[DimProduct]([AvailableForSaleDate]);
CREATE STATISTICS [stat_cso_DimProduct_BrandName] ON [cso].[DimProduct]([BrandName]);
CREATE STATISTICS [stat_cso_DimProduct_ClassID] ON [cso].[DimProduct]([ClassID]);
CREATE STATISTICS [stat_cso_DimProduct_ClassName] ON [cso].[DimProduct]([ClassName]);
CREATE STATISTICS [stat_cso_DimProduct_ColorID] ON [cso].[DimProduct]([ColorID]);
CREATE STATISTICS [stat_cso_DimProduct_ColorName] ON [cso].[DimProduct]([ColorName]);
CREATE STATISTICS [stat_cso_DimProduct_ETLLoadID] ON [cso].[DimProduct]([ETLLoadID]);
CREATE STATISTICS [stat_cso_DimProduct_ImageURL] ON [cso].[DimProduct]([ImageURL]);
CREATE STATISTICS [stat_cso_DimProduct_LoadDate] ON [cso].[DimProduct]([LoadDate]);
CREATE STATISTICS [stat_cso_DimProduct_Manufacturer] ON [cso].[DimProduct]([Manufacturer]);
CREATE STATISTICS [stat_cso_DimProduct_ProductDescription] ON [cso].[DimProduct]([ProductDescription]);
CREATE STATISTICS [stat_cso_DimProduct_ProductKey] ON [cso].[DimProduct]([ProductKey]);
CREATE STATISTICS [stat_cso_DimProduct_ProductLabel] ON [cso].[DimProduct]([ProductLabel]);
CREATE STATISTICS [stat_cso_DimProduct_ProductName] ON [cso].[DimProduct]([ProductName]);
CREATE STATISTICS [stat_cso_DimProduct_ProductSubcategoryKey] ON [cso].[DimProduct]([ProductSubcategoryKey]);
CREATE STATISTICS [stat_cso_DimProduct_ProductURL] ON [cso].[DimProduct]([ProductURL]);
CREATE STATISTICS [stat_cso_DimProduct_Size] ON [cso].[DimProduct]([Size]);
CREATE STATISTICS [stat_cso_DimProduct_SizeRange] ON [cso].[DimProduct]([SizeRange]);
CREATE STATISTICS [stat_cso_DimProduct_SizeUnitMeasureID] ON [cso].[DimProduct]([SizeUnitMeasureID]);
CREATE STATISTICS [stat_cso_DimProduct_Status] ON [cso].[DimProduct]([Status]);
CREATE STATISTICS [stat_cso_DimProduct_StockTypeID] ON [cso].[DimProduct]([StockTypeID]);
CREATE STATISTICS [stat_cso_DimProduct_StockTypeName] ON [cso].[DimProduct]([StockTypeName]);
CREATE STATISTICS [stat_cso_DimProduct_StopSaleDate] ON [cso].[DimProduct]([StopSaleDate]);
CREATE STATISTICS [stat_cso_DimProduct_StyleID] ON [cso].[DimProduct]([StyleID]);
CREATE STATISTICS [stat_cso_DimProduct_StyleName] ON [cso].[DimProduct]([StyleName]);
CREATE STATISTICS [stat_cso_DimProduct_UnitCost] ON [cso].[DimProduct]([UnitCost]);
CREATE STATISTICS [stat_cso_DimProduct_UnitOfMeasureID] ON [cso].[DimProduct]([UnitOfMeasureID]);
CREATE STATISTICS [stat_cso_DimProduct_UnitOfMeasureName] ON [cso].[DimProduct]([UnitOfMeasureName]);
CREATE STATISTICS [stat_cso_DimProduct_UnitPrice] ON [cso].[DimProduct]([UnitPrice]);
CREATE STATISTICS [stat_cso_DimProduct_UpdateDate] ON [cso].[DimProduct]([UpdateDate]);
CREATE STATISTICS [stat_cso_DimProduct_Weight] ON [cso].[DimProduct]([Weight]);
CREATE STATISTICS [stat_cso_DimProduct_WeightUnitMeasureID] ON [cso].[DimProduct]([WeightUnitMeasureID]);
CREATE STATISTICS [stat_cso_FactOnlineSales_CurrencyKey] ON [cso].[FactOnlineSales]([CurrencyKey]);
CREATE STATISTICS [stat_cso_FactOnlineSales_CustomerKey] ON [cso].[FactOnlineSales]([CustomerKey]);
CREATE STATISTICS [stat_cso_FactOnlineSales_DateKey] ON [cso].[FactOnlineSales]([DateKey]);
CREATE STATISTICS [stat_cso_FactOnlineSales_OnlineSalesKey] ON [cso].[FactOnlineSales]([OnlineSalesKey]);
CREATE STATISTICS [stat_cso_FactOnlineSales_ProductKey] ON [cso].[FactOnlineSales]([ProductKey]);
CREATE STATISTICS [stat_cso_FactOnlineSales_PromotionKey] ON [cso].[FactOnlineSales]([PromotionKey]);
CREATE STATISTICS [stat_cso_FactOnlineSales_StoreKey] ON [cso].[FactOnlineSales]([StoreKey]);

Obiettivo raggiuntoAchievement unlocked!

I dati pubblici sono stati caricati correttamente in Azure SQL Data Warehouse.You have successfully loaded public data into Azure SQL Data Warehouse. Ottimo lavoro.Great job!

Ora è possibile iniziare a eseguire le query sulle tabelle prendendo spunto dalle query seguenti:You can now start querying the tables using queries like the following:

SELECT  SUM(f.[SalesAmount]) AS [sales_by_brand_amount]
,       p.[BrandName]
FROM    [cso].[FactOnlineSales] AS f
JOIN    [cso].[DimProduct]      AS p ON f.[ProductKey] = p.[ProductKey]
GROUP BY p.[BrandName]

Passaggi successiviNext steps

Per caricare i dati completi del data warehouse di Contoso Retail, usare lo script disponibile nei suggerimenti per lo sviluppo e vedere la panoramica sullo sviluppo per SQL Data Warehouse.To load the full Contoso Retail Data Warehouse data, use the script in For more development tips, see SQL Data Warehouse development overview.