Skip to Content
author's profile photo Former Member
Former Member

HortonWorks Hadoop with SAP HANA Spark Controller

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: SELECT" target="_blank">http://www.sap.com/hana"> SELECT COUNT(*) AS CA_10261569202013848859 FROM "foodmart"."customer""Hadoop_customer" COL0 4
  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

Add a comment
10|10000 characters needed characters exceeded

Related questions

2 Answers

  • Posted on Aug 29, 2019 at 02:41 PM

    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_spark_hive_connection.html

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Oct 30, 2019 at 08:50 AM

    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]';

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.