Utilities to develop ETLs

The com.neptuny.cpit.etl.util packages included in the ETL Development API (EDA) provide a rich set of utility objects to develop ETLs.

Date and time utilities

This section elaborates common methods present in the com.neptuny.cpit.etl.util.DateUtil Java class that the ETL Development API offers.

The ETL timestamp format

The standard ETL timestamp format is YYYY-MM-DD hh:mm:ss, for example "2011-07-16 19:20:30". It is identical to the ISO-8601 format, barring the "T" character between date and time, for example "2011-07-16T19:20:30+01:00"; the meaning of symbols in both formats is the same. The following table lists these symbols along with their meaning:

ETL Timestamp symbols

As in ISO-8601, the absence of the time zone designator indicates that the timestamp is in local time.

The writeTimestamp method

A useful method provided by the DateUtil class is writeTimestamp. It receives a timestamp in standard ETL format and prints it using a specified date and time format. Consider the following example in Java:

The date and time strings can be formatted in various ways using the rich character set provided by the module. The following is a list of all characters allowed in the format string:

Some useful format strings are already provided by the module, and are listed in the following table:

Format strings

Based on the formats mentioned in the table, the preceding Java code example can be rewritten as:

The readTimestamp method

Another useful method provided by the DateUtil class is readTimestamp. It recognizes most of the common timestamp formats and converts them into the standard ETL timestamp format. The following code examples in Java script illustrate the use of this method:

The setDefaultLocale method

For some formats, the interpretation is locale-dependent, for instance, the U.S. notation for dates is MM/DD/YYYY while the European one is DD/MM/YYYY. To resolve ambiguities when the date is formatted as XX/XX/YYYY, the DateUtil class provides the setDefaultLocale method, which is called before any date format is interpreted.

The following table presents a list of all supported locales:

Supported locales

Another effect obtained with the setDefaultLocale method is the ability to understand day and month names, and to format dates according to local settings.

Consider the following code example that illustrates how you can set the default locale for all new instances of DateUtil:

As an alternative, you can specify the locale for a single instance of DateUtil in the constructor. The following code examples explain how:

The following table lists all formats recognized by readTimestamp. The order of rows in the table is important, because the readTimestamp method parses the list from top to bottom and applies the first matching format.

Formats recognized by the readTimestamp method

The diffTs method

This method provides the difference in milliseconds between two timestamps, passed as parameters. The following code example illustrates the use of this method:

Other methods

DateUtil provides some other useful methods that modify seconds, minutes, hours, and days of the given timestamp. These are:

• addSeconds
• addMinutes
• addHours
• addDays

The following code example illustrates the use of these methods:

CSV file utilities

Comma Separated Value (CSV) files are a very common data format. EDA provides the com.neptuny.cpit.etl.util class that contains useful methods for processing such files.

The safeSplit method

This method splits fields in different lines of a CSV file based on the double quote (") string delimiter.

To better understand the utility of this method, consider the following example where a particular line requires to be split based on the double quote delimiter:

If you use the safeSplit method, you can split the line and obtain 3 fields as expected. The following code example explains how this is done:

The detectSeparator method

This method automatically detects field separators present in different lines of a CSV file.

The following code example illustrates the usage of this method:

The technique that this method uses to find the separator is based on heuristics: it assumes that if a line in the CSV file contains a character that is not a word character, number, parenthesis, or a quoting delimiter, then it probably is a field separator.

Socket utilities to handle TCP and HTTP socket connections

The ETL Development API provides various methods to handle socket connections that are present in the following class:

• (com.neptuny.cpit.etl.util.SocketUtil)

You can connect to a remote host that supports TCP and UDP connections using the following method:

• connect(protocol, host, port)

Once the connection is established, the following methods can be used to send and receive data through the socket:

• send(message)
• receive(max_row) (the max_row parameter is optional)

The following code examples illustrate the usage of methods discussed so far in this section: