Converting Dates – Masking

It’s a common task to convert dates found in external sources to the PDI/Apache Hop date format. While PDI/Apache Hop offers some typcial conversion masks as a drop down, you may need to create an own one. Here a cheat sheet how to use the masking parameters:

Symbol	Meaning	Type	Example
G	Era	Text	“GG” -> “AD”
y	Year	Number	“yy” -> “03””yyyy” -> “2003”
M	Month	Text or Number	“M” -> “7””M” -> “12””MM” -> “07””MMM” -> “Jul””MMMM” -> “December”
d	Day in month	Number	“d” -> “3””dd” -> “03”
h	Hour(1-12, AM/PM)	Number	“h” -> “3””hh” -> “03”
H	Hour (0-23)	Number	“H” -> “15””HH” -> “15”
k	Hour (1-24)	Number	“k” -> “3””kk” -> “03”
K	Hour (0-11, AM/PM)	Number	“K” -> “15””KK” -> “15”
m	Minute	Number	“m” -> “7””m” -> “15””mm” -> “15”
s	Second	Number	“s” -> “15””ss” -> “15”
S	Millisecond (0-999)	Number	“SSS” -> “007”
E	Day in week	Text	“EEE” -> “Tue””EEEE” -> “Tuesday”
D	Day in year (1-365 or 1-364)	Number	“D” -> “65””DDD” -> “065”
F	Day of week in month (1-5)	Number	“F” -> “1”
w	Week in year (1-53)	Number	“w” -> “7”
W	Week in month (1-5)	Number	“W” -> “3”
a	AM/PM	Text	“a” -> “AM””aa” -> “AM”
z	Time zone	Text	“z” -> “EST””zzz” -> EST””zzzz” -> Eastern Standard Time”
X	Time zone offset	Text	“XXX” -> “-08:00”
‘	Escape for text	Delimiter	“hour’h” -> “hour 9”
”	Single quote	Literal	“ss”SSS” -> “45’876”. Use two quote marks in a row to create a single quote in a string.

Source: https://help.hitachivantara.com/Documentation/Pentaho/9.2/Products/Common_Formats

Extract PDF Content with Tika

The recently released Apache Hop 1.1 version comes with a very handy new transform step, that allows you to extract the metadata and content of different fileformats like PDF using Apache Tika.

The transform step is available in a pipeline:

It allows you to output the content as Plain text, XML, HTML and JSON:

Result:

Inititial capital of a sentence

provided a sentence like:

neue Technologien zur Verkehrsoptimierung

should get a inital capital like:

Neue Technologien zur Verkehrsoptimierung

This can be achieved in Pentaho / Apache Hop by a java expression

text == null ? null : text.substring(0,1).toUpperCase() + text.substring(1)

from: https://forums.pentaho.com/threads/218295-Capitalize-first-letter-of-a-string/

Workflow executor

The step “Workflow executor” with a pipeline in Apache Hop allows you to execute the same workflow for several rows. Values from those rows can be passed down to the workflow as parameters. E.g.

Parent Workflow:

Sub-Workflow (executed for each row), using parameters

Run PDI or Apache Hop on Apple M1

Currently PDI and Apache Hop do not run natively on M1. There are reports using the ARM optimised Azul JDK, but this was not working for me, as the newest STW Library is not yet ARM compatible.

Instead the following procedure worked for me on my Apple Macbook M1 (1st Gen).

Installation JDK 8.

If other JVMs are installed, they should be selected as follows:

Which Java versions do I have installed?

/usr/libexec/java_home -V

results in my case Matching Java Virtual Machines (3):

16.0.2 (x86_64) "Oracle Corporation" - "Java SE 16.0.2" /Library/Java/JavaVirtualMachines/jdk-16.0.2.jdk/Contents/Home

1.8.0_302 (arm64) "Azul Systems, Inc." - "Zulu 8.56.0.23" /Library/Java/JavaVirtualMachines/zulu-8.jdk/Contents/Home

1.8.0_301 (x86_64) "Oracle Corporation" - "Java SE 8" /Library/Java/JavaVirtualMachines/jdk1.8.0_301.jdk/Contents/Home

Set Java_HOME Path correctly:

unset JAVA_HOME

export JAVA_HOME=$(/usr/libexec/java_home -v "1.8.0_301")

Check selected JVM
java -version

echo $JAVA_HOME

Set terminal to Intel mode according to: https://stackoverflow.com/questions/67972804/pentaho-data-integration-not-starting-on-new-mac-m1

And replace SWT.jar library (according to previous step)

With that changes, you can start PDI and Apache Hop on M1

Row denormaliser (Transpose)

Given a list of key value pairs, like extracted metadata from html meta tags:

To move the values in colums the Row denormalizer step of PDI or Apache Hope can be used:

This will transpose the rows to colums:

I cases there are more values for one key, those values could also be aggregated, like “Concatenate string seperated by ,”

Force Pentaho to use UTF8 with mySQL JDBC Driver

In order to insert/update UTF8 Data from a transformation in an DB field, the JDBC driver configuration has to be explicitly configured with characterEncoding=UTF-8

This then also works with polsih diacritics like “Młotkowski”

Format Dates with Full Month

Given the following JSON:

{ "Available online": "29 January 2020",
“Received": "8 November 2019", "Revised": [ "16 January 2020" ], "Accepted": "23 January 2020", "Publication date": "1 May 2020" }

The Date can be captured with dd MMMM yyyy:

Scraping Elsevier abstract pages

Get a list of Elsevier’s IDs via Crossref

To get a first impression about the content and size I was using the Crossref Rest API facet search:

https://api.crossref.org/works?filter=member:78&facet=published:*

So for 2020 there are 542k entries. In order to download metadata data (or in my case just the DOI, date of DOI registration and the Elsevier internal ID) I’m using again the REST API of Crossref.

Actually a Crossref query is limited to provide max 1000 DOIs. Using cursors, it’s however to possible to loop further and get about 100k DOIs before the API times out. So in order to get all publications from 2020 I created a monthly batch based on the created date like:

https://api.crossref.org/works?filter=member:78,from-created-date:2020-01-01,until-created-date:2020-01-31&select=DOI,created,alternative-id,&rows=1000&cursor=*

In PDI I’ve created a job, that handles the cursor and repeats the transformation with the REST Query as long there is a new cursor coming back from Crossref.

So I get a list with all alternative IDs (like: S0960982219315106) from Elsevier articles. With that I can create an URL to all abstract pages of this article: https://www.sciencedirect.com/science/article/abs/pii/S0960982219315106

Getting HTML Abstract Page

Using the Pentaho HTTP Client, it’s now possible to get the HTML of the this abstract page.

https://www.sciencedirect.com/science/article/abs/pii/S0960982219315106

It’s important that there’s a HTTP-Header like: “Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_5)”, otherwise the Pentaho Client will be routed to a error page.

Extracting data from abstract HTML

Elsevier makes it very easy to extract information about the article without going deep into the HTML-Structure of ScienceDirect. Within the HTML there’s a whole section with structured data in JSON:

view-source:https://www.sciencedirect.com/science/article/abs/pii/S0960982219315106

Loading the whole HTML in one field, the following Regex can be used to extract the json block:

*<script type="application/json" data-iso-key="_0">(.*)</script>
<iframe style="display: none.*

Pentaho Step: Regex evaluation (Content Tab: Activate “Enable dotall mode” & “Enable multiline mode”)

Extracting data from JSON

Having the JSON data block separated from the rest of the HTML, we now can pass it to the JSON-input step. There is really plenty of information and much more than you would see via front end.

Exploring Json-Data and getting JSON-Path via https://jsonpathfinder.com/

For the moment I’m mostly interested in the author and affiliation data:

Extracting Affiliations

In the JSON we can extract:

whole affiliation section
individual affiliation
id, label, name (textfn) of affiliation

Extracting Authors

extracting the authors works similar like the affiliation.

whole author section: $..[?(@.#name==’author’)]
individual author: $.$.id ; $.$.orcid ; $..$$.[?(@.#name==’given-name’)]._
label: $.$$.[?(@.#name==’cross-ref’)].$$.[?(@.#name==’sup’)]._

An author can have multiple labels/refids, emails, contributor-roles. In order to get those flat in a row separated by a coma, a group step can be used, while getting it back into the stream using the “stream lookup”-step:

Merge affiliations and authors

In a third step authors and affiliations are merged by the the label:

in order to get a list with authors and affiliations

Extracted authors and affiliations from Elsevier articles

Unespace / Convert HTML Character

When working with HTML you may want to change a string from HTML to default encoding (and vice versa). Pentaho data integration provides that option within the calculator step:

This allows the following change:

Detlef Günther <-> Detlef Günther