Joining multiple tables

[davimazz83]

Hi All!

I have a problem. I’m trying to transform a SAS procedure to a Knime procedure. In SAS i have a multiple join of this kind:

PROC SQL;
CREATE TABLE WORK.JOINEDTABLE AS SELECT DISTINCT A.DATA1,A.DATA2,B.DATA_1
FROM WORK.A
INNER JOIN C ON (A.DATA1 = C.DATA1)
INNER JOIN B ON (B.ID_1 = C.ID_1)
LEFT JOIN WORK.D ON (A.DATA1 = D.DATA1)
WHERE C.DATA_2 <> “” AND D.DATA1 = “”;
QUIT;

graphically, it’s in this way:

my question is, how can i create the same join? I’ve tried to create a join on mysql database reader, but it’s tremendously slowly due to technical limits of network…

thank you,
Davide

[mlauber71]

I could think of two ways

• use a cascade of joins in KNIME
• use a SQL connection. You might try SQLite
  Work with SQLite database
• try the local BigData implementation of KNIME (https://www.knime.com/whats-new-in-knime-36#create-local-big-data-env) to use if it could help you distribute the workload. Also a good exercise if you once have to move your data to a real big data environment (that gives you extra power)

Of course it depends on how large your data is. In the end KNIME might need more power. You can read about that here: https://www.knime.com/blog/optimizing-knime-workflows-for-performance

If you could upload an example we could make a test.

If you have previous experience with SAS you might profit from this booklet:
https://www.knime.com/knimepress/from-sas-to-knime

[izaychik63]

Try to optimize your SQL in DB. Create appropriate indexes, create corresponding view or stored procedure.