概述
众所周知，hbase中存储的数据都是二进制的字节数组，是没有数据类型的 ，所以这里的数据类型也就是说的Phoenix中的数据类型。
在Phoenix中，有以下几张系统表，其中SYSTEM.CATALOG表保存了表的元数据信息
SYSTEM:CATALOG
SYSTEM:SEQUENCE
SYSTEM:FUNCTION
SYSTEM:LOG
SYSTEM:MUTEX
SYSTEM:STATS
本文内容慎用，因为会造成乱码，数据转换有误！！！
添加字段
alter table 表名 add 列名 列类型;
删除字段
alter table 表名 drop column 列名
修改字段
在mysql中可以使用类似语句进行字段的修改：alter table tb_table_info_copy MODIFY age VARCHAR(10);
但是在Phoenix中是不支持的。
Error: ERROR 601 (42P00): Syntax error. Encountered "modify" at line 1, column 20. (state=42P00,code=601)
既然如此，那么我们直接去SYSTEM:CATALOG修改元数据信息。
假设新增有一张person表，其表结构如下：
0: jdbc:phoenix:localhost:2181> select * from person;
+-----+------+-------+-------+-----------+
| ID | UID | NAME | AGE | ADDRESS |
+-----+------+-------+-------+-----------+
| 1 | 1 | 1 | 10 | beijing |
| 2 | 2 | 2 | 20 | shanghai |
| 4 | 4 | null | null | |
+-----+------+-------+-------+-----------+
3 rows selected (0.094 seconds)
0: jdbc:phoenix:localhost:2181> select TENANT_ID,TABLE_SCHEM,TABLE_NAME,COLUMN_NAME,COLUMN_FAMILY,DATA_TYPE,TYPE_NAME,COLUMN_SIZE,DECIMAL_DIGITS from SYSTEM.CATALOG where TABLE_NAME='PERSON';
+------------+--------------+-------------+--------------+----------------+------------+------------+--------------+-----------------+
| TENANT_ID | TABLE_SCHEM | TABLE_NAME | COLUMN_NAME | COLUMN_FAMILY | DATA_TYPE | TYPE_NAME | COLUMN_SIZE | DECIMAL_DIGITS |
+------------+--------------+-------------+--------------+----------------+------------+------------+--------------+-----------------+
| | | PERSON | | | null | | null | null |
| | | PERSON | | 0 | null | | null | null |
| | | PERSON | ADDRESS | 0 | 12 | | null | null |
| | | PERSON | AGE | 0 | 4 | | null | null |
| | | PERSON | ID | | 4 | | null | null |
| | | PERSON | NAME | 0 | -5 | | null | null |
| | | PERSON | UID | 0 | -5 | | null | null |
+------------+--------------+-------------+--------------+----------------+------------+------------+--------------+-----------------+
7 rows selected (0.112 seconds)
0: jdbc:phoenix:localhost:2181> !describe person
+------------+--------------+-------------+--------------+------------+------------+--------------+----------------+-----------------+-----------------+-----------+----------+-------------+----------------+-------------------+--------------------+-------------------+--+
| TABLE_CAT | TABLE_SCHEM | TABLE_NAME | COLUMN_NAME | DATA_TYPE | TYPE_NAME | COLUMN_SIZE | BUFFER_LENGTH | DECIMAL_DIGITS | NUM_PREC_RADIX | NULLABLE | REMARKS | COLUMN_DEF | SQL_DATA_TYPE | SQL_DATETIME_SUB | CHAR_OCTET_LENGTH | ORDINAL_POSITION | |
+------------+--------------+-------------+--------------+------------+------------+--------------+----------------+-----------------+-----------------+-----------+----------+-------------+----------------+-------------------+--------------------+-------------------+--+
| | | PERSON | ID | 4 | INTEGER | null | null | null | null | 0 | | | null | null | null | 1 | |
| | | PERSON | UID | -5 | BIGINT | null | null | null | null | 1 | | | null | null | null | 2 | |
| | | PERSON | NAME | -5 | BIGINT | null | null | null | null | 1 | | | null | null | null | 3 | |
| | | PERSON | AGE | 4 | INTEGER | null | null | null | null | 1 | | | null | null | null | 4 | |
| | | PERSON | ADDRESS | 12 | VARCHAR | null | null | null | null | 1 | | | null | null | null | 5 | |
+------------+--------------+-------------+--------------+------------+------------+--------------+----------------+-----------------+-----------------+-----------+----------+-------------+----------------+-------------------+--------------------+-------------------+--+
0: jdbc:phoenix:localhost:2181>
SYSTEM.CATALOG中字段含义：
| 列 | 说明 |
|---|---|
| TENANT_ID | 租户ID(这个不用管,所租户用的) |
| TABLE_SCHEM | 表的schema |
| TABLE_NAME | 表名 |
| COLUMN_NAME | 列名 |
| COLUMN_FAMIL | hbase底层的列族名 |
| DATA_TYPE | 列的数据类型 |
| COLUMN_SIZE | 列的数据长度(一般指char,varchar和decimal的长度) |
| DECIMAL_DIGITS | decimal类型的小数长度 |
我们把UID改为integer(这里只是测试使用，实际情况要看数据实际大小精度是否可以转换)
upsert into SYSTEM.CATALOG (TENANT_ID,TABLE_SCHEM,TABLE_NAME,COLUMN_NAME,COLUMN_FAMILY,DATA_TYPE) values('','','PERSON','UID','0',4);
upsert into SYSTEM.CATALOG (TENANT_ID,TABLE_SCHEM,TABLE_NAME,COLUMN_NAME,COLUMN_FAMILY,DATA_TYPE) values('','','PERSON','AGE','0',12);
再次查询数据
0: jdbc:phoenix:localhost:2181> select TENANT_ID,TABLE_SCHEM,TABLE_NAME,COLUMN_NAME,COLUMN_FAMILY,DATA_TYPE,TYPE_NAME,COLUMN_SIZE,DECIMAL_DIGITS from SYSTEM.CATALOG where TABLE_NAME='PERSON';
+------------+--------------+-------------+--------------+----------------+------------+------------+--------------+-----------------+
| TENANT_ID | TABLE_SCHEM | TABLE_NAME | COLUMN_NAME | COLUMN_FAMILY | DATA_TYPE | TYPE_NAME | COLUMN_SIZE | DECIMAL_DIGITS |
+------------+--------------+-------------+--------------+----------------+------------+------------+--------------+-----------------+
| | | PERSON | | | null | | null | null |
| | | PERSON | | 0 | null | | null | null |
| | | PERSON | ADDRESS | 0 | 12 | | null | null |
| | | PERSON | AGE | 0 | 12 | | null | null |
| | | PERSON | ID | | 4 | | null | null |
| | | PERSON | NAME | 0 | -5 | | null | null |
| | | PERSON | UID | 0 | 4 | | null | null |
+------------+--------------+-------------+--------------+----------------+------------+------------+--------------+-----------------+
7 rows selected (0.167 seconds)
0: jdbc:phoenix:localhost:2181> select * from person;
+-----+------+-------+-------+-----------+
| ID | UID | NAME | AGE | ADDRESS |
+-----+------+-------+-------+-----------+
| 1 | 1 | 1 | 10 | beijing |
| 2 | 2 | 2 | 20 | shanghai |
| 4 | 4 | null | null | |
+-----+------+-------+-------+-----------+
3 rows selected (0.087 seconds)
0: jdbc:phoenix:localhost:2181> upsert into person(id, name, age,address) values(1,1,11,'hangzhou');
1 row affected (0.022 seconds)
0: jdbc:phoenix:localhost:2181> select * from person;
+-----+------+-------+-------+-----------+
| ID | UID | NAME | AGE | ADDRESS |
+-----+------+-------+-------+-----------+
| 1 | 1 | 1 | 11 | hangzhou |
| 2 | 2 | 2 | 20 | shanghai |
| 4 | 4 | null | null | |
+-----+------+-------+-------+-----------+
3 rows selected (0.125 seconds)
数据类型和名称对应关系
元数据修改后不生效的解决办法
实际操作过程中，元数据修改后，SYSTEM.CATALOG中已经修改，！describe表名也是最新的，但是心插入数据后，实际Phoenix是没有剩下的，需要Phoenix重新加载元数据后者重启hbase。
以上操作慎用
以上操作，只适用于修改char, varchar, DECIMAL类型的长度等等，不可以直接修改integer到bigint，也就是说上面的实例是错的！
如果修改了以后，Phoenix中查询将出现乱码。数据转换有误。
0: jdbc:phoenix:localhost:2181> select * from person;
+-----+------+-----------------------+------+----------+
| ID | UID | NAME | AGE | ADDRESS |
+-----+------+-----------------------+------+----------+
| 1 | 0 | -9223372030412324864 | | |
| 2 | 0 | -9223372026117357568 | | |
| 4 | 0 | null | | |
+-----+------+-----------------------+------+----------+
3 rows selected (0.271 seconds)
参考
转载：https://blog.csdn.net/fgyibupi/article/details/101543906
查看评论