List of Figures

1.1. CloverETL Designer

1.2. CloverETL Server

1.3. CloverETL Cluster

1.4. CloverETL Community Edition

7.1. CloverETL Designer Splash Screen

7.2. Workspace Selection Dialog

7.3. CloverETL Designer Introductory Screen

7.4. CloverETL Help

8.1. Choose licensing

8.2. Dialog for specifying license

8.3. License agreement

8.4. Select Activate online radio button, enter your license number and password and click Next.

8.5. Confirm you accept the license agreement and click Finish button.

9.1. Choosing Install New Software in Help Menu

9.2. Available Software (in Eclipse 4.3)

9.3. Adding a Repository to the Eclipse

9.4. Installing Eclipse Plugin

9.5. Overview of CloverETL Plugins to be Installed

9.6. Accepting License Agreements

9.7. License Manager

10.1. License Manager showing installed licenses.

10.2. CloverETL License dialog

12.1. SmartScreen warning

12.2. Launching installer from SmartScreen

12.3. User Account Control Preventing the Intallation

12.4. Windows 10 Firewall

13.1. ETL Server Integration

13.2. Show component icon: enabled

13.3. Show component icon: disabled

13.4. Show component background: enabled

13.5. Show component background: disabled

13.6. Show component description: enabled

13.7. Show component description: disabled

13.8. Show rich tooltips: enbled

13.9. Show rich tooltips: disabled

13.10. Open context menu for newly created edge: enabled

14.1. CloverETL Runtime

14.2. Accessing CloverETL Runtime menu

14.3. Restarting CloverETL Runtime

14.4. Adding library to classpath using VM parameters

14.5. CloverETL Runtime - Logging

14.6. Setting the Master password

14.7. CloverETL Runtime - User Classpath

14.8. User Classpath - Advanced Options

15.1. ETL Server Integration

15.2. ETL Server Integration

16.1. Execution Monitoring

17.1. Setting The Java Runtime Environment

17.2. Preferences Wizard

17.3. Installed JREs Wizard

19.1. Refresh Operation

20.1. CloverETL Perspective

20.2. Selecting Components

20.3. Components in Palette

20.4. Removing Components from the Palette

20.5. Graph Editor with an Opened Palette of Components

20.6. Closing the Graphs

20.7. Rulers in the Graph Editor

20.8. Grid in the Graph Editor

20.9. A Graph before Selecting Auto-Layout.

20.10. A Graph after Selecting Auto-Layout.

20.11. Six New Buttons in the Tool Bar Appear Highlighted (Align Middle is shown)

20.12. Alignments from the Context Menu

20.13. Navigator Pane

20.14. Outline Pane

20.15. Outline Pane - Subgraphs

20.16. Outline Pane with Minimap

20.17. Show Element ID Enabled

20.18. Opening the ETL Graph Cleanup

20.19. ETL Graph Cleanup

20.20. Locking an Element

20.21. Locking an Element - Message dialog

20.22. Accessing a locked graph element - you can add any text you like to describe the lock.

20.23. Properties Tab

20.24. Console Tab

20.25. Problems Tab

20.26. Clover - Regex Tester Tab

20.27. Execution tab of a graph running on CloverETL Designer

20.28. Execution tab of a graph running on CloverETL Server

20.29. Execution tab of a clustered graph running on CloverETL Cluster

21.1. Placeholder File

21.2. Naming a CloverETL Project

21.3. CloverETL Project subdirectories

21.4. CloverETL Server Project Wizard - Server Connection

21.5. CloverETL Server Project Wizard - Sandbox Selection

21.6. CloverETL Server Project Wizard - Clustered Sandbox Creation

21.7. Giving a Name to the New CloverETL Server Project

21.8. CloverETL Examples Project Wizard

21.9. Renaming CloverETL Examples Projects

21.10. Context Menu with Convert to Server Project option

21.11. Convert local project to CloverETL Server project wizard

21.12. Convert local project to CloverETL Server project wizard II

21.13. Convert to Local Project

21.14. Project Folder Structure inside Navigator Pane

21.15. Opening the Workspace.prm File

21.16. Workspace.prm File

21.17. Basic Eclipse Perspective

21.18. Selecting CloverETL Perspective

21.19. CloverETL Perspective

21.20. Connection failed

21.21. Connection reestablished

21.22. CloverETL Connection

21.23. CloverETL Connection

22.1. Creating a New Graph

22.2. Giving a Name to a New CloverETL Graph

22.3. Graph Editor with a New Graph and the Palette of Components

22.4. Components Selected from the Palette

22.5. Components are Connected by Edges

22.6. Creating an Input File

22.7. Creating the Contents of the Input File

22.8. Metadata Editor with Default Names of the Fields

22.9. Metadata Editor with New Names of the Fields

22.10. Edge Has Been Assigned Metadata

22.11. Opening the Attribute Row

22.12. Selecting the Input File

22.13. Input File URL Attribute Has Been Set

22.14. Output File URL without a File

22.15. Output File URL with a File

22.16. Defining a Sort Key

22.17. Sort Key Has Been Defined

22.18. Running the Graph

22.19. Result of Successful Run of the Graph

22.20. Contents of the Output File

23.1. Running a Graph from the Main Menu

23.2. Running a Graph from the Context Menu

23.3. Running a Graph from the Upper Tool Bar

23.4. Successful Graph Execution

23.5. Console Tab with an Overview of the Graph Execution

23.6. Counting Parsed Data

23.7. Run Configuration - Main Tab

23.8. Run Configuration - Parameters Tab

23.9. Run Configuration - Refresh tab

23.10. Connect to Job action position in the main toolbar

23.11. Connect to Job action position in the Execution view

23.12. Connect to Job dialog

24.1. URL File Dialog

24.2. URL File Dialog - Local files

24.3. URL File Dialog - Workspace view

24.4. URL File Dialog - Clover Server

24.5. URL File Dialog - Hadoop HDFS

24.6. Edit URL Dialog

24.7. Edit URL Dialog

24.8. Edit URL Dialog - Amazon S3

24.9. URL File Dialog - Remote files

24.10. URL File Dialog - Input Port

24.11. URL File Dialog - Output Port

24.12. URL File Dialog - Dictionary

24.13. Configured URL File Dialog

24.14. Edit Value Dialog

24.15. Find Wizard

24.16. Go to Line Wizard

24.17. Open Type Dialog

25.1. Import (Main Menu)

25.2. Import (Context Menu)

25.3. Import Options

25.4. Import Projects

25.5. Import from CloverETL Server Sandbox Wizard (Connect to CloverETL Server)

25.6. Import from CloverETL Server Sandbox Wizard (List of Files)

25.7. Import Graphs

25.8. Import Metadata from XSD

25.9. Import Metadata from XSD - Review

25.10. Import Metadata from DDL

26.1. Export Options

26.2. Converting Graph to Jobflow

26.3. Converting Graph to Jobflow II

26.4. Converting Jobflow to Graph

26.5. Converting Jobflow to Graph

26.6. Converting Subgraph to Graph

26.7. Converting Subgraph to Graph II.

26.8. Export Graphs to HTML

26.9. Export to CloverETL Server Sandbox

26.10. Export Image

27.1. Edge tracking example

27.2. An example of a medium level of tracking information

27.3. An example of a high level tracking information

27.4. Enlarging the Font of Numbers

27.5. Setting the Font Size

28.1. CloverETL Search Tab

28.2. Search Results

29.1. CloverETL Server Project Displayed after Opening CloverETL Designer

29.2. Prompt to Open CloverETL Server Project

29.3. Opening CloverETL Server Project

29.4. Network connections window

30.1. Add Components dialog - finding a sorter.

30.2. Find Components dialog - the searched text is highlighted both in component names and description.

30.3. Edit Component Dialog (Properties Tab)

30.4. Edit Component Dialog (Ports Tab)

30.5. Running a Graph with Disabled Component

30.6. With Default PassThrough

30.7. With PassThrough to second Output Port

30.8. Simple Renaming Components

30.9. Running a Graph with Various Phases

30.10. Setting the Phases for More Components

30.11. Component allocation dialog

30.12. Allocation cardinality decorator

30.13. Defining Group Key

30.14. Defining Sort Key and Sort Order

30.15. Creating Metadata from a Template

31.1. Selecting the Edge Type

31.2. Creating Metadata on an empty Edge

31.3. Assigning Metadata to an Edge

31.4. Metadata in the Tooltip

31.5. Debugging options

31.6. Properties of an Edge

31.7. Filter Editor

31.8. Debug Properties Dialog

31.9. Choosing Inspect Data from Context Menu

31.10. Data Inspector

31.11. Search Data

31.12. Search Options

31.13. Export Debug Data to CSV

31.14. Data Inspector Preferences

31.15. Debug mode in the Properties tab

31.16. Debugging option in the Debug Configuration window

32.1. Externalizing and/or Exporting Internal Metadata

32.2. Selecting a Location for a New Externalized and/or Exported Internal Metadata

32.3. Creating External (Shared) Metadata in the Main Menu and/or in the Navigator Pane

32.4. Internalizing External (Shared) Metadata

32.5. Metadata propagation: metadata is propagated from the first edge on the left side to all connected edges.

32.6. Changing auto-propagated metadata to user-defined.

32.7. Changing user-defined metadata to auto-propagated.

32.8. Different priorities of metadata propagation

32.9. Metadata propagated from the component

32.10. Metadata propagated from the component II.

32.11. Metadata propagated from the component, metadata template is defined within the component.

32.12. Metadata propagated from the another edge

32.13. Metadata propagated from a distant edge

32.14. Advanced metadata propagation - DataIntersection

32.15. Overview of directions of metadata propagation

32.16. Metadata propagated from an unconnected distant edge

32.17. Metadata propagated from the another edge

32.18. Extracting Metadata from Delimited Flat File

32.19. Extracting Metadata from Fixed Length Flat File

32.20. Setting Up Delimited Metadata

32.21. Setting Up Fixed Length Metadata

32.22. Extract Metadata from Excel Spreadsheet Wizard

32.23. Format Extracted from Spreadsheet Cell

32.24. Extracting Internal Metadata from a Database

32.25. Database Connection Properties Dialog

32.26. Selecting Columns for Metadata

32.27. Generating a Query

32.28. DBF Metadata Editor

32.29. Extract metadata from Salesforce - specify connection

32.30. Extract metadata from Salesforce - enter SOQL query

32.31. Extract metadata from Salesforce - edit created metadata

32.32. Specifying Lotus Notes connection for metadata extraction

32.33. Lotus Notes metadata extraction wizard, page 2

32.34. Merging two metadata - conflicts can be resolved in one of the three ways (notice radio buttons at the bottom).

32.35. Creating Database Table from Metadata and Database Connection

32.36. Metadata Editor for a Delimited File

32.37. Metadata Editor for a Fixed Length File

32.38. Trackable Fields Selection in Metadata Editor

33.1. Creating Internal Database Connection

33.2. Externalizing Internal Database Connection

33.3. Internalizing External (Shared) Database Connection

33.4. Database Connection Properties Dialog

33.5. Adding a New JDBC Driver into the List of Available Drivers

33.6. JNDI resource - Basic tab

33.7. JNDI resource - Basic tab

33.8. Using password from secure graph parameter

33.9. Connecting to MS SQL with Windows authentication.

33.10. Adding path to the native dll to VM parameters.

33.11. Externalizing JMS Connection from Context menu

33.12. Edit JMS Connection Wizard

33.13. QuickBase Connection Dialog

33.14. Lotus Notes Connection Dialog

33.15. Hadoop Connection Dialog

33.16. MongoDB Connection Dialog

33.17. MongoDB Connection Dialog - Advanced Tab

33.18. Creating Salesforce Connection

33.19. Salesforce Connection Dialog

33.20. Salesforce Connection Dialog II

33.21. Salesforce Connection Dialog III

34.1. Creating Internal Lookup Table

34.2. Externalizing Wizard

34.3. Selecting Lookup Table Item

34.4. Lookup Table Internalization Wizard

34.5. Lookup Table Wizard

34.6. Simple Lookup Table Wizard

34.7. Edit Key Wizard

34.8. Simple Lookup Table Wizard with File URL

34.9. Simple Lookup Table Wizard with Data

34.10. Changing Data

34.11. Database Lookup Table Wizard

34.12. Appropriate Data for Range Lookup Table

34.13. Range Lookup Table Wizard

34.14. Persistent Lookup Table Wizard

34.15. Aspell Lookup Table Wizard

35.1. Creating a Sequence

35.2. Editing a Sequence

35.3. A New Run of the Graph with the Previous Start Value of the Sequence

36.1. Externalizing Internal Parameters

36.2. Internalizing External (Shared) Parameter

36.3. Example of a Graph parameters editor

36.4. Graph Parameters Type Editor

36.5. Select Editor Type Dialog

36.6. Edit Parameter Value

36.7. Edit Parameter Value

36.8. Multiline string parameter - configuration

36.9. Multiline string parameter - validating XML

36.10. File URL Dialog - Configuration

36.11. Select Types Dialog - Choosing file extension(s)

36.12. Properties - Usage

36.13. Single Field - Configuration

36.14. Single Field - Choosing the Field

36.15. Multiple Fields - Configuration

36.16. Multiple Fields - Choosing the Field

36.17. Field Mapping - Configuration

36.18. Field Mapping - Choosing the Field

36.19. Join Key - Configuration

36.20. Join Key - Configuration

36.21. Enumeration - Configuration

36.22. Character set

36.23. Time Zone - Configuration

36.24. Time Zone - Usage

36.25. Field Type

36.26. Locale

36.27. Graph parameters editor with secure attribute

36.28. Filter Component Configured by Graph Parameter

38.1. Dictionary Dialog with Defined Entries

39.1. Pasting a Note to the Graph Editor Pane

39.2. Enlarging the Note

39.3. Highlighted Margins of the Note Have Disappeared

39.4. Changing the Note Label

39.5. Writing a New Description in the Note

39.6. A New Note with a New Description

39.7. Toolbar for Format Editing

39.8. Note with formatted text and markup

39.9. Toolbar for Format Editing

39.10. Folded the Note

39.11. Properties of a Note

40.1. Define Error Actions Dialog

40.2. Transformations Tab of the Transform Editor

40.3. Copying the Input Field to the Output

40.4. Mapping of Inputs to Outputs (Connecting Lines)

40.5. Editor with Fields and Functions

40.6. Input Record Mapped to Output Record Using Wildcards

40.7. Transformation Definition in CTL (Source Tab)

40.8. Java Transform Wizard Dialog

40.9. Info after Java Transform Wizard Dialog

40.10. Confirmation Message

40.11. Transformation Definition in CTL (Transform Tab of the Graph Editor)

40.12. Content Assist (Record and Field Names)

40.13. Content Assist (List of CTL Functions)

40.14. Error in Transformation

40.15. Transformation Definition in Java

41.1. Parallel Run

41.2. Parallel Run

41.3. Parallel Run with Cluster Components

41.4. Component Allocation

41.5. Component Allocation

42.1. Subgraph Layout

42.2. Example of subgraph with multiple output ports

43.1. Subgraph Component

43.2. Example of User-defined Component

44.1. Original graph without subgraphs

44.2. Wrapping components into a subgraph

44.3. Wrapping Subgraph Wizard

44.4. CloverETL Graph with the Subgraph Component

44.5. A new Subgraph

44.6. Export as subgraph parameter button

44.7. Public parameter appeared as a subgraph component attribute

44.8. Use graph parameter as value button

44.9. Setting up an Optional Port

44.10. Setting up an Optional Port in Graph Editor

44.11. Setting up Color of Subgraph

44.12. Setting up Icon of Subgraph

44.13. Dialog for Filling Required Parameters

44.14. Subgraph providing metadata

44.15. Metadata propagated from Subgraph component

44.16. Subgraph explicitly defines input metadata for customers

44.17. Using subgraph with matching metadata

44.18. Generic subgraph not defining explicit metadata in its body

44.19. Metadata propagate through the Subgraph component

45.1. Subgraph - Reader

45.2. Subgraph - Writer

45.3. Subgraph - Transformer

45.4. Subgraph - Executor

48.1. Viewing Data in Components

48.2. XML Features Dialog

48.3. Configuring prefix selector in ComplexDataReader. Rules are defined in the Selector properties pane. Notice the two extra attributes for regular expressions.

48.4. Sequences Dialog

48.5. A Sequence Assigned

48.6. Edit Key Dialog

48.7. Generated Query with Question Marks

48.8. Generated Query with Output Fields

48.9. Mapping to Clover fields in EmailReader

48.10. JSONExtract - mapping the list

48.11. Example mapping of nested arrays - the result.

48.12. SpreadsheetDataReader Mapping Editor

48.13. Basic Mapping – notice leading cells and dashed borders marking the area data will be taken from

48.14. The difference between global data offsets set to 1 (default) and 3. In the right hand figure, reading would start at row 4 (ignoring data in rows 2 and 3).

48.15. Global data offset is set to 1 to all columns. In the third column, it is locally changed to 3.

48.16. Rows per record is set to 4. This makes SpreadsheetDataReader take 4 Excel rows and create one record out of their cells. Cells actually becoming fields of a record are marked by a dashed border, therefore the record is not populated by all data. Which cells populate a record is also determined by the data offsets setting, see the following bullet point.

48.17. Rows per record is set to 3. The first and third columns 'contribute' to the record by their first row (because of the global data offset being 1). The second and fourth columns have (local) data offsets 2 and 4, respectively. The first record will, thus, be formed by 'zig-zagged' cells (the yellow ones – follow them to make sure you understand this concept clearly).

48.18. Retrieving format from a date field. Format Field was set to the "Special" field as target.

48.19. Reading mixed data using two leading cells per column. Rows per record is 2, Data offset needed to be raised to 2 – looking at the first leading cell which has to start reading on the third row.

48.20. The Mapping Dialog for XMLExtract

48.21. Parent Elements

48.22. Editing Namespace Bindings in XMLExtract

48.23. Selecting subtype in XMLExtract

49.1. Viewing Data on Components

49.2. Generated Query with Question Marks

49.3. Generated Query with Input Fields

49.4. Generated Query with Returned Fields

49.5. EmailSender Message Wizard

49.6. Edit Attachments Wizard

49.7. Attachment Wizard

49.8. Defining bean structure - click the Select combo box to start.

49.9. Mapping editor in JavaBeanWriter after first open. Metadata on the input edge(s) are displayed on the left hand side. The right hand pane is where you design the desired output tree - it is pre-defined by your bean's structure (note: in the example, the bean contains employees and projects they are working on). Mapping is then performed by dragging metadata from left to right (and performing additional tasks described below).

49.10. Example mapping in JavaBeanWriter - employees are joined with projects they work on. Fields in bold (their content) will be printed to the output dictionary, i.e. they are used in the mapping.

49.11. Mapping editor in JavaMapWriter after first open. Metadata on the input edge(s) are displayed on the left hand side. The right hand pane is where you design the desired output tree. Mapping is then performed by dragging metadata from left to right (and performing additional tasks described below).

49.12. Example mapping in JavaMapWriter - employees are joined with projects they work on. Fields in bold (their content) will be printed to the output dictionary.

49.13. Mapping arrays in JavaMapWriter - notice the array contains a dummy element 'State' which you bind the input field to.

49.14. Mapping editor in JSONWriter after first open. Metadata on the input edge(s) are displayed on the left hand side. The right hand pane is where you design the desired JSON tree. Mapping is then performed by dragging metadata from left to right (and performing additional tasks described below).

49.15. Example mapping in JSONWriter - employees are joined with projects they work on. Fields in bold (their content) will be printed to the output file - see below.

49.16. Mapping arrays in JSONWriter - notice the array contains a dummy element 'State' which you bind the input field to.

49.17. Spreadsheet Mapping Editor

49.18. Explicit mapping of the whole record

49.19. The difference between global data offsets set to 1 (default) and 3. In the right hand figure, writing would start at row 4 with no data written to rows 2 and 3.

49.20. Global data offsets is set to 1. In the last column, it is locally changed to 4. In the output file, the initial rows of this column would be blank, data would start at D5.

49.21. With Rows per record set to 2 in leading cells Name and Address, the component always writes one data row, skips one and then writes again. This way various data does not get mixed (overwritten by the other one). For a successful output, make sure Data offsets is set to 2.

49.22. Rows per record is set to 3. Data in the first and third column will start in their first row (because of their data offsets being 1). The second and fourth columns have data offsets 2 and 4, respectively. The output will, thus, be formed by 'zig-zagged' cells (the dashed ones – follow them to make sure you understand this concept clearly).

49.23. Writing into a template. Its original content will not be affected, your data will be written into Name, Surname and Age fields.

49.24. Partitioning by one data field

49.25. Mapping summary

49.26. Create Mask Dialog

49.27. Tableau Table Structure

49.28. Mapping Editor

49.29. Adding Child to Root Element

49.30. Wildcard attribute and its properties

49.31. Attribute and its properties

49.32. Element and its properties

49.33. Mapping editor toolbar

49.34. Binding of Port and Element

49.35. Generating XML from XSD root element

49.36. Source tab in Mapping editor

49.37. Content Assist inside element

49.38. Content Assist for ports and fields

49.39. Writing non-standard xml

50.1. Denormalizer code workflow

50.2. Merge

50.3. Normalizer code workflow

50.4. Rollup code workflow

50.5. SimpleGather

50.6. XSLT Mapping

50.7. An Example of Mapping

51.1. Source Tab of the Transform Editor in Joiners

51.2. Hash Join Key Dialog

51.3. Join Key Wizard (Master Key Tab)

51.4. Join Key Wizard (Slave Key Tab)

51.5. LookupJoin - how it works

51.6. Edit Key Wizard

51.7. LookupJoin with Range Lookup Table

51.8. An Example of the Join Key Attribute in the RelationalJoin Component

51.9. Join Key Wizard (Master Key Tab)

51.10. Join Key Wizard (Slave Key Tab)

52.1. Example of typical usage of Barrier component

52.2. Example of mapping for Fail component

52.3. Example of Loop component usage

54.1. Usage example of ParallelRepartition component

54.2. Example of actual working of ParallelRepartition component in runtime

55.1. DataBase Configuration

55.2. AddressDoctor Parameters

55.3. Input mapping wizard

55.4. Input mapping wizard

55.5. Output mapping

55.6. Transform Editor in ProfilerProbe

55.7. Import/Externalize metrics buttons

55.8. Validator rules editor

55.9. Validator - Active rules

55.10. Validator - Error output mapping

55.11. Validator - If - then - else without else branch

56.1. Foreign Key Definition Wizard (Foreign Key Tab)

56.2. Foreign Key Definition Wizard (Primary Key Tab)

56.3. Foreign Key Definition Wizard (Foreign and Primary Keys Assigned)

56.4. Transform Editor in HTTPConnector

56.5. Multipart entities in input mapping

56.6. Transform Editor in HTTPConnector

56.7. Choosing WS operation name in WebServiceClient.

57.1. Join Key Wizard (Master Key Tab)

57.2. Join Key Wizard (Slave Key Tab)

57.3. An Example of the Join Key Attribute in ApproximativeJoin Component

57.4. Matching Key Wizard (Master Key Tab)

57.5. Matching Key Wizard (Slave Key Tab)

60.1. Inspect Action Popup Dialog

60.2. Expressions View