Caricare dati da Azure Data Lake Store ad SQL Data WarehouseLoad data from Azure Data Lake Store to SQL Data Warehouse

Usare tabelle PolyBase esterne per caricare dati da Azure Data Lake Store ad Azure SQL Data Warehouse.Use PolyBase external tables to load data from Azure Data Lake Store into Azure SQL Data Warehouse. Sebbene sia possibile eseguire query ad hoc sui dati archiviati in ADLS, è consigliabile importare i dati in SQL Data Warehouse per prestazioni ottimali.Although you can run adhoc queries on data stored in ADLS, we recommend importing the data into the SQL Data Warehouse for best performance.

  • Creare gli oggetti di database necessari da caricare da Azure Data Lake Store.Create database objects required to load from Azure Data Lake Store.
  • Connettersi a una directory di Azure Data Lake Store.Connect to an Azure Data Lake Store Directory.
  • Caricare i dati in Azure SQL Data Warehouse.Load data into Azure SQL Data Warehouse.

Se non si ha una sottoscrizione di Azure, creare un account gratuito prima di iniziare.If you don't have an Azure subscription, create a free account before you begin.

Prima di iniziareBefore you begin

Prima di iniziare questa esercitazione, scaricare e installare la versione più recente di SQL Server Management Studio (SSMS).Before you begin this tutorial, download and install the newest version of SQL Server Management Studio (SSMS).

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.For Azure Active Directory App Registration use the Application ID as the Client ID.

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. Per istruzioni, vedere Effettuare l'autenticazione in Azure Data Lake Store tramite Active Directory.For instructions, see Authenticate to Azure Data Lake Store Using Active Directory.

-- 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/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/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 il comando CREATE EXTERNAL DATA SOURCE per archiviare il percorso dei dati.Use this CREATE EXTERNAL DATA SOURCE command to store the location of the data.

-- 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 oggetto definisce la modalità di scrittura dei dati in ADLS.This object defines how the files are written in ADLS. Per un elenco completo, vedere la documentazione T-SQL di CREATE EXTERNAL FILE FORMATFor the complete list, look at our T-SQL documentation 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 missing values as default for datatype.

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 esterneCreate 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. Il parametro location può specificare un file o una directory.The location parameter can specify a file or a directory. Se specifica una directory, verranno caricati tutti i file all'interno della directory.If it specifies a directory, all files within the directory will be loaded.

-- 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.

Le tabelle esterne sono fortemente tipizzate.External Tables are 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 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.

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 e caricare i dati.This enables PolyBase to authenticate and load 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.

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

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

In questa esercitazione sono state create tabelle esterne per definire la struttura dei dati archiviati in Azure Data Lake Store e quindi è stata usata l'istruzione PolyBase CREATE TABLE AS SELECT per caricare dati nel data warehouse.In this tutorial, you created external tables to define the structure for data stored in Azure Data Lake Store, and then used the PolyBase CREATE TABLE AS SELECT statement to load data into your data warehouse.

Sono state eseguite queste operazioni:You did these things:

  • Creazione degli oggetti di database necessari da caricare da Azure Data Lake Store.Created database objects required to load from Azure Data Lake Store.
  • Connessione a una directory di Azure Data Lake Store.Connected to an Azure Data Lake Store Directory.
  • Caricamento dei dati in Azure SQL Data Warehouse.Loaded data into Azure SQL Data Warehouse.

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.Check out our development resources.