topic Re: Incremental load in qlikview from hive in QlikView

Incremental load in qlikview from hive

Thu, 26 Jun 2014 14:34:03 GMT

I want to do incremental data load process in qlikview and store the data into qvd and source of data is hive.

I am following the below approach for delta load,

Get the latest modified date from QVD and save it in a variable ex: Modifieddate.
Get records from Hive whose modified date is greater than Modifieddate, i am using where condition
Get new records from Hive whose primary key not exist in QVD.
Loading the fetched records from hive to qvd.

4.1. If the primary key already exist, update the record

4.2. If the primary key doesn’t exist, insert the record

the issue i am facing here is the hive query is not able to fetch records from according to the where condition,I have checked the same query from hive CLI it is working fine and I have checked static query from Qlikview to hive it is also working fine. The issue is occurring when I am using variable substitution in query.

Thanks

Hari

Re: Incremental load in qlikview from hive

fosuzuki — Thu, 26 Jun 2014 14:57:42 GMT

My guess is that you have to adjust the date format from the variable into an acceptable SQL date format.

Try using the Script Debugger to see exactly what is the SQL statement that QV is sending to Hive. When you are in the Script Editor, click the "Debug" button, and you can go step by step in the script reload.

Re: Incremental load in qlikview from hive

Brice-SACCUCCI — Thu, 26 Jun 2014 15:57:56 GMT

I'm just taking a guess here... Have you tried something like

WHERE data >= '$(myVariable)' ?

Re: Incremental load in qlikview from hive

Anonymous — Thu, 26 Jun 2014 16:31:13 GMT

You can use the Debug option, or you could trace out the SQL on reload, whichever way is easiest really, with the traced SQL you should spot any anomalies.

The problem we experience is getting the max ID/timestamp from the QVD when we are incrementally updating it as we have to pull in all 30million records, as the performance is pretty bad.

To solve this we shard QVDs with records greater than 2 million rows into QVDs with 1 million rows in each one, so for a 30 million record QVD file we now have 30 x 1 million records in each one, this makes it quicker to get the max ID from 1 million records rather than 30 million.

Each table that we pull in has its own QVS file which is used within a loop that works out what sort of QVD we are building.

Re: Incremental load in qlikview from hive

Fri, 27 Jun 2014 09:48:44 GMT

I am using the WHERE datefiled >= '$(myVariable)' approach for filtering data from hive tables.

But the problem i am getting is the date field in the hive table is in string format ,so when i am debugging the script the variable is showing null,as it is not able to compare the records from qvd with hive.

Could anyone please help on date conversion from sting to date and timestamp to date,because i need to compare records based on date and not timestamp.

Thanks

Hari

Re: Incremental load in qlikview from hive

Anonymous — Fri, 27 Jun 2014 10:23:18 GMT

Whats the date string, can you give example

Re: Incremental load in qlikview from hive

fosuzuki — Fri, 27 Jun 2014 12:27:47 GMT

when i am debugging the script the variable is showing null

Can you post the code where you define the content of myVariable?

Re: Incremental load in qlikview from hive

Fri, 27 Jun 2014 13:44:26 GMT

LOAD date(max(eventdate),'YYYY-MM-DD') as notemaxdate

FROM QvdFile (qvd);

LET vNoteMaxDate = peek('notemaxdate');

SQL SELECT * FROM HIVE.qvdb.eventstable

WHERE eventdate >= '$(vNoteMaxDate)';

vNoteMaxDate is a variable which contains maximum eventdate from qvd.

eventdate is the date field in hive table whose data type is string and format is DD/MM/YYYY

while debugging i am getting like WHERE eventdate >= '';

Thanks

Hari

Re: Incremental load in qlikview from hive

Anonymous — Fri, 27 Jun 2014 13:49:17 GMT

Hi Goli

Looking at your code, the syntax of your peek function is incorrect,

LET vNoteMaxDate = Peek('notemaxdate', -1, 'xx--table-name-xx');

Hope that works

Re: Incremental load in qlikview from hive

fosuzuki — Fri, 27 Jun 2014 14:34:23 GMT

What is the result of:

LOAD date(max(eventdate),'YYYY-MM-DD') as notemaxdate

FROM QvdFile (qvd);

Is it correctly generating the table with the max date?

Re: Incremental load in qlikview from hive

Fri, 27 Jun 2014 14:43:18 GMT

I debugged it ,

LOAD date(max(eventdate),'YYYY-MM-DD') as notemaxdate

FROM QvdFile (qvd);

LET vNoteMaxDate = peek('notemaxdate');

the value of vNoteMaxDate is showing as null

and eventdate values in qvd are something like 2010-07-10-05.22.27.12142, 2011-05-14-06.636490

Re: Incremental load in qlikview from hive

Anonymous — Fri, 27 Jun 2014 14:47:48 GMT

What did you do to resolve this issue ?

Could you please post you findings.

Many thanks

DaveT

Re: Incremental load in qlikview from hive

fosuzuki — Mon, 30 Jun 2014 17:00:50 GMT

If the eventdate is a string in the database, I guess you have to convert it to a date before using the max() function. So, try this:

LOAD Max(Date#(eventdate, 'YYYY-MM-DD-h.mm.ss.fffff')) AS notemaxdate

Re: Incremental load in qlikview from hive

Wed, 02 Jul 2014 07:14:48 GMT

Hi,

1.I resolved the issue ,i am taking the timestamp in in hive in 2010-05-15-05.12.48.628010 format.

I used below condition to get maximum record from QVD.

IF $(vQvdExists) THEN

maxdateTab:

// Get the max date from this QVD for use in incremental SELECT

LOAD date(max(moddate),'YYYY-MM-DD') as maxdate

FROM $(vQvdFile) (qvd);

LET vIncrementalExpression = peek('maxdate');

ENDIF

2.I used below script in compare date in hive with date from QVD and fetch new records.

$(vTableName):

Directory;

IF $(vQvdExists) THEN

SQL SELECT id,moddate

FROM HIVE.qvdb.test

WHERE date(moddate,'YYYY-MM-DD') > '$(vIncrementalExpression)';

else

SQL SELECT id,moddate

FROM HIVE.qvdb.test;

ENDIF

To concatenate the newly fetched records from hive to qvd, i am using the below script.

Directory;

// If incremental reload was , load previous data and concatenate to data just read.

IF $(vQvdExists) THEN

// Use CONCATENATE in case we've added any new fields.

CONCATENATE ($(vTableName)) LOAD * FROM $(vQvdFile) (qvd);

WHERE NOT exists($(vPK)) // Load only QVD rows that were not already loaded in the data load.

END IF

With the above script i am able to load only the records with new primary key or the records with updated timestamp.

Thanks

Hari

Re: Incremental load in qlikview from hive

Mon, 07 Jul 2014 14:10:09 GMT

Hi,

1.I resolved the issue ,i am taking the timestamp in in hive in 2010-05-15-05.12.48.628010 format.

I used below condition to get maximum record from QVD.

IF $(vQvdExists) THEN

maxdateTab:

// Get the max date from this QVD for use in incremental SELECT

LOAD date(max(moddate),'YYYY-MM-DD') as maxdate

FROM $(vQvdFile) (qvd);

LET vIncrementalExpression = peek('maxdate');

ENDIF

2.I used below script in compare date in hive with date from QVD and fetch new records.

$(vTableName):

Directory;

IF $(vQvdExists) THEN

SQL SELECT id,moddate

FROM HIVE.qvdb.test

WHERE date(moddate,'YYYY-MM-DD') > '$(vIncrementalExpression)';

else

SQL SELECT id,moddate

FROM HIVE.qvdb.test;

ENDIF

To concatenate the newly fetched records from hive to qvd, i am using the below script.

Directory;

// If incremental reload was , load previous data and concatenate to data just read.

IF $(vQvdExists) THEN

// Use CONCATENATE in case we've added any new fields.

CONCATENATE ($(vTableName)) LOAD * FROM $(vQvdFile) (qvd);

WHERE NOT exists($(vPK)) // Load only QVD rows that were not already loaded in the data load.

END IF

With the above script i am able to load only the records with new primary key or the records with updated timestamp.

Thanks

Hari

Re: Incremental load in qlikview from hive

Mon, 07 Jul 2014 14:12:34 GMT

Hi,

1.I resolved the issue ,i am taking the timestamp in in hive in 2010-05-15-05.12.48.628010 format.

I used below condition to get maximum record from QVD.

IF $(vQvdExists) THEN

maxdateTab:

// Get the max date from this QVD for use in incremental SELECT

LOAD date(max(moddate),'YYYY-MM-DD') as maxdate

FROM $(vQvdFile) (qvd);

LET vIncrementalExpression = peek('maxdate');

ENDIF

2.I used below script in compare date in hive with date from QVD and fetch new records.

$(vTableName):

Directory;

IF $(vQvdExists) THEN

SQL SELECT id,moddate

FROM HIVE.qvdb.test

WHERE date(moddate,'YYYY-MM-DD') > '$(vIncrementalExpression)';

else

SQL SELECT id,moddate

FROM HIVE.qvdb.test;

ENDIF

To concatenate the newly fetched records from hive to qvd, i am using the below script.

Directory;

// If incremental reload was , load previous data and concatenate to data just read.

IF $(vQvdExists) THEN

// Use CONCATENATE in case we've added any new fields.

CONCATENATE ($(vTableName)) LOAD * FROM $(vQvdFile) (qvd);

WHERE NOT exists($(vPK)) // Load only QVD rows that were not already loaded in the data load.

END IF

With the above script i am able to load only the records with new primary key or the records with updated timestamp.

Thanks

Hari