27.hadoop系列之50G数据清洗入库秒查询实践

1. 50G数据清洗入库秒查询实践

1.1 项目背景

目前本地有50G的企业年报csv数据, 需要清洗出通信地址，并需要与原有的亿条数据合并以供业务查询最新的企业通信地址

1.2 技术选型

Hadoop + ClickHouse

1.3 Hadoop数据清洗

我们50G的数据无须上传至集群处理，上传目前带宽2M/S, 巨慢，我直接在本地hadoop处理

我们先看下数据格式，以@_@分割，最后一列是杂乱的数据

315@_@102878404@_@91430802MA4PPBWA9Y@_@3@_@2021-03-19 15:29:05@_@2021-03-19 15:29:04@_@-@_@2019@_@<tr> <!--180 285 145--> <td>统一社会信用代码/注册号</td> <td>91430802MA4PPBWA9Y</td> <td>企业名称</td> <td>张家界恒晟广告传媒有限公司</td></tr><tr> <td>企业联系电话</td> <td>15874401535</td> <td>邮政编码</td> <td>427000</td></tr><tr> <td>企业经营状态</td> <td>开业</td> <td>从业人数</td> <td>1人</td></tr><tr> <td>电子邮箱</td> <td>-</td> <td>是否有网站或网店</td> <td>否</td></tr><tr> <td>企业通信地址</td> <td>湖南省张家界市永定区大庸桥办事处大庸桥居委会月亮湾小区金月阁5601号</td> <td>企业是否有投资信息<br>或购买其他公司股权</td> <td>否</td></tr><tr> <td>资产总额</td> <td>企业选择不公示</td> <td>所有者权益合计</td> <td>企业选择不公示</td></tr><tr> <td>销售总额</td> <td>企业选择不公示</td> <td>利润总额</td> <td>企业选择不公示</td></tr><tr> <td>营业总收入中主营业务收入</td> <td>企业选择不公示</td> <td>净利润</td> <td>企业选择不公示</td></tr><tr> <td>纳税总额</td>

public class Company implements Tool {

    private Configuration conf;

    @Override
    public int run(String[] args) throws Exception {
        Configuration conf = new Configuration();
        Job job = Job.getInstance(conf, "company");
        job.setJarByClass(CompanyDriver.class);
        job.setMapperClass(CompanyMapper.class);
        job.setReducerClass(CompanyReducer.class);
        job.setMapOutputKeyClass(Text.class);
        job.setMapOutputValueClass(NullWritable.class);
        job.setOutputKeyClass(Text.class);
        job.setOutputValueClass(NullWritable.class);
        job.setNumReduceTasks(1);
        FileInputFormat.addInputPath(job, new Path(args[0]));
        FileOutputFormat.setOutputPath(job, new Path(args[1]));
        return job.waitForCompletion(true) ? 0 : 1;
    }

    @Override
    public void setConf(Configuration conf) {
        this.conf = conf;
    }

    @Override
    public Configuration getConf() {
        return conf;
    }

    public static class CompanyMapper extends Mapper<LongWritable, Text, Text, NullWritable> {
        private Text keyOut = new Text();
        private Text valueOut = new Text();

        @Override
        protected void map(LongWritable key, Text value, Context context) throws IOException, InterruptedException {
            String line = value.toString();
            String[] words = line.split("@_@");
            keyOut.set(key.toString());
            String company_id = words[1];
            String unified_code = words[2];
            String year = words[7];
            String company = StringUtils.substringBetween(words[8], "<td>统一社会信用代码/注册号</td> <td>", "</td> <td>企业名称</td>")
                    .replaceAll("\"", "");
            String mailAddress = StringUtils.substringBetween(words[8], "<td>企业通信地址</td> <td>", "</td> <td>企业是否有投资信息")
                    .replaceAll("\"", "");
            if (!company.contains("td") && !mailAddress.contains("td")) {
                valueOut.set(key.toString() + '@' + company_id + '@' + unified_code + '@' + year + '@' + company + '@' + mailAddress);
                context.write(valueOut, NullWritable.get());
            }
        }
    }

    public static class CompanyReducer extends Reducer<Text, NullWritable, Text, NullWritable> {
        @Override
        protected void reduce(Text key, Iterable<NullWritable> values, Reducer<Text, NullWritable, Text, NullWritable>.Context context) throws IOException, InterruptedException {
            // 防止相同数据丢失
            for (NullWritable value : values) {
                context.write(key, NullWritable.get());
            }
        }
    }
}

public class CompanyDriver {
    private static Tool tool;

    public static void main(String[] args) throws Exception {
        // 1. 创建配置文件
        Configuration conf = new Configuration();

        // 2. 判断是否有 tool 接口
        switch (args[0]) {
            case "company":
                tool = new Company();
                break;
            default:
                throw new RuntimeException(" No such tool: " + args[0]);
        }
        // 3. 用 Tool 执行程序
        // Arrays.copyOfRange 将老数组的元素放到新数组里面
        int run = ToolRunner.run(conf, tool, Arrays.copyOfRange(args, 1, args.length));
        System.exit(run);
    }
}

参数传递运行与先前文章一致，25.hadoop系列之Yarn Tool接口实现动态传参 不在重复，10分钟左右处理完毕,处理后约1.8G

1.4 ClickHouse ReplaceMergeTree实践

现在我们将处理后数据导入ClickHouse

1.4.1 创建表company_report及导入处理后的part-r-00000文件

CREATE TABLE etl.company_report (
    id      String,
    company_id      String,
    unified_code      String,
    year  String,
    company    String,
    mail_address   String
) ENGINE MergeTree()
PARTITION BY substring(unified_code, 2, 2) PRIMARY KEY (id) ORDER BY (id);

clickhouse-client --format_csv_delimiter="@" --input_format_with_names_use_header=0 --query="INSERT INTO etl.company_report FORMAT CSV" --host=192.168.0.222 --password=shenjian < part-r-00000

1.4.2 关联插入dwd_company表

在左连接的子查询中，我们取当前企业最新的年报中的通信地址,如下图所示

# 关联导入，可能DataGrip客户端超时，就在ClickHouse-Client命令行运行即可
INSERT INTO etl.dwd_company(district, ent_name, reg_addr, unified_code, authority, region_code, reg_addr1, province_code, city_code, province_name, city_name, region_name, mail_address)
SELECT district, ent_name, reg_addr, unified_code, authority, region_code, reg_addr1, province_code, city_code, province_name, city_name, region_name, cr.mail_address
FROM etl.dwd_company c
LEFT JOIN (
    SELECT unified_code, argMax(mail_address, year) mail_address, argMax(year, year) new_year FROM etl.company_report GROUP BY unified_code
)  cr ON c.unified_code=cr.unified_code
WHERE cr.mail_address!='' and cr.mail_address is not null;

这插入速度还行吧,插入后，存在两条记录，对于ReplaceMergeTree来说，无妨，看过之前文章的你应该很熟悉为啥了吧

1.4.3 清洗企业通信地址

新建字段mail_address1，剔除省市区前缀信息，列式存储，全量更新很快，请不要单条那种更新

ALTER TABLE etl.dwd_company update mail_address1=replaceRegexpAll(mail_address, '^(.{2,}(省|自治区))?(.{2,}市)?(.{2,}(区|县))?', '') WHERE 1=1

1.4.4 手动执行分区合并

如果线上对ClickHouse服务稳定性要求极高不建议这样操作，可能影响服务，可以参考9.ClickHouse系列之数据一致性保证

optimize table etl.dwd_company final;

后面可以将dwd_company中所需字段数据导入数据中间层dwm_company，略

2. 调优实践

2.1 需求分析概括

需求：从1G数据中，统计每个单词出现次数。服务器3台，每台配置4G内存，4核CPU，4线程
分析：1G/128m=8个MapTask；1个ReduceTask；1个MRAppMaster。平均每个节点运行10个/3台≈3个任务（4 3 3）

以下参数我就不进行挂载说明了，仅仅说明容器中真实路径情况,均在路径/opt/hadoop-3.2.1/etc/hadoop/下文件中配置

2.2 HDFS参数调优

在hadoop-env.sh中修改配置：

export HDFS_NAMENODE_OPTS="-Dhadoop.security.logger=INFO,RFAS -Xmx1024m"
export HDFS_DATANODE_OPTS="-Dhadoop.security.logger=ERROR,RFAS -Xmx1024m"

在hdfs-site.xml中修改配置:

<!-- NameNode 有一个工作线程池，默认值是 10 -->
<property>
 <name>dfs.namenode.handler.count</name>
 <value>21</value>
</property>

企业经验：dfs.namenode.handler.count=20 × 𝑙𝑜𝑔𝑒𝐶𝑙𝑢𝑠𝑡𝑒𝑟 𝑆𝑖𝑧𝑒，比如集群规模（DataNode 台数）为 3 台时，此参数设置为 21。可通过python计算

import math
int(20*math.log(3))

在core-site.xml中修改配置

<!-- 配置垃圾回收时间为 60 分钟 -->
<property>
 <name>fs.trash.interval</name>
 <value>60</value>
</property>

2.3 MapReduce参数调优

在mapred-site.xml中修改配置

<!-- 环形缓冲区大小，默认100m -->
<property>
 <name>mapreduce.task.io.sort.mb</name>
 <value>100</value>
</property>
<!-- 环形缓冲区溢写阈值，默认0.8 -->
<property>
 <name>mapreduce.map.sort.spill.percent</name>
 <value>0.80</value>
</property>
<!-- merge合并文件数最大值，默认10个 -->
<property>
 <name>mapreduce.task.io.sort.factor</name>
 <value>10</value>
</property>
<!-- maptask内存，默认1g；maptask堆内存大小默认和该值大小一致mapreduce.map.java.opts -->
<property>
 <name>mapreduce.map.memory.mb</name>
 <value>-1</value>
</property>
<!-- maptask的CPU核数，默认1个 -->
<property>
 <name>mapreduce.map.cpu.vcores</name>
 <value>1</value>
</property>
<!-- maptask异常重试次数，默认4次 -->
<property>
 <name>mapreduce.map.maxattempts</name>
 <value>4</value>
</property>
<!-- 每个Reduce去Map中拉取数据的并行数。默认值是5 -->
<property>
 <name>mapreduce.reduce.shuffle.parallelcopies</name>
 <value>5</value>
</property>
<!-- Buffer大小占Reduce可用内存的比例，默认值0.7 -->
<property>
 <name>mapreduce.reduce.shuffle.input.buffer.percent</name>
 <value>0.70</value>
</property>
<!-- Buffer中的数据达到多少比例开始写入磁盘，默认值0.66 -->
<property>
 <name>mapreduce.reduce.shuffle.merge.percent</name>
 <value>0.66</value>
</property>
<!-- ReduceTask内存，默认1g；ReduceTask堆内存大小默认和该值大小一致mapreduce.reduce.java.opts -->
<property>
 <name>mapreduce.reduce.memory.mb</name>
 <value>-1</value>
</property>
<!-- ReduceTask的CPU核数，默认1个 -->
<property>
 <name>mapreduce.reduce.cpu.vcores</name>
 <value>2</value>
</property>
<!-- ReduceTask失败重试次数，默认4次 -->
<property>
 <name>mapreduce.reduce.maxattempts</name>
 <value>4</value>
</property>
<!-- 当MapTask完成的比例达到该值后才会为ReduceTask申请资源。默认是0.05 -->
<property>
 <name>mapreduce.job.reduce.slowstart.completedmaps</name>
 <value>0.05</value>
</property>
<!-- 如果程序在规定的默认10分钟内没有读到数据，将强制超时退出 -->
<property>
 <name>mapreduce.task.timeout</name>
 <value>600000</value>
</property>

2.4 Yarn参数调优

在yarn-site.xml中修改配置

<!-- 选择调度器 -->
<property>
  <name>yarn.resourcemanager.scheduler.class</name>
  <value>org.apache.hadoop.yarn.server.resourcemanager.scheduler.capaci
ty.CapacityScheduler</value>
</property>
<!-- ResourceManager处理调度器请求的线程数量,默认50；如果提交的任务数大于50，可以
增加该值，但是不能超过3台 * 4线程=12线程（去除其他应用程序实际不能超过 8） -->
<property>
  <name>yarn.resourcemanager.scheduler.client.thread-count</name>
  <value>8</value>
</property>
<!-- 是否让Yarn自动检测硬件进行配置，默认是false，如果该节点有很多其他应用程序，建议
手动配置。如果该节点没有其他应用程序，可以采用自动 -->
<property>
  <name>yarn.nodemanager.resource.detect-hardware-capabilities</name>
  <value>false</value>
</property>
<!-- 是否将虚拟核数当作CPU核数，默认是false，采用物理CPU核数 -->
<property>
  <name>yarn.nodemanager.resource.count-logical-processors-ascores</name>
  <value>false</value>
</property>
<!-- 虚拟核数和物理核数乘数，默认是1.0 -->
<property>
  <name>yarn.nodemanager.resource.pcores-vcores-multiplier</name>
  <value>1.0</value>
</property>
<!-- NodeManager使用内存数，默认8G，修改为4G内存 -->
<property>
  <name>yarn.nodemanager.resource.memory-mb</name>
  <value>4096</value>
</property>
<!-- nodemanager的CPU核数，不按照硬件环境自动设定时默认是8个，修改为4个 -->
<property>
  <name>yarn.nodemanager.resource.cpu-vcores</name>
  <value>4</value>
</property>
<!-- 容器最小内存，默认1G -->
<property>
  <name>yarn.scheduler.minimum-allocation-mb</name>
  <value>1024</value>
</property>
<!-- 容器最大内存，默认8G，修改为2G -->
<property>
  <name>yarn.scheduler.maximum-allocation-mb</name>
  <value>2048</value>
</property>
<!-- 容器最小CPU核数，默认1个 -->
<property>
  <name>yarn.scheduler.minimum-allocation-vcores</name>
  <value>1</value>
</property>
<!-- 容器最大CPU核数，默认4个，修改为2个 -->
<property>
  <name>yarn.scheduler.maximum-allocation-vcores</name>
  <value>2</value>
</property>
<!-- 虚拟内存检查，默认打开，修改为关闭 -->
<property>
  <name>yarn.nodemanager.vmem-check-enabled</name>
  <value>false</value>
</property>
<!-- 虚拟内存和物理内存设置比例,默认 2.1 -->
<property>
  <name>yarn.nodemanager.vmem-pmem-ratio</name>
  <value>2.1</value>
</property>

欢迎关注公众号算法小生获取更多最新内容

欢迎关注公众号算法小生与我沟通交流