0

I have a DuckDB with columns of data which I would like to query using multiple columns. I'm in R but I'm not sure how to create a multicolumn index (or even a single column index). Can anyone suggest a reference please? I've added SQLite as a tag because I gather that the commands could be the same.

Edit: Based on kukuk1de's recommendation I'm trying the following

require(DBI)
require(duckdb)

DBI::dbExecute(con,statement = "CREATE INDEX multi_idx ON  (percent prevalence fresh_flow maskProp dropExhale)")

but I get the following error:

Error in .local(conn, statement, ...) : 
  duckdb_prepare_R: Failed to prepare query CREATE INDEX multi_idx ON  (percent prevalence fresh_flow maskProp dropExhale)
Error: Parser Error: syntax error at or near "("
LINE 1: CREATE INDEX multi_idx ON  (percent prevalence fresh_flow maskProp...
HCAI
  • 2,213
  • 8
  • 33
  • 65
  • Suspecting you are using the R package `duckdb` you can connect to your DB and send an CREATE INDEX statement via `dbExecute`. See basic examples here https://duckdb.org/docs/api/r For indexes go here https://duckdb.org/docs/sql/indexes – kukuk1de Feb 26 '21 at 07:22
  • Ahhh thank you. I didn't realise what dbExecute was for. I was looking at the r API section and wasn't thinking outside the box. If you could write it as an answer with an example please, I'd be happy to accept. – HCAI Feb 26 '21 at 08:41

1 Answers1

2

Try this:

library("DBI")

con = dbConnect(duckdb::duckdb(), dbdir=":memory:", read_only=FALSE)

dbExecute(con, "CREATE TABLE items(item VARCHAR, value DECIMAL(10,2), count INTEGER)")
dbExecute(con, "INSERT INTO items VALUES ('jeans', 20.0, 1), ('hammer', 42.2, 2)")

dbExecute(con, "CREATE INDEX itemcount_idx ON items (item, count);")

Running the last command again will tell you the index already exists.

dbExecute(con, "CREATE INDEX itemcount_idx ON items (item, count);")
Error in duckdb_execute(res) : duckdb_execute_R: Failed to run query
Error: Catalog Error: Index with name "itemcount_idx" already exists!
kukuk1de
  • 386
  • 1
  • 12
  • Thank you very much, that has worked! It's not changed the GB weight of the DB file so how can I tell that it is working during querying? – HCAI Feb 26 '21 at 10:24
  • Good question. dbGetQuery(con, "PRAGMA show_tables;") shows the index visible as a separate table. These are details I'm not familiar with for duckdb. Probably you can get support on this on duckdb.org or the github page for duckdb. – kukuk1de Feb 26 '21 at 11:16