Re: [Virtuoso-users] Dump arbitrary query result as N-Triples

[Claus Stadler]

Hi,

Thanks for your reply. I was able to get it working.
I am posting my script here for reference - variables could be renamed 
to something more human readable, but at least it is capable of dumping 
huge result sets (multiple GB). *

Bascially you pass in a sparql select query that has 3 variables in its 
projection (there is no sanity check in place if you do it wrong ;)

Examle Usage: Only dump typed things:
dump_query_nt('Select ?s ?p ?o { ?s a ?c . ?s ?p ?o . }', '/tmp/result');


* Concerning the option with the following isql command I mentioned in 
my previous mail:
 ./isql 1111 dba dba "EXEC=Set Blobs On; Sparql define 
output:format'NT' Construct {?s ?p ?o .} {?s ?p ?o .};"

It works on the commercial version, but OSE 6.1.2 gives:
*** Error 37000: [Virtuoso Driver][Virtuoso Server]SQ074: Line 1: SP031: 
SPARQL generic error: Unsupported format name 'NT'

Also, this statement gives an incomplete result set (not even 1MB, when 
multiple GBs are expected). I noticed that with the command above, isql 
does not simply start printing out the results, so it seems as if isql 
is trying to hold the data in memory e.g. in order to format it nicely, 
and fails silently on some occasion. Maybe this is a known issue with a 
simple workaround which would make my script obsolete ;) ?


Here my adapted dump script:

drop procedure dump_query_nt;

create procedure dump_query_nt(in query varchar, in out_file varchar, in 
file_length_limit integer := -1)
{
    declare file_name varchar;
    declare env, ses any;
    declare ses_len, max_ses_len, file_len, file_idx integer;
    declare state, msg, descs any;
    declare chandle any;
    declare sub any;
    declare sql any;
    set isolation = 'uncommitted';
    max_ses_len := 10000000;
    file_len := 0;
    file_idx := 1;
    file_name := sprintf ('%s-%06d.nt', out_file, file_idx);
    string_to_file (file_name || '.query', query, -2);
    env := vector (0, 0, 0);
    ses := string_output ();


    state := '00000';
    sql := sprintf('sparql define input:storage "" %s', query);

    exec(sql, state, msg, vector (), 0, descs, null, chandle);
    if (state <> '00000') {
        signal (state, msg);
    }

    while(exec_next(chandle, state, msg, sub) = 0) {
        if (state <> '00000') {
            signal (state, msg);
        }

        http_nt_triple (env, sub[0], sub[1], sub[2], ses);
        ses_len := length (ses);

        if (ses_len > max_ses_len) {
            file_len := file_len + ses_len;
            if (file_length_limit >= 0 and file_len > file_length_limit) {
                string_to_file (file_name, ses, -1);
                file_len := 0;
                file_idx := file_idx + 1;
                file_name := sprintf ('%s-%06d.nt', out_file, file_idx);
                env := vector (0, 0, 0);
            }
            else {
              string_to_file (file_name, ses, -1);
            }

            ses := string_output ();
        }
    }
    if (length (ses)) {
        string_to_file (file_name, ses, -1);
    }

    exec_close(chandle);
};