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. Optional Steps
Support for SMB 2.x and 3.x
CloverETL Designer
CloverETL Server
13. Troubleshooting
Windows
Windows SmartScreen
User Account Control
Windows 10 Firewall
Linux
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
Others
Subclipse
III. Configuration
14. Configuration
15. Runtime Configuration
Logging
Master Password
User Classpath
16. ETL Server Integration
Ignored Files
17. Execution Monitoring
18. Java Configuration
19. Engine Configuration
20. Refresh Operation
IV. Using Designer
21. Designer User Interface
CloverETL Designer Panes
Graph Editor with Palette of Components
Navigator Pane
Outline Pane
Tabs Pane
Execution Tab
Keyboard Shortcuts
22. 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
23. Graphs
Creating Empty Graphs
Creating a Simple Graph
24. Execution
Successful Graph Execution
Run Configuration
Main Tab
Parameters Tab
Refresh Tab
Connecting to a Running Job
Graph States
25. Common Dialogs
URL File Dialog
Local Files
Workspace View
Clover Server
Hadoop HDFS
Remote Files
Port
Dictionary
Filtering Files and Tips
Edit Value Dialog
Open Type Dialog
26. Import
Import CloverETL Projects
Import from CloverETL Server Sandbox
Import Graphs
Import Metadata
Metadata from XSD
Metadata from DDL
27. Export
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
28. Graph Tracking
Enlarging the Font of Displayed Numbers
29. Search Functionality
30. 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
31. Components
Adding Components
Finding Components
Edit Component Dialog
Properties Tab
Ports 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
Group Key
Sort Key
Metadata Templates
32. Edges
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
33. Metadata
Records and Fields
Record Types
Data Types in Metadata
Data Formats
Locale and Locale Sensitivity
Time Zone
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)
34. 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
Kerberos Authentication for Hadoop
MongoDB connection
Salesforce connection
Creating Salesforce Connection
Important Details
35. 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
36. 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
37. 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
38. 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
Changing Form of Graph Elements
39. 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
40. Notes in the Graphs
Placing Notes into Graph
Resizing Notes
Editing Notes
Formatted Text
Links from Notes
Folding the Notes
Notes Properties
Compatibility
41. 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
42. Data Partitioning (Parallel Running)
VI. Subgraphs
43. Overview
Introduction
Design & Execution
Subgraphs vs. Jobflow
44. Using Subgraphs
Using Subgraphs
Configuring Subgraphs
45. Developing Subgraphs
Wrapping
Creating from Scratch
Making Subgraph Configurable
Developing and Testing Subgraphs
Filling Required Parameters
Metadata Propagation
46. Design Patterns
Readers
Writers
Transformers
Executors
VII. Jobflow
47. 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
Logging
Advanced Concepts
Daemon Jobs
Killing Jobs
48. Jobflow Design Patterns
VIII. Data Services
49. Overview
50. Architecture
51. 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
Execution Steps of Data Service Jobs
Exceptions and Error handling
Auto-generated documentation and Swagger/OpenAPI definition
Testing
Testing Service logic in Designer
Testing Services deployed on Server
52. Use cases
Custom Serialization
Sending a file generated by .rjob
Publishing a static file
Using CTL2 functions in Data Services
Data Service that receives a file or text in body part
Converting Graph to Data Service
Converting Graphs to Data Service
Publishing the Data Service
Publishing multiple Data Services at once
Unpublishing Data Service
Unpublishing multiple Data Services at once
53. Example
Echo to upper case
54. Troubleshooting
Server returns error code 404
Server returns error code 500
Server returns error code 503
IX. Component Reference
55. 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
JMSReader
JSONExtract
JSONReader
LDAPReader
LotusReader
MongoDBReader
MultiLevelReader
ParallelReader
QuickBaseRecordReader
QuickBaseQueryReader
SalesforceBulkReader
SalesforceReader
SpreadsheetDataReader
UniversalDataReader
XMLExtract
XMLReader
XMLXPathReader
56. 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
JMSWriter
JSONWriter
LDAPWriter
LotusWriter
MongoDBWriter
MSSQLDataWriter
MySQLDataWriter
OracleDataWriter
PostgreSQLDataWriter
QuickBaseImportCSV
QuickBaseRecordWriter
SalesforceBulkWriter
SalesforceWriter
SalesforceWaveWriter
SpreadsheetDataWriter
StructuredDataWriter
TableauWriter
Trash
UniversalDataWriter
XMLWriter
57. Transformers
Common Properties of Transformers
CTL Templates for Transformers
Java Interfaces for Transformers
Aggregate
Concatenate
CustomJavaTransformer
DataIntersection
DataSampler
Dedup
Denormalizer
ExtSort
FastSort
Filter
LoadBalancingPartition
Merge
MetaPivot
Normalizer
Partition
Pivot
Reformat
Rollup
SimpleCopy
SimpleGather
SortWithinGroups
XSLTransformer
58. Joiners
Common Properties of Joiners
Join Types
Slave Duplicates
CTL Templates for Joiners
Java Interfaces for Joiners
Combine
CrossJoin
DBJoin
ExtHashJoin
ExtMergeJoin
LookupJoin
RelationalJoin
59. Job Control
Common Properties of Job Control
Barrier
Condition
ExecuteGraph
ExecuteJobflow
ExecuteMapReduce
ExecuteProfilerJob
ExecuteScript
Fail
GetJobInput
KillGraph
KillJobflow
Loop
MonitorGraph
MonitorJobflow
SetJobOutput
Sleep
Subgraph
Success
TokenGather
60. File Operations
Common Properties of File Operations
Supported URL Formats for File Operations
CopyFiles
CreateFiles
DeleteFiles
ListFiles
MoveFiles
61. Data Partitioning
Common Properties of Data Partitioning Components
ParallelLoadBalancingPartition
ParallelMerge
ParallelPartition
ParallelRepartition
ParallelSimpleCopy
ParallelSimpleGather
62. Data Quality
Common Properties of Data Quality
AddressDoctor 5
EmailFilter
ProfilerProbe
Validator
List of Rules
63. Others
Common Properties of Others
CheckForeignKey
CustomJavaComponent
DBExecute
HTTPConnector
LookupTableReaderWriter
MongoDBExecute
RunGraph
SequenceChecker
SystemExecute
WebServiceClient
64. Deprecated
ApproximativeJoin
JavaExecute
X. CTL2 - CloverETL Transformation Language
65. Overview
66. Language Reference
Program Structure
Comments
Import
Data Types in CTL2
byte
cbyte
date
decimal
integer
long
number (double)
string
list
map
record
Literals
Variables
Dictionary in CTL2
Operators
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
Functions
Message Function
Conditional Fail Expression
Accessing Data Records and Fields
Mapping
Parameters
Regular Expressions
67. CTL Debugging
Debug Perspective
Importing and Exporting Breakpoints
Exporting Breakpoints
Importing Breakpoints
Inspecting Variables and Expressions
Inspect Action
Expressions View and Watch Action
Examples
Basic Example
Using Hit Count
Conditional Breakpoint
Detecting Changes of the Value
68. 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
List of Figures
List of Tables
List of Examples