I'm trying to get some data from GCP and I'm having issues with that. My company has an application in R and we are trying to connect and request data using the bigrquery package.
I have the same credentials that we used to connect through PowerBI within a JSON file. Thus, I managed to call the function bq_auth(path = 'credentials.json') and connect to GCP. I can get the list of tables, however, when I ask for the table using the functions tbl() or bq_table_download(as_bq_table(), n_max = 10) I get an empty table/data.frame.
Has anyone had a similar problem or know the solution to this?
# Listing tables in the connection
dbListTables(con)
tbl(con, "my_table")
# Getting and printing a table
tb = as_bq_table("project_id.database_name.my_table")
bq_table_download(tb, n_max = 10)
DBI::dbListTables(con)
DBI::dbReadTable(con, "my_table", n_max = 10)
SOLVED!!
As suggested I wrote a query using the format "SELECT * FROM database.table" and I worked!!
now my code looks like:
ds <- bq_dataset("project_id", "my_table")
tb <- bq_dataset_query(ds,
query = "SELECT * FROM database_name.my_table LIMIT 100",
billing = 'project_id'
)
bq_table_download(tb)
I had similar but with querying oracle database. It turned out that my statements needed schema owner included so schemaowner.table Without it PowerBI couldn't see the contents of the table at all. I know your issue is with R but maybe my issue will steer you in the right direction?