SYSCS_UTIL.GET_REGIONS

The SYSCS_UTIL.GET_REGIONS system procedure that retrieves the list of regions containing a range of key values.

Syntax

SYSCS_UTIL.GET_REGIONS( VARCHAR schemaName,
                        VARCHAR tableName,
                        VARCHAR indexName,
                        VARCHAR startKey,
                        VARCHAR endKey,
                        VARCHAR columnDelimiter,
                        VARCHAR characterDelimiter,
                        VARCHAR timestampFormat,
                        VARCHAR dateFormat,
                        VARCHAR timeFormat )

schemaName

The name of the schema of the table.

tableName

The name of the table.

indexName

NULL or the name of the index.

Specify NULL to indicate that the startKey is the primary key of the base table; specify an index name to indicate that the startKey is an index value.

startKey

For a table, this is a comma-separated-value (CSV) representation of the primary key value for the start of the regions in which you are interested. For an index, this is the CSV representation of the index columns.

endKey

For a table, this is a comma-separated-value (CSV) representation of the primary key value for the end of the regions in which you are interested. For an index, this is the CSV representation of the index columns.

columnDelimiter

The character used to separate columns in startKey. Specify null if using the comma (,) character as your delimiter.

characterDelimiter

Specifies which character is used to delimit strings in startKey. You can specify null or the empty string to use the default string delimiter, which is the double-quote (").

If your input contains control characters such as newline characters, make sure that those characters are embedded within delimited strings.

To use the single quote (') character as your string delimiter, you need to escape that character. This means that you specify four quotes ('''') as the value of this parameter. This is standard SQL syntax.

The Examples section below contains an example that uses the single quote as the string delimiter character.

timestampFormat

The format of timestamps in startKey. You can set this to null if there are no time columns in the split key, or if the format of any timestamps in the file match the Java.sql.Timestamp default format, which is: “yyyy-MM-dd HH:mm:ss”.

See the About Timestamp Formats section in the SYSCS_UTIL.IMPORT_DATA topic for more information about timestamps.

dateFormat

The format of datestamps in startKey. You can set this to null if there are no date columns in the startKey, or if the format of any dates in the split key match this pattern: “yyyy-MM-dd”.

timeFormat

The format of time values stored in startKey. You can set this to null if there are no time columns in the file, or if the format of any times in the split key match this pattern: “HH:mm:ss”.

Usage

Specify the starting and ending key values, this procedure returns information about all regions that span those key values.

Results

The displayed results of calling SYSCS_UTIL.SYSCS_GET_REGIONS include these values:

Value Description
ENCODED_REGION_NAME The HBase-encoded name of the region.
SPLICE_START_KEY The unencoded start key, in CSV format, for the list of regions in which you are interested. This is the value you supplied in the startKey parameter. For example: {1,2}.
SPLICE_END_KEY The unencoded end key for the region, in CSV format, for the list of regions in which you are interested. This is the value you supplied in the endKey parameter. For example: {1,6}.
HBASE_START_KEY The start key for the region, formatted as shown in the HBase Web UI. For example: \x81\x00\x82.
HBASE_END_KEY The end key for the region, formatted as shown in the HBase Web UI. For example: \x81\x00\x86.
NUM_HFILES The number of HBase Files contained in the region.
SIZE The size, in bytes, of the region.
LAST_MODIFICATION_TIME The most recent time at which the region was modified.
REGION_NAME The unencoded name of the region.

Example

The following call returns information about the regions that are in the key range {1,2} to {1,8}:

splice> CALL SYSCS_UTIL.GET_REGIONS( 'SPLICE','TestTable', null,
                                     '1|2', '1|8', '|',null,null,null,null);
ENCODED_REGION_NAME              |SPLICE_START_KEY |SPLICE_END_KEY |HBASE_START_KEY |HBASE_END_KEY |NUM_HFILES |SIZE  |LAST_MODIFICATION_TIME |REGION_NAME
-----------------------------------------------------------------
132c824b9e269006a8e0a3fad577bd12 |{ 1, 2}          |{ 1, 6}        |\x81\x00\x82    |\x81\x00\x86  |1          |1645  |2017-08-17 12:44:15.0  |splice:2944,\x81\x00\x82,1502999053574.132c824b9e269006a8e0a3fad577bd12.
2ee995a552cbb75b7172eed27b917cab |{ 1, 6 }         |{ 1, 8 }       |\x81\x00\x86    |\x81\x00\x88  |1          |1192  |2017-08-17 08:37:56.0  |splice:2944,\x81\x00\x86,1502984266749.2ee995a552cbb75b7172eed27b917cab.

2 rows selected

See Also