Description

This step reads data from an HBase table according to user-defined column metadata.

Options

Configure Query Tab

This tab contains connection details and basic query information. You can configure a connection in one of two ways: using the Hadoop cluster properties, or by using an hbase-site.xml and (an optional) hbase-default.xml configuration file.

Below the connection details are fields to specify the source HBase table to read from and a mapping by which to decode and interpret column values. Before a value can be read from HBase it is necessary to tell the step which column family it belongs to and what its type is. Furthermore, it is necessary to specify type information about the key of the table. This is where a mapping comes into play and the user must define a mapping to use for a given source table.

Below the Mapping name text field are fields to limit a query on the source table to a range of key values. The middle of the tab is dominated by a list of fields (as defined in the selected mapping) that the step will output. The user can opt to output all or some of the fields defined in the mapping. Rows from the table may be deleted to select a subset of the fields. Clearing all rows from the table indicates that all fields defined in the mapping should be output

At the very bottom of the tab information on the HBase key field is given. This shows the name of the Key (as defined in the mapping) along with its type in the HBase table.

Hadoop Cluster

The Hadoop cluster configuration dialog allows you to specify configuration detail such as host names and ports for HDFS, Job Tracker, and other big data cluster components, which can be reused in transformation steps and job entries that support this feature.

There are two fields by which the range of key values returned by a table scan can be limited. Leaving both blank will result in all rows being retrieved from the source table. The  Start key field allows the user to enter a starting key value from which to retrieve rows. This is inclusive of the value entered. The Stop key field allows a stopping key value for the scan to be entered. This is exclusive of the value entered. Both fields or the stop key field may be left blank. If the stop key field is left blank then all rows from (and including) the start key will be returned.

A formatting string must be provided for date values involved in a range scan (and optionally for numbers). There are two ways to provide this information in the dialog:

1. If the user has configured the step with fields from the mapping in question (i.e. the fields table in the Configure query tab is not completely blank), and the key is included in the fields to be output from the step, then a formatting string may be entered into the Format cell in the row corresponding to the key field.
2. If the user has not opted to output the key from the step, or, the user has opted to output all fields in the mapping by leaving the fields table in the Configure query tab blank, then formatting information can be supplied for start and stop key values independently by suffixing the start or stop key value with the formatting string following a '@' separator character. For example, a date start key value of 1969-08-28 can be specified as:

1969-08-28@yyyy-MM-dd

Beneath the Stop key field is a text field that allows the user to specify how many rows should be cached each time a fetch request is made to HBase. Leaving this blank uses the default, which is to perform no caching - i.e. one row returned per fetch request. Setting a value in this field will increase performance (faster scans) at the expense of memory consumption.

Create/Edit Mappings Tab

This tab creates or edits a mapping for a given HBase table. A mapping simply defines metadata about the values that are stored in the table. Since most information is stored as raw bytes in HBase, this enables PDI to decode values and execute meaningful comparisons for column-based result set filtering. The fields area of the tab can be used to enter information about the columns in the HBase table that the user wants to map. Selecting the name of an existing mapping will load the fields defined in that mapping into the fields area of the display.

A valid mapping must define meta data for at least the key of the source HBase table. The key must have an Alias specified because there is no name given to the key of an HBase table. Non-key columns must specify the Column family that they belong to and the Column name. An Alias is optional - if not supplied then the column name is used. All fields must have type information supplied.

To enable meaningful range scans over key values it is necessary for keys to sort properly in HBase. This is why we make a distinction between unsigned and signed numbers. Since HBase stores integers/longs in twos complement internally it is necessary to flip the sign bit before storing a signed number in order to have positive numbers sort after negative ones. Unsigned integers/longs are assumed to have been stored directly without without inverting the sign bit. Note that keys that are dates are assumed to have been stored as just signed or unsigned longs (i.e. number of milliseconds elapsed since the epoch). If you have a key that is date in string format (ISO 8601 date-time format is designed such that lexicographic order corresponds to chronological order) it can be mapped as type String and then PDI can be used to change the type to Date for manipulation in the transformation.

No distinction is made between signed and unsigned numbers here because no sorting is performed by HBase on anything other than the key. Boolean values may be stored in HBase as 0/1 integer/long or as strings (Y/N, yes/no, true/false, T/F). BigNumber may be stored as either a serialized BigDecimal object or in string form (i.e. a string that can be parsed by BigDecimal's constructor). Serializable is any serialized Java object and Binary is a raw array of bytes. String columns may optionally have a set of legal values defined for them by entering comma-separated data into the Indexed values column in the fields table.

Pressing the Save mapping button will save the mapping. The user will be alerted to any missing information in the mapping definition and prompted to rectify it before the mapping is actually saved. Note that it is possible to define multiple mappings - involving different subsets of columns - on the same HBase table. The Delete mapping button allows the current named mapping for the current named table to be deleted from the mapping table. Note that this does not delete the actual HBase table in question.

Filter Result Set Tab (Refining a Query Based on Column Values)

Within a range scan it is possible to further refine the set of rows returned by specifying filtering operations on the values of columns other than the key. The Filter result set tab allows the user to enter row filtering tests against one or more columns defined in the mapping.

NOTE: For exactly the same reasons as with the table key - two's complement is used internally which makes negative numbers bigger than positive ones when compared lexicographically according to their bytes). HBase Input ships with a custom comparator for deserializing column values before performing a comparison. This needs to be installed on each HBase node before signed comparisons will work correctly. Similarly, a special comparator for boolean values is provided that implements deserializing and interpreting boolean values from numbers and various string encodings.

When multiple column filters have been defined (as in the above screenshot), the user can opt to have returned only those rows that match all filters or any single filter. Note that bounded ranges on a single numeric column can be defined by defining two filters (upper and lower bounds) and selecting "Match all"; similarly, disjoint open-ended ranges can be defined by selecting "Match any".

Performance considerations

HBase server configuration and tuning aside, there are two things to consider when using HBase Input. The first (scanner row caching) has already been mentioned. The second involves selecting which columns from the specified mapping to return from a query. Specifying fields in the Configure query tab will result in scans that return just those columns. Since HBase is a sparse column-oriented database, this requires that HBase check to see for each row whether it contains a specific column. More lookups equate to reduced speed, although the use of Bloom filters (if enabled on the table in question) mitigates this to a certain extent. If, on the other hand, the fields table in the Configure query tab is left blank, it results in a scan that returns rows that contain all columns that exist in each row (not only those that have been defined in the mapping). However, HBase Input will only emit those columns that are defined in the mapping being used. Because all columns are returned it means that HBase does not have to do any lookups. However, if the table in question contains many columns and is dense then this will result in more data being pushed over the network.

Metadata Injection Support (7.x and later)

All fields of this step support metadata injection. You can use this step with ETL Metadata Injection to pass metadata to your transformation at runtime.