Apache Phoenix is an SQL middle layer built on top of HBase. Phoenix can be used to execute SQL statements to query HBase data.

Background information

An E-MapReduce (EMR) cluster is created, and Phoenix is selected from the optional services during cluster creation.

For more information about how to create an EMR cluster, see Create a cluster.

Use Phoenix

  1. Connect to the master node of your cluster in SSH mode. For more information, see Connect to the master node of an EMR cluster in SSH mode.
  2. Run the following command to switch to the hadoop user:
    su hadoop
  3. Run the following command to go to the bin directory:
    cd /usr/lib/phoenix-current/bin
  4. Run the following command to connect to the command-line interface (CLI) of Phoenix:
    sqlline.py
    Information in the following figure is returned.Phoenix
  5. You can execute SQL statements to query data. The following common operations are supported:
    • Create a table.
      CREATE TABLE example(
          my_pk bigint not null,
          m.first_name varchar(50),
          m.last_name varchar(50) 
          CONSTRAINT pk PRIMARY KEY (my_pk));
    • Insert data into a table.
      UPSERT INTO example(my_pk,m.first_name,m.last_name) VALUES(100,'Jack','Ben');
      UPSERT INTO example(my_pk,m.first_name,m.last_name) VALUES(200,'Jack3','Ben3');
    • Query data.
      SELECT * FROM example;
      The following information is returned:
      +--------+-------------+------------+
      | MY_PK  | FIRST_NAME  | LAST_NAME  |
      +--------+-------------+------------+
      | 100    | Jack        | Ben        |
      | 200    | Jack3       | Ben3       |
      +--------+-------------+------------+
    • Drop a table.
      DROP TABLE example;