Learn Pentaho - Advanced Sources & Targets

#Introduction

In episodes 4-7, you worked with databases and CSV files. In the real world, data sources are far more diverse: Excel files sent by other people, JSON from APIs, XML from legacy systems, all the way to data in Hadoop and cloud storage. This episode opens the door to all those sources.

You'll learn the step for each format, the pattern for reading REST APIs and JSON, connections to Hadoop and cloud, how to load into data warehouses, and the lookup and caching techniques that keep performance healthy.

#Relational and File Data Sources: Excel, XML

Input steps for files in Spoon follow intuitive names. Two that are often overlooked despite being very useful:

• Excel input: reads .xls and .xlsx files. Pick the sheet, specify the first row where data starts, and use Get Fields to read the structure. Important for files whose headers aren't on the first row.
• XML input: reads XML files. Here you work with XPath — select the element that becomes the data "rows" and map fields from attributes or child elements.

For XML, a basic understanding of XPath helps a lot. Example: for a document with the structure <orders><order id="1"><total>100</total></order></orders>, the rows are order elements, the id field is an attribute, and total is a child element. PDI lets you specify field locations visually in the XML input step.

#Excel with Multiple Sheets

Excel files from business users often contain several sheets at once — one sheet per branch or per month. In Excel input, enable the Get Sheet Names option to read the sheet list as its own stream, then determine which sheet is the main data source. Also note that the first row often contains a title that must be skipped: set Start row to skip the title row and check Is Header Names Present if the first row holds the column names.

#JSON with Nested Structures

Not all JSON is flat. Nested objects like {"customer": {"nama": "Budi", "alamat": {"kota": "Jakarta"}}} need JSON Path to reach fields inside — for example $.customer.alamat.kota. In the JSON input step, each selected path becomes one column, and you can select several fields from the same object in a single dialog. For arrays inside objects, combine the array path with the field path so Spoon produces one row per array element.

This pattern also applies when an API returns a paginated response: the second and subsequent responses are fetched by modifying the page parameter, then all results are combined. For large volumes, combine REST client with a batched Table output so memory doesn't blow up.

#Reading JSON and REST APIs

Modern data often arrives as JSON, whether from files or APIs. The two steps you must master:

• JSON input: reads JSON files or JSON strings. Select the path to the data array — for example $.orders[*] — and map fields from the objects inside it.
• REST client: calls a REST API and receives the response. It can be JSON, XML, or plain text, complete with HTTP headers and authentication.

The most common pattern: the REST client step calls an API and receives JSON, then its output feeds into the JSON input step to be split into rows. Here's an example of its shape:

REST client -> JSON input -> Transform -> Output

Let's look at its connection to a simple curl command equivalent to what the REST client step does:

curl -s -H "Authorization: Bearer TOKEN" https://api.example.com/v1/orders

The JSON response is then split by JSON input. Remember, the REST client step in Spoon does the same thing from inside a transformation, with a result you can hold as fields for further processing.

#Hadoop and Cloud Storage Connectors

PDI Enterprise provides the Pentaho Big Data plugin for connecting to the Hadoop ecosystem; the community version can use HDFS steps and basic connectors. What you need to understand:

• Hadoop/HDFS: reading and writing files in Hadoop. File formats matter — Parquet, Avro, and ORC offer compression and performance far above CSV, and will be covered in episode 17.
• Cloud storage: connectors for object storage like Amazon S3, Azure Blob, and Google Cloud Storage are available as plugins. They work the same way as regular file connectors, only the URL and credentials are cloud-based.

Because these connectors depend on plugins and versions, don't hesitate to check the official Pentaho documentation for installation steps matching your PDI version.

#Working with Cloud Storage

Cloud connectors follow the same pattern on all platforms: provide credentials (access key, secret, or role-based auth), specify the bucket and path, then use the step like reading a regular file. To try object storage access without opening Spoon, use the provider's CLI, for example aws s3 ls s3://bucket-lab — this is the fastest way to confirm credentials and path are correct before assembling a transformation. For modern architectures, many teams use PDI to write data to object storage in Parquet format (episode 17), then let analytical engines read directly from there. This turns cloud storage from a mere file target into the foundation of a data lake.

#Writing to Data Warehouses and Analytics Stores

As a target, PDI is very flexible. For warehouse workloads, the common patterns:

• Table output: writes to a target table with the Truncate table option for full replaces.
• Dimension lookup/update: the special pattern for slowly changing dimensions (SCD), which will be covered in episode 15.
• Bulk loading: for large volumes, use a Bulk load step or the SQL COPY/BULK INSERT commands, which are far faster than per-row inserts.

For analytics stores, PDI can write to OLAP databases like PostgreSQL/MonetDB, or export data to Parquet files for analytical engines to read. The key: understand the target's characteristics — batch size, indexing, and load mechanism — so loading doesn't slow down as the data grows.

Here's an example bulk-loading approach for large volumes — PDI exports data to a CSV file, then calls the database's bulk command from a Shell job entry:

psql -h localhost -d warehouse \
  -c "\copy fact_penjualan FROM '/tmp/staging.csv' WITH (FORMAT csv, HEADER true)"

INSERT INTO fact_penjualan
SELECT * FROM staging_penjualan;

Compared to per-row inserts from Table output, this approach can be tens of times faster for millions of rows — especially when network and database round-trips become the dominant cost.

#Lookup and Caching for Performance

As data volume grows, per-row lookup becomes the most common bottleneck. The optimization techniques you should know:

• Database lookup with caching: enable the Enable caching and Lookup all rows at start options. Small reference tables that rarely change are loaded once into memory, saving thousands of queries.
• Stream lookup: loads the reference stream into memory and does the search with no database at all.
• Table input with SQL join: if possible, do the join at the database level with one big query instead of per-row lookups. This is often far faster.
• Proper indexing: make sure the lookup key columns have indexes in the database.

#Conclusion

In episode 8 you widened PDI's reach: reading Excel, XML, JSON, and REST APIs; getting to know Hadoop and cloud storage connectors; writing to data warehouses and analytics stores; and applying lookup and caching for performance.

The key takeaways:

• Every format has its own step: Excel input, XML input, JSON input, REST client — know when to use each.
• The REST client-to-JSON input pattern is the main path for using APIs in pipelines.
• Hadoop and cloud connectors open access to data lakes and big data.
• Optimizing lookups with caching and SQL joins is the key to keeping performance healthy as volumes grow.

In episode 9, we tidy up our assets: metadata, repository, and version control — managing connections and shared objects, understanding the difference between repository and file-based storage, applying Git to PDI projects, and managing environments and variable substitution.