|NO.Z.00019|——————————|BigDataEnd|——|Hadoop&PB级数仓.V03|——|PB数仓.v03|会员活跃度分析|json数据处理&使用UDF处理json串|

一、使用UDF处理

### --- 自定义UDF处理json串中的数组。自定义UDF函数：

~~~     输入：json串、数组的key
~~~     输出：字符串数组

二、创建一个maven项目

### --- 创建一个maven项目：cn.yanqi.dw
### --- 添加pom.xml依赖

        <dependency>
            <groupId>org.apache.hive</groupId>
            <artifactId>hive-exec</artifactId>
            <version>2.3.7</version>
            <scope>provided</scope>
        </dependency>

三、编程代码实现

### --- 编程代码实现

package cn.yanqi.dw.hive.udf;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONException;
import com.alibaba.fastjson.JSONObject;
import com.google.common.base.Strings;
import org.apache.hadoop.hive.ql.exec.UDF;
import org.junit.Test;
import java.util.ArrayList;

public class ParseJsonArray extends UDF {
    public ArrayList<String> evaluate(String jsonStr, String arrKey){
        //  传入空字符串，返回null
        if (Strings.isNullOrEmpty(jsonStr)) {
            return null;
        }

        try{
            //  获取jsonArray
            JSONObject object = JSON.parseObject(jsonStr);
            JSONArray jsonArray = object.getJSONArray(arrKey);
            ArrayList<String> result = new ArrayList<>();
            for (Object o: jsonArray){
                result.add(o.toString());
            }
            return result;
        } catch (JSONException e){
            return null;
        }
    }

    @Test
    public void JunitParseJsonArray(){
        String str = "{\"id\": 1,\"ids\": [101,102,103],\"total_number\": 3}";
        String key = "ids";
        ArrayList<String> evaluate = evaluate(str, key);
        System.out.println(JSON.toJSONString(evaluate));
    }
}

四、编译打印

### --- 编译打印

D:\JAVA\jdk1.8.0_231\bin\java.exe -ea -Didea.test.cyclic.buffer.size=1048576 "-javaagent:D:\IntelliJIDEA\IntelliJ IDEA 2019.3.3\lib\idea_rt.jar=64989:D:\IntelliJIDEA\IntelliJ IDEA 2019.3.3\bin" -Dfile.encoding=UTF-8 -classpath "D:\IntelliJIDEA\IntelliJ IDEA 2019.3.3\lib\idea_rt.jar;D:\IntelliJIDEA\IntelliJ IDEA 2019.3.3\plugins\junit\lib\junit5-rt.jar;D:\IntelliJIDEA\IntelliJ IDEA 2019.3.3\plugins\junit\lib\junit-rt.jar;D:\JAVA\jdk1.8.0_231\jre\lib\charsets.jar;D:\JAVA\jdk1.8.0_231\jre\lib\deploy.jar;D:\JAVA\jdk1.8.0_231\jre\lib\ext\access-bridge-64.jar;D:\JAVA\jdk1.8.0_231\jre\lib\ext\cldrdata.jar;D:\JAVA\jdk1.8.0_231\jre\lib\ext\dnsns.jar;D:\JAVA\jdk1.8.0_231\jre\lib\ext\jaccess.jar;D:\JAVA\jdk1.8.0_231\jre\lib\ext\jfxrt.jar;D:\JAVA\jdk1.8.0_231\jre\lib\ext\localedata.jar;D:\JAVA\jdk1.8.0_231\jre\lib\ext\nashorn.jar;D:\JAVA\jdk1.8.0_231\jre\lib\ext\sunec.jar;D:\JAVA\jdk1.8.0_231\jre\lib\ext\sunjce_provider.jar;D:\JAVA\jdk1.8.0_231\jre\lib\ext\sunmscapi.jar;D:\JAVA\jdk1.8.0_231\jre\lib\ext\sunpkcs11.jar;D:\JAVA\jdk1.8.0_231\jre\lib\ext\zipfs.jar;D:\JAVA\jdk1.8.0_231\jre\lib\javaws.jar;D:\JAVA\jdk1.8.0_231\jre\lib\jce.jar;D:\JAVA\jdk1.8.0_231\jre\lib\jfr.jar;D:\JAVA\jdk1.8.0_231\jre\lib\jfxswt.jar;D:\JAVA\jdk1.8.0_231\jre\lib\jsse.jar;D:\JAVA\jdk1.8.0_231\jre\lib\management-agent.jar;D:\JAVA\jdk1.8.0_231\jre\lib\plugin.jar;D:\JAVA\jdk1.8.0_231\jre\lib\resources.jar;D:\JAVA\jdk1.8.0_231\jre\lib\rt.jar;E:\NO.Z.10000——javaproject\NO.Z.00002.Hadoop\cn.yanqi.dw\target\classes;C:\Users\Administrator\.m2\repository\org\apache\flume\flume-ng-core\1.9.0\flume-ng-core-1.9.0.jar;C:\Users\Administrator\.m2\repository\org\apache\flume\flume-ng-sdk\1.9.0\flume-ng-sdk-1.9.0.jar;C:\Users\Administrator\.m2\repository\org\apache\flume\flume-ng-configuration\1.9.0\flume-ng-configuration-1.9.0.jar;C:\Users\Administrator\.m2\repository\org\apache\flume\flume-ng-configfilters\flume-ng-config-filter-api\1.9.0\flume-ng-config-filter-api-1.9.0.jar;C:\Users\Administrator\.m2\repository\org\apache\flume\flume-ng-auth\1.9.0\flume-ng-auth-1.9.0.jar;C:\Users\Administrator\.m2\repository\org\slf4j\slf4j-api\1.7.25\slf4j-api-1.7.25.jar;C:\Users\Administrator\.m2\repository\com\google\guava\guava\11.0.2\guava-11.0.2.jar;C:\Users\Administrator\.m2\repository\com\google\code\findbugs\jsr305\1.3.9\jsr305-1.3.9.jar;C:\Users\Administrator\.m2\repository\commons-io\commons-io\2.1\commons-io-2.1.jar;C:\Users\Administrator\.m2\repository\commons-codec\commons-codec\1.8\commons-codec-1.8.jar;C:\Users\Administrator\.m2\repository\commons-cli\commons-cli\1.2\commons-cli-1.2.jar;C:\Users\Administrator\.m2\repository\commons-lang\commons-lang\2.5\commons-lang-2.5.jar;C:\Users\Administrator\.m2\repository\org\apache\avro\avro\1.7.4\avro-1.7.4.jar;C:\Users\Administrator\.m2\repository\org\codehaus\jackson\jackson-core-asl\1.8.8\jackson-core-asl-1.8.8.jar;C:\Users\Administrator\.m2\repository\org\codehaus\jackson\jackson-mapper-asl\1.8.8\jackson-mapper-asl-1.8.8.jar;C:\Users\Administrator\.m2\repository\com\thoughtworks\paranamer\paranamer\2.3\paranamer-2.3.jar;C:\Users\Administrator\.m2\repository\org\xerial\snappy\snappy-java\1.0.4.1\snappy-java-1.0.4.1.jar;C:\Users\Administrator\.m2\repository\org\apache\avro\avro-ipc\1.7.4\avro-ipc-1.7.4.jar;C:\Users\Administrator\.m2\repository\org\mortbay\jetty\jetty\6.1.26\jetty-6.1.26.jar;C:\Users\Administrator\.m2\repository\org\mortbay\jetty\jetty-util\6.1.26\jetty-util-6.1.26.jar;C:\Users\Administrator\.m2\repository\org\apache\velocity\velocity\1.7\velocity-1.7.jar;C:\Users\Administrator\.m2\repository\commons-collections\commons-collections\3.2.1\commons-collections-3.2.1.jar;C:\Users\Administrator\.m2\repository\io\netty\netty\3.10.6.Final\netty-3.10.6.Final.jar;C:\Users\Administrator\.m2\repository\joda-time\joda-time\2.9.9\joda-time-2.9.9.jar;C:\Users\Administrator\.m2\repository\org\eclipse\jetty\jetty-servlet\9.4.6.v20170531\jetty-servlet-9.4.6.v20170531.jar;C:\Users\Administrator\.m2\repository\org\eclipse\jetty\jetty-security\9.4.6.v20170531\jetty-security-9.4.6.v20170531.jar;C:\Users\Administrator\.m2\repository\org\eclipse\jetty\jetty-util\9.4.6.v20170531\jetty-util-9.4.6.v20170531.jar;C:\Users\Administrator\.m2\repository\org\eclipse\jetty\jetty-server\9.4.6.v20170531\jetty-server-9.4.6.v20170531.jar;C:\Users\Administrator\.m2\repository\javax\servlet\javax.servlet-api\3.1.0\javax.servlet-api-3.1.0.jar;C:\Users\Administrator\.m2\repository\org\eclipse\jetty\jetty-http\9.4.6.v20170531\jetty-http-9.4.6.v20170531.jar;C:\Users\Administrator\.m2\repository\org\eclipse\jetty\jetty-io\9.4.6.v20170531\jetty-io-9.4.6.v20170531.jar;C:\Users\Administrator\.m2\repository\org\eclipse\jetty\jetty-jmx\9.4.6.v20170531\jetty-jmx-9.4.6.v20170531.jar;C:\Users\Administrator\.m2\repository\com\google\code\gson\gson\2.2.2\gson-2.2.2.jar;C:\Users\Administrator\.m2\repository\org\apache\thrift\libthrift\0.9.3\libthrift-0.9.3.jar;C:\Users\Administrator\.m2\repository\org\apache\httpcomponents\httpclient\4.4.1\httpclient-4.4.1.jar;C:\Users\Administrator\.m2\repository\org\apache\httpcomponents\httpcore\4.4.1\httpcore-4.4.1.jar;C:\Users\Administrator\.m2\repository\org\apache\mina\mina-core\2.0.4\mina-core-2.0.4.jar;C:\Users\Administrator\.m2\repository\com\alibaba\fastjson\1.1.23\fastjson-1.1.23.jar;C:\Users\Administrator\.m2\repository\junit\junit\4.12\junit-4.12.jar;C:\Users\Administrator\.m2\repository\org\hamcrest\hamcrest-core\1.3\hamcrest-core-1.3.jar;C:\Users\Administrator\.m2\repository\org\apache\hive\hive-exec\2.3.7\hive-exec-2.3.7.jar;C:\Users\Administrator\.m2\repository\org\apache\hive\hive-vector-code-gen\2.3.7\hive-vector-code-gen-2.3.7.jar;C:\Users\Administrator\.m2\repository\org\apache\hive\hive-llap-tez\2.3.7\hive-llap-tez-2.3.7.jar;C:\Users\Administrator\.m2\repository\org\apache\hive\hive-common\2.3.7\hive-common-2.3.7.jar;C:\Users\Administrator\.m2\repository\org\apache\hive\hive-storage-api\2.4.0\hive-storage-api-2.4.0.jar;C:\Users\Administrator\.m2\repository\org\apache\orc\orc-core\1.3.4\orc-core-1.3.4.jar;C:\Users\Administrator\.m2\repository\io\airlift\aircompressor\0.8\aircompressor-0.8.jar;C:\Users\Administrator\.m2\repository\io\airlift\slice\0.29\slice-0.29.jar;C:\Users\Administrator\.m2\repository\org\openjdk\jol\jol-core\0.2\jol-core-0.2.jar;C:\Users\Administrator\.m2\repository\org\eclipse\jetty\aggregate\jetty-all\7.6.0.v20120127\jetty-all-7.6.0.v20120127.jar;C:\Users\Administrator\.m2\repository\org\apache\geronimo\specs\geronimo-jta_1.1_spec\1.1.1\geronimo-jta_1.1_spec-1.1.1.jar;C:\Users\Administrator\.m2\repository\javax\mail\mail\1.4.1\mail-1.4.1.jar;C:\Users\Administrator\.m2\repository\javax\activation\activation\1.1\activation-1.1.jar;C:\Users\Administrator\.m2\repository\org\apache\geronimo\specs\geronimo-jaspic_1.0_spec\1.0\geronimo-jaspic_1.0_spec-1.0.jar;C:\Users\Administrator\.m2\repository\org\apache\geronimo\specs\geronimo-annotation_1.0_spec\1.1.1\geronimo-annotation_1.0_spec-1.1.1.jar;C:\Users\Administrator\.m2\repository\asm\asm-commons\3.1\asm-commons-3.1.jar;C:\Users\Administrator\.m2\repository\asm\asm-tree\3.1\asm-tree-3.1.jar;C:\Users\Administrator\.m2\repository\asm\asm\3.1\asm-3.1.jar;C:\Users\Administrator\.m2\repository\org\eclipse\jetty\orbit\javax.servlet\3.0.0.v201112011016\javax.servlet-3.0.0.v201112011016.jar;C:\Users\Administrator\.m2\repository\org\apache\logging\log4j\log4j-web\2.6.2\log4j-web-2.6.2.jar;C:\Users\Administrator\.m2\repository\com\tdunning\json\1.8\json-1.8.jar;C:\Users\Administrator\.m2\repository\io\dropwizard\metrics\metrics-core\3.1.0\metrics-core-3.1.0.jar;C:\Users\Administrator\.m2\repository\io\dropwizard\metrics\metrics-jvm\3.1.0\metrics-jvm-3.1.0.jar;C:\Users\Administrator\.m2\repository\io\dropwizard\metrics\metrics-json\3.1.0\metrics-json-3.1.0.jar;C:\Users\Administrator\.m2\repository\com\fasterxml\jackson\core\jackson-databind\2.6.5\jackson-databind-2.6.5.jar;C:\Users\Administrator\.m2\repository\com\fasterxml\jackson\core\jackson-annotations\2.6.0\jackson-annotations-2.6.0.jar;C:\Users\Administrator\.m2\repository\com\fasterxml\jackson\core\jackson-core\2.6.5\jackson-core-2.6.5.jar;C:\Users\Administrator\.m2\repository\com\github\joshelser\dropwizard-metrics-hadoop-metrics2-reporter\0.1.2\dropwizard-metrics-hadoop-metrics2-reporter-0.1.2.jar;C:\Users\Administrator\.m2\repository\org\apache\hadoop\hadoop-common\2.6.0\hadoop-common-2.6.0.jar;C:\Users\Administrator\.m2\repository\org\apache\commons\commons-math3\3.1.1\commons-math3-3.1.1.jar;C:\Users\Administrator\.m2\repository\xmlenc\xmlenc\0.52\xmlenc-0.52.jar;C:\Users\Administrator\.m2\repository\commons-net\commons-net\3.1\commons-net-3.1.jar;C:\Users\Administrator\.m2\repository\javax\servlet\servlet-api\2.5\servlet-api-2.5.jar;C:\Users\Administrator\.m2\repository\com\sun\jersey\jersey-server\1.9\jersey-server-1.9.jar;C:\Users\Administrator\.m2\repository\tomcat\jasper-compiler\5.5.23\jasper-compiler-5.5.23.jar;C:\Users\Administrator\.m2\repository\tomcat\jasper-runtime\5.5.23\jasper-runtime-5.5.23.jar;C:\Users\Administrator\.m2\repository\javax\servlet\jsp\jsp-api\2.1\jsp-api-2.1.jar;C:\Users\Administrator\.m2\repository\commons-el\commons-el\1.0\commons-el-1.0.jar;C:\Users\Administrator\.m2\repository\net\java\dev\jets3t\jets3t\0.9.0\jets3t-0.9.0.jar;C:\Users\Administrator\.m2\repository\com\jamesmurty\utils\java-xmlbuilder\0.4\java-xmlbuilder-0.4.jar;C:\Users\Administrator\.m2\repository\commons-configuration\commons-configuration\1.6\commons-configuration-1.6.jar;C:\Users\Administrator\.m2\repository\commons-digester\commons-digester\1.8\commons-digester-1.8.jar;C:\Users\Administrator\.m2\repository\commons-beanutils\commons-beanutils\1.7.0\commons-beanutils-1.7.0.jar;C:\Users\Administrator\.m2\repository\commons-beanutils\commons-beanutils-core\1.8.0\commons-beanutils-core-1.8.0.jar;C:\Users\Administrator\.m2\repository\org\slf4j\slf4j-log4j12\1.7.5\slf4j-log4j12-1.7.5.jar;C:\Users\Administrator\.m2\repository\org\apache\hadoop\hadoop-auth\2.6.0\hadoop-auth-2.6.0.jar;C:\Users\Administrator\.m2\repository\org\apache\directory\server\apacheds-kerberos-codec\2.0.0-M15\apacheds-kerberos-codec-2.0.0-M15.jar;C:\Users\Administrator\.m2\repository\org\apache\directory\server\apacheds-i18n\2.0.0-M15\apacheds-i18n-2.0.0-M15.jar;C:\Users\Administrator\.m2\repository\org\apache\directory\api\api-asn1-api\1.0.0-M20\api-asn1-api-1.0.0-M20.jar;C:\Users\Administrator\.m2\repository\org\apache\directory\api\api-util\1.0.0-M20\api-util-1.0.0-M20.jar;C:\Users\Administrator\.m2\repository\com\jcraft\jsch\0.1.42\jsch-0.1.42.jar;C:\Users\Administrator\.m2\repository\org\apache\curator\curator-recipes\2.6.0\curator-recipes-2.6.0.jar;C:\Users\Administrator\.m2\repository\org\htrace\htrace-core\3.0.4\htrace-core-3.0.4.jar;C:\Users\Administrator\.m2\repository\org\apache\hive\hive-llap-client\2.3.7\hive-llap-client-2.3.7.jar;C:\Users\Administrator\.m2\repository\org\apache\hive\hive-llap-common\2.3.7\hive-llap-common-2.3.7.jar;C:\Users\Administrator\.m2\repository\org\apache\hive\hive-serde\2.3.7\hive-serde-2.3.7.jar;C:\Users\Administrator\.m2\repository\org\apache\hive\hive-service-rpc\2.3.7\hive-service-rpc-2.3.7.jar;C:\Users\Administrator\.m2\repository\org\apache\thrift\libfb303\0.9.3\libfb303-0.9.3.jar;C:\Users\Administrator\.m2\repository\net\sf\opencsv\opencsv\2.3\opencsv-2.3.jar;C:\Users\Administrator\.m2\repository\org\apache\parquet\parquet-hadoop-bundle\1.8.1\parquet-hadoop-bundle-1.8.1.jar;C:\Users\Administrator\.m2\repository\org\apache\commons\commons-lang3\3.1\commons-lang3-3.1.jar;C:\Users\Administrator\.m2\repository\org\apache\hive\hive-shims\2.3.7\hive-shims-2.3.7.jar;C:\Users\Administrator\.m2\repository\org\apache\hive\shims\hive-shims-common\2.3.7\hive-shims-common-2.3.7.jar;C:\Users\Administrator\.m2\repository\org\apache\hive\shims\hive-shims-0.23\2.3.7\hive-shims-0.23-2.3.7.jar;C:\Users\Administrator\.m2\repository\org\apache\hadoop\hadoop-yarn-server-resourcemanager\2.7.2\hadoop-yarn-server-resourcemanager-2.7.2.jar;C:\Users\Administrator\.m2\repository\org\apache\hadoop\hadoop-annotations\2.7.2\hadoop-annotations-2.7.2.jar;C:\Users\Administrator\.m2\repository\com\google\inject\extensions\guice-servlet\3.0\guice-servlet-3.0.jar;C:\Users\Administrator\.m2\repository\com\google\inject\guice\3.0\guice-3.0.jar;C:\Users\Administrator\.m2\repository\javax\inject\javax.inject\1\javax.inject-1.jar;C:\Users\Administrator\.m2\repository\aopalliance\aopalliance\1.0\aopalliance-1.0.jar;C:\Users\Administrator\.m2\repository\com\sun\jersey\jersey-json\1.9\jersey-json-1.9.jar;C:\Users\Administrator\.m2\repository\com\sun\xml\bind\jaxb-impl\2.2.3-1\jaxb-impl-2.2.3-1.jar;C:\Users\Administrator\.m2\repository\org\codehaus\jackson\jackson-jaxrs\1.8.3\jackson-jaxrs-1.8.3.jar;C:\Users\Administrator\.m2\repository\org\codehaus\jackson\jackson-xc\1.8.3\jackson-xc-1.8.3.jar;C:\Users\Administrator\.m2\repository\com\sun\jersey\contribs\jersey-guice\1.9\jersey-guice-1.9.jar;C:\Users\Administrator\.m2\repository\org\apache\hadoop\hadoop-yarn-common\2.7.2\hadoop-yarn-common-2.7.2.jar;C:\Users\Administrator\.m2\repository\org\apache\hadoop\hadoop-yarn-api\2.7.2\hadoop-yarn-api-2.7.2.jar;C:\Users\Administrator\.m2\repository\javax\xml\bind\jaxb-api\2.2.2\jaxb-api-2.2.2.jar;C:\Users\Administrator\.m2\repository\javax\xml\stream\stax-api\1.0-2\stax-api-1.0-2.jar;C:\Users\Administrator\.m2\repository\org\codehaus\jettison\jettison\1.1\jettison-1.1.jar;C:\Users\Administrator\.m2\repository\com\sun\jersey\jersey-core\1.9\jersey-core-1.9.jar;C:\Users\Administrator\.m2\repository\com\sun\jersey\jersey-client\1.9\jersey-client-1.9.jar;C:\Users\Administrator\.m2\repository\org\apache\hadoop\hadoop-yarn-server-common\2.7.2\hadoop-yarn-server-common-2.7.2.jar;C:\Users\Administrator\.m2\repository\org\apache\hadoop\hadoop-yarn-server-applicationhistoryservice\2.7.2\hadoop-yarn-server-applicationhistoryservice-2.7.2.jar;C:\Users\Administrator\.m2\repository\org\apache\hadoop\hadoop-yarn-server-web-proxy\2.7.2\hadoop-yarn-server-web-proxy-2.7.2.jar;C:\Users\Administrator\.m2\repository\org\fusesource\leveldbjni\leveldbjni-all\1.8\leveldbjni-all-1.8.jar;C:\Users\Administrator\.m2\repository\org\apache\zookeeper\zookeeper\3.4.6\zookeeper-3.4.6-tests.jar;C:\Users\Administrator\.m2\repository\org\apache\hive\shims\hive-shims-scheduler\2.3.7\hive-shims-scheduler-2.3.7.jar;C:\Users\Administrator\.m2\repository\commons-httpclient\commons-httpclient\3.0.1\commons-httpclient-3.0.1.jar;C:\Users\Administrator\.m2\repository\commons-logging\commons-logging\1.0.3\commons-logging-1.0.3.jar;C:\Users\Administrator\.m2\repository\org\apache\logging\log4j\log4j-1.2-api\2.6.2\log4j-1.2-api-2.6.2.jar;C:\Users\Administrator\.m2\repository\org\apache\logging\log4j\log4j-api\2.6.2\log4j-api-2.6.2.jar;C:\Users\Administrator\.m2\repository\org\apache\logging\log4j\log4j-core\2.6.2\log4j-core-2.6.2.jar;C:\Users\Administrator\.m2\repository\org\apache\logging\log4j\log4j-slf4j-impl\2.6.2\log4j-slf4j-impl-2.6.2.jar;C:\Users\Administrator\.m2\repository\org\antlr\antlr-runtime\3.5.2\antlr-runtime-3.5.2.jar;C:\Users\Administrator\.m2\repository\org\antlr\ST4\4.0.4\ST4-4.0.4.jar;C:\Users\Administrator\.m2\repository\org\apache\ant\ant\1.9.1\ant-1.9.1.jar;C:\Users\Administrator\.m2\repository\org\apache\ant\ant-launcher\1.9.1\ant-launcher-1.9.1.jar;C:\Users\Administrator\.m2\repository\org\apache\commons\commons-compress\1.9\commons-compress-1.9.jar;C:\Users\Administrator\.m2\repository\org\apache\ivy\ivy\2.4.0\ivy-2.4.0.jar;C:\Users\Administrator\.m2\repository\org\apache\zookeeper\zookeeper\3.4.6\zookeeper-3.4.6.jar;C:\Users\Administrator\.m2\repository\log4j\log4j\1.2.16\log4j-1.2.16.jar;C:\Users\Administrator\.m2\repository\jline\jline\0.9.94\jline-0.9.94.jar;C:\Users\Administrator\.m2\repository\org\apache\curator\curator-framework\2.7.1\curator-framework-2.7.1.jar;C:\Users\Administrator\.m2\repository\org\apache\curator\curator-client\2.7.1\curator-client-2.7.1.jar;C:\Users\Administrator\.m2\repository\org\codehaus\groovy\groovy-all\2.4.4\groovy-all-2.4.4.jar;C:\Users\Administrator\.m2\repository\org\datanucleus\datanucleus-core\4.1.17\datanucleus-core-4.1.17.jar;C:\Users\Administrator\.m2\repository\org\apache\calcite\calcite-core\1.10.0\calcite-core-1.10.0.jar;C:\Users\Administrator\.m2\repository\org\apache\calcite\calcite-linq4j\1.10.0\calcite-linq4j-1.10.0.jar;C:\Users\Administrator\.m2\repository\commons-dbcp\commons-dbcp\1.4\commons-dbcp-1.4.jar;C:\Users\Administrator\.m2\repository\commons-pool\commons-pool\1.5.4\commons-pool-1.5.4.jar;C:\Users\Administrator\.m2\repository\net\hydromatic\eigenbase-properties\1.1.5\eigenbase-properties-1.1.5.jar;C:\Users\Administrator\.m2\repository\org\codehaus\janino\janino\2.7.6\janino-2.7.6.jar;C:\Users\Administrator\.m2\repository\org\codehaus\janino\commons-compiler\2.7.6\commons-compiler-2.7.6.jar;C:\Users\Administrator\.m2\repository\org\pentaho\pentaho-aggdesigner-algorithm\5.1.5-jhyde\pentaho-aggdesigner-algorithm-5.1.5-jhyde.jar;C:\Users\Administrator\.m2\repository\org\apache\calcite\calcite-druid\1.10.0\calcite-druid-1.10.0.jar;C:\Users\Administrator\.m2\repository\org\apache\calcite\avatica\avatica\1.8.0\avatica-1.8.0.jar;C:\Users\Administrator\.m2\repository\org\apache\calcite\avatica\avatica-metrics\1.8.0\avatica-metrics-1.8.0.jar;C:\Users\Administrator\.m2\repository\com\google\protobuf\protobuf-java\3.0.0-beta-1\protobuf-java-3.0.0-beta-1.jar;C:\Users\Administrator\.m2\repository\stax\stax-api\1.0.1\stax-api-1.0.1.jar" com.intellij.rt.junit.JUnitStarter -ideVersion5 -junit4 cn.yanqi.dw.hive.udf.ParseJsonArray
["101","102","103"]

五、打成jar包并上传到服务器中

### --- 编译打印并上传到服务器中

[root@hadoop02 ~]# ll /data/yanqidw/jars/
cn.yanqi.dw-1.0-SNAPSHOT-jar-with-dependencies.jar

六、使用自定义UDF函数

### --- 添加UDF自定义函数包

~~~     # 语法
~~~     添加开发的jar包（在Hive命令行中）
add jar /data/yanqidw/jars/cn.yanqi.dw-1.0-SNAPSHOT-jar-with-dependencies.jar;

~~~     # 操作实例
[root@hadoop02 ~]# hive
hive (default)> add jar /data/yanqidw/jars/cn.yanqi.dw-1.0-SNAPSHOT-jar-with-dependencies.jar;

### --- 创建临时函数

~~~     # 语法
~~~     创建临时函数。指定类名一定要完整的路径，即包名加类名
create temporary function yanqi_json_array as "cn.yanqi.dw.hive.udf.ParseJsonArray";

~~~     # 操作实例
hive (default)> create temporary function yanqi_json_array as "cn.yanqi.dw.hive.udf.ParseJsonArray";

### --- 执行查询

~~~     # 切换到use test分区下
hive (default)> use test;
hive (test)> show tables;
jsont1

### --- 解析json串中的数组
~~~     # 语法
~~~     执行查询
~~~     解析json串中的数组

select username, age, sex, yanqi_json_array(json, "ids") 
ids from jsont1;

~~~     # 操作实例

hive (test)> select username, age, sex, yanqi_json_array(json, "ids") 
           > ids from jsont1;
OK
username    age sex ids
user1   18  male    ["101","102","103"]
user2   20  female  ["201","202","203","204"]
user3   23  male    ["301","302","303","304","305"]
user4   17  male    ["401","402","403","304"]
user5   35  female  ["501","502","503"]

### --- 解析json串中的数组并展开
~~~     # 语法
~~~     解析json串中的数组，并展开

select username, age, sex, ids1 from jsont1
lateral view explode(yanqi_json_array(json, "ids")) t1 as ids1;

~~~     # 操作实例

hive (test)> select username, age, sex, ids1 from jsont1
           > lateral view explode(yanqi_json_array(json, "ids")) t1 as ids1;
OK
username    age sex ids1
user1   18  male    101
user1   18  male    102
user1   18  male    103
user2   20  female  201
user2   20  female  202
user2   20  female  203
user2   20  female  204
user3   23  male    301
user3   23  male    302
user3   23  male    303
user3   23  male    304
user3   23  male    305
user4   17  male    401
user4   17  male    402
user4   17  male    403
user4   17  male    304
user5   35  female  501
user5   35  female  502
user5   35  female  503

### --- 解析json串中的id和num
~~~     # 语法
~~~     解析json串中的id、num

select username, age, sex, id, num from jsont1
lateral view json_tuple(json, 'id', 'total_number') t1 as id, num;

~~~     # 操作实例

hive (test)> select username, age, sex, id, num from jsont1
           > lateral view json_tuple(json, 'id', 'total_number') t1 as id, num;
OK
username    age sex id  num
user1   18  male    1   3
user2   20  female  2   4
user3   23  male    3   5
user4   17  male    4   5
user5   35  female  5   3

### --- 解析json串中的数组并展开
~~~     # 语法
~~~     解析json串中的数组，并展开

select username, age, sex, ids1, id, num from jsont1
lateral view explode(yanqi_json_array(json, "ids")) t1 as ids1
lateral view json_tuple(json, 'id', 'total_number') t1 as id, num;

~~~     # 操作实例

hive (test)> select username, age, sex, ids1, id, num from jsont1
           > lateral view explode(yanqi_json_array(json, "ids")) t1 as ids1
           > lateral view json_tuple(json, 'id', 'total_number') t1 as id, num;
OK
username    age sex ids1    id  num
user1   18  male    101 1   3
user1   18  male    102 1   3
user1   18  male    103 1   3
user2   20  female  201 2   4
user2   20  female  202 2   4
user2   20  female  203 2   4
user2   20  female  204 2   4
user3   23  male    301 3   5
user3   23  male    302 3   5
user3   23  male    303 3   5
user3   23  male    304 3   5
user3   23  male    305 3   5
user4   17  male    401 4   5
user4   17  male    402 4   5
user4   17  male    403 4   5
user4   17  male    304 4   5
user5   35  female  501 5   3
user5   35  female  502 5   3
user5   35  female  503 5   3

---

 

 

 

 

 

 

 

 

 

---

Walter Savage Landor:strove with none,for none was worth my strife.Nature I loved and, next to Nature, Art:I warm'd both hands before the fire of life.It sinks, and I am ready to depart

                                                                                                                                                   ——W.S.Landor

---