教程:从 SQL Server 大数据群集查询 Oracle

适用于:SQL Server 2019 (15.x)

Important

Microsoft SQL Server 2019 大数据群集已停用。 对 SQL Server 2019 大数据群集的支持已于 2025 年 2 月 28 日结束。 有关详细信息,请参阅Microsoft SQL Server 平台上的公告博客文章和大数据选项。

本教程演示如何从 SQL Server 2019 大数据群集中查询 Oracle 数据。 若要运行本教程,需要有 Oracle 服务器的访问权限。 需要拥有对外部对象的读取权限的 Oracle 用户帐户。 支持 Oracle 代理用户身份验证。 如果没有访问权限,可通过本教程了解数据虚拟化如何适用于 SQL Server 大数据群集中的外部数据源。

本教程中,您将学习如何:

  • 为外部 Oracle 数据库中的数据创建外部表。
  • 将此数据与主实例中的高值数据联接起来。

Tip

如果需要,可以下载并运行本教程中的命令脚本。 有关说明,请参阅 GitHub 上的数据虚拟化示例

Prerequisites

创建 Oracle 表

以下步骤在 Oracle 中创建一个名为 INVENTORY 的示例表。

  1. 连接到要用于本教程的 Oracle 实例和数据库。

  2. 若要创建 INVENTORY 表,请运行下列语句:

     CREATE TABLE "INVENTORY"
     (
         "INV_DATE" NUMBER(10,0) NOT NULL,
         "INV_ITEM" NUMBER(10,0) NOT NULL,
         "INV_WAREHOUSE" NUMBER(10,0) NOT NULL,
         "INV_QUANTITY_ON_HAND" NUMBER(10,0)
     );
    
     CREATE INDEX INV_ITEM ON HR.INVENTORY(INV_ITEM);
    
  3. 将 inventory.csv 文件的内容导入到此表中。 此文件由先决条件部分中的示例创建脚本创建。

创建外部数据源

第一步是创建可访问 Oracle 服务器的外部数据源。

  1. 在 Azure Data Studio 中,连接到大数据群集的 SQL Server 主实例。 有关详细信息,请参阅连接到 SQL Server 主实例

  2. 双击“服务器”窗口中的连接,以显示 SQL Server 主实例的服务器仪表板 。 选择“新建查询” 。

    SQL Server 主实例查询

  3. 运行以下 Transact-SQL 命令,将上下文更改为主实例中的 Sales 数据库。

    USE Sales
    GO
    
  4. 创建数据库范围凭据以连接到 Oracle 服务器。 在以下语句中向 Oracle 服务器提供适当的凭据。

    CREATE DATABASE SCOPED CREDENTIAL [OracleCredential]
    WITH IDENTITY = '<oracle_user,nvarchar(100),SYSTEM>', SECRET = '<oracle_user_password,nvarchar(100),manager>';
    
  5. 创建指向 Oracle 服务器的外部数据源。

    CREATE EXTERNAL DATA SOURCE [OracleSalesSrvr]
    WITH (LOCATION = 'oracle://<oracle_server,nvarchar(100)>',CREDENTIAL = [OracleCredential]);
    

可选:Oracle 代理身份验证

Oracle 支持代理身份验证,以提供精细的访问控制。 代理用户使用其凭据连接到 Oracle 数据库,并在数据库中模拟另一个用户。

可以将代理用户配置为,与被模拟的用户相比,具有有限的访问权限。 例如,可以允许代理用户使用被模拟用户的特定数据库角色进行连接。 即使多个用户使用代理身份验证进行连接,通过代理用户连接到 Oracle 数据库的用户的标识也会保留在连接中。 这样,Oracle 可以强制执行访问控制,并审核代表实际用户执行的操作。

如果你的方案需要使用 Oracle 代理用户,请将前面的第 4 步和第 5 步替换为以下内容。

  1. 创建数据库范围凭据以连接到 Oracle 服务器。 在下面的语句中,向 Oracle 服务器提供适当的 Oracle 代理用户凭据。

    CREATE DATABASE SCOPED CREDENTIAL [OracleProxyCredential]
    WITH IDENTITY = '<oracle_proxy_user,nvarchar(100),SYSTEM>', SECRET = '<oracle_proxy_user_password,nvarchar(100),manager>';
    
  2. 创建指向 Oracle 服务器的外部数据源。

    CREATE EXTERNAL DATA SOURCE [OracleSalesSrvr]
    WITH (LOCATION = 'oracle://<oracle_server,nvarchar(100)>',
    CONNECTION_OPTIONS = 'ImpersonateUser=%CURRENT_USER',
    CREDENTIAL = [OracleProxyCredential]);
    

创建外部表

接下来,在 Oracle 服务器上的表上创建一个名为INVENTORY的外部表。

CREATE EXTERNAL TABLE [inventory_ora]
    ([inv_date] DECIMAL(10,0) NOT NULL, [inv_item] DECIMAL(10,0) NOT NULL,
    [inv_warehouse] DECIMAL(10,0) NOT NULL, [inv_quantity_on_hand] DECIMAL(10,0))
WITH (DATA_SOURCE=[OracleSalesSrvr],
        LOCATION='<oracle_service_name,nvarchar(30),xe>.<oracle_schema,nvarchar(128),HR>.<oracle_table,nvarchar(128),INVENTORY>');

Note

针对 Oracle 进行查询时,表名称和列名称将使用 ANSI SQL 带引号的标识符。 因此,名称区分大小写。 务必在外部表定义中指定与 Oracle 元数据中的表和列名称完全匹配的名称。

查询数据

运行以下查询,将 inventory_ora 外部表中的数据与本地 Sales 数据库中的表联接起来。

SELECT TOP(100) w.w_warehouse_name, i.inv_item, SUM(i.inv_quantity_on_hand) as total_quantity
  FROM [inventory_ora] as i
  JOIN item as it
    ON it.i_item_sk = i.inv_item
  JOIN warehouse as w
    ON w.w_warehouse_sk = i.inv_warehouse
 WHERE it.i_category = 'Books' and i.inv_item BETWEEN 1 and 18000 --> get items within specific range
 GROUP BY w.w_warehouse_name, i.inv_item;

Clean up

使用以下命令删除本教程中创建的数据库对象。

DROP EXTERNAL TABLE [inventory_ora];
DROP EXTERNAL DATA SOURCE [OracleSalesSrvr] ;
DROP DATABASE SCOPED CREDENTIAL [OracleCredential];

Next steps

了解如何将数据引入到数据池中: