Applying Pointblank to Large Datasets

[phdjsep]

Hello! 👋

I'm really inspired by what I'm seeing with pointblank and I really appreciate all the documentation and videos surrounding it. I have a question on if pointblank can be used on large datasets in a cloud database (ex, Redshift)? The documentation and videos I've come across are all on smaller datasets (understandably), but could pointblank run on a table with four billion rows? Not sure I understand how it's working under the hood so any insight on scenarios like the above would be appreciated. 🙏

[rich-iannone]

@phdjsep Thanks for checking out the package! Database validation was actually the original use case for the package (for personally querying analytics data on Redshift). The queries that pointblank generates are pretty simple and run entirely on the database side (and very little data is collected back on the client side). The queries are translated/generated by dbplyr and there are even some variants (for certain functions, like col_vals_regex()) that are written differently for specific backends. My recommendation is to try out a simple agent validation, like perhaps col_vals_gt(), on a single column and see how long it takes before scaling up.

If you do run tests on a 4B row table, I definitely wouldn't mind hearing about the timings you get!

[phdjsep]

Sorry for the spam...

Waited long enough and received an error:

Error in `vec_as_location2_result()`:
! Can't extract columns that don't exist.
x Column `DATA_TYPE` doesn't exist.

[luke-slipski]

@rich-iannone I am having the exact same issue as above. I am able to successfully connect to my redshift DB using dplyr::tbl(). I am also able to successfully add the table and derivatives to a pointblank tbl_store and retrieve the tables using tbl_get. However, when I ran col_is_character(vars(a)) and create_agent() it took a few minutes to return the following.

Error: Expecting a single string value: [type=character; extent=0].

Any ideas on what to try next to get this working? Thanks!!

[rich-iannone]

Something is off and this should be filed as an issue. I used to have access to a redshift DB in a previous work role but I no longer do. This makes it a little difficult to test any fixes but I'll see what I can do w.r.t. access. Could you @luke-slipski (since you're the latest) open an issue with an MRE? I know this used to work just fine so there might be a regression here.

[rich-iannone]

Also, could you try this by using RPostgres package instead of odbc just to see if there is a difference? That's what I used earlier on in development.

[pachadotdev]

@rich-iannone, I found the same issue today. The problem is that https://github.com/rstudio/pointblank/blob/main/R/utils.R#L784 gives an empty string and then it is not possible to get https://github.com/rstudio/pointblank/blob/main/R/utils.R#L798 correctly

[pachadotdev]

here is a MWE with a public db that I just updated

library(RPostgres)
library(dplyr)

devtools::load_all()

# Remote connection
con <- dbConnect(
  Postgres(),
  dbname = "intendo",
  host = "databases.pacha.dev",
  port = 5432,
  user = Sys.getenv("dbedu_usr"),
  password = Sys.getenv("dbedu_pwd")
)

d = tbl(con, "revenue_small") %>%
  select(player_id, country_id)

col_is_integer(d, country_id)