impala&hive自定义UDF解析json中文key

迷南。 2022-09-09 13:58 186阅读 0赞

# 一、前述 #

由于hive的get\_json\_object 无法解析json中的中文key,于是重写了一个hive udf去对含有中文key的json进行解析。  
1）参考  
[https://cwiki.apache.org/confluence/display/Hive/HivePlugins][https_cwiki.apache.org_confluence_display_Hive_HivePlugins]  
[https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF][https_cwiki.apache.org_confluence_display_Hive_HivePlugins]  
[https://dzone.com/articles/writing-custom-hive-udf-andudaf][https_dzone.com_articles_writing-custom-hive-udf-andudaf]  
2）一些命令

SHOW FUNCTIONS;      查看hive函数
    DESCRIBE FUNCTION <function_name>;    查看hive某个函数的用法
    DESCRIBE FUNCTION EXTENDED <function_name>;  查看hive某个函数更详细的用法

# 二、程序编写 #

## 2.1 pom.xml ##

<repositories>
        <repository>
            <id>cloudera</id>
            <url>https://repository.cloudera.com/artifactory/cloudera-repos/</url>
        </repository>
    </repositories>
    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <hadoop.version>2.6.0-cdh5.16.2</hadoop.version>
        <hive.version>1.1.0-cdh5.16.2</hive.version>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
    </properties>
    <dependencies>
        
        <dependency>
            <groupId>org.apache.hadoop</groupId>
            <artifactId>hadoop-common</artifactId>
            <version>${hadoop.version}</version>
        </dependency>
        
        <dependency>
            <groupId>org.apache.hive</groupId>
            <artifactId>hive-exec</artifactId>
            <version>${hive.version}</version>
        </dependency>
    </dependencies>

## 2.2 java实现 ##

package com.yimian.hivefunction.udf;
    import org.apache.commons.lang.StringUtils;
    import org.apache.hadoop.hive.ql.exec.UDF;
    import org.json.JSONException;
    import org.json.JSONObject;
    import org.json.JSONTokener;
    public class GetJsonObject extends UDF { 
        /** * 解析json并返回对应的值。例如 * add jar jar/hive_customize_function-1.0-SNAPSHOT.jar; * create temporary function getJsonObject as 'com.yimian.hivefunction.udf.GetJsonObject'; * select getJsonObject(json字符串,"$.key值.key值") * * @param jsonStr * @param objName * @return */
        public String evaluate(String jsonStr, String objName) { 
            if (StringUtils.isBlank(jsonStr) || StringUtils.isBlank(objName) || StringUtils.containsNone(objName, "$")) { 
                return null;
            }
            String[] jsonParse = objName.split("\\.");
            if ((!jsonParse[0].trim().equals("$"))) { 
                return null;
            }
            Object objValue = new Object();
            try { 
                JSONObject jsonObject = new JSONObject(new JSONTokener(jsonStr));
                for (int i = 1; i < jsonParse.length; i++) { 
                    if (i != jsonParse.length - 1) { 
                        jsonObject = jsonObject.getJSONObject(jsonParse[i]);
                    } else { 
                        objValue = jsonObject.get(jsonParse[i]);
                    }
                }
            } catch (JSONException e) { 
                objValue = null;
            } finally { 
                if ((objValue == null)) { 
                    return null;
                }
            }
            return objValue.toString();
        }
    // public static void main(String[] args) throws JSONException { 
    // String jsonStr = "{\"包装清单\":\"思亲肤黑石榴莹润化妆水160mlx1\",\"品牌\":\"思亲肤(Skin food)\",\"类别\":\"化妆水\",\"国产\\/进口\":\"进口\",\"规格\":\"160毫升\",\"产地\":\"韩国\",\"国产产地\":\"无\",\"保质期\":\"3年\",\"是否为特殊用途化妆品\":\"否\",\"化妆品资质号\":\"国妆备进字J20144497\",\"适合肤质\":\"任何肤质\",\"适用人群\":\"通用\",\"功效\":\"修护,保湿补水,滋润营养,收缩毛孔\"}";
    // System.out.println(jsonStr);
    // GetJsonObject getJsonObject = new GetJsonObject();
    // System.out.println(getJsonObject.evaluate(jsonStr, "$.主体"));
    // }
    }

# 三、注册udf #

## 3.1 注册hive-udf ##

1.上传

hadoop fs -mkdir /user/hive/udf
    hadoop fs -copyFromLocal [jar包本地路径]/hive_customize_function-1.0-SNAPSHOT.jar /user/hive/udf/

2.临时注册

# 1.引用hive udf jar路径
    add jar /user/hive/udf/hive_customize_function-1.0-SNAPSHOT.jar;
    # 2.创建udf库，引用相关函数，查看是否注册成功
    CREATE DATABASE udfs;
    CREATE TEMPORARY FUNCTION udfs.GetJsonObject AS "com.yimian.hivefunction.udf.GetJsonObject";
    SHOW FUNCTIONS;
    # 3.退出
    quit;
    # 4.查看函数，已无
    SHOW FUNCTIONS;
    # 5.删除临时函数
    drop temporary function add_prefix;

3.永久注册

CREATE DATABASE udfs;
    # 注册永久函数
    CREATE FUNCTION udfs.GetJsonObject AS "com.yimian.hivefunction.udf.GetJsonObject" USING JAR 'hdfs:/user/hive/udfs/hive_customize_function-1.0-SNAPSHOT.jar'; 
    # 进入hive元数据mysql中查看
    select * from FUNCS;
    # 删除永久注册函数
    drop function GetJsonObject;
    # 查看udfs库下的函数
    SHOW FUNCTIONS;

# 3.2 注册impala-udf #

create function udfs.GetJsonObject(string,string) returns string location '/user/hive/udfs/hive_customize_function-1.0-SNAPSHOT.jar' symbol='com.yimian.hivefunction.udf.GetJsonObject';

[https_cwiki.apache.org_confluence_display_Hive_HivePlugins]: https://cwiki.apache.org/confluence/display/Hive/HivePlugins
[https_dzone.com_articles_writing-custom-hive-udf-andudaf]: https://dzone.com/articles/writing-custom-hive-udf-andudaf