Dynamically loading data based on user input from a relatively large live db (5mil+ rows)

[avindhyac]

Hi all,

I'm working with Observable Framework and trying to build dynamic visualizations that respond to user input. The user inputs are fairly simple—typically filtering data by geographic location (e.g., country) and year.

However, the underlying dataset is quite large (5 million+ rows), so performance is a concern. I'm looking for an efficient way to handle this, especially given that I'm new to Observable Framework.

I came across parameterized routes and thought they might help. I tried to use them to dynamically load a CSV file based on the route params. Here's what I attempted:

const tempdata = await FileAttachment(
  `data/sunburst-${observable.params.country}-${observable.params.year}.csv`
).csv({
  typed: true,
});

But this gives the error:

SyntaxError: FileAttachment requires a single literal string argument (3:23)

From what I understand, FileAttachment() doesn’t support dynamic paths via template strings, which throws off my initial idea.

My questions:

What’s the best practice for dynamically loading data based on route parameters in Observable Framework?

Should I be using a several data loaders to query the various filtered versions of the dataset on-demand? What's the solution there?

Are there any recommended patterns or examples for handling large datasets efficiently in the Observable Framework context?

Any help or advice would be greatly appreciated!

Thanks 🙏

[csdiehl16]

Hi Avindhya - the way you're doing it makes sense. Route params should be an exception to the static string literal rule, as documented here. Curious if it makes any difference with just a single parameter in the string. Also, do the params show up properly if you log them to the console outside of the file attachment code? If the params are correct, we should probably file a bug.

Is there a minimal example you could share so I could reproduce the issue?

Instead of outputting to csv, you may want to look into parquet data loaders to handle larger datasets in fewer files.

[avindhyac]

Hi Caleb, thanks for the reply! Here's a minimal example of the data loader:

// sunburst-[country]-[year].json.js

import path from "path";
import dotenv from "dotenv";
import { parseArgs } from "node:util";
import postgres from "postgres";

const __filename = fileURLToPath(import.meta.url);
const __dirname = path.dirname(__filename);

dotenv.config({ path: path.resolve(__dirname, "../.env") });

const sql = postgres({
-- db details --
});

// args
const {
  values: { country, year },
} = parseArgs({
  options: {
    country: { type: "string" },
    year: { type: "integer" },
  },
});

try {
  const data = await sql`
SELECT 
---
FROM database
---
WHERE m.segment_type = 'employed'
AND o.year = ${year}
AND g.geo_code = ${country};
`;

  const output = buildHierarchy(data);
  process.stdout.write(JSON.stringify(output, null, 2));
} catch (err) {
  console.error("Error:", err);
} finally {
  await sql.end();
}

As for using this data on a page, I've attempted to attach the file like this in a js block:

// sunburst.md (within a js block)
const sunburst = await FileAttachment(
  `data/sunburst-${observable.params.country}-${observable.params.year}.json`
).json();

[Fil]

I think what's missing here is that the markdown page should also be parametrized (sunburst-[country]-[year].md). See https://observablehq.com/framework/params

[avindhyac]

I added the parameters in the markdown page like you said (sunburst-[country]-[year].md)

// sunburst-[country]-[year].md

// file attachments
const countries = await FileAttachment("data/countries.csv").csv();

const sunburst = await FileAttachment(
  `data/sunburst-${observable.params.country}-${observable.params.year}.json`
).json(); // relevant dataloader

How do I send user inputs as parameters back to the data loader though?

// Create user input controls
console.log(countries);
const selectedCountry = view(
  Inputs.select(countries, {
    label: "Select a country:",
    format: (d) => d.geo_name,
    value: (d) => d.geo_code,
  })
);

const selectedYear = view(
  Inputs.range([2023, 2030], { label: "Year:", step: 1, value: 2023 })
);

I tried to assign them directly to the variables but they're immutable:

observable.params.country = selectedCountry
observable.params.year = selectedYear

I've also set up dynamic paths through observablehq.config.js:

  async *dynamicPaths() {
    // Querying database for available countries and years
    const results = await run(async (sql) => {
      return await sql`
        -- sql to pull year and countries --
      `;
    });

    // Generating a path for each country-year combination
    for (const { geo_code, year } of results) {
      yield `src/sunburst-${geo_code}-${year}`;
    }
  }

[csdiehl16]

Hi Avindhya - the data loaders run at build time, so it's not possible to pass back user input you get at run time. This is so that dashboards load quickly by not running a new query in response to user input. The way you have it set up with parameterized routes, a user selecting a country and year should take them to a new page, which loads the static file that has already been produced by the data loader at build time.

If you're creating a single-page application, you might want to look into creating a duckDB database client, which would allow you to run SQL queries based on user input: https://observablehq.com/framework/lib/duckdb