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

10 мин

В настоящее время университет хранит данные в наборе файлов с разделителями-запятыми. Эти данные нужно перенести в Базу данных SQL Azure.

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

Изучение существующих данных с разделителями-запятыми

В Cloud Shell выполните следующую команду, чтобы скачать файлы данных и код приложения для системы университета.
```
git clone https://github.com/MicrosoftDocs/mslearn-develop-app-that-queries-azure-sql education
```
Выполните следующие команды, чтобы переместить пример данных в свою папку и вывести список файлов в папке.
```
mv ~/education/data ~/educationdata
cd ~/educationdata
ls
```
В этой папке есть три файла: courses.csv, modules.csv и studyplans.csv.
Просмотрите содержимое файла courses.csv.
```
cat courses.csv
```
Файл содержит данные с разделителями-запятыми, представленные ниже. Они включают в себя название и идентификатор каждого курса, предлагаемого университетом.
```
ID,Course
1,Computer Science
2,Maths with Computing
3,Maths with Physics
4,Computer Science with Physics
5,Maths with Chemistry
6,Physics with Chemistry
7,Maths
8,Physics
9,Chemistry
```

Просмотрите содержимое файла modules.csv.

cat modules.csv

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

Module Code,Title
CS101,Introduction to Computer Science
CS102,Java Programming
CS103,Distributed Applications
CS104,Cloud-based systems
MA101,Foundations of Applied Maths
MA102,Advanced Calculus
MA103,Number Theory
MA104,String Theory
PH101,Foundations of Physics
PH102,Basic Experimental Phyics
PH103,Basic Theoretical Physics
PH104,Subatomic Physics
CH101,Elements of Chemistry
CH102,Basic Inorganic Chemistry
CH103,Basic Organic Chemistry
CH104,Chemical Engineering

Просмотрите содержимое файла studyplans.csv.
```
cat studyplans.csv
```
Этот файл содержит сведения о том, какие модули должен пройти студент, чтобы успешно завершить курс. В столбце Sequence указывается очередность прохождения модулей. Например, в курсе 1 (Информатика) учащийся должен пройти модуль CS101, прежде чем приступать к модулю MA101. Часть этих данных показана ниже.
```
Course ID,Module Code,Sequence
1,CS101,1
1,MA101,2
1,CS102,3
1,CS103,4
1,CS104,5
2,MA101,1
2,MA102,2
2,CS101,3
2,CS102,4
2,CS103,5
3,MA101,1
3,MA102,2
3,PH101,3
3,PH102,4
3,PH103,5
...
```

Создание сервера базы данных и базы данных с помощью службы "База данных SQL"

Давайте создадим базу данных и сервер для хранения данных приложения.

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

Откроется панель Создание ресурса.
В области меню слева выберите базы данных и в разделе "Популярные службы Azure" выберите База данных SQL.

Появится панель Создание Базы данных SQL.

На вкладке Основные сведения введите указанные ниже значения для каждого параметра.

Параметр	Значение
Сведения о проектах
Отток подписок	Подписка Concierge
Группа ресурсов	[имя группы ресурсов песочницы]
Сведения о базе данных
Имя базы данных	База данных должна иметь уникальное имя. Рекомендуем использовать, например, coursedatabaseNNN, где NNN — случайное число.
Сервер	Щелкните ссылку Создать, а затем на панели Новый сервер введите сведения из таблицы ниже.
Want to use SQL elastic pool? (Нужно ли использовать эластичный пул баз данных SQL?)	No
Вычисления и хранение	Универсальные

Введите следующие значения для каждого параметра сервера.

Параметр	Значение
Имя сервера	courseserverNNN, где NNN — то же число, что вы выбрали для базы данных.
Имя для входа администратора сервера	azuresql
Password	Введите пароль, отвечающий требованиям.
Подтверждение пароля	Подтвердите пароль.
Location	Центральная часть США

Нажмите ОК.
Нажмите кнопку "Далее: сеть".

На вкладке Сеть введите указанные ниже значения для каждого параметра.

Параметр	Значение
Сетевое подключение
Метод подключения	Общедоступная конечная точка
Правила брандмауэра
Разрешить доступ к серверу службам и ресурсам Azure	Да
Добавьте текущий IP-адрес клиента	Да

Выберите Review + create (Просмотреть и создать).
Нажмите кнопку создания. Прежде чем продолжить, дождитесь создания сервера и базы данных.

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

Теперь можно приступить к созданию таблиц, в которых будут храниться данные из CSV-файлов.

Выберите Перейти к ресурсу. Появится База данных SQL для coursedatabaseNNN.
На панели меню слева выберите Редактор запросов (предварительная версия).

Появится панель Редактор запросов для NNN.

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

Параметр	Значение
Проверка подлинности SQL Server
Имя входа	azuresql
Password	Введите пароль, который вы использовали при создании этого пользователя.

Примечание.

Если при входе в базу данных возникнет ошибка, проверьте IP-адрес, указанный в сообщении об ошибке, и убедитесь, что он был добавлен в качестве IP-адреса клиента. Вы можете сделать это, выбрав Обзор>Настройка брандмауэра для сервера.

Щелкните ОК, чтобы подключиться к службе базы данных.
В области "Запрос 1" введите следующую инструкцию Transact-SQL (T-SQL), а затем нажмите кнопку "Выполнить". Эта инструкция создает таблицу для хранения сведений о курсах. Убедитесь в том, что она выполняется без ошибок.
```
CREATE TABLE Courses
(
    CourseID INT NOT NULL PRIMARY KEY,
    CourseName VARCHAR(50) NOT NULL
)
```
Замените текущую инструкцию приведенной ниже инструкцией, которая создает таблицу для сведений о модулях. Выберите Выполнить и убедитесь в том, что инструкция выполняется без ошибок.
```
CREATE TABLE Modules
(
    ModuleCode VARCHAR(5) NOT NULL PRIMARY KEY,
    ModuleTitle VARCHAR(50) NOT NULL
)
```
Измените инструкцию, чтобы создать таблицу с именем StudyPlans, а затем нажмите кнопку "Выполнить".
```
CREATE TABLE StudyPlans
(
    CourseID INT NOT NULL,
    ModuleCode VARCHAR(5) NOT NULL,
    ModuleSequence INT NOT NULL,
    PRIMARY KEY(CourseID, ModuleCode)
)
```
В окне базы данных на панели инструментов щелкните значок Обновить. Разверните узел Таблицы, а затем по очереди разверните каждую таблицу. Вы должны увидеть три таблицы (dbo.Courses, dbo.Modulesи dbo.StudyPlans), а также столбцы и первичный ключ для каждой таблицы.

Примечание.

dbo означает владельца базы данных. Это схема по умолчанию для пользователя базы данных. Все три таблицы созданы с ее использованием.

Импорт данных

Вернитесь в Cloud Shell и убедитесь, что находитесь в папке educationdata .
```
cd ~/educationdata
```
Создайте переменные, которые вы используете в последующих шагах. Замените NNN номером, который вы использовали для базы данных и сервера.
```
export DATABASE_NAME=coursedatabaseNNN
export DATABASE_SERVER=courseserverNNN
export AZURE_USER=azuresql
export AZURE_PASSWORD=[enter your password]
```
bcp Запустите программу, чтобы создать файл форматирования из схемы dbo.Courses таблицы в базе данных. Файл формата указывает, что данные в формате символов (-c) и разделены запятыми (-t,).
```
bcp "[$DATABASE_NAME].[dbo].[courses]" format nul -c -f courses.fmt -t, -S "$DATABASE_SERVER.database.windows.net" -U $AZURE_USER -P $AZURE_PASSWORD
```

В редакторе кода откройте courses.fmt. Файл формата, созданный предыдущей командой.

code courses.fmt

Файл должен выглядеть так:

14.0
2
1       SQLCHAR             0       12      ","    1     CourseID                                     ""
2       SQLCHAR             0       50      "\n"   2     CourseName                                   SQL_Latin1_General_CP1_CI_AS

Просмотрите файл. Данные в первом столбце файла, разделенного запятыми, попадают в CourseID столбец dbo.Courses таблицы. Второе поле переходит в CourseName столбец. Второй столбец является символьным, и с ним связаны параметры сортировки. В качестве разделителя полей в файле ожидается запятая. Признаком завершения строки (после второго поля) должен быть символ новой строки. В реальном сценарии данные могут не быть организованы так аккуратно. Разделители полей могут быть разными, а очередность полей может отличаться от очередности столбцов. В таком случае можно отредактировать отдельные элементы для каждого поля в файле форматирования. Нажмите CTRL+Q, чтобы закрыть редактор.
Выполните следующую команду, чтобы импортировать данные в courses.csv файле в формате, указанном в измененном courses.fmt файле. Флаг -F 2 указывает, что программа bcp должна начать импорт со строки 2 в файле данных. Первая строка содержит заголовки.
```
bcp "[$DATABASE_NAME].[dbo].[courses]" in courses.csv -f courses.fmt -S "$DATABASE_SERVER.database.windows.net" -U $AZURE_USER -P $AZURE_PASSWORD -F 2
```
Служебная программа bcp должна импортировать девять строк и не возвратить никаких ошибок.
Выполните следующую последовательность операций, чтобы импортировать данные для dbo.Modules таблицы из modules.csv файла.
1. Создайте файл форматирования.
```
bcp "[$DATABASE_NAME].[dbo].[modules]" format nul -c -f modules.fmt -t, -S "$DATABASE_SERVER.database.windows.net" -U $AZURE_USER -P $AZURE_PASSWORD
```
2. Импортируйте данные из modules.csv файла в таблицу dbo.Modules в базе данных.
```
bcp "[$DATABASE_NAME].[dbo].[modules]" in modules.csv -f modules.fmt -S "$DATABASE_SERVER.database.windows.net" -U $AZURE_USER -P $AZURE_PASSWORD -F 2
```
  Команда должна импортировать 16 строк.
Выполните следующую последовательность операций, чтобы импортировать данные для dbo.StudyPlans таблицы из studyplans.csv файла.
1. Создайте файл форматирования.
```
bcp "[$DATABASE_NAME].[dbo].[studyplans]" format nul -c -f studyplans.fmt -t, -S "$DATABASE_SERVER.database.windows.net" -U $AZURE_USER -P $AZURE_PASSWORD
```
2. Импортируйте данные из studyplans.csv файла в таблицу dbo.StudyPlans в базе данных.
```
bcp "[$DATABASE_NAME].[dbo].[studyplans]" in studyplans.csv -f studyplans.fmt -S "$DATABASE_SERVER.database.windows.net" -U $AZURE_USER -P $AZURE_PASSWORD -F 2
```
  Убедитесь в том, что эта команда импортировала 45 строк.

Запрос данных из базы данных

Вернитесь на портал Azure.
В меню портала Microsoft Azure выберите Базы данных SQL.
В области Базы данных SQL выберите coursedatabaseNNN. Появится панель coursedatabaseNNN.
На панели меню слева выберите Редактор запросов. Появится панель Редактор запросов для NNN.

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

Параметр	Значение
Проверка подлинности SQL Server
Имя входа	azuresql
Password	Введите пароль для этого пользователя

Щелкните ОК, чтобы подключиться к службе базы данных.
В области "Запрос 1" введите следующую инструкцию T-SQL и нажмите кнопку "Выполнить".
```
SELECT * FROM dbo.Courses
```
Эта инструкция извлекает данные из dbo.Courses таблицы. В окне результатов должно отобразиться девять строк.
Измените запрос, как показано ниже, и выберите Выполнить.
```
SELECT * FROM dbo.Modules
```
Теперь в окне результатов должны отобразиться модули. Показано 16 строк.
Вернитесь в Cloud Shell и выполните приведенную ниже команду, чтобы подключиться к базе данных.
```
sqlcmd -S "$DATABASE_SERVER.database.windows.net" -d "$DATABASE_NAME" -U $AZURE_USER -P $AZURE_PASSWORD
```
В командной строке 1> выполните следующую команду T-SQL, чтобы получить данные из dbo.StudyPlans таблицы.
```
SELECT * FROM StudyPlans;  
GO
```
Этот запрос должен вернуть 45 строк.
В командной строке введите, чтобы закрыть служебную 1>программу sqlcmd.exit

Вы создали отдельную базу данных с помощью службы "База данных SQL". Затем вы использовали редактор запросов на портале Azure для создания таблиц. Вы добавили в них данные из нескольких файлов с разделителями-запятыми с помощью служебной программы bcp. Наконец, вы выполнили ряд запросов к таблицам в базе данных с помощью редактора запросов на портале Azure и служебной программы sqlcmd в Cloud Shell.

Продолжить

Изучение существующих данных с разделителями-запятыми

Создание сервера базы данных и базы данных с помощью службы "База данных SQL"

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

Импорт данных

Запрос данных из базы данных

Обратная связь