Column 'itemId' is not present in table
'success_events.sojsuccessevents1' or 'dw_bid'
did you mean 'sojsuccessevents2_spark' table in your select query ?
Thanks,
Jins
On 01/03/2016 07:22 PM, ÐΞ€ρ@Ҝ (๏̯͡๏) wrote:
Code:
val hiveContext = new org.apache.spark.sql.hive.HiveContext(sc)
hiveContext.sql("drop table sojsuccessevents2_spark")
hiveContext.sql("CREATE TABLE `sojsuccessevents2_spark`( `guid` string
COMMENT 'from deserializer', `sessionkey` bigint COMMENT 'from
deserializer', `sessionstartdate` string COMMENT 'from deserializer',
`sojdatadate` string COMMENT 'from deserializer', `seqnum` int COMMENT
'from deserializer', `eventtimestamp` string COMMENT 'from
deserializer', `siteid` int COMMENT 'from deserializer',
`successeventtype` string COMMENT 'from deserializer', `sourcetype`
string COMMENT 'from deserializer', `itemid` bigint COMMENT 'from
deserializer', `shopcartid` bigint COMMENT 'from deserializer',
`transactionid` bigint COMMENT 'from deserializer', `offerid` bigint
COMMENT 'from deserializer', `userid` bigint COMMENT 'from
deserializer', `priorpage1seqnum` int COMMENT 'from deserializer',
`priorpage1pageid` int COMMENT 'from deserializer',
`exclwmsearchattemptseqnum` int COMMENT 'from deserializer',
`exclpriorsearchpageid` int COMMENT 'from deserializer',
`exclpriorsearchseqnum` int COMMENT 'from deserializer',
`exclpriorsearchcategory` int COMMENT 'from deserializer',
`exclpriorsearchl1` int COMMENT 'from deserializer',
`exclpriorsearchl2` int COMMENT 'from deserializer',
`currentimpressionid` bigint COMMENT 'from deserializer',
`sourceimpressionid` bigint COMMENT 'from deserializer',
`exclpriorsearchsqr` string COMMENT 'from deserializer',
`exclpriorsearchsort` string COMMENT 'from deserializer',
`isduplicate` int COMMENT 'from deserializer', `transactiondate`
string COMMENT 'from deserializer', `auctiontypecode` int COMMENT
'from deserializer', `isbin` int COMMENT 'from deserializer',
`leafcategoryid` int COMMENT 'from deserializer', `itemsiteid` int
COMMENT 'from deserializer', `bidquantity` int COMMENT 'from
deserializer', `bidamtusd` double COMMENT 'from deserializer',
`offerquantity` int COMMENT 'from deserializer', `offeramountusd`
double COMMENT 'from deserializer', `offercreatedate` string COMMENT
'from deserializer', `buyersegment` string COMMENT 'from
deserializer', `buyercountryid` int COMMENT 'from deserializer',
`sellerid` bigint COMMENT 'from deserializer', `sellercountryid` int
COMMENT 'from deserializer', `sellerstdlevel` string COMMENT 'from
deserializer', `csssellerlevel` string COMMENT 'from deserializer',
`experimentchannel` int COMMENT 'from deserializer') ROW FORMAT SERDE
'org.apache.hadoop.hive.serde2.avro.AvroSerDe' STORED AS INPUTFORMAT
'org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat' LOCATION
'hdfs://apollo-phx-nn.vip.ebay.com:8020/user/dvasthimal/spark/successeventstaging/sojsuccessevents2
<http://apollo-phx-nn.vip.ebay.com:8020/user/dvasthimal/spark/successeventstaging/sojsuccessevents2>'
TBLPROPERTIES (
'avro.schema.literal'='{\"type\":\"record\",\"name\":\"success\",\"namespace\":\"Reporting.detail\",\"doc\":\"\",\"fields\":[{\"name\":\"guid\",\"type\":{\"type\":\"string\",\"avro.java.string\":\"String\"},\"doc\":\"\",\"default\":\"\"},{\"name\":\"sessionKey\",\"type\":\"long\",\"doc\":\"\",\"default\":0},{\"name\":\"sessionStartDate\",\"type\":{\"type\":\"string\",\"avro.java.string\":\"String\"},\"doc\":\"\",\"default\":\"\"},{\"name\":\"sojDataDate\",\"type\":{\"type\":\"string\",\"avro.java.string\":\"String\"},\"doc\":\"\",\"default\":\"\"},{\"name\":\"seqNum\",\"type\":\"int\",\"doc\":\"\",\"default\":0},{\"name\":\"eventTimestamp\",\"type\":{\"type\":\"string\",\"avro.java.string\":\"String\"},\"doc\":\"\",\"default\":\"\"},{\"name\":\"siteId\",\"type\":\"int\",\"doc\":\"\",\"default\":0},{\"name\":\"successEventType\",\"type\":{\"type\":\"string\",\"avro.java.string\":\"String\"},\"doc\":\"\",\"default\":\"\"},{\"name\":\"sourceType\",\"type\":{\"type\":\"string\",\"avro.java.string\":\"String\"},\"doc\":\"\",\"default\":\"\"},{\"name\":\"itemId\",\"type\":\"long\",\"doc\":\"\",\"default\":0},{\"name\":\"shopCartId\",\"type\":\"long\",\"doc\":\"\",\"default\":0},{\"name\":\"transactionId\",\"type\":\"long\",\"doc\":\"\",\"default\":0},{\"name\":\"offerId\",\"type\":\"long\",\"doc\":\"\",\"default\":0},{\"name\":\"userId\",\"type\":\"long\",\"doc\":\"\",\"default\":0},{\"name\":\"priorPage1SeqNum\",\"type\":\"int\",\"doc\":\"\",\"default\":0},{\"name\":\"priorPage1PageId\",\"type\":\"int\",\"doc\":\"\",\"default\":0},{\"name\":\"exclWMSearchAttemptSeqNum\",\"type\":\"int\",\"doc\":\"\",\"default\":0},{\"name\":\"exclPriorSearchPageId\",\"type\":\"int\",\"doc\":\"\",\"default\":0},{\"name\":\"exclPriorSearchSeqNum\",\"type\":\"int\",\"doc\":\"\",\"default\":0},{\"name\":\"exclPriorSearchCategory\",\"type\":\"int\",\"doc\":\"\",\"default\":0},{\"name\":\"exclPriorSearchL1\",\"type\":\"int\",\"doc\":\"\",\"default\":0},{\"name\":\"exclPriorSearchL2\",\"type\":\"int\",\"doc\":\"\",\"default\":0},{\"name\":\"currentImpressionId\",\"type\":\"long\",\"doc\":\"\",\"default\":0},{\"name\":\"sourceImpressionId\",\"type\":\"long\",\"doc\":\"\",\"default\":0},{\"name\":\"exclPriorSearchSqr\",\"type\":[\"null\",{\"type\":\"string\",\"avro.java.string\":\"String\"}],\"doc\":\"\",\"default\":null},{\"name\":\"exclPriorSearchSort\",\"type\":[\"null\",{\"type\":\"string\",\"avro.java.string\":\"String\"}],\"doc\":\"\",\"default\":null},{\"name\":\"isDuplicate\",\"type\":\"int\",\"doc\":\"\",\"default\":0},{\"name\":\"transactionDate\",\"type\":[\"null\",{\"type\":\"string\",\"avro.java.string\":\"String\"}],\"doc\":\"\",\"default\":null},{\"name\":\"auctionTypeCode\",\"type\":\"int\",\"doc\":\"\",\"default\":0},{\"name\":\"isBin\",\"type\":\"int\",\"doc\":\"\",\"default\":0},{\"name\":\"leafCategoryId\",\"type\":\"int\",\"doc\":\"\",\"default\":0},{\"name\":\"itemSiteId\",\"type\":\"int\",\"doc\":\"\",\"default\":0},{\"name\":\"bidQuantity\",\"type\":\"int\",\"doc\":\"\",\"default\":0},{\"name\":\"bidAmtUsd\",\"type\":\"double\",\"doc\":\"\",\"default\":0.0},{\"name\":\"offerQuantity\",\"type\":\"int\",\"doc\":\"\",\"default\":0},{\"name\":\"offerAmountUsd\",\"type\":\"double\",\"doc\":\"\",\"default\":0.0},{\"name\":\"offerCreateDate\",\"type\":[\"null\",{\"type\":\"string\",\"avro.java.string\":\"String\"}],\"doc\":\"\",\"default\":null},{\"name\":\"buyerSegment\",\"type\":[\"null\",{\"type\":\"string\",\"avro.java.string\":\"String\"}],\"doc\":\"\",\"default\":null},{\"name\":\"buyerCountryId\",\"type\":\"int\",\"doc\":\"\",\"default\":0},{\"name\":\"sellerId\",\"type\":\"long\",\"doc\":\"\",\"default\":0},{\"name\":\"sellerCountryId\",\"type\":\"int\",\"doc\":\"\",\"default\":0},{\"name\":\"sellerStdLevel\",\"type\":[\"null\",{\"type\":\"string\",\"avro.java.string\":\"String\"}],\"doc\":\"\",\"default\":null},{\"name\":\"cssSellerLevel\",\"type\":[\"null\",{\"type\":\"string\",\"avro.java.string\":\"String\"}],\"doc\":\"\",\"default\":null},{\"name\":\"experimentChannel\",\"type\":\"int\",\"doc\":\"\",\"default\":0}]}',
'numFiles'='546', 'transient_lastDdlTime'='1427359138',
'COLUMN_STATS_ACCURATE'='true', 'totalSize'='22595607864',
'numRows'='325974', 'rawDataSize'='0')")
valsojsuccessevents2= hiveContext.sql("insert overwrite table
sojsuccessevents2_spark"+
" select
guid,sessionKey,sessionStartDate,sojDataDate,seqNum,eventTimestamp,siteId,successEventType,sourceType,itemId,"+
" shopCartId,b.transaction_Id as transactionId,offerId,b.bdr_id as
userId,priorPage1SeqNum,priorPage1PageId,exclWMSearchAttemptSeqNum,exclPriorSearchPageId,"+
"
exclPriorSearchSeqNum,exclPriorSearchCategory,exclPriorSearchL1,exclPriorSearchL2,currentImpressionId,sourceImpressionId,exclPriorSearchSqr,exclPriorSearchSort,"+
" isDuplicate,b.bid_date as
transactionDate,auctionTypeCode,isBin,leafCategoryId,itemSiteId,b.qty_bid
as bidQuantity,"+
" b.bid_amt_unit_lstg_curncy * b.bid_exchng_rate as
bidAmtUsd,offerQuantity,offerAmountUsd,offerCreateDate,buyerSegment,buyerCountryId,sellerId,sellerCountryId,"+
" sellerStdLevel,cssSellerLevel,a.experimentChannel"+
" from success_events.sojsuccessevents1 a join dw_bid b "+
" on a.itemId = b.item_id and a.transactionId = b.transaction_id "+
" where b.auct_end_dt >= '"+startDate+"' AND b.bid_dt >=
'"+startDate+"' "+
" AND b.bid_type_code IN (1,9) AND b.bdr_id > 0 AND ( b.bid_flags &
32) = 0 and lower(a.successEventType) IN ('bid','bin')")
Error:
16/01/03 20:15:09 INFO yarn.ApplicationMaster: Final app status:
FAILED, exitCode: 15, (reason: User class threw exception:
org.apache.spark.sql.AnalysisException: cannot resolve 'a.itemId'
given input columns auct_end_dt, bdr_cntry_id, bid_flags, qty_bid,
app_id, bid_cobrand_id, bid_date, cre_date, upd_user, transaction_id,
bid_dt, ebx_bid_yn_id, bid_status_id, bdr_id, half_on_ebay_bid_id,
winning_qty, bid_exchng_rate, slng_chnl_id, bidding_site_id,
bdr_site_id, item_vrtn_id, bid_amt_unit_lstg_curncy, bid_type_code,
upd_date, lstg_curncy_id, host_ip_addr, item_id, cre_user; line 1 pos 864)
Regards,
