Connecting any Database to Database
Information is frequently stored in databases under various architectures, naming standards, and formats. To overcome these discrepancies, it takes careful mapping and transformation procedures to align data items across source and target databases.
eZintegrations, an iPaaS integration tool provides various operations to transform and map the data across various systems. This makes the integration process smoother and more efficient for the users.
In this user guide, users will get step-by-step instructions on configuring the Database as a source and a target. By following this guide, users can establish a connection, retrieve data from their chosen source database and send it to Target.
The steps to configure the Database are:
Step 1: Select Source as Database.
Step 2: Select your Storage Name (Database) as Oracle Database or as per the requirement from the list.
Step 3: Once the storage name is selected, add the required credentials and the SQL query. Users can test their query below in the SQL Statement box by clicking on the “Execute Button”.
Select Storage Name
In this section, users need to select the database from the drop-down list, that they will require for the integration.
E.g.: Oracle Database
Version
Once the storage (database) is selected, users need to select the associated version with their database.
E.g.: Latest
Database Credentials
To establish their database connection, users need to provide the Host IP Address, Port Number, Schema Name, Username, and Password of their database.
Chunk Size
This represents the size of records that can be streamed in one go. The ideal size for streaming records from Bizintel360 is 1000, which can be extended up to 10,000 or more records for one-time historical data loads.
Response Parameters
Response parameters are set by the user for every status code that the integration provides. A key-value map represents the response parameters. The key indicates where the request parameter is located and how to modify it. The new data for the parameter is specified by the value.
SQL Statement
Users need to provide their SQL query in the given area and using the Execute button, users can check the response in the response area.
Step 4: Click Next and go to Operations. Operations are used to transform, wrangle and clean the data before sending them to the target and can be modified based on the requirement of the data.
For sending records to the database, certain operations are used. They are:
The Singleline to Multiline operation is the first step in converting records. It transforms single-line JSON data into a multiline format, making the data more organized and simpler to work with. This restructured format facilitates easier application of specific operations for individual entries, streamlining subsequent data tasks.
Value to be passed in Singleline to Multiline operation:
['bizdata_dataset_response']['data']In this, we are converting the value stored under [‘data’], from singleline to multiline.
ii. Append
To create a comma-separated new key-value pair, Append Operation is used. This operation helps in mapping the key created to the target key.
In Database-to-Database integration, Append is used to create a new key-value pair, wherein going forward all the keys will be stored.
Example:
"new_key": "new_value"
iii. Data Aggregation
Data Aggregation operation combines or groups all the data into an array. For Database integration, data aggregation is done to combine all the list of columns coming from the source to an array.
This operation consists of multiple parameters:
Parameter: Agg Data Key
Agg Data Key is passed as empty for multiline data and will have data key in case of single line data.
Example:
['bizdata_dataset_response']['items']Parameter: Groupby Key
Groupby Key provides the key name, or unique identifier inside the dataset, that the user intends to group by.
Example:
"Order"Parameter: Array Key
With an array key, the user can assign any key name to be used as the key for holding common keys.
Example:
"Order Details"Parameter: Array Key Nested Columns
Give the key names as comma-separated keys that should be available inside the Array Key.
To send bulk records to the database, Tuple is created. Using this tuple, records are sent into batches to the database. Singleline to Tuple operation combines all the singleline values into a comma-separated tuple.
This operation consists of 3 parameters:
Parameter: Singleline Key
To read the dataset from a single line, Singleline Key is used.
Example:
"Order"In this, “Order” is used to hold the singleline data.
Parameter: Table Headers
Table Headers specifies the sequence of the converted tuple data.
Example:
"Order","Customer Name","Product"Parameter: Tuple Key
This key will hold the Tuple data.
Example:
OrderdetailStep 5: Click next, and go to Data Target. Select target as Database and add the database details along with SQL query.
Select Storage Name
In this section, users need to select the database from the drop-down list, that they will require for the integration.
E.g.: Oracle Database
Version
Once the storage (database) is selected, users need to select the associated version with their database.
E.g.: Latest
Database Credentials
To establish their database connection, users need to provide the Host IP Address, Port Number, Schema Name, Username, and Password of their database.
Order Set of Values (Tuple Key)
Provide the key name that has been used for creating the tuple in the Singleline to Tuple operation. The Tuple key should be written in square brackets, enclosed in single quotes.
E.g.:
['Orderdetail']Batch Size
The number of streaming records sent from the source is defined by the Batch Size in the target system, which maximizes transfer efficiency for real-time processing. Smoother data transfer across systems is made possible by a suggested value of 1000.
SQL Statement
Users need to provide their SQL query in the given area and using the Execute button, users can check the response in the response area.
SQL Statement to be passed with 3 double quotes,
E.g.:
"""Insert into table_name (column1,column2) values (?,?)""","column1","column2"Add all the details and save the bridge. Once the details are submitted, the connection is established between two databases, completing the Database to Database integration.