Skip to content

Commit d45d739

Browse files
rothjarothja
committed
Adding oracle tutorial content
1 parent af8a84a commit d45d739

4 files changed

Lines changed: 149 additions & 18 deletions

File tree

‎docs/big-data-cluster/toc.yml‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -12,6 +12,8 @@
1212
items:
1313
- name: 1_Query storage pool
1414
href: tutorial-query-hdfs-storage-pool.md
15+
- name: 2_Query Oracle data
16+
href: tutorial-query-oracle.md
1517
- name: Concepts
1618
items:
1719
- name: Controller

‎docs/big-data-cluster/tutorial-query-hdfs-storage-pool.md‎

Lines changed: 3 additions & 18 deletions
Original file line numberDiff line numberDiff line change
@@ -28,22 +28,7 @@ In this tutorial, you learn how to:
2828
- [Install Azure Data Studio and the SQL Server 2019 extension](deploy-big-data-tools.md).
2929
- [Load sample data into the cluster](#sampledata).
3030

31-
### <a id="sampledata"></a> Load sample data
32-
33-
SQL Server big data cluster tutorials use a common set of sample data. You can load this sample data into your cluster with the following steps:
34-
35-
1. Download the sample backup file [tpcxbb_1gb.bak](https://sqlchoice.blob.core.windows.net/sqlchoice/static/tpcxbb_1gb.bak) to your machine.
36-
1. Navigate to the SQL Server 2019 big data cluster [samples directory](https://github.com/Microsoft/sql-server-samples/tree/master/samples/features/sql-big-data-cluster).
37-
1. Download the [bootstrap-sample-db.sql](https://github.com/Microsoft/sql-server-samples/blob/master/samples/features/sql-big-data-cluster/bootstrap-sample-db.sql) Transact-SQL script.
38-
1. Download and run one of the following two sample scripts from the command line:
39-
40-
* **Windows**: [bootstrap-sample-db.cmd](https://github.com/Microsoft/sql-server-samples/blob/master/samples/features/sql-big-data-cluster/bootstrap-sample-db.cmd)
41-
* **Linux**: [bootstrap-sample-db.sh](https://github.com/Microsoft/sql-server-samples/blob/master/samples/features/sql-big-data-cluster/bootstrap-sample-db.sh)
42-
43-
> [!TIP]
44-
> You can get usage instructions by running the script with no parameters.
45-
46-
The script restores the sample database to the SQL Server master instance and also loads data into HDFS in the storage pool.
31+
[!INCLUDE [Load sample data](../includes/big-data-cluster-load-sample-data.md)]
4732

4833
## Create an external table to HDFS
4934

@@ -123,6 +108,6 @@ GO
123108

124109
## Next steps
125110

126-
Advance to the next article to learn how to TBD.
111+
Advance to the next article to learn how to query Oracle from a big data cluster.
127112
> [!div class="nextstepaction"]
128-
> [TBD](big-data-cluster-overview.md)
113+
> [Query Oracle data](tutorial-query-oracle.md)

‎docs/big-data-cluster/tutorial-query-oracle.md‎

Lines changed: 128 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,128 @@
1+
---
2+
title: How to query Oracle from a SQL Server big data cluster | Microsoft Docs
3+
description: This tutorial demonstrates how to query Oracle data from a SQL Server 2019 big data cluster (preview). You create an external table over data in Oracle and then run a query.
4+
author: rothja
5+
ms.author: jroth
6+
manager: craigg
7+
ms.date: 10/12/2018
8+
ms.topic: tutorial
9+
ms.prod: sql
10+
---
11+
12+
# Tutorial: Query Oracle from a SQL Server big data cluster
13+
14+
This tutorial demonstrates how to Query Oracle data from a SQL Server 2019 big data cluster. To run this tutorial, you will need to have access to an Oracle server. If you do not have access, this tutorial can give you a sense of how data virtualization works for external data sources in SQL Server big data cluster.
15+
16+
In this tutorial, you learn how to:
17+
18+
> [!div class="checklist"]
19+
> * Create an external table for data in an external Oracle database.
20+
> * Join this data with high-value data in the master instance.
21+
22+
> [!TIP]
23+
> If you prefer, you can download and run a script for the commands in this tutorial. For instructions, see the [Data virtualization samples](https://github.com/Microsoft/sql-server-samples/tree/master/samples/features/sql-big-data-cluster/data-virtualization) on GitHub.
24+
25+
## <a id="prereqs"></a> Prerequisites
26+
27+
* [Deploy a big data cluster on Kubernetes](deployment-guidance.md).
28+
* [Install Azure Data Studio and the SQL Server 2019 extension](deploy-big-data-tools.md).
29+
* [Load sample data into the cluster](#sampledata).
30+
31+
[!INCLUDE [Load sample data](../includes/big-data-cluster-load-sample-data.md)]
32+
33+
## Create an Oracle table
34+
35+
The following steps create a sample table named `INVENTORY` in Oracle.
36+
37+
1. Connect to an Oracle instance and database that you want to use for this tutorial.
38+
39+
1. Run the following statement to create the `INVENTORY` table:
40+
41+
```sql
42+
CREATE TABLE "INVENTORY"
43+
(
44+
"INV_DATE" NUMBER(10,0) NOT NULL,
45+
"INV_ITEM" NUMBER(10,0) NOT NULL,
46+
"INV_WAREHOUSE" NUMBER(10,0) NOT NULL,
47+
"INV_QUANTITY_ON_HAND" NUMBER(10,0)
48+
);
49+
50+
CREATE INDEX INV_ITEM ON HR.INVENTORY(INV_ITEM);
51+
```
52+
53+
1. Import the contents of the **inventory.csv** file into this table. This file was created by the sample creation scripts in the [Prerequisites](#prereqs) section.
54+
55+
## Create an external data source
56+
57+
The first step is to create an external data source that can access your Oracle server.
58+
59+
1. In Azure Data Studio, connect to the SQL Server master instance of your big data cluster. For more information, see [Connect to the SQL Server master instance](deploy-big-data-tools.md#master).
60+
61+
1. In the server dashboard, select **New Query**.
62+
63+
1. Run the following Transact-SQL command to change the context to the **Sales** database in the master instance.
64+
65+
```sql
66+
USE Sales
67+
GO
68+
```
69+
70+
1. Create a database scoped credential to connect to the Oracle server. Provide appropriate credentials to your Oracle server in the following statement.
71+
72+
```sql
73+
CREATE DATABASE SCOPED CREDENTIAL [OracleCredential]
74+
WITH IDENTITY = '<oracle_user,nvarchar(100),SYSTEM>', SECRET = '<oracle_user_password,nvarchar(100),manager>';
75+
```
76+
77+
1. Create an external data source that points to the Oracle server.
78+
79+
```sql
80+
CREATE EXTERNAL DATA SOURCE [OracleSalesSrvr]
81+
WITH (LOCATION = 'oracle://<oracle_server,nvarchar(100)>',CREDENTIAL = [OracleCredential]);
82+
```
83+
84+
## Create an external table
85+
86+
Next, create an external table named **iventory_ora** over the `INVENTORY` table on the Oracle server.
87+
88+
```sql
89+
CREATE EXTERNAL TABLE [inventory_ora]
90+
([inv_date] DECIMAL(10,0) NOT NULL, [inv_item] DECIMAL(10,0) NOT NULL,
91+
[inv_warehouse] DECIMAL(10,0) NOT NULL, [inv_quantity_on_hand] DECIMAL(10,0))
92+
WITH (DATA_SOURCE=[OracleSalesSrvr],
93+
LOCATION='<oracle_service_name,nvarchar(30),xe>.<oracle_schema,nvarchar(128),HR>.<oracle_table,nvarchar(128),INVENTORY>');
94+
```
95+
96+
> [!NOTE]
97+
> Table names and column names will use ANSI SQL quoted identifier while querying against Oracle. As a result, the names are case-sensitive. It is important to specify the name in the external table definition that matches the exact case of the table and column names in the Oracle metadata.
98+
99+
## Query the data
100+
101+
Run the following query to join the data in the `iventory_ora` external table with the tables in the local `Sales` database.
102+
103+
```sql
104+
SELECT TOP(100) w.w_warehouse_name, i.inv_item, SUM(i.inv_quantity_on_hand) as total_quantity
105+
FROM [inventory_ora] as i
106+
JOIN item as it
107+
ON it.i_item_sk = i.inv_item
108+
JOIN warehouse as w
109+
ON w.w_warehouse_sk = i.inv_warehouse
110+
WHERE it.i_category = 'Books' and i.inv_item BETWEEN 1 and 18000 --> get items within specific range
111+
GROUP BY w.w_warehouse_name, i.inv_item;
112+
```
113+
114+
## Clean up
115+
116+
Use the following command to remove the database objects created in this tutorial.
117+
118+
```sql
119+
DROP EXTERNAL TABLE [inventory_ora];
120+
DROP EXTERNAL DATA SOURCE [OracleSalesSrvr] ;
121+
DROP DATABASE SCOPED CREDENTIAL [OracleCredential];
122+
```
123+
124+
## Next steps
125+
126+
Advance to the next article to learn how to TBD.
127+
> [!div class="nextstepaction"]
128+
> [TBD](big-data-cluster-overview.md)

‎docs/includes/big-data-cluster-load-sample-data.md‎

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,16 @@
1+
### <a id="sampledata"></a> Load sample data
2+
3+
SQL Server big data cluster tutorials use a common set of sample data. You can load this sample data into your cluster with the following steps:
4+
5+
1. Download the sample backup file [tpcxbb_1gb.bak](https://sqlchoice.blob.core.windows.net/sqlchoice/static/tpcxbb_1gb.bak) to your machine.
6+
1. Navigate to the SQL Server 2019 big data cluster [samples directory](https://github.com/Microsoft/sql-server-samples/tree/master/samples/features/sql-big-data-cluster).
7+
1. Download the [bootstrap-sample-db.sql](https://github.com/Microsoft/sql-server-samples/blob/master/samples/features/sql-big-data-cluster/bootstrap-sample-db.sql) Transact-SQL script.
8+
1. Download and run one of the following two sample scripts from the command line:
9+
10+
* **Windows**: [bootstrap-sample-db.cmd](https://github.com/Microsoft/sql-server-samples/blob/master/samples/features/sql-big-data-cluster/bootstrap-sample-db.cmd)
11+
* **Linux**: [bootstrap-sample-db.sh](https://github.com/Microsoft/sql-server-samples/blob/master/samples/features/sql-big-data-cluster/bootstrap-sample-db.sh)
12+
13+
> [!TIP]
14+
> You can get usage instructions by running the script with no parameters.
15+
16+
The script restores the sample database to the SQL Server master instance and also loads data into HDFS in the storage pool.

0 commit comments

Comments
 (0)