Esercitazione: caricare il set di dati del taxi di New YorkTutorial: Load the New York Taxicab dataset

Questa esercitazione usa l' istruzione Copy per caricare il set di dati del taxi di New York da un account di archiviazione BLOB di Azure.This tutorial uses the COPY statement to load New York Taxicab dataset from an Azure blob storage account. Questa esercitazione usa il portale di Azure e SQL Server Management Studio (SSMS) per:The tutorial uses the Azure portal and SQL Server Management Studio (SSMS) to:

  • Creare un utente designato per il caricamento dei datiCreate a user designated for loading data
  • Creare le tabelle per il set di dati di esempioCreate the tables for the sample dataset
  • Usare l'istruzione T-SQL COPY per caricare i dati nel data warehouseUse the COPY T-SQL statement to load data into your data warehouse
  • Visualizzare lo stato di avanzamento dei dati durante il caricamentoView the progress of data as it is loading

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

In questa esercitazione si presuppone che sia già stato creato un pool dedicato a SQL nell' esercitazioneseguente.This tutorial assumes you have already created a SQL dedicated pool from the following tutorial.

Creare un utente per il caricamento dei datiCreate a user for loading data

L'account amministratore del server ha la funzione di eseguire operazioni di gestione e non è appropriato per l'esecuzione di query sui dati degli utenti.The server admin account is meant to perform management operations, and is not suited for running queries on user data. Il caricamento di dati è un'operazione a elevato utilizzo di memoria.Loading data is a memory-intensive operation. I valori massimi di memoria sono definiti in base alle unità di data warehouse e alla classe di risorse configurata.Memory maximums are defined according to the data warehouse units and resource class configured.

È consigliabile creare un account di accesso e un utente dedicato per il caricamento dei dati.It's best to create a login and user that is dedicated for loading data. Quindi aggiungere l'utente con il compito di caricare i dati a una classe di risorse che consente un'allocazione di memoria massima appropriata.Then add the loading user to a resource class that enables an appropriate maximum memory allocation.

Connettersi come amministratore del server in modo che sia possibile creare account di accesso e utenti.Connect as the server admin so you can create logins and users. Usare questa procedura per creare un account di accesso e un utente denominato LoaderRC20.Use these steps to create a login and user called LoaderRC20. Quindi assegnare l'utente alla classe di risorse staticrc20.Then assign the user to the staticrc20 resource class.

  1. In SSMS, fare clic con il pulsante destro del mouse su Master per visualizzare un menu a discesa e scegliere nuova query.In SSMS, right-select master to show a drop-down menu, and choose New Query. Viene visualizzata una nuova finestra di query.A new query window opens.

    Nuova query nel master

  2. Nella finestra della query immettere i comandi T-SQL per creare un account di accesso e un utente denominato LoaderRC20, sostituendo la propria password al valore "a123STRONGpassword!".In the query window, enter these T-SQL commands to create a login and user named LoaderRC20, substituting your own password for 'a123STRONGpassword!'.

    CREATE LOGIN LoaderRC20 WITH PASSWORD = 'a123STRONGpassword!';
    CREATE USER LoaderRC20 FOR LOGIN LoaderRC20;
    
  3. Selezionare Esegui.Select Execute.

  4. Fare clic con il pulsante destro del mouse su mySampleDataWarehouse e scegliere Nuova query.Right-click mySampleDataWarehouse, and choose New Query. Viene visualizzata una nuova finestra della query.A new query Window opens.

    Nuova query nel data warehouse di esempio

  5. Immettere i comandi T-SQL seguenti per creare un utente del database denominato LoaderRC20 per l'account di accesso LoaderRC20.Enter the following T-SQL commands to create a database user named LoaderRC20 for the LoaderRC20 login. La seconda riga concede al nuovo utente autorizzazioni di CONTROLLO sul nuovo data warehouse.The second line grants the new user CONTROL permissions on the new data warehouse. Queste autorizzazioni sono simili a rendere l'utente proprietario del database.These permissions are similar to making the user the owner of the database. La terza riga aggiunge il nuovo utente come membro della classe di risorse staticrc20.The third line adds the new user as a member of the staticrc20 resource class.

    CREATE USER LoaderRC20 FOR LOGIN LoaderRC20;
    GRANT CONTROL ON DATABASE::[mySampleDataWarehouse] to LoaderRC20;
    EXEC sp_addrolemember 'staticrc20', 'LoaderRC20';
    
  6. Selezionare Esegui.Select Execute.

Connettersi al server come utente addetto al caricamentoConnect to the server as the loading user

Il primo passo per caricare dati è eseguire l'accesso come LoaderRC20.The first step toward loading data is to login as LoaderRC20.

  1. In Esplora oggetti selezionare il menu a discesa Connetti e selezionare motore di database.In Object Explorer, select the Connect drop down menu and select Database Engine. Viene visualizzata la finestra di dialogo Connetti al server .The Connect to Server dialog box appears.

    Connettersi con il nuovo account

  2. Immettere il nome completo del server e LoaderRC20 come account di accesso.Enter the fully qualified server name, and enter LoaderRC20 as the Login. Immettere la password per LoaderRC20.Enter your password for LoaderRC20.

  3. Selezionare Connetti.Select Connect.

  4. Quando la connessione è pronta, si vedranno due connessioni server in Esplora oggetti.When your connection is ready, you will see two server connections in Object Explorer. Una connessione ServerAdmin e una connessione MedRCLogin.One connection as ServerAdmin and one connection as MedRCLogin.

    La connessione riesce

Creare tabelle per i dati di esempioCreate tables for the sample data

Ora è possibile iniziare il processo di caricamento dei dati nel nuovo data warehouse.You are ready to begin the process of loading data into your new data warehouse. Questa parte dell'esercitazione illustra come usare l'istruzione COPY per caricare il set di dati CAB di New York City da un BLOB del servizio di archiviazione di Azure.This part of the tutorial shows you how to use the COPY statement to load the New York City taxi cab dataset from an Azure Storage blob. Per riferimento futuro, per informazioni su come ottenere i dati nell'archivio BLOB di Azure o per caricarli direttamente dall'origine, vedere la Panoramica del caricamento.For future reference, to learn how to get your data to Azure blob storage or to load it directly from your source, see the loading overview.

Eseguire gli script SQL seguenti e specificare le informazioni sui dati che si desidera caricare.Run the following SQL scripts and specify information about the data you wish to load. Queste informazioni includono la posizione dei dati, il formato del contenuto dei dati e la definizione della tabella per i dati.This information includes where the data is located, the format of the contents of the data, and the table definition for the data.

  1. Nella sezione precedente è stato eseguito l'accesso al data warehouse come LoaderRC20.In the previous section, you logged into your data warehouse as LoaderRC20. In SSMS fare clic con il pulsante destro del mouse sulla connessione LoaderRC20 e selezionare Nuova query.In SSMS, right-click your LoaderRC20 connection and select New Query. Verrà visualizzata una nuova finestra di query,A new query window appears.

    Finestra della nuova query di caricamento

  2. Confrontare la finestra della query all'immagine precedente.Compare your query window to the previous image. Verificare che la nuova finestra della query sia in esecuzione come LoaderRC20 ed esegua query sul database MySampleDataWarehouse.Verify your new query window is running as LoaderRC20 and performing queries on your MySampleDataWarehouse database. Usare questa finestra della query per eseguire tutte le operazioni di caricamento.Use this query window to perform all of the loading steps.

  3. Eseguire le istruzioni T-SQL seguenti per creare le tabelle:Run the following T-SQL statements to create the tables:

    CREATE TABLE [dbo].[Date]
    (
        [DateID] int NOT NULL,
        [Date] datetime NULL,
        [DateBKey] char(10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [DayOfMonth] varchar(2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [DaySuffix] varchar(4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [DayName] varchar(9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [DayOfWeek] char(1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [DayOfWeekInMonth] varchar(2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [DayOfWeekInYear] varchar(2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [DayOfQuarter] varchar(3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [DayOfYear] varchar(3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [WeekOfMonth] varchar(1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [WeekOfQuarter] varchar(2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [WeekOfYear] varchar(2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [Month] varchar(2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [MonthName] varchar(9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [MonthOfQuarter] varchar(2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [Quarter] char(1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [QuarterName] varchar(9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [Year] char(4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [YearName] char(7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [MonthYear] char(10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [MMYYYY] char(6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [FirstDayOfMonth] date NULL,
        [LastDayOfMonth] date NULL,
        [FirstDayOfQuarter] date NULL,
        [LastDayOfQuarter] date NULL,
        [FirstDayOfYear] date NULL,
        [LastDayOfYear] date NULL,
        [IsHolidayUSA] bit NULL,
        [IsWeekday] bit NULL,
        [HolidayUSA] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    )
    WITH
    (
        DISTRIBUTION = ROUND_ROBIN,
        CLUSTERED COLUMNSTORE INDEX
    );
    
    CREATE TABLE [dbo].[Geography]
    (
        [GeographyID] int NOT NULL,
        [ZipCodeBKey] varchar(10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
        [County] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [City] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [State] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [Country] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [ZipCode] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    )
    WITH
    (
        DISTRIBUTION = ROUND_ROBIN,
        CLUSTERED COLUMNSTORE INDEX
    );
    
    CREATE TABLE [dbo].[HackneyLicense]
    (
        [HackneyLicenseID] int NOT NULL,
        [HackneyLicenseBKey] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
        [HackneyLicenseCode] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    )
    WITH
    (
        DISTRIBUTION = ROUND_ROBIN,
        CLUSTERED COLUMNSTORE INDEX
    );
    
    CREATE TABLE [dbo].[Medallion]
    (
        [MedallionID] int NOT NULL,
        [MedallionBKey] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
        [MedallionCode] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    )
    WITH
    (
        DISTRIBUTION = ROUND_ROBIN,
        CLUSTERED COLUMNSTORE INDEX
    );
    
    CREATE TABLE [dbo].[Time]
    (
        [TimeID] int NOT NULL,
        [TimeBKey] varchar(8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
        [HourNumber] tinyint NOT NULL,
        [MinuteNumber] tinyint NOT NULL,
        [SecondNumber] tinyint NOT NULL,
        [TimeInSecond] int NOT NULL,
        [HourlyBucket] varchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
        [DayTimeBucketGroupKey] int NOT NULL,
        [DayTimeBucket] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
    )
    WITH
    (
        DISTRIBUTION = ROUND_ROBIN,
        CLUSTERED COLUMNSTORE INDEX
    );
    
    CREATE TABLE [dbo].[Trip]
    (
        [DateID] int NOT NULL,
        [MedallionID] int NOT NULL,
        [HackneyLicenseID] int NOT NULL,
        [PickupTimeID] int NOT NULL,
        [DropoffTimeID] int NOT NULL,
        [PickupGeographyID] int NULL,
        [DropoffGeographyID] int NULL,
        [PickupLatitude] float NULL,
        [PickupLongitude] float NULL,
        [PickupLatLong] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [DropoffLatitude] float NULL,
        [DropoffLongitude] float NULL,
        [DropoffLatLong] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [PassengerCount] int NULL,
        [TripDurationSeconds] int NULL,
        [TripDistanceMiles] float NULL,
        [PaymentType] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [FareAmount] money NULL,
        [SurchargeAmount] money NULL,
        [TaxAmount] money NULL,
        [TipAmount] money NULL,
        [TollsAmount] money NULL,
        [TotalAmount] money NULL
    )
    WITH
    (
        DISTRIBUTION = ROUND_ROBIN,
        CLUSTERED COLUMNSTORE INDEX
    );
    
    CREATE TABLE [dbo].[Weather]
    (
        [DateID] int NOT NULL,
        [GeographyID] int NOT NULL,
        [PrecipitationInches] float NOT NULL,
        [AvgTemperatureFahrenheit] float NOT NULL
    )
    WITH
    (
        DISTRIBUTION = ROUND_ROBIN,
        CLUSTERED COLUMNSTORE INDEX
    );
    

Caricare i dati nel data warehouseLoad the data into your data warehouse

In questa sezione viene utilizzata l' istruzione Copy per caricare i dati di esempio da BLOB del servizio di archiviazione di Azure.This section uses the COPY statement to load the sample data from Azure Storage Blob.

Nota

Questa esercitazione carica i dati direttamente nella tabella finale.This tutorial loads the data directly into the final table. In genere si esegue il caricamento in una tabella di staging per i carichi di lavoro di produzione.You would typically load into a staging table for your production workloads. Mentre i dati si trovano nella tabella di staging è possibile eseguire le trasformazioni eventualmente necessarie.While data is in the staging table you can perform any necessary transformations.

  1. Eseguire le istruzioni seguenti per caricare i dati:Run the following statements to load the data:

    COPY INTO [dbo].[Date]
    FROM 'https://nytaxiblob.blob.core.windows.net/2013/Date'
    WITH
    (
        FILE_TYPE = 'CSV',
        FIELDTERMINATOR = ',',
        FIELDQUOTE = ''
    )
    OPTION (LABEL = 'COPY : Load [dbo].[Date] - Taxi dataset');
    
    
    COPY INTO [dbo].[Geography]
    FROM 'https://nytaxiblob.blob.core.windows.net/2013/Geography'
    WITH
    (
        FILE_TYPE = 'CSV',
        FIELDTERMINATOR = ',',
        FIELDQUOTE = ''
    )
    OPTION (LABEL = 'COPY : Load [dbo].[Geography] - Taxi dataset');
    
    COPY INTO [dbo].[HackneyLicense]
    FROM 'https://nytaxiblob.blob.core.windows.net/2013/HackneyLicense'
    WITH
    (
        FILE_TYPE = 'CSV',
        FIELDTERMINATOR = ',',
        FIELDQUOTE = ''
    )
    OPTION (LABEL = 'COPY : Load [dbo].[HackneyLicense] - Taxi dataset');
    
    COPY INTO [dbo].[Medallion]
    FROM 'https://nytaxiblob.blob.core.windows.net/2013/Medallion'
    WITH
    (
        FILE_TYPE = 'CSV',
        FIELDTERMINATOR = ',',
        FIELDQUOTE = ''
    )
    OPTION (LABEL = 'COPY : Load [dbo].[Medallion] - Taxi dataset');
    
    COPY INTO [dbo].[Time]
    FROM 'https://nytaxiblob.blob.core.windows.net/2013/Time'
    WITH
    (
        FILE_TYPE = 'CSV',
        FIELDTERMINATOR = ',',
        FIELDQUOTE = ''
    )
    OPTION (LABEL = 'COPY : Load [dbo].[Time] - Taxi dataset');
    
    COPY INTO [dbo].[Weather]
    FROM 'https://nytaxiblob.blob.core.windows.net/2013/Weather'
    WITH
    (
        FILE_TYPE = 'CSV',
        FIELDTERMINATOR = ',',
        FIELDQUOTE = '',
        ROWTERMINATOR='0X0A'
    )
    OPTION (LABEL = 'COPY : Load [dbo].[Weather] - Taxi dataset');
    
    COPY INTO [dbo].[Trip]
    FROM 'https://nytaxiblob.blob.core.windows.net/2013/Trip2013'
    WITH
    (
        FILE_TYPE = 'CSV',
        FIELDTERMINATOR = '|',
        FIELDQUOTE = '',
        ROWTERMINATOR='0X0A',
        COMPRESSION = 'GZIP'
    )
    OPTION (LABEL = 'COPY : Load [dbo].[Trip] - Taxi dataset');
    
  2. Visualizzare i dati man mano che vengono caricati.View your data as it loads. Si stanno caricando diversi GB di dati e compressi in indici columnstore cluster a elevato livello di prestazioni.You're loading several GBs of data and compressing it into highly performant clustered columnstore indexes. Eseguire la query riportata di seguito, che usa le viste a gestione dinamica (DMV) per visualizzare lo stato del caricamento.Run the following query that uses a dynamic management views (DMVs) to show the status of the load.

    SELECT  r.[request_id]                           
    ,       r.[status]                               
    ,       r.resource_class                         
    ,       r.command
    ,       sum(bytes_processed) AS bytes_processed
    ,       sum(rows_processed) AS rows_processed
    FROM    sys.dm_pdw_exec_requests r
                  JOIN sys.dm_pdw_dms_workers w
                         ON r.[request_id] = w.request_id
    WHERE [label] = 'COPY : Load [dbo].[Date] - Taxi dataset' OR
        [label] = 'COPY : Load [dbo].[Geography] - Taxi dataset' OR
        [label] = 'COPY : Load [dbo].[HackneyLicense] - Taxi dataset' OR
        [label] = 'COPY : Load [dbo].[Medallion] - Taxi dataset' OR
        [label] = 'COPY : Load [dbo].[Time] - Taxi dataset' OR
        [label] = 'COPY : Load [dbo].[Weather] - Taxi dataset' OR
        [label] = 'COPY : Load [dbo].[Trip] - Taxi dataset' 
    and session_id <> session_id() and type = 'WRITER'
    GROUP BY r.[request_id]                           
    ,       r.[status]                               
    ,       r.resource_class                         
    ,       r.command;
    
  3. Visualizzare tutte le query di sistema.View all system queries.

    SELECT * FROM sys.dm_pdw_exec_requests;
    
  4. I dati vengono caricati nel data warehouse.Enjoy seeing your data nicely loaded into your data warehouse.

    Visualizzare le tabelle caricate

Pulire le risorseClean up resources

Le risorse di calcolo e i dati caricati nel data warehouse prevedono un addebito.You are being charged for compute resources and data that you loaded into your data warehouse. Questi costi vengono addebitati separatamente.These are billed separately.

  • Se si vogliono mantenere i dati nelle risorse di archiviazione, è possibile sospendere il calcolo quando il data warehouse non è in uso.If you want to keep the data in storage, you can pause compute when you aren't using the data warehouse. Sospendendo il calcolo verrà addebitata solo l'archiviazione dati: sarà possibile riprendere il calcolo quando sarà necessario elaborare i dati.By pausing compute you will only be charge for data storage and you can resume the compute whenever you are ready to work with the data.
  • Per evitare di ricevere addebiti in futuro, è possibile eliminare il data warehouse.If you want to remove future charges, you can delete the data warehouse.

Seguire questa procedura per pulire le risorse nel modo desiderato.Follow these steps to clean up resources as you desire.

  1. Accedere al portale di Azure, selezionare la data warehouse.Log in to the Azure portal, select your data warehouse.

    Pulire le risorse

  2. Per sospendere il calcolo, selezionare il pulsante Pausa.To pause compute, select the Pause button. Quando si sospende il data warehouse, viene visualizzato il pulsante Avvia.When the data warehouse is paused, you will see a Start button. Per riprendere il calcolo, selezionare Avvia.To resume compute, select Start.

  3. Per rimuovere il data warehouse in modo che non venga addebitato il calcolo o l'archiviazione, selezionare Elimina.To remove the data warehouse so you won't be charged for compute or storage, select Delete.

  4. Per rimuovere il server creato, selezionare MyNewServer-20180430.database.Windows.NET nell'immagine precedente e quindi selezionare Elimina.To remove the server you created, select mynewserver-20180430.database.windows.net in the previous image, and then select Delete. Prestare attenzione con questa operazione perché eliminando il server saranno eliminati tutti i database assegnati al server.Be careful with this as deleting the server will delete all databases assigned to the server.

  5. Per rimuovere il gruppo di risorse, selezionare myResourceGroup e quindi Elimina gruppo di risorse.To remove the resource group, select myResourceGroup, and then select Delete resource group.

Passaggi successiviNext steps

In questa esercitazione è stato descritto come creare un data warehouse e creare un utente per il caricamento dei dati.In this tutorial, you learned how to create a data warehouse and create a user for loading data. È stata usata l' istruzione Copy semplice per caricare i dati nel data warehouse.You used the simple COPY statement to load data into your data warehouse.

Sono state eseguite queste operazioni:You did these things:

  • È stato creato un data warehouse nel portale di AzureCreated a data warehouse in the Azure portal
  • Impostare una regola del firewall a livello di server nel portale di AzureSet up a server-level firewall rule in the Azure portal
  • Ci si è connessi al data warehouse con SSMSConnected to the data warehouse with SSMS
  • È stato creato un utente designato per il caricamento dei datiCreated a user designated for loading data
  • Creazione delle tabelle per i dati di esempioCreated the tables for the sample data
  • È stata usata l'istruzione COPY T-SQL per caricare i dati nel data warehouseUsed the COPY T-SQL statement to load data into your data warehouse
  • È stato visualizzato lo stato di avanzamento dei dati durante il caricamentoViewed the progress of data as it is loading

Passare alla panoramica dello sviluppo per informazioni su come eseguire la migrazione di un database esistente ad Azure sinapsi Analytics:Advance to the development overview to learn how to migrate an existing database to Azure Synapse Analytics:

Per ulteriori informazioni sul caricamento di esempi e riferimenti, vedere la documentazione seguente:For more loading examples and references, view the following documentation: