Archiv der Kategorie: Data Integration

Pentaho Community Dashboards: Button-Komponente und Parameterübergabe

(English version)

Manchmal ist es am einfachsten, simple „Webanwendungen“ zur Dateneingabe mit den Pentaho-Dashboards zu entwickeln.  (RapidMiner hat vor einiger Zeit ähnliche Funktionen in den Server eingebaut, um statt nur Dashboards echte Webapps entwickeln zu können.)

Eine wichtige Rolle spielt hierbei die Button-Komponente. Mit ihr kann man die Eingabe abschießen und z. B. mit einer Kettle-Transformation die Daten verarbeiten.

Normalerweise ist das Zusammenspiel zwischen Dashboard-Parametern, Komponenten-Parametern und Datenquellen-Parametern ziemlich simpel und logisch, aber in dieser Konstellation kann es kompliziert werden, insbesondere wenn die Dashboard-Parameter auch noch anders heißen als die Parameter der Transformation.

In diesem Beispiel legen wir die Simple Parameters Param1, Param2 und Parameter3 im Dashboard an, und in der PDI-Transformation die Parameter Param1, Param2 und Param3. Sie bekommen entsprechende Default-Werte, damit wir sie im Output unterscheiden können. Parameter3 ist im Dashboard absichtlich anders benannt.

Wir legen eine Datenquelle vom Typ „kettle over kettleTransFromFile“ an und wählen die Transformationsdatei aus. Der Name eines Schrittes der Transformation wird unter „Kettle Step name“ festgelegt. Unter Variables geben wir die Namen der in der Transformation definierten Parameter (also Param1, Param2, Param3) ohne Value an (also bleiben alle Value-Felder leer). Unter Parameters wiederholen wir diese drei Namen, und als Value geben wir die gleichen Namen nochmal ein. (Also: Param1/Param1, Param2/Param2, Param3/Param3.)

Wichtig ist jetzt das korrekte Einstellen der Button-Komponente. Unter Advanced Properties wird zuerst „Action Datasource“ auf den Namen der Datenquelle gesetzt, und dann die Action Parameters eingegeben. Hier geben wir unter Arg die Parameternamen, die die Datenquelle erwartet, ein, also Param1, Param2 und Param3. Als Value aber die Namen der Dashboard-Parameter, also Param1, Param2 und Parameter3. Damit haben wir die Verbindung vom anders benannten Parameter zur Transformation geschaffen.

Pentaho Community Dashboards: The Button component and passing parameters

Sometimes, the easiest way to create a simple „data input web application“ is a Pentaho Dashboard. (RapidMiner put similar functionality into the Server to enable the development of web apps instead of only dashboards.)

The Button component has an important role when building applications: It is an easy way to take the input and call a Kettle transformation (for example) to process the data.

Usually, the interaction between Dashboard parameters, Component parameters and Datasource parameters is quite simple and logical. However, in this situation it can became more complicated, especially if the parameters in the dashboard and in the transformation have different names.

In this example, we create Simple Parameters called Param1, Param2 and Parameter3 in the dashboard, and Param1, Param2, Param3 in the PDI transformation. We assign them different default values so we can distinguish them in the output. Parameter3 is intentionally called differently in the dashboard.

We create the datasource of type „kettle over kettleTransFromFile“ and select the transformation file on the server. The name of one step in the transformation must be entered in „Kettle Step name“. In the Variables input, we enter the transformation parameter names Param1, Param2, Param3 without entering values. (The Value fields must stay empty.) In the Parameters, we enter the same names but this time also in the value fields: Param1/Param1, Param2/Param2, Param3/Param3.

Now it’s important to set up the Button component correctly. In the Advanced Properties, we set the Action Datasource to be the name of the data source, and then enter the Action Parameters. Here, the Arg names are the data source parameter names (Param1, Param2, Param3), and the Value names are the Dashboard parameter names: Param1, Param2 and Parameter3. This is how the link to differently named dashboard parameters is done.

PostgreSQL: Zugriff auf fremde Datenbanken

(English version)
Komplexe Lösungen bestehen häufig aus mehr als einer Datenbank, manchmal sogar unterschiedlichen Typs. Im Fall von Sco2t haben wir eine „geerbte“ MySQL-Datenbank, während neue Daten wegen der erweiterten GIS-Funktionalität in PostgreSQL liegen. Natürlich müssen gewisse Daten zwischen beiden Systemen synchronisiert werden.

Pentaho Data Integration ist dafür eine sehr gute Lösung und wird auch intensiv verwendet. Allerdings möchte ich in einigen Fällen sofort auf Änderungen reagieren und nicht erst eine Minute später. (Und auch nicht ständig Java-Prozesse starten, die die meiste Zeit keine Änderungen finden.) Dafür bieten sich PostgreSQL-seitig Trigger an, die Änderungen an einer Tabelle in der MySQL-Datenbank nachvollziehen.

PostgreSQL 9.1 hat die erste Version der Foreign Data Wrappers genannten Funktionalität bekommen, seit 9.3 können diese sogar in fremde Datenbanken schreiben. Ein FDW-Modul für den Zugriff auf andere PostgreSQL-Server ist in 9.4 im PostgreSQL-Contrib-Bereich dabei, Module für viele andere Datenbanken (auch NoSQL und Hadoop!) sind verfügbar.

(Linked Server sind schon seit Längerem in vielen Datenbanksystemen verfügbar, PostgreSQL hatte dafür „dblink“. Diese Funktionalität hat sich aber großteils auf Verbindungen zum eigenen Datenbanktyp beschränkt.)

EnterpriseDB hat ein FDW-Modul für MySQL realisiert. Derzeit muß man es selbst kompilieren, da in den üblichen Repositories keine fertigen Pakete enthalten sind. Das ist aber nicht schwierig.

Installation auf einem Debian-System:


git clone https://github.com/EnterpriseDB/mysql_fdw.git

apt-get install make gcc libmysqlclient-dev postgresql-server-dev-9.4

cd mysql_fdw

make USE_PGXS=1

make USE_PGXS=1 install

Damit ist das Modul übersetzt und als PostgreSQL-Extension installiert. Der Rest passiert als PostgreSQL-User mit Superuser-Rechten in SQL:


CREATE EXTENSION mysql_fdw;

CREATE SERVER mysql_server FOREIGN DATA WRAPPER mysql_fdw

OPTIONS (host '127.0.0.1', port '3306');

CREATE USER MAPPING FOR username SERVER mysql_server

OPTIONS (username 'myuser', password 'mypassword');

CREATE FOREIGN TABLE mysqltabelle (

-- Felder aus der MySQL-Tabelle mit ihrem Datentyp

)

SERVER mysql_server

OPTIONS (dbname='datenbankname', table_name='OriginalTabelleName');

Damit ist die Fremdtabelle „mysqltabelle“ in der PostgreSQL-Datenbank angelegt und kann wie eine normale Tabelle angesprochen, gelesen und beschrieben werden.

CREATE EXTENSION registriert das Modul in der aktuellen Datenbank. (Verwendet man mehrere Datenbanken auf einem Server, muß CREATE EXTENSION in jeder, die die Funktionalität braucht, ausgeführt werden.)

CREATE SERVER erzeugt die Verbindung zum fremden Datenbankserver und gibt die Verbindungsparameter an.

CREATE USER MAPPING ist notwendig, um PostgreSQL-seitige User mit einem User in der fremden Datenbank zu verknüpfen. Es können mehrere Mappings für unterschiedliche User angelegt werden.

CREATE FOREIGN TABLE definiert dann die Fremdtabelle und gibt ihren Namen in der fremden Datenbank an. Die Datentypen der Felder müssen in PostgreSQL existieren. Es ist möglich, Constraints wie NOT NULL oder Default-Werte schon PostgreSQL-seitig anzugeben.

Foreign Tables eröffnen ganz neue Möglichkeiten der Datenintegration: Daten müssen nicht mehr hin und her kopiert werden, stattdessen sind sie in der Master-Datenbank und die anderen Datenbanken arbeiten immer mit der aktuellen Kopie. Datenübernahmeprozesse können direkt in der Datenbank, ohne Zwischenschaltung einer ETL-Software ausgeführt werden. Ich bin gespannt, welche Anwendungsmöglichkeiten mir noch einfallen.

Accessing foreign databases in PostgreSQL

Many complex solutions use more than one database, sometimes even different database systems. At Sco2t we „inherited“ a MySQL database, but new data are stored in PostgreSQL because of the better GIS functionality. So some data need to be kept in sync between the two systems.

Pentaho Data Integration is a great solution for this and it’s already in heavy use. In some situations, however, I’d like to react to changes instantly instead of waiting a minute. (Also, I dont’t want to start Java processes frequently that don’t have to process any changes most of the time.) In PostgreSQL, we can use triggers for updating data in other tables, now even in the MySQL database.

PostgreSQL 9.1 was the first release with Foreign Data Wrappers functionality. With 9.3, even writing into foreign databases is supported. In 9.4 there is a FDW module for accessing other PostgreSQL servers in the Contrib area. Modules for accessing other database types (even NoSQL and Hadoop!) available.

(Linked server functionality has been available in many database systems for a long time. PostgreSQL had „dblink“. However, linked servers usually had to be of the same type.)

There is a FDW module for MySQL from EnterpriseDB. Currently we need to compile it ourselves, as there are no binary packages in the usual repositories. But it’s not hard.

This is the installation on a Debian system:


git clone https://github.com/EnterpriseDB/mysql_fdw.git

apt-get install make gcc libmysqlclient-dev postgresql-server-dev-9.4

cd mysql_fdw

make USE_PGXS=1

make USE_PGXS=1 install

After this, the module is compiled and registered as a PostgreSQL extension. The rest of the procedure has to be executed (with database superuser rights) in SQL:


CREATE EXTENSION mysql_fdw;

CREATE SERVER mysql_server FOREIGN DATA WRAPPER mysql_fdw

OPTIONS (host '127.0.0.1', port '3306');

CREATE USER MAPPING FOR username SERVER mysql_server

OPTIONS (username 'myuser', password 'mypassword');

CREATE FOREIGN TABLE mysqltable (

-- Fields of the MySQL table, including their type

)

SERVER mysql_server

OPTIONS (dbname='databasename', table_name='OriginalTableName');

The result of this script is a foreign table „mysqltable“ in the PostgreSQL database. This table can be used like any normal table, you can read and write it.

CREATE EXTENSION registers the compiled module in the current database. (When using multiple databases on a server, CREATE EXTENSION must be executed in each database that will contain foreign tables.)

CREATE SERVER creates the link to the foreign database server and defines connection parameters.

CREATE USER MAPPING is necessary to link a PostgreSQL user to a user in the foreign database. Multiple mappings can be created for different users.

CREATE FOREIGN TABLE defines the foreign table and its name in the foreign database. The field types must exist in PostgreSQL. It is possible to define constraints like NOT NULL or give default values on the PostgreSQL side.

Foreign tables open entire new worlds of possibilities for data integration. Master data don’t need to be copied to multiple databases: other databases can work directly on the original which is always current. Data integration processes run in the database, without a separate ETL software. It’ll be interesting to explore further ideas and usage scenarios.

Pentaho: Data integration in Dashboard ausgeben

Vor kurzem wurde ich gefragt, wie aufwändig es ist, das Ergebnis eines ETL-Jobs (also aus Pentaho Data Integration) im BA Server auf einem Dashboard auszugeben. Es ist gar nicht kompliziert und geht sehr schnell.

Als erstes erstellen wir eine Transformation und merken uns den Namen des Schrittes, dessen Ergebnis im BA Server ausgegeben werden soll. In diesem Beispiel lese ich den RSS-Feed von pentaho.com aus und selektiere daraus die Spalten Titel, Text und Datum.

Beispiel-Transformation
Beispiel-Transformation

Diese Transformation muß dann auf den Server geladen werden. Wenn man in Kettle ohne Repository oder mit einem Datei-Repository arbeitet, ist die Datei schon vorhanden; aus einem Datenbank-Repository müßte man sie exportieren (File/Export/To XML).

Die .ktr-Datei wird dann im BA Server unter Browse Files ins gewünschte Verzeichnis raufgeladen (Upload…).

Danach geht’s ans Erstellen des Dashboards. Im Hauptmenü Create New, dann CDE Dashboard. (Wenn dieser Menüpunkt nicht erscheint, muß man im Marketplace „Community Dashboard Editor“ installieren und den Server neu starten.)

Der erste Weg führt zu den Einstellungen des Dashboards:

Dashboard-Einstellungsdialog
Dashboard-Einstellungen

Interessant hier ist die Auswahl des Dashboard-Typs: blueprint oder bootstrap. Blueprint ist (noch?) Standard, aber viele neue Dashboards entstehen mit dem moderneren Bootstrap-Framework. Hier arbeiten wir mit dem Standard, blueprint.

In diesem Beispiel brauchen wir keine komplexe Dashboard-Struktur (die auch möglich wäre). Deswegen fügen wir nur eine Zeile und innerhalb der Zeile eine Spalte ein. Wir geben der Zeile einen Namen (RSSTableHTML) und stellen Span size auf 24, weil bei Blueprint die ganze Breite auf 24 Spalten aufgeteilt ist.

Dashboard-Struktur mit Spalteneinstellungen
Dashboard-Struktur

Als nächstes kommt die Datenquelle. Dazu wählen wir unter Datenquellen  aus den „KETTLE Queries“ den Eintrag „kettle over kettleTransFromFile“ und stellen die Eigenschaften ein.

Name: RSSFeedResult
Kettle Transformation File: (die hochgeladene Datei wählen)
Kettle Step name: Der Schritt, dessen Ergebnis ausgegeben werden soll

Wir können Cache=True belassen, um das Ergebnis für eine Stunde (3600 Sekunden) zu cachen.

Eigenschaften der Kettle-Datenquelle
Eigenschaften der Kettle-Datenquelle

Jetzt bleibt nur mehr die Tabelle. Unter Komponenten wählen wir Others und Table Component. Wir benennen die Komponente (RSSTable), wählen die Datenquelle (RSSFeedResult) und das HTML-Objekt, in dem die Tabelle angezeigt wird (RSSTableHTML). Fertig!

Eigenschaften der Tabellenkomponente
Tabellenkomponente

Wer mit dem Aussehen der Tabelle nicht zufrieden ist, kann unter Advanced Properties noch einiges umstellen und z. B. Style auf Classic umstellen.

Das Ergebnis der Bemühungen ist eine schöne Tabelle mit den Inhalten des RSS-Feeds.

Dashboard-Vorschau
Dashboard-Vorschau

Natürlich können die Kettle-Transformationen und die Dashboards beliebig komplex werden. Es ist nur zu beachten, daß der Datenerfassungsprozess nicht zu lang dauert, sonst warten die Benutzer des Dashboards zu lange.