Руководство: выполнение запроса к Oracle из кластера больших данных SQL Server

Область применения: SQL Server 2019 (15.x)

В этом руководстве показано, как запрашивать данные Oracle из кластера больших данных SQL Server 2019. Чтобы запустить это руководство, вам потребуется доступ к серверу Oracle. Требуется учетная запись пользователя Oracle с правами чтения для внешнего объекта. Проверка подлинности пользователей Oracle Proxy поддерживается. Если у вас нет доступа, в этом руководстве можно понять, как работает виртуализация данных для внешних источников данных в кластере больших данных SQL Server.

В этом руководстве вы узнаете, как:

Prerequisites

• Средства работы с большими данными
  • kubectl
  • Azure Data Studio
  • Расширение SQL Server 2019
• Загрузка примеров данных в кластер больших данных

Создание таблицы Oracle

Ниже описано, как создать пример таблицы с именем INVENTORY Oracle.

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. Выберите Создать запрос.

   

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 следующими.

4. Создайте учетные данные, привязанные к базе данных, для подключения к серверу Oracle. Укажите соответствующие учетные данные пользователя прокси-сервера Oracle в следующей инструкции.

   CREATE DATABASE SCOPED CREDENTIAL [OracleProxyCredential]
   WITH IDENTITY = '<oracle_proxy_user,nvarchar(100),SYSTEM>', SECRET = '<oracle_proxy_user_password,nvarchar(100),manager>';
   

5. Создайте внешний источник данных, указывающий на сервер Oracle.

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

Создание внешней таблицы

Затем создайте внешнюю таблицу с именем inventory_ora на INVENTORY сервере Oracle.

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>');

Запрос данных

Выполните следующий запрос, чтобы присоединить данные во 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

Узнайте, как загрузить данные в пул данных: