How to query public data sets using BigQuery
BigQuery is a fully managed data warehouse and analytics platform. Public datasets are available for you to analyze using SQL queries. You can access BigQuery public data sets using the web UI the command-line tool, or by making calls to the BigQuery REST API using a variety of client libraries such as Java, .NET, or Python.
Currently, BigQuery public datasets are stored in the
location. When you query a public dataset, supply
--location=US flag on the command line, choose
US as the
processing location in the BigQuery web UI, or specify the
location property in the
jobReference section of the job resource
when you use the API. Because the public datasets are stored in the US, you cannot write public
data query results to a table in another region, and you cannot join tables in public datasets
with tables in another region.
To get started using a BigQuery public dataset, create or select a project. The first terabyte of data processed per month is free, so you can start querying public datasets without enabling billing. If you intend to go beyond the free tier, you should also enable billing.
Sign in to your Google Account.
If you don't already have one, sign up for a new account.
Select or create a GCP project.
Make sure that billing is enabled for your project.
- BigQuery is automatically enabled in new projects. To activate BigQuery in a pre-existing project, Enable the BigQuery API.
This dataset combines key health statistics from a variety of sources to provide a look at global health and population trends. It includes information on nutrition, reproductive health, education, immunization, and diseases from over 200 countries.
For more information, see the World Bank website.
You can start exploring this data in the BigQuery console:
Here are some examples of SQL queries you can run on this data in BigQuery.
These samples use BigQuery’s support for standard SQL. Use the
#standardSQL tag to let BigQuery know you want to use standard SQL. For more
information about the
#standardSQL prefix, see
Setting a query prefix.
What’s the average age of first marriages for females around the world?
This query retrieves the average age of first marriages for females by country. Females are used because there is a larger age spread in first marriages for females. This spread provides a better visualization of the data.
#standardSQL SELECT country_name, ROUND(AVG(value),2) AS average FROM `bigquery-public-data.world_bank_health_population.health_nutrition_population` WHERE indicator_code = "SP.DYN.SMAM.FE" AND year > 2000 GROUP BY country_name ORDER BY average
bq query --use_legacy_sql=false ' SELECT country_name, ROUND(AVG(value),2) AS average FROM `bigquery-public-data.world_bank_health_population.health_nutrition_population` WHERE indicator_code = "SP.DYN.SMAM.FE" AND year > 2000 GROUP BY country_name ORDER BY average'
Sample results are shown here:
+--------------------------------+---------+ | country_name | average | +--------------------------------+---------+ | Niger | 16.43 | | Central African Republic | 17.15 | | Chad | 18.3 | | Mali | 18.67 | | Mozambique | 18.76 | | Bangladesh | 18.92 | | Sao Tome and Principe | 19.1 | | Afghanistan | 19.23 | | Madagascar | 19.4 | | Malawi | 19.4 | | ... | +--------------------------------+---------+
The results range from 16.43 years of age in Niger to 33.1 years in French Polynesia. If you compare the results to males, the average first marriage age changes by approximately 5 years.
The following visualization presents an interesting comparison between countries. You can view this visualization in Google Data Studio. For a tutorial on using Google Data Studio with BigQuery, see Visualizing BigQuery Data Using Google Data Studio.
About the data
Dataset Source: World Bank
Category: Demographic, Health, Nutrition
Use: This dataset is publicly available for anyone to use under the following terms provided by the Dataset Source - http://www.data.gov/privacy-policy#data_policy - and is provided "AS IS" without any warranty, express or implied, from Google. Google disclaims all liability for any damages, direct or indirect, resulting from the use of the dataset.
Update Frequency: Biannual
View in BigQuery: Go to World Bank HNP data