阿里云ADB(MySQL)迁移Apache Doris或StarRocks SQL查询兼容性处理

1. 问题和解决办法

1.1 SELECT 字段中的非聚合函数包裹的字段,必须在 GROUP BY中申明

# SQLSTATE[HY000]: General error: 1064 '`u`.`name`' must be an aggregate expression or appear in GROUP BY clause

这个问题会是遇到最多的问题(实际上在大数据场景,例如hive中也有这个要求的)
因为ADB(MySQL)引擎是MySQL所以对这个没要求,导致迁移到Doris的时候这个问题最为突出
时间处理函数、字符串处理函数这种是非聚合函数,所以也是需要在GROUP BY中申明的

1.2 Doris的 GROUP_CONCAT函数中字段前不支持用distinct去重 #11932

https://github.com/StarRocks/starrocks/issues/8079
array_distinct(array_agg(str_col))
to get a distinct a array value.
And if you want to make it a value you can use the following function
array_join(array_distinct(array_agg(str_col)), ‘,’)

也就是先取出列为数组,然后去重数组,再把数组拼接字符串

ps:新版本的Doris已经支持,见
[Bug] doris的group_concat函数不支持distinct #11932

1.3 GROUP_CONCAT函数中的字段不支持int类型

需要拼接的字段如果为int类型那么必须先强转成字符串,使用CAST函数
GROUP_CONCAT(CAST( id整形字段 as STRING), ‘,’)

1.4 SUBSTRING_INDEX函数不支持

截止2022年11月11日需要使用UDF,也就是用户定义函数解决
Apache Doris 1.1 版本只支持原生UDF,也就是需要重新编译整个Doris,1.2 版本开始支持Java UDF 可以动态挂载
StarRocks 2.2.0 版本开始支持Java UDF 可以动态挂载

提供一个已经实现好的StarRocks Java UDF,可直接使用(由同事贡献)
代码

package com.starrocks.udf;  

import org.apache.commons.lang3.StringUtils;  

/**  
 * 根据下标截取  
 *  
 * @author dingyoukun  
 * @date 2022-10-26 14:30  
 **/public class SubStringByIndex {  
    public final String evaluate (String targetStr, String str, Integer index) {  
        Boolean desc = false;  
        if (index < 0 ) {  
            desc = true;  
            index = Math.abs(index);  
        }  

        String result = targetStr;  
        String partStr = str;  

        if (StringUtils.isBlank(targetStr)) {  
            return result;  
        }  

        if (index == 0) {  
            return targetStr;  
        }  

        if (desc) {  
            targetStr = new StringBuffer(targetStr).reverse().toString();  
            partStr = new StringBuffer(partStr).reverse().toString();  
        }  
        int beginIndex = 0;  
        int count = 0;  
        while ((beginIndex = targetStr.indexOf(partStr, beginIndex)) != -1) {  
            count++;  
            if (count == index) {  
                if (desc) {  
                    targetStr = new StringBuffer(targetStr).reverse().toString();  
                    result = targetStr.substring(targetStr.length() - beginIndex);  
                } else {  
                    result = targetStr.substring(0, beginIndex);  
                }  
                return result;  
            }  
            beginIndex = beginIndex + partStr.length();  
        }  
        return result;  
    }  
}

2. 参考

https://docs.starrocks.io/zh-cn/latest/introduction/StarRocks_intro
https://github.com/StarRocks/starrocks/issues

https://github.com/apache/doris/issues
https://doris.apache.org/zh-CN/docs/summary/basic-summary

[Apache Doris Java UDF https://doris.apache.org/zh-CN/docs/dev/ecosystem/udf/java-user-defined-function] (https://doris.apache.org/zh-CN/docs/dev/ecosystem/udf/java-user-defined-function)
[StarRocks Java UDF https://docs.starrocks.io/zh-cn/latest/sql-reference/sql-functions/JAVA_UDF] (https://docs.starrocks.io/zh-cn/latest/sql-reference/sql-functions/JAVA_UDF)

发表回复

您的电子邮箱地址不会被公开。

注意: 评论者允许使用'@user空格'的方式将自己的评论通知另外评论者。例如, ABC是本文的评论者之一,则使用'@ABC '(不包括单引号)将会自动将您的评论发送给ABC。使用'@all ',将会将评论发送给之前所有其它评论者。请务必注意user必须和评论者名相匹配(大小写一致)。