ODPS SQL，UDTF实现递归查询，以及不走网络获取本表数据的问题

目录

前言（屁话）：

数据获取：

数据处理：

---

前言（屁话）：

        要知道，有些数据处理可能需要循环的操作，例如MySQL中经典的找到父子节点的问题，其是通过SQL递归的方式实现，ODPS貌似（或许可以用do_while节点？）不能通过纯SQL的方式实现递归操作，因此如果要进行查询父子节点的问题，可能用UDF的方式比较简单一些。
        想想看，通过UDF获取本表的数据存储至Map后，来一行数据就进行一次比对查询Map，非常的简单。但是它走网络，不论是在UDF中运行SQLTask，亦或者用Tunnel，都是走网络的，本地测试都能通过，但如果你的项目空间没有开通网络权限的话可能就比较复杂。对于你来说可能只是来自于上级的要求简单的对少量的数据进行一个递归查询操作；但是你有可能为了获取表的数据而层层上报，让不会ODPS的领导给你弄网络配置，并且不一定能整好。

那么，我们该怎样获取数据，并进行递归查询呢？

数据获取：

        这个可能仅对非大量表的数据有效。比如一张用户表，有用户id（user_id）和上级id（parent_id）两个字段，当上级id为“root”时，说明该用户的id层级为1，依次递推，我们需要获取用户的层级。如下图：

        我们获取到id与上级id就能做，难点就在这，怎样不走网络得到这样的数据呢？我一开始看到这个类型对应表的时候，想把user_id与parent_id变成一个map存储，但是ODPS里的map创建是横向的，也就是说你要将这两列的数据变成一行作为字段，这并不现实（如果有100w用户，就会多出200w字段）。因此ODPS的map结构更多的是用于已经统计完了的ADS层报表数据（字段中文名，统计值）。

 这时还有另一个存储方式就是Array，也是不定长值的存储方式，通过collect_set()函数就能实现，但是怎么将两列的数据都包含其中呢？我们可以用concat(user_id,'-',parent_id)将其变成一个字符串存入。这样两列数据首先变成了一个字符串，再全部装入了一个array中。所以数据量过大还是不建议这样实现。

数据处理：

         也就是我们的UDTF怎么去写，这里直接放出处理逻辑代码(具体逻辑怎么写可以参考官网的UDTF)。

import com.aliyun.odps.udf.ExecutionContext;
import com.aliyun.odps.udf.UDFException;
import com.aliyun.odps.udf.UDTF;
import com.aliyun.odps.udf.annotation.Resolve;
import java.util.ArrayList;
import java.util.HashMap;

/*  该udtf用于判断用户级别
 *
 * */
// TODO define input and output types, e.g. "string,string->string,bigint".
@Resolve({"array<String>->String,bigint"})
public class MyLevel extends UDTF {
    public static HashMap<String, String> map;
    public static ArrayList<String> list;

    @Override
    public void setup(ExecutionContext ctx) throws UDFException {
        map = new HashMap<String, String>();
        list = new ArrayList<String>();
    }

    @Override
    public void process(Object[] args) throws UDFException {
        // TODO
        //获取数据
        ArrayList<String> l = (ArrayList<String>) args[0];
        //对每一组数据进行处理
        for (String s1 : l) {
            String[] split = s1.split("-");
            String uid = split[0];
            String pid = split[1];
            //存放uid，用于之后的取数据
            list.add(uid);
            //数据处理好存入map
            map.put(uid, pid);
        }
        //得到总共用户数
        int size = list.size();
        //对所有的用户遍历
        for (int i = 0; i < size; i++) {
            //存一下uid
            String uid = list.get(i);
            long level = 0;
            //赋值，进行循环
            String s2 = uid;
            System.out.println(uid);
            //parent_id为root时退出循环
            do {
                s2 = map.get(s2);
                level++;
            } while (!("root".equals(s2) || uid.equals(s2) ||s2==null));
            //进行输出
            forward(uid, level);
        }
    }

    @Override
    public void close() throws UDFException {

    }

}

注册好函数（详情见官网注册流程，很快的），提交jar包（不用带依赖的jar包就行），我们就可以直接写SQL了

    
SELECT 
    user_level(collect_set(CONCAT(cast(user_id as STRING),'-',parent_id))) as (user_id,level) 
--用于函数输入，需要一个Array<String>
from nanyan_space.dim_channel_webusers_info  
where pt=99990101

运行结果无误（这里就不演示了）。