Как создать инкрементный источник данных SQL Azure для набора данных о задержках рейсов американских авиаперевозчиков

Впоследствии я обнаружил, что на портале публикации Windows Azure возникают проблемы с загрузкой больших (~ 500 000 строк, ~ 15 МБ) файлов On_Time_Performance_ YYYY _ MM .csv. Менеджер групповых программ Microsoft для DataMarket сообщил мне, что функция загрузки * .csv будет отключена для «предотвращения путаницы». Для получения дополнительной информации об этой проблеме см. Мои предварительные просмотры под кодовым названием Microsoft «Передача данных» и «Хаб данных» .

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

Обходной путь для решения двух предыдущих проблем — создать локальный клон таблицы SQL Azure со столбцом идентификаторов RowID и заново создать таблицу SQL Azure без свойства идентификатора в столбце RowID . Это позволяет использовать инструкцию BULK INSERT, чтобы импортировать новые строки из файлов On_Time_Peformance_ YYYY_MM .csv в локальную таблицу SQL Server 2012, а затем использовать мастер миграции SQL Azure (SQLMW) Джорджа Хьюи v3.8.7 или новее для добавления новых данных в один файл On_Time_Performance Таблица SQL Azure. Управление значениями идентификаторов первичного ключа в локальной таблице SQL Server безопаснее и проще, чем в SQL Azure.

Недостатком этого решения является то, что для поддержания доступа к веб-базе данных SQL Azure объемом 1 ГБ потребуется 9,99 долл. США в месяц после истечения срока действия бесплатной пробной версии. Microsoft предоставляет бесплатное хранилище SQL Azure при указании новой базы данных на портале публикации Windows Azure Marketplace.

В этом посте описан процесс и инструкции T-SQL для создания и управления локальными базами данных SQL Server [Express] 2012, а также для постепенной загрузки новых данных в базу данных SQL Azure.

Создание таблицы SQL Azure On_Time_Performance

Если у вас нет подписки SQL Azure, подпишитесь на трехмесячную бесплатную пробную версию Windows Azure , которая включает веб-базу данных SQL Azure объемом 1 ГБ, и используйте портал управления Windows Azure для создания экземпляра сервера SQL Azure и Портал управления SQL Azure для добавления базы данных (с именем On_Time_Performance для этого примера.)

Совет . Преобразуйте подписку в платную подписку до истечения срока действия бесплатной пробной версии, если вы хотите сохранить доступ к базам данных экземпляра.

Ниже приведен сценарий T-SQL, сгенерированный SQL Server Management Studio 2012 для одной таблицы On_Time_Performance с параметром «Включить индексы», для которого установлено значение «истина», а для всех полей указано значение «НЕ НУЛЬ» :

/****** Object:  Table [dbo].[On_Time_Performance] ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[On_Time_Performance](
    [RowId] [bigint] NOT NULL,
    [Year] [int] NOT NULL,
    [Month] [int] NOT NULL,
    [DayofMonth] [int] NOT NULL,
    [FlightDate] [datetime] NOT NULL,
    [Carrier] [nvarchar](256) NOT NULL,
    [Origin] [nvarchar](256) NOT NULL,
    [Dest] [nvarchar](256) NOT NULL,
    [DepDelayMinutes] [int] NOT NULL,
    [ArrDelayMinutes] [int] NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [RowId] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF)
)

GO

/****** Object:  Index [IX_ArrDelayMinutes] ******/
CREATE NONCLUSTERED INDEX [IX_ArrDelayMinutes] ON [dbo].[On_Time_Performance]
(
    [ArrDelayMinutes] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF)
GO

SET ANSI_PADDING ON

GO

/****** Object:  Index [IX_Carrier] ******/
CREATE NONCLUSTERED INDEX [IX_Carrier] ON [dbo].[On_Time_Performance]
(
    [Carrier] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF)
GO

/****** Object:  Index [IX_DepDelayMinutes] ******/
CREATE NONCLUSTERED INDEX [IX_DepDelayMinutes] ON [dbo].[On_Time_Performance]
(
    [DepDelayMinutes] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF)
GO

SET ANSI_PADDING ON

GO

/****** Object:  Index [IX_Dest] ******/
CREATE NONCLUSTERED INDEX [IX_Dest] ON [dbo].[On_Time_Performance]
(
    [Dest] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF)
GO

/****** Object:  Index [IX_FlightDate] ******/
CREATE NONCLUSTERED INDEX [IX_FlightDate] ON [dbo].[On_Time_Performance]
(
    [FlightDate] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF)
GO

/****** Object:  Index [IX_Month] ******/
CREATE NONCLUSTERED INDEX [IX_Month] ON [dbo].[On_Time_Performance]
(
    [Month] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF)
GO

/****** Object:  Index [IX_Origin] ******/
CREATE NONCLUSTERED INDEX [IX_Origin] ON [dbo].[On_Time_Performance]
(
    [Origin] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF)
GO

/****** Object:  Index [IX_Year] ******/
CREATE NONCLUSTERED INDEX [IX_Year] ON [dbo].[On_Time_Performance]
(
    [Year] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF)
GO

Примечание . SQL Azure требует, чтобы все таблицы имели кластерный первичный ключ; столбец идентификаторов RowKey такой же, как и для схемы, которая создается, если указать новую таблицу при добавлении строк из файлов * .csv с кодовым именем «Передача данных» или «Концентратор данных», а также текущий импорт DataMarket из *. функция файла CSV. Индексы необходимы для каждого поля набора данных, который вы указываете как запрашиваемый.

Откройте SQL Azure Management Studio [Express] 2012, войдите на сервер SQL Azure ( e3895m7bbt.database.windows.net для этого примера), введите свой административный идентификатор пользователя (с добавлением @ и идентификатора сервера ) и пароль:

Соединитесь, чтобы открыть соединение, нажмите Новый запрос, чтобы добавить пустое окно редактора запросов, скопируйте и вставьте предыдущую инструкцию T-SQL DDL в окно и нажмите Выполнить, чтобы создать новую таблицу и ее индексы:

Создание локальной таблицы клонирования SQL Server

/****** Object:  Table [dbo].[On_Time_Performance] ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[On_Time_Performance](
    [RowId] [bigint] identity NOT NULL,
    [Year] [int] NOT NULL,
    [Month] [int] NOT NULL,
    [DayofMonth] [int] NOT NULL,
    [FlightDate] [datetime] NOT NULL,
    [Carrier] [nvarchar](256) NOT NULL,
    [Origin] [nvarchar](256) NOT NULL,
    [Dest] [nvarchar](256) NOT NULL,
    [DepDelayMinutes] [int] NOT NULL,
    [ArrDelayMinutes] [int] NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [RowId] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF)
)

GO

Импорт данных * .csv с помощью команды BULK IMPORT

USE [On_Time_Performance]
GO

CREATE TABLE dbo.[FlightDataTemp](
    [Year] [int] NOT NULL,
    [Month] [int] NOT NULL,
    [DayofMonth] [int] NOT NULL,
    [FlightDate] [datetime] NOT NULL,
    [Carrier] [nvarchar](256) NOT NULL,
    [Origin] [nvarchar](256) NOT NULL,
    [Dest] [nvarchar](256) NOT NULL,
    [DepDelayMinutes] [int] NOT NULL,
    [ArrDelayMinutes] [int] NOT NULL)

GO

BULK INSERT dbo.[FlightDataTemp]
FROM 'C:\Users\Administrator\Documents\FlightData\On_Time_Performance_2012_1.csv' 
WITH ( FIELDTERMINATOR =',', FIRSTROW = 2 )

GO

INSERT INTO dbo.[On_Time_Performance] (
[Year],
[Month],
[DayofMonth],
[FlightDate],
[Carrier],
[Origin],
[Dest],
[DepDelayMinutes],
[ArrDelayMinutes])
SELECT [Year], [Month], [DayofMonth], [FlightDate], [Carrier], [Origin],
[Dest], [DepDelayMinutes], [ArrDelayMinutes]
FROM dbo.[FlightDataTemp]

GO

DROP TABLE [FlightDataTemp]

GO