navigation
search
CloverETL examples
To download example package go to download page.
Index of all examples
| graphAggregateSorted.grf | This graph illustrates usage of Aggregate component. |
| graphAggregateUnsorted.grf | This graph illustrates usage of Aggregate component. |
| graphAproximativeJoin.grf | In this example data are read from from file customers0.dat and from database table employee; then for both flows there is generated matching key consisting of 4 letters of last name (flat file: lname, database: last_name) and 3 letters of first name (flat file: fname, database: first_name) . Approximative join component joins data from thees flows (sorted due the matching key); it compares records with the same matching key only, but to conforming output sends only thees, which join key is similar enough (conformity attribute), so among conforming records you can found such record: “4 Damstra Robert Damstra Roberta 0.875” - with conformity 0.875 (conformity equals 1 means that records are identical). This graph illustrates usage of CustomizedRecordTransform class too. This class extend abstract class DataRecordTransform and allows to create complex transformation in relatively easy way. |
| graphBlobReformat.grf | This graph illustrates handling of BLOB field. |
| graphCheckForeignKey.grf | This graph shows usage of CheckForeignKey component. Data are read from table customers and City and State fields are checked against the table of known cities. If city is not found between known cities null is substituted on requested fields. On output are printed customers, for which there wasn't found city and state in city table. |
| graphCloverData.grf | This graph illustrates usage of CloverDataReader/Writer components. This components reads/writes data in clover internal format and can be used for moving data between different graphs. Reading/writing data in clover format is faster then in any other format. This graph shows also how works StructureWriter component (it can be used for writing records and some additional information eg. in xml format). |
| graphDb2Load.grf | This graph illustrates usage of Db2DataWriterComponent. This component can be used in two ways: * data read from input port, are properly formated and saved in temporary file or named pipe (on Linux/Unix) and then load to database (phase 0) * data from existing file are lodaed to database (phase 1) Information about rejected records can be sent to output port. |
| graphDataPolicy.grf | This graph illustrates usage of dataPolicy attribute. With strict data policy, data are not processed - graph execution finishes with error; for controlled data policy, parseable records are processed, info about incorrect records is logged or saved in file; with lenient data policy, graph execution is successful, but there is no info about unparseable records. |
| graphDataValidation.grf | This graph ilustrates how to use Universal Data Reader component for input data validation. |
| graphDBExecute.grf | This graph illustrates how-to run SQL/DDL/DML commands againts database. Phase 0 illustrates working of DBExecute as stand alone component. In phase 2, procedure created in phase 0, is executed for each input record. Data produced by procedure are parsed to output record. |
| graphDBFJoin.grf | This graph illustrates complex transformation. It reads data about orders made and combines them (joins) with data about which items were purchased. It then adds information about which company made particular order/bought particular item. It illustrates usage of DBFDataReader,Sort,MergeJoin,HashJoin,Filter. It also shows how to embed data transformation routine directly into transformation graph layout file (both joins have the transformations embedded). Note: It is faster to reference record's fields by index as opposite to names – used in this example. |
| graphDBFJoinTL.grf | This graph illustrates complex transformation like preceding example, but transformations are written in Transformation Language. |
| graphDBJoin.grf | This graph shows usage of DBJoin component: data are read from flat file and joined with the records from database due to conforming key field (lname in records read from file, last_name in records read from database). |
| graphDBFLoad.grf | This graph illustrates reading data from DBF file. |
| graphDBLoad.grf | This graph illustrates how to propagate data to database. |
| graphDBLoad5.grf | This graph illustrates usage of dbFields and cloverFields parameters of DB_OUTPUT_TABLE component. Parameter dbFields allows to specify that only certain fields of target DB table will be populated. Parameter cloverFields allows to specify which input/Clover fields are used for populating target fields. The final mapping is determined by the order in which Clover fields and DB fields appear in the parameters or can be set directly. Note: parameter batchMode allows for grouping of inserts into DB thus increasing throughput. It works only with DBs/JDBC drivers which support this feature (e.g. Oracle). |
| graphDBLoad6.grf | This graph illustrates how to run hand-written SQL/DML against DB. Parameters (question-marks) are substituted by input fields (in order they are listed in cloverFields). Also target parameter types are devised from input field types - this may cause some problems when trying to map incompatible data types. |
| graphDBLookup.grf | This graph illustrates how to join data using reformat component. graphLookupJoin.grf shows how to do similar transformation in easier way. |
| graphDBRead.grf | This graph reads personal data from three sources and finds records with the same values on corresponding fields (lname – last_name, fname – first_name). Matching pairs are transformed into one output record and saved in intersection_customer_employee.txt file. |
| graphDBUnload.grf | In this example, the DBInputTable component connects to database and unloads all records from employee table. It then stores the records in employees.list.out file. |
| graphDBUnloadParametrized.grf | This graph illustrates how to read not all records from database. This example is similar to graphDBJoin.grf, but found records from database are not sent to transformation function, but directly to output port. For illustration in the next phase are read all data from the same table. |
| graphDBUnloadUniversal.grf | This graph illustrates usage of dynamic metadata generation based on SQL query. It allows unloading of database data without prior assembly of DB table metadata. Dynamic metadata can be for example used for easy migration of data between two different databases when one universal graph with parameters specifying which table should be unload/migrated can be used. This graph requires db_table parameter to be defined. When running this graph, use command like this one: java -cp ”../cloveretl.engine.jar:../lib/commons-logging.jar:../lib/log4j-1.2.12.jar:../lib/javolution.jar:javaExamples” org.jetel.main.runGraph -plugins ../plugins-P:db_table=Employee graphDBUnloadUniversal.grf Note: where clause 1=0 in SQL query in dynamic metadata definition is there only for speeding up query processing - no real data is needed, only metadata describing the result - thus we specify condition which ensures that no data should really be returned - it is up to database whether it will optimize the execution plan based on this. |
| graphDenormalizeInline.grf | This graph demonstrates functionality of DENORMALIZER component with denormalization specified by inline Java source. |
| graphDenormalizeTL.grf | This graph demonstrates functionality of DENORMALIZER component with denormalization specified by inline Transformation Language source. |
| graphGenerateData.grf | This graph illustrates usage of Data Generator, Reader, Writer components. It also shows how to use shift attribute on metadata. |
| graphExtFilter.grf | This illustrates usage of Filter and HashJoin components. |
| graphExtFilter2.grf | This graph shows some more functionality of EXT_FILTER component. Filters all employees younger than 41 years. The current age is calculated as today's date minus date of birth. Notice that instead of classical comparison operators ==,!=, …etc… one can use their textual abbreviations .eq. .ne. .lt. .gt. …etc… |
| graphFilter.grf | This graph demonstrates functionality of Filter component. It can filter on text, date, integer, numeric fields with comparison [<, >, ==, <=, >=, !=]. Text fields can also be compared to a Java regexp using ~ (tilda) operator. A filter can be made of different parts separated by a ”;” (semicolon). If one of the parts is verified, the record passes the filter (it's an OR combination of the parts, and AND can be achieved by several filters cascaded). Date format used for comparison depends on input field's format – e.g. if input field HireDate has defined date format “yyyy-MM-dd”, then the same format must be used in filter when specifying date constant. This graph produces two output files: First with all employees who were hired before 1993-12-31. Second with all employees who have in their memo that they are “fluent” in some language Note: some of the comparison operators have to be XML encoded - e.g. ”>” as ”.gt.” etc. |
| graphFixLenByteMode.grf | This graph demonstrates difference between byte mode and char mode for FixLenDataReader. |
| graphIntersectData.grf | This graph demonstrates functionality of DATA_INTERSECT component - performs joining of two sorted (based on specified key) data flows (A and B) and outputs: 1. to port 0 records found only in flow A 2. to port 1 records found in A & B 3. to port 2 records found only in flow B . Records both in flow A and B should be unique |
| graphJms.grf | This graph illustrates usage of JMS Reader/Writer Components. It requires ACtiveMQ JMS server to be running on localhost! |
| graphJoinData.grf | This graph demonstrates functionality of SORT and MERGE_JOIN components. MERGE_JOIN component performs joining of master data with slave data based on specified key. It requires both master and slave data to be sorted according to used key. |
| graphJoinHash.grf | This graph demonstrates functionality of HASH_JOIN components. HASH_JOIN component performs joining of master data with slave data based on specified key. First all slave data (from all slave ports) are read and hash-tables are constructed from them. Then for every master record, attempt is made to find corresponding slave-records. HASH_JOIN performs better than SORT/MERGE_JOIN when you have relatively small slave data set and big master data set. It doesn't require master and slave data to be sorted. |
| graphJoinHashInline.grf | This graph demonstrates how to use HASH_JOIN component for 3way join. HASH_JOIN component performs joining of master data with slave data based on specified key. First all slave data are read and hash-tables are constructed from them. Then for every master record, attempt is made to find corresponding slave-records. |
| graphJoinHashUsingTransform.grf | This graph demonstrates functionality of HASH_JOIN components. Transformation function is written in Transformation Language Lite. |
| graphJoinHashUsingTransformLanguage.grf | This graph demonstrates functionality of HASH_JOIN components. Transformation function is written in Transformation Language, |
| graphJoinMergeInline.grf | This graph illustrates usage of MERGE component. It merges data based on specified key. It can merge master flow with a few slave flows. |
| graphLdapReader_Uninett.grf | Reads information from LDAP directory. |
| graphLdapReaderWriter.grf | This graph illustrates usage of LDAP Reader/Writer components. It requires LDAP server properly installed and configured. |
| graphLookupJoin.grf | This graph joins data from file with data from lookup table. |
| graphLookupReader.grf | This graph illustrates usage of in-memory lookup table. |
| graphMergeData.grf | This graph illustrates usage of MERGE component. It merges data based on specified key. |
| graphMysqlWriter.grf | This graph illustrates usage of MySQL Writer Component. |
| graphNormalizeInline.grf | This graph demonstrates functionality of NORMALIZER component with normalization specified by in-line Java source. |
| graphNormalizeTL.grf | This graph demonstrates functionality of NORMALIZER component with normalization specified by in-line Transformation Language source. |
| graphOrdersReformat.grf | This example illustrates usage of Reformat component. Source data contains info about orders customers made. We add artificial key - ProductID which is a sequence of integers. We also combine individual fields of ship-to-address (address,city,country) into one single field. Reformat component calls reformatOrders class which implements RecordTransform interface (required by Reformat component). The directory in which recormatOrders class exists must be part of the Java classpath |
| graphOrdersReformatExternTransform.grf | This example illustrates usage of Reformat component with transformation code written in extern file. The source code is automatically compiled at runtime. For successful compilation, tools.jar library must be part of CLASSPATH. |
| graphOrdersReformatInline.grf | This example illustrates usage of Reformat component with transformation code (class) inlined (saved with the graph itself). The source code is automatically compiled at runtime. For successful compilation, tools.jar library must be part of CLASSPATH. |
| graphOrdersReformatUsingTransform.grf | This example illustrates usage of Reformat component with transformation written in Transformation Language Lite. |
| graphOrdersTLReformat.grf | This example illustrates usage of Reformat component with transformation written in Transformation Language. |
| graphParametrizedLookup.grf | This graph demonstrates usage of Node-level parameters. |
| graphPartition.grf | This graph demonstrates functionality of Partition component. This component can be used to split input data flow into several (based on number of connected output ports) output data flows. |
| graphPhasesDemo.grf | This graph illustrates usage of several components in different phases. First, all components from phase 0 are run, then the execution continues with phase 1. Data which crosses phase boundary is automatically buffered so the data producing node can finish its work and the data consuming node can later start reading the data. |
| graphRangeLookup.grf | This graph ilustrates usage of range lookup table. During phase 0 lookup table is read from xls file to memory and in the next phase for data from flat file are looked up records from lookup table. |
| graphRevenues.grf | This is practical illustration of usage CloverETL. This graph transforms data for branch bank. On inputs are clients, month interests and charges and half-year bonus. On outputs we have aggregated revenues by clients, clients without revenues and clients from database, who are not in input file. |
| graphSequence.grf | This example extends graphOrdersReformat.grf in a sense that the counter used for generating PRODUCTID is taken from Sequence object which is persistent between graph executions – i.e. you get continuous sequence of unique numbers. For successful compilation, tools.jar library must be part of CLASSPATH. |
| graphSequenceCheckData.grf | This graph illustrates usage of Sequence Checker Component. |
| graphSimpleCopy.grf | This graph demonstrates functionality of SimpleCopy component. Everything brought to SimpleCopy on port 0 is duplicated onto all connected output ports. It also shows functionality of Trash Component. It discards everything which is sent into it. Its purpose is debugging - shows how many records ended in it and can print incoming records, if desired (option debugPrint) |
| graphSimpleCopyEmbeddedMetadata.grf | This graph demonstrates functionality of SimpleCopy component. Everything brought to SimpleCopy on port 0 is duplicated onto all connected output ports. It also shows functionality of Trash Component. It discards everything which is sent into it. Its purpose is debugging - shows how many records ended in it and can print incoming records, if desired (option debugPrint). Metadata in this graph is embedded directly to graph instead of external file. |
| graphSimpleCopyLocale.grf | This graph demonstrates functionality of SimpleCopy component. Everything brought to SimpleCopy on port 0 is duplicated onto all connected output ports. It also shows functionality of Trash Component. It discards everything which is sent into it. Its purpose is debugging - shows how many records ended in it and can print incoming records, if desired (option debugPrint). Metadata in this graph is embedded directly to graph instead of external file. Output metadata is the same as input except that for BirthDate and HireDate, locale property is defined. It allows to specify language or language.Country locale which is used when formatting output. In this example, date info on input is expected to be in dd/MM/yyyy format, whereas on output it should follow US/UK standard → (usually defined as MM/dd/YYYY). |
| graphSimpleLookup.grf | This graph illustrates how to join data using reformat component. |
| graphSortData.grf | This graph illustrates usage of Sort component. It sorts data based on specified key. |
| graphSortUniversal.grf | This graph illustrates usage of Sort component. It sorts data based on specified key. It also shows how to use parameters/global properties within graph. For executing see Sort Universal example. |
| graphSystemExecute.grf | This graph ilustrates usage of System Execute Component |
| graphViewData.grf | This graph is a simple utility which demonstrates usage of parameters. It allows viewing content of particular data file (in delimited format). It reads in data and stores them in debug format. |
| graphXLSReadWrite.grf | In this example data are read from ORDERS.xls file, partitioned to 3 parts and each of thees parts are saved in orders.partitioned$.xls “multifile” in different sheets. (See Different ways of reading files example.) |
| graphXMLExtract.grf | In this example data are read from employees.xml file. “Mapping” attribute describes dependencies between xml nodes and metadata fields. |
| SCDType2_example1.grf | Graph with SCD transformation (see Slowly Changing Dimension (SCD) example). |
| SOAP_EXAMPLE.grf | This graph ilustrates how data contained in an XML file can be get via the SOAP protocol. Information about maximum air temperatures is get, processed and saved to output xls files. |
