Starburst HBase connector#

The Starburst HBase connector allows querying an external Apache HBase database.

Note

The HBase connector is a private preview. Contact Starburst support with questions or feedback.

Requirements#

To connect to HBase, you need:

  • Apache HBase version 2.3.x

  • Network access from the coordinator and workers to the HBase and Zookeeper servers. The default port is 2181.

  • A valid Starburst Enterprise license.

Configuration#

This section describes how to configure the HBase connector and create a catalog properties file that specifies both the HBase connector and the metadata store type.

To configure the HBase connector, create a catalog properties file that specifies the HBase connector by setting the connector.name to hbase.

For example, to access a database as the example catalog, create the file etc/catalog/example.properties. Replace the connection properties as appropriate for your setup:

connector.name=hbase
hbase.zookeeper.quorum=host1,host2
hbase.zookeeper.port=2181
hbase.zookeeper.znode=/hbase

General configuration properties#

The following configuration properties can be set in the catalog properties file:

HBase connector general configuration properties#

Property name

Description

Default value

hbase.zookeeper.quorum

Comma-separated list of ZooKeeper server hostnames.

hbase.zookeeper.port

Server port for the ZooKeeper service.

2181

hbase.zookeeper.znode

The root znode containing all znodes created and/or used by HBase.

hbase.config.resources

Comma-separated paths to HBase configuration files for HBase clients. In the event of conflicting configuration options, the SEP connector catalog configuration overrides these files.

hbase.authentication.type

HBase authentication mechanism type, either UNSECURED or KERBEROS.

UNSECURED

hbase.impersonation.enabled

When set to true, enables session user impersonation.

false

hbase.projection-pushdown.enabled

When set to true, enables projection pushdown. See projection pushdown.

false

hbase.allow-create

When set to true, enables CREATE TABLE, CREATE TABLE AS, and CREATE SCHEMA SQL statements. See CREATE, DROP, and INSERT support.

false

hbase.allow-drop

When set to true, enables DROP TABLE and DROP SCHEMA SQL statements. See CREATE, DROP, and INSERT support.

false

hbase.allow-insert

When set to true, enables INSERT SQL statements. See CREATE, DROP, and INSERT support.

false

hbase.connection-pool.enabled

Enable connection pooling. When set to true, the connector attempts to re-use connections to HBase instead of establishing a new connection.

true

hbase.connection-pool.ttl

If connection pooling is enabled, determines how long in seconds to keep a connection alive when not in active use.

30

Table metadata storage options#

The HBase connector supports two types of table stores for metadata options:

  • HBase native table store (default)

  • File-based table store (legacy)

Specify the desired table store using the hbase.table-store.type property.

HBase table store#

The HBase table store is now the default option for metadata storage in HBase.

The following example specifies the HBase table store:

hbase.table-store.type=hbase

File-based table store#

The file-based table store is the legacy option and requires .json file configuration. See table descriptions.

The following example specifies the file-based table store:

hbase.table-store.type=file

Table store configuration properties#

The following table store-related properties can be set in the catalog properties file:

HBase connector table configuration properties#

Property name

Description

Default value

hbase.table-description-dir

Directory path that holds JSON description files for file-based HBase tables. See table descriptions for more information.

hbase.table-store.type

The store type for metadata, either file or hbase. See metadata storage options.

hbase

hbase.table-store.hbase.user

Specifies the user of the Hbase table store.

hbase.table-store.schema-name

Specifies the schema where the HBase table store is located.

hbase.table-store.table-name

Specifies the table for the HBase table store.

hbase.table-store.hbase.migrate-from-file-based-table-store

When set to true, enables the stored procedure to migrate from the file-based table store to the HBase table store. This property requires the hbase.table-description-dir property.

false

Migrating from file-based to HBase table store#

To migrate metadata from the file-based table store to the HBase native table store, enable the migration procedure using the following property:

hbase.table-store.hbase.migrate-from-file-based-table-store.enabled=true

Then execute the migration procedure:

CALL system.migrate_from_file_table_store(
  SCHEMA_NAME => 'my_schema',
  TABLE_NAME => 'my_table',
  OVERRIDE_IF_EXISTS => true,
  REMOVE_ORIGINAL_AFTER_MIGRATION => true);

After the migration is complete, metadata is stored in the HBase table store and is discoverable without the need for JSON files.

Create new metadata#

To create new metadata mapped to an existing table, use the CREATE TABLE statement with the external_table configuration property:

The following example creates new table metadata using the existing table and specifies columns using column options:

CREATE TABLE hbase.default.table_name (
    ID BIGINT WITH (remote_name = 'ID'),
    ID2 VARCHAR(4) WITH (remote_name = 'ID2'),
    ID3 BIGINT WITH (remote_name = 'ID3'),
    VALUE1 BIGINT WITH (family = 'CF', remote_name = 'VALUE1')
)
WITH (
    external_table = true,
    key_column_names = ARRAY['ID', 'ID2', 'ID3']
)

Table descriptions#

Table descriptions are required to query HBase tables from SEP. A table description directory on all SEP nodes contains table description .json files that describe HBase table names, columns, and data types. The connector reads these files at runtime to create a 1:1 mapping between SEP and HBase tables. Any HBase tables without a corresponding table description file in this directory cannot be queried from SEP.

The table description directory must contain a subdirectory for each HBase namespace or SEP schema name, which then contain the corresponding table description files. The following shows an example directory and file structure:

table-description-dir/
  namespace1/
    table1.json
    table2.json
  namespace2/
    table1.json
    table2.json

Warning

Schema, table, and column names are case-insensitive. However, we recommend that you use lowercase identifiers in table description .json files and file names. If the HBase database has multiple tables with the same name in different cases, they cannot be mapped to SEP tables and therefore cause query failures.

Table description files use the following format:

HBase table description file format#

Property name

Description

Required

columns

List of the table’s columns along with their types and column families.

Yes

keyColumnNames

List of column names to be used as the HBase row key. This property is required but can be set to an empty list object, or [ ]. In this case a random UUID is used to automatically populate the HBase row key for each row during insertion. Does not support multiple columns with variable-length data types in case the key is composite. Instead, use multiple fixed-length columns or a single variable-length column.

Yes

saltBuckets

Number of salt buckets for this table, between 1 to 256.

No

The following block shows an example JSON table description file:

{
  "columns" : [ {
    "name" : "varchar_column",
    "type" : "varchar",
    "family" : "column_family",
    "comment" : "comment"
  }, {
    "name" : "int_column",
    "type" : "integer",
    "family" : "column_familia",
    "comment" : "comment"
  } ],
  "keyColumnNames" : [ "int_column" ],
  "saltBuckets" : 5,
  "comment" : "table_comment"
}

Create a namespace#

In SEP, HBase namespaces are represented by schemas. To create a namespace in HBase, use a CREATE SCHEMA SQL statement in SEP.

CREATE SCHEMA test_namespace;

Note

CREATE SCHEMA statements are disabled by default, but can be enabled with the hbase.allow-create configuration property. See CREATE, DROP, and INSERT support for more information.

Create a table#

In SEP, running a CREATE TABLE statement creates a table in HBase using an existing table’s schema as the new table’s namespace.

All columns within these created tables are part of the default cf column family. Column families cannot be modified in an SEP session, but can be changed in the table’s description .json file.

Note

CREATE TABLE statements are disabled by default, but can be enabled with the hbase.allow-create configuration property. See CREATE, DROP, and INSERT support for more information.

CREATE TABLE statements support the same properties as the table descriptions.

CREATE TABLE hbase.default.test (
  id1 BIGINT,
  id2 BIGINT,
  value VARCHAR
)
WITH (
  key_column_names = ARRAY['id1', 'id2'],
  salt_buckets = 10
);

Type mapping#

The connector supports the following SQL types:

  • BOOLEAN

  • TINYINT

  • SMALLINT

  • BIGINT

  • REAL

  • DOUBLE

  • VARCHAR

  • VARCHAR(N)

  • CHAR(N)

  • VARBINARY

  • DATE

  • TIMESTAMP

  • TIMESTAMP(N)

All other SQL types are not supported.

SQL support#

The connector provides read and write access to data and metadata in HBase. In addition to the globally available and read operation statements, the connector supports the following features:

CREATE, DROP, and INSERT support#

The following features are supported by the connector but disabled by default:

These operations are non-transactional and can therefore result in data loss if interrupted. To enable these SQL statements, set the corresponding configuration in the catalog properties file:

//Enable CREATE TABLE, CREATE TABLE AS, and CREATE SCHEMA
hbase.allow-create=true

//Enable DROP TABLE and DROP SCHEMA
hbase.allow-drop=true

//Enable INSERT
hbase.allow-insert=true

INSERT#

INSERT operations with the same values for key columns overwrite rows in HBase.

ALTER TABLE#

The connector does not support ALTER TABLE RENAME or ALTER TABLE SET AUTHORIZATION.

Performance#

The connector includes a number of performance improvements, detailed in the following sections.

Parallelism#

By default, the connector attempts to create multiple splits off available table regions for reading data. Each split is mapped to a single region and processed in parallel. Therefore, it is best practice to split HBase tables over multiple regions for better performance.

Table salting#

Sequential write operations can perform poorly across region servers when the row keys increase monotonically. SEP supports salting tables as a way to mitigate this problem.

If a table is not created through SEP and doesn’t follow the one byte salt key prefix, the connector won’t be able to distinguish the salt prefix from the actual key. In this case, resultant data includes the salt prefix as part of the row key column.

Projection pushdown#

The connector can improve performance of table scans by performing pushdown of column projections to HBase. This reduces the amount of data transferred between SEP and the region servers.

If projection pushdown is enabled, rows without any of the projected columns are skipped. This is because HBase is a non-relational database with an arbitrary number of columns per row, so it can only return rows where at least one of the projected columns exist.

Projection pushdown is disabled by default, but can be enabled by setting the hbase.projection-pushdown.enabled property in the catalog properties file to true:

hbase.projection-pushdown.enabled=true

Security#

The connector includes a number of security-related features, detailed in the following sections.

User impersonation#

The connector supports user impersonation. Enable user impersonation by setting the hbase.impersonation.enabled property in the catalog properties file to true:

hbase.impersonation.enabled=true

Kerberos authentication#

The connector supports Kerberos authentication. Use the following properties in the catalog properties file to configure it.

hbase.authentication.type=KERBEROS
hbase.master.kerberos.principal=hbase/_HOST@example.com
hbase.regionserver.kerberos.principal=hbase/_HOST@example.com
hbase.client.kerberos.principal=example@example.com
hbase.client.kerberos.keytab=etc/kerberos/example.keytab

With this configuration the user example@example.com is used to connect to the database, and the related Kerberos service ticket is stored in the example.keytab file.