Generating a QVX file with block layout

[MarcB]

Hi,

I'm currently developing a 3rd-party QVX generator from an Oracle Database (PL/SQL language).

Everything's fine so far, following the official QVX file format reference.

To be complete, I'd like to implement Block structure too but got a hard time trying to understand the expected layout.

The doc says :

UsesSeparatorByte

Flag specifying if extra byte for separating records is used. When record separator is used: Record separator byte RS (0x1E) is written before every record. File separator byte FS(0x1C) is written after the last record and indicates the end of file (EOF). Null byte NUL(0x0) is used to pad to the next block boundary (used when BlockSize is given). Record separators have to be used together with BlockSize, otherwise their use is optional.

BlockSize

The specified block size (>1, _int64) together with  UsesSeparatorByte define a block structure to store formatted data. The use of block structure is optional and enables parallel load of the data. Blocks, except of the first one, are aligned to the whole file.

Adding RS and FS is trivial, however there's nothing about how records (or fields) must be laid out within a block.

In particular :

• Can a block boundary occur in the middle of a record? of a field? Or does a block only contain whole (complete) records?
• If the latter, what about large field (e.g. CLOB) that itself may exceed block size?
• How to check whether the file is actually loaded in parallel (to somehow validate the generated structure)?

What I observed so far :

• QVX load still works after adding RS and FS and specifying an arbitrary block size, so I must be missing something
• Filling blocks with whole records only (and NUL padding to block size) works too, but if I manually modify the declared BlockSize (in header) without altering the data structure, it continues working...

Has anyone ever succeeded in implementing the block layout?

Thanks in advance.

Marc.

(Using Qlikview Desktop 12.40)

[Brett_Bleess]

Hey Marc, looks like you have been doing your homework here, let me see if I can find the right person to have a look at things for you, may take a few days with the holidays and vacations etc., which I know may be a bit of a problem, but I will see what I can do.

Regards,
Brett

[Brett_Bleess]

Hey Marc, apologies, I was out a couple of weeks, and I realized I have not heard back from the person I reached out to, so I have shouted out to someone else who I hope may be able to help, or get us to the right person.  Sorry for the delay.  Oh, SR2 is out for 12.40 too, just FYI.  I am doubtful it would fix anything, but I wanted to let you know.

Regards,
Brett

[Brett_Bleess]

Marc, I think I may have found the right person to have a look at things, just a matter of him having a few minutes to come have a look, just wanted to leave you a quick update.

Regards,
Brett

[MarcB]

For those interested, here the first version of my PL/SQL QVX generator :

https://github.com/mbleron/Qlik/tree/master/oracle

Sample usage :

begin
  
  xutl_qvx.createFile(
    p_directory => 'QVX_DIR'
  , p_filename  => 'emp.qvx'
  , p_query     => 'select * from scott.emp'
  , p_tablename => 'SCOTT.EMP'
  );
  
end;
/

After loading in QlikView :

(Attached : emp.qvx)

[Brett_Bleess]

Hey Marc, sorry for tardy reply here, but I wanted to be sure you saw the new IDEA area on Community:

https://community.qlik.com/t5/Ideas/idb-p/qlik-ideas

This might be the better option on this at this point to go post a request there for further work/update to things, that way other folks can vote on it, and if enough people are interested, that should help drive things.

Regards,
Brett