原创

phoenix5表结构变更,修改字段类型

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://it007.blog.csdn.net/article/details/101543906

概述

众所周知,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)

参考

Phoenix的Data Types(官方文档)

文章最后发布于: 2019-09-27 17:18:14
展开阅读全文
0 个人打赏
私信求帮助

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览