开端：
今天咱先说问题，经过几天测试题的练习，我们有从某题库中找到了新题型，并且成功把我们干趴下，昨天今天就干了一件事，站起来。
沙问题？
java mapeduce 清洗 hive 中的数据 ，清晰之后将driver代码 进行截图提交。
坑号1: spark之前抽取的数据是.parquet格式的， 对 mapreduce 不太友好，我决定从新抽取， 还是用spark技术，换一种文件格式
坑号2: 使用新方法进行sink的时候我是直接like别的现成表结构折磨干的，后来hive分割字段都TM乱套啦，赞看看！
需求：
1.使用scala+spark技术实现抽取mysql到Hive中
2.使用java+ Mapeduce 技术实现清洗Hive数据
问题产生：
- Mapeduce 无法 正常读取Hive数据
- Mapeduce 无法 正常将结果sink到Hive中
解决路线：首先从spark入手
为了解决spark写入hive后文件格式为 .parquet 问题
首先我们需要创建一个表，至于为什么不用自动建表,是因为自动建表 spark使用的是.parquet文件格式存储的
hive> CREATE TABLE `ods.region2`( > `regionkey` string, > `name` string, > `comment` string) > PARTITIONED BY ( > `etldate` string > ) > row format delimited > fields terminated by '|' ; OK Time taken: 0.055 seconds
spark sink hive 部分代码
spark.sql("select *,'20220616' as etldate from data ") .write .partitionBy("etldate") .mode(saveMode = SaveMode.Overwrite) .format("hive") .option("delimiter","|") .insertInto("ods.region2")
重点是这两条
.format("hive")
.insertInto("ods.region2")
我们看一下写好的数据
hdfs dfs -cat /user/hive/warehouse/ods.db/region2/etldate=20220616/*
3|EUROPE|ly final courts cajole furiously final excuse 4|MIDDLE EAST|uickly special accounts cajole carefully blithely close requests. carefully final asymptotes haggle furiousl 0|AFRICA|lar deposits. blithely final packages cajole. regular waters are final requests. regular accounts are according to 1|AMERICA|hs use ironic, even requests. s 2|ASIA|ges. thinly even pinto beans ca
可以正常编写和运行java mapReduce 代码啦
代码不再一一贴出，放一个driver把
<groupId>org.li</groupId> <artifactId>mapreduce_06-21</artifactId> <version>1.0</version> <properties> <maven.compiler.source>8</maven.compiler.source> <maven.compiler.target>8</maven.compiler.target> <parquet.version>1.8.1</parquet.version> <!-- JDateTime 依赖 --> <jodd.version>3.3.8</jodd.version> </properties> <dependencies> <!-- https://mvnrepository.com/artifact/org.apache.hadoop/hadoop-common --> <dependency> <groupId>org.apache.hadoop</groupId> <artifactId>hadoop-common</artifactId> <version>2.7.6</version> </dependency> <!-- https://mvnrepository.com/artifact/org.apache.hadoop/hadoop-client --> <dependency> <groupId>org.apache.hadoop</groupId> <artifactId>hadoop-client</artifactId> <version>2.7.6</version> </dependency> <!-- https://mvnrepository.com/artifact/org.apache.hadoop/hadoop-hdfs --> <dependency> <groupId>org.apache.hadoop</groupId> <artifactId>hadoop-hdfs</artifactId> <version>2.7.6</version> </dependency> <!-- parquet-hadoop --> <dependency> <groupId>org.apache.parquet</groupId> <artifactId>parquet-hadoop</artifactId> <version>${parquet.version}</version> </dependency> <!-- jodd --> <dependency> <groupId>org.jodd</groupId> <artifactId>jodd</artifactId> <version>${jodd.version}</version> </dependency> </dependencies>
package com.li.mapreduce; import org.apache.hadoop.conf.Configuration; import org.apache.hadoop.fs.FileSystem; import org.apache.hadoop.fs.Path; import org.apache.hadoop.io.IntWritable; import org.apache.hadoop.io.LongWritable; import org.apache.hadoop.io.NullWritable; import org.apache.hadoop.io.Text; import org.apache.hadoop.mapreduce.Job; import org.apache.hadoop.mapreduce.lib.db.DBInputFormat; import org.apache.hadoop.mapreduce.lib.input.FileInputFormat; import org.apache.hadoop.mapreduce.lib.output.FileOutputFormat; import org.apache.parquet.hadoop.ParquetInputFormat; //import org.apache.parquet.hadoop.ParquetInputFormat; import java.io.File; import java.io.IOException; import java.net.URI; import java.net.URISyntaxException; public class HiveDriver{ public static void main(String[] args) throws IOException, InterruptedException, ClassNotFoundException, URISyntaxException { System.setProperty("HADOOP_USER_NAME","root"); System.out.println("删除本地目录" + new File("/home/rjxy/output").delete()); Configuration configuration = new Configuration(); configuration.set("dfs.client.use.datanode.hostname","true"); //hadoop配值文件 //获取i工作势力 Job instance = Job.getInstance(configuration); //关联driver instance.setJarByClass(HiveDriver.class); //关联mapper reduce instance.setMapperClass(HiveMapper.class); instance.setReducerClass(HiveReduce.class); //设置map输出的kv类型 instance.setMapOutputKeyClass(LongWritable.class); instance.setMapOutputValueClass(Text.class); //设置最终的输入输出类型 instance.setOutputKeyClass(NullWritable.class); instance.setOutputValueClass(Text.class); //Parquet // instance.setInputFormatClass(); // instance.setInputFormatClass(ParquetInputFormat.class); //设置输入输出路径 FileInputFormat.setInputPaths(instance,new Path("hdfs://master:9000/user/hive/warehouse/ods.db/" + "region2" + "/*/*")); Path outputDir = new Path("hdfs://master:9000/test4"); // Path outputDir = new Path("/home/rjxy/output"); FileOutputFormat.setOutputPath(instance, outputDir); //7 提交job boolean result = instance.waitForCompletion(true); System.exit(result?0:1); } }
这些代码就包含啦我的resource 数据信息 sink 位置
现在看一下怎么将hdfs数据进行load进hive表中
先建好表
hive> CREATE TABLE `ods.region2`( > `regionkey` string, > `name` string, > `comment` string) > PARTITIONED BY ( > `etldate` string > ) > row format delimited > fields terminated by '|' ; OK Time taken: 0.055 seconds
LOAD DATA INPATH '/test2' INTO TABLE ods.region2 partition(etldate="20220622");
查看hive清洗后的数据
hive (default)> select * from ods.region2 where etldate="20220622"; OK region2.regionkey region2.name region2.comment region2.etldate 3 EUROPE ly final courts cajole furiously final excuse 20220622 3 EUROPE ly final courts cajole furiously final excuse 20220622 4 MIDDLE EAST uickly special accounts cajole carefully blithely close requests. carefully final asymptotes haggle furiousl 20220622 4 MIDDLE EAST uickly special accounts cajole carefully blithely close requests. carefully final asymptotes haggle furiousl 20220622 0 AFRICA lar deposits. blithely final packages cajole. regular waters are final requests. regular accounts are according to 20220622 0 AFRICA lar deposits. blithely final packages cajole. regular waters are final requests. regular accounts are according to 20220622 1 AMERICA hs use ironic, even requests. s 20220622 1 AMERICA hs use ironic, even requests. s 20220622 2 ASIA ges. thinly even pinto beans ca 20220622 2 ASIA ges. thinly even pinto beans ca 20220622 Time taken: 0.194 seconds, Fetched: 10 row(s)
