Is there a way to combine multiple sources to query as one?

[AndySchmitt]

In my requirement I receive 30 csv files. 10 files are companies, 10 files are the partners of those companies and 10 files are details about the activity.

I need to join data from these 3 types of files to create a json that will be imported to a mongodb instance.

My idea is to loop row by row in each of the company csv, take the Id and query partners csv and activity csv. With those data, I can make a json to import like this:

{
    Id: 3000
    companyName: ACME
    partners: [
        { partner: "Partner 1" },
        { partner: "Partner 2" }
    ],
    activities: [
        { activity: "Oil" },
        { activity: "Gas" }
    ]
}

the csvs are zipped and with all together I have 5 Gb of data. Unzipped I have 14 gb of data. There is about 100,000,000 lines.

Is it possible? Is it the best way to suit this problem? Whats the performance for huge file sizes?
These files are public, I can give it to you, for testing purposes.

[paillave]

The very point of etl net is to deal with many streams and combine them.
Go in the documentation as there are many way to do it depending on your needs.
Any way, what U mention is perfectly possible to do indeed.

[AndySchmitt]

The problem I want to solve is someway specific.

For example, lets take the Company's CSV. In this file every line is a company.
Foreach company I need to find ALL partners and ALL activities.
I have 10 files with partners and 10 files with activities.
Each file have about 4 million rows. So, I have 40M rows of partners and 40M rows of activities.
I need to make this "join" for every 20M companies.
I cant load all this to memory, so, my problem is:

• How can I make this "join" in a performant way and generate a json for every company in the final of process?

All files are public, so, I can send to you if you want, but I need your help to accomplish this.

[paillave]

The problem is that no miracle is possible, whatever the ETL you will use.
Join together 10 files of 4 million rows each is possible in an efficient way only if you join all of them based on the same key, and that every file is sorted on this key. In this case you will jon them using LeftJoin operator like described here: https://paillave.github.io/Etl.Net/docs/recipes/linkStreams#join-and-leftjoin
Otherwise, nothing is possible out of the box for any ETL. Therefore, the only solution is to save files one by one in the database, ensuring the sequence of dependencies using the operator WaiWhenDone. Then you will lookup on the depending table (you just imported) using properly the EfCoreLookup operator depending on the volume like described here: https://paillave.github.io/Etl.Net/docs/recipes/database#lookup-with-entity-framework

High level example to give an idea:

var type1Stream = fileType1Stream
    .CrossApplyTextFile("parse Type1 file", ...)
    .Select("create Type1", ...)
    .EfCoreSave("save Type1");

var type4Stream = fileType4Stream
    .CrossApplyTextFile("parse Type4 file", ...)
    .Select("create Type4", ...)
    .EfCoreSave("save Type4");

var type2Stream = fileType2Stream
    .WaitWhenDone("wait the type 1 files are pocessed", type1Stream)
    .CrossApplyTextFile("parse Type2 file", ...)
    .EfCoreLookup("get related Type1", ...)
    .Select("create Type2", ...)
    .EfCoreSave("save Type2");

var type3Stream = fileType3Stream
    .WaitWhenDone("wait the type 2 files are pocessed", type2Stream)
    .CrossApplyTextFile("parse Type3 file", ...)
    .EfCoreLookup("get related Type2", ...)
    .WaitWhenDone("wait the type 4 files are pocessed", type4Stream )
    .EfCoreLookup("get related Type4", ...)
    .Select("create Type3", ...)
    .EfCoreSave("save Type3");