适用于: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
-
大数据工具
- kubectl
- Azure 数据工作室
- SQL Server 2019 扩展
- 将示例数据加载到大数据群集中
创建 Oracle 表
以下步骤在 Oracle 中创建一个名为 INVENTORY
的示例表。
连接到要用于本教程的 Oracle 实例和数据库。
若要创建
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);
将 inventory.csv 文件的内容导入到此表中。 此文件由先决条件部分中的示例创建脚本创建。
创建外部数据源
第一步是创建可访问 Oracle 服务器的外部数据源。
在 Azure Data Studio 中,连接到大数据群集的 SQL Server 主实例。 有关详细信息,请参阅连接到 SQL Server 主实例。
双击“服务器”窗口中的连接,以显示 SQL Server 主实例的服务器仪表板 。 选择“新建查询” 。
运行以下 Transact-SQL 命令,将上下文更改为主实例中的 Sales 数据库。
USE Sales GO
创建数据库范围凭据以连接到 Oracle 服务器。 在以下语句中向 Oracle 服务器提供适当的凭据。
CREATE DATABASE SCOPED CREDENTIAL [OracleCredential] WITH IDENTITY = '<oracle_user,nvarchar(100),SYSTEM>', SECRET = '<oracle_user_password,nvarchar(100),manager>';
创建指向 Oracle 服务器的外部数据源。
CREATE EXTERNAL DATA SOURCE [OracleSalesSrvr] WITH (LOCATION = 'oracle://<oracle_server,nvarchar(100)>',CREDENTIAL = [OracleCredential]);
可选:Oracle 代理身份验证
Oracle 支持代理身份验证,以提供精细的访问控制。 代理用户使用其凭据连接到 Oracle 数据库,并在数据库中模拟另一个用户。
可以将代理用户配置为,与被模拟的用户相比,具有有限的访问权限。 例如,可以允许代理用户使用被模拟用户的特定数据库角色进行连接。 即使多个用户使用代理身份验证进行连接,通过代理用户连接到 Oracle 数据库的用户的标识也会保留在连接中。 这样,Oracle 可以强制执行访问控制,并审核代表实际用户执行的操作。
如果你的方案需要使用 Oracle 代理用户,请将前面的第 4 步和第 5 步替换为以下内容。
创建数据库范围凭据以连接到 Oracle 服务器。 在下面的语句中,向 Oracle 服务器提供适当的 Oracle 代理用户凭据。
CREATE DATABASE SCOPED CREDENTIAL [OracleProxyCredential] WITH IDENTITY = '<oracle_proxy_user,nvarchar(100),SYSTEM>', SECRET = '<oracle_proxy_user_password,nvarchar(100),manager>';
创建指向 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
了解如何将数据引入到数据池中: