13.20. Phoenix Connector

The Phoenix connector allows querying data stored in HBase using Apache Phoenix.

Compatibility

The Phoenix connector is compatible with all Phoenix versions starting from 4.14.1.

Configuration

To configure the Phoenix connector, create a catalog properties file etc/catalog/phoenix.properties with the following contents, replacing host1,host2,host3 with a comma-separated list of the ZooKeeper nodes used for discovery of the HBase cluster:

connector.name=phoenix
phoenix.connection-url=jdbc:phoenix:host1,host2,host3:2181:/hbase
phoenix.config.resources=/path/to/hbase-site.xml

The optional paths to Hadoop resource files such as hbase-site.xml are used to load custom Phoenix client connection properties.

Configuration Properties

The following configuration properties are available:

Property Name

Required

Description

phoenix.connection-url

Yes

jdbc:phoenix[:zk_quorum][:zk_port][:zk_hbase_path]. The zk_quorum is a comma separated list of ZooKeeper servers. The zk_port is the ZooKeeper port. The zk_hbase_path is the HBase root znode path, that is configurable using hbase-site.xml. By default the location is /hbase

phoenix.config.resources

No

Comma-separated list of configuration files (e.g. hbase-site.xml) to use for connection properties. These files must exist on the machines running Presto.

Querying Phoenix Tables

The default empty schema in Phoenix maps to a schema named default in Presto. You can see the available Phoenix schemas by running SHOW SCHEMAS:

SHOW SCHEMAS FROM phoenix;

If you have a Phoenix schema named web, you can view the tables in this schema by running SHOW TABLES:

SHOW TABLES FROM phoenix.web;

You can see a list of the columns in the clicks table in the web schema using either of the following:

DESCRIBE phoenix.web.clicks;
SHOW COLUMNS FROM phoenix.web.clicks;

Finally, you can access the clicks table in the web schema:

SELECT * FROM phoenix.web.clicks;

If you used a different name for your catalog properties file, use that catalog name instead of phoenix in the above examples.

Data types

The data type mappings are as follows:

Phoenix

Presto

BOOLEAN

(same)

BIGINT

(same)

INTEGER

(same)

SMALLINT

(same)

TINYINT

(same)

DOUBLE

(same)

FLOAT

REAL

DECIMAL

(same)

BINARY

VARBINARY

VARBINARY

(same)

DATE

(same)

TIME

(same)

VARCHAR

(same)

CHAR

(same)

The Phoenix fixed length BINARY data type is mapped to the Presto variable length VARBINARY data type. There is no way to create a Phoenix table in Presto that uses the BINARY data type, as Presto does not have an equivalent type.

Table Properties - Phoenix

Table property usage example:

CREATE TABLE myschema.scientists (
  recordkey VARCHAR,
  birthday DATE,
  name VARCHAR,
  age BIGINT
)
WITH (
  rowkeys = 'recordkey,birthday',
  salt_buckets = 10
);

The following are supported Phoenix table properties from https://phoenix.apache.org/language/index.html#options

Property Name

Default Value

Description

rowkeys

ROWKEY

Comma-separated list of primary key columns. See further description below

split_on

(none)

List of keys to presplit the table on. See Split Point.

salt_buckets

(none)

Number of salt buckets for this table.

disable_wal

false

Whether to disable WAL writes in HBase for this table.

immutable_rows

false

Declares whether this table has rows which are write-once, append-only.

default_column_family

0

Default column family name to use for this table.

rowkeys

This is a comma-separated list of columns to be used as the table’s primary key. If not specified, a BIGINT primary key column named ROWKEY is generated , as well as a sequence with the same name as the table suffixed with _seq (i.e. <schema>.<table>_seq) , which is used to automatically populate the ROWKEY for each row during insertion.

Table Properties - HBase

The following are the supported HBase table properties that are passed through by Phoenix during table creation. Use them in the the same way as above: in the WITH clause of the CREATE TABLE statement.

Property Name

Default Value

Description

versions

1

The maximum number of versions of each cell to keep.

min_versions

0

The minimum number of cell versions to keep.

compression

NONE

Compression algorithm to use. Valid values are NONE (default), SNAPPY, LZO, LZ4, or GZ.

ttl

FOREVER

Time To Live for each cell.

bloomfilter

ROW

Bloomfilter to use. Valid values are NONE, ROW (default), or ROWCOL.