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

В этом учебнике используется инструкция 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, заменив 'a123STRONGpassword!' на собственный пароль.

   CREATE LOGIN LoaderRC20 WITH PASSWORD = 'a123STRONGpassword!';
   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. В обозревателе объектов выберите раскрывающееся меню Подключить и выберите пункт Ядро СУБД. Откроется диалоговое окно Соединение с сервером .

   

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;
• настроить правило брандмауэра на уровне сервера на портале Azure;
• подключились к хранилищу данных с помощью SSMS;
• создали учетную запись пользователя, предназначенную для загрузки данных;
• создали таблицы для образца данных;
• использовали инструкцию T-SQL COPY для загрузки данных в хранилище данных;
• просмотрели ход выполнения загрузки данных;

Перейдите к обзору разработки, чтобы узнать, как перенести существующую базу данных в Azure Synapse Analytics.

Проектные решения для переноса существующей базы данных в Azure Synapse Analytics

Дополнительные примеры загрузки и ссылки см. в следующей документации:

• Справочная документация по инструкции COPY
• Примеры использования инструкции COPY для каждого способа проверки подлинности
• Краткое руководство по инструкции COPY для одной таблицы