Benetl a free ETL tool for files using postgreSQL and MySQL

Benetl quality indicators

Benoît Carpentier — 2024-11-07T05:58:28Z

Here are the quality indicators (SonarQube) of Benetl 5.9:

JSON example

Benoît Carpentier — 2021-07-31T12:29:18Z

Here is the content of the JSON test file:


 [
 {
 "id": "0001",
 "type": "donut",
 "name": "Cake",
 "ppu": 0.55,
 "batters":
 {
 "batter":
 [
 { "id": "1001", "type": "Regular" },
 { "id": "1002", "type": "Black Chocolate" },
 { "id": "1003", "type": "Blueberry" },
 { "id": "1004", "type": "Devil's Food" }
 ]
 },
 "topping":
 [
 { "id": "5001", "type": "None" },
 { "id": "5002", "type": "Glazed" },
 { "id": "5005", "type": "Sugar" },
 { "id": "5007", "type": "Powdered Sugar" },
 { "id": "5006", "type": "Chocolate with Sprinkles" },
 { "id": "5003", "type": "Chocolate" },
 { "id": "5004", "type": "Maple" }
 ] 
 },
 {
 "id": "0002",
 "type": "donut",
 "name": "Raised",
 "ppu": 0.55,
 "batters":
 {
 "batter": 
 [
 { "id": "1001", "type": "Regular" }
 ]
 },
 "topping":
 [
 { "id": "5001", "type": "None" },
 { "id": "5002", "type": "Glazed" },
 { "id": "5005", "type": "Sugar" },
 { "id": "5003", "type": "Chocolate" },
 { "id": "5004", "type": "Maple" }
 ] 
 },
 {
 "id": "0003",
 "type": "donut",
 "name": "Old Fashioned",
 "ppu": 0.55,
 "batters":
 {
 "batter":
 [
 { "id": "1001", "type": "Regular" },
 { "id": "1002", "type": "Chocolate" }
 ]
 },
 "topping":
 [
 { "id": "5001", "type": "None" },
 { "id": "5002", "type": "Glazed" },
 { "id": "5003", "type": "Chocolate" },
 { "id": "5004", "type": "Maple" }
 ]
 }
 ]

JSON will be understood by Benetl as this:

Add an extra field "ladate" for the date:

Then define the ETL:

Here is the data extracted from the JSON file:

These data loaded in the database:

Download here

Benoît Carpentier — 2020-03-26T22:03:56Z

The last version is Benetl 6.2.0.

Click here to start download

Click here to start download Linux version

01/04/2024, Benetl is clean software, report at:
11/02/2020, Benetl has a review on:
04/16/2019, Benetl in top 9 MySQL ETL tools:
12/04/2018, Benetl has a review on:

forum

Benoît Carpentier — 2011-04-16T19:55:34Z

Interesting example

Benoît Carpentier — 2010-08-10T19:38:35Z

Interesting example

In this example we are using two extra fields, a regular expression and a if condition (brought by Benetl version 3.5).

The aim is: if the field value1 contains the substring "asa" then we retrieve the value of this field except asa (value1 value without "asa").

Otherwise (if value1 doesn't contain "asa" we give the value "rien" (nothing in french)).

The datasource file "test3.txt" is:

entity;date;value1;value2
guillaume;16/04/2009;diasafi;sipo
ben;15/03/2009;olasaps;iksipo
thom;16/02/2009;pifo;ospo

It has 4 columns, the two last columns contain the values.

You can download the whole example at: test3.zip

Benetl for Linux

Benoît Carpentier — 2008-11-08T17:30:37Z

Benetl for Linux:

Benetl 6.2.0 passed tests under a Linux distribution, CentOS 7.

The .tar.gz file must be unzip in /opt directory.
Benetl 6.2.0 directory must be: /opt/benetl.

You have to define your java installation in etltool_gui.sh, and later in etltool.sh when you have defined your first EtlTool.

You have to change the following line:

export JAVA=/usr/lib/jvm/temurin-25-jdk/bin/java # depends on distribution

Don't forget to set postgreSQL configuration in file benetl_pg.xml in /opt/benetl.
Don't forget to set MySQL configuration in file benetl_my.xml in /opt/benetl.
To know more about it, please look at: 10. Benetl core configuration

Discover Benetl author

Benoît Carpentier — 2008-07-21T00:10:23Z

Benetl author is Benoît Carpentier.

Contact:	benoit@benetl.net

You can see his professional profile here:

External test passed

Benoît Carpentier — 2008-07-21T00:09:18Z

External test passed:

An ETL comparison test is described here:

http://marcrussel.files.wordpress.com/2007/08/benchmark-tos-vs-kettle.pdf

The data file is available here:

http://s3.amazonaws.com/kettle3/biginputfile.txt.gz

The current test passed by Benetl is described page 17 figure 18.

It is about 1 000 000 lines to manipulate and retrieve into database.

Control file of the test EtlTool with Benetl version 1.4: control file

Managing entities

Benoît Carpentier — 2008-07-21T00:08:54Z

Managing entities:

You can now regroup entities in entities groups, through out Benetl's interface.

This menu is frozen if you have no entity.

In this screen you can create entities groups:

In the current screen you can add entity to entities groups or remove entity from entities groups.

In order to do such task, you just have to click on an entity name, then will be able to check/uncheck entities groups this entity is bound to.

List of error messages

Benoît Carpentier — 2008-07-20T22:59:47Z

List of error messages:

When you run an EtlTool, a log file is created.
In this log file (which has the same name as your EtlTool) you can find the number of an error.

Here is the list of error messages:

1: there are too much fields in the line

2: field value is not "like" the operand (of date, entity, or value)

3: field value is not "unlike" the operand (of date, entity, or value)

4: "regex" in "part" is not working

5: "part" is not working

6: trouble with "date"

7: trouble with "entity"

8: "date" is not greater than operand

9: "date" is not valid

10: "date" is not greater than or equal to operand

11: "date" is not valid

12: "date" is not lesser than operand

13: "date" is not valid

14: "date" is not lesser than or equal to operand

15: "date" is not valid

16: trouble when using "plus" operator for values

17: trouble when using "minus" operator for values

18: trouble when using "divide" operator for values

19: trouble when using "multiply" operator for values

20: trouble with entity // unused, version 4.5

21: trouble with max function

22: trouble with min function

23: trouble with avg function

24: trouble with dist function

25: trouble with round function

26: trouble with ceil function

27: trouble with floor function

28: trouble with square function

29: trouble with pow function

30 : trouble with exp function

31 : trouble with log function

32 : trouble with asin function

33 : trouble with sin function

34 : trouble with acos function

35 : trouble with cos function

36 : trouble with atan function

37 : trouble with tan function

38 : trouble with inv function

39 : trouble with abs function

40 : trouble with substr function

41 : trouble with if function

42 : raw data date is before 1970

43 : trouble with batch mode

44 : function needs one argument

45 : function needs two arguments

46 : trouble with fact function