Home	CloverETL Designer

CloverETL Designer

Table of Contents

I. CloverETL Overview

1. Family of CloverETL Products

CloverETL Designer

CloverETL Engine

CloverETL Server

Getting Additional Information

2. Integrating CloverETL Designer with CloverETL Server

Creating 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

II. Installation Instructions

3. System Requirements for CloverETL Designer

Related Links

4. Downloading CloverETL

CloverETL Desktop Edition
CloverETL Desktop Trial Edition
CloverETL Community Edition

5. Starting CloverETL Designer

6. Installing Designer as Eclipse plugin

III. Getting Started

7. Creating CloverETL Projects

CloverETL Project
CloverETL Server Project
CloverETL Examples Project

8. Structure of CloverETL Projects

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

9. Appearance of CloverETL Perspective

CloverETL Designer Panes

Graph Editor with Palette of Components
Navigator Pane
Outline Pane
Tabs Pane

10. Creating CloverETL Graphs

Creating Empty Graphs
Creating a Simple Graph in a Few Simple Steps

11. Running CloverETL Graphs

Successful Graph Execution
Using the Run Configurations Dialog

IV. Working with CloverETL Designer

12. Using Cheat Sheets

13. Common Dialogs

URL File Dialog
Edit Value Dialog
Open Type Dialog

Import CloverETL Projects

Import from CloverETL Server Sandbox

Import Graphs

Import Metadata

Metadata from XSD
Metadata from DDL

Export Graphs
Export Graphs to HTML
Export Metadata to XSD
Export to CloverETL Server Sandbox
Export Image

16. Advanced Topics

Program and VM Arguments

Example of Setting Up Memory Size

Changing Default CloverETL Settings

Enlarging the Font of Displayed Numbers

Setting and Configuring Java

Setting Java Runtime Environment
Installing Java Development Kit

V. Graph Elements, Structures and Tools

17. Components

What Are the Edges?

Connecting Components by the Edges

Types of Edges

Assigning Metadata to the Edges

Propagating Metadata through the Edges

Colors of the Edges

Debugging the Edges

Enabling Debug
Selecting Debug Data
Viewing Debug Data
Turning Off Debug

Edge Memory Allocation

19. Metadata

Data Types and Record Types

Data Types in Metadata
Record Types

Data Formats

Data and Time Format

Numeric Format

Boolean Format

String Format

Locale and Locale Sensitivity

Locale Sensitivity

Autofilling Functions

Internal Metadata

Creating Internal Metadata
Externalizing Internal Metadata
Exporting Internal Metadata

External (Shared) Metadata

Creating External (Shared) Metadata
Linking External (Shared) Metadata
Internalizing External (Shared) 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

Creating Metadata by User

Extracting Metadata from Lotus Notes

Dynamic Metadata

Reading Metadata from Special Sources

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 Delimiter
Defining Non-Default Delimiter for a Field

Editing Metadata in the Source Code

20. Database Connections

Internal Database Connections

Creating Internal Database Connections
Externalizing Internal Database Connections
Exporting Internal Database Connections

External (Shared) Database Connections

Creating External (Shared) Database Connections
Linking External (Shared) Database Connections
Internalizing External (Shared) Database Connections

Database Connection Wizard

Encrypting the Access Password

Browsing Database and Extracting Metadata from Database Tables

21. JMS Connections

Internal JMS Connections

Creating Internal JMS Connections
Externalizing Internal JMS Connections
Exporting Internal JMS Connections

External (Shared) JMS Connections

Creating External (Shared) JMS Connections
Linking External (Shared) JMS Connection
Internalizing External (Shared) JMS Connections

Edit JMS Connection Wizard

Encrypting the Authentication Password

22. QuickBase Connections

23. Lotus Connections

24. Lookup Tables

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

25. 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

26. Parameters

Internal Parameters

Creating Internal Parameters
Externalizing Internal Parameters
Exporting Internal Parameters

External (Shared) Parameters

Creating External (Shared) Parameters
Linking External (Shared) Parameters
Internalizing External (Shared) Parameters

Parameters Wizard

Parameters with CTL Expressions

Environment Variables

Canonizing File Paths

Using Parameters

27. 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

28. Dictionary

Creating a Dictionary

Using the Dictionary in a Graph

29. Notes in the Graphs

30. Search Functionality

31. Transformations

32. Fact table loader

Launching Fact Table Loader Wizard

Wizard with project parameters file enabled
Wizard with the project parameter file disabled

Working with Fact Table Loader Wizard

Created graph

VI. Components Overview

33. Introduction to Components

34. Palette of Components

35. Common Properties of All Components

Edit Component Dialog

Component Name

Enable/Disable Component

PassThrough Mode

36. Common Properties of Most Components

Defining Transformations

Return Values of Transformations

Error Actions and Error Log (deprecated since 3.0)

Transform Editor

Regex Tester

Common Java Interfaces

37. 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

38. Common Properties of Writers

Supported File URL Formats for Writers

Viewing Data on Writers

Output Port Writing

How and Where Data Should Be Written

Selecting Output Records

Partitioning Output into Different Output Files

Java Interfaces for Writers

39. Common Properties of Transformers

CTL Templates for Transformers
Java Interfaces for Transformers

40. Common Properties of Joiners

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

41. Common Properties of Cluster Components

CTL Templates for Cluster Components
Java Interfaces for Cluster Components

42. Common Properties of Others

43. Custom Components

VII. Component Reference

44. Readers

CloverDataReader

ComplexDataReader

DataGenerator

DBFDataReader

DBInputTable

EmailReader

LotusReader

MultiLevelReader

ParallelReader

QuickBaseRecordReader

QuickBaseQueryReader

UniversalDataReader

XLSDataReader

XMLXPathReader

45. Writers

CloverDataWriter

DB2DataWriter

DBOutputTable

EmailSender

InfobrightDataWriter

InformixDataWriter

LotusWriter

MSSQLDataWriter

MySQLDataWriter

OracleDataWriter

PostgreSQLDataWriter

QuickBaseImportCSV

QuickBaseRecordWriter

StructuredDataWriter

UniversalDataWriter

XLSDataWriter

46. Transformers

Address Doctor 5

Concatenate

DataIntersection

DataSampler

Denormalizer

EmailFilter

SimpleGather

SortWithinGroups

XSLTransformer

47. Joiners

ApproximativeJoin

ExtHashJoin

ExtMergeJoin

RelationalJoin

48. Cluster Components

ClusterGather

ClusterPartitioner

CheckForeignKey

HTTPConnector

JavaExecute

LookupTableReaderWriter

SequenceChecker

SpeedLimiter

SystemExecute

WebServiceClient

VIII. CTL - CloverETL Transformation Language

50. Overview

51. CTL1 vs. CTL2 Comparison

Typed Language
Arbitrary Order of Code Parts
Compiled Mode
Access to Graph Elements (Lookups, Sequences, ...)
Metadata

52. Migrating CTL1 to CTL2

Language Reference

Program Structure

Data Types in CTL

Simple Statement and Block of Statements

Control Statements

Error Handling

Conditional Fail Expression

Accessing Data Records and Fields

Functions Reference

Conversion Functions
Date Functions
Mathematical Functions
String Functions
Container Functions
Miscellaneous Functions
Dictionary Functions
Lookup Table Functions
Sequence Functions
Custom CTL Functions

Language Reference

Program Structure

Data Types in CTL2

Dictionary in CTL2

Simple Statement and Block of Statements

Control Statements

Error Handling

Conditional Fail Expression

Accessing Data Records and Fields

Functions Reference

Conversion Functions
Date Functions
Mathematical Functions
String Functions
Container Functions
Miscellaneous Functions
Lookup Table Functions
Sequence Functions
Custom CTL Functions
Functions for Dynamic Field Access
CTL2 Appendix - List of National-specific Characters

List of Figures

List of Examples