topic Re: Load table from Mysql in parts in SQL SELECT in App Development

Load table from Mysql in parts in SQL SELECT

ioannagr — Mon, 10 May 2021 09:48:39 GMT

Hi all.

I was thinking to load data from a very big table from MySql db in parts in SELECT part.

So I have

LOAD pk from Table1;

INNER JOIN

LOAD pk as pk;

SQL SELECT pk, year from Table2

where year='2020'

UNION ALL

SELECT pk, year from Table2

where year='2021';

Would something like that work? How do i do it with a loop in the SQL SELECT part?

Re: Load table from Mysql in parts in SQL SELECT

Dalton_Ruer — Mon, 10 May 2021 13:31:21 GMT

Not sure what you are trying to accomplish by not just selecting everything from table 2 in a single query. By doing a UNION ALL you are still going to build a giant result set on the MYSQL side.

If you are trying to save processing in MySQL you could bring the data back in sets and concatenate the data inside of Qlik Sense instead.

Re: Load table from Mysql in parts in SQL SELECT

ioannagr — Mon, 10 May 2021 13:42:16 GMT

@Dalton_Ruer this table is a billion rows. I want to do hard delete incremental load, which by following Qlik Help routine, (https://help.qlik.com/en-US/sense/February2021/Subsystems/Hub/Content/Sense_Hub/LoadData/use-QVD-files-incremental-load.htm) involves an inner join with the initial table calling it from the database straight.

I have figured out a way by splitting the tables of interest by years in qvds for an initial load, but the hard delete routine calls for calling the table once more straight from the database and I have NO clue how to make it speedier. I'm looking into it for days, the load time is days... incremental load goes to waste.

Re: Load table from Mysql in parts in SQL SELECT

Dalton_Ruer — Mon, 10 May 2021 14:17:16 GMT

Gotcha. Incremental Load makes a lot of sense but Qlik doesn't have a UNION ALL which is why I thought you were doing that in MYSQL. I understand your confusion and will go through what the post meant by INNER JOIN

If you already have your years broken out then I would handle the Incremental Load on a Year by Year basis per the normal scheme.

Qlik now provides a MERGE to handle incremental loads more cleanly for integration with Change Data Capture, https://help.qlik.com/en-US/sense/February2021/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPrefixes/Merge.htm but it doesn't handle DELETES so you might as well stick with the known method.

Typically for Incremental load you would:

Read the new information into a table we will call it MyTable

Concatenate the old information into the new information table using a

WHERE NOT EXISTS (the where not exists forces you to keep the updated values)

INNER JOIN - The Inner Join is done within Qlik. The concept is:

Read all of the Primary keys from the databases into a table we will call it MyTableKeys

Now we do an Inner Join within Qlik Sense, (not the db) between MyTable and MyTableKeys. Thus removing any values that no longer exist in the database.

Re: Load table from Mysql in parts in SQL SELECT

ioannagr — Mon, 10 May 2021 14:25:22 GMT

@Dalton_Ruer i've read thoroughly your answer. I do as you said, but inner join with myTableKeys , even if i just load the pks takes days! That's why I tried asking for union all, or reading in parts straight from the db.

Kindly look here as well, It's another post I made about this where you can see my code so far : https://community.qlik.com/t5/New-to-Qlik-Sense/hard-delete-incremental-load-on-very-large-table/m-p/1806242#M184711

Re: Load table from Mysql in parts in SQL SELECT

ioannagr — Mon, 10 May 2021 14:32:18 GMT

@Dalton_Ruer oh, wait, you mean create a qvd with only MyTableKeys and inner join it with the above? Was it so simple?! 😱 Is that okay with the transactional data though? Doesn't this mean it will only get stored just once so if a deletion happens in data loaded after the qvd's creation will not get affected or recognized as deletion? Why Qlik Help proposes then reading straight from the db?

So many questions. 😅 Thank you in advance for your time @Dalton_Ruer , I so enjoy learning.

Re: Load table from Mysql in parts in SQL SELECT

Dalton_Ruer — Mon, 10 May 2021 15:44:52 GMT

No you don't store the table that is just the KEYs. You load it live each time you reload. You only store the MERGED data.

Load new data live

Concatenate to it from your QVD using where not exists

Load PK live

Inner join

Store the merged data back to the QVD

Re: Load table from Mysql in parts in SQL SELECT

ioannagr — Mon, 10 May 2021 17:53:01 GMT

Okay, I think we're so close to solving my problem that I have for SO long, please bear with me @Dalton_Ruer .

So you suggest

1) I load my new data from source table by years live as I have done

2) concatenate with my qvd with where not exists

3) Load all pks from source table by years (again i suppose, else it takes aaaaaaaages) live

4) Inner join with what? 🤔

5) Store as a final step.