Phoenix Shell操作

1. 显示所有表

!table 或 !tables

2. 创建表

主键的列会被作为RowKey，所有建表必须都是主键表：

0: jdbc:phoenix:> create table if not exists student(
. . . . . . . .)> id varchar primary key,
. . . . . . . .)> name varchar,
. . . . . . . .)> age bigint,
. . . . . . . .)> addr varchar);
No rows affected (1.222 seconds)
0: jdbc:phoenix:> !table
+-----------+-------------+------------+--------------+---------+-----------+---------------------------+----------------+-------------+-----------------+
| TABLE_CAT | TABLE_SCHEM | TABLE_NAME |  TABLE_TYPE  | REMARKS | TYPE_NAME | SELF_REFERENCING_COL_NAME | REF_GENERATION | INDEX_STATE | IMMUTABLE_ROWS  |
+-----------+-------------+------------+--------------+---------+-----------+---------------------------+----------------+-------------+-----------------+
|           | SYSTEM      | CATALOG    | SYSTEM TABLE |         |           |                           |                |             | false           |
|           | SYSTEM      | CHILD_LINK | SYSTEM TABLE |         |           |                           |                |             | false           |
|           | SYSTEM      | FUNCTION   | SYSTEM TABLE |         |           |                           |                |             | false           |
|           | SYSTEM      | LOG        | SYSTEM TABLE |         |           |                           |                |             | true            |
|           | SYSTEM      | MUTEX      | SYSTEM TABLE |         |           |                           |                |             | true            |
|           | SYSTEM      | SEQUENCE   | SYSTEM TABLE |         |           |                           |                |             | false           |
|           | SYSTEM      | STATS      | SYSTEM TABLE |         |           |                           |                |             | false           |
|           | SYSTEM      | TASK       | SYSTEM TABLE |         |           |                           |                |             | false           |
|           | SYSTEM      | TRANSFORM  | SYSTEM TABLE |         |           |                           |                |             | false           |
|           |             | STUDENT    | TABLE        |         |           |                           |                |             | false           |
+-----------+-------------+------------+--------------+---------+-----------+---------------------------+----------------+-------------+-----------------+

在phoenix中，表名等会自动转换为大写，若要小写，使用双引号，如"us_population"。
指定多个列的联合作为RowKey:

0: jdbc:phoenix:> create table if not exists student1(
. . . . . . . .)> id varchar not null,
. . . . . . . .)> name varchar not null,
. . . . . . . .)> age bigint,
. . . . . . . .)> addr varchar,
. . . . . . . .)> constraint my_pk primary key (id, name));
No rows affected (1.181 seconds)

在Phoenix中建表，会在HBase中创建一张对应的表。为了减少数据对磁盘空间的占用，Phoenix默认会对HBase中的列名做编码处理。
若不想对列名编码，可在建表语句末尾加上COLUMN_ENCODED_BYTES = 0;

3. 插入数据

Phoenix不支持使用insert语法，插入和更新使用upsert:

upsert into student values('1001','zhangsan', 10, 'beijing');
upsert into student values('1001','lisi', 20, 'sichuan');

查询结果：

4. 查询记录

select * from student;
select * from student where id='1001';

查询id='1000'的数据；

0: jdbc:phoenix:> select * from student where id='1001';
+------+------+-----+---------+
|  ID  | NAME | AGE |  ADDR   |
+------+------+-----+---------+
| 1001 | lisi | 20  | sichuan |
+------+------+-----+---------+
1 row selected (0.016 seconds)

5. 删除记录

0: jdbc:phoenix:> delete from student where id='1001';
1 row affected (0.02 seconds)

6. 删除表

drop table student;