MariaDB Columnstore 数仓OLAP使用注意事项

原创

贺春旸的技术博客

修改于 2023-12-21 11:42:53

1790

修改于 2023-12-21 11:42:53

文章被收录于专栏：DBA 平台和工具DBA 平台和工具

MariaDB Columnstore引擎使用注意事项

一、字段属性限制

1、varchar最大8000
2、不支持bit类型
3、不支持Reserved keywords保留关键字user、comment、match、key、update、status作为表名、字段名或用户定义的变量、函数或存储过程的名称。
4、不支持zerofill
5、不支持enum枚举类型
6、comment不能携带''引号
create table t1(id int comment '主键''ID')engine=Columnstore;
7、不支持主键自增

二、SQL语句限制

1、查询的字段不在group by里，就不能分组统计

错误写法：

MariaDB [test]> select id from t1 group by name;  
ERROR 1815 (HY000): Internal error: MCS-2021: '`test`.`t1`.`id`' is not in GROUP BY clause. All non-aggregate columns in the SELECT and ORDER BY clause must be included in the GROUP BY clause.

MariaDB [test]> select id,name from t1 group by name;  
ERROR 1815 (HY000): Internal error: MCS-2021: '`test`.`t1`.`id`' is not in GROUP BY clause. All non-aggregate columns in the SELECT and ORDER BY clause must be included in the GROUP BY clause.

正确写法：

MariaDB [test]> select name from t1 group by name;

2、alter不支持多列操作和不支持after

MariaDB [test]> alter table t1 add age tinyint,add address varchar(100);
ERROR 1178 (42000): The storage engine for the table doesn't support Multiple actions in alter table statement is currently not supported by Columnstore.

3、字段类型不同 join 关联查询报错，比如表1的id字段为int，表2的字段id为varchar，进行关联查询join就会报错

MariaDB [test]> select t1.id from t1 join t2 on t1.id=t2.cid;
ERROR 1815 (HY000): Internal error: IDB-1002: 't1' and 't2' have incompatible column type specified for join condition.

4、alter不支持change/modify更改字段属性

MariaDB [test]> alter table t1 change id id bigint;
ERROR 1815 (HY000): Internal error: CAL0001: Alter table Failed:  Changing the datatype of a column is not supported

建表范例

CREATE TABLE `sbtest` (
  `id` int(10)   unsigned NOT NULL,
  `k` int(10)   unsigned NOT NULL DEFAULT '0',
  `c` char(120)   DEFAULT '',
  `pad` char(60)   NOT NULL DEFAULT ''
) ENGINE=Columnstore DEFAULT CHARSET=utf8;

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

大数据解决方案