I've search high and low, and can't quite get to what I think I need. Here is my situation.
I have a 'large' file (180K records) with 3 columns of data that looks like below. Lets call the file ASSETS.CSV
CODE SERIAL_NUMBER ACCOUNT
ABC LK100222 123456789
ABC MN012345 123456789
ABC PQR12345 123456780
Need to, effectively, use the is file a DB and left join SERIAL_NUMBER with data in an actual table. lets call the table PARTS_SALES
The PARTS_SALES table will have millions of rows and 00s of columns
I effectively need to be able to:
SELECT a1.serial_no, a1.col2, a1.col3, a1col3, a1.col5, b1.SERIAL_NUMBER, b1.ACCOUNT
FROM ASSETS.CSV b1
LEFT JOIN PARTS_SALES a1 ON b1.SERIAL_NUMBER = a1.serial_no
Is there a straight forward way to do this.
Solved! Go to Solution.
Hi @youngjw
There are a number of ways of doing this, but probably the easiest is using the in-db tool palette. You can stream your csv into a temp table and then create the left join. All of this will happen in the database, so you won't need to download the millions of sales records to your designer instance unnecessarily
Thanks Ollie, I have some learning to do on the stream in tool, but this looks exactly like what I need to do. thanks!
@youngjw the Stream In/Out tools let you switch modalities and take data into the database, or into memory. Yours is the perfect use-case for the Stream In tool
Thanks Ollie!
So my situation is this. In a large corporation and not a lot of flexibility to get write access to a DB, especially co-located on the table with 00s of millions of rows.
Is it fair to say that it is not possible to more simply just use Join tool to join two Input Data tools (one linked to input file, 00k rows and the other a sql statment 00sM rows). ?