Kamil Nešetřil

Environmental data management and analytics

User Tools

Site Tools

Differences

This shows you the differences between two versions of the page.


Previous revision
—en:cheatsheet [2024-03-21] (current) – Kamil Nešetřil
Line 1: Line 1:
 +======Pentaho Data Integration Cheat Sheet======
 +//This is a short guideline for Kettle: Pentaho Data Integration (<nowiki>PDI</nowiki>) – mainly with <nowiki>Spoon</nowiki> – the development environment//. First read general information about [[tools#pentaho_platform|Pentaho platform and PDI]].
  
 +> Since Hitachi acquired Pentaho, the development of the Pentaho platform has stagnated, and the platform's website is confusing. The original author of PDI (Matt Casters) created [[https://hop.apache.org/|Apache Hop]], a fork of PDI that finally has elegant architecture and is being intensively developed. PDI transformations can be imported into it.
 +
 +{{ :spoon.png?nolink|}}
 +===== How to start =====
 +  - Install [[https://www.java.com/en/download/manual.jsp|Java (64-bit)]]((64-bit is necessary! \\ [[https://adoptopenjdk.net/?variant=openjdk11&jvmVariant=hotspot|Open-source Java]] is available if needed.\\ 
 +If you cannot install it, use the [[https://portableapps.com/apps/utilities/OpenJDK64|portable]] version.\\ 
 +When Java gets **updated** then ''JAVA_HOME'' path in Windows needs to be updated accordingly.)).
 +  - Download  [[download>https://www.hitachivantara.com/pentaho/pentaho-plus-platform/data-integration-analytics/pentaho-community-edition.html|Pentaho Data Integration (Community Edition)]].
 +  - Unzip the file to the folder of your choice.
 +  - Run ''<nowiki>Spoon</nowiki>.bat''. In case of problems: [[vid>https://www.youtube.com/watch?v=PH6mWe3YVCQ|video]]
 +  - Go to the **Design** tab. \\ {{:spoon_tabs.png}}
 +  - Drag and drop items from the left bar to the canvas.
 +
 +===== Some steps used in transformations =====
 +^ Symbol                    ^ Name             ^ Description   ^
 +| {{ :pdi07_textfileinput.png?40&nolink }} | [[https://help.hitachivantara.com/Documentation/Pentaho/9.4/Products/Text_File_Input|Text file input]]          | Use for CSV also (not ''<nowiki>CSV</nowiki> file input'' that cannot process the whole folder). |
 +|Other steps for data input and output from/to databases, other sources (e-mail, local computer, FTP, HTTP), and files (MS Excel, MS Access, [[wpcs>Shapefile|ESRI SHP]], [[wpcs>XML]], [[wpcs>JSON]], [[wpcs>YAML]], [[wpcs>RSS]], [[wpcs>DBase|dBase]], ZIP, etc.) |||
 +| {{ :pdi08_textfileoutput.png?40&nolink }} | [[https://help.hitachivantara.com/Documentation/Pentaho/9.4/Products/Text_File_Output|Text file output]]       | Can set huge ''length'' and return an error. Solution: do not define ''length''.                                                                                                                                                                                                                                                                                                           |
 +| {{ :cheatsh17.png?40&nolink }}  | [[https://help.hitachivantara.com/Documentation/Pentaho/9.4/Products/Table_Output|Table output]]                         |                                                                                                                                                         |
 +| {{ :pdi03_msexcelwriter.png?40&nolink }} | [[https://help.hitachivantara.com/Documentation/Pentaho/9.4/Products/Microsoft_Excel_Writer|Microsoft Excel Writer]]                                                          |                                                                                                                                                                                                                                                                                                                                                                          |
 +| {{ :cheatsh05.png?40&nolink }}  | [[https://pentaho-community.atlassian.net/wiki/spaces/EAI/pages/371558145/Filter+Rows|Filter rows]]                                                                                         | For multiple options, use [[https://help.hitachivantara.com/Documentation/Pentaho/9.4/Products/Switch-Case|Switch-Case]].                                                                                                                                                                       |
 +| {{ :cheatsh06.png?40&nolink }}  | [[https://pentaho-community.atlassian.net/wiki/spaces/EAI/pages/376442115/Formula|Formula]]                                                                                                 | More functions than ''Calculator''.                                                                                                                                                                                                               |
 +| {{ :pdi06_calculator.png?40&nolink }} | [[https://help.hitachivantara.com/Documentation/Pentaho/9.4/Products/Calculator|Calculator]]                       | Faster than ''Formula''.                                                                                                                                                                                                                                                                                                                |
 +| {{ :cheatsh09.png?40&nolink }}  | [[https://help.hitachivantara.com/Documentation/Pentaho/9.4/Products/Group_By|Group by]]                                 |                                                                                                                                                                                                                                             |
 +| {{ :cheatsh14.png?40&nolink }}  | [[https://help.hitachivantara.com/Documentation/Pentaho/9.4/Products/Select_Values|Select values]]                       |                                                                                                                                                                                            |
 +| {{ :cheatsh15.png?40&nolink }}  | [[https://help.hitachivantara.com/Documentation/Pentaho/9.4/Products/Sort_rows|Sort rows]]                                                                                             | Also an option: ''Only pass unique rows?''                                                                                                                                                                         |
 +| {{ :cheatsh13.png?40&nolink }}  | [[https://help.hitachivantara.com/Documentation/Pentaho/9.4/Products/Replace_in_String|Replace in string]]                                                                             |                                                                                                                                                   |
 +| {{ :cheatsh16.png?40&nolink }}  | [[https://help.hitachivantara.com/Documentation/Pentaho/9.4/Products/Split_Fields|Split Fields]]                                                                                       |                                                                                                                                                                                                                                                                                                               |
 +| {{ :pdi16_stremlookup.png?40&nolink }} | [[https://pentaho-community.atlassian.net/wiki/spaces/EAI/pages/371558139/Stream+Lookup|Stream lookup]]                                                                              | To join two streams (tables) without the need to sort them.                                                                                                                                                                                                                                                                                                                                                              |
 +| {{ :pdi20_rownorm.png?40&nolink }} | [[https://help.hitachivantara.com/Documentation/Pentaho/9.4/Products/Row_Normaliser|Row Normaliser]]                  | ''Type field'' (name of the new column of categories)\\ ''Fieldname'' (input header)\\ ''Type'' (values of input categories)\\ ''New field'' (output header of values) – //needs to be one value for all//                                                                                                                                                                                                   |
 +| {{ :pdi21_rowdenorm.png?40&nolink }} | [[https://help.hitachivantara.com/Documentation/Pentaho/9.4/Products/Row_Denormaliser|Row denormaliser]]            | //Key// – input categories.\\ ''The key field'' (name of the input colums with categories),\\ ''Group field'' (what identifies the whole future row – e.g. //filename//),\\ ''Target fieldname'' = ''Key value'' (single categories),\\ ''Value fieldname'' (name of the input column with values)\\ [[resources#excel_add-ins_for_data_processing|More: Microsoft Power Query for Excel]]|
 +| {{ :pdi17_setvar.png?40&nolink }} | [[https://pentaho-community.atlassian.net/wiki/spaces/EAI/pages/371558231/Set+Variables|Set Variables]]                                                                                   | In other transformations, this variable can be used as a variable or as a parameter. The parameter can have a default value (taken into effect if the variable is not defined).      |
 +| {{ :pdi18_etlmetadatainjec.png?40&nolink }} | [[https://help.hitachivantara.com/Documentation/Pentaho/9.4/Products/ETL_metadata_injection|ETL Metadata Injection]]    | To control the transformations. Combine with ''Transformation Executor''.\\ [[https://support.pentaho.com/hc/article_attachments/360003635351/PDI_Techniques_-_Design_Guidelines.pdf|Best practices]].\\ [[https://planet.mysql.com/entry/?id=27425|Matt Casters: Parse nasty XLS with dynamic ETL]] \\ At the end of the [[http://kettle.bleuel.com/2016/04/14/pentaho-6-1-part-2/|article]] is an example including source codes. \\ Alternative: run the transformation in job and check //Execute every input row// – [[vid>https://www.youtube.com/watch?v=A8-oQHFi4VA|video]].|
 +| {{ :pdi19_transformexe.png?40&nolink }} | [[https://help.hitachivantara.com/Documentation/Pentaho/9.4/Products/Transformation_Executor|Transformation Executor]]  | Every row runs a new transformation.                                                                                                                                                                                                                                                                                                                                                         |
 +| {{ :cheatsh01.png?40&nolink }}  | [[https://pentaho-community.atlassian.net/wiki/spaces/EAI/pages/371558172/Add+Constants|Add Constants]]                                                                                     |                                                                                                                                                                                                                                                                                                                                                                              |
 +| {{ :cheatsh02.png?40&nolink }}  | [[https://pentaho-community.atlassian.net/wiki/spaces/EAI/pages/375133731/Analytic+Query|Analytic Query]]                                                                                   | To involve data from multiple rows. Aggregation.                                                                                                         |
 +| {{ :cheatsh10.png?40&nolink }}  | [[https://pentaho-community.atlassian.net/wiki/spaces/EAI/pages/386793725/Mail+step|Mail]]                                                                                                       |                                                                                                                                        |
 +| {{ :cheatsh11.png?40&nolink }}  | [[https://help.hitachivantara.com/Documentation/Pentaho/9.4/Products/Modified_Java_Script_Value|Modified Java Script Value]]                                                           |                                                                                                                                                                                                                                                              |
 +| {{ :pdi05_udefjavaexp.png?40&nolink }} | [[https://pentaho-community.atlassian.net/wiki/spaces/EAI/pages/376439964/User+Defined+Java+Expression|User Defined Java Expression]]                                                |                                                                                                                                                                                                                                                                                                                    |
 +| {{ :cheatsh12.png?40&nolink }}  | [[https://help.hitachivantara.com/Documentation/Pentaho/9.4/Products/Pentaho_Reporting_Output|Pentaho Reporting Output]] | Feed and create reports designed in PRD.                                                                               |
 +| {{ :pdi10_addsequence.png?40&nolink }} | [[https://help.hitachivantara.com/Documentation/Pentaho/9.4/Products/Add_sequence|Add sequence]]                                                                                |                                                                                                                                                                                                                                                                                                                                                                                     |
 +| {{ :pdi15_regexeval.png?40&nolink }}  | [[https://help.hitachivantara.com/Documentation/Pentaho/9.4/Products/Regex_Evaluation|Regex evaluation]]           | Regular expressions.  [[cheatsheet#regular_expressions|My examples below]].                                                                                                                                                                                                                                                                                        |
 +| | [[https://pentaho-community.atlassian.net/wiki/spaces/EAI/pages/371558152/Dummy+do+nothing|Dummy (do nothing)]] | Useful for merging streams or to see result of some step (e.g. ''Filter rows'').                                                                 |
 +
 +Other steps:
 +  * Google Spreadsheet Input/Output
 +  * [[git>mattyb149/load-text-from-file-plugin|Load Text From File]]
 +  * [[https://www.hitachivantara.com/en-us/pdfd/datasheet/pentaho-machine-learning-orchestration-datasheet.pdf|Machine Intelligence]] uses engines: R, Python, Weka, or [[https://deeplearning4j.konduit.ai/|Deeplearning4j]].
 +  * [[git>pentaho-labs/pmi-visualization|PMI Visualization]] Plugin {{:pmi_visu_plugin.jfif?nolink|PMI Visualization – 3D Exploration and Scatter Plot Matrix}}
 +
 +===== Often used job entries =====
 +^ Symbol ^ Name ^ Description ^
 +| {{ :cheatsheet_job_02.png?40&nolink }}  | [[https://pentaho-community.atlassian.net/wiki/spaces/EAI/pages/370967988/Start|START]] |  |
 +| {{ :cheatsheet_job_03.png?40&nolink }}  | [[https://help.hitachivantara.com/Documentation/Pentaho/9.4/Products/Transformation_(job_entry)|Transformation]] |  |
 +| {{ :cheatsheet_job_04.png?40&nolink }}  | [[https://pentaho-community.atlassian.net/wiki/spaces/EAI/pages/372703425/Success|Success]] |  |
 +| {{ :cheatsheet_job_01.png?40&nolink }}  | [[https://pentaho-community.atlassian.net/wiki/spaces/EAI/pages/371558464/Get+a+file+with+FTP|Get a file with FTP]] |  |
 +| {{ :cheatsheet_job_05.png?40&nolink }}  | [[https://pentaho-community.atlassian.net/wiki/spaces/EAI/pages/371558462/Mail|Mail]] |  |
 +| {{ :cheatsheet_job_06.png?40&nolink }}  | [[https://pentaho-community.atlassian.net/wiki/spaces/EAI/pages/372703451/Unzip+file|Unzip file]] |  |
 +| {{ :cheatsheet_job_07.png?40&nolink }}  | [[https://pentaho-community.atlassian.net/wiki/spaces/EAI/pages/372081674/Truncate+tables|Truncate tables]] |  |
 +
 +Official source with all [[https://help.hitachivantara.com/Documentation/Pentaho/9.4/Products/Transformation_step_reference|transformation steps]] and  [[https://help.hitachivantara.com/Documentation/Pentaho/9.4/Products/Job_entry_reference|job entries]].
 +
 +=====Regular expressions=====
 +
 +**Table** Selection of the input files (regex corresponds to the file name)
 +^Description ^Reguar expression  ^
 +|''.xlsx'' files |''.*\.xlsx'' |
 +|All files|''.*'' |
 +|Files starting with ''facts'' | ''facts.*''|
 +
 +**Table** Select part of a text string
 +^Description  ^Regular expression  ^Input  ^Output  ^
 +|Between brackets |<code reg>.*\((.*)\).*</code> |stanice: ČK (9 m n.m) |9 m n.m |
 +|Up to '','','';'' or ''.''|''([^\s?:(?!;).]+).*'' | |
 +|Up to '','', '';'', ''.'' or similar|''([^\s]+).*'' | |
 +
 +
 +=====Tips and tricks=====
 +  * Empty rows in GUI dialogs cause errors.
 +  * Manage errors in separate streams. The first step in a transformation cannot deal with error rows because they do not exist in PDI yet.
 +  * Use [[https://help.hitachivantara.com/Documentation/Pentaho/9.4/Products/ETL_metadata_injection|ETL metadata injection]] step for more complex transformations (see above).
 +  * Use variables and parameters (see above)
 +  * Use relative paths (''${Internal.Entry.Current.Directory}'')
 +  * Check ''Date Format Lenient'' or ''Lenient number conversion'' if data type is not resolved properly or returns error.
 +  * To export to e.g. SQLite, first create the table by SQL and then load data there (even in the same transformation because scripts are executed first).
 +  * [[https://diethardsteiner.github.io/pdi/2017/04/21/PDI-Get-Metadata.html|Automatically source Metadata for ETL Metadata Injection]] (automated from different Excel spreadsheets)
 +  * [[git>rpbouman/kettle-cookbook|kettle-cookbook]] Automated documentation
 +  * [[https://support.pentaho.com/hc/en-us/articles/360000307943-Pentaho-Data-Integration|Best Practices]] – detailed pdf
 +  * [[vid>http://iptv.usp.br/portal/video.action?idItem=23309&idVideoVersion=19916|Video]] history of <nowiki>PDI</nowiki>
 +
 +
 +  * **FAQ [[https://community.hitachivantara.com/s/article/data-integration-kettle|(source)]]**
 +    * //When I start ''<nowiki>Spoon</nowiki>.bat'' in a Windows environment nothing happens. How can I solve it?//
 +      * Edit the ''<nowiki>Spoon</nowiki>.bat'' file and:
 +        * Replace in the last line ''start javaw'' with only ''java''.
 +        * Add a ''pause'' in the next line.
 +        * Save and try it again.
 +    * //How to use JNDI?//
 +      * If you look inside the PDI main directory you'll see a sub-directory called ''simple-jndi'', which contains a file named ''jdbc.properties''. You should change this file, so the JNDI information matches the one you use in your application server.
 +
 +<html><center><iframe width="560" height="315" src="https://www.youtube.com/embed/A4wZJqovMP8" frameborder="0" allow="accelerometer; autoplay; encrypted-media; gyroscope; picture-in-picture" allowfullscreen></iframe></center></html>
 +
 +===== References =====
 +ROLDÁN, María Carina, 2017. //Learning Pentaho Data Integration 8 CE : Third Edition//. [[https://www.packtpub.com/product/learning-pentaho-data-integration-8-ce-third-edition/9781788292436|Packt Publishing]]. ISBN 978-1-78829-007-4.