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

  • 10 мин

Университету нужно где-то хранить данные, которые в настоящее время хранятся в текстовых файлах. Необходимо сделать данные реляционными, чтобы улучшить доступ к ним. В этих целях университет выбрал отдельную базу данных в службе "База данных SQL Azure". Давайте рассмотрим базу данных SQL и то, как передавать в нее данные и запрашивать их.

Создание отдельной базы данных с помощью портала Azure

Служба "База данных SQL" — это служба реляционных баз данных на базе последней стабильной версии ядра СУБД Microsoft SQL Server. База данных SQL — это простая в использовании, высокопроизводительная база данных, которая надежна и безопасна. Вы можете использовать База данных SQL для создания новых приложений, веб-сайтов и микрослужб на выбранном языке программирования, и вам не нужно управлять инфраструктурой.

Отдельную базу данных можно создать с помощью портала Microsoft Azure, Azure PowerShell или CLI.

  1. В меню портала Azure выберите Создать ресурс.

    Screenshot of Azure portal menu and Create a resource option.

  2. Выберите Базы данных, а затем База данных SQL.

    Screenshot of the Databases and SQL Database options.

  3. Чтобы использовать интерфейс командной az sql server create строки, выполните команды и az sql db create команды.

  4. Чтобы использовать PowerShell, выполните New-AzSqlServer команды и New-AzSqlDatabase команды.

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

Во время создания сервера с использованием проверки подлинности SQL появится запрос на указание имени и пароля администратора сервера. Используйте эти учетные данные для подключения к серверу для выполнения административных задач и доступа к базам данных, которыми управляет сервер. База данных SQL также поддерживает проверку подлинности Microsoft Entra. Вы также можете использовать проверку подлинности SQL и Microsoft Entra. Но при создании нового сервера необходимо всегда задать администратора или создать учетную запись администратора. Затем предоставьте доступ к учетным записям, хранящимся в идентификаторе Microsoft Entra.

Каждый сервер базы данных защищен брандмауэром, который блокирует доступ к потенциально вредоносным процессам. Вы можете открыть брандмауэр для других служб Azure. Можно также выборочно представить доступ другим компьютерам в зависимости от их IP-адреса или диапазона адресов, в который они входят. База данных SQL также обеспечивает расширенную защиту данных, которая позволяет:

  • Указывать конфиденциальность данных в отдельных столбцах таблиц.
  • Оценивать уязвимость баз данных и принимать необходимые меры по исправлению.
  • Отправлять оповещения при обнаружении угрозы.

Вы настраиваете ресурсы с помощью модели виртуальных ядер( виртуальных ядер), которая задает выделенные ресурсы памяти, ввода-вывода и ЦП. Вы можете масштабировать вычислительные ресурсы и ресурсы хранения независимо друг от друга. Кроме того, ресурсы можно назначать в единицах передачи данных (DTU). DTU — это откалиброванная мера затрат ресурсов, требуемых для выполнения эталонной транзакции.

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

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

Создание таблиц

Для создания таблиц можно использовать любое из этих средств:

  • редактор запросов на портале Azure;
  • служебная программа sqlcmd и Cloud Shell;
  • Среда SQL Server Management Studio

Любое выбранное средство определяет таблицу с помощью CREATE TABLE команды Transact-SQL (T-SQL). База данных SQL поддерживает первичные ключи, внешние ключи, индексы и триггеры в таблицах. В следующем примере кода создается пара связанных таблиц и некластеризованный индекс. В редакторе запросов или служебной программе sqlcmd эти команды можно выполнить в пакетном режиме.

CREATE TABLE MyTable
(
    MyColumn1 INT NOT NULL PRIMARY KEY,
    MyColumn2 VARCHAR(50) NOT NULL
);

CREATE TABLE MyTable2
(
    AnotherColumn1 INT NOT NULL,
    AnotherColumn2 INT NOT NULL REFERENCES MyTable,
    AnotherColumn3 VARCHAR(50) NULL,
    PRIMARY KEY (AnotherColumn1, AnotherColumn2)
);

CREATE INDEX cci ON MyTable2(AnotherColumn3);

Чтобы открыть редактор запросов на портале Azure, перейдите на страницу базы данных и выберите пункт Редактор запросов. Вам будет предложено указать учетные данные. Вы можете установить для параметра Тип авторизации значение Проверка подлинности SQL Server и ввести имя пользователя и пароль, которые вы установили при создании базы данных. Также можно выбрать проверку подлинности паролей Active Directory и указать учетные данные авторизованного пользователя в идентификаторе Microsoft Entra. Если включен единый вход Active Directory, подключиться можно с помощью удостоверения Azure.

The SQL Database sign-in page in the Azure portal.

Введите код T-SQL в области запроса и нажмите кнопку "Выполнить ", чтобы выполнить его. Если инструкция T-SQL является запросом, в области результатов отображаются все возвращаемые строки. В области Сообщения приводятся такие сведения, как количество возвращенных строк или возникшие ошибки.

The query editor in the Azure portal with the various panes highlighted.

Чтобы использовать служебную программу sqlcmd, перейдите в Cloud Shell и выполните указанную ниже команду. Замените <server> именем созданного сервера базы данных, <database> — именем базы данных, а <user name> и <password> — своими учетными данными.

sqlcmd -S <server>.database.windows.net -d <database> -U <username> -P <password>

Если команда входа выполнена успешно, появится 1> запрос. Команды T-SQL можно ввести в нескольких строках, а затем ввести GO для их запуска.

Массовый импорт данных с помощью bcp

Корпорация Майкрософт предоставляет несколько средств для отправки данных в базу данных SQL.

  • SQL Server Integration Services (SSIS);
  • инструкция SQL BULK INSERT;
  • программа массового копирования (BCP).

Обычно применяется программа bcp, так как она удобна в работе и поддерживает написание скриптов для импорта данных в несколько таблиц. bcp — это программа командной строки, с помощью которой можно импортировать данные в базу данных и экспортировать их из нее. Для импорта данных программе bcp требуется следующее:

  • Исходные данные для отправки.
  • Существующая таблица в целевой базе данных.
  • Файл формата, определяющий формат данных и способ сопоставления данных столбцам в целевой таблице.

Служебная программа bcp отличается большой гибкостью. Исходные данные могут иметь практически любой структурированный формат. Файл форматирования указывает макет данных и указывает, являются ли данные двоичными или символьными. Он также указывает тип и длину каждого элемента, а также способ разделения данных. В нем также указывается, как каждый элемент сопоставляется со столбцом таблицы. Содержимое этого файла должно определяться корректно. В противном случае данные могут не импортироваться или могут считываться в неправильные столбцы.

Допустим, у вас есть следующие данные в файле mydata.csv и вы хотите импортировать их в таблицу MyTable, созданную ранее.

Column1,Column2
99,some text
101,some more text
97,another bit of text
87,yet more text
33,a final bit of text

Первая строка содержит имена полей, которые не совпадают с именами столбцов таблицы. Данные разделены запятыми, и каждая строка завершается новым символом. Учитывайте, что очередность столбцов в файле может отличаться от их очередности в таблице. В этом примере первый столбец в таблице является числовым, а второй — строковым, как показано ниже.

CREATE TABLE MyTable
(
    MyColumn1 INT NOT NULL PRIMARY KEY,
    MyColumn2 VARCHAR(50) NOT NULL
);

Вы можете создать файл форматирования для импорта с помощью команды bcp. Команда bcp может создать файл форматирования на основе схемы конечной таблицы в базе данных. Затем этот файл можно изменить в соответствии с данными в исходном файле.

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

bcp <database>.dbo.mytable format nul -c -f mytable.fmt -t, -S <server>.database.windows.net -U <username> -P <password>

Служебная программа bcp имеет несколько параметров, которые управляют ее работой. Можно указать:

  • целевую таблицу (<database>.<schema>.<table>);
  • данные для импорта и сведения о данных (format nul -c -f mytable.fmt -t,);
  • сведения о подключении для базы данных (-S <server>.database.windows.net -U <username> -P <password>).

Полный синтаксис и параметры командной строки см. в справочной документации по служебной программе.

Команда создает файл формата mytable.fmt с содержимым, которые выглядят следующим образом:

14.0
2
1       SQLCHAR             0       12      ","    1     MyColumn1                                ""
2       SQLCHAR             0       50      "\n"   2     MyColumn2                                SQL_Latin1_General_CP1_CI_AS

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

Обе строки начинаются с числа, которое является номером столбца в таблице. Второе поле (SQLCHAR) указывает, что при использовании этого файла форматирования для импорта данных каждое поле в исходном файле содержит символьные данные. Программа bcp пытается преобразовать эти данные в соответствующий тип соответствующего столбца в таблице. Следующее поле (12 и 50) — это длина данных в столбце таблицы. Не изменяйте это поле! Следующие элементы ("," и "\n") — это признаки конца поля в исходном файле. "\n" представляет символ новой строки. В следующем столбце содержится номер поля в исходном файле. Второе последнее поле (MyColumn1 и MyColumn2) — это имя столбца в базе данных. По последнему полю выполняется сортировка, которая применяется только к символьным данным в базе данных.

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

14.0
2
1       SQLCHAR             0       12      ","    2     MyColumn1                                ""
2       SQLCHAR             0       50      "\n"   1     MyColumn2                                SQL_Latin1_General_CP1_CI_AS

Данные в поле 2 в исходном файле сопоставляются с первым столбцом в базе данных. Поле 1 сопоставляется со вторым столбцом.

С помощью команды bcp теперь можно импортировать данные следующим образом:

bcp <database>.dbo.mytable in mydata.csv -f mytable.fmt -S <server>.database.windows.net -U <username> -P <password> -F 2

Флаг in указывает, что для импорта данных мы используем bcp. Вы можете использовать out для передачи данных из базы данных в файл. Флаг -F 2 означает, что операция импорта должна начинаться со строки 2 в исходном файле. Напомним, что первая строка содержит заголовки, а не данные.

Команда должна быть выполнена успешно с такими сообщениями:

Starting copy...

5 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 46     Average : (108.7 rows per sec.)

Важная строка в выходных данных — "5 строк, скопированных". Количество строк в исходном файле, содержащих импортированные данные. Если значение отличается (или равно нулю), возможно, файл форматирования некорректен.

Запрос данных

Чтобы проверить, успешно ли импортированы данные, можно запросить их. Вы можете использовать редактор запросов на портале Azure. Можно также подключиться к базе данных из командной строки, используя программу sqlcmd. В обоих случаях можно выполнить такую инструкцию SELECT:

SELECT *
FROM MyTable;

Вы увидите следующие результаты:

The query editor in the Azure portal shows the results of a query.