Tutorial: Carregar o conjunto de dados do New York Taxicab

Este tutorial usa a instrução COPY para carregar o conjunto de dados do New York Taxicab de uma conta de Armazenamento de Blob do Azure. Este tutorial utiliza o portal do Azure e o SQL Server Management Studio (SSMS) para:

• Criar um utilizador designado para carregar dados
• Criar as tabelas para o conjunto de dados de exemplo
• Use a instrução COPY T-SQL para carregar dados em seu data warehouse
• Ver o progresso dos dados à medida que são carregados

Se não tiver uma subscrição do Azure, crie uma conta do Azure gratuita antes de começar.

Antes de começar

Antes de começar este tutorial, transfira e instale a versão mais recente do SQL Server Management Studio (SSMS).

Este tutorial pressupõe que você já tenha criado um pool dedicado SQL.

Criar um utilizador para carregar dados

A conta de administrador do servidor destina-se a efetuar operações de gestão e não é adequada para executar consultas em dados do utilizador. O carregamento de dados é uma operação de memória intensiva. Os máximos de memória são definidos de acordo com as unidades de armazém de dados e a classe de recursos configurada.

É melhor criar um login e um utilizador dedicados para carregar dados. Em seguida, adicione o utilizador de carregamento a uma classe de recursos que permita uma alocação de memória máxima adequada.

Conecte-se como administrador do servidor para que você possa criar logins e usuários. Use estas etapas para criar um login e usuário chamado LoaderRC20. Em seguida, atribua o utilizador à classe de recurso staticrc20.

1. No SSMS, selecione master com o botão direito do rato para mostrar um menu suspenso e escolha Nova consulta. É aberta uma nova janela de consulta.

2. Na janela de consulta, insira esses comandos T-SQL para criar um login e um usuário chamado LoaderRC20, substituindo sua própria senha forte.

   CREATE LOGIN LoaderRC20 WITH PASSWORD = '<strong password here>';
   CREATE USER LoaderRC20 FOR LOGIN LoaderRC20;
   

3. Selecione Execute (Executar).

4. Clique com o botão direito do rato em mySampleDataWarehouse e escolha Nova Consulta. É aberta uma nova janela de consulta.

5. Insira os seguintes comandos T-SQL para criar um usuário de banco de dados nomeado LoaderRC20 para o LoaderRC20 logon. A segunda linha concede ao novo utilizador permissões CONTROL no novo armazém de dados. Estas permissões são semelhantes a tornar o utilizador o proprietário da base de dados. A terceira linha adiciona o novo usuário como um membro da staticrc20classe de recurso.

   CREATE USER LoaderRC20 FOR LOGIN LoaderRC20;
   GRANT CONTROL ON DATABASE::[mySampleDataWarehouse] to LoaderRC20;
   EXEC sp_addrolemember 'staticrc20', 'LoaderRC20';
   

6. Selecione Execute (Executar).

Ligar ao servidor como utilizador de carregamento

O primeiro passo para carregar dados é fazer login como LoaderRC20.

1. No Explorador de Objetos, selecione o menu pendente Conectar e selecione Mecanismo de Banco de Dados. A caixa de diálogo Ligar ao Servidor é apresentada.

2. Insira o nome do servidor totalmente qualificado e digite LoaderRC20 como o Login. Introduza a palavra-passe para LoaderRC20.

3. Selecione Ligar.

4. Quando a conexão estiver pronta, você verá duas conexões de servidor no Pesquisador de Objetos. Uma conexão como ServerAdmin e uma conexão como LoaderRC20.

Criar tabelas para os dados de exemplo

Você está pronto para começar o processo de carregamento de dados em seu novo data warehouse. Esta parte do tutorial mostra como usar a instrução COPY para carregar o conjunto de dados do táxi de Nova York a partir de um blob de Armazenamento do Azure. Para referência futura, para saber como transferir os seus dados para o Armazenamento de Blobs do Azure ou carregá-los diretamente da sua fonte, consulte a visão geral de carregamento.

Execute os seguintes scripts SQL e especifique informações sobre os dados que você deseja carregar. Estas informações incluem a localização dos dados, o formato do conteúdo dos dados e a definição da tabela dos dados.

1. Na secção anterior, acedeu ao armazém de dados como LoaderRC20. No SSMS, clique com o botão direito na ligação de LoaderRC20 e selecione Nova Consulta. É apresentada uma nova janela de consulta.

2. Compare a janela de consulta com a imagem anterior. Verifique se a sua nova janela de consulta está a ser executada como LoaderRC20 e a realizar consultas na sua base de dados MySampleDataWarehouse. Utilize esta janela de consulta para executar todos os passos de carregamento.

3. Execute as seguintes instruções T-SQL para criar as tabelas:

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

Carregar os dados para o armazém de dados

Esta seção usa a instrução COPY para carregar os dados de exemplo do Blob de Armazenamento do Azure.

Nota

Este tutorial carrega os dados diretamente para a tabela final. Normalmente, você carregaria em uma tabela de preparo para suas cargas de trabalho de produção. Enquanto os dados estiverem na tabela de teste, pode efetuar quaisquer transformações necessárias.

1. Execute as seguintes instruções para carregar os dados:

   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. Veja os dados à medida que são carregados. Você está carregando vários GBs de dados e compactando-os em índices columnstore clusterizados de alto desempenho. Execute a seguinte consulta que utiliza vistas de gestão dinâmica (DMVs) para mostrar o estado do carregamento.

   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. Veja todas as consultas de sistema.

   SELECT * FROM sys.dm_pdw_exec_requests;
   

4. Aproveite os seus dados devidamente carregados no armazém de dados.

Limpar recursos

Estão a ser-lhe cobrados os recursos de computação e os dados que carregou para o armazém de dados. São faturados em separado.

• Se quiser manter os dados no armazenamento, pode interromper a computação quando não estiver a utilizar o armazém de dados. Ao pausar a computação, você será cobrado apenas pelo armazenamento de dados e poderá retomar a computação sempre que estiver pronto para trabalhar com os dados.
• Se quiser remover futuras cobranças, pode eliminar o armazém de dados.

Siga estes passos para limpar os recursos conforme quiser.

1. Entre no portal do Azure e selecione seu data warehouse.

2. Para pausar a computação, selecione o botão Pausar . Quando o armazém de dados estiver em pausa, verá um botão Iniciar. Para retomar a computação, selecione Iniciar.

3. Para remover o armazém de dados para que não lhe seja cobrada computação ou armazenamento, selecione Eliminar.

4. Para remover o servidor que criou, selecione mynewserver-20180430.database.windows.net na imagem anterior e, em seguida, selecione Eliminar. Tenha cuidado com isso, pois excluir o servidor exclui todos os bancos de dados atribuídos ao servidor.

5. Para remover o grupo de recursos, selecione myResourceGroup e, em seguida, selecione Excluir grupo de recursos.

Conteúdos relacionados

• Declaração COPY
• Decisões de design para migrar um banco de dados existente para o Azure Synapse Analytics
• Documentação de referência da declaração COPY
• COPY: exemplos para cada método de autenticação
• Guia de início rápido COPY para uma única tabela