Lab: Qlik Replicate for Oracle

Database to Amazon AWS

Oracle Database to Amazon AWS in Qlik Replicate Environment

0
TABLE OF CONTENTS
Overview ................................................................................................................................................................. 3
Introduction ........................................................................................................................................................... 3
Accessing Replicate Environment ......................................................................................................................... 4
Oracle Source Configuration ................................................................................................................................. 5
Amazon AWS Target Configuration ....................................................................................................................... 7
Configure Replication Task .................................................................................................................................... 9
Run Task ............................................................................................................................................................... 15
View Data in Amazon S3 ....................................................................................................................................... 17
Tips & Tricks ......................................................................................................................................................... 19
Sending files AWS S3 19

Lab: Qlik Replicate for Oracle Database to Amazon AWS | Overview 1

Overview
This Document covers the process involved in configuring Qlik Replicate to extract data out of an Oracle
Database and store it into Amazon AWS Environment. The areas covered include:

o Configuring Endpoints (Source and Target)

o Creating Replicate Task

o Execute Task

o Validate data

Introduction
Replicate Tasks are used to manage the extraction and loading from various Source Systems/Databases to
targeted Databases. Although a Replicate task can only manage one source and one target system, it does
not limit the development of multiple tasks within a project. Example: A client may have three different
Database/File Systems in their environment, (Oracle, SAP HANA, IBM VSAM files) and would like to extract all
these data into a centralized Database/Cloud Environment (ex. Amazon AWS) for Analysis. In this scenario,
three Replicate Tasks would be required.
What will surprise those who worked with other data extraction tools, is the seamlessness with which Qlik
Replicate performs these processes.

In this Lab, you will define the steps needed to configure Qlik Replicate to extract data from Oracle Database
to Amazon AWS. Below are the steps required.

Lab: Qlik Replicate for Oracle Database to Amazon AWS | Overview 3

Accessing Replicate Environment
Steps
1. Ensure that the URL for the Replicate Server is available, and access granted.
- This will be provided by your Systems Administrator.

2. Open browser** and enter the URL of the Replication Server.

The format is: https://ReplicateComputerName:3552/attunityreplicate/

Example: https://replicate.attunitydemo.com:3552/attunityreplicate

- This will prompt you to log into Replicate.

3. Enter your Username and Password.

- This will take you to the Replicate Console.

Oracle
Lab: Qlik Replicate for Database
Oracle Databaseto
toAmazon AWS| Accessing
Amazon AWS in Qlik Replicate
ReplicateEnvironment
Environment 4
4
Oracle Source Configuration

4. Select Manage Endpoint Connections.

The following window will appear:

We will now create an Oracle Endpoint.

5. Select + New Endpoint Connection.

Oracle
Lab: Qlik Replicate for Database
Oracle Databaseto
toAmazon AWS| Oracle
Amazon AWS in QlikSource
Replicate Environment
Configuration 5
5
 6. Enter a meaningful Endpoint Name and Description for the Endpoint Connector.

7. With Source button selected, select dropdown arrow to select Oracle.

You will notice as we proceed that the content of the configuration window is context sensitive.

8. Fill in both Source, Target Servers Storage credentials as provided by your Systems Administrator.

• Server:
• Port:
• User:
• Password:
• Security/SSL Mode:

9. Select Test Connection.

Oracle
Lab: Qlik Replicate for Database
Oracle Databaseto
toAmazon AWS| Oracle
Amazon AWS in QlikSource
Replicate Environment
Configuration 6
6
 This tells whether your configuration is correct.

Look for the “Test Connection succeeded” message. Any other message means something may be
incorrect with your Server/Database definitions, or the Server/Database is unavailable.

10. Select Save.

11. Select Close.

Amazon AWS Target Configuration

Next, we need to configure our Amazon AWS target endpoint. This process contains more information about the files
being output but is similar to other endpoints. As before, the first step in the configuration process is to provide the
attributes of the endpoint to be created.

1. In the Replicate Console, Select Manage Endpoint Connections.

2. Select + New Endpoint Connection.

3. Enter a meaningful Endpoint Name and Description for the Endpoint Connector.

4. Select Target button selected in Role.

5. Enter parameters for the Amazon S3 Storage as indicated below.

Note: Your System Administrator/Instructor will provide these credentials.

i. Bucket Name:
ii. Bucket Region
iii. Access Options
iv. Security Key
v. Target Folder (This will be created dynamically once name is entered)

Oracle
Lab: Qlik Replicate for Database
Oracle Databaseto
toAmazon AWS| Amazon
Amazon AWS in Qlik Replicate
AWS TargetEnvironment
Configuration 7
7
 6. Enter File Attributes:
i. Format
ii. Field Delimiter
iii. Null Value
iv. Escape Character
v. Record Delimiter
vi. Quote Character

7. Enter Change Processing, Metadata Files and Data Encryption information.

Oracle
Lab: Qlik Replicate for Database
Oracle Databaseto
toAmazon AWS| Amazon
Amazon AWS in Qlik Replicate
AWS TargetEnvironment
Configuration 8
8
 8. Select Test Connection.
Again, look for the “Test Connection succeeded” message. Any other message means something may be
incorrect with your Server/Database definitions, or the Server/Database is unavailable.

9. Select Save.

Configure Replication Task

Now that we have configured our Oracle source and Amazon AWS target endpoints, we need to tie them together in
what we call a Replicate task. In short, a task defines the following:

• A source endpoint

• A target endpoint

• The list of tables that we want to capture.

• Any transformations we want to make on the data.

We start by doing the following:

1. In the Replication Console, Select +New Task to create a New Task.

The following window will appear:

Oracle
Lab: Qlik Replicate for Database
Oracle Databaseto
toAmazon AWS| Configure
Amazon AWS in Qlik Replicate Environment
Replication Task 9
9
 2. Enter a meaningful Task Name.

3. Select Unidirectional radio button.

- This indicates data flows from Source to Target – with no writebacks to source system.

4. Ensure that Full Load and Apply Changes are shaded.

You should have the following filled in:

• Unidirectional
• Full Load: enabled (Blue highlight is enabled; Select to enable / disable.)
• Apply Changes: enabled (Blue highlight is enabled; Select to enable / disable.)
• Store Changes: disabled (Blue highlight is enabled; Select to enable / disable.)

5. Select OK.
- This closes the New Task dialog box.

Oracle
Lab: Qlik Replicate for Database
Oracle Databaseto
toAmazon AWS| Configure
Amazon AWS in Qlik Replicate Environment
Replication Task 10
10
 Once completed, the following window will appear.

We will now assign the newly created endpoints - source endpoint (Oracle) and target endpoint (Amazon AWS).

At this point the Source and Target Endpoints created above will be assigned, in order for Replicate to extract and
load the data as defined.

Oracle
Lab: Qlik Replicate for Database
Oracle Databaseto
toAmazon AWS| Configure
Amazon AWS in Qlik Replicate Environment
Replication Task 11
11
 1. On the left of the Replicate Console panel, Select Source .

2. Locate the Source Endpoint created above or one which meets your Source definitions.

3. Drag and drop to Source Endpoint on the right, as indicated in diagram.

4. On the left of the Replicate Console panel, Select Target.

5. Locate the Target Endpoint created above or one which meets your Target definitions.

6. Drag and drop to Target Endpoint on the right, as indicated in diagram.

7. Select Save.
Replicate is ready to extract the data from Oracle into Amazon AWS S3 File Storage.

Oracle
Lab: Qlik Replicate for Database
Oracle Databaseto
toAmazon AWS| Configure
Amazon AWS in Qlik Replicate Environment
Replication Task 12
12
 Steps
1. In the Qlik Replicate Console, Select Table Selection.

2. Enter % in Table.

3. Enter HR in Schema.

Oracle
Lab: Qlik Replicate for Database
Oracle Databaseto
toAmazon AWS| Configure
Amazon AWS in Qlik Replicate Environment
Replication Task 13
13
 4. Select Search.
A list of available files/tables will appear.

5. Select Files/tables to be transfer into Replicate the Qlik Replicate Console.

6. Select HR. Departments.

7. Select HR. Employee.

Oracle
Lab: Qlik Replicate for Database
Oracle Databaseto
toAmazon AWS| Configure
Amazon AWS in Qlik Replicate Environment
Replication Task 14
14
 8. Select > icon to transfer data to the Selected Tables column.

9. Select OK.
That completes configuration of the task. We are now ready to save our task and run it.

Run Task

1. Select down arrow beside Run.

2. Select Start Processing.

- If this is not the first time this Task is being used to extract data, Reload Target must be used – Reload
Target will not be an option.

3. Select Yes to reload Data Target.

Oracle
Lab: Qlik Replicate for Database
Oracle Databaseto
toAmazon AWS| Run
Amazon AWS in Qlik
TaskReplicate Environment 15
15
 Replicate switches to Monitor mode to allow for monitoring of Data Loads.

After Full Load is complete, select on the Completed bar to display the tables. There

is DML activity running in the background. Select on the Change Processing tab to

see it in action.

Note: Changes to the tables occur somewhat randomly in the background. You may

need to wait a few minutes before you will see changes appear in the tables that we

selected.

Oracle
Lab: Qlik Replicate for Database
Oracle Databaseto
toAmazon AWS| Run
Amazon AWS in Qlik
TaskReplicate Environment 16
16
View Data in Amazon S3
In this scenario, the data was extracted into Amazon S3 Storage. We will now go to view the data. The tool I will use to
browse the data, Is S3 Browser. S3 Browser is a freeware Windows Client for Amazon S3 File Storage and Amazon
CloudFront. Amazon S3 provides a simple web services interface that can be used to store and retrieve any amount of
data, at any time, from anywhere on the web.

If not yet installed, download, and install S3 Browser Application Interface.

1. Double click on S3 Browser Application.

2. Locate the Bucket as defined in the Target Endpoint.

3. Select the File Folder for further analysis.

4. Select the file .csv in the Selected Folder to be viewed.

Oracle
Lab: Qlik Replicate for Database
Oracle Databaseto
toAmazon AWS| View
Amazon AWS in Qlik Replicate
Data Environment
in Amazon S3 17
17
 5. Select Preview contents of File.

You can also download the .csv file is desired.

6. Select Download.
7. Enter Folder.

Oracle
Lab: Qlik Replicate for Database
Oracle Databaseto
toAmazon AWS| View
Amazon AWS in Qlik Replicate
Data Environment
in Amazon S3 18
18
Tips & Tricks
Sending files AWS S3
You can send files to S3 after they are created by using a post processing script.

These scripts use the AWS Command Line Utility.

Linux Example:

[root@sandbox aws_s3]# cat pushtos3.sh

#!/bin/sh

export AWS_ACCESS_KEY_ID=AKIAJSZCZBY3SR5J55KQ

export AWS_SECRET_ACCESS_KEY=yQtzRx28+z9/lTUUEiMqQ7aXSlmR86vhIZUR2bgH

export AWS_DEFAULT_REGION=us-east-1

echo "-------------- `date` ----------------" >> /aws_s3/output.txt

a=`/usr/local/bin/aws s3 mv /aws_s3/. $1 --exclude '*' --include '*.csv' --recursive 2>&1`

res=$?

echo $a | tee -a /aws_s3/output.txt

if [[ $res -ne 0 ]]

then

# Add your own error handling here....

echo "AWS returned an error!!!"

fi

Oracle
Lab: Qlik Replicate for Database
Oracle Databaseto
toAmazon AWS| Tips
Amazon AWS in Qlik Replicate Environment
& Tricks 19
19
echo " " >> /aws_s3/output.txt

exit $res

Windows Example:

echo -------------- %date% %time% ---------------- >> "D:\Temp\AWS_S3\output.txt"

set AWS_ACCESS_KEY_ID=AKIAJSZCZBY3SR5J55KQ

set AWS_SECRET_ACCESS_KEY=yQtzRx28+z9/lTUUEiMqQ7aXSlmR86vhIZUR2bgH

set AWS_DEFAULT_REGION=us-east-1

"C:\Program Files\Amazon\AWSCLI\aws" s3 mv %CD%. %1 --exclude "*" --include "*.csv" --recursive >>

"D:\Temp\AWS_S3\output.txt"

Oracle
Lab: Qlik Replicate for Database
Oracle Databaseto
toAmazon AWS| Tips
Amazon AWS in Qlik Replicate Environment
& Tricks 20
20
 About Qlik
Qlik’s vision is a data-literate world, where everyone can use data and analytics to improve decision-making and solve their most challenging
problems. Qlik provides an end-to-end, real-time data integration and analytics cloud platform to close the gaps between data, insights and action.
By transforming data into active intelligence, businesses can drive better decisions, improve revenue and profitability, and optimize customer
relationships. Qlik does business in more than 100 countries and serves over 50,000 customers around the world.
qlik.com
© 2021 QlikTech International AB. All rights reserved. All company and/or product names may be trade names, trademarks and/or registered trademarks of the respective owners with which they are
associated. CODE NEEDED FOR PUBLISHING

Oracle
Lab: Qlik Replicate for Database
Oracle Databaseto
toAmazon AWS| Tips
Amazon AWS in Qlik Replicate Environment
& Tricks 21
21