6.19. Phoenix Connector

The Phoenix connector allows querying data stored in Apache 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`.

Presto 329 Documentation