有2张csv表(13列),先合并,再对公司规模(size)这一列进行统计
原代码:
val df3 = spark.read .format("csv") .option("delimiter", ",") //分隔符 .option("inferSchema", "true") //自动推断模式 .option("header", "true") //第一行是否为列名 .load("shixun/3nowcoder.csv") val df4 = spark.read .format("csv") .option("delimiter", ",") .option("inferSchema", "true") .option("header", "true") .load("shixun/4nowcoder.csv") //横向拼接多张表 val union_df = df3.union(df4) //选择需要的列 val df = df.select("city", "degree", "workday", "workmonth", "salary", "company", "regular", "financing", "comtag", "size", "label1", "label2", "label3") df.createOrReplaceTempView("datas") //统计不同规模的公司数量 val df_size = spark.sql("select size,count(size) as count from datas group by size order by count(size) DESC") df_size.show()
发现出现了其它不属于公司规模(size)的字段,
这是由于,横向拼接(不管列名有没有对应直接拼接)两张表的时候,由于列的顺序不同,导致数据不对应。应该从先union后select需要的列----->先select需要的列(这时两表的顺序相同)后union
修改后代码:
val df3 = spark.read .format("csv") .option("delimiter", ",") //分隔符 .option("inferSchema", "true") //自动推断模式 .option("header", "true") //第一行是否为列名 .load("shixun/3nowcoder.csv") .select("city", "degree", "workday", "workmonth", "salary", "company", "regular", "financing", "comtag", "size", "label1", "label2", "label3") val df4 = spark.read .format("csv") .option("delimiter", ",") .option("inferSchema", "true") .option("header", "true") .load("shixun/4nowcoder.csv") .select("city", "degree", "workday", "workmonth", "salary", "company", "regular", "financing", "comtag", "size", "label1", "label2", "label3") //横向拼接多张表 val union_df = df3.union(df4) df.createOrReplaceTempView("datas") //统计不同规模的公司数量 val df_size = spark.sql("select size,count(size) as count from datas group by size order by count(size) DESC") df_size.show()
(原本以为是由于表里面含null空值,导致分隔字符不正确,后来填补缺失值后还是错误才想到是拼接时出错。后来查询最开始拼接后的表才发现,发现错误可以从开头查起来,将后面的代码先注释掉,一步步运行查看结果)