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

Join multiple Tables and apply function group by function

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.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Dec 06, 2013 at 03:13 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Dec 06, 2013 at 04:19 AM

    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

    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.