Another SQLP question

[ondohotola]

As part of m current little project (see #2425) I had to resort to sqlite3 where I can do something like

-- Create the table in memory
CREATE TABLE data (FQDN TEXT, HOST TEXT, ns TEXT, ip TEXT);

-- Import the CSV into the table
.mode csv
.import '$CSVFILE' data

-- Assign unique indices to each ip for every FQDN
CREATE TABLE numbered AS
SELECT 
    FQDN,
    HOST || '|' || ip AS host_ip,
    ip,
    (SELECT COUNT(*) 
     FROM data d2 
     WHERE d2.FQDN = d1.FQDN AND d2.rowid <= d1.rowid) AS ip_index
FROM data d1;

-- Turn headers back on for clean output
.headers on
.mode csv
.nullvalue ''

-- Pivot the data into 12 columns, using NS1-NS12 as column names
SELECT 
    FQDN,
    MAX(CASE WHEN ip_index = 1 THEN host_ip END) AS NS1,
    MAX(CASE WHEN ip_index = 2 THEN host_ip END) AS NS2,
    MAX(CASE WHEN ip_index = 3 THEN host_ip END) AS NS3,
    MAX(CASE WHEN ip_index = 4 THEN host_ip END) AS NS4,
    MAX(CASE WHEN ip_index = 5 THEN host_ip END) AS NS5,
    MAX(CASE WHEN ip_index = 6 THEN host_ip END) AS NS6,
    MAX(CASE WHEN ip_index = 7 THEN host_ip END) AS NS7,
    MAX(CASE WHEN ip_index = 8 THEN host_ip END) AS NS8,
    MAX(CASE WHEN ip_index = 9 THEN host_ip END) AS NS9,
    MAX(CASE WHEN ip_index = 10 THEN host_ip END) AS NS10,
    MAX(CASE WHEN ip_index = 11 THEN host_ip END) AS NS11,
    MAX(CASE WHEN ip_index = 12 THEN host_ip END) AS NS12
FROM numbered
WHERE FQDN != 'FQDN'
GROUP BY FQDN;

When I issue qsv sqlp data.csv t.sql with t.sql having:

CREATE TABLE numbered AS
SELECT 
    FQDN,
    HOST || '|' || ip AS host_ip,
    ip,
    (SELECT COUNT(*) 
     FROM data d2 
     WHERE d2.FQDN = d1.FQDN AND d2.rowid <= d1.rowid) AS ip_index
FROM data d1;

I get this error message

Failed to execute query: CREATE TABLE numbered AS
SELECT
    FQDN,
    HOST || '|' || ip AS host_ip,
    ip,
    (SELECT COUNT(*)
     FROM data d2
     WHERE d2.FQDN = d1.FQDN AND d2.rowid <= d1.rowid) AS ip_index
FROM data d1: unexpected subquery

[ondohotola]

Switched to duckDB, and ran this query (the bash variables equate to CSV files>

COPY(
   SELECT fqdn, 
   MAX(CASE WHEN row_num = 1 THEN host END) AS NS1,
   MAX(CASE WHEN row_num = 2 THEN host END) AS NS2, 
   MAX(CASE WHEN row_num = 3 THEN host END) AS NS3, 
   MAX(CASE WHEN row_num = 4 THEN host END) AS NS4,
   MAX(CASE WHEN row_num = 5 THEN host END) AS NS5,
   MAX(CASE WHEN row_num = 6 THEN host END) AS NS6,
   MAX(CASE WHEN row_num = 7 THEN host END) AS NS7,
   MAX(CASE WHEN row_num = 8 THEN host END) AS NS8,
   MAX(CASE WHEN row_num = 9 THEN host END) AS NS9,
   MAX(CASE WHEN row_num = 10 THEN host END) AS NS10,
   MAX(CASE WHEN row_num = 11 THEN host END) AS NS11,
   MAX(CASE WHEN row_num = 12 THEN host END) AS NS12,
   FROM 
   (
      SELECT fqdn, host, ROW_NUMBER() 
      OVER (PARTITION BY fqdn ORDER BY host) AS row_num 
      from '$GLUEIN'
   ) Z
   group by Z.fqdn
   order by Z.fqdn
) TO '$GLUEOUT' (HEADER, DELIMITER ',');

works great but when I tried to run it (sans the COPY of course) under qsv sqlp it tells me it doesn't have a ROW_NUMBER() feature.

Is there a way around that, at present?

I really would love to see how fast this can get :-)-O

[jqnatividad]

I'm afraid ROW_NUMBER() is still in Polar's SQL interface backlog

pola-rs/polars#16968

I can imagine using a combination of the partition and enum commands to prepare the equivalent of your subselect, and then taking advantage of sqlp's script capabilities.

But TBH, that's too much work and if duckDB is working already, I'd stay with it and use qsv for other data-wrangling that duckDB is not able to do.

That's why composability is one of qsv's goals. 😉