Top 2 Methods for Oracle to Azure Synapse Migration

Data integration from a database like Oracle to an analytics environment like Azure Synapse Analytics is one of the most crucial steps for any business. It can help you analyze the data more efficiently and produce insights, resulting in effective decision-making.

Azure Synapse Analytics’s ADX dashboard features allow you to explore your data from an end-to-end analytics workflow. Its in-built user-defined functions feature enables you to reuse functions across many data flows.

This article discusses two of the most widely used methods of moving data from Oracle to Azure Synapse Analytics.

Methods to Connect Oracle to Azure

If you wonder, “How do I migrate from Oracle to Azure?” This section discusses the most prominently used methods. Take a look at this blog to learn How to Connect to Oracle DB.

Method 1: Copy Data from Oracle to Azure Synapse Using Hevo Data

Hevo Data is a no-code, real-time ELT data pipeline platform that cost-effectively automates your data pipeline according to your requirements. With its easy-to-use user interface, you can integrate data from 150+ source connectors to a destination without prior technical expertise.

Here are some of the key features of using Hevo:

• Data Transformation: Hevo Data enables you to clean and prepare the data to be loaded into the destination using Python-based and drag-and-drop methods.
• Automated Schema Mapping: Hevo automatically manages the schema by detecting the format of incoming data and replicating it to the destination schema. It lets you choose between Full & Incremental Mappings according to your data replication requirements.
• Incremental Data Load: Hevo ensures efficient bandwidth utilization on both the source and the destination by allowing modified data transfer in real-time.

Hevo Data can enable you to perform Oracle to Azure Synapse replication in real-time.

Step 1 – Configure Oracle as a Source

Before configuring Oracle as a Source, consider the prerequisites: 

• Oracle Database Version – You must be using Oracle Database version 11 or above. To check your database version, run:
  
  SELECT BANNER_FULL 

       FROM V$VERSION 

       WHERE BANNER_FULL LIKE 'Oracle Database%';

• Redo Log-based Replication (Optional) – For ingestion using Redo Logs with SYSDBA privileges, ensure that Redo Log-based replication is enabled on your Oracle instance. 
• User Privileges – The database user must be granted the required privileges to perform replication.
• Service Name – Ensure that a valid Service Name is available for your Oracle database.
• Hevo Role Permissions – To create a Data Pipeline, your Hevo account must have one of these roles: Team Administrator, Pipeline Administrator, or Team Collaborator.
  

Steps to Configure

1. In the Hevo App, navigate to the Sources tab.
   
2. Click + Create and choose Database → Oracle.
   
3. On the Set up the Source page, provide the following details:

• Name: A unique name for your Oracle Source (e.g., Oracle_Prod).
• Host: The Oracle server’s host address. 
• Port: Default is 1521. 
• Service Name or SID: Enter the identifier for your Oracle instance. 
• Username and Password: Enter valid database credentials.

4. (Optional) Enable SSL if your database connection requires it. Upload the necessary certificates if prompted.
5. Click Test Connection to validate connectivity.

• If the test succeeds, click Continue.
  
• If it fails, verify firewall settings, credentials, and connection parameters.

6. Select the Schema and Tables you want to replicate.
7. Click Save Source.
   

Step 2 – Configure Azure Synapse Analytics as a Destination

Prerequisites

1. Azure Subscription – Active subscription with billing enabled.
2. Contributor Role – User with Contributor role in Azure RBAC.
3. Azure Synapse Analytics Instance – Active instance with a dedicated SQL pool.
4. Hevo’s IP Whitelisted – Ensure Hevo IP addresses are whitelisted.
5. Hevo Role Permissions – Team Collaborator, Pipeline Admin, or Team Admin role in Hevo.
   

Configure Azure Synapse as a Destination

1. Go to DESTINATIONS in Hevo and click + CREATE DESTINATION.
2. Select Azure Synapse Analytics.
3. On the Configure Destination page, provide:
   • Destination Name – Unique name (≤ 255 characters).
     

Connection Settings – Choose either:

a. Paste Connection String
jdbc:sqlserver://yourworkspace.sql.azuresynapse.net;user=your_username;password={your_password};encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.sql.azuresynapse.net;loginTimeout=30;

b. Enter Connection Settings Manually

• Server Name – Dedicated SQL endpoint (e.g., yourworkspace.sql.azuresynapse.net)
• Database Port – Default: 1433
• Database Name – Target database
• Username & Password – Credentials
• Schema Name – Default: dbo

Advanced Settings

Hevo provides the following advanced options when configuring Azure Synapse as a Destination:

1. Connect through SSH
   • Enabled: Hevo connects via an SSH tunnel instead of directly connecting to your Synapse database. This enhances security by avoiding public exposure of your database.
   • Disabled: Ensure Hevo’s IP addresses for your region are whitelisted.
     
2. Populate Loaded Timestamp
   • Enabled: Hevo appends a __hevo_loaded_at__ column to each Destination table to record when the data was loaded.
     
3. Sanitize Table/Column Names
   • Enabled: Hevo replaces all non-alphanumeric characters and spaces in table or column names with underscores (_).

4. Click Test Connection.
   • If successful, click Save & Continue.
   • If failed, verify firewall rules, credentials, and connection parameters.
     
5. Select the tables or schema to replicate.

Hevo supports incremental replication using system logs or primary keys. Filters can be applied to replicate only the required tables or columns.

Refer to the Azure Synapse Analytics Hevo Documentation to learn more about the steps involved in configuring Azure Synapse Analytics as a destination in Hevo.

Method 2: Integrating Oracle to Azure Synapse Using CSV Data File Transfer

This method will highlight how to connect Oracle Database to Azure Synapse using CSV data file transfer. Follow the steps given below:

Step 1: Export CSV Data from Oracle Database

To export data from the Oracle database to your local system, you can use cx_Oracle, a Python module that interacts with Oracle. Run the following command by entering your specific information on the database and columns placeholders:

import os

import csv

import sys

import cx_Oracle

db = cx_Oracle.connect('user/pass@host:port/service_name')

SQL = "select column1 , column2 , .... , column from table"

print(SQL)

cursor = db.cursor()

f = open("C:\myfile.csv", "w")

writer = csv.writer(f, lineterminator="\n", quoting=csv.QUOTE_NONNUMERIC)

r = cursor.execute(SQL)

#this takes the column names

col_names = [row[0] for row in cursor.description]

writer.writerow(col_names)

for row in cursor:

    writer.writerow(row)

f.close()

Step 2: Move Data to Azure Synapse Analytics

To move the CSV file data from your local machine to Azure Synapse Analytics, run the code snippet in an SQL Client tool such as Azure Synapse Studio by removing the placeholder values.

COPY INTO [ schema. ] table_name
[ (Column_list) ]<br>FROM '<external_location>' [ , ...n ]
WITH<br> (<br> [ FILE_TYPE = { 'CSV' | 'PARQUET' | 'ORC' } ]<br> [ , FILE_FORMAT = EXTERNAL FILE FORMAT OBJECT ]
 [ , CREDENTIAL = (AZURE CREDENTIAL) ]<br> [ , ERRORFILE = ' [ http(s)://storageaccount/container ] /errorfile_directory [ / ] ] '<br> [ , ERRORFILE_CREDENTIAL = (AZURE CREDENTIAL) ]<br> [ , MAXERRORS = max_errors ]<br> [ , COMPRESSION = { 'Gzip' | 'DefaultCodec' | 'Snappy' } ]<br> [ , FIELDQUOTE = 'string_delimiter' ]<br> [ , FIELDTERMINATOR =  'field_terminator' ]<br> [ , ROWTERMINATOR = 'row_terminator' ]<br> [ , FIRSTROW = first_row ]<br> [ , DATEFORMAT = 'date_format' ]<br> [ , ENCODING = { 'UTF8' | 'UTF16' } ]<br> [ , IDENTITY_INSERT = { 'ON' | 'OFF' } ]<br> [ , AUTO_CREATE_TABLE = { 'ON' | 'OFF' } ]<br>)

Why Integrate Oracle to Azure Synapse Analytics?

There are many reasons to move data from an Oracle database to an analytics platform like Azure Synapse Analytics. Here are some of the most common benefits:

Oracle to Azure Synapse Analytics allows you to query relational and non-relational data using the programming language of your preference.

Integrating Oracle to Azure Synapse Analytics can offer a better analytical environment where you can perform multiple machine learning tasks.

Azure Synapse provides advanced security features, such as dynamic data-masking abilities and row and column-level security.

An Overview of Oracle Database

Oracle Database is a prominent relational database management system (RDBMS) that stores organizes, and retrieves data according to user requirements. Storing data in the Oracle Database allows users to quickly create scalable and high-performance applications using SQL or some other procedural language.

With features like Oracle Active Data Guard and Oracle Real Application Clusters, you can efficiently scale and consolidate customer databases, maximizing their availability. It provides a single-pane-of-glass management dashboard to help you increase business performance while constantly managing processes.

An Overview of Azure Synapse Analytics

Azure Synapse Analytics is Microsoft’s analytics service that generates insights from any organization’s on-premise data. It combines the capabilities of SQL, Azure Spark, and Azure Data Explorer to manage big data and perform time series analysis. Azure Synapse Analytics provides advanced privacy features, like always-on encryption and automated threat detection.

Its pay-as-you-go feature allows you to manage costs by using the services you need while maintaining the cost-effectiveness of the analytics procedure. As Azure Synapse Analytics runs on the Microsoft ecosystem, it is easy to connect it with other software on the Azure marketplace. Refer to Azure Synapse Analytics benefits to know more about it.

Limitations of Using CSV Data File Transfer Method

Although the second method is efficient in moving data from Oracle to Azure Synapse Analytics, there are some limitations associated with this method.

• Error Production: For large datasets involving complex multiple data structures, the process of moving data using the COPY INTO command can get complicated, requiring additional monitoring while running the code.
• Limitations on File Formats: The COPY INTO command supports limited formats of data files, including CSV, Parquet, and ORC.
• Lack of Automation: This method requires you to move data using code snippets manually and lacks the real-time integration factor. Any changes on the source do not reflect on the destination. To achieve this, you must continually perform the steps at regular intervals.

Use Cases of Integrating Oracle to Azure Synapse

• Integrating Oracle to Azure Synapse Analytics can enable you to use machine learning to uncover insights from your data.
• This migration can help you save time, as Azure Synapse provides a unified platform for developing end-to-end analytics applications.
• Azure Synapse Analytics provides workload isolation, management, and limitless concurrency that can enable you to optimize the performance of your queries.

You can also read about:

• Migrate MySQL to Oracle
• Migrate PostgreSQL to Oracle
• Amazon RDS to Azure Synapse
• BigQuery to Azure Synapse
• Migrate SQL Server to Azure Synapse

Conclusion

This article mentions two of the most widely used Oracle to Azure Synapse Analytics data integration methods. Both methods efficiently load data from Oracle to Azure Synapse. However, the second method has limitations. To overcome these limitations, you can use Hevo to integrate data.

Hevo provides 150+ data source connectors from which you can quickly extract data and load it into the destination of your choice.

Sign Up for a 14-day free trial and experience the feature-rich Hevo suite firsthand. Also checkout our unbeatable pricing to choose the best plan for your organization.

Share your experience of Oracle to Azure Synapse integration in the comments section below!

Frequently Asked Questions (FAQs)