Data Provisioning using OracleLogreaderAdapter and CamelJDBCAdapter

[Hemanth1]

Introduction:

In this blog post we will get introduced to

• Data Provisioning


• SAP HANA Smart Data Integration – Architecture


• SDI Architecture


• Downloading and installing Data Provisioning agent


• Configuring Data Provisioning agent


• Configuring OracleLogReader Adapter


• Configuring CamelJDBC Adapter


• Configuring remote source in SAP HANA Studio

 

What is DATA Provisioning?

DATA Provisioning is a process of creating, preparing, and enabling a network to provide data to its user. Data needs to be loaded to SAP HANA before data reaches to the user via a front-end tool.

 

SAP HANA Smart Data Integration

SAP HANA smart data integration loads data, in batch or real-time, into SAP HANA (on premise or in the cloud) from a variety of sources using pre-built and custom adapters.

You deploy this method by installing a Data Provisioning Agent to house adapters and connect the source system with the Data Provisioning server, housed in the SAP HANA system. You then create replication tasks, using WebIDE, to replicate data, or flowgraphs, using Application Function Modeler nodes, to transform and cleanse the data on its way to HANA.

 

SAP HANA Smart Data Integration – Architecture

Image source: https://blogs.sap.com/2016/01/18/hana-smart-data-integration-architecture/

SDI Consists of 3 components:

1.SAP HANA Index Server:Index Server is heart of SAP HANA database system. It contains actual data and engines for processing that data. When SQL or MDX is fired for SAP HANA system, an Index Server takes care of all these requests and processes them. All SAP HANA processing takes place in Index Server.

2.SAP HANA Data Provisioning Server (DP Server):

Data Provisioning Server gives you the ability to use SAP HANA smart data integration.

3.SAP HANA Data Provisioning Agent (DP Agent):

The Data Provisioning Agent provides secure connectivity between the SAP HANA database and your on-premise, adapter-based sources.

 

Steps for data provisioning:

1.Activate DP server on the SAP HANA server

• In the Administration editor, choose the Configuration tab.


• Expand the daemon.ini configuration file.


• Select and expand dpserver, select Instances, right click, and choose Change.


• Set Instances to 1.

 

2. Download and install Data Provisioning Agent (DP Agent)

• Download DP agent from SAP Software Download Center (https://launchpad.support.sap.com/#/softwarecenter)


• In the search bar type 'DP Agent'


• Download the latest version of DP Agent

 

• Download SAP CAR utility to extract the SAR file.


• To download SAP CAR utility search for 'SAPCAR'


• Use the following command to extract the SAR file using the SAP CAR utility


• SAPCAR -xvf IMDB_DPAGENT200_04_0-70002517.SAR

Installing Data Provisioning agent:

• Find hdbsetup.exe and right click to run as administrator.


• Installation is self guided, on the second screen (define installation properties) use windows credentials in the Username for Agent and Password for agent as the DP agent runs as a windows service.

3. Configure DP agent:

• Right click on the DP agent icon on your desktop and runs as administrator


• Configure  - Connect to SAP HANA, Register Agent and Register Adapter

 

Connect to SAP HANA:

Enter SAP HANA credentials

Register Agent:

Enter an Agent name

 

Register Adapter:

Choose CamelJDBCAdapter and click Register

Choose OracleLogReaderadapter and click Register

 

4. Configuring OracleLogReaderAdapter :

DP agent requires libraries from Oracle to import data, depending on the version of your Oracle database. Find which libraries are required on the Product Availability Matrix (PAM) .

 

As an example, if the Oracle version is 12c then,

Search for the below supporting libraries inside Oracle client folder (C:\Oracle\product\..)

• 
  
  
  
  • ojdbc7.jar
  
  
  • xdb6.jar
  
  
  • xmlparserv2.jar
  
  
  
  

Paste the supporting libraries into the usr\sap\dataprovagent\lib folder.

 

Stop the agent and start the agent again.

 

5. Configure CamelLogReaderAdapter:

Use the Camel JDBC adapter to connect to most databases for which SAP HANA smart data integration does not already provide a pre-delivered adapter.

In general, the Camel JDBC adapter supports any database that has SQL-based data types and functions, and a JDBC driver.

Steps to set up the Camel JDBC Adapter:

Open adapters.xml file present in usr\sap\dataprovagent\camel folder.

Uncomment the configuration of Camel JDBC Adapter in the adapters.xml file

Uncommented code in the adapter.xml file as below

<Adapter type="CamelJdbcAdapter" displayName="Camel Jdbc Adapter">

        <RemoteSourceDescription>

            <PropertyGroup name="configuration" displayName="Configuration">

                <PropertyEntry name="dbtype" displayName="Database Type" description="Database Type" defaultValue="other" isRequired="true">

                    <Choices>

                        <Choice name="access" displayName="Access"/>

                        <Choice name="informix" displayName="Informix"/>

                        <Choice name="other" displayName="Other"/>

                    </Choices>

                </PropertyEntry>

                <PropertyEntry name="filepath" displayName="Access file path" description="Access file path" isRequired="false">

                    <Dependencies>

                        <Dependency name="dbtype" value="access"/>

                    </Dependencies>

                </PropertyEntry>

                <PropertyEntry name="filename" displayName="Access file name" description="Access file name" isRequired="false">

                    <Dependencies>

                        <Dependency name="dbtype" value="access"/>

                    </Dependencies>

                </PropertyEntry>

                <PropertyEntry name="host" displayName="Host" description="Host name" isRequired="false"/>

                <PropertyEntry name="port" displayName="Port" description="Port number" isRequired="false"/>

                <PropertyEntry name="dbname" displayName="Database" description="Database Name" isRequired="false"/>

                <PropertyEntry name="servername" displayName="Server Name" description="Server Name" isRequired="false"/>

                <PropertyEntry name="delimident" displayName="delimident" description="delimident" defaultValue="false" isRequired="false"/>

                    <Choices>

                        <Choice name="true" displayName="True"/>

                        <Choice name="false" displayName="False"/>

                    </Choices>

                <PropertyEntry name="driverClass" displayName="JDBC Driver Class" description="JDBC Driver Class" isRequired="false"/>

                <PropertyEntry name="url" displayName="JDBC URL" description="JDBC URL" isRequired="false"/>

            </PropertyGroup>

            <CredentialEntry name="db_credential" displayName="Credential" userDisplayName="user" passwordDisplayName="password"/>

        </RemoteSourceDescription>

        <Capabilities>

            CAP_AND_DIFFERENT_COLUMNS,

            CAP_TRUNCATE_TABLE,

            CAP_LIKE,

            CAP_IN,

            CAP_AND,

            CAP_OR,

            CAP_DISTINCT,

            CAP_HAVING,

            CAP_ORDERBY,

            CAP_ORDERBY_EXPRESSIONS,

            CAP_GROUPBY,

            CAP_SELECT,

            CAP_INSERT,

            CAP_UPDATE,

            CAP_DELETE,

            CAP_EXCEPT,

            CAP_INTERSECT,

            CAP_AGGREGATES,

            CAP_AGGREGATE_COLNAME,

            CAP_DIST_AGGREGATES,

            CAP_INSERT_SELECT,

            CAP_JOINS,

            CAP_JOINS_OUTER,

            CAP_BI_SUBSTRING,

            CAP_BI_NOW,

            CAP_BI_UPPER,

            CAP_BI_LOWER,

            CAP_BI_LCASE,

            CAP_BI_UCASE,

            CAP_BI_CONCAT,

            CAP_BI_LTRIM,

            CAP_BI_RTRIM,

            CAP_BI_TRIM,

            CAP_WHERE,

            CAP_SIMPLE_EXPR_IN_PROJ,

            CAP_EXPR_IN_PROJ,

            CAP_NESTED_FUNC_IN_PROJ,

            CAP_SIMPLE_EXPR_IN_WHERE,

            CAP_EXPR_IN_WHERE,

            CAP_NESTED_FUNC_IN_WHERE,

            CAP_SIMPLE_EXPR_IN_INNER_JOIN,

            CAP_EXPR_IN_INNER_JOIN,

            CAP_NESTED_FUNC_IN_INNER_JOIN,

            CAP_SIMPLE_EXPR_IN_LEFT_OUTER_JOIN,

            CAP_EXPR_IN_LEFT_OUTER_JOIN,

            CAP_NESTED_FUNC_IN_LEFT_OUTER_JOIN,

            CAP_SIMPLE_EXPR_IN_ORDERBY,

            CAP_EXPR_IN_ORDERBY,

            CAP_NESTED_FUNC_IN_ORDERBY,

            CAP_NONEQUAL_COMPARISON,

            CAP_OR_DIFFERENT_COLUMNS,

            CAP_PROJECT,

            CAP_BI_SECOND,,

            CAP_BI_MINUTE,

            CAP_BI_HOUR,

            CAP_BI_MONTH,

            CAP_BI_YEAR,

            CAP_BI_COT,

            CAP_BI_ABS,

            CAP_BI_ACOS,

            CAP_BI_ASIN,

            CAP_BI_ATAN,

            CAP_BI_ATAN2,

            CAP_BI_CEILING,

            CAP_BI_COS,

            CAP_BI_EXP,

            CAP_BI_FLOOR,

            CAP_BI_LN,

            CAP_BI_CEIL,

            CAP_BI_LOG,

            CAP_BI_MOD,

            CAP_BI_POWER,

            CAP_BI_SIGN,

            CAP_BI_SIN,

            CAP_BI_SQRT,

            CAP_BI_TAN,

            CAP_BI_ROUND,

            CAP_BI_ASCII,

            CAP_BI_RIGHT,

            CAP_BI_LEFT,

            CAP_BI_TO_BIGINT,

            CAP_BI_TO_DECIMAL,

            CAP_BI_TO_DOUBLE,

            CAP_BI_TO_REAL,

            CAP_BI_TO_SMALLINT,

            CAP_BI_TO_INT,

            CAP_BI_TO_INTEGER

            CAP_BI_COALESCE,

            CAP_BI_IFNULL,

            CAP_BI_NULLIF,

            CAP_BIGINT_BIND

        </Capabilities>

        <RouteTemplate>jdbc-general.xml</RouteTemplate>

    </Adapter>

</Adapters>

 

 

Download the appropriate JDBC file, and copy it to the <DPAgent_root>/camel/lib directory.

In our example, we are connecting to MariaDB, hence we have placed mariadb-java-client-2.6.1.jar

Stop the agent and start the agent again.

6. Establishing remote sources in SAP HANA Studio

 

Open SAP HANA Studio

Navigate to the database -> Provisioning -> Right click on Remote Sources and select New Remote Source

1.Mention Source Name

2. Choose the Adapter

3. Choose the Source location.

In the Canvas below enter Oracle's details (Host, Port Number, Database Name and credential information.)

The procedure to create a remote source for camel adapter based datasources is similar.

 

Summary:

In this blog post we have learnt about Data Provisioning, the basics of SDI architecture, configuring Data Provisioning agent and finally configuring OracleLogReader Adapter and CamelJDBC Adapter.

Please leave the comments/questions that you may have in the section below.

Thank you!