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

完了

大学では、現在テキスト ファイルに存在するデータを格納する場所を必要としています。 データにアクセスする機能を向上させるために、データをリレーショナルにすることが考えられています。 このデータのストレージ サービスとして、Azure SQL Database での単一データベースを選択しました。 SQL Database でデータをアップロードしてクエリを実行する方法を見てみましょう。

Azure portal を使用して単一データベースを作成する

SQL Database とは、Microsoft SQL Server データベース エンジンの安定した最新バージョンに基づく、リレーショナル データベース サービスです。 SQL Database は、信頼性と安全性が高く使いやすい高パフォーマンスのデータベースです。 SQL Database を使うと、任意のプログラミング言語で新しいアプリ、Web サイト、マイクロサービスを構築でき、インフラストラクチャを管理する必要はありません。

単一データベースは、Azure portal で、または Azure PowerShell や Azure CLI を使って、作成できます。

  1. Azure portal のメニューで、[リソースの作成] を選択します。

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

  2. [データベース][SQL Database] の順に選択します。

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

  3. CLI を使う場合は、az sql server create コマンドと az sql db create コマンドを実行します。

  4. PowerShell を使う場合は、New-AzSqlServer コマンドと New-AzSqlDatabase コマンドを実行します。

単一データベースを作成するときは、それを管理するサーバーの指定を求められます。 新しいサーバーを作成することも、既存のサーバーを使うこともできます。

新しいサーバーを作成する場合は、サーバー管理者のユーザー名とパスワードの指定を求められます。 これらの資格情報を使用って、サーバーに接続して管理タスクを行ったり、サーバーによって制御されるデータベースにアクセスしたりします。 SQL Database では、Azure Active Directory (Azure AD) 認証もサポートされています。 ただし、新しいサーバーを作成するときは、常に管理者アカウントを作成し、Azure AD に格納されているアカウントへのアクセスを許可する必要があります。

各データベース サーバーは、潜在的に悪意のあるプロセスをブロックするため、ファイアウォールによって保護されます。 他の Azure サービスに対してファイアウォールを開くことができます。 また、IP アドレスまたはアドレス範囲に基づいて、他のコンピューターへのアクセスを選択的に有効にすることができます。 SQL Database には、次のことを可能にする高度なデータ セキュリティも用意されています。

  • テーブル内の個々の列のデータの機密度を指定する。
  • データベースの脆弱性を評価し、必要な修復手順を実行する。
  • 脅威が検出されたときにアラートを送信する。

リソースをプロビジョニングするには、仮想コア (vCore) モデルを使用します。このモデルでは、割り当てるリソース (メモリ、I/O、CPU) を指定します。 コンピューティング リソースとストレージ リソースを個別にスケーリングできます。 または、データベース トランザクション ユニット (DTU) の観点からリソースを割り当てることができます。 DTU は、ベンチマークされたトランザクションを実行するために必要なリソースの調整されたコストの尺度です。

複数のデータベースがあり、データベースのリソース ニーズが変動する場合は、SQL エラスティック プールを使用できます。 この機能により、必要に応じて、プールされたデータベース間でリソースのプールを共有できます。

データベースを作成するときは、データの照合方法も指定します。 "照合順序" によって、データベースでデータの並べ替えと比較に使用されるルールが定義されます。 また、テキスト データに使用される文字セットも指定されます。 データベースを作成した後で照合順序を変更できますが、データベースにデータが含まれるようになった後では変更しないことをお勧めします。

テーブルを作成する

テーブルを作成するには、次のいずれかのツールを使用できます。

  • Azure portal のクエリ エディター
  • sqlcmd ユーティリティと Cloud Shell
  • SQL Server Management Studio

いずれのツールを選択しても、CREATE TABLE SQL コマンドを使用してテーブルを定義します。 SQL Database では、テーブルでの主キー、外部キー、インデックス、トリガーがサポートされています。 次のサンプル コードでは、関連テーブルと非クラスター化インデックスのペアが作成されています。 これらのコマンドは、クエリ エディターまたは 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 portal でクエリ エディターにアクセスするには、データベースのページに移動して、[クエリ エディター] を選択します。 資格情報の入力を求められます。 [認証の種類][SQL Server 認証] に設定し、データベースの作成時に設定したユーザー名とパスワードを入力できます。 または、[Active Directory パスワード認証] を選択し、Azure AD で承認されたユーザーの資格情報を指定することもできます。 Active Directory シングル サインオンが有効になっている場合は、自分の Azure ID を使用して接続できます。

Azure portal の SQL Database サインイン ページ。

クエリ ウィンドウで SQL コードを入力し、[実行] をクリックしてそれを実行します。 SQL ステートメントがクエリの場合、返されたすべての行が [結果] ウィンドウに表示されます。 [メッセージ] ウィンドウには、返された行の数や、発生したエラーなどの情報が表示されます。

各種ペインが強調表示された Azure portal のクエリ エディター。

sqlcmd ユーティリティを使用するには、Cloud Shell に移動し、次のコマンドを実行します。 <server> を作成したデータベース サーバーの名前に、<database> をご自分のデータベースの名前に、<user name><password> をご自分の資格情報にそれぞれ置き換えます。

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

サインイン コマンドが成功すると、1> プロンプトが表示されます。 複数行の SQL コマンドを入力してから、「GO」と入力してそれらを実行できます。

bcp を使用してデータを一括インポートする

Microsoft では、SQL データベースにデータをアップロードするために使用できるいくつかのツールが用意されています。

  • SQL Server Integration Services (SSIS)
  • SQL の BULK INSERT ステートメント
  • 一括コピー プログラム (bcp) ユーティリティ

bcp ユーティリティは便利で、複数のテーブルへのデータのインポートを簡単にスクリプト化できるため、よく使用されています。 bcp ツールは、データをデータベースにインポートまたはデータベースからエクスポートできるコマンド ライン ユーティリティです。 bcp でデータをインポートするには、次の 3 つのものが必要です。

  • アップロードするソース データ
  • インポート先データベース内の既存テーブル
  • データの形式と、データをインポート先のテーブル内の列にマップする方法が定義されている、"フォーマット ファイル"

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

最初の行にはフィールド名が含まれています。これはテーブル内の列と同じではありません。 データはコンマで区切られ、各行は改行文字で終了します。 ファイル内の列の順序は、テーブルとは異なる場合があることに注意してください。 この例では、次に示すように、テーブルの 1 列目は数値で、2 列目は文字列です。

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 Database の内部バージョン番号が示されています。 2 行目では、ソース テーブルの列の数が示されています。 最後の 2 行では、ソース ファイル内のデータをそれらの列にマップする方法が示されています。

どちらの行も数字で始まっています。 これらはテーブル内の列番号です。 2 番目のフィールド (SQLCHAR) では、このフォーマット ファイルを使ってデータをインポートするときは、ソース ファイル内の各フィールドに文字データが含まれることが指定されています。 bcp ユーティリティにより、このデータから、テーブル内の対応する列に適した型への変換が試みられます。 次のフィールド (12 と 50) は、データベース内の各列のデータの長さです。 "これは変更しないでください。" 次の項目 ("," と "\n") は、それぞれ、ソース ファイル内のフィールド ターミネータと、改行文字です。 次の列は、ソース ファイル内のフィールド番号です。 最後から 2 番目のフィールド (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 列目にマップされます。 フィールド 1 は 2 列目にマップされます。

これで、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 行目で開始することを示しています。 1 行目にはデータではなく、ヘッダーが含まれていることを思い出してください。

コマンドは正常に実行されて、次の例のようなメッセージが返されるはずです。

Starting copy...

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

重要な行は "5 rows copied." です。 これは、インポートされたデータを含むソース ファイル内の行数を示しています。 この数が異なる (または 0 の) 場合、フォーマット ファイルが正しくない可能性があります。

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

データのクエリを実行して、インポートが成功したことを確認します。 クエリ エディターは、Azure portal から使用できます。 または、sqlcmd ユーティリティを使用して、コマンド ラインからデータベースに接続することもできます。 いずれの場合も、次のような SELECT ステートメントを実行します。

SELECT *
FROM MyTable;

次のような結果が表示されます。

クエリの結果を示す Azure portal のクエリ エディター。