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

  • 10 分

大学では、現在テキスト ファイルに存在するデータを格納する場所を必要としています。 データにアクセスする機能を向上させるために、データをリレーショナルにすることが考えられています。 このデータのストレージ サービスとして、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 のメニューで、[リソースの作成] を選択します。

    Screenshot of Azure portal menu and Create a resource option.

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

    Screenshot of the Databases and SQL Database options.

  3. CLI を使用するには、az sql server create コマンドと az sql db create コマンドを実行します。

  4. PowerShell を使用するには、New-AzSqlServer コマンドと New-AzSqlDatabase コマンドを実行します。

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

新しいサーバーを作成し、SQL 認証の使用を選択する場合は、サーバー管理者のユーザー名とパスワードの指定を求められます。 これらの資格情報を使用して、サーバーに接続して管理タスクを行ったり、サーバーが制御するデータベースにアクセスしたりします。 SQL Database では、Microsoft Entra 認証もサポートされています。 SQL 認証と Microsoft Entra 認証の両方の使用を選択することもできます。 ただし、新しいサーバーを作成するときは、常に管理者を設定するか、管理者アカウントを作成する必要があります。 次に、Microsoft Entra ID に格納されているアカウントにアクセス権を付与します。

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

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

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

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

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

テーブルを作成する

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

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

いずれのツールを選択しても、CREATE TABLE Transact-SQL (T-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 パスワード認証] を選択し、Microsoft Entra ID で許可されているユーザーの資格情報を指定できます。 Active Directory シングル サインオンが有効になっている場合は、自分の Azure ID を使用して接続できます。

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 を使用してデータを一括インポートする

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 行がコピーされました" です。これはインポートされたデータを含むソース ファイル内の行の数です。 この数が異なる (または 0 の) 場合、フォーマット ファイルが正しくない可能性があります。

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

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

SELECT *
FROM MyTable;

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

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