Skip to content
Queria Docs
English
日本語English
Esc
↑↓navigate↵open⌘Jpreview
On this page

Connect from the DuckDB CLI

Connect to a DuckLake catalog from the DuckDB CLI and analyze prefecture-level statistics

Launch the DuckDB CLI and experience “touch → peek → analyze” in three steps. As an example, we use the e-Stat government statistics dataset (e_stat) to pull out prefecture-level indicators.

1. Connect

Load the DuckLake extension and attach the e-Stat catalog.

INSTALL ducklake;
LOAD ducklake;

ATTACH 'ducklake:https://data.queria.io/e_stat/ducklake.duckdb' AS e_stat (READ_ONLY);

2. Peek inside

e_stat contains prefecture and municipality statistics organized by domain — population, economy, labor, and more. List the tables in the ssds schema:

SELECT schema, name FROM (SHOW ALL TABLES) WHERE database = 'e_stat' AND schema = 'ssds' LIMIT 20;

3. Pull out a prefecture ranking

Rank prefectures by total population for fiscal year 2024. The item_name values (for example, A1101_総人口 — total population) join e-Stat’s classification code and indicator name with an underscore.

SELECT DISTINCT area_name, value AS population
FROM e_stat.ssds.a_pref_population
WHERE item_name = 'A1101_総人口'
  AND year = 2024
  AND area_name != '全国'
ORDER BY value DESC
LIMIT 10;

You get the familiar ranking with Tokyo, Kanagawa, and Osaka at the top. (Note: 全国 means “nationwide” and is excluded here.)

Analyze with computed metrics

Pivot “labor force” and “unemployed persons” into columns with CASE, and compute the unemployment rate by prefecture.

SELECT
  area_name,
  MAX(CASE WHEN item_name = 'F1101_労働力人口' THEN value END) AS labor_force,
  MAX(CASE WHEN item_name = 'F1107_完全失業者数' THEN value END) AS unemployed,
  ROUND(
    MAX(CASE WHEN item_name = 'F1107_完全失業者数' THEN value END)
    / MAX(CASE WHEN item_name = 'F1101_労働力人口' THEN value END) * 100,
    2
  ) AS unemployment_rate_pct
FROM e_stat.ssds.f_pref_labor
WHERE year = 2020 AND area_name != '全国'
GROUP BY area_name
ORDER BY unemployment_rate_pct DESC NULLS LAST
LIMIT 10;

Attach multiple datasets at once

Call ATTACH multiple times to query other datasets side by side.

ATTACH 'ducklake:https://data.queria.io/reinfolib/ducklake.duckdb' AS reinfolib (READ_ONLY);
ATTACH 'ducklake:https://data.queria.io/zipcode/ducklake.duckdb' AS zipcode (READ_ONLY);

Explore the catalog with SQL

Read the catalog with read_json_auto to query which datasets hold which tables and columns. Deciding what to connect to also happens inside DuckDB. limit caps at 200 and anything beyond that is not returned. When more remains, has_more is true and offset fetches the next page.

Here is the dataset list. Prefix catalog_url with ducklake: when you pass it to ATTACH.

SELECT name, title, 'ducklake:' || catalog_url AS attach_url, bytes, updated_at
FROM (SELECT unnest(datasets, recursive := true)
      FROM read_json_auto('https://queria.io/api/catalog/datasets?limit=200'))
ORDER BY bytes DESC;

Search tables and columns by keyword. type takes dataset, table, or column.

SELECT e.qualified_name, e.schema_name, e.table_name, e.column_name, e.title
FROM (SELECT unnest(entries) AS e
      FROM read_json_auto('https://queria.io/api/catalog/search?q=population&type=column&limit=200'));

Here are the tables in a single dataset.

SELECT t.schema, t.name, t.title, len(t.fields) AS fields
FROM (SELECT unnest(declaration.tables) AS t
      FROM read_json_auto('https://queria.io/api/catalog/datasets/queria/zipcode'))
WHERE t.published;

Columns carry their type and nullability.

SELECT f.name, f.type, f.nullable, f.title
FROM (SELECT unnest(t.fields) AS f
      FROM (SELECT unnest(declaration.tables) AS t
            FROM read_json_auto('https://queria.io/api/catalog/datasets/queria/zipcode'))
      WHERE t.name = 'mart_zipcode');

Run a one-liner straight from the shell

Pass a whole script with the -c option to get results instantly from your terminal.

duckdb -c "
INSTALL ducklake;
LOAD ducklake;
ATTACH 'ducklake:https://data.queria.io/e_stat/ducklake.duckdb' AS e_stat (READ_ONLY);
SELECT DISTINCT area_name, value AS population
FROM e_stat.ssds.a_pref_population
WHERE item_name = 'A1101_総人口' AND year = 2024 AND area_name != '全国'
ORDER BY value DESC LIMIT 5;
"

Notes

  • The ducklake extension bundled with the DuckDB CLI can be outdated depending on your version. Use 1.5.2 or later, for example via uvx duckdb.
  • The httpfs extension required for access over https:// loads automatically on first access. In environments where automatic extension installation is disabled, run INSTALL httpfs; LOAD httpfs; first.
  • Datasets containing GEOMETRY columns (nlftp, e_stat boundary data, and so on) require INSTALL spatial; LOAD spatial;.

The ATTACH statements on this page work unchanged in other environments. Queria’s data is published in the standard DuckLake format, so any language or environment with duckdb 1.5.2 or later can read it the same way.

Was this page helpful?