• Performing ANSI SQL2 operations on existing AWS S3 assets
• Joining with other AWS S3 assets as if the were ‘tables’ (again using ANSI SQL2)
• Performing ANSI SQL2 operations on the results of previous operations
• Export/create new assets to
  • AWS S3
  • AWS Redshift
  • AWS Aurora

The following are minimum requirements for BryteFlow Blend

• Real or virtual machine running Windows 10
• Java version 7 or higher is required to be installed on machine
• Connectivity and permissions to access AWS S3
• Connectivity and permissions to access AWS EMR
  • Applications
    • Spark 2.3.0,
    • Ganglia 3.7.2,
    • Zeppelin 0.7.3
• Port 8080 needs to be available on system for the application to bind to
• Open access to Port 8080 through any firewalls for interface access
• Google Chrome

The application can be placed in any folder, however Bryte recommends installing to the following location:

c:\BryteFlow\Blend\

Edit

Step 1 – Application Files

Unzip the application archive to your desired folder, for example

c:\BryteFlow\Blend\

Step 2 – Run the Application

Follow below steps to do the install:

1. Go to the main directory ‘bryteflow’->’blend’
2. In order to install the service open windows command prompt with ‘Run as Administrator’
3. Change the path to ‘blend’ path
4. Execute  “Install-BryteFlow Blend Service.bat”
5. ‘BryteFlow Blend’ service is now installed on the server.
6. Go to Windows ‘services’ and start the service for Ingest.
7. Launch the user interface from google chrome browser with the URL: localhost:8082
8. Make sure you have a valid license before starting the replication. For more details on how to get the license key refer this section.

Step 3 – Access the User Interface

BryteFlow Blend utilises a browser based user interface. You can access this, once the application is running, on:

http://localhost:8082/

If you are accessing remotely then use the appropriate domain and port 8082.

Configuration of BryteFlow Blend is accessed from the ‘SETUP’ tab on the top of the header breadcrumb.

The Setup view is divided into two sections. The left panel lists the available external systems that can be configured. Clicking on a tab in the list will display the available properties in the right hand panel.

The BryteFlow Blend system utilises external systems to provide data and SQL operations. Core to BryteFlow Blend are the following services:

Blend for S3:

• AWS Location
• S3 Repository
• EMR Instance

Blend for Data Warehouse:

• Data Warehouse connection

Clicking on AWS Location provides access to the required configuration. Please enter the following:

• Connection Name, a business friendly name you can provide to your connection
• Region, for example us-west-2
• AWS Access Key, enter the required key
• AWS Secret Access Key, enter the required key
• Product ID, the ID of the current install of BryteFlow Blend
• License Key, enter the required license key
  • To obtain a valid license key email the BryteFlow support team support@bryteflow.com along with your Product ID
• AWS Proxy Host, if your organisation uses proxies please enter the appropriate host
• AWS Proxy Port, if your organisation uses proxies please enter the appropriate port
• AWS Proxy User Id, if your organisation uses proxies please enter the appropriate User Id
• AWS Proxy Password, if your organisation uses proxies please enter the appropriate password

In case your licence is expired, you can email the ‘Product ID’ to the Bryte support team, support@bryteflow.com. In response, you will receive the valid licence key to be applied back to the product. The key can be entered in the following location, this will reactivate the software.

Once all of the AWS Location details are complete use the TEST and SAVE buttons at the bottom right of the screen to confirm accordingly.

Click on ‘S3 Repository’ on the left panel, this will allow the connection to AWS S3 to be configured. The following fields need to be completed:

• Connection Name, the name for this connection. This is for you use and identification within the system, for example a business name
• Output Format, data structure type for this connection. This could be of the following types :
  • Struct metadata file
  • Parquet format
  • ORC format
• Bucket, the S3 bucket where the data will reside
• S3 Folder (structure), the folder location for the structure information, this is required only for ‘Struct metadata file’ schema type (see above)
• S3 Folder (data), the folder location for the data in S3

For additional information on AWS S3 please refer to the following link

• https://aws.amazon.com/s3

Clicking ‘EMR Instance’ on the left panel will allow the EMR Instance to be configured. The following fields need to be completed:

• Name, the name for this instance, for example this could be a business name, it has no effect on the operation of the instance
• Instance, the unique AWS EMR instance ID, this can be obtained from your AWS Console
• S3 Work Area, is the full S3 location that will be used by the EMR cluster as a work area to process data.

For further details on AWS EMR, please refer to the following link

• https://docs.aws.amazon.com/emr/latest/ManagementGuide/emr-what-is-emr.html

Optionally BryteFlow Blend can integrate with additional AWS Services, this makes additional features such as exporting of data or advanced monitoring available.

• AWS Redshift
• AWS Aurora
• AWS Cloudwatch Logs
• AWS Cloudwatch Metrics
• AWS SNS (Simple Notification Service)
• AWS CodeCommit

BryteFlow Blend allows users to export the final data as output to the Datawarehouse. It allows users to run the transformations on S3 while only taking the curated/transformed data set to Data Warehouse, saving on cost.  Both AWS Redshift and Snowflake is supported as an export destination.

BryteFlow Blend can export data to the following Destinations:

• AWS Redshift
• AWS Aurora
• Snowflake

The AWS RDS (Relational Database Service) makes it easy to set up, operate, and scale a relational databases in the cloud. It provides cost-efficient and resizable capacity while automating time-consuming administration tasks such as hardware provisioning, database setup, patching and backups. It provides fast performance, high availability, security and compatibility.

For further details on AWS RDS please refer to the following link

• https://aws.amazon.com/rds/

AWS Redshift is a fast, scalable data warehouse that makes it simple and cost-effective to analyze all your data across your data warehouse and data lake. Redshift delivers ten times faster performance than other data warehouses by using machine learning, massively parallel query execution, and columnar storage on high-performance disk. To select Redshift as a destination select AWS Redshift in the left panel and then configure as shown below

For further details regarding AWS Redshift Database service please refer to the following link

• https://aws.amazon.com/redshift/

BryteFlow Blend is supported for Aurora PostgreSQL. It can orchestrate jobs and run transformations on Aurora database. This can be used to setup ELT pipelines using Ingest and Blend in orchestration. While Ingest does the E-Extract and L- Load, Blend does the T- Transform and together help achieve the ELT model in modern data analytics.

AWS Aurora is a relational database engine that combines the speed and reliability of high-end commercial databases with the simplicity and cost-effectiveness of open source databases. For further details regarding the AWS Aurora service please refer to the following link : https://aws.amazon.com/rds/aurora/

BryteFlow Blend can be integrated with the AWS CloudWatch Log Service, this enables a unified view of AWS resources…with regard to BryteFlow Blend it can capture job execution events such as

• Job completion
• Job failure

Monitoring using AWS CloudWatch Logs is optional, it is configured by selecting CloudWatch Logs on the left panel and completing the options as shown below

• Connection Name, the name for this connection. This is to identify this connection within the system (perhaps a business name)
• Log Group, name of the Log Group. This Log Group has to already exist in AWS CloudWatch.
  • The Log Group is configured using the AWS console
• Log Stream, name of the Log Stream. This Log Stream has to already exist in AWS CloudWatch.
  • The Log Stream is configured using the AWS console

For further information regarding AWS CloudWatch Log Groups and Log Streams please refer to the following link

• https://docs.aws.amazon.com/AmazonCloudWatch/latest/logs/Working-with-log-groups-and-streams.html

• https://docs.aws.amazon.com/AmazonCloudWatch/latest/monitoring/cloudwatch_concepts.html#Metric

BryteFlow Blend supports AWS Simple Notification Service (SNS). This service can be used to send SNS messages on significant events like job completion or failure. SNS is a publish/subscribe notification service that can be used to reliably route notifications to multiple endpoints. This can be used to trigger event-driven computing (for example AWS Lambda) and workflows.

Selection of the SNS on the left panel will allow access to SNS to be configured for BryteFlow Blend. The following fields need to be completed:

• Connection Name, The name for this connection. This is to identify this connection within the system
• SNS Topic, Name of the SNS Topic. This has to already exist and can be configured using the AWS console

For further information on AWS SNS and AWS Lambada and please refer to the following links

• https://docs.aws.amazon.com/sns/latest/dg/welcome.html
• https://aws.amazon.com/lambda/features/

BryteFlow Blend works hand-in-hand with BryteFlow Ingest, where the on-premise data is continuously getting synced to S3.

To auto trigger your Blend job based on Ingest table load, you need to configure the Ingest Instance in Blend.

All it needs is the Ingest install directory path.

**Assumption here is Ingest and Blend resides on the same server.

BryteFlow Blend allows a job to be run for debugging purposes. A small subset of the records in input files is used to reduce the run time of the trace process and unlike a normal run, all intermediate outputs are available. Enter the required details to allow this optional feature to be activated.

BryteFlow Blend supports connection to a Git repository to provide version control for jobs created o the system. The Git repository supported is AWS CodeCommit service.

AWS CodeCommit is a version control service hosted by AWS that you can use to privately store and manage assets (such as documents, source code, and binary files) in the cloud.

This connection is optional and is used only if you decide to store all jobs in a version control system.

Selecting the ‘CodeCommitRepository’ item in the left panel will allow Git access to be configured. The following fields need to be completed:

• Connection Name, The name for this connection. This is to identify this connection within the system.
• Remote repository path, Enter the path for the remote repository
• Git User Name, This is the user id used to connect to AWS CodeCommit
• Git Password, This is the password used to connect to AWS CodeCommit

For more information about AWS CodeCommit please refer to the following link

• https://aws.amazon.com/codecommit/

BryteFlow Blend has the following concepts, each will be discussed in turn

• Task
• Pipeline
• Job
• S3 View

A task is an individual step or operation (for example load from S3, save to S3, query or export), these tasks are chained together to form a pipeline, one or more pipeline(s) reside inside a single Job

As mentioned earlier a job contains a pipeline of tasks, so the first step is to create a job that houses the pipeline and it’s underlying tasks

A new Job can be added to BryteFlow Blend by clicking the ‘Add New Job’ button located in the top left corner of the Job List

This will open the Add Job Dialog where the name for the Job can be entered, and the schedule for when the Job will be executed can be set

The new Job will be added to the Job List and is disabled by default

Please refer to later sections in this document for the following other job edit options

• Monitor
• Save to Git
• Run Now
• Debug

As mentioned earlier a pipeline is simply a number of tasks chained together that reside inside a job, within a single job there can be multiple independent pipelines. Please refer to the diagram below for an example of a pipeline.

A BryteFlow Blend task is a single operation, this can be one of the following four types :

	Input
	Query
	Save
	Export

The ‘Input’ task allows data to be loaded into BryteFlow Blend from AWS S3. When editing a you will need to select the AWS S3 connection you want to use to load data, you will also need to provide the S3 path to the data folder and the manifest folder where the data resides. Please refer to the following screenshot for an example.

The Query Task allows the data from Input tasks or other Query Tasks to be transformed, filtered, or joined using regular ANSI SQL2. When editing a Query task you are able to use the editor to directly enter SQL statements.

BryteFlow Blend allows you to reference the data from other preceding tasks as if they were regular tables using a curly brace notation. For example, if you had a Task named ‘My Data’ you can query the data from that Task with the following SQL:

SELECT * FROM {My_Data}

The BryteFlow Blend tool editor also provides hinting for synta and built in functions.

Tasks are added to pipeline by simple drag and drop method. The first action is to select the Job from the Job list (left panel), this will result in the pipeline(s) being displayed for the job in question (right panel).

You can now add a new task by simply dragging the required task icon into the Task Pipeline area.

To edit the task simply click it in the Task Pipeline. This will open the Edit Task dialog box specific to the type of Task that you are editing. On desktop devices you are able to position the Task within the Task Pipeline by dragging the Task to a new position. This will automatically redraw any connection lines indicating the data flow.

The edit depends on which of the four types of task it to be edited, each will be addressed in turn

	Input
	Query
	Save
	Export

This Edit task dialogs for each Task type also includes a DELETE button that will, after confirmation, delete the Task and associated data connections.

Additionally a query task can also be deleted while editing, by selecting the trash can icon at the top right of the screen

The following sections discuss some of the execution and development scenarios for BryteFlow Blend.

Typically during development of a BryteFlow Blend job the developer will require immediate (rather than scheduled execution). BryteFlow Blend jobs can now be instantly triggered with the ‘Run Now’ feature. This forces a execution irrespective of the jobs status (enabled/disabled) and schedule. To invoke Run Now on any job, click on the menu next to the job and select ‘Run Now’.

A confirmation dialog will be presented, click RUN to confirm execution (or CANCEL if you wish to change your mind).

The job will be run next on the EMR instance. The status will be updated on the screen similar to a normal job run.

All files associated with the following tasks can be viewed within BryteFlow Blend.

	Input Task
	Output Task

The bottom panel in the Task pane is the S3 view. It shows the data view of the objects present in the ‘Input’ and ‘Output’ tasks of the selected job.

Switch between the object views by clicking the object names in the header of ‘S3 View’.

The top bar of the panel shows tabs for all input and output tasks with an icon next to it indicating the task type. Click on a tab to see the contents of the S3 file. The title bar shows the column names. In some cases, the job may not have yet been run and the output file may not exist. In that case, a message to that effect will appear in the data window.

Resize the ‘S3 View’ panel by doing simple click and drag the border line.

BryteFlow Blend allows a job to be run for debugging purposes. A small subset of the records in input files is used to reduce the run time of the trace process and unlike a normal run, all intermediate outputs are available.

To invoke a Run Trace, on any job, click on the menu next to the job and select Run Trace.

A confirmation dialog will be presented – click Run

The status of the run will be displayed in the job status.

Once the run is complete, all data for each Input, Query and Output task will be available for viewing. Switch the display to the debug output by clicking on the view mode tab (as shown below) and select ‘View Sample Data’.

Once the debug mode is selected, the S3 file view is switched to the sample outputs.

The top bar of the panel shows tabs for all input, query and output tasks with an icon next to it indicating the task type. Click on a tab to see the contents of the debug output for that task. The title bar shows the column names.

A job can be saved to Git by clicking on the icon to the right of the job name in the job list and selecting the Save to Git option.

This will open the Save to remote Repository dialog where a comment for this version can be entered.

A previously saved job can be retrieved from Git. This can be useful to transfer jobs from one instance of BryteFlow Blend to another. This is a recommended practice for releasing jobs from a development environment to a production environment.

To list all jobs in a Git repository, click on the top menu and select Versions.

This displays a list of jobs stored in the Git repository. Click on a job to see all versions of that job.

Select one of the versions to retrieve by clicking on the box for that version. This will show the download dialog. Select Download.

If the job does not exist, it will be downloaded and added to the list of jobs. If the job already exists, it will be replaced by the retrieved job.

Jobs within BryteFlow Blend can be in a number of states, the following section will detail these.

Jobs within BryteFlow Blend can be in a number of states as represented by their status. Possible status values are as follows…

• Never Run, the Job has never been executed
• Pending, the Job has been queued to EMR however execution has not yet commenced
• Running, the Task Pipeline for the Job is being executed
• Completed, the execution of the Task Pipeline has completed successfully
• Logging, there was an error and the job aborted without completing, logging information is being prepared
• Error, there was an error and the job aborted without completing

The status of a job execution can be viewed in two ways.

Job List status

The current status of a Job is displayed in a secondary line of text beneath the name of the Job in the Job List, in the example below the Reconciliation job is RUNNING.

Monitor View

If more detailed information is required, for example in the case of an error, the Monitor View can be accessed via the Job Item menu on the right side of the Job Item

The monitor view shows the status information in addition to any error/exception information the Job has produced during execution.

BryteFlow Blend gives the ability to execute SQL in the form of Spark SQL Query Task. Spark SQL in BryteFlow Blend is executed as distributed in-memory computation, on massive scale. The SQL contained in the Query Task is compatible with ANSI SQL2 and the editor provides auto suggestion for syntax and built in functions.

The SQL is contained in a Query Task. A Query Task enables the user to filter, join and transform data from Load tasks or other Query Tasks using regular SQL.

Query Tasks enable users to reference the data from other BryteFlow Blend tasks within the same job as if they were regular database tables though the use of curly brace ‘{ }’ annotation. For example, if there is an existing task called ‘MyData’…it can be queried upon in another SQL Query Task with the following SQL:

SELECT *
  FROM {MyData}

The following screenshot shows an example of the SQL in a SQL Query Task:

BryteFlow Blend uses SPARK SQL, this being compatible with ANSI SQL2 Standards and allows the developer to leverage existing skillsets. The standard set of SQL, functions and properties as listed as follows.

Below are some more functions supported in Spark SQL :

Below are the functions supported in Spark SQL for Conditional operations :

Below are the Date Time functions supported in Spark SQL :

Below are the Mathematical functions supported in Spark SQL :

Below are the Aggregate functions supported in Spark SQL :

Below are some more functions supported in Spark SQL :

BryteFlow Blend is under continual evolution, we receive feedback from new and established users of BryteFlow Blend and use this communication to produce the product road-map, this leads to continual improvements and additions.

Our Release Notes contain the information you’ll need to keep updated on the latest improvements and additions. Stay up to date and get an overview of the new features included in the releases.

Release Notes BryteFlow Blend – v2.5.1 Build 539

Released November 2019

• New Features
• New UI
• Display Lineage for a table
• Login page has been removed
• Ability to define an input that includes all partitions of a table
• Trigger job on a companion Ingest Table or Full Load
• Support for Export to Snowflake
• User defined namespace for Cloudwatch Metrics
• Data Viewer now handles Parquet and ORC files
• Exponential backoff on throttling of EMR access
• Ability to use EMR clusters using a tag instead of instance id
• Jobs still running during shutdown are marked as in error on startup

Bug Fixes

• Support for UTF-8 characters in data
• Column names correctly identified for SQL queries using the UNION keyword

Known Issues

• Timestamps may be converted to integers in Parquet files in some cases
• Data Viewer may not display some CSV files correctly
• Exporting Parquet and ORC files to Redshift or Snowflake may fail in some cases

Release date

01-Sep-2018

What’s New in 2.3 ?

• S3 Data View
• Git Support
• Run Now
• Run Trace

For additional information please refer to the following link

• Release Notes Blend 2.3