For as long as business intelligence (BI) has been around, visualization tools have played a key role in helping analysts and decision-makers get quick insights from data.
In this post, you’ll learn how to connect Google Data Studio with BigQuery.
What is BigQuery
Putting away and questioning large datasets can be tedious and costly without the correct equipment and foundation. Thus, BigQuery is an enterprise information warehouse that takes care of this issue by empowering super-quick SQL questions utilizing the handling intensity of Google’s foundation. It moves your information into BigQuery and let it handle the complicated work. You can control access to both the venture and your information depending on your business needs, for example, enabling others to view or inquire about your data.
How to Connect to BigQuery
You can connect Data Studio to a single Google BigQuery table or view:
- Sign in to Data Studio.
- In the top left, click, and then select Data Source.
- Select the BigQuery connector.
- Select a connection option (described below) and provide your connection details.
- In the upper right, click CONNECT.
- The data source fields panel appears.
- The data source is now connected to your data set.
When creating a BigQuery data source, you can choose from the following options:
Use this option to select a full table in a project to which you have access. The data source editor allows you to traverse the project, data set, and table hierarchy.
Use this to access a shared data set and use a different project for billing purposes.
To connect to a shared project:
- Select a billing project.
- Enter the shared project name.
- Select a data set and table.
- Click CONNECT.
Select the CUSTOM QUERY option to provide a SQL query instead of connecting to a single table. Data Studio uses this custom SQL as an inner select statement for each generated query to the database.
To use Custom Query, first, select a project you have access to. We will use it for billing purposes. Second, input your SQL query.
Custom Query Syntax
The custom query syntax should follow the BigQuery SQL dialect with the following caveats:
- Comments in the SQL statement are not supported.
- Field names and aliases in the SELECT statement must not contain a period (.) To work around this, you can do one of the following:
- Below the query input field, uncheck Use Legacy SQL. Undoubtedly, this will cause your query to use standard SQL, which supports periods in field names and aliases. Learn more about the Standard SQL dialect.
- Uses aliases to replace each period with an underscore (_), for example:
SELECT category.product AS category_product FROM …
- Create a view within BigQuery and reference the view in the SQL statement or select the view under My Projects.
You can use the BigQuery user interface to test that your query works, then copy and paste that query into Data Studio.
Custom Query Parameters
In addition, you can utilize parameters to give report clients an approach to alter the question. However, utilize a similar linguistic structure as depicted for running parameterized queries in BigQuery.
To Add a Query Parameter
- In the body of your custom query, replace a hard-coded value with an identifier beginning with the character. For example:
- Below the custom query editor, click ADD PARAMETER. The parameters in your query are listed below the editor.
- Click a parameter in the list to configure its options:
- Name. This is the parameter identifier.
- Display name. Determines how the parameter appears to report users.
- Data type. Determines the kind of data expected for the parameter.
- Input type. Determines how the parameter values are displayed in the data source’s Parameters section.
- Default value. Report users can override this. If modifying the parameter has been turned off, then the default value will be used.
- By default, parameters can be modified in reports. Uncheck the box to turn this off. Learn more about allowing data source parameters in the report.
You can use the following reserved parameters to pass the report’s time frame to the query:
@DS_START_DATE sets the beginning of the time frame.
@DS_END_DATE sets the end of the time frame.
To use these date parameters:
- Below the query editor, check the Enable date parameters option.
- Be sure to use uppercase for the parameter names.
- Hence, if your report includes a date range control, viewers can use that to request different starting and ending dates from the BigQuery data.
You can utilize this alternative to access BigQuery open datasets. BigQuery offers various open examples, where the dataset is shared, yet not the undertaking. In addition, to question this information, you should determine your own task, which will be utilized to charge for handling costs on the common information.
Determine the area wherein the question will execute. Likewise, inquiries that run in a particular area may just reference information in that area. Be that as it may, for information in the US or EU, you may decide to run the question in the area where the information dwells. You should choose the inquiry area expressly rather than in different areas.
Override Billing Project ID
Specify a different Billing Project ID for this custom query.
Use _PARTITIONTIME as a time dimension
This choice shows up when you select a date-divided table. As a matter of course, the information source utilizes the table’s parcel time as the date extend measurement. Uncheck this alternative to utilize express date fields in the table as the date run measurement. Adapt more about date-divided tables in BigQuery.
Google Data Integrations
Some Google products export data directly into BigQuery tables. When recognized, Data Studio will automatically enrich the fields to include common aggregations, calculations, and field names.
Google Analytics BigQuery Export
At the point when Data Studio experiences a table produced by Google Analytics BigQuery Export, the table will have a Google Analytics symbol by it. At the point when chosen, a considerable lot of the normal measurements found in Google Analytics will consequently be made as Data Studio fields.
BigQuery BI Engine
BigQuery BI Engine is a quick, in-memory examination administration. In addition, by utilizing BI Engine, you can dissect information put away in BigQuery with sub-second question reaction time and with high simultaneousness. Thus, you can quicken your reports and investigations also by interfacing Data Studio to a BigQuery table oversaw by BI Engine.
Get familiar with BigQuery BI Engine.
Configure the Data Source
Also, the information source fields panel is where you arrange the information source by renaming fields and including depictions, including determined fields, and changing information types and collections. Adapt more about working with information source fields.
Control Who Sees the Data
In the upper right of the fields board, you can change the information source certifications. Consequently, qualifications control who can see the information gave by this information source.
Utilizing OWNER’S CREDENTIALS, for sure, allows others to people or make reports that utilization this information without expecting them to have their own entrance to the informational collection.
Utilizing VIEWER’S CREDENTIALS, then again, requires every client of the information source to give their very own accreditations to get to the informational collection.
Adapt more about data source accreditations.
Create a New Report from the Data Source
- In the upper right, click CREATE REPORT.
- The report editor appears.
- Click ADD TO REPORT.
- This adds the data source to the report.
- Finally, you can now create charts and controls that get their data from this data source.
New to Data Studio?
Take the Create a report tutorial or learn more about the report editor.
Also, BigQuery too supports querying over numerous tables, where each table has a single day of information. The tables have the arrangement of YYYYMMDD. At the point when Data Studio experiences a table that has the arrangement of YYYYMMDD, the table will be set apart as a multi-day table, and just the name prefix_YYYYMMDD will be shown in the table select.
At the point when a graph is made to imagine this table, Data Studio will naturally make a default date scope of the most recent 28 days and appropriately question the last 28 tables. You can design this setting by altering the report, choosing the graph, at that point modify the Date Range properties in the chart’s DATA tab.
Likewise, to accelerate the client experience, Data Studio Reports will attempt to nonconcurrently get information from BigQuery at regular intervals as a matter of course. Every one of these gets can cause BigQuery costs.
Caching and Report Viewing
When the information is reserved, when a report is seen, Data Studio will attempt to get to the information from the store, and in this way limit BigQuery costs.
To last long, storing is a best-exertion highlight and may not generally be conceivable, relying upon the question reaction size.
Record Count Metric
BigQuery information sources naturally give a default Record Count metric. Most importantly, you can utilize this to breakdown your measurements to show the number of records being amassed by your outlines.
You can share your inputs and comments about this blog in the section below. To know more about our services, please visit Loginworks Softwares Inc.