Exception in thread “main” org.apache.spark.sql.AnalysisException: Resolved attribute(s) name#36 missing from dotName#155,mobile#121,cityId#190L,dotTel#157,state#198L,email#60,cid#2L,name#64,companyAddrGis#192,type#13,remark#180,id#150L,id#176L,companyTel#195,udt#125,state#161L,id#58L,companyNumber#194,id#104L,day#1670,remark#197,cdt#59,remark#160,orderChannelId#6L,OrderTerminalTypeCode#212,remark#67,type#70,udt#141,tel#69,id#188L,cdt#118,cdt#177,pwBill#138,remark#140,dotGisAddr#154,isCollectPackageTimeout#5,cdt#151,OrderChannelTypeCode#216,addressId#105L,udt#181,detailAddr#119,reserveDt#11,companyId#178L,mobile#63,regDt#66,pwDotId#139L,consumerId#107L,cdt#137,remark#123,udt#162,udt#109,companyName#193,companyAddr#191,remark#108,udt#14,dotAddr#153,warehouseId#142L,eid#3L,name#122,remark#10,orderTerminalOsType#8,id#0L,id#116L,gisAddr#120,areaId#117L,dotNumber#156,timeoutDt#12,udt#199,isSubCompany#196L,companyId#152L,udt#71,cdt#189,id#136L,expressNumber#4,lastLoginDt#62,orderTerminalType#9,tel#124,dotId#179L,OrderTerminalTypeName#213,cdt#106,orderDt#7,isOwnReg#61,OrderChannelTypeName#217,manageAreaGis#158,cdt#1,manageAreaId#159L,state#68,regChannelId#65 in operator !Project [id#0L, expressNumber#4 AS expressNumber#1759, cid#2L, name#64 AS cname#1760, detailAddr#119 AS caddress#1761, eid#3L, name#36 AS ename#1762, id#150L AS dot_id#1763L, dotName#155 AS dot_name#1764, companyName#193 AS company_name#1765, orderChannelId#6L AS order_channel_id#1766L, OrderChannelTypeName#217 AS order_channel_name#1767, orderDt#7 AS order_dt#1768, OrderTerminalTypeCode#212 AS order_terminal_type#1769, OrderTerminalTypeName#213 AS order_terminal_type_name#1770, orderTerminalOsType#8 AS order_terminal_os_type#1771, reserveDt#11 AS reserve_dt#1772, isCollectPackageTimeout#5 AS is_collect_package_timeout#1773, timeoutDt#12 AS timeout_dt#1774, type#70, cdt#1, udt#14, remark#10, day#1670]. Attribute(s) with the same name appear in the operation: name. Please check if the right attribute(s) are used.;;
!Project [id#0L, expressNumber#4 AS expressNumber#1759, cid#2L, name#64 AS cname#1760, detailAddr#119 AS caddress#1761, eid#3L, name#36 AS ename#1762, id#150L AS dot_id#1763L, dotName#155 AS dot_name#1764, companyName#193 AS company_name#1765, orderChannelId#6L AS order_channel_id#1766L, OrderChannelTypeName#217 AS order_channel_name#1767, orderDt#7 AS order_dt#1768, OrderTerminalTypeCode#212 AS order_terminal_type#1769, OrderTerminalTypeName#213 AS order_terminal_type_name#1770, orderTerminalOsType#8 AS order_terminal_os_type#1771, reserveDt#11 AS reserve_dt#1772, isCollectPackageTimeout#5 AS is_collect_package_timeout#1773, timeoutDt#12 AS timeout_dt#1774, type#70, cdt#1, udt#14, remark#10, day#1670]
± Sort [cdt#1 ASC NULLS FIRST], true
± Project [id#0L, cdt#1, cid#2L, eid#3L, expressNumber#4, isCollectPackageTimeout#5, orderChannelId#6L, orderDt#7, orderTerminalOsType#8, orderTerminalType#9, remark#10, reserveDt#11, timeoutDt#12, type#13, udt#14, id#58L, cdt#59, email#60, isOwnReg#61, lastLoginDt#62, mobile#63, name#64, regChannelId#65, regDt#66, … 64 more fields]
± Join LeftOuter, (id#188L = companyId#178L)
:- Join LeftOuter, (dotId#179L = id#150L)
: :- Join LeftOuter, (id#150L = pwDotId#139L)
: : :- Join LeftOuter, (pwBill#138 = expressNumber#4)
: : : :- Join LeftOuter, (id#116L = addressId#105L)
: : : : :- Join LeftOuter, (consumerId#107L = id#58L)
: : : : : :- Join LeftOuter, (cast(OrderTerminalTypeCode#212 as int) = orderTerminalType#9)
: : : : : : :- Join LeftOuter, (cast(OrderChannelTypeCode#216 as bigint) = orderChannelId#6L)
: : : : : : : :- Join LeftOuter, (cid#2L = id#58L)
: : : : : : : : :- Relation[id#0L,cdt#1,cid#2L,eid#3L,expressNumber#4,isCollectPackageTimeout#5,orderChannelId#6L,orderDt#7,orderTerminalOsType#8,orderTerminalType#9,remark#10,reserveDt#11,timeoutDt#12,type#13,udt#14] org.apache.kudu.spark.kudu.KuduRelation@55061418
: : : : : : : : ± Relation[id#58L,cdt#59,email#60,isOwnReg#61,lastLoginDt#62,mobile#63,name#64,regChannelId#65,regDt#66,remark#67,state#68,tel#69,type#70,udt#71] org.apache.kudu.spark.kudu.KuduRelation@2b400bd0
: : : : : : : ± Project [code#88 AS OrderChannelTypeCode#216, codeDesc#89 AS OrderChannelTypeName#217]
: : : : : : : ± Filter (type#93 = 18)
: : : : : : : ± Relation[id#86L,cdt#87,code#88,codeDesc#89,codeType#90,name#91,state#92L,type#93,udt#94] org.apache.kudu.spark.kudu.KuduRelation@fceab5d
: : : : : : ± Project [code#88 AS OrderTerminalTypeCode#212, codeDesc#89 AS OrderTerminalTypeName#213]
: : : : : : ± Filter (type#93 = 17)
: : : : : : ± Relation[id#86L,cdt#87,code#88,codeDesc#89,codeType#90,name#91,state#92L,type#93,udt#94] org.apache.kudu.spark.kudu.KuduRelation@fceab5d
: : : : : ± Relation[id#104L,addressId#105L,cdt#106,consumerId#107L,remark#108,udt#109] org.apache.kudu.spark.kudu.KuduRelation@79445efb
: : : : ± Relation[id#116L,areaId#117L,cdt#118,detailAddr#119,gisAddr#120,mobile#121,name#122,remark#123,tel#124,udt#125] org.apache.kudu.spark.kudu.KuduRelation@1592e540
: : : ± Relation[id#136L,cdt#137,pwBill#138,pwDotId#139L,remark#140,udt#141,warehouseId#142L] org.apache.kudu.spark.kudu.KuduRelation@3e2772a9
: : ± Relation[id#150L,cdt#151,companyId#152L,dotAddr#153,dotGisAddr#154,dotName#155,dotNumber#156,dotTel#157,manageAreaGis#158,manageAreaId#159L,remark#160,state#161L,udt#162] org.apache.kudu.spark.kudu.KuduRelation@4449b273
: ± Relation[id#176L,cdt#177,companyId#178L,dotId#179L,remark#180,udt#181] org.apache.kudu.spark.kudu.KuduRelation@1fa18f87
± Relation[id#188L,cdt#189,cityId#190L,companyAddr#191,companyAddrGis#192,companyName#193,companyNumber#194,companyTel#195,isSubCompany#196L,remark#197,state#198L,udt#199] org.apache.kudu.spark.kudu.KuduRelation@1d9bd1d6
at org.apache.spark.sql.catalyst.analysis.CheckAnalysis$class.failAnalysis(CheckAnalysis.scala:42)
at org.apache.spark.sql.catalyst.analysis.Analyzer.failAnalysis(Analyzer.scala:95)
at org.apache.spark.sql.catalyst.analysis.CheckAnalysis$$anonfun$checkAnalysis$1.apply(CheckAnalysis.scala:326)
at org.apache.spark.sql.catalyst.analysis.CheckAnalysis$$anonfun$checkAnalysis$1.apply(CheckAnalysis.scala:85)
at org.apache.spark.sql.catalyst.trees.TreeNode.foreachUp(TreeNode.scala:127)
at org.apache.spark.sql.catalyst.analysis.CheckAnalysis$class.checkAnalysis(CheckAnalysis.scala:85)
at org.apache.spark.sql.catalyst.analysis.Analyzer.checkAnalysis(Analyzer.scala:95)
at org.apache.spark.sql.catalyst.analysis.Analyzer$$anonfun$executeAndCheck$1.apply(Analyzer.scala:108)
at org.apache.spark.sql.catalyst.analysis.Analyzer$$anonfun$executeAndCheck$1.apply(Analyzer.scala:105)
at org.apache.spark.sql.catalyst.plans.logical.AnalysisHelper$.markInAnalyzer(AnalysisHelper.scala:201)
at org.apache.spark.sql.catalyst.analysis.Analyzer.executeAndCheck(Analyzer.scala:105)
at org.apache.spark.sql.execution.QueryExecution.analyzed$lzycompute(QueryExecution.scala:57)
at org.apache.spark.sql.execution.QueryExecution.analyzed(QueryExecution.scala:55)
at org.apache.spark.sql.execution.QueryExecution.assertAnalyzed(QueryExecution.scala:47)
at org.apache.spark.sql.Dataset$.ofRows(Dataset.scala:79)
at org.apache.spark.sql.Dataset.org$apache$spark$sql$Dataset$$withPlan(Dataset.scala:3407)
at org.apache.spark.sql.Dataset.select(Dataset.scala:1335)
at cn.itcast.logistics.offline.dwd.ExpressBillDWD$.process(ExpressBillDWD.scala:93)
at cn.itcast.logistics.offline.dwd.ExpressBillDWD$.main(ExpressBillDWD.scala:133)
at cn.itcast.logistics.offline.dwd.ExpressBillDWD.main(ExpressBillDWD.scala)
问题解决:多了注释//11111111//下的代码
package cn.itcast.logistics.offline.dwd
import cn.itcast.logistics.common.{CodeTypeMapping, Configuration, OfflineTableDefine, SparkUtils, TableMapping}
import cn.itcast.logistics.offline.BasicOfflineApp
import org.apache.spark.SparkConf
import org.apache.spark.sql.{DataFrame, SparkSession}
import org.apache.spark.sql.functions._
object ExpressBillDWD extends BasicOfflineApp{
override def process(dataFrame: DataFrame): DataFrame = {
//先获取关联的维度表的数据(也就是dataFrame对应的表)
//获取sparksession
val spark: SparkSession = dataFrame.sparkSession
import spark.implicits._
//load(spark:SparkSession,tableName:String,isLoadFullData: Boolean = false):DataFrame=
//获取不同的维度表数据
val courierDF: DataFrame = load(spark, TableMapping.COURIER, isLoadFullData = true)
// 1.2：加载客户维度表的数据
val customerDF: DataFrame = load(spark, TableMapping.CUSTOMER, isLoadFullData = true)
// 1.3：加载物流码表的数据
val codesDF: DataFrame = load(spark, TableMapping.CODES, isLoadFullData = true)
// 1.4：客户地址关联表的数据
val addressMapDF: DataFrame = load(spark, TableMapping.CONSUMER_ADDRESS_MAP, isLoadFullData = true)
// 1.5：加载地址表的数据
val addressDF: DataFrame = load(spark, TableMapping.ADDRESS, isLoadFullData = true)
// 1.6：加载包裹表的数据
val pkgDF: DataFrame = load(spark, TableMapping.PKG, isLoadFullData = true)
// 1.7：加载网点表的数据
val dotDF: DataFrame = load(spark, TableMapping.DOT, isLoadFullData = true)
// 1.8：加载公司网点表的数据
val companyDotMapDF: DataFrame = load(spark, TableMapping.COMPANY_DOT_MAP, isLoadFullData = true)
// 1.9：加载公司表的数据
val companyDF: DataFrame = load(spark, TableMapping.COMPANY, isLoadFullData = true)
// 1.10：获取终端类型码表数据
//import spark.implicits._
val orderTerminalTypeDF: DataFrame = codesDF
.where($"type" === CodeTypeMapping.ORDER_TERMINAL_TYPE)
.select(
$"code".as("OrderTerminalTypeCode"),
$"codeDesc".as("OrderTerminalTypeName")
)
// 1.11：获取下单渠道类型码表数据
val orderChannelTypeDF: DataFrame = codesDF
.where($"type" === CodeTypeMapping.ORDER_CHANNEL_TYPE)
.select(
$"code".as("OrderChannelTypeCode"),
$"codeDesc".as("OrderChannelTypeName")
)
//对表进行关联,选取需要的字段
// 2. 将事实表与维度进行关联：leftJoin，左外连接
// join(right: Dataset[_], usingColumns: Seq[String], joinType: String): DataFrame = {
//设置需要拉宽的表的额名称
val expressBillDF=dataFrame
val joinType: String ="left_outer"
expressBillDF.show()
println("expressBillDF"+":"+expressBillDF)
println("expressBillDF(\"eid\")"+expressBillDF("eid"))
val joinDF: DataFrame = expressBillDF
//与维度表进行左外关联
// 快递单表与客户表进行关联
.join(customerDF, expressBillDF("cid") === customerDF("id"), joinType)
// 下单渠道表与快递单表关联
.join(
orderChannelTypeDF,
orderChannelTypeDF("OrderChannelTypeCode") === expressBillDF("orderChannelId"),
joinType
)
// 终端类型表与快递单表关联
.join(
orderTerminalTypeDF,
orderTerminalTypeDF("OrderTerminalTypeCode") === expressBillDF("orderTerminalType"),
joinType
)
// 客户地址关联表与客户表关联
.join(addressMapDF, addressMapDF("consumerId") === customerDF("id"), joinType)
// 地址表与客户地址关联表关联
.join(addressDF, addressDF("id") === addressMapDF("addressId"), joinType)
// 包裹表与快递单表关联
.join(pkgDF, pkgDF("pwBill") === expressBillDF("expressNumber"), joinType)
// 网点表与包裹表关联
.join(dotDF, dotDF("id") === pkgDF("pwDotId"), joinType)
// 公司网点关联表与网点表关联
.join(companyDotMapDF, companyDotMapDF("dotId") === dotDF("id"), joinType)
// 公司网点关联表与公司表关联
.join(companyDF, companyDF("id") === companyDotMapDF("companyId"), joinType)
// .withColumn("time", date_format(date_sub(current_timestamp(), 1), "YYYY-MM-dd"))
// .withColumn("day",date_sub(current_date(),1))
.withColumn("day",date_format(expressBillDF("cdt"),"yyyy-MM-dd"))
//获取关联表中的字段
val expressBillDetailDF: DataFrame = joinDF.sort(expressBillDF("cdt").asc)
.select(
expressBillDF("id"),
expressBillDF("expressNumber").as("expressNumber"),
expressBillDF("cid"), //客户id
customerDF("name").as("cname"), //客户名称
addressDF("detailAddr").as("caddress"), //客户地址
expressBillDF("eid"), //员工id
courierDF("name").as("ename"), //员工名称
dotDF("id").as("dot_id"), //网点id
dotDF("dotName").as("dot_name"), //网点名称
companyDF("companyName").as("company_name"), //公司名称
expressBillDF("orderChannelId").as("order_channel_id"), //下单渠道id
orderChannelTypeDF("OrderChannelTypeName").as("order_channel_name"), //下单渠道id
expressBillDF("orderDt").as("order_dt"), //下单时间
orderTerminalTypeDF("OrderTerminalTypeCode").as("order_terminal_type"), //下单设备类型id
orderTerminalTypeDF("OrderTerminalTypeName").as("order_terminal_type_name"), //下单设备类型id
expressBillDF("orderTerminalOsType").as("order_terminal_os_type"), //下单设备操作系统
expressBillDF("reserveDt").as("reserve_dt"), //预约取件时间
expressBillDF("isCollectPackageTimeout").as("is_collect_package_timeout"), //是否取件超时
expressBillDF("timeoutDt").as("timeout_dt"), //超时时间
customerDF("type"), //客户类型
expressBillDF("cdt"), //创建时间
expressBillDF("udt"), //修改时间
expressBillDF("remark") ,//备注
$"day"
)
// 添加字段：day，
//11111111//.withColumn("time", date_format(date_sub(current_timestamp(), 1), "YYYY-MM-dd"))
expressBillDetailDF.show(10, truncate = false)
expressBillDetailDF
}
def main(args: Array[String]): Unit = {
//加载一个表
val spark: SparkSession = SparkUtils.createSparkSession(SparkUtils.autoSettingEnv(SparkUtils.sparkConf()), this.getClass)
//import spark.implicits._
//设置日志级别
spark.sparkContext.setLogLevel(Configuration.LOG_OFF)
val expressBillDF: DataFrame = load(spark, TableMapping.EXPRESS_BILL, isLoadFullData = Configuration.IS_FIRST_RUNNABLE)
//在proess中实现这个表的拉宽操作
val expressBillDetailDF: DataFrame = process(expressBillDF)
//将数据保存
save(expressBillDetailDF,OfflineTableDefine.EXPRESS_BILL_DETAIL)
//关闭资源
spark.stop()
}
}
扫一扫
6304
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
