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 <[email protected]> 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
