Skip to content
imibare

How-to guide

Query with DuckDB

This guide shows how to query imibare datasets directly with DuckDB, either through the Apache Iceberg integration or by reading a flat Parquet file over HTTP.

Credential requirement: the Iceberg path needs credentials. Cloudflare R2 Data Catalog does not support unauthenticated public access, so you need a Cloudflare API token with Workers R2 Storage and Workers R2 Data Catalog permissions. If you only need the current data, read the Parquet file directly instead: that path needs no credentials at all.

Prerequisites

Terminal window
pip install duckdb

Set credentials in your environment:

Terminal window
export R2_ACCOUNT_ID=<your-cloudflare-account-id>
export R2_ACCESS_KEY_ID=<your-r2-key-id>
export R2_SECRET_ACCESS_KEY=<your-r2-secret>
export R2_CATALOG_URL=https://catalog.cloudflarestorage.com/<account-id>/imibare-data
export R2_CATALOG_TOKEN=<your-catalog-token>

Connect to the Iceberg catalog

import os
import duckdb


account_id = os.environ["R2_ACCOUNT_ID"]


# Create the S3-compatible secret for R2
duckdb.sql(f"""
    CREATE SECRET r2 (
        TYPE S3,
        KEY_ID '{os.environ["R2_ACCESS_KEY_ID"]}',
        SECRET '{os.environ["R2_SECRET_ACCESS_KEY"]}',
        ENDPOINT '{account_id}.r2.cloudflarestorage.com',
        REGION 'auto',
        URL_STYLE 'path'
    )
""")


# Attach the Iceberg catalog (read-only)
duckdb.sql(f"""
    ATTACH '{account_id}_imibare-data' AS imi (
        TYPE ICEBERG,
        ENDPOINT '{os.environ["R2_CATALOG_URL"]}',
        TOKEN '{os.environ["R2_CATALOG_TOKEN"]}',
        READ_ONLY
    )
""")

Run queries

# Latest CPI data
duckdb.sql("SELECT * FROM imi.rw_nisr_cpi_monthly ORDER BY date DESC LIMIT 10").show()


# Annual average CPI by category
duckdb.sql("""
    SELECT
        date_trunc('year', date) AS year,
        category,
        AVG(value) AS avg_cpi
    FROM imi.rw_nisr_cpi_monthly
    GROUP BY 1, 2
    ORDER BY 1 DESC, 2
""").show()


# USD exchange rate in 2024
duckdb.sql("""
    SELECT date, rwf_per_unit
    FROM imi.rw_bnr_fx_daily
    WHERE currency = 'USD'
      AND year(date) = 2024
    ORDER BY date
""").show()

Load results into pandas

df = duckdb.sql("""
    SELECT date, value
    FROM imi.rw_nisr_cpi_monthly
    WHERE category = 'overall'
    ORDER BY date
""").df()   # .df() returns a pandas DataFrame

Table names

DuckDB table names use underscores, not dots:

Dataset IDDuckDB table
rw.nisr.cpi.monthlyimi.rw_nisr_cpi_monthly
rw.nisr.gdp.annualimi.rw_nisr_gdp_annual
rw.bnr.fx.dailyimi.rw_bnr_fx_daily
rw.minecofin.budget-execution.quarterlyimi.rw_minecofin_budget_execution_quarterly
rw.rssb.contributions.annualimi.rw_rssb_contributions_annual

Rule: replace dots with underscores, hyphens with underscores.

List all tables

duckdb.sql("SHOW ALL TABLES").show()

Time-travel queries

Iceberg snapshots let you query a table as it existed on a specific date. Check your installed DuckDB version for the exact syntax, as it varies between releases:

duckdb.sql("""
    SELECT * FROM imi.rw_nisr_cpi_monthly
    AT (TIMESTAMP => TIMESTAMPTZ '2025-06-01 00:00:00')
    ORDER BY date DESC LIMIT 5
""").show()

Read a flat Parquet file directly (no Iceberg, no credentials)

Every dataset is published as a flat Parquet file. Reading one needs no credentials:

import duckdb


duckdb.sql("""
    SELECT * FROM read_parquet(
        'https://<account-id>.r2.cloudflarestorage.com/imibare-data/v1/rw/nisr/cpi/monthly/latest.parquet'
    ) LIMIT 10
""").show()

The path follows the convention v1/{country}/{institution}/{topic}/{frequency}/latest.parquet.

Reference