演習 - テーブルの作成、一括インポート、データのクエリを実行する

  • 10 分

大学では現在、データは一連のコンマ区切りファイルに格納されています。 あなたは、このデータを Azure SQL Database に移行する必要があります。

この演習では、SQL Database サービスを使用することで、データベース サーバーと単一データベースを作成します。 次に、テーブルを作成しデータベースにデータをインポートします。 最後に、クエリ エディターと sqlcmd ユーティリティを使用してデータのクエリを実行します。

既存のコンマ区切りのデータを調べる

  1. Azure Cloud Shell で、次のコマンドを実行して、大学のシステムのデータ ファイルとアプリケーション コードをダウンロードします。

    git clone https://github.com/MicrosoftDocs/mslearn-develop-app-that-queries-azure-sql education

  2. 次のコマンドを実行してサンプル データを固有のフォルダーに移動し、そのフォルダー内のファイルを一覧表示します。

    mv ~/education/data ~/educationdata
cd ~/educationdata
ls

    このフォルダーには、courses.csv、modules.csv、studyplans.csv の 3 つのファイルが含まれています。

  3. courses.csv ファイルの内容を表示します。

    cat courses.csv

    このファイルには、次のコンマ区切りデータが含まれています。 それには、大学で行われているコースの名前と各コースの ID が含まれます。

    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

  4. 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

  5. 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 Database を使用してデータベース サーバーとデータベースを作成する

アプリのデータを格納するデータベースとサーバーを作成しましょう。

  1. サンドボックスのアクティブ化に使ったアカウントを使って、Azure portal にサインインします。

  2. Azure portal メニューの [Azure サービス] の下の [リソースの作成] を選択します。

    Screenshot of Azure portal menu and Create a resource option.

    [リソースの作成] ウィンドウが表示されます。

  3. 左側のメニュー ペインで、[データベース] を選択し、[一般的な Azure サービス] の下にある [SQL Database] を選択します。

    Screenshot of the Databases and SQL Database options.

    [SQL Database の作成] ペインが表示されます。

  4. [基本] タブで、各設定に対して次の値を入力します。

    設定 値
    プロジェクトの詳細
    サブスクリプション コンシェルジェ サブスクリプション
    リソース グループ [サンドボックス リソース グループ]
    データベースの詳細
    データベース名 データベースは一意の名前にする必要があります。 coursedatabaseNNN のようにすることをお勧めします (NNN はランダムな数字)。
    サーバー [新規作成] リンクを選択し、[新しいサーバー] パネルで、次の表に示されている詳細を入力します。
    SQL エラスティック プールを使用しますか? いいえ
    コンピューティングとストレージ General Purpose

    サーバーについては、各設定に次の値を入力します。

    設定 値
    サーバー名 courseserverNNN (NNN はデータベースに選択したのと同じ番号)
    サーバー管理者のログイン azuresql
    パスワード 要件を満たすパスワードを入力します。
    パスワードの確認 パスワードを確認します。
    場所 米国中部

  5. [OK] を選択します。

  6. [次へ: ネットワーク] を選択します。

  7. [ネットワーク] タブで、各設定に対して次の値を入力します。

    設定 値
    ネットワーク接続
    接続方法 パブリック エンドポイント
    ファイアウォール規則
    Azure のサービスとリソースにこのサーバーへのアクセスを許可する はい
    現在のクライアント IP アドレスを追加する はい

  8. [Review + create](レビュー + 作成) を選択します。

  9. [作成] を選択します。 サーバーとデータベースが作成されるのを待ってから、続行します。

テーブルを作成する

.csv ファイルのデータを格納するテーブルを作成できます。

  1. [リソースに移動] を選択します。 coursedatabaseNNN の SQL データベースが表示されます。

  2. 左側のメニュー ペインで [クエリ エディター (プレビュー)] を選択します。

    The database page in the Azure portal with the query editor option highlighted.

    coursedatabaseNNN の [クエリ エディター] ペインが表示されます。

  3. 各設定に対して次の値を入力します。

    設定 値
    SQL Server 認証
    ログイン azuresql
    パスワード このユーザーを作成したときに使用したパスワードを指定します。

    Note

    データベースへのサインインでエラーが発生した場合は、エラーに示されている IP を確認し、クライアント IP として追加されたものであることを確認してください。 これを行うには、[概要]>[サーバー ファイアウォールの設定] を選択します。

  4. [OK] を選択して、データベース サービスに接続します。

  5. [クエリ 1] ウィンドウで、次の Transact-SQL (T-SQL) ステートメントを入力して、[実行] を選択します。 このステートメントにより、コース情報を保持するための新しいテーブルが作成されます。 ステートメントがエラーをまったく発生させずに実行されることを確認します。

    CREATE TABLE Courses
(
    CourseID INT NOT NULL PRIMARY KEY,
    CourseName VARCHAR(50) NOT NULL
)

    The Query editor window in the Azure portal. The user has entered a statement to create the Courses table.

  6. 既存のステートメントを、モジュールを保持するためのテーブルを作成する次のステートメントで上書きします。 [実行] を選択して、ステートメントがエラーなく実行されることを確認します。

    CREATE TABLE Modules
(
    ModuleCode VARCHAR(5) NOT NULL PRIMARY KEY,
    ModuleTitle VARCHAR(50) NOT NULL
)

  7. StudyPlans という名前のテーブルを作成するようにステートメントを変更して、[実行] を選択します。

    CREATE TABLE StudyPlans
(
    CourseID INT NOT NULL,
    ModuleCode VARCHAR(5) NOT NULL,
    ModuleSequence INT NOT NULL,
    PRIMARY KEY(CourseID, ModuleCode)
)

  8. データベース ウィンドウで、ツールバーの [更新] アイコンを選択します。 [テーブル] を展開し、順番に各テーブルを展開します。 3 つのテーブル (dbo.Courses、dbo.Modules、dbo.StudyPlans) と共に、各テーブルの列と主キーが表示されます。

    Note

    dbo は、database owner (データベース所有者) を表します。 それがデータベースでの既定のスキーマです。 3 つのテーブルはすべてこのスキーマで作成されました。

    The database window in the Azure portal, showing the tables and columns.

データをインポートする

  1. Cloud Shell に戻り、educationdata フォルダーにいることを確認します。

    cd ~/educationdata

  2. 後の手順で使用する変数を作成します。 NNN をデータベースとサーバーに使用した番号に置き換えます。

    export DATABASE_NAME=coursedatabaseNNN
export DATABASE_SERVER=courseserverNNN
export AZURE_USER=azuresql
export AZURE_PASSWORD=[enter your password]

  3. 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

  4. コード エディターで、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

  5. ファイルを確認します。 コンマ区切りファイルの最初の列のデータは dbo.Courses テーブルの CourseID 列になります。 2番目のフィールドは CourseName 列になります。 2 列目は文字ベースで、照合順序が関連付けられています。 ファイルのフィールド区切り記号はコンマである必要があります。 行ターミネータ (2 番目のフィールドの後) は、改行文字である必要があります。 実際のシナリオでは、データがこれほど整然とまとまっていない可能性があります。 フィールド区切り記号が異なり、フィールドが列とは異なる順序になっている場合があります。 そのような状況では、フォーマット ファイルを編集して、フィールド単位でこれらの項目を変更できます。 Ctrl + Q キーを押してエディターを閉じます。

  6. 次のコマンドを実行し、修正済みの courses.fmt ファイルで指定されている形式で、courses.csv ファイル内のデータをインポートします。 -F 2 フラグでは、データ ファイルの 2 行目からデータのインポートを開始するよう bcp ユーティリティに指示されます。 1 行目にはヘッダーが含まれています。

    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 ユーティリティによって 9 つの行がインポートされ、エラーが報告されていないことを確認します。

  7. 次の一連の操作を実行し、modules.csv ファイルから dbo.Modules テーブルのデータをインポートします。

    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. データベースの dbo.Modules テーブルに modules.csv ファイルからデータをインポートします。

      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 行がインポートされることを確認します。

  8. 次の一連の操作を実行して、studyplans.csv ファイルから dbo.StudyPlans テーブルのデータをインポートします。

    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. データベースの dbo.StudyPlans テーブルに studyplans.csv ファイルからデータをインポートします。

      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 行がインポートされることを確認します。

データベース内のデータのクエリを実行する

  1. Azure portal に戻ります。

  2. Azure portal のメニューで、[SQL データベース] を選択します。

  3. [SQL データベース] ペインで、coursedatabaseNNN を選択します。 [coursedatabaseNNN] ペインが表示されます。

  4. 左側のメニュー ペインで [クエリ エディター] を選択します。 coursedatabaseNNN の [クエリ エディター] ペインが表示されます。

  5. 各設定に対して次の値を入力します。

    設定 値
    SQL Server 認証
    ログイン azuresql
    パスワード このユーザーのパスワードを入力します。

  6. [OK] を選択して、データベース サービスに接続します。

  7. [クエリ 1] ウィンドウで、次の T-SQL ステートメントを入力して、[実行] を選択します。

    SELECT * FROM dbo.Courses

    このステートメントにより dbo.Courses テーブルからデータが取得されます。 結果ウィンドウには、9 つの行が表示されます。

    Screenshot of the query editor in the Azure portal, showing the data retrieved from the Courses table.

  8. 次のようにクエリを変更して、[実行] を選択します。

    SELECT * FROM dbo.Modules

    今度は、[結果] ウィンドウにモジュールが表示されるはずです。 16 行あります。

  9. Cloud Shell に戻り、次のコマンドを実行してデータベースに接続します。

    sqlcmd -S "$DATABASE_SERVER.database.windows.net" -d "$DATABASE_NAME" -U $AZURE_USER -P $AZURE_PASSWORD

  10. 1> プロンプトで次の T-SQL コマンドを実行して、dbo.StudyPlans テーブルからデータをフェッチします。

    SELECT * FROM StudyPlans;  
GO

    このクエリにより 45 行が返されるはずです。

  11. 1> プロンプトで、exit を入力して sqlcmd ユーティリティを閉じます。

SQL Database を使用して単一データベースを作成しました。 次に、Azure portal でクエリ エディターを使用して、テーブルを作成しました。 その後、bcp ユーティリティを使用して、一連のコンマ区切りのデータ ファイルからデータをアップロードしました。 最後に、Azure portal のクエリ エディターと、Cloud Shell の sqlcmd ユーティリティから、データベース内のテーブルに対しクエリを実行しました。