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

完了

大学では現在、データは一連のコンマ区切りファイルに格納されています。 あなたは、このデータを 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.csvmodules.csvstudyplans.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 サービス] の下の [リソースの作成] を選択します。

    Azure portal メニューのスクリーンショット。リソースオプションを作成します。

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

  3. 左端のメニュー ペインで、[データベース] を選択し、[人気のあるオファー] の下にある [SQL Database] を選択します。

    データベースと SQL Database オプションのスクリーンショット。

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

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

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

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

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

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

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

    設定
    ネットワーク接続
    接続方法 パブリック エンドポイント
    ファイアウォール規則
    Azure のサービスとリソースにこのサーバーへのアクセスを許可する はい
    現在のクライアント IP アドレスを追加する はい
  8. [Review + create](レビュー + 作成) を選択します。

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

テーブルを作成する

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

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

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

    クエリ エディターのオプションが強調表示された、Azure portal のデータベース ページ。

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

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

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

    注意

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

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

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

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

    Azure portal の [クエリ エディター] ウィンドウ。 ユーザーが Courses テーブルを作成するステートメントを入力している。

  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.Coursesdbo.Modulesdbo.StudyPlans) と共に、各テーブルの列と主キーが表示されます。

    注意

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

    テーブルと列が示されている Azure portal の [データベース] ウィンドウ。

データをインポートする

  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 ユーティリティを実行して、データベースの 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. ファイルを確認します。 コンマ区切りファイルの最初の列のデータは、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. modules.csv ファイルからデータベース内の 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 行がインポートされることを確認します。

  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. studyplans.csv ファイルからデータベース内の 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 行がインポートされることを確認します。

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

  1. Azure portal に戻ります。

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

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

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

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

    設定
    SQL Server 認証
    ログイン azuresql
    パスワード このユーザーのパスワードを入力します。
  6. [OK] を選択して、データベース サービスに接続します。

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

    SELECT * FROM dbo.Courses
    

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

    Courses テーブルから取得されたデータが示されている Azure portal のクエリ エディターのスクリーンショット。

  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> プロンプトで次の SQL コマンドを実行して、StudyPlans テーブルからデータをフェッチします。

    SELECT * FROM StudyPlans;  
    GO
    

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

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

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