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'
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')")
val sojsuccessevents2 = 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,
