4

I want to create a shiny application which makes use of the bigrquery to connect to the BigQuery API and run a query. I use the following code to execute the query:

 library(bigrquery)
    project <- "PROJECT_ID" # put your project ID here
    sql <- 'QUERY '
    test <- query_exec(sql, project = project)

But before this there is an authentication process in the bigrquery package like: 

    google <- oauth_endpoint(NULL, "auth", "token",
      base_url = "https://accounts.google.com/o/oauth2")

    bigqr <- oauth_app("google",
      "465736758727.apps.googleusercontent.com",
      "fJbIIyoIag0oA6p114lwsV2r")

    cred <- oauth2.0_token(google, bigqr,
          scope = c(
              "https://www.googleapis.com/auth/bigquery",
              "https://www.googleapis.com/auth/cloud-platform"))

How can I integrate the auth process in my application that

  • the process needs no interaction or
  • the process works with given app key and secrets (where do I get them? ) or
  • the auth process opens up in another browser window.

Regards

JulianHi
  • 286
  • 2
  • 4
  • 14

1 Answers1

4

One suggestion I have, which is similar to an answer I provided on a question about server-side access to Google Analytics data, is to use a Google Service Account. The googleAuthR package by Mark Edmondson, available through CRAN, provides functionality to perform server-side authentication in R using a Google Service Account. Another package by the same author called bigQueryR, also on CRAN, integrates with googleAuthR and uses the resulting authentication token to execute queries to Google BigQuery.

To achieve this:

  1. Create a service account for your Google API project.
  2. Download the JSON file containing the private key of the service account.
  3. Grant the service account access to your Google BigQuery project, in the same way as you would for any other user. This is done via the Google API console IAM screen where you set permissions for your project.
  4. Supply the location of the private key JSON file as an argument when authenticating with googleAuthR (see the example below.):

The following example R script, based off an example from the bigrquery package, references the JSON file containing the private key and performs a basic Google BigQuery query. Remember to set the json_file argument to the appropriate file path and the project argument to your Google BigQuery project:

library(googleAuthR)
library(bigQueryR)

gar_auth_service(
  json_file = "API Project-xxxxxxxxxxxx.json",
  scope = "https://www.googleapis.com/auth/bigquery"
)

project <- "project_id" # put your project ID here
sql <- "SELECT year, month, day, weight_pounds
        FROM [publicdata:samples.natality] LIMIT 5"

bqr_query(projectId = project, query = sql, datasetId = "samples")
Community
  • 1
  • 1
johannux
  • 186
  • 10