Incremental Load, Max(id) not loading and creation of Syn Table.

[TheKiwi]

Hi, i'm trying to implement incremental load for a few table in a Database, i'm 99% of the way there but there is still a few issue:

First of all, both my tables MaxValueTasks and MaxValueLogs only work half the times. Sometimes they will work properly and IncrementalLogs and Incrementasks will only load the new/changed data, sometimes they won't load anything from my qvd files, and everything will get loaded from the DataBase for IncrementalLogs and Incrementasks.

Second of all, since i can't use Qualify *; for this incremental load (doesn't recognise the variables names after the creation of the 1st qvd file) i'm trying to rename the fields i use to prevent the creation of Syn Tables, but one table is still created there (id+date_mod).

Here is my code :

let Logsize = FileSize('lib://FileQVD\IncrementalLogs.qvd');
let Tasksize = FileSize('lib://FileQVD\IncrementalTasks.qvd');

Let ThisExecTime = Now( );

if not isnull(Tasksize) and not isnull(Logsize) then

MaxValueTasks:
Load

max (id) AS MaxTasks

FROM [lib://FileQVD\IncrementalTasks.qvd] (qvd);

Let ttMaxId = peek('MaxTasks',-1);

drop table MaxValueTasks;

MaxValueLogs:
Load

max (id) AS MaxLogs

FROM [lib://FileQVD\IncrementalLogs.qvd] (qvd);

Let lMaxId = peek('MaxLogs',-1);

drop table MaxValueLogs;

IncrementalTasks :
SQL Select id,
tickets_id,
date,
users_id,
actiontime,
date_mod FROM glpi_tickettasks

WHERE date_mod BETWEEN '$(LastExecTime)' AND '$(ThisExecTime)' OR id > '$(ttMaxId)'
;

Concatenate LOAD [id] AS [ttid],
[tickets_id] AS [tatickets_id],
[date] AS [ttdate],
[users_id] AS [ttusers_id],
[actiontime] AS [ttactiontime],
[date_mod] AS [ttdate_mod]
FROM [lib://FileQVD\IncrementalTasks.qvd] (QVD)
where Not Exists(id)
;

Inner Join SQL SELECT id FROM glpi_tickettasks;

     If ScriptErrorCount = 0 then

     STORE IncrementalTasks INTO [lib://FileQVD\IncrementalTasks.qvd] (QVD);

     End If

IncrementalLogs :
SQL Select id,
itemtype,
items_id,
date_mod,
id_search_option,
old_value,
new_value FROM glpi_logs

WHERE id > '$(lMaxId)'
;

Concatenate LOAD [id] AS [lid],
[itemtype] AS [litemtype],
[items_id] AS [litems_id],
[date_mod] AS [ldate_mod],
[id_search_option] AS [lid_search_option],
[old_value] AS [lold_value],
[new_value] AS [lnew_value]
FROM [lib://FileQVD\IncrementalLogs.qvd] (QVD)
where Not Exists(id)
;

Inner Join SQL SELECT id FROM glpi_logs;

     If ScriptErrorCount = 0 then

     STORE IncrementalLogs INTO [lib://FileQVD\IncrementalLogs.qvd] (QVD);

     Let LastExecTime = ThisExecTime;

     End If

Else
IncrementalLogs :
SQL Select id,
itemtype,
items_id,
date_mod,
id_search_option,
old_value,
new_value FROM glpi_logs;

STORE IncrementalLogs INTO [lib://FileQVD\IncrementalLogs.qvd] (QVD);

IncrementalTasks :
SQL Select id,
tickets_id,
date,
users_id,
actiontime,
date_mod FROM glpi_tickettasks;

STORE IncrementalTasks INTO [lib://FileQVD\IncrementalTasks.qvd] (QVD);

End if

My DataBase is in MariaDB. Hope someone can help.

Have a good day.

[rubenmarin]

Hi, if you load the id field from database, and concatenates from qvd loading id as ttid, when you do the inner join by id, it will remove all the rows loaded from qvd, as none will have the id field.

I think you should make the rename of the fieds when loading from sql.

I usually keep database names on incremental loads, wich is usually done of the first layer of qvds, and rename fields on the nexts layers of qvds.

[TheKiwi]

Hi,

Thank you, I tried that earlier but for some reason it works now. The only issue is that the Inner Join won't work now (it gets stuck trying to process the query). Probably because now the id field is renamed in the table. How should i rewrite the INNER JOIN query ?

let Logsize = FileSize('lib://FileQVD\IncrementalLogs.qvd');
let Tasksize = FileSize('lib://FileQVD\IncrementalTasks.qvd');
Let ThisExecTime = Now( );

if not isnull(Tasksize) and not isnull(Logsize) then

MaxValueTasks:
Load

Max(ttid) AS MaxTasks

FROM [lib://FileQVD\IncrementalTasks.qvd] (qvd);

Let ttMaxId = peek('MaxTasks',-1);

drop table MaxValueTasks;

MaxValueLogs:
Load

Max(lid) AS MaxLogs

FROM [lib://FileQVD\IncrementalLogs.qvd] (qvd);

Let lMaxId = peek('MaxLogs',-1);

drop table MaxValueLogs;

IncrementalTasks :
LOAD [id] AS [ttid],
[tickets_id] AS [tatickets_id],
[date] AS [ttdate],
[users_id] AS [ttusers_id],
[actiontime] AS [ttactiontime],
[date_mod] AS [ttdate_mod];
SQL Select id,
tickets_id,
date,
users_id,
actiontime,
date_mod FROM glpi_tickettasks

WHERE date_mod BETWEEN '$(LastExecTime)' AND '$(ThisExecTime)' OR id > '$(ttMaxId)'
;

Concatenate LOAD ttid,
tatickets_id,
ttdate,
ttusers_id,
ttactiontime,
ttdate_mod
FROM [lib://FileQVD\IncrementalTasks.qvd] (QVD)
where Not Exists(ttid)
;

Inner Join SQL SELECT id FROM glpi_tickettasks;

If ScriptErrorCount = 0 then

STORE IncrementalTasks INTO [lib://FileQVD\IncrementalTasks.qvd] (QVD);

End If

IncrementalLogs :
LOAD [id] AS [lid],
[itemtype] AS [litemtype],
[items_id] AS [litems_id],
[date_mod] AS [ldate_mod],
[id_search_option] AS [lid_search_option],
[old_value] AS [lold_value],
[new_value] AS [lnew_value];
SQL Select id,
itemtype,
items_id,
date_mod,
id_search_option,
old_value,
new_value FROM glpi_logs

WHERE id > '$(lMaxId)'
;

Concatenate LOAD lid,
litemtype,
litems_id,
ldate_mod,
lid_search_option,
lold_value,
lnew_value
FROM [lib://FileQVD\IncrementalLogs.qvd] (QVD)
where Not Exists(lid)
;

Inner Join SQL SELECT id FROM glpi_logs;

If ScriptErrorCount = 0 then

STORE IncrementalLogs INTO [lib://FileQVD\IncrementalLogs.qvd] (QVD);

Let LastExecTime = ThisExecTime;

End If
Else
IncrementalLogs :
LOAD [id] AS [lid],
[itemtype] AS [litemtype],
[items_id] AS [litems_id],
[date_mod] AS [ldate_mod],
[id_search_option] AS [lid_search_option],
[old_value] AS [lold_value],
[new_value] AS [lnew_value];
SQL Select id,
itemtype,
items_id,
date_mod,
id_search_option,
old_value,
new_value FROM glpi_logs;

STORE IncrementalLogs INTO [lib://FileQVD\IncrementalLogs.qvd] (QVD);

IncrementalTasks :
LOAD [id] AS [ttid],
[tickets_id] AS [tatickets_id],
[date] AS [ttdate],
[users_id] AS [ttusers_id],
[actiontime] AS [ttactiontime],
[date_mod] AS [ttdate_mod];
SQL Select id,
tickets_id,
date,
users_id,
actiontime,
date_mod FROM glpi_tickettasks;

STORE IncrementalTasks INTO [lib://FileQVD\IncrementalTasks.qvd] (QVD);

End if

I tried doing this : 
SQL SELECT id FROM glpi_tickettasks
Inner Join [IncrementalTasks] where id = ttid;

But I get an error ( or it thinks IncrementalTasks is a table in my db without both []).

[rubenmarin · Accepted solution]

Hi, it should be renamed to match the field names loaded in the table, in example, instead of

Inner Join SQL SELECT id FROM glpi_tickettasks;

Use

Inner Join
LOAD id as ttid;
SQL SELECT id FROM glpi_tickettasks;

[TheKiwi]

Thank you so much ! I will need to verify if the data is correct but everything is working properly !

Have a good day !