0

I'm attempting to connect BigQuery to Looker. I am pulling sample data from a Google Sheets document to a BigQuery dataset; this part is working fine, as my internal BigQuery queries are running just fine for this dataset. Using this documentation from the Looker forums, I tried to create a service account key to connect my BigQuery dataset to Looker. Unfortunately, the documentation is slightly out of date: Google now asks which service account (compute engine default service account, app engine default service account, or a new service account that can have any of multiple roles) you want to attach the key to.

Thus far, I have tried using P12 keys created for the compute engine default service account, the app engine default service account, as well as a new Project Owner service account. When I create the connection in Looker, the admin page confirms that the connection "can connect, can cancel queries, can run simple select query" (I need it to do more complex things, but am just trying to connect at all right now). Using the SQL Runner to test a simple select 10 query out, I was able to query the public datasets, e.g. hacker_news or usa_names. However, whenever I tried to run the same query on my personal sample dataset, I received this error:

Failed to retrieve data - The job encountered an internal error during execution and was unable to complete successfully.

The permissions for the base Google Sheet that the BigQuery project is pulling from are set to be viewable by my coworkers who have the link. I have also been adding each service account I test as an editor (which I assume has the highest permissions). At this point, I am creating new service accounts with each of the different possible roles to see if it's a permissions issue from the role perspective. Nothing has worked so far, so any insight would be helpful!

UPDATE: I have created a new table within the same BigQuery dataset. The new table was created using a CSV file, which was simply a download of my previous table in Google Sheets. I updated the connection to Looker. When I wrote a select 10 query pulling from the new table, it worked fine and ran very quickly. This seems to imply that the problem is something about the permissions between Google Sheets and Google BigQuery.

Amy Ho
  • 1
  • 4

1 Answers1

2

I've been wanting to do something like this myself for a bit, saw this question, and decided to dig in.

First thing I found was this "documentation" over in the looker discourse:

https://discourse.looker.com/t/live-spreadsheets-in-databases/2698/7

In there, it describes the steps necessary to get this working.

Two important things that you are probably missing, based on your description of events so far (since it sounds like you've already attached the sheet to your dataset and are able to query it from the BigQuery UI):

  1. Make sure you share the Google Sheet with the service account you are using to connect Looker to BigQuery. This is the Username from the Connections tab of the Admin page in Looker.

  2. Make sure you have enabled the Drive and Sheets APIs for your google project. You can do that via The API Library. Just search for "Drive" (or "Sheets"), click on the name, and then click on the "Enable" button from the API detail page.

Once I did the above, I had to wait a few minutes before things started working. I'll go out on a limb and guess that this was because Looker needed to cycle it's internal connection pool before the permissions would reset and work. So you may need to run a few failing queries, or wait out the connection pool before this will go into effect.

Hope that helps.

numbsafari
  • 351
  • 4
  • 8