SQL を使用してデータを照会

  • [アーティクル]

[このトピックはプレリリース ドキュメントであり、変更されることがあります。 なお、SQL Server Management Studio と .NET ライブラリによる SQL データ接続のみプレビュー版です。 Power BI は一般提供 (GA) です]

Microsoft Dataverse ビジネス レイヤーは、SQL データ接続をエミュレートする表形式データ ストリーム (TDS) エンドポイントを提供します。 SQL 接続では、ターゲット Dataverse 環境のテーブル データへの読み取り専用アクセスが提供されるため、Dataverse データ テーブルに対して SQL クエリを実行できます。 データのカスタムビューは提供されていません。 Dataverse エンドポイントの SQL 接続は、データアクセスに Dataverse セキュリティ モデルを使用しています。 ユーザーがアクセスできるすべての Dataverse テーブルのデータを取得できます。

前提条件

TDS エンドポイントを有効にするの設定を、ご使用の環境で有効にする必要があります。 既定では、 有効化されています。 詳細情報: 機能設定の管理

アプリケーション サポート

Power BIと SQL Server Management Studio の TDS (SQL) エンドポイント アプリケーションのサポートについては以下で説明します。

SQL Server Management Studio (プレビュー)

注意

SQL Server Management Studio 19.0.1 のビルドで互換性の問題が見つかりました。 修正については調査中です。 修正が利用可能になるまで、ビルド SQL Server Management Studio の 18.12.1 を使用してください。 このメモは、修正が利用可能になり次第更新されます。

また、SQL Server Management Studio (SSMS) バージョン 18.12.1 以降を Dataverse エンドポイント SQL 接続で使用することもできます。 SQL データ接続で SSMS を使用する例を以下に示します。

拡張アカウント テーブル。

セキュリティおよび認証

Azure Active Directory 認証のみサポートしています。 SQL 認証と Windows 認証はサポートされていません。 以下は、SSMS で SQL 接続にログオンする方法の例です。 サーバー名が組織のアドレス URL であることに注意してください。

接続ダイアログ。

注意

SSMS などのクライアント アプリケーションから TDS エンドポイントを使用するには、ポート 1433 または 5558、あるいはその両方を有効にする必要があります。 5558 番ポートだけを有効化した場合、SSMS の サーバーに接続する ダイアログでユーザーがそのポート番号をサーバー名に追加する必要があります - 例: myorgname.crm.dynamics.com,5558。

エンドポイント暗号化に関する情報: 転送中のデータ保護

テーブル データ クエリの例

以下は、SSMS で構成されるクエリのいくつかの例です。 最初の画像は、エイリアスと結果の順序を使用した簡単なクエリを示しています。

select top 5 a.name as [VIP customer], a.address1_postalcode as [ZIP code] from account a order by a.address1_postalcode desc

エイリアスと順序を使用した簡単なクエリ。

次のクエリは、JOIN を示しています。

select name, fullname from account a inner join contact c on a.primarycontactid = c.contactid

JOIN を使用した別のクエリ。

Power BI (一般提供)

Power Apps (https://make.powerapps.com) の Power BI で分析 オプション (データ > テーブル > Power BI で分析) を使用すると、Dataverse コネクタを使用して Power BI Desktop でデータを分析できます。 詳細: Power BI Desktop でのテーブル データの表示

注意

この機能を有効にするには、機能設定を管理する の TDS エンドポイント設定を参照してください。 有効にすると、Power Apps のコマンドバーで Power BI で分析 ボタンが表示されます。

サポートされている操作とデータ型

データを変更しようとする操作 (つまり、INSERT、UPDATE) は、この読み取り専用の SQL データ接続では機能しません。 Dataverse エンドポイントでサポートされている SQL 操作の詳細なリストについては、Dataverse SQL がいかに Transact-SQL と異なる点 を参照してください。

以下の Dataverse データ型は SQL 接続では対応していません: binary、image、sql_variant、varbinary、virtual、HierarchyId、managedproperty、file、xml、partylist、timestamp、choices。 また、現時点では、テーブル タイプ「virtual」および「audit」はサポートされていません。

ヒント

代わりに、partylist 属性は、以下のように activityparty テーブルに結合して問い合わせを行うことができます。

select act.activityid, act.subject, string_agg([to].partyidname, ', ')
from activitypointer as act
left outer join activityparty as [to] on act.activityid = [to].activityid and [to].participationtypemask = 2
group by act.activityid, act.subject

検索列タイプの動作

Dataverse 検索列は、結果セットの <lookup> ID と <lookup> 名で表されます。

選択列タイプの動作

Dataverse 選択列は、結果セットの <choice> 名と <choice> ラベルで表されます。

ヒント

選択列のラベルを変更した後、テーブルのカスタマイズを公開する必要があります。

注意

クエリに多数の選択ラベルを含めると、パフォーマンスに大きな影響を与えます。 可能であれば、10 未満のラベルを使用することをお勧めします。 選択ラベルはローカライズされているため、ローカライズされた文字列を返すとコストが高くなります。

報告済みの SQL バージョン

Dataverse TDS エンドポイントは、Dataverse ビジネス ロジックを介して Microsoft SQL Server 読み取り専用クエリ機能をエミュレートします。 Dataverse は select @@version に対して現在の SQL Azure バージョン 12.0.2000.8 を返します。

操作ガイド

TDS エンドポイント を介してデータを取得する場合、使用する必要があるいくつかの重要なクエリ パターンがあります。 以下で説明するように、これらのクエリ パターンはパフォーマンスと結果セットのサイズを管理します。

必要な列のみ

クエリを作成するときは、必要な列のみを返します。 これは、クエリの実行と、クライアント アプリケーションへの結果の転送の両方に役立ちます。 一般に、クエリを 100 列未満に保つことをお勧めします。

列の選択

選択肢の列が 2 つの列にフラット化され、使いやすくなりました。 ただし、選択列の値部分に対して集計やフィルター処理を行うことが重要です。 値の部分はインデックスを持つことができ、ベース テーブルに格納されます。 ただし、ラベル部分 ('choicecolumn' 名) は個別に保存されるため、取得にコストがかかり、インデックスを作成できません。 多数の選択ラベル列を使用すると、非常に遅いクエリが生成される可能性があります。

上位 X を使用

データのテーブル全体を返そうとするのを防ぐために、クエリでトップ句を使用することは非常に効果的です。 たとえば、Select Top 1000 accountid,name From account Where revenue > 50000 を使用すると、結果が最初の 1000 アカウントに制限されます。

NOLOCK は使用しないでください

クエリを作成するときは、テーブル ヒント NOLOCK を使用しないでください。 これは、Dataverse クエリの最適化を妨げます。

制限

Dataverse エンドポイントから返されるクエリ結果には、80 MB の最大サイズ制限があります。 80 MB を超えるデータを返す大規模なデータ クエリの場合、Azure Synapse Link for Dataverse や データフロー などのデータ統合ツールの使用を検討してください。 詳細情報: データのインポートとエクスポート

ヒント

返却されるデータのサイズを許容範囲内に収めるため、複数行のテキスト列や選択肢の列は可能な限り少なくしてください。

クエリ結果で返される日付は、協定世界時 (UTC) としてフォーマットされます。 以前は、日付は現地時間で返されていました。

SQL を使ってデータを取得しても、RetrieveMultipleRequest や RetrieveRequest のメッセージに登録されているプラグインがトリガーされません。 そのため、このようなプラグインによって通常実行されるクエリや結果の書き換えは、SQL クエリでは有効になりません。

TDS エンドポイントの実行を使用したクエリは、サービス保護 API 制限の下で実行されます。

接続に関する問題のトラブルシューティング

以下は、いくつかの既知のエラー状態とそれらを解決する方法です。

注意

SSMS などのクライアント アプリケーションから TDS エンドポイントを使用するには、ポート 1433 または 5558、あるいはその両方を有効にする必要があります。 5558 番ポートだけを有効化した場合、SSMS の サーバーに接続する ダイアログでユーザーがそのポート番号をサーバー名に追加する必要があります - 例: myorgname.crm.dynamics.com,5558。

認証

Azure Active Directory 認証のみが Dataverse エンドポイント SQL 接続でサポートされています。 推奨される認証メカニズムは、"Azure Active Directory – 多要素認証 (MFA) を備えた "ユニバーサル" です。 しかしながら、"Azure Active Directory – パスワード" は、MFA が構成されていない場合に機能します。 他の形式の認証を使用しようとすると、次のようなエラーが表示されます。

  • Azure Active Directory – 統合型 認証使用時にエラーが返される。

"ログインに失敗しました: HTTP 要求はクライアント認証スキーム '匿名' で禁止されました。 RequestId: TDS;81d8a4f7-0d49-4d21-8f50-04364bddd370;2 Time: 2020-12-17T01:10:59.8628578Z (.Net SqlClient データ プロバイダー)”

  • SQL サーバー 認証の使用時にエラーが返される。

“ログイン エラー: この要求は認証されていません。 RequestId: TDS;918aa372-ccc4-438a-813e-91b086355343;1 Time: 2020-12-17T01:13:14.4986739Z (.Net SqlClient データ プロバイダー)”

  • Windows 認証使用時にエラーが返される。

“ログイン エラー: この要求は認証されていません。 RequestId: TDS;fda17c60-93f7-4d5a-ad79-7ddfbb917979;1 Time: 2020-12-17T01:15:01.0497703Z (.Net SqlClient データ プロバイダー)”

ブロックされたポート

ブロックされたポート エラーは、次のようになります。

エラー メッセージ。

解決策は、クライアントからの TCP ポート 1433 または 5558 のブロックが解除されていることを確認することです。 これを行うには、以下のいずれかの方法を使用します。

PowerShell を使用して TDS エンドポイントとの接続を検証します

  1. PowerShell コマンド ウィンドウを開きます。
  2. 接続テストのコマンドを実行します。
    Test-NetConnection -ComputerName <environment>.crm.dynamics.com -port 1433

接続が成功すると、「TcpTestSucceeded : True」という行が返されます。

場合によっては、トラフィックが IP レベルで直接ブロックされることがあります。 IP アドレスも機能していることを検証するには、上記のドメイン テスト接続から返された IP アドレスを取得し、ComputerName パラメーター値を IP アドレスに置き換えます。

  1. 上記のコマンドから返されたアドレスを "RemoteAddress" とします
  2. Test-NetConnection -ComputerName <RemoteAddress> -port 1433 を実行します

これにより、"TcpTestSucceeded : True" が返されます

TDS エンドポイントへの Telnet セッションを確立します

  1. Microsoft Windows コンピュータで、telnet をインストール/有効にします。
    1. 開始を選択します。
    2. コントロール パネルを選択します。
    3. プログラムと機能を選択します。
    4. Windows の機能の有効化または無効化を選択します。
    5. Telnet クライアント オプションを選択します。
    6. OK を選択します。 インストールを確認するダイアログ ボックスが表示されます。 telnet コマンドが利用可能になりました。
  2. [コマンド] ウィンドウで telnet コマンドを実行します。
    telnet <environmentname>.crm.dynamics.com 1433

接続に成功すると、アクティブな telnet セッションになります。 失敗した場合は、次のエラーが表示されます。

<environmentname>.crm.dynamics.com への接続が… ポート 1433 でホストへの接続を開くことができませんでした: 接続に失敗しました」。

これは、ポートがクライアントでブロックされていることを意味します。

参照

Dataverse SQL と Transact-SQL の違い 仮想テーブル (エンティティ) の使用を開始する
FetchXML の使用によるクエリの作成
サービス保護の API 制限

注意

ドキュメントの言語設定についてお聞かせください。 簡単な調査を行います。 (この調査は英語です)

この調査には約 7 分かかります。 個人データは収集されません (プライバシー ステートメント)。