Samouczek: ładowanie zestawu danych Taxicab w Nowym Jorku

W tym samouczku użyto instrukcji COPY do załadowania zestawu danych Taxicab w Nowym Jorku z konta usługi Azure Blob Storage. W tym samouczku użyto witryny Azure Portal i programu SQL Server Management Studio (SSMS), aby wykonać następujące czynności:

• Tworzenie użytkownika wyznaczonego do ładowania danych
• Tworzenie tabel dla przykładowego zestawu danych
• Użyj instrukcji COPY języka T-SQL, aby załadować dane do magazynu danych
• Wyświetlanie postępu ładowania danych

Jeśli nie masz subskrypcji platformy Azure, przed rozpoczęciem utwórz bezpłatne konto platformy Azure.

Zanim rozpoczniesz

Zanim rozpoczniesz ten samouczek, pobierz i zainstaluj najnowszą wersję programu SQL Server Management Studio (SSMS).

W tym samouczku założono, że utworzono już dedykowaną pulę SQL.

Tworzenie użytkownika do ładowania danych

Konto administratora serwera jest przeznaczone do wykonywania operacji zarządzania i nie jest odpowiednie do wykonywania zapytań względem danych użytkownika. Operacja ładowania danych bardzo obciąża pamięć. Maksymalne wartości pamięci są definiowane zgodnie ze skonfigurowanymi jednostkami magazynu danych i klasą zasobów.

Najlepszym rozwiązaniem jest utworzenie identyfikatora logowania i użytkownika, które są przeznaczone do ładowania danych. Następnie należy dodać użytkownika ładującego do klasy zasobów, która umożliwia odpowiednią maksymalną alokację pamięci.

Połącz się jako administrator serwera, aby móc tworzyć identyfikatory logowania i użytkowników. Wykonaj następujące kroki, aby utworzyć identyfikator logowania i użytkownika o nazwie LoaderRC20. Następnie przypisz użytkownika do staticrc20 klasy zasobów.

1. W programie SSMS wybierz prawym przyciskiem wyboru master , aby wyświetlić menu rozwijane, a następnie wybierz pozycję Nowe zapytanie. Otworzy się okno nowego zapytania.

2. W oknie zapytania wprowadź te polecenia języka T-SQL, aby utworzyć identyfikator logowania i użytkownika o nazwie LoaderRC20, zastępując własne silne hasło.

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

3. Wybierz polecenie Wykonaj.

4. Kliknij prawym przyciskiem myszy pozycję mySampleDataWarehouse i wybierz pozycję Nowe zapytanie. Zostanie otwarte okno nowego zapytania.

5. Wprowadź następujące polecenia języka T-SQL, aby utworzyć użytkownika bazy danych o nazwie LoaderRC20 dla identyfikatora LoaderRC20 logowania. Drugi wiersz przyznaje nowemu użytkownikowi uprawnienia kontrolne (CONTROL) do nowego magazynu danych. Te uprawnienia dają użytkownikowi możliwości podobne do tych, które miałby jako właściciel bazy danych. Trzeci wiersz dodaje nowego użytkownika jako członka staticrc20 klasy zasobów.

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

6. Wybierz polecenie Wykonaj.

Nawiązywanie połączenia z serwerem jako użytkownik ładujący

Pierwszym krokiem w kierunku ładowania danych jest zalogowanie się jako LoaderRC20.

1. W Eksplorator obiektów wybierz menu rozwijane Połącz i wybierz pozycję Aparat bazy danych. Zostanie wyświetlone okno dialogowe Nawiązywanie połączenia z serwerem.

2. Wprowadź w pełni kwalifikowaną nazwę serwera, a następnie wprowadź nazwę LoaderRC20 logowania. Wprowadź hasło dla użytkownika LoaderRC20.

3. Wybierz pozycję Połącz.

4. Gdy połączenie będzie gotowe, zobaczysz dwa połączenia serwera w Eksplorator obiektów. Jedno połączenie jako ServerAdmin i jedno połączenie jako LoaderRC20.

Tworzenie tabel dla przykładowych danych

Możesz rozpocząć proces ładowania danych do nowego magazynu danych. W tej części samouczka pokazano, jak za pomocą instrukcji COPY załadować zestaw danych taksówki w Nowym Jorku z obiektu blob usługi Azure Storage. Aby dowiedzieć się, jak pobrać dane do usługi Azure Blob Storage lub załadować je bezpośrednio ze źródła, zobacz omówienie ładowania.

Uruchom następujące skrypty SQL i określ informacje o danych, które chcesz załadować. Informacje te obejmują obecną lokalizację danych, format zawartości danych i definicję tabel dla danych.

1. W poprzedniej sekcji zalogowano się do magazynu danych jako LoaderRC20. W programie SSMS kliknij prawym przyciskiem myszy połączenie użytkownika LoaderRC20, a następnie wybierz polecenie Nowe zapytanie. Zostanie otwarte okno nowego zapytania.

2. Porównaj to okno zapytania z poprzednią ilustracją. Sprawdź, czy nowe okno zapytania jest uruchomione jako LoaderRC20 i wykonuje zapytania w MySampleDataWarehouse bazie danych. Wszystkie kroki ładowania wykonaj w tym oknie zapytania.

3. Uruchom następujące instrukcje języka T-SQL, aby utworzyć tabele:

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

Ładowanie danych do magazynu danych

W tej sekcji użyto instrukcji COPY do załadowania przykładowych danych z obiektu blob usługi Azure Storage.

Uwaga

W tym samouczku dane są ładowane bezpośrednio do tabeli końcowej. Zazwyczaj należy załadować je do tabeli przejściowej dla obciążeń produkcyjnych. Gdy dane znajdują się w tabeli przejściowej, można wykonać wszelkie niezbędne przekształcenia.

1. Uruchom następujące instrukcje, aby załadować dane:

   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. Wyświetlaj dane podczas ładowania. Ładujesz kilka gb danych i kompresujesz je do wysoce wydajnych klastrowanych indeksów magazynu kolumn. Uruchom następujące zapytanie korzystające z dynamicznych widoków zarządzania (DMV), aby wyświetlić stan ładowania.

   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. Wyświetl wszystkie zapytania systemowe.

   SELECT * FROM sys.dm_pdw_exec_requests;
   

4. Korzystaj z danych załadowanych ładnie do magazynu danych.

Czyszczenie zasobów

Opłaty są naliczane za zasoby obliczeniowe i dane załadowane do magazynu danych. Są one rozliczane osobno.

• Jeśli chcesz przechowywać dane w magazynie, możesz wstrzymać obliczenia, gdy nie korzystasz z magazynu danych. Wstrzymując obliczenia, opłaty będą naliczane tylko za magazyn danych i można wznowić obliczenia za każdym razem, gdy wszystko będzie gotowe do pracy z danymi.
• Aby uniknąć opłat w przyszłości, możesz usunąć magazyn danych.

Wykonaj następujące kroki, aby wyczyścić zasoby zgodnie z potrzebami.

1. Zaloguj się do witryny Azure Portal i wybierz magazyn danych.

2. Aby wstrzymać obliczenia, wybierz przycisk Wstrzymaj. Gdy magazyn danych jest wstrzymany, zobaczysz przycisk Uruchom. Aby wznowić obliczenia, wybierz pozycję Uruchom.

3. Aby usunąć magazyn danych, aby nie być naliczane opłaty za zasoby obliczeniowe lub magazyn, wybierz pozycję Usuń.

4. Aby usunąć utworzony serwer, wybierz mynewserver-20180430.database.windows.net na poprzedniej ilustracji , a następnie wybierz pozycję Usuń. Należy zachować ostrożność, ponieważ usunięcie serwera spowoduje usunięcie wszystkich baz danych przypisanych do serwera.

5. Aby usunąć grupę zasobów, wybierz pozycję myResourceGroup, a następnie wybierz pozycję Usuń grupę zasobów.

Powiązana zawartość

• COPY, instrukcja
• Decyzje projektowe dotyczące migracji istniejącej bazy danych do usługi Azure Synapse Analytics
• Dokumentacja referencyjna instrukcji COPY
• Przykłady kopiowania dla każdej metody uwierzytelniania
• Przewodnik Szybki start dotyczący kopiowania pojedynczej tabeli