Ingest from a REST API

Fetch data from a real API — no Python, no pip, no setup

Fetch European country data from the REST Countries API. No Python, no pip.

1. Write the Script

# models/raw/countries.star
# @kind: table

resp = http.get(
    "https://restcountries.com/v3.1/region/europe?fields=name,capital,population,area,region,subregion",
    retry=3,
)

if not resp.ok:
    fail("API returned " + str(resp.status_code))

for country in resp.json:
    capital = ""
    if country.get("capital") and len(country["capital"]) > 0:
        capital = country["capital"][0]

    save.row({
        "name":       country["name"]["common"],
        "capital":    capital,
        "population": country["population"],
        "area_km2":   country.get("area", 0),
        "subregion":  country.get("subregion", ""),
    })

print("Fetched " + str(save.count()) + " countries")

No API key needed. No environment setup. Just run it.

2. Run

ondatrasql run raw.countries

Fetched 53 countries
[OK] raw.countries (table, backfill, 53 rows, 799ms)

3. Transform Downstream

-- models/staging/stg_countries.sql
-- @kind: table
-- @constraint: name NOT NULL
-- @constraint: population >= 0
-- @audit: row_count > 0

SELECT
    name,
    capital,
    population,
    area_km2,
    subregion,
    CASE
        WHEN population > 50000000 THEN 'large'
        WHEN population > 10000000 THEN 'medium'
        ELSE 'small'
    END AS size_category
FROM raw.countries
WHERE population > 0

-- models/mart/population_by_subregion.sql
-- @kind: table
-- @audit: row_count > 0

SELECT
    subregion,
    COUNT(*) AS countries,
    SUM(population) AS total_population,
    ROUND(AVG(population)) AS avg_population,
    ROUND(SUM(area_km2)) AS total_area_km2
FROM staging.stg_countries
GROUP BY subregion
ORDER BY total_population DESC

4. Run the Full Pipeline

ondatrasql run

Running 3 models...
Fetched 53 countries
[OK] raw.countries (table, full, 53 rows, 820ms)
[OK] staging.stg_countries (table, backfill, 53 rows, 440ms)
[OK] mart.population_by_subregion (table, backfill, 6 rows, 243ms)

OndatraSQL detects that stg_countries depends on countries and runs them in the right order.

What’s Built In

http.get, http.post with retry, timeout, headers
oauth.token for OAuth 2.0 flows
env.get for secrets from .env
incremental for cursor-based loading
save.row and save.rows for output

No external packages. No virtual environments. Everything in one binary.