SQL UPDATE

[ondohotola]

Given two files which should but are not guaranteed to be of equal lengths as they are generated after another (DomainsInclude.csv and DomainsExclude.csv) I need to set a column eppstatus in the one to the value of same in the other.

The following CSVQ command works but is of course extremely slow with 20000 lines (with or without the ORDER):

UPDATE DomainsInclude 
SET eppstatus = (
   SELECT DomainsExclude.eppstatus 
   FROM DomainsExclude 
   WHERE DomainsInclude.name = DomainsExclude.name
   ORDER BY DomainsExclude.name
);
COMMIT;

Is there a qsv pipeline to do this (so I can test for speed)?

sqlp does not seem to have such an update command.

[jqnatividad]

Hi @ondohotola , I can think of two solutions:

1. sqlp with joinp:

First, do the SELECT with sqlp:

qsv sqlp DomainExclude.csv DomainsInclude.csv \
  "select DomainsExclude.name, DomainsExclude.appstatus \
   FROM DomainsExclude, DomainsInclude \
   WHERE DomainsInclude.name = DomainsExclude.name \
   ORDER BY DomainsExclude.name" \
  --output DE_appstatus.csv

and then joinp it with DomainsInclude

qsv joinp name DomainsInclude.csv name DE_appstatus.csv --output joined_with_appstatus.csv

2. create a SQL script and do it with one sqlp operation..

Create a SQL script named Domains.sql:

-- first, create a table from the select
CREATE TABLE DomainsExclude_AppStatus as 
SELECT DomainsExclude.name, DomainsExclude.appstatus
 FROM DomainsExclude, DomainsInclude
 WHERE DomainsInclude.name = DomainsExclude.name
 ORDER BY DomainsExclude.name;

-- then we do the join with the just created table
SELECT *
FROM DomainsInclude JOIN DomainsExclude_AppStatus
ON DomainsInclude.name = DomainsExclude_AppStatus.name;

and then run it with sqlp:

qsv sqlp DomainsExclude.csv DomainsInclude.csv Domains.sql --output Domains_Appstatus.csv

Let me know if either of this works and how fast it is compared with CSVQ.

[ondohotola]

option 1 takes seconds :-)-O (`csvq` takes 3 minutes) thank you very much

[jqnatividad]

Wow! That's blazing-fast!!! 🚀 And it's really more a testament to the power of Polars' LazyFrame expressions.

Anyways, Solution 2 should be at least as fast, if not faster because you don't have to call qsv twice.

Here's a simplified SQL script to try which I know works because I tried it on dummy data.

Create a file named DI_status.sql

CREATE TABLE DE_AppStatus as 
  SELECT DE.name, DE.appstatus 
   FROM DE, DI
   WHERE DI.name = DE.name
   ORDER BY DE.name;

SELECT *
  FROM DI join DE_AppStatus
  ON DI.name = DE_AppStatus.name;

And run it using:

$ qsv sqlp DI.csv DE.csv DI_status.sql

[derekmahar]

How did you generate your dummy data?

[ondohotola]

The simplified script is actually Option 2 which is the fastest (ie 0.3 seconds for 19000 lines).

I am really impressed.

[derekmahar]

How long does it take the simplified script to run in DuckDB?

[jqnatividad]

Hi @derekmahar ,

The dummy data was just a few lines:

DE.csv
DI.csv

Basically, just having enough data to retrieve appstatus from DomainsExclude.appstatus to join with DomainsInclude.

[ondohotola]

Ok, will measure later in the PM.

[derekmahar]

Can you share any sample data files?

[ondohotola]

No, NDA

[derekmahar]

What about column headers with obfuscated, fake, or random data?

[derekmahar]

Have you tried DuckDB just to see how its performance compares to that of csvq and qsv sqlp?

[ondohotola]

Too much work, almost 100 columns and almost all of them GDPR'ed.

[jqnatividad]

One more reason I should work on the fake command - #235 😉

fake scans your data, compile stats and frequency tables on it, and use that metadata to generate fake data that has the same schema and similar summary statistics - but using faker keywords for certain field classes to ensure privacy.

[ondohotola]

Great idea

[ondohotola]

Can you propose some command?

[jqnatividad]

Great stuff!

You may want to look into using hyperfine - a CLI tool purpose made for benchmarking.

Its statistical analysis should help tease out the times introduced by running bash, especially at these sub second response times.

[derekmahar]

Thank you for the tip about hyperfine. Given that each test script invokes bash, how does hyperfine exclude the start-up time that bash imposes?

[jqnatividad]

Because it can do warm-ups, do multiple runs, and clear the cache - it should help establish a common/fair baseline for any "noise" introduced by bash/system IO/operating system stuff.
It will also allow you to compare multiple commands side-by-side.

[derekmahar]

Are you referring to the intermediate shell that hyperfine invokes? I'm referring to the bash shell that each of my test scripts invoke since most of the scripts invoke multiple commands.

[derekmahar]

Alternatively, I could define my tests as shell functions and export these into the current shell where hyperfine can find them.

[ondohotola]

didn't try

[derekmahar]

It's still good to know that qsv sqlp is holding its own compared to these tools with a minimum of preparation - right from the command-line with one command, without importing the data first into a database.

Two commands if you count qsv sqlp and qsv joinp.

[jqnatividad]

Currently, you can't. It just returns all the columns. Polars is just smart enough to add the "_left", "_right" suffix if there are duplicate column names.

I was thinking folks can just use qsv select afterwards to remove the unwanted columns.

But yeah, I'll be sure to add output column selection to the backlog

[jqnatividad]

Actually @derekmahar , there is no specific command-line option to select the result columns.

It just returns the columns based on the LAST query in the SQL script we used. And since we did "SELECT *", it returned all the columns of the JOIN query, with the "_right" suffix to differentiate duplicate columns.

You can limit the columns by changing the SELECT. You can even do "SELECT colname AS othername" to rename columns.

[derekmahar]

Are you referring to qsv sqlp? I was referring to qsv joinp.

[jqnatividad]

My mistake... I was thinking sqlp - joinp effectively does SELECT * by default...

which gives me an idea how to enable column selection for joinp - filter the result using a SQLcontext.... 😉

[jqnatividad]

And joinp now has the --sql-filter option.

#1287

[ondohotola]

That _right is rather helpful, actually.

[ondohotola]

It might be interesting to know that since sqlp doesn't do subqueries I have used CSVQ with something like this

SELECT
   DISTINCT clients.Name as `COMPANY NAME`,
   clients.`Client ID` as `Client Identifier`,
   clients.`E-mail` as `Main E-mail`,
   "no" as `Connect via EPP`,
   clients.Country as Country,
   clients.Phone as Phone,
   clients.`Admin Contact` as `Administrative Contact`,
   clients.`E-mail` as `Administrative E-mail`
FROM
   Clients clients
WHERE
   EXISTS ( SELECT 1
            FROM DomainsInclude domains
            WHERE clients.`Client ID` = domains.`registrar_id` )
ORDER BY
   clients.Name,
   clients.`Client ID`

which on a small clients.csv and the 19000 DomainsInclude.csv file takes 5 seconds.

Last night I was bored and figured out joinp and am pleased to report that this takes 200 milliseconds

[derekmahar]

What joinp command did you use? sqlp can perform join operations, too.

[jqnatividad]

Actually, sqlp supports Common Table Expressions @ondohotola ...

WITH millennials AS (SELECT * FROM people WHERE age >= 25 and age <= 40) \
    SELECT * FROM millennials WHERE owns_home = 'N'

qsv sqlp people.csv "WITH millennials AS (SELECT * FROM people WHERE age >= 25 and age <= 40) \
    SELECT * FROM millennials WHERE owns_home = 'N'"

Myself, I actually prefer using SQL scripts with multiple SQL statements with sqlp instead of CTEs/subqueries. It's easier to troubleshoot and allows you to break down a complex query.

It also has the added benefit of being able to DROP temporary tables if you're working with very large CSV files as everything is done in-memory by sqlp. Though it can do streaming operations, its always better to have plenty of memory headroom.

-- first, create a table from the select
CREATE TABLE DomainsExclude_AppStatus as 
SELECT DomainsExclude.name, DomainsExclude.appstatus
 FROM DomainsExclude, DomainsInclude
 WHERE DomainsInclude.name = DomainsExclude.name
 ORDER BY DomainsExclude.name;

-- then we do the join with the just created table
CREATE TABLE ResultTable as 
 SELECT *
  FROM DomainsInclude JOIN DomainsExclude_AppStatus
  ON DomainsInclude.name = DomainsExclude_AppStatus.name;

-- drop DomainsExclude_AppStatus
-- all the tables created during the script are automatically dropped at the end
-- but you can manually drop tables inside the script to free up memory
DROP TABLE DomainsExclude_AppStatus;

-- Group and sort the results
SELECT category, count(*) as category_cnt, *
 FROM ResultTable
 GROUP BY category
 ORDER BY category_cnt desc, category asc;

[ondohotola]

something like

qsv joinp -Q --left-semi 'Client ID' Clients.csv registrar_id DomainsInclude.csv

as the column has a space in ot I am struggling with SQL, but this is clean and quick

[derekmahar]

You can refer to a column name that contains a space by enclosing the column name in double quotes:

$ cat example_1.csv
Column 1,Column 2
a,1
b,2
c,3
$ qsv sqlp --quiet example_1.csv 'SELECT "Column 2", "Column 1" FROM example_1'
Column 2,Column 1
1,a
2,b
3,c