使用spark对hive表中的多列数据判重

本文处理的场景如下，hive表中的数据，对其中的多列进行判重deduplicate。

1、先解决依赖，spark相关的所有包，pom.xml

spark-hive是我们进行hive表spark处理的关键。

<dependencies>
        <dependency>
            <groupId>org.apache.spark</groupId>
            <artifactId>spark-core_2.10</artifactId>
            <version>1.6.0</version>
            <scope>provided</scope>
        </dependency>
        <dependency>
            <groupId>org.apache.spark</groupId>
            <artifactId>spark-hive_2.10</artifactId>
            <version>1.6.0</version>
            <scope>provided</scope>
        </dependency>
        <dependency>
            <groupId>org.apache.spark</groupId>
            <artifactId>spark-sql_2.10</artifactId>
            <version>1.6.0</version>
            <scope>provided</scope>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.19</version>
        </dependency>
    </dependencies>

2、spark-client

package com.xiaoju.kangaroo.duplicate;

import org.apache.spark.SparkConf;
import org.apache.spark.api.java.JavaSparkContext;
import org.apache.spark.sql.SQLContext;
import org.apache.spark.sql.hive.HiveContext;

import java.io.Serializable;

public class SparkClient implements Serializable{
    private SparkConf sparkConf;
    private JavaSparkContext javaSparkContext;

    public SparkClient() {
        initSparkConf();
        javaSparkContext = new JavaSparkContext(sparkConf);
    }


    public SQLContext getSQLContext() {
        return new SQLContext(javaSparkContext);
    }

    public HiveContext getHiveContext() {
        return new HiveContext(javaSparkContext);
    }

    private void initSparkConf() {
        try {
            String warehouseLocation = System.getProperty("user.dir");
            sparkConf = new SparkConf()
                    .setAppName("duplicate")
                    .set("spark.sql.warehouse.dir", warehouseLocation)
                    .setMaster("yarn-client");
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

}

3、判重流程

package com.xiaoju.kangaroo.duplicate;

import org.apache.spark.api.java.JavaRDD;
import org.apache.spark.api.java.function.FlatMapFunction;
import org.apache.spark.api.java.function.Function;
import org.apache.spark.api.java.function.Function2;
import org.apache.spark.api.java.function.PairFunction;
import org.apache.spark.sql.DataFrame;
import org.apache.spark.sql.Row;
import org.apache.spark.sql.hive.HiveContext;
import scala.Tuple2;

import java.io.Serializable;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class SparkDuplicate implements Serializable  {

    private transient SparkClient sparkClient;
    private transient HiveContext hiveContext;
    private String db;
    private String tb;
    private String pt;
    private String cols;

    public SparkDuplicate(String db, String tb, String pt, String cols) {
        this.db = db;
        this.tb = tb;
        this.pt = pt;
        this.cols = cols;
        this.sparkClient = new SparkClient();
        this.hiveContext = sparkClient.getHiveContext();
    }

    public void duplicate() {
        String partition = formatPartition(pt);
        String query = String.format("select * from %s.%s where %s", db ,tb, partition);
        System.out.println(query);
        DataFrame rows = hiveContext.sql(query);
        JavaRDD<Row> rdd = rows.toJavaRDD();
        Map<String, Integer> repeatRetMap = rdd.flatMap(new FlatMapFunction<Row, String>() {
            public Iterable<String> call(Row row) throws Exception {
                HashMap<String, Object> rowMap = formatRowMap(row);
                List<String> sList = new ArrayList<String>();
                String[] colList = cols.split(",");
                for (String col : colList) {
                    sList.add(col + "@" + rowMap.get(col));
                }
                return sList;
            }
        }).mapToPair(new PairFunction<String, String, Integer>() {
            public Tuple2<String, Integer> call(String s) throws Exception {
                return new Tuple2<String, Integer>(s, 1);

            }
        }).reduceByKey(new Function2<Integer, Integer, Integer>() {
            public Integer call(Integer integer, Integer integer2) throws Exception {
                return integer + integer2;
            }
        }).map(new Function<Tuple2<String,Integer>, Map<String, Integer>>() {
            public Map<String, Integer> call(Tuple2<String, Integer> stringIntegerTuple2) throws Exception {
                Map<String, Integer> retMap = new HashMap<String, Integer>();
                if (stringIntegerTuple2._2 > 1) {
                    retMap.put(stringIntegerTuple2._1, stringIntegerTuple2._2);
                }
                return retMap;
            }
        }).reduce(new Function2<Map<String, Integer>, Map<String, Integer>, Map<String, Integer>>() {
            public Map<String, Integer> call(Map<String, Integer> stringIntegerMap, Map<String, Integer> stringIntegerMap2) throws Exception {
                stringIntegerMap.putAll(stringIntegerMap2);
                return stringIntegerMap;
            }
        });

        for (Map.Entry<String, Integer> entry : repeatRetMap.entrySet()) {
            if (entry.getValue() > 1) {
                System.out.println("重复值为:" + entry.getKey() + ", 重复个数" + entry.getValue());
            }
        }
    }

    private String formatPartition(String partition) {
        String format = "";
        if (partition.startsWith("pt") || partition.startsWith("dt")) {
            String[] items = partition.split("=");
            for (int i = 0; i < items.length; i++) {
                if (items[i].equals("pt") || items[i].equals("dt")) {
                    format += items[i];
                } else {
                    format += "='" + items[i] + "'";
                }
            }
        } else {
            String[] keys;
            if (partition.contains("w=")){
                keys = new String[] {"year", "week"};
                partition = partition.replace("w=", "");
            }
            else{
                keys = new String[] {"year","month","day", "hour"};
            }
            String[] items = partition.split("/");
            for(int i=0; i<items.length; i++) {
                if (i == items.length-1) {
                    format += keys[i] + "='" + items[i] + "'";
                } else {
                    format += keys[i] + "='" + items[i] + "' and ";
                }
            }
        }
        return format;
    }

    private HashMap<String, Object> formatRowMap(Row row){
        HashMap<String, Object> rowMap = new HashMap<String, Object>();
        try {
            for (int i=0; i<row.schema().fields().length; i++) {
                String colName = row.schema().fields()[i].name();
                Object colValue = row.get(i);
                rowMap.put(colName, colValue);

            }
        }catch (Exception ex) {
            ex.printStackTrace();
        }
        return rowMap;
    }

    public static void main(String[] args) {
        String db = args[0];
        String tb = args[1];
        String pt = args[2];
        String cols = args[3];
        SparkDuplicate sparkDuplicate = new SparkDuplicate(db, tb, pt, cols);
        sparkDuplicate.duplicate();
    }
}

4、运行方式

提交任务脚本

#!/bin/bash
source /etc/profile
source ~/.bash_profile
db=$1
table=$2
partition=$3
cols=$4
spark-submit 
    --queue=root.zhiliangbu_prod_datamonitor 
    --driver-memory 500M 
    --executor-memory 13G 
    --num-executors 50 
    spark-duplicate-1.0-SNAPSHOT-jar-with-dependencies.jar ${db} ${table} ${partition} ${cols}

运行：

sh run.sh gulfstream_ods g_order 2017/07/11 area,type

结果

重复值为:area@179, 重复个数225                                                  
重复值为:area@80, 重复个数7398
重复值为:area@82, 重复个数69823
重复值为:area@81, 重复个数98317
重复值为:area@84, 重复个数91775
重复值为:area@83, 重复个数72053
重复值为:area@180, 重复个数2362
重复值为:area@86, 重复个数264487
重复值为:area@181, 重复个数2927
重复值为:area@85, 重复个数230484
重复值为:area@88, 重复个数87527
重复值为:area@87, 重复个数74987
重复值为:area@89, 重复个数130297
重复值为:area@188, 重复个数24463
重复值为:area@189, 重复个数15699
重复值为:area@186, 重复个数13517
重复值为:area@187, 重复个数4774
重复值为:area@184, 重复个数5022
重复值为:area@185, 重复个数6737
重复值为:area@182, 重复个数12705
重复值为:area@183, 重复个数18961
重复值为:area@289, 重复个数20715
重复值为:area@168, 重复个数15179
重复值为:area@169, 重复个数1276
重复值为:area@91, 重复个数31664
重复值为:area@90, 重复个数61261
重复值为:area@93, 重复个数32496
重复值为:area@92, 重复个数55877
重复值为:area@95, 重复个数40933
重复值为:area@94, 重复个数32564
重复值为:area@290, 重复个数300
重复值为:area@97, 重复个数21405
重复值为:area@170, 重复个数37696
重复值为:area@291, 重复个数212
重复值为:area@96, 重复个数12442
重复值为:area@99, 重复个数2526
重复值为:area@98, 重复个数17456
重复值为:area@298, 重复个数12688
重复值为:area@177, 重复个数17285
重复值为:area@178, 重复个数11511
重复值为:area@299, 重复个数6622
重复值为:area@175, 重复个数9573
重复值为:area@296, 重复个数2416
重复值为:area@176, 重复个数8109
重复值为:area@297, 重复个数27915
重复值为:area@173, 重复个数58942
重复值为:area@294, 重复个数18842
重复值为:area@295, 重复个数3482
重复值为:area@174, 重复个数31452
重复值为:area@292, 重复个数11436
重复值为:area@171, 重复个数656
重复值为:area@172, 重复个数31557
重复值为:area@293, 重复个数1726
重复值为:type@1, 重复个数288479
重复值为:type@0, 重复个数21067365