Home	CloverETL Designer	CloverETL 4.7.0

CloverETL Designer

Table of Contents

I. Overview

1. Family of CloverETL Products

Introduction

CloverETL Designer

CloverETL Server

CloverETL Cluster

CloverETL Community Edition

2. Getting Additional Information

Online Resources

Support

II. Installation

3. System Requirements

Installation Types

Full Installation
Plugin Installation

Related Links

4. Downloading

5. Installing

6. Upgrading

7. Starting

8. Activating

Activation using License Key
Activation Online

9. Other Options of Installation

Installing Designer as Eclipse Plugin
Activating Eclipse Plugin

10. License Manager

CloverETL License Dialog

11. IBM InfoSphere MDM Plugin Installation

Downloading

Requirements

Installation into Designer

Troubleshooting

Installation into Server

Troubleshooting

12. Troubleshooting

Windows SmartScreen
User Account Control
Windows 10 Firewall

Designer Crashes due to Issue in Eclipse on Linux
Designer Stops Responding
Welcome Page not Displayed on Linux
Hints on Edges Have no Content on Linux

Subclipse

III. Configuration

13. Configuration

14. Runtime Configuration

Master Password

User Classpath

15. ETL Server Integration

Ignored Files

16. Execution Monitoring

17. Java Configuration

18. Engine Configuration

19. Refresh Operation

IV. Using Designer

20. Designer User Interface

CloverETL Designer Panes

Graph Editor with Palette of Components

Navigator Pane

Outline Pane

Execution Tab

Keyboard Shortcuts

21. Projects

Types of CloverETL Projects

CloverETL (Local) Project

CloverETL Server Project

Creating CloverETL Projects

CloverETL Project

CloverETL Server Project

CloverETL Examples Project

Converting CloverETL Projects

Converting Local Project to Server Project

Converting Server Projects to Local Project

Structure of CloverETL Projects

Standard Structure of All CloverETL Projects
The .classpath File
Workspace.prm File
Opening the CloverETL Perspective

Versioning of Server Project Content

Initial Check-Out of Project from Repository

Adding Server Project to Version Control

Connecting Server Project to Existing Repository

Getting Changes from Repository

Committing into Repository

Working with CloverETL Server Projects

Working Offline
Handling Conflicts

Project Configuration

CloverETL Connection
Ignored Files

Creating Empty Graphs

Creating a Simple Graph

23. Execution

Successful Graph Execution

Run Configuration

Main Tab
Parameters Tab
Refresh Tab

Connecting to a Running Job

Graph States

24. Common Dialogs

URL File Dialog

Local Files

Workspace View

Clover Server

Hadoop HDFS

Remote Files

Filtering Files and Tips

Edit Value Dialog

Open Type Dialog

Import CloverETL Projects

Import from CloverETL Server Sandbox

Import Graphs

Import Metadata

Metadata from XSD
Metadata from DDL

Convert Graph to Jobflow

Convert Jobflow to Graph

Convert Subgraph to Graph

Conversion of Subgraph to Graph in Steps

Export Graphs to HTML

Export to CloverETL Server Sandbox

Export Image

27. Graph Tracking

Enlarging the Font of Displayed Numbers

28. Search Functionality

29. Working with CloverETL Server

CloverETL Server Project Basic Principles

Opening CloverETL Server Projects

Connecting via HTTP

Connecting via HTTPS

Designer has its Own Certificate

Designer does not have its Own Certificate

Connecting via Proxy Server

V. ETL Graphs

30. Components

Adding Components

Finding Components

Edit Component Dialog

Properties Tab

Enable/Disable Component

Enabling Component
Disabling Component
Enabling by Graph Parameter
Enabling by Connected Input Port
Disable as Trash

Passing Data through Disabled Component

Common Properties of Components

Component Name
Phases
Component Allocation

Specific Attribute Types

Time Intervals

Metadata Templates

What Are the Edges?

Connecting Components with Edges

Types of Edges

Assigning Metadata to the Edges

Colors of the Edges

Debugging the Edges

Selecting Debug Data

Viewing Debug Data

Turning Off Debug

Edge Memory Allocation

32. Metadata

Records and Fields

Record Types

Data Types in Metadata

Data Formats

Locale and Locale Sensitivity

Autofilling Functions

Metadata Types

Internal Metadata

External (Shared) Metadata

Dynamic Metadata

Reading Metadata from Special Sources

Auto-propagated Metadata

Priorities of Metadata

Creating Metadata

Extracting Metadata from a Flat File

Extracting Metadata from an XLS(X) File

Extracting Metadata from a Database

Extracting Metadata from a DBase File

Extracting Metadata from Salesforce

Extracting Metadata from Lotus Notes

Creating Metadata by User

Merging Existing Metadata

Creating Database Table from Metadata and Database Connection

Metadata Editor

Basics of Metadata Editor

Record Pane

Field Name vs. Label vs. Description

Details Pane

Changing and Defining Delimiters

Changing Record Delimiter
Changing Default (Field) Delimiter
Defining Non-Default Delimiter for a Field

Editing Metadata in the Source Code

Multivalue Fields

Lists and Maps Support in Components
Joining on Lists and Maps (Comparison Rules)

33. Connections

Database Connections

Internal Database Connections

External (Shared) Database Connections

Database Connection Properties

Encryption of Access Password

Browsing Database and Extracting Metadata from Database Tables

Windows Authentication on Microsoft SQL Server

Hive Connection

Troubleshooting

JMS Connections

Internal JMS Connections

External (Shared) JMS Connections

Edit JMS Connection Wizard

Encrypting the Authentication Password

QuickBase Connections

Lotus Connections

Hadoop connection

Libraries Needed for Hadoop

MongoDB connection

See also

Salesforce connection

Creating Salesforce Connection

Important Details

34. Lookup Tables

LookupTables in CloverETL Cluster Environment

Internal Lookup Tables

Creating Internal Lookup Tables

Externalizing Internal Lookup Tables

Exporting Internal Lookup Tables

External (Shared) Lookup Tables

Creating External (Shared) Lookup Tables

Linking External (Shared) Lookup Tables

Internalizing External (Shared) Lookup Tables

Types of Lookup Tables

Simple Lookup Table

Database Lookup Table

Range Lookup Table

Persistent Lookup Table

Aspell Lookup Table

35. Sequences

Persistent Sequences

Non Persistent Sequences

Internal Sequences

Creating Internal Sequences

Externalizing Internal Sequences

Exporting Internal Sequences

External (Shared) Sequences

Creating External (Shared) Sequences

Linking External (Shared) Sequences

Internalizing External (Shared) Sequences

Editing a Sequence

Sequence in Cluster Environment

36. Parameters

Internal Parameters

Externalizing Internal Parameters

External (Shared) Parameters

Internalizing External (Shared) Parameters
XML Schema of External Parameters

Graph Parameter Editor

Secure Graph Parameters

Parameters with CTL Expressions (Dynamic parameters)

Time of Evaluation

Environment Variables

Canonicalizing File Paths

Using Parameters

37. Internal/External Graph Elements

Internal Graph Elements
External (Shared) Graph Elements
Working with Graph Elements
Advantages of External (Shared) Graph Elements
Advantages of Internal Graph Elements
Changes of the Form of Graph Elements

38. Dictionary

Creating a Dictionary

Using the Dictionary in a Graph

Accessing the Dictionary from Readers and Writers

Accessing the Dictionary with Java

Accessing the Dictionary with CTL2

39. Notes in the Graphs

Placing Notes into Graph

Resizing Notes

Editing Notes

Formatted Text

Links from Notes

Folding the Notes

Notes Properties

Compatibility

40. Transformations

Defining Transformations

Components Allowing Transformation

Java or CTL

Internal or External Definition

Return Values of Transformations

Error Actions and Error Log (deprecated since 3.0)

Transform Editor

Common Java Interfaces

41. Data Partitioning (Parallel Running)

VI. Subgraphs

42. Overview

Introduction

Design & Execution

Subgraphs vs. Jobflow

43. Using Subgraphs

Using Subgraphs

Configuring Subgraphs

44. Developing Subgraphs

Creating from Scratch

Making Subgraph Configurable

Developing and Testing Subgraphs

Filling Required Parameters

Metadata Propagation

45. Design Patterns

Readers
Writers
Transformers
Executors

VII. Jobflow

46. Jobflow Overview

Introduction

What is CloverETL Jobflow?
Design and Execution
Anatomy of the Jobflow Module

Important Concepts

Dynamic Attribute Setting

Parameter Passing

Pass-Through Mapping

Execution Status Reporting

Error Handling

Jobflow Execution Model: Single Token

Jobflow Execution Model: Multiple Tokens

Stopping on Error

Synchronous vs. Asynchronous Execution

Advanced Concepts

Daemon Jobs
Killing Jobs

47. Jobflow Design Patterns

VIII. Data Service

48. Overview

49. Architecture

50. Development

Data Service job editor

Endpoint Configuration

Data Service REST Job logic

Anatomy of Data Service jobs

Input and Output components
HTTP request payload
HTTP request parameters
HTTP Headers
HTTP response
Multiple Edges
HTTP status code and headers

Custom Serialization

Execution Steps of Data Service Jobs

Exceptions and Error handling

Auto-generated documentation and Swagger/OpenAPI definition

Testing Service logic In Designer

Testing Services deployed on Server

51. Example

Echo to upper case

52. Troubleshooting

Server returns error code 404

Server returns error code 500

Server returns error code 503

IX. Component Reference

53. Readers

Common Properties of Readers

Supported File URL Formats for Readers

Viewing Data on Readers

Input Port Reading

Incremental Reading

Selecting Input Records

Data Policy

XML Features

CTL Templates for Readers

Java Interfaces for Readers

CloverDataReader

ComplexDataReader

CustomJavaReader

DataGenerator

DBFDataReader

DBInputTable

EmailReader

FlatFileReader

HadoopReader

JavaBeanReader

JSONExtract

LotusReader

MongoDBReader

MultiLevelReader

ParallelReader

QuickBaseRecordReader

QuickBaseQueryReader

SalesforceBulkReader

SalesforceReader

SpreadsheetDataReader

UniversalDataReader

XMLXPathReader

54. Writers

Common Properties of Writers

Supported File URL Formats for Writers

Viewing Data on Writers

Output Port Writing

Appending or Overwriting

Creating Directories

Selecting Output Records

Partitioning Output into Different Output Files

Excluding Fields

Java Interfaces for Writers

CloverDataWriter

CustomJavaWriter

DB2DataWriter

DBFDataWriter

DBOutputTable

EmailSender

FlatFileWriter

HadoopWriter

InfobrightDataWriter

InformixDataWriter

JavaBeanWriter

JavaMapWriter

LotusWriter

MongoDBWriter

MSSQLDataWriter

MySQLDataWriter

OracleDataWriter

PostgreSQLDataWriter

QuickBaseImportCSV

QuickBaseRecordWriter

SalesforceBulkWriter

SalesforceWriter

SalesforceWaveWriter

SpreadsheetDataWriter

StructuredDataWriter

TableauWriter

UniversalDataWriter

55. Transformers

Common Properties of Transformers

CTL Templates for Transformers
Java Interfaces for Transformers

Concatenate

CustomJavaTransformer

DataIntersection

DataSampler

Denormalizer

LoadBalancingPartition

SimpleGather

SortWithinGroups

XSLTransformer

56. Joiners

Common Properties of Joiners

Join Types
Slave Duplicates
CTL Templates for Joiners
Java Interfaces for Joiners

ExtHashJoin

ExtMergeJoin

RelationalJoin

57. Job Control

Common Properties of Job Control

ExecuteGraph

ExecuteJobflow

ExecuteMapReduce

ExecuteProfilerJob

ExecuteScript

GetJobInput

KillJobflow

MonitorGraph

MonitorJobflow

SetJobOutput

TokenGather

58. File Operations

Common Properties of File Operations

Supported URL Formats for File Operations

CreateFiles

DeleteFiles

59. Data Partitioning

Common Properties of Data Partitioning Components

ParallelLoadBalancingPartition

ParallelMerge

ParallelPartition

ParallelRepartition

ParallelSimpleCopy

ParallelSimpleGather

60. Data Quality

Common Properties of Data Quality

AddressDoctor 5

EmailFilter

ProfilerProbe

List of Rules

Common Properties of Others

CheckForeignKey

CustomJavaComponent

HTTPConnector

LookupTableReaderWriter

MongoDBExecute

SequenceChecker

SystemExecute

WebServiceClient

62. Deprecated

ApproximativeJoin

JavaExecute

X. CTL2 - CloverETL Transformation Language

63. Overview

64. Language Reference

Program Structure

Data Types in CTL2

number (double)

Dictionary in CTL2

Arithmetic Operators

Relational Operators

Logical Operators

Assignment Operator

Ternary Operator

Conditional fail expression

Simple Statement and Block of Statements

Control Statements

Conditional Statements

Iteration Statements

Jump Statements

Error Handling

Message Function

Conditional Fail Expression

Accessing Data Records and Fields

Regular Expressions

65. CTL Debugging

Debug Perspective

Importing and Exporting Breakpoints

Exporting Breakpoints
Importing Breakpoints

Inspecting Variables and Expressions

Inspect Action
Expressions View and Watch Action

Basic Example

Using Hit Count

Conditional Breakpoint

Detecting Changes of the Value

66. Functions Reference

Conversion Functions

Date Functions

Mathematical Functions

String Functions

Mapping Functions

Container Functions

Record Functions (dynamic field access)

Miscellaneous Functions

Lookup Table Functions

Sequence Functions

Subgraph functions

Data Service HTTP Library functions

Custom CTL Functions

List of All CTL2 Functions

CTL2 Appendix - List of National-specific Characters

Index

List of Figures

List of Examples