Visualizing BigQuery data using Data StudioBigQuery is a petabyte-scale analytics data warehouse that you can use to run SQL queries over vast amounts of data in near real-time. Show
Data visualization tools can help you make sense of your BigQuery data and help you analyze the data interactively. You can use visualization tools to help you identify trends, respond to them, and make predictions using your data. In this tutorial, you use Google Data Studio to visualize data in the BigQuery natality sample table. ObjectivesIn this tutorial, you can do the following:
CostsThe Google Data Studio BigQuery connector lets you access data from your BigQuery tables within Google Data Studio. BigQuery is a paid product and you incur BigQuery usage costs when accessing BigQuery through Google Data Studio. BigQuery query pricing provides the first 1 TB per month free of charge. For more information, see the BigQuery Pricing page. Before you beginBefore you begin this tutorial, use the Google Cloud Console to create or select a project and enable billing.
Create reports and charts using Google Data Studio and the BigQuery connectorIn this section of the tutorial, you use Google Data Studio to visualize data in BigQuery using the BigQuery connector. You create a data source, a report, and charts that visualize data in the natality sample table. Create a data sourceThe first step in creating a report in Google Data Studio is to create a data source for the report. A report may contain one or more data sources. When you create a BigQuery data source, Google Data Studio uses the BigQuery connector. You must have the appropriate permissions in order to add a BigQuery data source to a Google Data Studio report. The permissions applied to BigQuery datasets also apply to the reports, charts, and dashboards you create in Google Data Studio. When a Google Data Studio report is shared, the report components are visible only to users who have appropriate permissions. To create a data source:
Create a bar chart using a calculated fieldOnce you have added the natality data source to the report, the next step is to create a visualization. Begin by creating a bar chart. The bar chart displays the total number of births for each year. To display the births by year, you create a calculated field. To create a bar chart that displays total births by year:
Filter the chartCurrently, the bar chart displays the total number of male and female children born each year. Add a filter to display only the female children born each year.
Notice the chart is updated to display only female children born each year. Also notice that the legend does not change. The legend name still reflects the name of the metric birth_count. Create a chart using a custom queryCreating a chart using the Custom Query option lets you leverage BigQuery's full query capabilities such as joins, unions, and analytical functions. Alternatively, you can leverage BigQuery's full query capabilities by creating a view. A view is a virtual table defined by a SQL query. You can query data in a view by adding the dataset containing the view as a data source. For more information on views, see Using views. When you specify a SQL query as your BigQuery data source, the results of the query are in table format, which becomes the field definition (schema) for your data source. When you use a custom query as a data source, Google Data Studio uses your SQL as an inner select statement for each generated query to BigQuery. For more information on custom queries in Google Data Studio, consult the online help. Add a bar chart using a custom queryTo add a bar chart to your report that uses a custom query data source:
View your query historyYou can view queries submitted through the BigQuery Connector by examining your query history in the BigQuery web interface. Using the query history, you can estimate query costs, and you can save queries for use in other scenarios. To examine your query history: Console
Clean upTo avoid incurring charges to your Google Cloud account for the resources used in this tutorial, either delete the project that contains the resources, or keep the project and delete the individual resources.
If you plan to explore multiple tutorials and quickstarts, reusing projects can help you avoid exceeding project quota limits. Deleting your project prevents Google Data Studio from querying the data because the data source is associated with your project. If you do not want to delete your Google Cloud project, you can delete the Google Data Studio report and data source. To delete the Google Data Studio resources:
What's next
|