cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

HortonWorks Hadoop with SAP HANA Spark Controller

bernhard_sauerteig
Explorer

on ‎06-06-2019 1:44 PM

0 Kudos

Hello,

we are running sandbox 2.6.5 with Spark2 v2.3.0 and SAP HANA SparkController v2.3.5 on top. The Ambari dashboard shows all services as up and running, everything seems fine:


Now, we wish to connect from our HANA database (running on IP 10.118.169.32) to Hadoop via the SparkController. We configured the connection in HANA Studio client tool using these properties:

The connection is successfully established and we can see the table that we wish to access:

However, when we run a simple select query, it just runs for about 10 minutes, until the connection is closed. No data is returned:

  1. internal error:Error opening the cursor for the remote database Connection has been closed.for query SELECT COUNT(*) FROM "foodmart"."customer"

In the hana_controller.log we can see that the SQL query was accepted and the execution plan is processed =>

  1. 19/06/0611:39:28 DEBUG NetworkUtils:InboundMessage=>'Message Type => 'PING'; Message Id => '693201651063748'; Request Id => '35d3a519-002a-0016-b033-31a660011a59';'
  2. 19/06/0611:39:28 DEBUG HanaMessageHandler:ReceivedRequestfrom:'172.18.0.3';MessageType=>'PING'
  3. 19/06/0611:39:28 DEBUG NetworkUtils:OutboundMessage=>'Message Type => 'PONG'; Message Id => '693201670667961'; Request Id => '35d3a519-002a-0016-b033-31a660011a59';'
  4. 19/06/0611:39:28 DEBUG NetworkUtils:InboundMessage=>'Message Type => 'START_SESSION'; Message Id => '693201689176436'; Request Id => '35d3a519-002a-0016-b033-31a660011a5b'; Session Id => '3878625230891646998-12696546365132577370';'
  5. 19/06/0611:39:28 DEBUG HanaMessageHandler:ReceivedRequestfrom:'172.18.0.3';MessageType=>'START_SESSION'
  6. 19/06/0611:39:28 INFO CommandRouterDefault:CurrentAssignments=> NONE;IncomingAssignment=>'3878625230891646998-12696546365132577370#35d3a519-002a-0016-b033-31a660011a5b'
  7. 19/06/0611:39:28 INFO RequestHandler:BoundMessage to Orchestrator:'35d3a519-002a-0016-b033-31a660011a5b'
  8. 19/06/0611:39:28 INFO RequestHandler:RetrievingBoundMessage:'35d3a519-002a-0016-b033-31a660011a5b'
  9. 19/06/0611:39:28 DEBUG RequestHandler:RelayingMessage to Client:'CONTINUE_SESSION'
  10. 19/06/0611:39:28 DEBUG NetworkUtils:OutboundMessage=>'Message Type => 'CONTINUE_SESSION'; Message Id => '693201720489241'; Request Id => '35d3a519-002a-0016-b033-31a660011a5b'; Session Id => '3878625230891646998-12696546365132577370';'
  11. 19/06/0611:39:28 DEBUG NetworkUtils:InboundMessage=>'Message Type => 'CONTINUE_SESSION'; Message Id => '693201726088539'; Request Id => '35d3a519-002a-0016-b033-31a660011a5b'; Session Id => '3878625230891646998-12696546365132577370';'
  12. 19/06/0611:39:28 DEBUG HanaMessageHandler:ReceivedRequestfrom:'172.18.0.3';MessageType=>'CONTINUE_SESSION'
  13. 19/06/0611:39:28 INFO CommandRouterDefault:CurrentAssignments=>3878625230891646998-12696546365132577370#35d3a519-002a-0016-b033-31a660011a5b -> Actor[akka://SAPHanaSpark/user/Controller/HanaSparkRouter/$a#1995820509]; Incoming Assignment => '3878625230891646998-12696546365132577370#35d3a519-002a-0016-b033-31a660011a5b'
  14. 19/06/0611:39:28 INFO RequestHandler:RetrievingBoundMessage:'35d3a519-002a-0016-b033-31a660011a5b'
  15. 19/06/0611:39:28 INFO HanaSparkFacade:Inside appraise session: hanaes (auth:SIMPLE)
  16. 19/06/0611:39:28 DEBUG Session:ConnectingUser:'root'
  17. 19/06/0611:39:28 DEBUG RequestHandler:RelayingMessage to Client:'SESSION_OPENED'
  18. 19/06/0611:39:28 DEBUG NetworkUtils:OutboundMessage=>'Message Type => 'SESSION_OPENED'; Message Id => '693201738786757'; Request Id => '35d3a519-002a-0016-b033-31a660011a5b'; Session Id => '3878625230891646998-12696546365132577370';'
  19. 19/06/0611:39:28 DEBUG RequestHandler:Finished executing:'START_SESSION'.Returning to the pool
  20. 19/06/0611:39:28 DEBUG ChannelManager:NewSessionis added:'3878625230891646998-12696546365132577370'
  21. 19/06/0611:39:28 DEBUG NetworkUtils:InboundMessage=>'Message Type => 'PING'; Message Id => '693201743893250'; Request Id => '35d3a519-002a-0016-b033-31a660011a5c';'
  22. 19/06/0611:39:28 DEBUG HanaMessageHandler:ReceivedRequestfrom:'172.18.0.3';MessageType=>'PING'
  23. 19/06/0611:39:28 DEBUG NetworkUtils:OutboundMessage=>'Message Type => 'PONG'; Message Id => '693201746082223'; Request Id => '35d3a519-002a-0016-b033-31a660011a5c';'
  24. 19/06/0611:39:28 DEBUG NetworkUtils:InboundMessage=>'Message Type => 'PING'; Message Id => '693201748509338'; Request Id => '35d3a519-002a-0016-b033-31a660011a5d';'
  25. 19/06/0611:39:28 DEBUG HanaMessageHandler:ReceivedRequestfrom:'172.18.0.3';MessageType=>'PING'
  26. 19/06/0611:39:28 DEBUG NetworkUtils:OutboundMessage=>'Message Type => 'PONG'; Message Id => '693201750304135'; Request Id => '35d3a519-002a-0016-b033-31a660011a5d';'
  27. 19/06/0611:39:28 DEBUG NetworkUtils:InboundMessage=>'Message Type => 'PING'; Message Id => '693201752596927'; Request Id => '35d3a519-002a-0016-b033-31a660011a5e';'
  28. 19/06/0611:39:28 DEBUG HanaMessageHandler:ReceivedRequestfrom:'172.18.0.3';MessageType=>'PING'
  29. 19/06/0611:39:28 DEBUG NetworkUtils:OutboundMessage=>'Message Type => 'PONG'; Message Id => '693201753461377'; Request Id => '35d3a519-002a-0016-b033-31a660011a5e';'
  30. 19/06/0611:39:28 DEBUG NetworkUtils:InboundMessage=>'Message Type => 'QUERY_EXECUTE'; Message Id => '693201755190134'; Request Id => '35d3a519-002a-0016-b033-31a660011a5f'; Session Id => '3878625230891646998-12696546365132577370';'
  31. 19/06/0611:39:28 DEBUG HanaMessageHandler:ReceivedRequestfrom:'172.18.0.3';MessageType=>'QUERY_EXECUTE'
  32. 19/06/0611:39:28 INFO CommandRouterDefault:CurrentAssignments=> NONE;IncomingAssignment=>'3878625230891646998-12696546365132577370#35d3a519-002a-0016-b033-31a660011a5f'
  33. 19/06/0611:39:28 INFO RequestHandler:BoundMessage to Orchestrator:'35d3a519-002a-0016-b033-31a660011a5f'
  34. 19/06/0611:39:28 INFO RequestHandler:RetrievingBoundMessage:'35d3a519-002a-0016-b033-31a660011a5f'
  35. 19/06/0611:39:28 DEBUG HanaExecPlan:<?xml version="1.0"?><hana:queryplan xmlns:hana="http://www.sap.com/hana"><select><sql>SELECT COUNT(*) AS CA_10261569202013848859 FROM "foodmart"."customer""Hadoop_customer"</sql><typeInfo><column><name>COL0</name><type>4</type></column></typeInfo><parameters/><hints/><relocators/></select></hana:queryplan>
  36. 19/06/0611:39:28 DEBUG RequestHandler:ChosenEngine ID:'sparksql'
  37. 19/06/0611:39:28 DEBUG DataRouteRegistry:RegisteringQueryExecutionwith requestId:'147BF43D222C974CC039F0B3C6E6356C'
  38. 19/06/0611:39:28 INFO AsyncExecutor:RunningwithUserPrivilege'root'
  39. 19/06/0611:39:28 DEBUG RequestHandler:UnblockedRelay
  40. 19/06/0611:39:28 DEBUG RequestHandler:RelayingMessage to Client:'QUERY_ACCEPTED'
  41. 19/06/0611:39:28 DEBUG NetworkUtils:OutboundMessage=>'Message Type => 'QUERY_ACCEPTED'; Message Id => '693201797735164'; Request Id => '35d3a519-002a-0016-b033-31a660011a5f'; Session Id => '3878625230891646998-12696546365132577370';'
  42. 19/06/0611:39:28 INFO HanaSparkFacade:HanaSelectTask(SQL): SELECT COUNT(*) AS CA_10261569202013848859 FROM `foodmart`.`customer``Hadoop_customer`
  43. 19/06/0611:39:29 INFO SparkCompositeCatalog:Lookingfor table:'foodmart.customer'
  44. 19/06/0611:39:30 DEBUG HanaHiveSQLContext: resolveRelations23 - hti.table ='customer'; rel =''SubqueryAlias customer
  45. +-'UnresolvedCatalogRelation `foodmart`.`customer`, org.apache.hadoop.hive.ql.io.orc.OrcSerde
  46. '
  47. 19/06/0611:39:30 DEBUG DistributedDataSetFactoryImpl:Query plan:
  48. 'Project ['COUNT(1) AS CA_10261569202013848859#39]
  49. +-'SubqueryAlias Hadoop_customer
  50. +- 'SubqueryAlias customer
  51. +-'SubqueryAlias customer
  52. +- 'UnresolvedCatalogRelation`foodmart`.`customer`, org.apache.hadoop.hive.ql.io.orc.OrcSerde
  53. 19/06/0611:39:30 INFO SparkFacade$anon$1:Created broadcast 0from
  54. 19/06/0611:39:31 INFO SparkFacade$anon$1:Starting job: transferDatafromPartitions at SparkFacade.scala:299
  55. 19/06/0611:39:31 INFO SparkFacade$anon$1:Created broadcast 1from broadcast at DAGScheduler.scala:1039
  56. 19/06/0611:39:33 INFO SparkFacade$anon$1:Created broadcast 2from broadcast at DAGScheduler.scala:1039
  57. 19/06/0611:39:35 DEBUG SmartReverseProxyHandler:StartingChannelTag:'sandbox-hdp.hortonworks.com:49372'
  58. 19/06/0611:39:35 DEBUG SmartReverseProxyHandler:MessageType=>'DATA_SOURCE_LOCATION'
  59. 19/06/0611:39:35 DEBUG DataRouteRegistry:RegisteringEndPointfor requestId:'147BF43D222C974CC039F0B3C6E6356C'('3878625230891646998-12696546365132577370','35d3a519-002a-0016-b033-31a660011a5f'), transferId:'Result:0'
  60. 19/06/0611:39:35 DEBUG NetworkUtils:InboundMessage=>'Message Type => 'DATA_SOURCE_LOCATION'; Message Id => '693208692846518'; Request Id => '35d3a519-002a-0016-b033-31a660011a5f'; Session Id => '3878625230891646998-12696546365132577370';'
  61. 19/06/0611:39:35 DEBUG HanaMessageHandler:ReceivedRequestfrom:'sandbox-hdp.hortonworks.com';MessageType=>'DATA_SOURCE_LOCATION'
  62. 19/06/0611:39:35 INFO CommandRouterDefault:CurrentAssignments=>3878625230891646998-12696546365132577370#35d3a519-002a-0016-b033-31a660011a5f -> Actor[akka://SAPHanaSpark/user/Controller/HanaSparkRouter/$b#-336533065]; Incoming Assignment => '3878625230891646998-12696546365132577370#35d3a519-002a-0016-b033-31a660011a5f'
  63. 19/06/0611:39:35 INFO RequestHandler:RetrievingBoundMessage:'35d3a519-002a-0016-b033-31a660011a5f'
  64. 19/06/0611:39:35 INFO RequestHandler:ForwardingDataVent:'QUERY_DATA_SOURCE';Direction:'0'
  65. 19/06/0611:39:35 DEBUG RequestHandler:DataTransferEndPoint:'172.18.0.2:7861'
  66. 19/06/0611:39:35 DEBUG RequestHandler:RelayingMessage to Client:'QUERY_DATA_SOURCE'
  67. 19/06/0611:39:35 DEBUG NetworkUtils:OutboundMessage=>'Message Type => 'QUERY_DATA_SOURCE'; Message Id => '693208699630324'; Request Id => '35d3a519-002a-0016-b033-31a660011a5f'; Session Id => '3878625230891646998-12696546365132577370';'
  68. 19/06/0611:49:35 DEBUG HanaMessageHandler:Channelis unregistered...Closing all sessions
  69. 19/06/0611:49:35 DEBUG ChannelManager:ClosingAllSessions
  70. 19/06/0611:49:35 DEBUG ChannelManager:Sessionis closed:'3878625230891646998-12696546365132577370'

We need help to figure out, why the result is not coming back. This is our first Hadoop installation, we do not know what the root cause could possibly be. Maybe there is an issue with the IP or port that is mentioned in line "Data Transfer End Point"?

Your advice on how to pinpoint and resolve the root cause would be greatly appreciated.

Thank you very much and best regards,

Bernhard

Show replies
Show replies
Answer

Accepted Solutions (0)

Answers (2)

Answers (2)

cf2
Explorer
‎10-30-2019 8:50 AM
0 Kudos

Finally we got the solution. If you are using external tables in hive instead of hive-managed tables, it should work:

Example:

CREATE external TABLE IF NOT EXISTS [table_name]

(key int, value string)

ROW FORMAT DELIMITED

FIELDS TERMINATED BY ','

STORED AS TEXTFILE

LOCATION '[HDFS-Storage-Location-as-Folder]';

Show replies
Show replies
cf2
Explorer
‎08-29-2019 3:41 PM
0 Kudos

I am facing exactly the same problem. My guess is that problem is linked to the fact that from Hortonworks 3.0 on, Spark und Hive do not share the same database schemas anymore. This is documented at

https://docs.hortonworks.com/HDPDocuments/HDP3/HDP-3.0.0/integrating-hive/content/hive_configure_a_s...

Unfortunately the solution mentioned at the link above does not help - I added the configuration in Ambari for Spar as well as for SAP Spark Controller. With no effect.

So any help would be very much appreciated.

Show replies
Show replies
Ask a Question