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, ÐΞ€ρ@Ҝ (๏̯͡๏) <[email protected]> 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 <[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 > > -- Deepak
