World Bank: International Debt Data

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.

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.

  1. Sign in to your Google account.

    If you don't already have one, sign up for a new account.

  2. Select or create a Cloud Platform project.

    Go to the Manage resources page

  3. Enable billing for your project.

    Enable billing

  4. BigQuery is automatically enabled in new projects. To activate BigQuery in a pre-existing project, Enable the BigQuery API.

    Enable the API

Dataset overview

This dataset contains both national and regional debt statistics captured by over 200 economic indicators. Time series data is available for those indicators from 1970 to 2015 for reporting countries.

The dataset includes 5 tables:

  • country_series_definitions
  • country_summary
  • international_debt
  • series_summary
  • series_times

For more information, see the World Bank website.

You can start exploring this data in the BigQuery console:

Go to World Bank international debt data

Sample queries

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 countries have the largest outstanding debt?

This dataset includes over 200 economic indicators. In this query, you retrieve the "Present value of external debt (current US$)" using the indicator_code and the latest year of available data.

The international_debt table includes aggregated country entries (such as Sub-Saharan Africa). You can eliminate the aggregates using an inner join with the country_summary table. The country_summary table contains a region attribute for each country. The aggregated country entries do not.

Web UI

Open the following query in the Web UI

#standardSQL
SELECT
  id.country_name,
  id.value AS debt --format in DataStudio
FROM (
  SELECT
    country_code,
    region
  FROM
    `bigquery-public-data.world_bank_intl_debt.country_summary`
  WHERE
    region != "" ) cs --aggregated countries do not have a region
INNER JOIN (
  SELECT
    country_code,
    country_name,
    value,
    year
  FROM
    `bigquery-public-data.world_bank_intl_debt.international_debt`
  WHERE
    indicator_code = "DT.DOD.PVLX.CD"
    AND year = 2015 ) id
ON
  cs.country_code = id.country_code
ORDER BY
  id.value DESC

Command-line

bq query --use_legacy_sql=false '
SELECT
  id.country_name,
  id.value AS debt --format in DataStudio
FROM (
  SELECT
    country_code,
    region
  FROM
    `bigquery-public-data.world_bank_intl_debt.country_summary`
  WHERE
    region != "" ) cs --aggregated countries do not have a region
INNER JOIN (
  SELECT
    country_code,
    country_name,
    value,
    year
  FROM
    `bigquery-public-data.world_bank_intl_debt.international_debt`
  WHERE
    indicator_code = "DT.DOD.PVLX.CD"
    AND year = 2015 ) id
ON
  cs.country_code = id.country_code
ORDER BY
  id.value DESC'

Sample results are shown here:

+------------------------+---------------------+
|      country_name      |        debt         |
+------------------------+---------------------+
| Mexico                 | 3.03034941502891E11 |
| Brazil                 | 1.85565928851414E11 |
| Indonesia              | 1.57987459485284E11 |
| India                  | 1.48745095807612E11 |
| Russian Federation     | 1.33839822670607E11 |
| Turkey                 | 1.22029712325355E11 |
| Argentina              | 1.10750816034451E11 |
| Colombia               | 6.84426231461233E10 |
| Venezuela, RB          |  6.4135718109114E10 |
| South Africa           | 6.07924748068575E10 |
| ...                                          |
+------------------------+---------------------+

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.

Government debt

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.

Citation: The World Bank: International Debt Statistics

Update Frequency: Biannual

View in BigQuery: Go to World Bank international debt data

Monitor your resources on the go

Get the Google Cloud Console app to help you manage your projects.

Send feedback about...