Google BigQuery
Summary
| Item | Description |
|---|---|
| Release State | General Availability |
| Products | Power BI (Semantic models) Power BI (Dataflows) Fabric (Dataflow Gen2) Power Apps (Dataflows) Customer Insights (Dataflows) |
| Authentication Types Supported | Organizational account Service account |
Note
Some capabilities may be present in one product but not others due to deployment schedules and host-specific capabilities.
Note
Effective July 2021, Google discontinued support for sign-ins to Google accounts from embedded browser frameworks. Due to this change, you need to update your Power BI Desktop version to June 2021 or later to support signing in to Google.
Prerequisites
You need a Google account or a Google service account to sign in to Google BigQuery.
Capabilities supported
- Import
- DirectQuery (Power BI semantic models)
Connect to Google BigQuery data from Power Query Desktop
To connect to Google BigQuery from Power Query Desktop, take the following steps:
Select Google BigQuery in the get data experience. The get data experience in Power Query Desktop varies between apps. For more information about the Power Query Desktop get data experience for your app, go to Where to get data.
If you want to use any advance options, select Advanced options. Otherwise, select OK to continue. More information: Connect using advanced options
The Google BigQuery connector supports connecting through an organizational account or a service account sign-in. In this example, you'll use the organizational account to sign in. Select Sign In to continue.
You can also sign in using a Google service account. In this case, select Service Account Login and enter your service account email and your service account JSON key file contents. Then select Connect.
A Sign in with Google dialog appears. Select your Google account and approve connecting to Power BI Desktop.
Once signed in, select Connect to continue.
Once you successfully connect, a Navigator window appears and displays the data available on the server. Select your data in the navigator. Then select either Transform Data to transform the data in Power Query or Load to load the data in Power BI Desktop.
Connect to Google BigQuery data from Power Query Online
To connect to Google BigQuery from Power Query Online, take the following steps:
Select the Google BigQuery option in the get data experience. Different apps have different ways of getting to the Power Query Online get data experience. For more information about how to get to the Power Query Online get data experience from your app, go to Where to get data.
In the Google BigQuery Database dialog, you may need to either create a new connection or select an existing connection. If you're using on-premises data, select an on-premises data gateway. Then select Sign in.
A Sign in with Google dialog appears. Select your Google account and approve connecting.
Note
Although the sign in dialog box says you'll continue to Power BI Desktop once you've signed in, you'll be sent to your online app instead.
If you want to use any advance options, select Advanced options. More information: Connect using advanced options
Once signed in, select Next to continue.
Once you successfully connect, a Navigator window appears and displays the data available on the server. Select your data in the navigator. Then select Next to transform the data in Power Query.
Connect using advanced options
Both Power Query Desktop and Power Query Online provide a set of advanced options that you can add to your query if needed.
The following table lists all of the advanced options you can set in Power Query Desktop and Power Query Online.
| Advanced option | Description |
|---|---|
| Billing Project ID | A project against which Power Query will run queries. Permissions and billing are tied to this project. If no Billing Project ID is provided, by default the first available project returned by Google APIs will be used. |
| Use Storage Api | A flag that enables using the Storage API of Google BigQuery. This option is true by default. This option can be set to false to not use the Storage API and use REST APIs instead. |
| Connection timeout duration | The standard connection setting (in seconds) that controls how long Power Query waits for a connection to complete. You can change this value if your connection doesn't complete before 15 seconds (the default value.) |
| Command timeout duration | How long Power Query waits for a query to complete and return results. The default depends on the driver default. You can enter another value in minutes to keep the connection open longer. |
| Project ID | The project that you want to run native queries on. This option is only available in Power Query Desktop. |
| SQL statement | For information, go to Import data from a database using native database query. In this version of native database query functionality, you need to use fully qualified table names in the format Database.Schema.Table, for example SELECT * FROM DEMO_DB.PUBLIC.DEMO_TABLE. This option is only available in Power Query Desktop. |
Once you've selected the advanced options you require, select OK in Power Query Desktop or Next in Power Query Online to connect to your Google BigQuery data.
Limitations and considerations
This section describes any limitations or considerations of the Google BigQuery connector.
Connecting to Google BigQuery in Power BI Desktop
There are a few limits and considerations to keep in mind when using the Google BigQuery connector with Power BI.
Connector availability
The Google BigQuery connector is available in Power BI Desktop and in the Power BI service. In the Power BI service, the connector can be accessed using the Cloud-to-Cloud connection from Power BI to Google BigQuery.
"Access Denied" error
When you try to connect to Google BigQuery from Power BI Desktop, you might get the following error message:
Datasource.Error: ODBC: ERROR [HY000][Microsoft][BigQuery] (100) Error interacting with REST API: Access Denied: Project <project name>: The user <user name> bigquery.jobs.create permissions in project <project name>.
In this case, you might need to enter a Billing Project ID in the Billing Project advanced option in the Power Query Connection settings.
In addition, if you also create a report in Power BI service using a gateway, you might still get this error. In this case, you must manually include the Billing Project ID in the M code for the connection using the Power Query editor or the Power Query formula bar. For example:
Source = GoogleBigQuery.Database([BillingProject="Include-Billing-Project-Id-Here"])
Nested fields
To optimize performance considerations, Google BigQuery does well with large data sets when denormalized, flattened, and nested.
The Google BigQuery connector supports nested fields, which are loaded as text columns in JSON format.
Users should select Transform Data and then use the JSON parsing capabilities in the Power Query Editor to extract the data.
- Under the Transforms ribbon tab, the Text Column category, select Parse and then JSON.
- Extract the JSON record fields using the Expand Column option.
Setting up a Google service account
For more information on setting up or using Google service accounts, go to Creating and managing service account keys in the Google docs.
Authenticating through a Google service account
When you authenticate through a Google service account in Power BI Desktop, there's a specific credential format that's required by the connector.
- Service Account Email: must be in email format
- Service Account JSON key file contents: once this JSON key is downloaded, all new lines must be removed from the file so that the contents are in one line. Once the JSON file is in that format, the contents can be pasted into this field.
When you authenticate through a Google service account in Power BI service or Power Query Online, users need to use "Basic" authentication. The Username field maps to the Service Account Email field above, and the Password field maps to the Service Account JSON key file contents field above. The format requirements for each credential remain the same in both Power BI Desktop, Power BI service, and Power Query Online.
Unable to authenticate with Google BigQuery Storage API
The Google BigQuery connector uses Google BigQuery Storage API by default. This feature is controlled by the advanced option called UseStorageApi. You might encounter issues with this feature if you use granular permissions. In this scenario, you might see the following error message or fail to get any data from your query:
ERROR [HY000] [Microsoft][BigQuery] (131) Unable to authenticate with Google BigQuery Storage API. Check your account permissions
You can resolve this issue by adjusting the user permissions for the BigQuery Storage API correctly. These storage API permissions are required to access data correctly with BigQueryStorage API:
bigquery.readsessions.create: Creates a new read session via the BigQuery Storage API.bigquery.readsessions.getData: Reads data from a read session via the BigQuery Storage API.bigquery.readsessions.update: Updates a read session via the BigQuery Storage API.
These permissions typically are provided in the BigQuery.User role. More information, Google BigQuery Predefined roles and permissions
If the above steps don't resolve the problem, you can disable the BigQuery Storage API.
Unable to use DateTime type data in Direct Query mode
There's a known issue where the DateTime type isn't supported through Direct Query. Selecting a column with the DateTime type will cause an "Invalid query" error or a visual error.
Limitations on querying column that has the same name as table name
When querying a column that has the same name as the table name, BigQuery interprets the column as a struct that includes all columns in the table instead of the specified column. For example, SELECT debug FROM dataset.debug returns a struct with all columns in the debug table, instead of the specified debug column. This behavior is usually not intuitive and a fix is being investigated. There are three workarounds available:
- Workaround 1: Wrap the table with a view that doesn't conflict the column name—
CREATE VIEW dataset.new_view AS SELECT * FROM dataset.debug - Workaround 2: Rename the column to avoid conflicts with the table name—
ALTER TABLE dataset.debug RENAME COLUMN debug to new_debug - Workaround 3: Change the SELECT query to use table.column to reference the conflict column—
SELECT debug.debug FROM dataset.debug
Feedback
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see: https://aka.ms/ContentUserFeedback.
Submit and view feedback for