SQL Serverの外部データソースとは？

こんばんは。今日はSQL Serverの外部データソースという概念に触れたので、これについて調べて分かったことをまとめておきたいと思います。

なお、筆者はSQL Server勉強中のため、理解が正しくないかもしれません。その場合には、コメント等からご指摘をいただけると大変助かります。

それでは参ります。

外部データソースとは？

意外にも公式ドキュメント等から明確な定義をみつけることができませんでしたが、検索すると先頭にヒットする以下のドキュメントから、「外部のデータソース（他のSQL Server、Oracle、MongoDBなどの他のデータベースなど）に、自データベース内の操作で接続できるようにするために作成する必要のあるオブジェクト」と理解しました。

https://docs.microsoft.com/ja-jp/sql/t-sql/statements/create-external-data-source-transact-sql?view=sql-server-ver15&tabs=dedicated

外部データソースの用途

上のドキュメントを読んで、以下の通り理解しました。

この整理から、サービスによって対応状況は微妙に異なるものの、以下の３通りの用途に使われることが分かりました。

• PolyBaseを利用したデータ仮想化とデータ読み込み
• BULK INSERTまたはOPENROWSETを使用した一括読み込み操作
• エラスティッククエリ（Azure SQL Databaseだけの用途）

前者については以下に詳しい説明があります。

PolyBase によるデータ仮想化の概要

https://docs.microsoft.com/ja-jp/sql/relational-databases/polybase/polybase-guide?view=sql-server-ver15

BULK INSERT または OPENROWSET(BULK…) を使用して SQL Server にデータをインポートする

https://docs.microsoft.com/ja-jp/sql/relational-databases/import-export/import-bulk-data-by-using-bulk-insert-or-openrowset-bulk-sql-server?view=sql-server-ver15

エラスティッククエリ

https://docs.microsoft.com/ja-jp/azure/azure-sql/database/elastic-query-getting-started-vertical

https://azure.microsoft.com/ja-jp/blog/querying-remote-databases-in-azure-sql-db/

外部データソースの作り方

サービスによって、微妙に付与するオプションや、その値のバリエーションが異なるようですが、以下のようにCREATE EXTERNAL DATA SOURCEのT-SQLによって生成することができます。

SQL Serverの場合

CREATE EXTERNAL DATA SOURCE <data_source_name>
WITH
  ( [ LOCATION = '<prefix>://<path>[:<port>]' ]
    [ [ , ] CONNECTION_OPTIONS = '<key_value_pairs>'[,...]]
    [ [ , ] CREDENTIAL = <credential_name> ]
    [ [ , ] PUSHDOWN = { ON | OFF } ]
    [ [ , ] TYPE = { HADOOP | BLOB_STORAGE } ]
    [ [ , ] RESOURCE_MANAGER_LOCATION = '<resource_manager>[:<port>]' )
[ ; ]

Azure SQL Databaseの場合

CREATE EXTERNAL DATA SOURCE <data_source_name>
WITH
  ( [ LOCATION = '<prefix>://<path>[:<port>]' ]
    [ [ , ] CREDENTIAL = <credential_name> ]
    [ [ , ] TYPE = { BLOB_STORAGE | RDBMS | SHARD_MAP_MANAGER } ]
    [ [ , ] DATABASE_NAME = '<database_name>' ]
    [ [ , ] SHARD_MAP_NAME = '<shard_map_manager>' ] )
[ ; ]

また、この外部データソース作成に先立ち、CREDENTIAL句で付与する資格情報、およびその資格情報を暗号化するためのマスターキーを作成しておく必要がありそうです。

作成した外部データソースはSSMS > 外部リソース > 外部データソースから確認できます。

以下は、Azure SQL DatabaseでBlob Storageを外部データソースに登録してCSVをBULK INSERTする例ですがご参考までに。

https://docs.microsoft.com/ja-jp/sql/t-sql/statements/bulk-insert-transact-sql?view=azuresqldb-current#f-importing-data-from-a-file-in-azure-blob-storage

外部データソースの使い方

PolyBase

工事中

BULK INSERT

こんな感じでBULK INSERT時のDATA_SOURCE引数に指定して使います。

BULK INSERT ★テーブル名★
FROM '★BLOB STORAGEに配置したファイルパス★'
WITH 
    (	
		DATA_SOURCE = '★作成した外部データソース名★',
		FIRSTROW = 2,
		FORMAT = 'CSV'
    )

こちらの詳細は以下の記事にまとめています。

以上、簡単ですが外部データソースの概要についてのまとめでした。

少しでも参考になりましたら幸いです。おしまい