Using Apache Phoenix to store and access data
Also available as:

Apache Phoenix-Hive usage examples

You can view the examples of creating a table, loading data, and querying data.

Creating a table

In HDP 3.0, all the Hive tables that are backed by a StorageHandler must use the EXTERNAL keyword.

The following example creates a corresponding table in Phoenix when the Hive table is created. Similarly, the Phoenix table is also dropped when the Hive table is dropped. If the Phoenix table already exists when the Hive table is created, Hive does not delete the Phoenix table when dropping the Hive table.

create external table ext_table (
  i1 int,
  s1 string,
  f1 float,
  d1 decimal
STORED BY 'org.apache.phoenix.hive.PhoenixStorageHandler'
  "" = "ext_table",
  "phoenix.zookeeper.quorum" = "localhost",
  "phoenix.zookeeper.znode.parent" = "/hbase",
  "phoenix.zookeeper.client.port" = "2181",
  "phoenix.rowkeys" = "i1",
  "phoenix.column.mapping" = "i1:i1, s1:s1, f1:f1, d1:d1"

Load data

Use insert statement to load data to the Phoenix table through Hive.

insert into table T values (....);
insert into table T select c1,c2,c3 from source_table;

Query data

You can use HiveQL for querying data in a Phoenix table. A Hive query on a single table can be as fast as running the query in the Phoenix CLI with the following property settings:
hive.fetch.task.conversion=more and hive.exec.parallel=true

Following are some of the parameters that you could use when querying the data.

Parameter Default Value Description
hbase.scan.cache 100 Read row size for a unit request
hbase.scan.cacheblock false Whether or not cache block false If true, mappers use table statistics. One mapper per guide post.
[hive-table-name].reducer.count 1 Number of reducers. In Tez mode, this affects only single-table queries. See Limitations.
[phoenix-table-name].query.hint Hint for Phoenix query (for example, NO_INDEX)