Microsoft SQL Server でフェデレーション クエリを実行する
この記事では、Azure Databricks で管理されていない SQL Server データに対してフェデレーション クエリを実行できるように、レイクハウス フェデレーションを設定する方法について説明します。 Lakehouse フェデレーションの詳細については、「Lakehouse フェデレーションとは?」を参照してください。
レイクハウス フェデレーションを使って SQL Server データベースに接続するには、Azure Databricks の Unity Catalog メタストアで以下を作成する必要があります。
- SQL Server データベースへの接続。
- Unity Catalog で SQL Server データベースをミラーリングする 外部カタログ。これにより、Unity Catalog のクエリ構文とデータ ガバナンス ツールを使って、Azure Databricks ユーザーのデータベースへのアクセスを管理できるようになります。
Lakehouse フェデレーションでは、SQL Server、Azure SQL Database、Azure SQL Managed Instance がサポートされています。
開始する前に
ワークスペースの要件:
- Unity Catalog を使用できるワークスペース。
コンピューティングの要件:
- Databricks Runtime クラスターまたは SQL ウェアハウスから対象となる データベース システムに接続するためのネットワーク接続。 「レイクハウス フェデレーションのためのネットワークに関する推奨事項」を参照してください。
- Azure Databricks クラスターでは、Databricks Runtime 13.3 LTS 以降と共有またはシングル ユーザー アクセス モードを使用する必要があります。
- SQL ウェアハウスは Pro またはサーバーレスである必要があり、かつ 2023.40 以上を使用する必要があります。
必要なアクセス許可:
- 接続を作成するには、メタストア管理者であるか、ワークスペースにアタッチされている Unity Catalog メタストアに対する
CREATE CONNECTION特権を持つユーザーである必要があります。 - 外部カタログを作成するには、メタストアに対する
CREATE CATALOG権限を持ち、接続の所有者であるか、接続に対するCREATE FOREIGN CATALOG特権を持っている必要があります。
追加の権限要件は、以下の各タスク ベースのセクションで規定されています。
- OAuth を使って認証を行う予定の場合は、Azure Databricks の Microsoft Entra ID にアプリを登録します。 詳細については、以下のセクションを参照してください。
(省略可能) Azure Databricks の Microsoft Entra ID にアプリを登録する
OAuth を使って認証を行う場合は、SQL Server 接続を作成する前に、この手順のようにします。 代わりにユーザー名とパスワードを使用して認証するには、このセクションをスキップしてください。
- Azure portal にサインインします。
- 左側のナビゲーションで、[Microsoft Entra ID] をクリックします。
- [アプリの登録] をクリックします。
- [新規登録] をクリックします。 新しいアプリの名前を入力し、リダイレクト URI を
https://<workspace-url>/login/oauth/azure.htmlに設定します。 - [登録] をクリックします。
- [基本] ボックスに、アプリケーション (クライアント) ID をコピーして保存します。 この値は、アプリケーションを構成するために使用します。
- [証明書とシークレット] をクリックします。
- [クライアント シークレット] タブで、[新しいクライアント シークレット] をクリックします。
- シークレットの説明と有効期限を入力します (既定の設定は 180 日です)。
- 追加をクリックします。
- クライアント シークレットの生成された値をコピーします。
- [API アクセス許可] をクリックします。
- [アクセス許可の追加] をクリックします。
- [Azure SQL Database] を選び、[委任されたアクセス許可] の下で user_impersonation をクリックします。
- アクセス許可の追加 をクリックします。
接続を作成する
接続では、外部データベース システムにアクセスするためのパスと資格情報を指定します。 接続を作成するには、Catalog Explorer を使用するか、Azure Databricks ノートブックまたは Databricks SQL クエリ エディターで CREATE CONNECTION SQL コマンドを使用します。
Note
Databricks REST API または Databricks CLI を使用して接続を作成することもできます。 POST /api/2.1/unity-catalog/connections および Unity Catalog コマンドを参照してください。
必要な権限: メタストア管理者、または CREATE CONNECTION 特権を持つユーザー。
カタログ エクスプローラー
Azure Databricks ワークスペースで、
[カタログ] をクリックします。[カタログ] ペインの上部にある
[追加] アイコンをクリックし、メニューから [接続の追加] を選択します。または、[クイック アクセス] ページで、[外部データ ] ボタンをクリックし、[接続] タブに移動し、[接続の作成] をクリックします。
わかりやすい接続名を入力します。
SQL Server の [接続の種類] を選択します。
[認証の種類] に [OAuth] または [ユーザー名とパスワード] を選択します。
使用する認証方法に応じて、SQL Server インスタンスの接続プロパティを以下のように入力します。
- ホスト: 使用する SQL サーバー。
- (基本認証) ポート
- (基本認証) trustServerCertificate: 既定値は
falseです。trueに設定されている場合、トランスポート層で SSL を使用してチャネルが暗号化され、証明書チェーンによる信頼性の検証がバイパスされます。 信頼性の検証をバイパスする必要がある場合を除き、この設定は既定値のままにします。 - (基本認証) ユーザー
- (基本認証) パスワード
- (OAuth) 承認エンドポイント:
https://login.microsoftonline.com/<tenant-id>/oauth2/v2.0/authorize形式の Azure Entra 承認エンドポイント。 - (OAuth) クライアント ID: 作成したアプリから。
- (OAuth) クライアント シークレット: 作成したクライアント シークレットから。
- (OAuth) クライアント スコープ: 次の値を修正せずに入力します:
https://database.windows.net/.default offline_access。 - (OAuth) サインインして Azure Entra ID でログインするように求められます。 Azure のユーザー名とパスワードを入力します。 接続の作成ページにリダイレクトされた後、承認コードが UI に表示されます。
(省略可能) [接続のテスト] をクリックして、動作することを確認します。
(省略可能) コメントを追加します。
Create をクリックしてください。
Note
(OAuth) Azure Entra ID の OAuth エンドポイントには、Azure Databricks コントロール プレーンの IP からアクセスできる必要があります。 「Azure Databricks のリージョン」を参照してください。
SQL
ノートブックまたは Databricks SQL クエリ エディターで次のコマンドを実行します。
CREATE CONNECTION <connection-name> TYPE sqlserver
OPTIONS (
host '<hostname>',
port '<port>',
user '<user>',
password '<password>'
);
資格情報などの機密性の高い値には、プレーンテキストの文字列ではなく Azure Databricks のシークレットを使用することをお勧めします。 次に例を示します。
CREATE CONNECTION <connection-name> TYPE sqlserver
OPTIONS (
host '<hostname>',
port '<port>',
user secret ('<secret-scope>','<secret-key-user>'),
password secret ('<secret-scope>','<secret-key-password>')
)
シークレットの設定については、「シークレットの管理」を参照してください。
外部カタログを作成する
外部カタログは、外部データ システム内のデータベースをミラーリングし、Azure Databricks と Unity Catalog を使ってそのデータベース内のデータに対するクエリの実行とアクセス管理ができるようにします。 外部カタログを作成するには、定義済みのデータ ソースへの接続を使用します。
外部カタログを作成するには、Catalog Explorer を使用するか、Azure Databricks ノートブックまたは SQL クエリ エディターで CREATE FOREIGN CATALOG SQL コマンドを使用します。
Note
Databricks REST API または Databricks CLI を使用してカタログを作成することもできます。 POST /api/2.1/unity-catalog/catalogs および Unity Catalog コマンドを参照してください。
必要なアクセス許可: メタストアに対する CREATE CATALOG アクセス許可と、接続の所有権または接続に対する CREATE FOREIGN CATALOG 特権。
カタログ エクスプローラー
Azure Databricks ワークスペースで、
[カタログ] をクリックしてカタログ エクスプローラーを開きます。[カタログ] ペインの上部にある
[追加] アイコンをクリックし、メニューから [カタログの追加] を選択します。または、[クイック アクセス] ページで、[カタログ] ボタンをクリックし、[カタログの作成] ボタンをクリックします。
「カタログを作成する」で外部カタログを作成する手順に従います。
SQL
ノートブックまたは SQL クエリ エディターで次のコマンドを実行します。 角かっこ内の項目は省略可能です。 プレースホルダー値を次のように置き換えます。
<catalog-name>: Azure Databricks 内のカタログの名前。<connection-name>: データ ソース、パス、アクセス資格情報を指定する接続オブジェクト。<database-name>: Azure Databricks でカタログとしてミラーリングするデータベースの名前。
CREATE FOREIGN CATALOG [IF NOT EXISTS] <catalog-name> USING CONNECTION <connection-name>
OPTIONS (database '<database-name>');
サポートされているプッシュダウン
すべてのコンピューティングで以下のプッシュダウンがサポートされています:
- フィルター
- プロジェクション
- 制限
- 関数: 部分的。フィルター式の場合のみ。 (文字列関数、数学関数、データ関数、時刻関数、タイムスタンプ関数、その他 Alias、Cast、SortOrder などの関数)
Databricks Runtime 13.3 LTS 以上および SQL ウェアハウス コンピューティングでは、以下のプッシュダウンがサポートされています。
- 集計
- ブール演算子: =、<、<=、>、>=、<=>
- 数学関数 (ANSI が無効な場合はサポートされません): +、-、*、%、/
- その他の演算子: ^、|、~
- 制限付きで使用した場合の並べ替え
以下のプッシュダウンはサポートされていません。
- 結合
- Windows 関数
データ型マッピング
SQL Server から Spark に読み取ると、データ型は次のようにマップされます。
| SQL Server の型 | Spark の型 |
|---|---|
| bigint (無署名)、decimal、money、numeric、smallmoney | DecimalType |
| smallint、tinyint | ShortType |
| int | IntegerType |
| bigint (署名されている場合) | LongType |
| real | FloatType |
| float | DoubleType |
| char、nchar、uniqueidentifier | CharType |
| nvarchar、varchar | VarcharType |
| text、xml | StringType |
| binary、geography、geometry、image、timestamp、udt、varbinary | BinaryType |
| bit | BooleanType |
| date | DateType |
| datetime、datetime、smalldatetime、time | TimestampType/TimestampNTZType |
*SQL Serverから読み取ると、preferTimestampNTZ = false (既定値) の場合、SQL Server datetimes は Spark TimestampType にマップされます。 preferTimestampNTZ = true の場合、SQL Server datetimes は TimestampNTZType にマップされます。