How to list more than 50 tables with bigQueryR::bqr_list_tables?

1
votes

I am using the package bigQueryR, and in particular the function bqr_list_tables to get a list of all tables in a dataset in Google Big Query.

My issue is I only get 50 tables when I would ideally like to get them all so I can regex out the ones I want programmatically.

bqr_list_tables only takes two arguments, the datasetId and projectId. Is there any way to accomplish this restricting myself to this package?

I'm using version ‘0.2.0’ of bigQueryR

Edit:

enter image description here

1
rgoogle-bigquery
BigQuery's API itself has a parameter maxResults, which defaults to 50. I'm not sure if it's exposed in the bigQueryR lib though. cloud.google.com/bigquery/docs/reference/rest/v2/tables/list. Do you know @MarkeD? – Graham Polley
I looked at the github repo here github.com/cloudyr/bigQueryR/blob/master/R/tables.R, the max results is set to 1000, not sure why 50 is being returned? This also is not a parameter in my function of bqr_list_tables, is my version off? – ZeroStack
Try updating to the latest version then. – Graham Polley
@GrahamPolley Turns out ‘0.2.0’ is the latest stable release. – ZeroStack
Hmm, by the looks of things, then it should be exposed as a parameter if you're using 0.2.0. I know the guy to ask. Let me ping him! – Graham Polley

1 Answers

1
votes

Instead of installing the latest github version, I just directly used the following code from the repo https://github.com/cloudyr/bigQueryR/blob/master/R/tables.R, no issues and works as prescribed.

bqr_list_tables <- function(projectId, datasetId, maxResults = 1000, pageToken = ""){

  l <- googleAuthR::gar_api_generator("https://www.googleapis.com/bigquery/v2",
                                      "GET",
                                      path_args = list(projects = projectId,
                                                       datasets = datasetId,
                                                       tables = ""),
                                      pars_args = list(maxResults = maxResults,
                                                       pageToken = pageToken),
                                      data_parse_function = parse_bqr_list_tables)

  out <- l(path_arguments = list(projects = projectId, 
                                 datasets = datasetId))

  out
}

parse_bqr_list_tables <- function(x) {
  d <- x$tables
  data.frame(id = d$id,
             projectId = d$tableReference$projectId,
             datasetId = d$tableReference$datasetId,
             tableId = d$tableReference$tableId, stringsAsFactors = FALSE)

}