Data Orchestration

Database

Overview

The Database operation enables users to execute essential SQL actions including Insert, Update, Delete, and PLSQL procedures for efficient data manipulation and retrieval in data orchestration workflows.

When to Use

Use the Database operation when you need to perform direct SQL operations on supported databases within your data pipeline, such as batch inserts, updates, or custom PLSQL execution.

How It Works

The operation establishes a secure connection to the selected database using provided credentials and executes the specified SQL statement. It supports batch processing for optimized performance with configurable batch sizes.

How to Configure

Number of Parameters: 10

Select Storage Name

Choose your preferred data storage solution from the available database options including Oracle Database, Microsoft SQL Server, MySQL/MariaDB, Snowflake, Amazon Redshift, Amazon Aurora (MySQL/MariaDB), Amazon Aurora (PostgreSQL), PostgreSQL, Teradata DB, IBM DB2, and SQLite.

Select Version

Select the specific version for the chosen storage name from the dropdown menu to ensure compatibility with your database environment.

Host IP

Enter the Host IP Address to establish connection to the selected database storage.

10.0.0.100

Port Number

Specify the port number for database connection. Default value is 3306.

3306

Schema Name

Provide the schema name containing the target table.

customer_info

Username

Enter the username for database authentication.

john_peter

Password

Provide the password associated with the username for secure authentication.

SecurePassword@123

Tuple Key

For batch data insertion, specify the key name organizing the batched data.

['table_data']

Batch Size

Set the number of records processed per batch. Default value is 1000.

1000

SQL Statement

Enter the SQL query to execute within triple double quotes.

"""Insert into customer_info (column1,column2) values (?,?)""","column1","column2"

Email

Overview

The Email operation provides versatile email sending capabilities for notifications, alerts, and standard messaging within data orchestration workflows.

When to Use

Use the Email operation for automated notifications, status reports, alert systems, or any workflow requiring email communication.

Configuration Tabs

Compose Tab

To

Enter recipient email addresses. Supports string interpolation for dynamic recipients.

[user@email.com]
{%receiver%}

Cc

Add email addresses receiving carbon copy. Array format supports both static emails and interpolated values.

[additional@email.com]
{%additionalreceiver%}

Bcc

Specify blind carbon copy recipients. Recipients remain hidden from other recipients.

[additional2@email.com]
{%additionalreceiver2%}

Subject

Enter a concise subject line describing email content.

Records Status

Source Key

Define dynamic keys for email content. Use empty string for multiline records.

Items

Body

Create rich email content using the HTML editor textarea.

This is a sample body text

Attachment Tab

Is Attachment?

Toggle Yes/No to include file attachments with the email.

File Name

Specify attachment filename when Is Attachment is set to Yes.

order.tsv

Settings Tab

From

Enter sender email address.

[sender@email.com]

Password

Enter password for sender email authentication.

Mail Server

Specify email server hostname or address.

mail.email.com

Port Number

Enter mail server port number.

123

Notes

Use the Test button in the UI to validate Email Settings configuration before deployment.

WebSocket

Overview

The WebSocket operation is used to send a message to a WebSocket endpoint and optionally consume the response. It builds the WebSocket URL from the endpoint URL and query parameters, converts headers into WebSocket-compatible headers, sends the payload as JSON, and stores the response in the target key. This WebSocket integration uses source data as the base payload, merges the message into that payload, adds a timestamp automatically if one is not already present, and sends the final payload as JSON. The response received from the WebSocket connection is then stored in the specified target.

How It Works

The WebSocket execution flow follows a predefined sequence to ensure consistent request handling and response processing. The process begins by constructing the WebSocket connection URL using the configured endpoint_url and endpoint_params. The endpoint_headers are then transformed into a WebSocket-compatible header format before establishing the connection.

Once the connection is established, the system retrieves the base payload from the configured source and merges the incoming message object into the payload structure. If a timestamp field is not already present, the system automatically appends one to the payload. The final payload is then serialized and transmitted in JSON format through the WebSocket connection.

After the request is sent, the response returned by the WebSocket server is read and processed. The processed response is subsequently stored in the configured target destination for downstream usage or persistence.

When to Use

Use this operation when you need to send structured data to a WebSocket endpoint and capture the response in pipeline data. It is suitable for workflows that require a WebSocket request/response exchange.

How to Configure

This operation uses 8 parameters. Configure each parameter based on the source data, endpoint connection details, and output destination.

Notes

• The payload is sent as JSON.
• The timestamp is added automatically only when it is not already present.
• The response is read from the WebSocket connection and stored in the configured target.

DL Ingestion

Overview

Datalake Ingestion operation securely transfers data into Bizintel360 data lake from various sources using optimized ingestion processes.

Number of Parameters: 5

Data Lake Version

Select target Data Lake version: Data Lake from Bizdata after 2023 or before 2023. Parameters remain consistent across versions except operation name.

Index / Table Name

Specify destination index or table name in the Data Lake.

table

Action Type

Primary Key

Required for Upsert, Update, Delete actions to uniquely identify records.

API

Overview

API operation facilitates data transmission between software products through System and Test configurations.

System Configuration

1. Select Target name from Catalog or create new target.
2. Choose Business object from available catalog options for the selected target.

Test Configuration

Test area validates API configurations. Auto-generates fields from catalog selections requiring authentication and test execution.

Response Parameters

• Text (default)
• XML
• JSON

Pre-Request Script of Python

Overview

Pre-request scripts execute before HTTP requests to modify parameters, generate signatures, set headers, or perform preprocessing tasks including authentication signature generation and debugging.

Amazon SP API Examples

Method – POST

import time
import datetime, hashlib, hmac
import json
access_key='{{access_key}}'
secret_key='{{secret_key}}'
host = '{{host}}'
endpoint = '{{hostname}}'
canonical_uri = '{{canonical_uri}}'
body = {{body}}
request_parameters =json.dumps(body)
t = datetime.datetime.utcnow()
amzdate = t.strftime('%Y%m%dT%H%M%SZ')
datestamp = t.strftime('%Y%m%d')
method = 'POST'
service = 'execute-api'
region = 'us-east-1'
canonical_querystring = ''
canonical_headers = 'host:' + host + '\\n' + 'x-amz-date:' + amzdate + '\\n'
signed_headers = 'host;x-amz-date'
payload_hash = hashlib.sha256((request_parameters).encode('utf-8')).hexdigest()
canonical_request = method + '\\n' + canonical_uri + '\\n' + canonical_querystring + '\\n' + canonical_headers + '\\n' + signed_headers + '\\n' + payload_hash
algorithm = 'AWS4-HMAC-SHA256'
credential_scope = datestamp + '/' + region + '/'+ service + '/' + 'aws4_request'
string_to_sign = algorithm + '\\n' +  amzdate + '\\n' +  credential_scope + '\\n' +  hashlib.sha256(canonical_request.encode('utf-8')).hexdigest()
kDate = hmac.new(('AWS4' + secret_key).encode('utf-8'), datestamp.encode('utf-8'), hashlib.sha256).digest()
kRegion = hmac.new(kDate, region.encode('utf-8'), hashlib.sha256).digest()
kService = hmac.new(kRegion, service.encode('utf-8'), hashlib.sha256).digest()
kSigning = hmac.new(kService, 'aws4_request'.encode('utf-8'), hashlib.sha256).digest()
signing_key = kSigning
signature = hmac.new(signing_key, (string_to_sign).encode('utf-8'), hashlib.sha256).hexdigest()
authorization_header = algorithm + ' ' + 'Credential=' + access_key + '/' + credential_scope + ', ' +  'SignedHeaders=' + signed_headers + ', ' + 'Signature=' + signature

Notes

• Text response format selected by default in Response Parameters
• Available in IB (Integration Bridge) Postman view for API source, operation, and target
• Oracle NetSuite Target/Operations supports POST or PATCH methods

Datalake Search

Overview

DataLake Search retrieves records from Bizintel360 datalake using specified index names with pagination support.

Number of Parameters: 5

DataLake Version

• Neptune Datalake
• Pluto Datalake

Index/Table Name

Enter the Index or Table name from where data is required to be retrieved data from the Datalake.

Pagination Wait Time

By default it is set to 2m, where m is minute. Pagination is a standard API capability. Bizintel360 Data Lake source retrieve data in paginated way. This parameter is to set the tome required to wait for next page. So pagination focuses to retrieve data in small chunks with low waiting time. Pagination is also use to retrieve data rapidly with low waiting time.

Timeout

By default it is `2m`, where m is minute. In general `2m` is high enough to get response from Bizintel360 Data Lake. Increase this when response from Bizintel360 DataLake is slow. This can happen when the Data Lake Cluster size is small. Reach out to Bizdata support team to make a increase in cluster size of Bizintel360 Data Lake.

Frequently Asked Questions

What databases are supported by Database operation?

Supported databases include Oracle Database, Microsoft SQL Server, MySQL/MariaDB, Snowflake, Amazon Redshift, Amazon Aurora (MySQL/MariaDB), Amazon Aurora (PostgreSQL), PostgreSQL, Teradata DB, IBM DB2, and SQLite.

What Action Types are available in DL Ingestion?

Available actions: Upsert, Update, Delete, Create, Insert. Upsert, Update, and Delete require Primary Key specification.

What Response formats are supported in API operation?

API Response Parameters support Text (default), XML, and JSON formats.