Table of Contents
This chapter describes how to use the Tibero HBase Connector.
An external table must be created to query against an HBase table from Tibero InfiniData. If the table does not exist in HBase, the external table fails to be created in HBase and an error occurs during query execution.
The following is an example of creating an HBase external table.
Creation Syntax
CREATE TABLE table_name
organization HBase (
access ('HBase_table_name')
location ('ip:port')
)
| Component | Description |
|---|---|
| table_name | Table name for Tibero. |
| HBase_table_name | HBase table name to access. |
| ip | Zookeeper IP address or host. |
| port | Zookeeper port number. |
Creation Example
The following example creates an external table called TB_HT for the existing table, HT, in HBase using the zookeeper hostname hbase01 (or IP address of 192.1.1.2) and port number 2181.
CREATE TABLE TB_HT
organization HBase (
access ('HT')
location ('hbase01:2181')
);
OR
CREATE TABLE TB_HT
organization HBase (
access ('HT')
location ('192.1.1.2:2181')
);
Queries against an HBase external table are executed with SQL as with other RDBMS tables except that columns must be specified with its column-family. A rowkey column can be used without its column-family.
columns-family$column-name
The following are the precautions for querying an HBase external table.
Must know about the column-families and columns in HBase external tables. An error occurs if a column-family is used incorrectly but NULL is returned without an error when a column is used incorrectly.
Since the column-family and column names are case sensitive in HBase, double quotes (" ") must be used to specify lower case letters.
Unlike in Tibero a row that does not have a column specified in the SQL SELECT clause is excluded from the result.
Using timestamp as a condition is currently not supported in Tibero. Queries can only be based on the most recent rows.
An asterisk (*) cannot be currently used in the SELECT clause for an HBase external table.
The following is an example of querying the HBase external table TB_HT.
TMC> SELECT rowkey, CF1$COL1, CF2$COL2 FROM TB_HT;
TMC> SELECT rowkey, CF1$COL1, CF2$COL2 FROM TB_HT WHERE CF1$COL1 > 5;
TMC> SELECT rowkey, "cf1$col1", CF2$COL2
FROM TB_HT WHERE "cf1$col1" > 5 and CF1$COL3 = 2;
As with queries, insert operations into an HBase external table is also executed with SQL except that each column must be explicitly specified along with its column-family.
Column-family$column-name
The following are the precautions for inserting data into an HBase external table.
rowkey column must always be included.
Must know about the column-families and columns in HBase external tables. An error occurs if a column-family is used incorrectly.
Since the column-family and column names are case sensitive in HBase, double quotes (" ") must be used to specify lower case letters.
If a duplicate rowkey exists in the target table, the existing data is updated.
Transactions are not supported for HBase external tables.
The following is an example of inserting into the HBase external table TB_HT.
TMC> INSERT INTO TB_HT (rowkey, CF1$COL1, CF1$COL2) values (1,2,3); TMC> INSERT INTO TB_HT (rowkey, "CF1$col3", "CF2$col4") values (2,4,1); TMC> INSERT INTO TB_HT (rowkey, CF1$COL1, CF1$COL2) SELECT level, level + 1, level + 2 FROM dual connect by level <= 1000;
As with queries, delete operations into an HBase external table is also executed with SQL.
The following are the precautions for deleting from an HBase external table.
Must be aware of column-families and columns in HBase external tables. An error occurs if a column-family is used incorrectly.
Since the column-family and column names are case sensitive in HBase, double quotes (" ") must be used to specify lower case letters.
Most recent row is only marked for deletion without actually deleting the data.
Transactions are not supported for HBase external tables.
The following is an example of deleting data from the HBase external table TB_HT.
TMC> DELETE FROM TB_HT; TMC> DELETE FROM TB_HT WHERE rowkey = 1; TMC> DELETE FROM TB_HT WHERE CF1$COL1 = 2; TMC> DELETE FROM TB_HT WHERE CF1$COL1 = 2 and "CF1$col3" = 2;