Учебник. Загрузка набора данных о такси Нью-Йорка

В этом учебнике используется инструкция Copy для загрузки набора данных о такси Нью-Йорка из учетной записи Хранилища BLOB-объектов Azure. В рамках этого руководства портал Azure и SQL Server Management Studio (SSMS) используются, чтобы выполнить такие действия:

• создать учетную запись пользователя, предназначенную для загрузки данных;
• создать таблицы для шаблона набора данных;
• использовать инструкцию T-SQL COPY для загрузки данных в хранилище данных;
• просмотреть ход выполнения загрузки данных;

Если у вас еще нет подписки Azure, создайте бесплатную учетную запись Azure, прежде чем начинать работу.

Подготовка к работе

Перед началом работы с этим руководством скачайте и установите последнюю версию SQL Server Management Studio (SSMS).

В этом руководстве предполагается, что вы уже создали выделенный пул SQL.

Создание учетной записи пользователя для загрузки данных

Учетная запись администратора сервера предназначена для операций управления и не подходит для выполнения запросов к пользовательским данным. Загрузка данных — это операция с интенсивным использованием памяти. Максимальные ограничения памяти определяются в соответствии с единицами использования хранилища данных и настроенным классом ресурсов.

Лучше всего создать имя для входа и имя пользователя, предназначенные для загрузки данных. Затем добавьте пользователя загрузки в класс ресурсов, позволяющий выполнять соответствующее выделение максимальной памяти.

Подключитесь как администратор сервера, чтобы создать имена входа и пользователей. Чтобы создать имя для входа и пользователя, выполните LoaderRC20следующие действия. Затем назначьте пользователя классу staticrc20 ресурсов.

1. В SSMS щелкните правой кнопкой мыши master раскрывающееся меню и выберите пункт "Создать запрос". Откроется новое окно запроса.

2. В окне запроса введите эти команды T-SQL для создания имени входа и пользователя LoaderRC20, заменив собственный надежный пароль.

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

3. Выберите Выполнить.

4. Щелкните правой кнопкой мыши mySampleDataWarehouse и выберите Новый запрос. Откроется окно нового запроса.

5. Введите следующие команды T-SQL, чтобы создать пользователя базы данных с именем LoaderRC20 LoaderRC20 для входа. Вторая строка предоставляет новому пользователю разрешения на управление (CONTROL) новым хранилищем данных. Эти разрешения аналогичны назначению пользователя владельцем базы данных. Третья строка добавляет нового пользователя в качестве участника класса ресурсов staticrc20.

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

6. Выберите Выполнить.

Подключение к серверу от имени пользователя, выполняющего загрузку

Первым шагом к загрузке данных является вход в качестве LoaderRC20имени.

1. В обозреватель объектов выберите раскрывающееся меню Connect и выберите ядро СУБД. Откроется диалоговое окно Соединение с сервером .

2. Введите полное имя сервера и введите LoaderRC20 имя входа. Введите пароль для LoaderRC20.

3. Нажмите Подключиться.

4. Когда подключение будет готово, в обозреватель объектов вы увидите два подключения сервера. Одно подключение как ServerAdmin и одно подключение в качестве LoaderRC20.

Создание таблиц для примеров данных

Вы готовы начать процесс загрузки данных в новое хранилище данных. В этом разделе учебника показано, как использовать инструкцию COPY для загрузки набора данных о такси города Нью-Йорка из BLOB-объекта службы хранилища Azure. Подробности о перемещении данных в Хранилище BLOB-объектов Azure или их загрузке непосредственно из источника, см. в Общие сведения о загрузке.

Выполните следующие скрипты SQL и укажите сведения о данных, которые требуется загрузить. К этим сведениям относится информация о расположении данных, формате содержимого данных и определении таблицы для данных.

1. В предыдущем разделе вы вошли в хранилище данных как LoaderRC20. В среде SSMS щелкните правой кнопкой мыши подключение LoaderRC20 и выберите Новый запрос. Откроется окно нового запроса.

2. Сравните окно запроса с предыдущим изображением. Убедитесь, что новое окно запроса выполняется как LoaderRC20 и выполняет запросы в базе MySampleDataWarehouse данных. Используйте это окно запроса для выполнения всех шагов загрузки.

3. Чтобы создать таблицы, выполните приведенные ниже инструкции T-SQL:

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

Загрузка данных в хранилище данных

В этом разделе используется инструкция COPY для загрузки образца данных из Azure Storage Blob.

Примечание.

В этом руководстве данные загружаются непосредственно в итоговую таблицу. В случае производственных рабочих нагрузок данные обычно загружают в промежуточную таблицу. Если данные расположены в промежуточной таблице, вы можете выполнять все необходимые преобразования.

1. Выполните следующие инструкции, чтобы загрузить данные:

   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. Просмотрите данные при загрузке. Вы загружаете несколько гигабайт данных и сжимаете их в высокопроизводительные кластеризованные индексы columnstore. Выполните следующий запрос, использующий динамические административные представления, для отображения состояния загрузки.

   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. Просмотрите все запросы в системе.

   SELECT * FROM sys.dm_pdw_exec_requests;
   

4. Наслаждайтесь своими данными, которые хорошо загружаются в хранилище данных.

Очистка ресурсов

Плата взимается за вычислительные ресурсы и данные, которые загружаются в хранилище данных. По ним выставляется отдельный счет.

• Если вы хотите сохранить данные в хранилище, то можете приостановить работу вычислительных ресурсов, когда не используете хранилище данных. При приостановке вычислений плата будет взиматься только за хранилище данных, и вы можете возобновить вычисление всякий раз, когда вы будете готовы к работе с данными.
• Если вы хотите исключить будущие расходы, то можете удалить хранилище данных.

Выполните следующие действия, чтобы очистить ресурсы по необходимости.

1. Войдите в портал Azure и выберите хранилище данных.

2. Чтобы приостановить вычисление, нажмите кнопку Пауза. Если работа хранилища данных приостановлена, вы увидите кнопку Запуск. Чтобы возобновить вычисление, нажмите кнопку Пуск.

3. Чтобы удалить хранилище данных во избежание дальнейших платежей за вычисления или хранение, выберите Удалить.

4. Чтобы удалить созданный вами сервер, выберите сервер mynewserver-20180430.database.windows.net из предыдущего изображения, а затем нажмите кнопку Удалить. Будьте осторожны с этим, так как удаление сервера удаляет все базы данных, назначенные серверу.

5. Чтобы удалить группу ресурсов, выберите myResourceGroup, а затем Удалить группу ресурсов.

Связанный контент

• Инструкция COPY
• Проектные решения для переноса существующей базы данных в Azure Synapse Analytics
• Справочная документация по инструкции COPY
• Примеры использования инструкции COPY для каждого способа проверки подлинности
• Краткое руководство по инструкции COPY для одной таблицы