Join multiple Tables and apply function group by function

[Former Member]

Hi All,

I am pretty new in HANA development and need help on the following requirement.

I have 5 tables loaded from MYSQL database with millions of records in most of them. Following are the tables with number of records.

• mid_2_sid –47,827,411
• sample – 68,045,998
• release_history – 2,850,194
• release_build_time  - 22,426
• session – 43,161,301

I need to achieve following

SELECT session_id, A.Sha256, A.Sample_Time, session_time,
filename, A.WF_ID, B.time as WF_Signature_time

    FROM

(SELECT

     session_id, s.sha256,

     create_date AS sample_time,

     t.create_time AS session_time,

     t.filename, h.pid AS WF_ID,

     MIN(build_version) AS build_version

  FROM session t

  INNER JOIN sample s ON t.sha256 = s.sha256

  INNER JOIN mid_2_sid m ON s.mid =m.mid      

  INNER JOIN release_history h ON m.sid = h.sid

      GROUP By sha256,s.mid,m.sid, create_date,create_time,filename)  AS A

INNER JOIN release_build_time AS B ON A.build_version=B.build_version

I created an attribute view joining tables session,sample,mid_2_sid and release_history and the attribute view returned me almost 12 Billion records and the when I tried to write a sql script to group by on some fields to get the minimum of build version , I received following error.

SAP DBTech JDBC: [2048]: column store error: search table error:  [9] Memory allocation failed

Can anyone help me giving some guidance on how this should be designed or what would be best approach.

[Former Member]

So it's tough to read someone's SQL like this.

First, this SQL should run fine as-is if you are running Rev.70. Try running it as is on Rev.70.

If you want to build it into a model then you need to be careful. I'd build it as a calc view. You should be able to do your aggregation nodes and joins directly on the tables.

Do you have primary keys set correctly? This will ensure that the calc view modeler correctly compiles the view.

It's likely you are getting a memory error because of a join cardinality problem, which is exploding the number of rows.

John

[Former Member]

Does your inner Select statement work standalone?

(SELECT

     session_id, s.sha256,

     create_date AS sample_time,

     t.create_time AS session_time,

     t.filename, h.pid AS WF_ID,

     MIN(build_version) AS build_version

  FROM session t

  INNER JOIN sample s ON t.sha256 = s.sha256

  INNER JOIN mid_2_sid m ON s.mid =m.mid     

  INNER JOIN release_history h ON m.sid = h.sid

      GROUP By sha256,s.mid,m.sid, create_date,create_time,filename) 

To include the aggregate function Min you should have all the object (except build version )in the select clause in group by