SQLP to support empty fields

[ondohotola]

As part of my recent project, I had to add 73 fields to a table, some of which have an underscore _ and/or a star * and the output must be delimited by semicolon ;. The table can have anywhere from a few thousand to a few million records.

Using enum 73 times and then piping to fmt -d';' proved to be a bottleneck.

When I try something like qsv sqlp "select '' as test, 'abc' as placeholder, *, '' as sss from data" I get Invalid arguments.

So duckdb to the rescue which is plain twice as fast as the enum pipeline.

Since I would like to push the envelope on this I'd love to see this done within SQLP.

[jqnatividad]

If I understand your problem correctly, you have a CSV with 73 fields, with special characters and you want to return an SSV.

Also, I believe empty fields are not valid SQL.

Have you tried something like this pipeline?

# rename headers with generic column names (_col_N)
qsv rename _all_generic data.csv
qsv sqlp "select _col_1 as test, _col_2 as placeholder ... from data" data.csv --delimiter ; -o output.ssv 

[ondohotola]

Apologies for confusing

Actually I generate in earlier steps a table with 13 columns (fqdn and ns1 to ns12) which are comma separated.

To this I have to add 69 (sorry for the miscalc, but the principle applies) columns of specified names, in a particular order.

SELECT '' as a, '' AS b, fqdn, '' AS c, ns1, ns2 , 
   ...,
   ns12, '' as d, 
   ... ,
   '' AS lastcolumn FROM data;

in other words I want to enum 69 additional columns.

In csvq I can do it as well as duckdb which is, however, much faster.

Unfortunately duckdb doesn't like names with '_' or '*' ie something like

COPY(
   SELECT '' as a, '' AS b_1, fqdn, '' AS c*, ns1, ns2 , 
   ...,
   ns12, '' as d, 
   ..., 
   '' AS lastcolumn FROM data
) to '/dev/stdout' (HEADER, DELIMITER ',');

bombs, escaping doesn't help, so I have to use column names without these offending characters and then pipe it into something like

HEADERS="first,second_,fqdn,c*,ns1,ns2,...,lastcolumn`
duckdb ... | qsv rename "$HEADERS" | qsv fmt -d ';'

[jqnatividad]

@ondohotola , have you tried using null as instead of '' as?

SELECT null as a, null as b, fqdn, null as c, ns1, ns2 , 
   ...,
   ns12, null as d, 
   ... ,
   null as lastcolumn FROM data;

BTW, converted the issue to a discussion so other folks with similar questions can find it...

[ondohotola]

That works, thank you very much. Shaves a second off of the 'duckdb' version at a Million records.