Caricare dati da Azure Data Lake Store a SQL Data WarehouseLoad data from Azure Data Lake Store into SQL Data Warehouse

Questo documento illustra tutti i passaggi necessari per caricare i dati da Azure Data Lake Store (ADLS) a SQL Data Warehouse usando PolyBase.This document gives you all steps you need to load your own data from Azure Data Lake Store (ADLS) into SQL Data Warehouse using PolyBase. Anche se è possibile eseguire query ad hoc sui dati archiviati in ADLS usando le tabelle esterne, è consigliabile importare i dati in SQL Data Warehouse.While you are able to run adhoc queries over the data stored in ADLS using the External Tables, as a best practice we suggest importing the data into the SQL Data Warehouse.

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

  1. Creare oggetti di database esterno da caricare da Azure Data Lake Store.Create External Database objects to load from Azure Data Lake Store.
  2. Connettersi a una directory di Azure Data Lake Store.Connect to an Azure Data Lake Store Directory.
  3. Caricare i dati in Azure SQL Data Warehouse.Load data into Azure SQL Data Warehouse.

Prima di iniziareBefore you begin

Per eseguire questa esercitazione è necessario:To run this tutorial, you need:

Nota

Sono necessari ID client, chiave e valore dell'endpoint di token OAuth 2.0 dell'applicazione Active Directory per la connessione ad Azure Data Lake da SQL Data Warehouse.You need the client ID, Key, and OAuth2.0 Token Endpoint Value of your Active Directory Application to connect to your Azure Data Lake from SQL Data Warehouse. I dettagli su come ottenere questi valori sono disponibili nel collegamento precedente.Details for how to get these values are in the link above. Per la registrazione di app di Azure Active Directory usare l'ID applicazione come ID client.Note for Azure Active Directory App Registration use the 'Application ID' as the Client ID.

Configurare l'origine datiConfigure 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. Gli oggetti esterni vengono archiviati in SQL Data Warehouse e fanno riferimento ai dati archiviati esternamente.The external objects are stored in SQL Data Warehouse and reference the data th is stored externally.

Creare una credenzialeCreate a credential

Per accedere ad Azure Data Lake Store è necessario creare una chiave master del database per crittografare il segreto delle credenziali usato nel passaggio successivo.To access your Azure Data Lake Store, you will need to create a Database Master Key to encrypt your credential secret used in the next step. È quindi possibile creare una credenziale con ambito database che archivia le credenziali dell'entità servizio impostate in AAD.You then create a Database scoped credential, which stores the service principal credentials set up in AAD. Per coloro che hanno usato PolyBase per connettersi ai BLOB del servizio di archiviazione di Azure, si noti che la sintassi delle credenziali è diversa.For those of you who have used PolyBase to connect to Windows Azure Storage Blobs, note that the credential syntax is different. Per connettersi ad Azure Data Lake Store, prima è necessario creare un'applicazione Azure Active Directory, creare una chiave di accesso e concedere all'applicazione l'accesso alla risorsa Azure Data Lake.To connect to Azure Data Lake Store, you must first create an Azure Active Directory Application, create an access key, and grant the application access to the Azure Data Lake resource. Le istruzioni per eseguire questi passaggi sono disponibili qui.Instructions to perform these steps are located here.

-- A: Create a Database Master Key.
-- Only necessary if one does not already exist.
-- Required to encrypt the credential secret in the next step.
-- For more information on Master Key: https://msdn.microsoft.com/en-us/library/ms174382.aspx?f=255&MSPPError=-2147217396

CREATE MASTER KEY;


-- B: Create a database scoped credential
-- IDENTITY: Pass the client id and OAuth 2.0 Token Endpoint taken from your Azure Active Directory Application
-- SECRET: Provide your AAD Application Service Principal key.
-- For more information on Create Database Scoped Credential: https://msdn.microsoft.com/en-us/library/mt270260.aspx

CREATE DATABASE SCOPED CREDENTIAL ADLCredential
WITH
    IDENTITY = '<client_id>@<OAuth_2.0_Token_EndPoint>',
    SECRET = '<key>'
;

-- It should look something like this:
CREATE DATABASE SCOPED CREDENTIAL ADLCredential
WITH
    IDENTITY = '536540b4-4239-45fe-b9a3-629f97591c0c@https://login.microsoftonline.com/42f988bf-85f1-41af-91ab-2d2cd011da47/oauth2/token',
    SECRET = 'BjdIlmtKp4Fpyh9hIvr8HJlUida/seM5kQ3EpLAmeDI='
;

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. Per trovare l'URI ADL nel portale di Azure, individuare l'archivio Azure Data Lake Store, quindi esaminare il pannello Essentials.To find the ADL URI in the Azure portal, navigate to your Azure Data Lake Store, and then look at the Essentials panel.

-- C: Create an external data source
-- TYPE: HADOOP - PolyBase uses Hadoop APIs to access data in Azure Data Lake Store.
-- LOCATION: Provide Azure Data Lake accountname and URI
-- CREDENTIAL: Provide the credential created in the previous step.

CREATE EXTERNAL DATA SOURCE AzureDataLakeStore
WITH (
    TYPE = HADOOP,
    LOCATION = 'adl://<AzureDataLake account_name>.azuredatalakestore.net',
    CREDENTIAL = ADLCredential
);

Configurare il formato dei datiConfigure data format

Per importare i dati da ADLS è necessario specificare il formato file esterno.To import the data from ADLS, you need to specify the external file format. Questo comando ha opzioni specifiche del formato per descrivere i dati.This command has format-specific options to describe your data. Di seguito è riportato un esempio di formato file comunemente usato, ovvero un file di testo delimitato da barre verticali.Below is an example of a commonly used file format that is a pipe-delimited text file. Esaminare la documentazione di T-SQL per un elenco completo di CREATE EXTERNAL FILE FORMATLook at our T-SQL documentation for a complete list of CREATE EXTERNAL FILE FORMAT

-- D: Create an external file format
-- FIELD_TERMINATOR: Marks the end of each field (column) in a delimited text file
-- STRING_DELIMITER: Specifies the field terminator for data of type string in the text-delimited file.
-- DATE_FORMAT: Specifies a custom format for all date and time data that might appear in a delimited text file.
-- Use_Type_Default: Store all Missing values as NULL

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
                    )
);

Creare le tabelle esterne.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. Le tabelle esterne rappresentano la modalità di interazione con i dati esterni.External tables are how you interact with external data. PolyBase usa l'attraversamento ricorsivo delle directory per leggere tutti i file in tutte le sottodirectory della directory specificata nel parametro del percorso.PolyBase uses recursive directory traversal to read all files in all subdirectories of the directory specified in the location parameter. L'esempio seguente mostra come creare l'oggetto.Also, the following example shows how to create the object. È necessario personalizzare l'istruzione perché funzioni con i dati presenti in ADLS.You need to customize the statement to work with the data you have in ADLS.

-- D: Create an External Table
-- LOCATION: Folder under the ADLS root folder.
-- DATA_SOURCE: Specifies which Data Source Object to use.
-- FILE_FORMAT: Specifies which File Format Object to use
-- REJECT_TYPE: Specifies how you want to deal with rejected rows. Either Value or percentage of the total
-- REJECT_VALUE: Sets the Reject value based on the reject type.

-- DimProduct
CREATE EXTERNAL TABLE [dbo].[DimProduct_external] (
    [ProductKey] [int] NOT NULL,
    [ProductLabel] [nvarchar](255) NULL,
    [ProductName] [nvarchar](500) NULL
)
WITH
(
    LOCATION='/DimProduct/'
,   DATA_SOURCE = AzureDataLakeStore
,   FILE_FORMAT = TextFileFormat
,   REJECT_TYPE = VALUE
,   REJECT_VALUE = 0
)
;

Considerazioni sulle tabelle esterneExternal Table Considerations

La creazione di una tabella esterna è semplice, ma esistono alcuni aspetti da considerare.Creating an external table is easy, but there are some nuances that need to be discussed.

Il caricamento dei dati con PolyBase è fortemente tipizzato.Loading data with PolyBase is strongly typed. Ciò significa che ogni riga di dati inserita deve soddisfare la definizione dello schema tabella.This means that each row of the data being ingested must satisfy the table schema definition. Il caricamento di una riga non corrispondente alla definizione dello schema verrà rifiutato.If a given row does not match the schema definition, the row is rejected from the load.

Le opzioni REJECT_TYPE e REJECT_VALUE permettono di definire il numero di righe o la percentuale dei dati che dovranno essere presenti nella tabella finale.The REJECT_TYPE and REJECT_VALUE options allow you to define how many rows or what percentage of the data must be present in the final table. Se durante il caricamento viene raggiunto il valore rifiutato, il caricamento avrà esito negativo.During load, if the reject value is reached, the load fails. La causa più comune del rifiuto delle righe è una mancata corrispondenza con la definizione dello schema.The most common cause of rejected rows is a schema definition mismatch. Se ad esempio a una colonna viene erroneamente assegnato lo schema di int quando i dati nel file sono in formato stringa, il caricamento di tutte le righe avrà esito negativo.For example, if a column is incorrectly given the schema of int when the data in the file is a string, every row will fail to load.

Il percorso specifica la directory di livello più alto dalla quale leggere i dati.The Location specifies the topmost directory that you want to read data from. In questo caso, se sono presenti sottodirectory in /DimProduct/ PolyBase importerà tutti i dati all'interno delle sottodirectory.In this case, if there were subdirectories under /DimProduct/ PolyBase would import all the data within the subdirectories. Azure Data Lake Store usa il controllo degli accessi in base al ruolo per controllare l'accesso ai dati.Azure Data Lake store uses Role Based Access Control (RBAC) to control access to the data. Questo significa che l'entità servizio deve avere autorizzazioni di lettura per le directory definite nel parametro location e per i figli della directory e dei file finali.This means that the Service Principal must have read permissions to the directories defined in the location parameter and to the children of the final directory and files. Questo comportamento consente a PolyBase di autenticare, caricare e leggere i dati.This enables PolyBase to authenticate and load read that data.

Caricare i datiLoad the data

Per caricare i dati da Azure Data Lake Store usare l'istruzione CREATE TABLE AS SELECT (Transact-SQL).To load data from Azure Data Lake Store use the CREATE TABLE AS SELECT (Transact-SQL) statement. Il caricamento con CTAS usa la tabella esterna fortemente tipizzata creata.Loading with CTAS uses the strongly typed external table you have created.

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 is a replica of the columns and data types in the external table.

In questo esempio viene creata una tabella con distribuzione hash chiamata DimProduct dalla tabella esterna DimProduct_external.In this example, we are creating a hash distributed table called DimProduct from our External Table DimProduct_external.


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

Ottimizzare la compressione columnstoreOptimize 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.


ALTER INDEX ALL ON [dbo].[DimProduct] 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.

Ottimizzare le statisticheOptimize 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.

Obiettivo raggiuntoAchievement unlocked!

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

Passaggi successiviNext Steps

Il caricamento dei dati è il primo passaggio per lo sviluppo di una soluzione di data warehouse con SQL Data Warehouse.Loading data is the first step to developing a data warehouse solution using SQL Data Warehouse. Vedere le risorse di sviluppo in Tabelle e T-SQL.Check out our development resources on Tables and T-SQL.