Simple Data Analysis (SDA)

SimpleDB uses a deferred execution model. Sync builder methods (like filter() or convert()) only queue operations. Execution is triggered when an async observer method (like getData(), log(), or writeData()) is awaited.

• run(): Use this when your script ends in pure mutations with no observer. It executes all queued methods across every table in the database in program order.
• close(): Executes all pending transformations, saves SDA metadata (for writable persistent databases), and closes the connection, cleans up temporary files, and the cache.

// Nothing is observed after the mutations, so run() executes them.
table1.loadData("data1.csv").convert({ price: "number" });
table2.loadData("data2.csv").filter(`price > 0`);
await sdb.run();

// close() executes queued transformations before cleaning up resources.
table.loadData("data.csv").convert({ price: "number" });
await sdb.close();

SDA is built on DuckDB and uses a lazy execution model to optimize performance:

• Lazy Transformation Queueing: Core transformation methods are synchronous and chainable. Instead of executing immediately, SDA queues these operations. Consecutive compatible operations are fused into a single DuckDB statement to reduce database round trips.
• Synchronous vs. Asynchronous Methods:
  • Transformation methods are synchronous and chainable.
  • Observer methods (e.g., getData(), log(), writeData()) are asynchronous and flush the transformation queue before producing a result.
  • Export/Answer methods (e.g., aiRAG(), toBucket(), toSheet(), toDatawrapper(), toGeoDatawrapper(), writeChart(), writeMap()) are asynchronous and must be awaited.
• Lazy Dependency Loading: AI, Google, and charting dependencies are only loaded when those specific operations are executed, minimizing startup costs.

SDA provides several ways to visualize data:

1. Terminal Charts: Use logBarChart, logDotChart, logLineChart, or logHistogram for quick terminal output.
2. Observable Plot Images: Use writeChart to generate .png or .svg files using Observable Plot.
3. Observable Plot Maps: Use writeMap to generate map images.

// Example: Creating a chart with writeChart
import { SimpleDB } from "@nshiab/simple-data-analysis";
import { dodgeX, dot, plot } from "@observablehq/plot";

const sdb = new SimpleDB();
const table = await sdb
  .newTable()
  .loadData("https://raw.githubusercontent.com/nshiab/simple-data-analysis/main/test/geodata/files/firesCanada2023.csv")
  .filter(`hectares > 1`)
  .replace("cause", { "H": "Human", "N": "Natural", "U": "Unknown" })
  .log();

await table.writeChart(
  (data) =>
    plot({
      height: 600,
      width: 800,
      color: { legend: true },
      y: { type: "log", label: "Hectares" },
      r: { range: [1, 20] },
      marks: [
        dot(
          data,
          dodgeX("middle", {
            fx: "cause",
            y: "hectares",
            fill: "cause",
            r: "hectares",
          }),
        ),
      ],
    }),
  "sda/output/chart.png",
);

await sdb.close();

Use toBucket to upload a table to GCS and loadBucket to download and load an object. Authentication relies on Google Application Default Credentials (ADC).

Required Environment Variables:

• BUCKET_PROJECT: Your Google Cloud project ID.
• BUCKET_NAME: Your GCS bucket name.
• GOOGLE_APPLICATION_CREDENTIALS (Optional): Path to a service account JSON file.

BUCKET_PROJECT=my-google-cloud-project
BUCKET_NAME=my-storage-bucket
GOOGLE_APPLICATION_CREDENTIALS=./service-account.json

import { SimpleDB } from "@nshiab/simple-data-analysis";

const sdb = new SimpleDB();

const temperatures = await sdb
  .newTable("temperatures")
  .loadBucket("inputs/temperatures.parquet")
  .filter("temperature > 30")
  .log();

const uri = await temperatures.toBucket(
  "outputs/hotTemperatures.parquet",
  { overwrite: true },
);

console.log(uri); // gs://my-storage-bucket/outputs/hotTemperatures.parquet

await sdb.close();

Use loadSheet() to read from a Google Sheet tab and toSheet() to write to one. Authentication requires a service account.

Required Environment Variables:

• GOOGLE_SERVICE_ACCOUNT_EMAIL: The service account email.
• GOOGLE_PRIVATE_KEY: The service account private key.
• GOOGLE_APPLICATION_CREDENTIALS (Alternative): Path to the service account JSON file.

Note: You must share the spreadsheet with the service account email before running.

// Writing to a sheet
import { SimpleDB } from "@nshiab/simple-data-analysis";

const sdb = new SimpleDB();
const temperatures = sdb.newTable("temperatures");

await temperatures
  .loadData("https://raw.githubusercontent.com/nshiab/simple-data-analysis/main/test/data/files/dailyTemperatures.csv")
  .renameColumns({ t: "temperature", id: "station" })
  .selectColumns(["station", "time", "temperature"])
  .toSheet("https://docs.google.com/spreadsheets/d/.../edit#gid=0");

await sdb.close();