SPUPad API

The SPUPad provides a way for a UDX to store data that remains in memory throughout the life of a transaction.

All memory allocated to the SPUPad is automatically released at the end of a transaction. The finalize() method (see finalize() is often used to call saveString() or saveTable() to write the final results of a calculation to the SPUPad, since it is called after the final row of data has been processed by the UDX.

To use the SPUPad to pass data between multiple SQL statements, it is necessary to surrounded all of the SQL statements in a single begin/commit block due to the SPUPad being completely cleared at the end of each transaction. The primary use cases for storing data in the SPUPad are:
  • Multiple output streams during ELT processing.
  • An nzLua UDTF can be used to parse a set of input records, outputting the good records and storing the bad records in the SPUPad. A second SQL statement would then pull the bad records back out of the SPUPad and store them into an error table.
  • Lookup tables.
  • In some cases it may be useful to store a lookup table in the SPUPad rather than joining the lookup table using SQL. This can be very useful in cases where, rather than finding an exact match, the UDX needs to find the "best" match based on the lookup data or a set of patterns. This type of "join" can be extremely difficult and inefficient to expression with SQL.
  • Configuration data.
  • The SPUPad can be used to hold configuration data or source code, which will affect how a UDX operates. For example, nzLua source code could be stored in a table, loaded into the SPUPad via one SQL statement, and then used to process a set of data using a second SQL statement.

The nzLua examples provided as part of the nzLua distribution also have the save_string.nzl and restore_string.nzl scripts, which demonstrate usage of the SPUPad.