如何在Apache Phoenix表中将列数据类型从VARCHAR修改为BIGINT

支持性说明 #

Phoenix 4.8及更高版本原生支持ALTER TABLE修改列数据类型，VARCHAR转BIGINT属于合法兼容转换场景，可直接操作。

前置检查要求 #

执行修改前必须完成以下校验，避免操作失败：

• 确认该列所有现有VARCHAR值均可合法转换为BIGINT，不存在非数字格式字符串、超出BIGINT取值范围（-9223372036854775808 ~ 9223372036854775807）的数值
• 执行以下SQL查询确认无脏数据：

SELECT 待修改列名 FROM 表名 WHERE 待修改列名 NOT REGEXP '^-?[0-9]+$' OR CAST(待修改列名 AS BIGINT) IS NULL;

• 若该列有关联索引，需先删除索引，列类型修改完成后再重建索引，避免索引与主表类型不兼容

标准修改操作 #

直接执行ALTER TABLE语句即可完成类型修改，语法如下：

ALTER TABLE 你的表名 ALTER COLUMN 待修改列名 BIGINT;

注意：Phoenix默认表名、列名均为大写，若建表时通过双引号指定了小写名称，需填写对应实际名称
修改完成后可执行以下语句验证类型是否生效：

SELECT COLUMN_NAME, DATA_TYPE FROM SYSTEM.CATALOG WHERE TABLE_NAME = '你的表名' AND COLUMN_NAME = '待修改列名';

脏数据场景替代方案 #

如果存在无法转换的脏数据，建议采用新增列迁移数据的稳妥方案：

1. 新增BIGINT类型的目标列

ALTER TABLE 你的表名 ADD 新列名 BIGINT;

2. 批量将原列合法值转换后写入新列

UPSERT INTO 你的表名 (主键列1, 主键列2, 新列名) 
SELECT 主键列1, 主键列2, CAST(待修改列名 AS BIGINT) 
FROM 你的表名 
WHERE 待修改列名 REGEXP '^-?[0-9]+$';

3. 删除原有VARCHAR类型列

ALTER TABLE 你的表名 DROP COLUMN 待修改列名;

4. 将新列重命名为原有列名

ALTER TABLE 你的表名 RENAME COLUMN 新列名 TO 待修改列名;

内容的提问来源于stack exchange，提问作者pawinder gupta