Executing SQL

[krlmlr]

Hi, thank you so much for creating this package. You won't believe how long I've been waiting for something like duckplyr, experimenting with data.table, databases (do you remember Monet.DB?), and even Apache Spark. I started building science tools on top of ducklpyr already, and I'm extremely glad the way it turned out. Fantastic job!

I want to continue this issue discussion to make sure I clearly understand this duckplyr feature status as of today. My use case is relatively straightforward – the duckplyr-based data structure should have a feature to find similar strings stored in it using a user-provided vector of target strings, both regex and hamming/Levenshtein distance.

Please correct me if I'm wrong, but as of today, it is possible only via running two compute(name=...) -s, one for the source data, one for the target data, and then executing read_sql_duckdb("execute levenshtein SQL code here"). Non-reproducible example:

source_table <- my_data_structure_on_duckplyr |>
  compute(name = "source_table")

pattern_vec <- c("ASSSRDALNIQY", <more patterns>)
pattern_table <- duckdb_tibble(pattern = pattern_vec) |> 
  compute(name = "pattern_table")

query <- "
  SELECT
    s.seq_id,
    s.sequence,
    p.patterns,
    levenshtein(s.sequence, p.pattern) AS edit_distance
  FROM source_table s
  CROSS JOIN pattern_table p
  WHERE edit_distance <= <threshold>
"

found_matches <- read_sql_duckdb(query)

# do some joins with the original `my_data_structure_on_duckplyr` in order to get the found `seq_id`

Another possible use case is concatenating several string columns together to form a new column.

If this is correct, I'm a bit worried about the data duplication due to the first compute(name = "source_table"). Does #650 aim to fix this? I personally would be more than fine if I could execute an arbitrary SQL code without copying the data.

How would you remove those "temporary" tables from the duckdb instance from the current duckplyr-based session? Again, I would be more than satisfied if I could just connect to the duckdb instance and do some housekeeping.

Thank you again (:

Originally posted by @vadimnazarov in #67

[krlmlr]

Thanks. In #650 and #634, as_tbl() will create views from relational objects. Faster than compute(), fewer worries about stale objects (aiming to clean them up when they go out of scope). These are about to happen, I'll ping you for review if I don't forget.

[vadimnazarov]

Hi Kirill, thank you for your work. I sincerely appreciate it. I see several new tags; could I test them or wait for a specific (pre-)release?

[krlmlr]

Thanks. The mainline should be fine, CI/CD is quite rigorous (but not perfect, of course).