Read Me Install Guide Lines

User Manual:

Open the PDF directly: View PDF .
Page Count: 6

Download
Open PDF In Browser	View PDF

Data Warehouse & ETL Offload Code
Samples Overview and Install Guide
Copyright © 2018, Oracle and/or its affiliates. All rights reserved.
The Universal Permissive License (UPL), Version 1.0

The Data Warehouse & ETL Offload Code Samples provide sample code artifacts to support data
warehousing and ETL offload solution patterns in the Oracle Public Cloud and in an Oracle Cloud at
Customer deployment. The code artifacts and sample dataset provided are described below by folder.
Installation and execution guidelines are included below.

Code Samples & Installation Content
Pre-Requisites and Assumptions .................................................................................................................. 1
Global Files - SampleSourceFiles................................................................................................................... 2
Loading Autonomous Data Warehouse ........................................................................................................ 3
GoldenGate_Parameter_Samples ............................................................................................................ 3
ODISampleAutonomousDataWarehouseCloudLoad ................................................................................ 4
BigDataCloudETLOffloadSparkNotebook ...................................................................................................... 5
CloudAtCustomerODIETLOffload .................................................................................................................. 5

Pre-Req’s and Assumptions
These code samples are built using Oracle Cloud services (either Oracle Public Cloud or Oracle Cloud at
Customer) and are provided as-is with no expressed warranty. Solution documentation along with stepby-step guides for service integration and configurations on the Data Warehouse and ETL Offload
Solution patterns are available online at docs.oracle.com and should be read prior to implementing
these samples:


Learn how to load the data warehouse for business analytics



Load the data warehouse for business analytics on Oracle Cloud



Load the data warehouse for business analytics on Cloud at Customer

The solution documentation provides step-by-step guides to configuring the services and should be
followed prior to installation / execution of the sample workloads. To implement these samples you
will need to have provisioned the following services –
Oracle Autonomous Data Warehouse Cloud
Oracle Data Integration Platform Cloud
Oracle Analytics Cloud
Oracle Big Data Cloud (for Oracle Public Cloud ETL Offload)
Oracle Big Data Cloud @ Customer (for Cloud@Customer ETL Offload)

Global Files - SampleSourceFiles
SampleSourceFiles provide a set of .csv files that are used throughout the solution. These files are used
in both ODI Smart export samples (loading ADWC and ETL Offload for BigDataCloud@Customer) as well
as in the sample BigDataCloud Notebook for ETL Offload / Spark processing in the Oracle Public Cloud
CUSTOMER_SRC_FILE.csv provides a set of made up customer data (customer names are random letters
and numbers). This is used to load the CUSTOMER dimension and to provide REGION data for the ETL
Offload spark sample
Column Ordinal
1
2
3
4
5

Column Name
CUSTOMER_ID
FIRST_NAME
LAST_NAME
REGION
CITY

DataType
NUMERIC
VARCHAR
VARCHAR
VARCHAR
VARCHAR

SRC_PRODUCT.csv provides a set of product data. This is used to load the PRODUCT dimension in
ADWC and to provide Product Category / Family data for the ETL Offload Spark sample.
Column Ordinal
1
2
3
4

Column Name
PRODUCT_ID
PRODUCT_NAME
PRICE
FAMILY

DataType
NUMERIC
VARCHAR
NUMERIC
VARCHAR

ORDERS_FILE.csv provides a set of order data. This is used to load the SALES_FACT fact table in ADWC
(and the subsequent SALES_ANALYSIS table). It provides the dataset for the SALES_ANALYSIS ETL
Offload Spark Notebook and ODI project.

Column Ordinal
1
2
3
4
5
6
7

Column Name
ORDER_ID
CUSTOMER_ID
ORDER_DATE
PRODUCT_ID
AMOUNT
QTY
ORDER_LINE_NUMBER

DataType
NUMERIC
NUMERIC
DATE
NUMERIC
NUMERIC
NUMERIC
NUMERIC

Loading Autonomous Data Warehouse
This folder provides artifacts demonstrating how to replicate a source table to ADWC for reporting with
GoldenGate and how to load a star schema data warehouse with ODI. (Note this project can also be
used against an Exadata Cloud at Customer solution but may require you to change the knowledge
modules used in the solution)

GoldenGate_Parameter_Samples
Artifacts in this folder represent sample GoldenGate parameter files for real-time replication of data
from an Oracle database source (on-premise or DBCS) to an ADWC target. These files can be used with
any GoldenGate for Oracle 12.3 implementation including the Data Integration Platform Cloud’s
REMOTE AGENT.
exadwc.prm is a sample GoldenGate EXTRACT parameter file to capture from an Oracle database
source. To configure this file replace the  entry with your Oracle database SID or CDB name.
This file is configured to capture from the schema ADWC_SRC.
padwc.prm is a sample GoldenGate EXTRACT PUMP parameter file to pump transactions capture via the
extract to the Data Integration Platform Cloud instance for delivery to Autonomous Data Warehouse
Cloud. To configure this file – replace the  and  entries.
radwc.prm is a sample GoldenGate REPLICAT parameter file for a Classic replicat. This applies the
transactions to the Autonomous Data Warehouse Cloud instance. To configure this file replace the
 entry with your SID or CDB name. This applies data from the ADWC_SRC schema to the
adwc_repl schema in your adwc instance.

Place these files in your dirprm directory for the GoldenGate installation. (exadwc.prm and padwc.prm
in the source and radwc.prm in your DIPC host / DIPC remote agent installation).
Follow the instructions in the Data Warehouse Solution Pattern for info on configuring GoldenGate
within the DIPC remote agent and where to leverage these files.

ODISampleAutonomousDataWarehouseCloudLoad
These artifacts provide create user / ddl scripts to create a small star schema in the Autonomous Data
Warehouse Cloud and an ODI Smart Export to load the Autonomous Data Warehouse Cloud star
schema.
To import / execute these loads you must perform the following steps –


















Connect to your ADWC instance via SQL Developer
Run the CreateODI_USER_and_TABLES.sql script to create the ODI_USER and the target tables
for the ODI project. **Note – replace the  tag in the first line of the script with
the password you wish to use for the ODI User.
SSH into your Data Integration Platform Cloud instance as OPC user
Create a directory in your DIPC instance
o Mk dir /tmp/ADWCSample
Copy the SourceFiles ORDERS_FILE.csv, SRC_PRODUCT.csv and CUSTOMER_SRC_FILE.csv and
ODIADWCSample.xml to your DIPC instance – the /tmp/ADWCSample directory you just created
Change permissions on the directory and files to ensure the oracle user can read the files
o Sudo chmod 755 -R /tmp/ADWCSample
VNC into your DIPC instance.
Run ODI Studio
Use Smart Import to import the ODI processes.
Navigate to the Topology Manager in ODI Studio and alter the ADWC connection strings (review
the Solution Documentation for more details on how to obtain / configure your ODI / ADWC
connection).
Open the ODI project and review the mappings.
To run the processes execute the ODI package PKG ODI_User DW Sample Load

Using SQL Developer – query the tables in the ADWC ODI_USER schema
o CUST_DIM
o DATE_DIM
o PRODUCT_DIM
o SALES_FACT
o SALES_FACT_ANALYSIS

Review the ODI mappings. Note that the 2 folders provide 2 methods for executing dimension and fact
loads in ADWC:
Loads Using Mappings and KM’s provides mappings the leverage certified Knowledge Modules for
ADWC. These are appropriate for Type 1 dimension style loads.

Loads using Dimensions and Cubes objects provides mappings that leverage the Dimensions and Cubes
objects within ODI to load the same set of tables into the ADWC ODI_USER schema. The Dimensions
objects are appropriate for Type 2 or Type 3 dimension loads.

BigDataCloudETLOffloadSparkNotebook
This artifact is a sample BigDataCloud Notebook that demonstrates Spark to load data from files stored
in Oracle Object Storage – perform an ETL routine leveraging SparkSQL and then store the result in
multiple file formats back in Object Storage (all running in the Oracle Public Cloud).
To configure and execute the Spark notebook –







Login to your Oracle Public Cloud acct.
Create a Container within your Oracle ObjectStorage service (for example BDCETL)
Navigate to your BigDataCloud instance and the Notebook tab.
Click Import to import the notebook (point to the json file and import).
Open the notebook and click the run / play button to execute the Spark script.
The Spark notebook generates a SALES_FACT_ANALYSIS similar to the results of the ODI ADWC
processes.

To review the results simply execute the %SQL section of the notebook which queries the resulting
SALES_ANALYSIS table in BigDataCloud.

CloudAtCustomerODIETLOffload
These artifacts demonstrate ODI performing ETL Offload in a BigData Cloud@Customer (Cloudera)
environment. These leverage the SourceFiles sample dataset to ingest data into the Cloudera Cluster
(via SQOOP or Spark) and then ODI executes Spark workloads to generate the SALES_ANALYSIS data set.
To configure and execute the ODI jobs –









Follow the documentation on configuring your BigData Cloud at Customer machine for ODI
workload. Do not create your topology objects yet however.
Ssh / vnc into you ODI Agent node in your cluster
Create a directory in your clusters local file system
o Mkdir /tmp/sourcefiles
Copy the ODISmartExport_ETLOffload_BigDataCloud@Customer.zip file to your ODI agent node
in your cluster.
Copy the SourceFiles ORDERS_FILE.csv, SRC_PRODUCT.csv and CUSTOMER_SRC_FILE.csv to your
DIPC instance – the /tmp/sourcefiles directory you just created
Run ODI Studio
Smart Import the ODI project into you environment
Configure your topology connections based on the Solution documentation step by step guide.













Edit the ORCL_SRC connection to point to an Oracle database that you have (the ingestion
mappings pull from an Oracle db to demonstrate how to leverage either SQOOP or Spark to
ingest data to your cluster)
Follow the Solution documentation instructions on configuring your Hadoop credential store for
the ORCL_SRC connection if you plan on using Spark to ingest the data
Review the ODI project.
0. Create Oracle Source Objects
o The PKG Create Oracle Objects and Load Data will create tables in your source Oracle
database and load the SourceFiles to that database.
1. Ingest Data to BigData HDFS – SQOOP & Spark
o The PKG Ingest Data to Hive leverages ODI mappings to import data into your Big data
cluster leverage SQOOP.
o Open one of the mappings and navigate to the physical tab. Note that there are
physical designs for both SQOOP and Spark ingestion. Either can be leveraged to move
data into the cluster.
2. ETL Offload – Spark
o The Mapping MAP Spark ETL Offload… demonstrates how to create a mapping in ODI
that leverages Spark to perform ETL offload to the cluster and create a SALES_ANALYSIS
data set.
Execute the packages / mappings listed above in order to
o Create Oracle source objects
o Ingest data into your cluster
o Generate a SALES_ANALYSIS dataset leveraging ODI’s Spark capabilities.



---

Source Exif Data:

File Type                       : PDF
File Type Extension             : pdf
MIME Type                       : application/pdf
PDF Version                     : 1.5
Linearized                      : No
Page Count                      : 6
Language                        : en-US
Tagged PDF                      : Yes
Author                          : tgarrod
Creator                         : Microsoft® Word 2016
Create Date                     : 2018:07:06 15:57:34-06:00
Modify Date                     : 2018:07:06 15:57:34-06:00
Producer                        : Microsoft® Word 2016

EXIF Metadata provided by EXIF.tools