You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@spark.apache.org by on 2016/01/04 04:22:25 UTC
Unable to run spark SQL Join query.
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,
Re: Unable to run spark SQL Join query.
Posted by .
Any suggestions on how to do joins in Spark SQL. Above Spark SQL
format/Syntax is not working.
On Mon, Jan 4, 2016 at 2:33 AM, ÐΞ€ρ@Ҝ (๏̯͡๏) <de...@gmail.com> wrote:
> There are three tables in action here.
>
> Table A (success_events.sojsuccessevents1) JOIN TABLE B (dw_bid) to
> create TABLE C (sojsuccessevents2_spark)
>
> Now table success_events.sojsuccessevents1 has itemid that i confirmed by
> running describe success_events.sojsuccessevents1 from spark-sql shell.
>
> I changed my join query to use itemid.
>
> " on a.itemid = b.item_id and a.transactionid = b.transaction_id " +
>
> But still i get the same error
>
>
> 16/01/04 03:29:27 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 bid_flags, slng_chnl_id, upd_user, bdr_id, bid_status_id,
> bid_dt, transaction_id, host_ip_addr, upd_date, item_vrtn_id, auct_end_dt,
> bid_amt_unit_lstg_curncy, bidding_site_id, cre_user, bid_cobrand_id,
> bdr_site_id, app_id, lstg_curncy_id, bid_exchng_rate, bid_date,
> ebx_bid_yn_id, cre_date, winning_qty, bid_type_code, half_on_ebay_bid_id,
> bdr_cntry_id, qty_bid, item_id; line 1 pos 864)
>
> It appears as if its trying to look for itemid in TABLE B (dw_bid) instead
> of TABLE A (success_events.sojsuccessevents1) As above columns are from
> TABLE B.
>
> Regards,
> Deepak
>
>
>
>
> On Sun, Jan 3, 2016 at 7:42 PM, Jins George <ji...@aeris.net> wrote:
>
>> 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'
>> 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,
>>
>>
>>
>>
>
>
> --
> Deepak
>
>
--
Deepak
Re: Unable to run spark SQL Join query.
Posted by .
There are three tables in action here.
Table A (success_events.sojsuccessevents1) JOIN TABLE B (dw_bid) to create
TABLE C (sojsuccessevents2_spark)
Now table success_events.sojsuccessevents1 has itemid that i confirmed by
running describe success_events.sojsuccessevents1 from spark-sql shell.
I changed my join query to use itemid.
" on a.itemid = b.item_id and a.transactionid = b.transaction_id " +
But still i get the same error
16/01/04 03:29:27 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 bid_flags, slng_chnl_id, upd_user, bdr_id, bid_status_id,
bid_dt, transaction_id, host_ip_addr, upd_date, item_vrtn_id, auct_end_dt,
bid_amt_unit_lstg_curncy, bidding_site_id, cre_user, bid_cobrand_id,
bdr_site_id, app_id, lstg_curncy_id, bid_exchng_rate, bid_date,
ebx_bid_yn_id, cre_date, winning_qty, bid_type_code, half_on_ebay_bid_id,
bdr_cntry_id, qty_bid, item_id; line 1 pos 864)
It appears as if its trying to look for itemid in TABLE B (dw_bid) instead
of TABLE A (success_events.sojsuccessevents1) As above columns are from
TABLE B.
Regards,
Deepak
On Sun, Jan 3, 2016 at 7:42 PM, Jins George <ji...@aeris.net> wrote:
> 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'
> 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,
>
>
>
>
--
Deepak
Re: Unable to run spark SQL Join query.
Posted by Jins George <ji...@aeris.net>.
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,
>
>
>