I have this query 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 sojsuccessevents1 a *join dw_bid b* on a.itemId = b.item_id and a.transactionId = b.transaction_id where b.auct_end_dt >= '2015-02-16' AND b.bid_dt >= '2015-02-16' 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')
If i create sojsuccessevents2_spark from hive command line and run above command form Spark SQL program then i get error "sojsuccessevents2_spark table not found". Hence i dropped the command from Hive and run create table sojsuccessevents2_spark from Spark SQL before running above command and it works until it hits next road block "*dw_bid table not found"* This makes me belive that Spark for some reason is not able to read/understand the tables created outside Spark. I did copy /apache/hive/conf/hive-site.xml into Spark conf directory. Please suggest. Regards, Deepak On Thu, Mar 26, 2015 at 1:26 PM, ÐΞ€ρ@Ҝ (๏̯͡๏) <deepuj...@gmail.com> wrote: > I have a hive table named dw_bid, when i run hive from command prompt and > run describe dw_bid, it works. > > I want to join a avro file (table) in HDFS with this hive dw_bid table and > i refer it as dw_bid from Spark SQL program, however i see > > 15/03/26 00:31:01 INFO HiveMetaStore.audit: ugi=dvasthimal > ip=unknown-ip-addr cmd=get_table : db=default tbl=dw_bid > 15/03/26 00:31:01 ERROR metadata.Hive: > NoSuchObjectException(message:default.dw_bid table not found) > at > org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.get_table(HiveMetaStore.java:1560) > at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) > at > sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) > > > Code: > > val successDetail_S1 = sqlContext.avroFile(input) > successDetail_S1.registerTempTable("sojsuccessevents1") > val countS1 = sqlContext.sql("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 sojsuccessevents1 a join dw_bid b " + > " on a.itemId = b.item_id and a.transactionId = b.transaction_id " > + > " where 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')") > println("countS1.first:" + countS1.first) > > > > Any suggestions on how to refer a hive table form Spark SQL? > -- > > Deepak > > -- Deepak