
## Why Seafowl
Seafowl is a database designed to power data-driven Web apps like dashboards and interactive visualizations.
Its [cache-friendly HTTP API](seafowl-sql-cdn) is optimized for clients sending SQL queries directly from the browser.
As a single-binary database (built in Rust with <ValeIgnore>DataFusion</ValeIgnore> and [delta-rs](seafowl-delta-storage-layer)),
it's easily deployable to multi-region cloud functions that run "at the edge" and query columnar data in object storage.

Of course, before Seafowl can answer clients' SQL queries, it must have access to the data in question.
For Parquet or CSV data accessible over HTTP, Seafowl's [external tables](https://seafowl.io/docs/guides/csv-parquet-http-external) feature enables querying without any preceding import step.

For everything else, the data must be uploaded into Seafowl.
In this blog post, we'll demonstrate how to populate a Seafowl table using [Dagster](https://dagster.io), the hip new Pythonic framework that excels at creating declarative and reactive data pipelines.

## Why Dagster

Dagster solves the problem of unmaintainable data pipelines, by providing a framework for defining them with Python functions that you can run from the command line or a web UI. It doesn't require too much ceremony or "activation energy" to start using it, so even the simplest Python scripts or dbt jobs can start life as a Dagster [asset](https://docs.dagster.io/concepts/assets/software-defined-assets) and benefit from features like parallel processing of data on multiple nodes. You can add complexity if and when it becomes required.

If you want to run Dagster on your own, you can: it's open source. But Dagster Cloud is also available, so the same Python codebase can grow from a proof-of-concept on a developer's laptop to a production data pipeline powering applications gradually, without full rewrites.

## Inserting data into Seafowl from a Dagster pipeline

Here's how easy it is to append an "upload to Seafowl" step to the end of the Hacker News data pipeline from the [canonical "Hello Dagster" example](https://docs.dagster.io/getting-started/hello-dagster):

```python
@op
def export_to_seafowl(
    context, config: ExportToSeafowlConfig, data: pd.DataFrame
) -> None:
    context.log.info("exporting to seafowl")
    conn = SeafowlConnectionParams(
        url=config.url,
        secret=config.secret,
        database=None)
    destination = QualifiedTableName(schema="public", table=config.table)
    dataframe_to_seafowl(data, conn, destination)
```

Check out the [demo project repository](https://github.com/splitgraph/seafowl-dagster-demo) to follow along as we load the data used in the official [Hello Dagster](https://docs.dagster.io/getting-started/hello-dagster) example into Seafowl!

### 3-minute setup

#### Seafowl

If you don't already have a running Seafowl instance, head over to the [Seafowl Releases page](https://github.com/splitgraph/seafowl/releases/latest) and download the appropriate build for your platform.
Move the `seafowl` binary into the demo repo's folder prior to execution to use the provided `seafowl.toml` configuration.

#### Python and Dagster setup

You will also need a Python installation containing [Dagster](https://dagster.io/) and the [Python Seafowl client](https://github.com/splitgraph/seafowl/tree/main/examples/clients/python).

The following commands create such a virtualenv for you:

```bash
python3 -m venv venv
# Note: the following command is required before running anything installed in the virtualenv
. venv/bin/activate
pip install -e 'git+https://git@github.com/splitgraph/seafowl.git@main#egg=seafowl[pandas]&subdirectory=examples/clients/python'
pip install dagster dagit
```

### Starting Dagster
To start the Dagster UI (called "Dagit"), run the following:
```bash
dagster dev -f demo.py
```

Once the UI is running, the assets must be materialized before the data can be sent to Seafowl:

<video width="100%" height="240" controls>
  <source src="/static-v2/demo-videos/dagster-materialize.mp4" type="video/mp4" />
</video>


After the assets materialize, it's time to configure and run the `hn_stories_to_seafowl_pipeline` job:

<video width="100%" height="240" controls>
  <source src="/static-v2/demo-videos/dagster-run-job.mp4" type="video/mp4" />
</video>

### Querying Seafowl

The [Python Seafowl Client](https://github.com/splitgraph/seafowl/tree/v0.4.1/examples/clients/python) library can be used from the command line to verify the data has been successfully written to Seafowl by the Dagster job:

```bash
PASSWORD="iHBHflYfEaMpX4j7Du0z6vFNS6w2BMaX" ENDPOINT="http://127.0.0.1:8080/q" python -m seafowl "SELECT * FROM hn"
```

## Other ways to load data into Seafowl

As convenient as Dagster is, you might not need an ETL pipeline if your data is already in a Splitgraph repository.

You can export query results directly from Splitgraph to Seafowl. Just click the button with a database icon in the Splitgraph Console: ![Export to seafowl](/static-v2/seafowl/blog/seafowl-dagster/export-to-seafowl.png)

You can import CSV or Parquet files into Seafowl by [uploading them via HTTP](https://seafowl.io/docs/guides/uploading-csv-parquet).

In the above demo, the Dagster job invokes the [Python Seafowl Client](https://github.com/splitgraph/seafowl/tree/v0.4.1/examples/clients/python) to upload a Pandas DataFrame into Seafowl, which can be used directly from custom Python code.

For TypeScript users, the [madatdata library](https://github.com/splitgraph/madatdata) provides a polished API for querying and importing data into Seafowl.

Seafowl also includes a [Node.js client](https://github.com/splitgraph/seafowl/blob/v0.4.1/examples/clients/node/seafowl-client.js).

Finally, one can simply [`INSERT` data directly](https://seafowl.io/docs/guides/writing-sql-queries) into Seafowl.

The Python Seafowl client's `dataframe_to_seafowl()` function uses the Parquet upload endpoint if the `pyarrow` Python package is installed.
Otherwise, it issues `INSERT` queries for each row individually.

## Conclusion

Building interactive analytical applications like [Open Data Monitor](opendatamonitor) on Seafowl starts with uploading data. Dagster is a powerful tool for creating data pipelines which populate the Seafowl tables queried by users' browsers.


We're looking forward to seeing the incredible analytical applications you build with Seafowl! If you're excited about using Dagster with Seafowl or missing something, [let us know](mailto:hello@splitgraph.com?subject=seafowl%20dagster)!


Import the result of your data pipeline into Seafowl easily using Dagster!

Using Dagster with Seafowl


# Introducing SQLite file uploads

Splitgraph already had excellent support for [importing CSV files](csv-to-sql-upload-to-data-delivery-network-ddn), but now we can use the same interface to upload SQLite files as well. Try it out:

<EmbeddedUpload />

# Why use Splitgraph for SQLite data?

[SQLite](https://sqlite.org/index.html) is an incredibly versatile embedded database, used by apps on mobile devices, client code running in web browsers (via WebAssembly), and native applications.
The main difference compared to more traditional relational databases such as PostgreSQL is that an SQLite database is just a file, there's no need for a dedicated database server process.
This is an enormous advantage in resource-constrained environments and makes it possible to build cost-effective data-intensive applications using [Litestream](https://litestream.io/) for example.
At the same time, SQLite's database-as-a-file approach makes concurrent multi user access -and thus collaboration- difficult.

In addition to collaboration, Splitgraph offers countless features beyond what SQLite provides, for example:
- column-based data storage for efficient aggregation queries
- immutable data images
- built-in support for [`dbt` transformations](dbt)
- joining tables from other databases using [foreign data wrappers](foreign-data-wrappers)

# What about Datasette?

[Datasette](https://datasette.io/) is a versatile open source tool for data analysis and publishing built on SQLite.
It's a popular solution for making SQLite data available to the public, but doesn't have built-in support for controlling access to published data.
For those Datasette users who need [fine grained access control](data-sharing-settings-invites-private-repositories), Splitgraph offers a solution.

Similar to Datasette, Splitgraph also makes it possible to directly query database tables [from the browser](select-from-the-browser).
Unlike Datasette, authenticated Splitgraph-backed web apps can even write to database tables.

Datasette documentation includes two demo databases, [`global-power-plants.db`](https://global-power-plants.datasettes.com/global-power-plants.db
) and [`legislators.db`](https://congress-legislators.datasettes.com/legislators.db), both of which can be loaded into Splitgraph. You can also try the SQLite version of the [Chinook](https://github.com/lerocha/chinook-database/blob/master/ChinookDatabase/DataSources/Chinook_Sqlite.sqlite?raw=true) reference database. Try [uploading them here](/upload)!

# Key differences and limitations

SQLite [inherited](https://www.pgcon.org/2014/schedule/events/736.en.html) most of it's syntax from PostgreSQL, so the majority of SQLite queries should work on Splitgraph out of the box.
A major difference is that by default, SQLite allows any type of data to be written to any column, regardless of the column's declared type.
In fact, column type declarations are entirely optional in SQLite!

PostgreSQL is more strict when it comes to enforcing data types. Importing SQLite databases which store different types of data in a column than what was declared in the table schema may fail.
Tables containing columns without a declared type are ignored by the current implementation of SQLite import.

We'd love to hear from you! If you're excited about this new feature or if you're missing something from the current SQLite import functionality, [let us know](mailto:hello@splitgraph.com?subject=sqlite%20import)!


Importing SQLite data into Splitgraph is now as easy as drag-n-drop!

SQLite file uploads

Announcing the replacement of our custom storage layer with Delta Lake, facilitated by its open-source Rust implementation.

A Lakehouse by the sea: Migrating Seafowl storage layer to delta-rs


![](/static-v2/og/blog/opendatamonitor.png)

## Tracking open government datasets
Say hello to [Open Data Monitor](https://open-data-monitor.splitgraph.io/), an open government dataset diff visualizer. Observe when [Socrata](https://dev.socrata.com/) datasets were added/deleted by day, week, and month, with upstream attribution and easy querying via the respective Splitgraph repository.

In a previous [post](https://www.splitgraph.com/blog/seafowl-sql-cdn) we discussed the benefits of Seafowl's approach to HTTP caching; in particular its effective use of [ETags](https://developer.mozilla.org/en-US/docs/Web/HTTP/Headers/ETag) to deliver results on the edge with excellent latency characteristics. Today, we continue on our journey to:
- Ingest Splitgraph's Socrata catalog into Seafowl
- Insert tables that expose added/deleted status ("observe" Socrata)
- Swap Fly.io instances in a cost-effective way
- Build a mini Next.js app to render it all out in the browser, making sure to balance the best of SSR SEO benefits with Seafowl's browser-friendly cache semantics.

## Ingestion
In case you didn't already know, Splitgraph maintains a catalog of Socrata datasets, stored as a [Splitgraph repository at `splitgraph/socrata`](https://www.splitgraph.com/splitgraph/socrata/). Additionally, Splitgraph actually _indexes_ all of the 40,000+ datasets and makes each one queryable at its own URL (like [`cdc-gov/20202021-nationwide-blood-donor-seroprevalence-mtc3-kq6r`](https://www.splitgraph.com.cdc-gov/20202021-nationwide-blood-donor-seroprevalence-mtc3-kq6r)) and Splitgraph repository on the Data Delivery Network (DDN). It's possible to point your favorite Postgres client to the DDN and query arbitrary Socrata datasets in a familiar way. From [traffic accidents](https://neumark.github.io/splitgraph-map-demo/) in Fort Worth, TX [^1], to [taxi rides](https://nyctaxis.surge.sh/) as a proxy for "how's NYC Covid bounce back going"[^2], we've built several integrations that present data in visual and interactive ways without custom plumbing.
We also made [Socrata roulette](https://splitgraph.github.io/socrata-roulette/) for generating random queries against all of the available datasets.

As useful as the DDN is, today we will demonstrate how [Seafowl](https://seafowl.io) can make for an effective alternative that performs with low latency and generally has a smaller resource footprint. First, by starting with this catalog, we [fetch](https://github.com/splitgraph/socrata-to-seafowl/blob/2df59549675136056b096cac7d96f18bd3a8a7ad/src/s2sf/ingest.py#L42) all Socrata images courtesy of Splitgraph's GQL API. (Conveniently we can export directly in [Parquet format](https://github.com/splitgraph/socrata-to-seafowl/blob/master/src/s2sf/ingest.py#L141)!) Then, we [insert](https://github.com/splitgraph/socrata-to-seafowl/blob/2df59549675136056b096cac7d96f18bd3a8a7ad/src/s2sf/ingest.py#L213) the results into Seafowl tables. This whole job is powered by Github [Actions](https://docs.github.com/en/actions), and occurs on a regular basis (currently nightly). After we've ingested the new state of all Socrata datasets, we [pre-compute a diff across time](https://github.com/splitgraph/socrata-to-seafowl/blob/master/src/s2sf/sql/daily_diff.sql) of all datasets that were added or removed on a given day. We also downsample these diffs to week-over-week and month-over-month changes.

Because this involves a self-join of a table with several million rows, this currently requires a large amount of memory as you'll see later.

##  Dynamically scaling our Fly.io instance for ingestion
Similarly to the [Seafowl tutorial](https://seafowl.io/docs/getting-started/tutorial-fly-io/introduction), we again went with Fly.io for hosting. While the free tier generally offers enough performance for Open Data Monitor's read-heavy workload, when it comes to _ingestion_ and building the day-over-day diff table, this time we ran into memory issues because of a self-join. It is perhaps unsurprising that as Socrata has grown over time, so have the number and size of datasets on offer. That's why we added a step in the GHA to temporarily [scale up](https://github.com/splitgraph/socrata-to-seafowl/blob/master/.github/workflows/sync.yml#L30) the Fly.io instance memory for ingestion purposes. We also include an [insurance policy](https://github.com/splitgraph/socrata-to-seafowl/blob/master/.github/workflows/downsize-insurance.yml) to help reduce risk of job errors causing unexpected cloud costs.

## Rendering in the browser
With a loaded Seafowl instance ready to go, we can now build a [Next.js app](https://github.com/splitgraph/open-data-monitor) so we can fetch from Seafowl and render it in the browser.

By default, we render the current week, but the user can specify a certain day, week, or month.

## Disappearing data examples

<div style={{display: 'flex'}}>
<div>
Because traversing over time is now trivial, we can surface curious moments.
<br /><br />
For example, just a few clicks moving backwards from January 2023 yielded disappearing

- [greenhouse gas emissions](https://open-data-monitor.splitgraph.io/2023-01-13#data.bayareametro.gov)
- [commercial ovens](https://open-data-monitor.splitgraph.io/2023-01-13#data.energystar.gov)
- [hot spots](https://open-data-monitor.splitgraph.io/2023-01-05) 🤔
- [Austin Schools Rated as Improvement Required](https://open-data-monitor.splitgraph.io/2022-12-31#data.austintexas.gov)

</div>
<img src="/static-v2/seafowl/blog/open-data-monitor/opendatamonitor-hero-2.png"
  style={{float: 'right', width: '300px', paddingLeft: '2em'}}
  alt="detective smoking a pipe"
/>
</div>


## SSR by default, but browser talks to Seafowl directly on nav
To strike a balance between SSR-conferred SEO advantages, and Seafowl's HTTP cache friendly defaults, we pre-fetch inside [getInitialProps](https://github.com/splitgraph/open-data-monitor/blob/153e4464eb24994ce5503680ce4b907a8e1faa8d/pages/index.tsx#L26), which means the page renders properly even with JS disabled.
But since we don't want the Vercel server to intermediate subsequent fetches, when the user clicks Previous/Next, their browser requests from Seafowl directly. Because we are using the GET API, we benefit from the nice cache behavior, so subsequent loads are nice and zippy, even on Fly.io's free tier, and even if the actual ingest was a memory hungry job.

<video width="100%" height="240" controls>
  <source src="/static-v2/demo-videos/opendatamonitor-demo.mp4" type="video/mp4" />
</video>

## Read the code

The code for all of this is available on GitHub, in two repositories:

- [splitgraph/socrata-to-seafowl](https://github.com/splitgraph/socrata-to-seafowl): GitHub Actions for syncing from Socrata to Seafowl instance hosted on Fly.io
- [splitgraph/open-data-monitor](https://github.com/splitgraph/open-data-monitor): The Next.js frontend application



## Conclusion

We hope this post has illustrated a pathway to combine Seafowl's "analytics at the edge" benefits with Socrata's bevy of government data, in a cost-effective way.
While today we focused on open government data, regardless of the data you're working with, consider checking out what Seafowl offers.

If you discover any curious dataset mysteries, or build an integration with Seafowl, we'd love to hear from you. Tweet us [@splitgraph](https://twitter.com/intent/tweet?text=@splitgraph).

Happy sleuthing!


[^1]: https://www.splitgraph.com/jumpstart/mapping-geo-data
[^2]: https://github.com/splitgraph/nyctaxis


##### Image credits
[mediamodifier](https://unsplash.com/photos/yx17UuZw1Ck), [sammywilliams](https://unsplash.com/photos/ocvLVIrC7c0), [ayosake](https://unsplash.com/photos/esOA3o92zxE)


How can we track open government datasets over time? Say hello to Open Data Monitor, a Socrata tracking tool powered by Seafowl and Splitgraph.

Open Data Monitor


We've recently released a new feature in Seafowl, allowing one to [remotely query](/seafowl/docs/guides/remote-tables) a variety of external data sources. Coincidentally,
the feature is completely independent of Seafowl itself; anyone using DataFusion as a query engine framework can make use of it.
In fact, to facilitate this, we've factored out the code to a stand-alone crate named [`datafusion-remote-tables`](https://github.com/splitgraph/seafowl/tree/main/datafusion_remote_tables)!

In this blog post we'll touch on how this querying mechanism works. Specifically, we'll focus on how we achieve filter
pushdown, a special optimisation reducing the amount of data needed to be transferred over the network. As it happens, this also serves
as a great overview of some DataFusion internals as well as a couple of fundamental Rust concepts[^1] at the same time.

## DataFusion tables and filter expressions 101

In order for a particular data source to become queryable in DataFusion, it needs to implement the
[`TableProvider` trait](https://docs.rs/datafusion/latest/datafusion/datasource/datasource/trait.TableProvider.html).
It is an `async_trait`, so as to enable an asynchronous `scan` method, which is responsible for creating a physical plan
for a particular query.

The `scan` method receives a number of useful parameters:
- `projection`: An optional list of column indices. If provided scopes down the required output to only a subset of table's columns.
- `filters`: A slice of `Expr`s (see below) parsed from the `WHERE` clause.
- `limit`: Denotes the maximum number of rows requested (`LIMIT` clause).

All of these represent an optimisation strategy for pruning the data closer to where it actually lives. In our remote
table implementation of this trait we use all these arguments to re-construct a SQL query in a string form native to the
corresponding remote data source and execute it[^2].

Importantly, an `Expr` is a DataFusion enum representing an arbitrary logical expression (and thus filters as well):

```rust
#[derive(Clone, PartialEq, Eq, Hash)]
pub enum Expr {
    ...
    /// A named reference to a qualified field in a schema.
    Column(Column),
    /// A constant value.
    Literal(ScalarValue),
    /// A binary expression such as "age > 21"
    BinaryExpr(BinaryExpr),
    ...
}
```

It is also recursive, as some of the variants contain structs that are themselves composed out of `Expr`:

```rust
#[derive(Clone, PartialEq, Eq, Hash)]
pub struct BinaryExpr {
    /// Left-hand side of the expression
    pub left: Box<Expr>,
    /// The comparison operator
    pub op: Operator,
    /// Right-hand side of the expression
    pub right: Box<Expr>,
}
```
while others (e.g. `Column` and `ScalarValue`) represent leaf nodes in the expression tree.

#### `supports_filter_pushdown` method

Just because `WHERE` and `LIMIT` clauses are used in a statement, doesn't mean they'll be passed as arguments to the
`scan` function. This is ultimately controlled by another method in the `TableProvider` trait named `supports_filter_pushdown`.

During plan optimisation phase, DataFusion splits the whole `WHERE` clause into constituent `Expr`s separated by `AND`s.
Then it calls `supports_filter_pushdown` method for each `Expr` to find out the corresponding pushdown status:

- `Unsupported`: The filter node is kept in the plan, and it won't be passed down into `scan`. DataFusion will perform filtering once all
the matching rows are fetched. This is the default pushdown status.
- `Inexact`: The filter node is kept in the plan, but it is also passed down into `scan`. The data source will try to filter
as much data as it can, but it doesn't guarantee that all rows pass the provided filter[^3].
- `Exact`: The filter node is removed from the plan and is passed down into `scan`. The data source guarantees that all returned
rows match the filter.

The reason why the `WHERE` clause is broken up at the `AND`s is for enabling granular pushdown: all individual conjunction
components that are shippable can be pushed down independently of each other's pushdown status. This allows for reaping the
performance benefits even when only a part of the whole `WHERE` clause is shippable, while retaining correctness.

## FDW approach

Note that `projection` is said to be commutative with `filters`, meaning the order of their application (source vs destination) is not important
(i.e. one trims down the columns, while the other trims down the rows returned). The implication of this is that `projection`
always gets pushed down to `scan`, regardless of the filter pushdown status.

However, `limit` is not commutative with `filters`, as both prune rows according to different rules. Hence, `limit` is passed
down to `scan` if and only if all filter expressions in a query are eligible for exact pushdown. Keep in mind that the potential reduction
in the amount of data retrieved with these two parameters is anywhere between 0 and 100%, so the performance benefits
can be very large.

Therefore, in order to make the most out of it, it pays off to be as diligent as possible in `supports_filter_pushdown`.
We need to ensure that we can determine the shippability of each (part of each) expression in a query, and when needed
translate it to the dialect of the remote data source.

This problem has already been encountered in the family of Postgres foreign data wrappers (FDWs). The usual approach there
is to recursively walk the AST of the query, converting it piece-by-piece into remote-compatible SQL if possible, otherwise
forego the filter pushdown.

## Traversing a DataFusion's `Expr`

Fortunately, DataFusion provides the primitives that suit our needs in the form of
[`ExpressionVisitor` trait](https://docs.rs/datafusion-expr/15.0.0/datafusion_expr/expr_visitor/trait.ExpressionVisitor.html).
It employs the visitor pattern for walking the entire tree of an arbitrary `Expr` in a post-order manner. In particular,
it provides two convenient methods for hooking custom actions. One is invoked just prior to visiting all the
children of a given node (`pre_visit`), while the other is invoked once all the child nodes have been visited (`post_visit`).

Consider the following illustrative example:

```sql
SELECT ... WHERE c_1 ^ 21 > c_2 OR c_3 = 'abc'
```

The entire filter expression and the node visit order looks like below
![](/static-v2/seafowl/blog/datafusion-filter-expr-visitor/expr-tree-visit.png)

This should now clarify how we can utilise the two hooks in `ExpressionVisitor` trait for our purpose. On the one hand,
we can check for the shippability of the expression, operator or data type of the current node in the `pre_visit` method.
If it's not compatible with the remote there's no need to waste time visiting any of the children, so we can exit the
visit immediately and inform DataFusion about this (i.e. flag the filter pushdown as `Unsupported` in `supports_filter_pushdown`).

On the other hand, if we do reach the `post_visit` method on a particular node it means that it (alongside all its children
nodes) is shippable. In other words, there is a corresponding remote-compatible SQL string (and we should flag the
filter pushdown as `Exact` in `supports_filter_pushdown`). Furthermore, this enables a bottom-up approach of generating
the equivalent SQL efficiently.

Namely, we can't generate the SQL representation for a recursive node (in our case `Expr::BinaryExpr`s) before knowing
the SQL of its components. Therefore, we must first reach the leaf nodes (`Expr::Column` and `Expr::Literal`), which
can be independently converted to SQL, and then progressively build the SQL up from there.

An ideal way to facilitate this is to use a stack. Simply push resulting SQL for each expression to the stack, and for any interpolation
(recursive nodes) use the SQL from child nodes (i.e. pop from the stack). By the time we exit from `post_visit` of a root node, we will
have a single element on the stack, representing the SQL of the entire expression.

## Filter translation

Prior to implementing the `ExpressionVisitor`, we need to keep in mind one more requirement for our filter pushdown
mechanism, and that is re-usable polymorphism. In other words, we want to have the same interface regardless of the remote source
type, and re-use as much of it as possible. For start we aim to support PostgreSQL (whose dialect DataFusion is closest to),
as well as MySQL and SQLite.

To achieve this let's first make a new trait, one that converts a leaf node or an operator to SQL for a particular data source:
```rust
pub trait FilterPushdownConverter {
    fn col_to_sql(&self, col: &Column) -> String {
        quote_identifier_double_quotes(&col.name)
    }

    fn scalar_value_to_sql(&self, value: &ScalarValue) -> Option<String> {
        match value {
            ScalarValue::Utf8(Some(val)) | ScalarValue::LargeUtf8(Some(val)) => {
                Some(format!("'{}'", val.replace('\'', "''")))
            }
            _ => Some(format!("{}", value)),
        }
    }

    fn op_to_sql(&self, op: &Operator) -> Option<String> {
        Some(op.to_string())
    }
}

pub fn quote_identifier_double_quotes(name: &str) -> String {
    format!("\"{}\"", name.replace('\"', "\"\""))
}
```
Here we make sure to quote the identifier used in the column expression to handle any special characters properly (such as spaces).
In addition, we enclose the string literals in single quotes, as well as convert the `Operator` enum to the default string representation (relying on the `Display` trait).
The default trait implementation is perfectly adequate for Postgres:
```rust
pub struct PostgresFilterPushdown {}

impl FilterPushdownConverter for PostgresFilterPushdown {}
```

However, the other two data sources require some changes. For instance, MySQL uses backticks to escape the identifiers,
while the bitwise xor operator is denoted as `^` (and not `#` as in Postgres, which `op.to_string()` returns):

```rust
pub struct MySQLFilterPushdown {}

impl FilterPushdownConverter for MySQLFilterPushdown {
    fn col_to_sql(&self, col: &Column) -> String {
        quote_identifier_backticks(&col.name)
    }

    fn op_to_sql(&self, op: &Operator) -> Option<String> {
        match op {
            Operator::BitwiseXor => Some("^".to_string()),
            _ => Some(op.to_string()),
        }
    }
}

pub fn quote_identifier_backticks(name: &str) -> String {
    format!("`{}`", name.replace('`', "``"))
}
```

On the other hand, SQLite doesn't have a dedicated bitwise xor operator[^4]:
```rust
pub struct SQLiteFilterPushdown {}

impl FilterPushdownConverter for SQLiteFilterPushdown {
    fn op_to_sql(&self, op: &Operator) -> Option<String> {
        match op {
            Operator::BitwiseXor => None,
            _ => Some(op.to_string()),
        }
    }
}
```

## The winning pushdown

Now, in order to actually employ our filter conversion we need to implement the DataFusion's `ExpressionVisitor`. Note
that the Rust's usual orphan rules apply here: when implementing a trait for some type at least one of them needs to
be defined locally (in the current crate). In our case however we go one step ahead, as we want to implement the
`ExpressionVisitor` trait for any type that also implements the `FilterPushdownConverter` trait. This would ensure
that a single implementation covers all remote types (Postgres, MySQL, SQLite).

The naive way to go about this is to try the following definition:
```rust
impl<T: FilterPushdownConverter> ExpressionVisitor for T {
...
```

However, `rustc` will quickly dissuade you from this approach, as this can also lead to violating the orphan rule:
```rust
error[E0210]: type parameter `T` must be used as the type parameter for some local type (e.g., `MyStruct<T>`)
   --> datafusion_remote_tables/src/filter_pushdown.rs:110:6
    |
110 | impl<T: FilterPushdownConverter> ExpressionVisitor for T {
    |      ^ type parameter `T` must be used as the type parameter for some local type
    |
    = note: implementing a foreign trait is only possible if at least one of the types for which it is implemented is local
    = note: only traits defined in the current crate can be implemented for a type parameter

For more information about this error, try `rustc --explain E0210`.
```
In other words, you could inadvertently implement `FilterPushdownConverter` for some foreign type that already implements
`ExpressionVisitor`, at which point there would be a conflict. Thankfully, the error message is very helpful and suggests
the proper course of action. Namely, we must use a local struct in the definition instead. This works well with our intention
of keeping a stack of converted SQL expression during the visit:
```rust
pub struct FilterPushdownVisitor<T: FilterPushdownConverter> {
    pub source: T,
    // LIFO stack for keeping the intermediate SQL expression results to be used in interpolation
    // of the parent nodes. After a successful visit, it should contain exactly one element, which
    // represents the complete SQL statement corresponding to the given expression.
    pub sql_exprs: Vec<String>,
}

impl<T: FilterPushdownConverter> FilterPushdownVisitor<T> {
    // Intended to be used in the node post-visit phase, ensuring that SQL representation of inner
    // nodes is on the stack.
    fn pop_sql_expr(&mut self) -> String {
        self.sql_exprs
            .pop()
            .expect("No SQL expression in the stack")
    }
}
```

Finally, we can go ahead and implement the two visitor methods:
```rust
impl<T: FilterPushdownConverter> ExpressionVisitor for FilterPushdownVisitor<T> {
    fn pre_visit(self, expr: &Expr) -> Result<Recursion<Self>> {
        match expr {
            Expr::Column(_) => {}
            Expr::Literal(val) => {
                if self.source.scalar_value_to_sql(val).is_none() {
                    return Err(DataFusionError::Execution(format!(
                        "ScalarValue {} not shippable",
                        val,
                    )));
                }
            }
            Expr::BinaryExpr(BinaryExpr { op, .. }) => {
                // Check if operator pushdown supported; left and right expressions will be checked
                // through further recursion.
                if self.source.op_to_sql(op).is_none() {
                    return Err(DataFusionError::Execution(format!(
                        "Operator {} not shippable",
                        op,
                    )));
                }
            }
            _ => {
                // Expression is not supported, no need to visit any remaining child or parent nodes
                return Err(DataFusionError::Execution(format!(
                    "Expression {:?} not shippable",
                    expr,
                )));
            }
        };
        Ok(Recursion::Continue(self))
    }
...
```
Nothing fancy here, all we use the `pre_visit` method for is to bail out of the whole visit as soon as we detect a node
that is not shippable. The meat of the implementation is in the `post_visit` method:

```rust
...
    fn post_visit(mut self, expr: &Expr) -> Result<Self> {
        match expr {
            // Column and Literal are the only two leaf nodes atm - they don't depend on any SQL
            // expression being on the stack.
            Expr::Column(col) => self.sql_exprs.push(self.source.col_to_sql(col)),
            Expr::Literal(val) => {
                let sql_val = self.source.scalar_value_to_sql(val).unwrap();
                self.sql_exprs.push(sql_val)
            }
            Expr::BinaryExpr(BinaryExpr { op, .. }) => {
                // The visitor has been through left and right sides in that order, so the topmost
                // item on the SQL expression stack is the right expression
                let right_sql = self.pop_sql_expr();
                let left_sql = self.pop_sql_expr();
                let op_sql = self.source.op_to_sql(&op).unwrap();

                self.sql_exprs
                    .push(format!("{} {} {}", left_sql, op_sql, right_sql))
            }
            _ => {}
        };
        Ok(self)
    }
}
```
Again, the leaf nodes are simply converted to the equivalent SQL using our `FilterPushdownConverter` trait and pushed
to the stack. As for the binary expression, we first pop two items from the stack, corresponding to right and left
sub-expression SQL, and then interpolate them with the operator. In principle, since the `Expr` has an implicit operator precedence,
we need to convert it to an explicit one using parenthesis if the left/right sub-expression is also a `BinaryExpr` of
lower operator precedence, but we omit that here for the sake of brevity.

## Putting it all together

Lastly, in order to make the full trip from a `Expr` to a SQL string we can define a generic helper function, where we set off
a visit starting at the root expression, and then extract the final SQL representation:

```rust
pub fn filter_expr_to_sql<T: FilterPushdownConverter>(
    filter: &Expr,
    source: T,
) -> Result<String> {
    // Construct the initial visitor state
    let visitor = FilterPushdownVisitor {
        source,
        sql_exprs: vec![],
    };

    // Perform the walk through the expr AST trying to construct the equivalent SQL for the
    // particular source type at hand.
    let FilterPushdownVisitor { sql_exprs, .. } = filter.accept(visitor)?;

    Ok(sql_exprs
        .first()
        .expect("Exactly 1 SQL expression expected")
        .clone())
}
```

For our example expression from above, the resulting PostgreSQL statement is:

```sql
SELECT ... WHERE "c_1" # 21 > "c_2" OR "c_3" = 'abc'
```

while the MySQL output is:
```sql
SELECT ... WHERE `c_1` ^ 21 > `c_2` OR `c_3` = 'abc'
```

As for SQLite, an error gets thrown since the conversion of the bitwise xor operator is not supported. As mentioned, in such
cases a bare `SELECT` without the `WHERE` clause would get pushed down, while the desired filtration is then achieved afterwards.
Had there been an `AND` instead of an `OR` in our query however, DataFusion would have broken the `WHERE` clause into two expressions,
and the second one (`"c_3" = 'abc'`) would have been pushed down.

## Conclusion

We hope this exploratory blog post has intrigued you enough to dig into the DataFusion framework (and perhaps Seafowl too).
On our side, we aim to keep expanding the surface area of shippable filter expressions as well as supported remotes
in the `datafusion-remote-tables` crate, so if you're already using DataFusion you may find it handy.

[^1]:
    And so can be helpful to someone new to DataFusion and/or Rust, like the author of this blog post.
[^2]:
    For actually running the query on the remote we use the [connector-x](https://docs.rs/connectorx/latest/connectorx/) crate.
[^3]:
    This is what ordinary Seafowl tables use, as it allows us to perform [partition pruning](/seafowl/docs/learning/partition-pruning).
[^4]:
    Given that it can be emulated by other primitive boolean operators easily.

We explore the inner workings of DataFusion filter pushdown optimisation, and see what it takes to ship them to remote data sources.

Rust visitor pattern and efficient DataFusion query federation


## Measuring Urbanness

Sometimes a hard-to-measure property has significant impact on performance. For example, it's safe to assume sexier lingerie sells better,
but devising an algorithm to classify the "sexiness" of an article of clothing based on its design is far from trivial.

Another ill-defined quality impacting countless more measurable metrics is "urbanness", an individual's preference for densely or sparsely populated areas.
All sorts of trends, from [voting patterns](https://government.cornell.edu/news/exploring-widening-chasm-between-urban-and-rural-voters),
to life expectancy and religious affiliation all have marked differences in urban versus rural populations.

Statistically, it makes sense to classify someone as urban or rural based on their current residence, regardless of where they may have lived in the past.
On an individual level, it's not always so clear-cut. I currently live in a city with a population of over 1.5 million,
but I've also lived in a small town (less than 4000 residents). Today, a census would classify me as urban, but is that who I really am?

I decided to find out using photo location data. Smartphones usually encode the longitude and latitude in the EXIF data of photographs.
Over the years, I've collected thousands of photos (21808 to be exact), enough to give me a representative sample of where I prefer to spend my time.

## Classifying locations

The geometric center of a city is not always its most densely populated spot. What's a better proxy for "urbanness"?

If urban individuals are those who choose to spend their time in the city, areas offering many places to "hang out" are probably ideal locations for spotting urbanites.
Conversely, if one frequents such areas, chances are they can be labelled as urban. And what could possibly be a more urban place to hang out than the largest coffeehouse chain in the world, Starbucks?

Following in the footsteps of [Golden Arches Theory of Conflict Prevention](https://en.wikipedia.org/wiki/The_Lexus_and_the_Olive_Tree#Golden_Arches_theory),
let's define the equally tongue in cheek **Omnipresent [Siren](https://stories.starbucks.com/stories/2016/who-is-starbucks-siren/) Metric of Urbanness** (OSM) as the number of Starbucks locations within 2 kilometers of any given point on the globe.

Conveniently, there's a dataset of Starbucks locations [which can be downloaded from Kaggle](https://www.kaggle.com/datasets/starbucks/store-locations?resource=download), which I used to classify my photos by this metric.

## Loading photo coordinates

An easy way to extract EXIF metadata from images is to let Google Photos do it for you. For users of Google Photos, [Google Takeout](https://takeout.google.com/) offers a way to download photos along with their EXIF data extracted to separate JSON files.
Although not covered here, it's also possible to read location data (and much more) directly from images using [exiftool](https://exiftool.org/).

After downloading and extracting the Google Takeout archives, I ran the following to get a CSV file with the coordinates of each photo:

```bash
# enter Google Photos directory within Takeout directory
cd 'Takeout/Google Photos'
# extract image metadata from JSON files
find 'Photos from'* -name '*.json' -exec jq -c '{latitude: .geoData.latitude, longitude: .geoData.longitude, timestamp: .photoTakenTime.timestamp, filename: input_filename}' {} >> unfiltered_image_attributes.ndjson \;
# only keep image metadata with GPS data (non-smartphone cameras don't write location data)
grep -v '{"latitude":0,' unfiltered_image_attributes.ndjson  > filtered_image_attributes.ndjson
# write CSV header
echo "latitude,longitude,timestamp,filename" > photo_attributes.csv
# write CSV data
jq -r '([.latitude, .longitude, .timestamp, .filename]) | @csv' < filtered_image_attributes.ndjson >> photo_attributes.csv
```

## Loading data into Seafowl

Seafowl has great CSV support. To see it in action, [install Seafowl](https://seafowl.io/docs/guides/installing) if you haven't yet, then run:

```bash
curl -v -F "data=@starbucks_directory.csv" "http://localhost:8080/upload/public/starbucks_directory"
curl -v -F "data=@photo_attributes.csv" "http://localhost:8080/upload/public/photo_attributes"
```

Note that I changed the CSV header of the [Kaggle Starbucks dataset](https://www.kaggle.com/datasets/starbucks/store-locations?resource=download) for more convenient querying to:
```
brand,store_number,store_name,ownership_type,street_address,city,state,country,post_code,phone_number,timezone,longitude,latitude
```

## Calculating distances

Seafowl doesn't come with built-in functions for calculating the distance between two points on the planet,
but fortunately there is an excellent [Rust library](https://docs.rs/geoutils/latest/geoutils/) we can use.

To call it from Seafowl queries, we must create a user-defined function (UDF) and compile it to [WebAssembly](/blog/seafowl-wasm-udfs).
The code for the UDF is available [on GitHub](https://github.com/splitgraph/seafowl-udf-geo).

After running the [`create_udf.sh`](https://github.com/splitgraph/seafowl-udf-geo/blob/main/create_udf.sh) script, the previously unavailable `distance()` function may be used in queries
like so:

```sql
-- calculate the distance between Berlin and Paris in meters
-- distance(longitude_1, latitude_1, longitude_2, latitude_2) -> distance in meters
SELECT distance(52.5200, 13.4050, 48.8566, 2.3522)
```
We're ready to start classifying our photo collection by the Omnipresent Siren Metric!

## Calculating proximity: the naive approach

A simple way to calculate nearby Starbucks count for each photo is to find the distance to each Starbucks, then filter out distant Starbucks in the `WHERE` clause:

```sql
SELECT
    filename,
    COUNT(0) AS nearby_starbucks_count
FROM (
    SELECT
        filename,
        photo_lat,
        photo_lon,
        starbucks_lat,
        starbucks_lon,
        distance(photo_lat, photo_lon, starbucks_lat, starbucks_lon) as dist
    FROM (
        SELECT
            filename,
            latitude as photo_lat,
            longitude as photo_lon
        FROM photo_attributes
    ) p
    JOIN (
        SELECT
            latitude as starbucks_lat,
            longitude as starbucks_lon
        FROM starbucks_directory
    ) s ON true
) t
WHERE t.dist < 2000 -- distance is in meters, in this case a 2km radius
GROUP BY 1
ORDER BY 2
```

One of the great advantages of using an SQL database is that we can ask how a query will be executed with the `EXPLAIN SELECT ...` statement.
The logical plan for this query is:

```
Sort: nearby_starbucks_count ASC NULLS LAST
  Projection: t.filename, COUNT(Int64(0)) AS nearby_starbucks_count
    Aggregate: groupBy=[[t.filename]], aggr=[[COUNT(Int64(0))]]
      Projection: t.filename, alias=t
        Projection: p.filename, alias=t
          Filter: distance(p.photo_lat, p.photo_lon, s.starbucks_lat, s.starbucks_lon) < Float64(2000)
            CrossJoin:
              Projection: p.filename, p.photo_lat, p.photo_lon, alias=p
                Projection: photo_attributes.filename, photo_attributes.latitude AS photo_lat, photo_attributes.longitude AS photo_lon, alias=p
                  TableScan: photo_attributes projection=[filename, latitude, longitude]
              Projection: s.starbucks_lat, s.starbucks_lon, alias=s
                Projection: starbucks_directory.latitude AS starbucks_lat, starbucks_directory.longitude AS starbucks_lon, alias=s
                  TableScan: starbucks_directory projection=[latitude, longitude]
```

Notice the `CrossJoin` (also called Cartesian product)! It's the type of join between two tables where every row in table A is joined with every row in table B.
The resulting table will have a total row count of `rows in A * rows in B`.
The `distance()` function is pretty fast: a single call returns in 5-6 microseconds on my laptop. Even so, calculating all `25600 * 21808 = 558284800` distances took me about an hour.
Can we do better?

## Calculating proximity: using pre-filtered pairs

Calculating the precise distance between two points on a globe is more complex than on a plane.
Still, we can use a rough heuristic to filter out photo - Starbucks pairs which are obviously much farther away than 2 km.
At the equator, one degree latitude / longitude corresponds to 111km. I've never been so close to the poles that 2 degrees would be less than 2km apart, so any Starbucks
more than two degrees latitude or longitude away from the photo's location will surely be out of the 2km range.

### Pre-filtering pairs
First, I created a table of candidate pairs based on differences in latitude and longitude:

```sql
CREATE TABLE filtered_pairs_less_than_2_degrees_apart AS WITH
    all_pairings AS (
        SELECT
                filename,
                photo_lat,
                photo_lon,
                starbucks_lat,
                starbucks_lon,
                ABS(photo_lat - starbucks_lat) AS diff_lat,
                ABS(photo_lon - starbucks_lon) AS diff_lon
        FROM (
            SELECT
                filename,
                photo_lat,
                photo_lon,
                starbucks_lat,
                starbucks_lon
            FROM (
                SELECT
                    filename,
                    latitude as photo_lat,
                    longitude as photo_lon
                FROM photo_attributes
            ) p
            JOIN (
                SELECT
                    latitude as starbucks_lat,
                    longitude as starbucks_lon
                FROM starbucks_directory
            ) s ON true) r
    )
SELECT *
FROM all_pairings
-- A difference of 2 degrees or more latitude or longitude will definitely
-- result in more than 2 kilometers between the points in the range we're
-- working with.
WHERE diff_lat < 2.0 AND diff_lon < 2.0;
```

The `filtered_pairs_less_than_2_degrees_apart` table has 687911 rows, less than 1% of the rows resulting from the cross join in the previous query.

### Calculating the distance of candidate pairs
The following query filters the pairs further based on the precise distance:

```sql
CREATE TABLE filtered_pairs_less_than_2_km_apart AS SELECT * FROM (
    SELECT
        filename,
        photo_lat,
        photo_lon,
        starbucks_lat,
        starbucks_lon,
        distance(photo_lat, photo_lon, starbucks_lat, starbucks_lon) as dist
    FROM filtered_pairs_less_than_2_degrees_apart) t
-- Filter the list according to the actual distance in meters, so
-- < 2000 results in a 2km radius.
WHERE t.dist < 2000;
```

This second filter throws out over 94% of the rows in the `filtered_pairs_less_than_2_degrees_apart` table, leaving only
Starbucks locations within the 2km radius of each photograph.

The `filtered_pairs_less_than_2_km_apart` table contains 39715 rows, the same number we got with the original hour-long calculation, but the two
`CREATE TABLE` queries take a total of 15.1 seconds, less than 1% of the original runtime.

## My most urban photograph

There's a clear winner among the 21808 contestants for "most urban" photo, with an OSM of 68:

![Sheep in Shanghai](/static-v2/images/blog/20221209-seafowl-geo-distance-udf/IMG_6092.JPG)

Directing the [Starbucks Store Locator](https://www.starbucks.com/store-locator?map=31.232686,121.460572,15z) to the coordinates where the photo was captured helps explain why.
Considering Shanghai has the most Starbucks locations of any city on Earth, more than double that of Seoul at second place, it's not surprising that my "winning" photo was taken there.

## How urban am I

To find out, I needed the number of photographs for each OSM score:

```sql
-- query.sql
SELECT
    nearby_starbucks_count,
    COUNT(0) as num_photos
FROM (
    SELECT
        filename,
        COUNT(0) AS nearby_starbucks_count
    FROM filtered_pairs_less_than_2_km_apart
    GROUP BY 1
) t
GROUP BY 1
ORDER BY 1 ASC;
```

I used the following bash command to get the results of the query as CSV with the help of `jq`:

```bash
curl  -H "Content-Type: application/json" http://localhost:8080/q -d@- << END |
{"query": $(cat "query.sql" | jq -Rsa .)}
END
jq -r '[.nearby_starbucks_count, .num_photos] | @csv' > photos_by_osm.csv
```

Using the [Node.js Seafowl client](https://github.com/splitgraph/seafowl/blob/main/examples/clients/node/seafowl-client.js) is also an option:

```bash
seafowl/examples/clients/node/seafowl-client.js -f filtered_step3_simplified_osm_histogram.sql | tail -n +2 | jq -r '.[] | [.nearby_starbucks_count, .num_photos]' | @csv > photos_by_osm.csv'
```

With most of my photos having an OSM of 0, the answer seems to be that I'm not very urban, especially when compared to someone who feels at home in Shanghai:

![Number of photos by OSM](/static-v2/images/blog/20221209-seafowl-geo-distance-udf/number_of_photos_by_osm.svg)

## Urbanness over time

If "urbanness" is an innate quality unlikely to change much once our adult personality has developed, any reliable indicator should be pretty stable over time.
Looking at the number of photos I've taken with OSM ≥ 1 each month makes it seem like that is indeed the case.

![OSM ≥ 1 photo count by month](/static-v2/images/blog/20221209-seafowl-geo-distance-udf/osm_gte_1_photo_count_by_month.svg)

The exception is a trip to Shanghai in October of 2015 when I took the picture with the plastic sheep shown above.

## Conclusion

It was fun analyzing where I've taken photos over the last decade. Seafowl has been a convenient tool throughout the process:
- CSV import made it easy to work with data from [Kaggle](https://www.kaggle.com/datasets/starbucks/store-locations?resource=download) and Google Photos.
- We can easily extend Seafowl's builtin functions with [our own UDFs](https://github.com/splitgraph/seafowl-udf-geo).
- JSON output is convenient in most cases and can easily be converted to CSV using `jq` when necessary.

I look forward to all the cool stuff people build with this fast new database!


I used Seafowl to analyze how much of a city slicker I am—with geographic user-defined WASM functions and caffeine addiction!

Deciding if I'm urban with WebAssembly and Seafowl


## Do we really need User-Defined Functions?

User-defined functions (UDFs) are bits of application-specific logic which can be invoked within database queries, extending the builtin capabilities of the database.
You might be wondering why not keep application-specific logic in the _application_ layer? Are user-defined functions (UDFs) the relic of a past when databases were designed to be directly queried by end-users?

Far from it! Placing domain-specific logic in the database is often the best (sometimes only) way to implement functionality such as:
- Security policies such as PostgreSQL's [RLS feature](https://www.postgresql.org/docs/current/ddl-rowsecurity.html), which rely on UDFs to enforce application-specific security policies.
- Custom aggregation functions similar to `MIN`, `MAX` and `AVG` which must run within the database to avoid shipping the entire set of input rows to the client to calculate output.
- General, application-independent domain specific capabilities such as working with JSON fields, calculating geographical distances or full text search, extending the core DB's capabilities without having to modify the database itself.
- Finally, if raw and cleaned data live in the same database, it makes sense to use the database to transform that data with tools like [dbt](https://www.getdbt.com/) instead of passing it through a separate process -- and UDFs might be needed to implement some special functionality for that. [^1]

UDFs are an essential building block supported by the majority of databases one way or another, and in this regard Seafowl is no exception. What makes Seafowl special is its commitment to running "at the edge": as close to the user as possible, which means pretty much anywhere.
But if Seafowl is to run _anywhere_, UDFs referenced by Seafowl queries should be just as portable.
How can Seafowl offer UDF authors a platform which makes this possible?

## Platforms

Like any program, a UDF targets a specific platform. This platform should be hardware-independent so UDFs keep working if the database is moved to a different machine, but also efficient enough to process large datasets in acceptable time.

Mature databases often have a custom language for defining UDFs such as [PostgreSQL's PL/PGSQL](https://www.postgresql.org/docs/current/plpgsql.html) or [PL/SQL](https://www.oracle.com/database/technologies/appdev/plsql.html).
For simple functions just beyond the capabilities of an SQL statement, these languages offer an ideal solution.
For more complex functionality or code with lots of dependencies, many developers choose a general programming language, preferably one used in the application layer.

PostgreSQL and Redis both support authoring UDFs in Lua, for example. As a high-level language,
Lua abstracts away the lower layers of the stack such as the CPU's instruction set or the flavor of the C standard library.
Instead, the platform becomes the Lua virtual machine and standard library.
Lua is designed to be embedded in the host process, allowing scripts to efficiently invoke native functions exposed by the host.
It isn't the only scripting language embedded in databases. PostgreSQL for example supports many scripting languages including javascript and python.
While the source of the UDF may be portable, Lua, Javascript, Python, Ruby rely on native modules typically written in C or C++ for performance-intensive code such as crypto libraries.
These modules are co-located with the database and must be recompiled for each architecture supported by the database, limiting the portability of UDFs using these functions.

Custom logic written in compiled languages such as C++, Rust or Java is more difficult to invoke from a native database process, requiring
either running the UDF in a separate process or compiling against the C ABI of the database and executing it with full access to the database.
The security implications of the second option rule out this approach for UDFs.

Inter-process communication (IPC) with a separate JVM process provides a portable solution supported by [Oracle](https://docs.oracle.com/cd/B19306_01/java.102/b14187/chfive.htm) and [IBM DB2](https://www.ibm.com/docs/en/db2-for-zos/11?topic=java-stored-procedures-user-defined-functions),
but introduces a performance penalty for each UDF invocation along with additional corner cases arising from the need to synchronize a JVM process with the database.
For example, should updating the Java bytecode for a UDF impact running transactions?
If not, how many versions of the UDF must be kept in memory and how should the database keep track of which is the current version in each transaction?
When is it safe to remove classes upon which a previous version of the UDF depends?
If a transaction defining a UDF is rolled back, which classes' bytecode needs to be ejected from the JVM?
The list goes on and on, and it's mostly independent of Java, stemming instead from the marriage of a transactional database and another process which dynamically loads UDF code.

To summarize, there's hardly a silver bullet: each solution for providing UDFs has its pros and cons.
Worse, none of the solutions considered so far are truly portable in the sense that they can be used in the browser, on dedicated servers and desktops without serious limitations.

## Enter WebAssembly

Predating Seafowl and Splitgraph by decades, developers of large native applications faced a similar conundrum.
As new devices and platforms appeared, customers wanted to run their applications in previously unsupported environments.
Porting existing applications to new platforms was the traditional remedy, a slow and expensive process.

In the 90s, Java tried to address this problem, promising developers they could "write once, run everywhere".
As Java eventually found success in the very different niches of backend services and Android devices,
its early focus on "running everywhere" understandably changed to working best where it's used.

With the explosion of web apps, the browser appeared as yet another platform for applications to support, albeit a highly unusual one:
instead of a CPU instruction set, developers were to target a high level language, JavaScript.
Garbage collection came with the package, but integers were out (JavaScript has only floating point numbers).
Being so different from other platforms, porting existing applications to the web seemed more difficult than a complete rewrite in many cases.

Even green field projects struggled to find a stack sufficiently portable and efficient to ensure the application's long-term growth in desktop, mobile and browser environments.

As the performance of computers in general and JavaScript engines in particular improved, it became feasible to emulate a CPU in JavaScript.
A single C/C++ codebase could now be compiled to run natively on desktops or mobile devices, but also run in the browser with [emscripten](https://emscripten.org/), which targeted the javascript-powered CPU emulator.
It was hacky, but it worked and the world started to [take notice](https://kripken.github.io/mloc_emscripten_talk/#/).

With growing demand for efficiently running compiled code in the browser, Chrome, Firefox and Safari started to include new virtual machine called WebAssembly (WASM), very different from the high-level javascript VM:
- It wasn't optimized for a specific programming language like V8 or the JVM.
- It doesn't support sophisticated just-in-time compilation.
- It doesn't even have a garbage collector.

Instead, the WebAssembly specification provides a limited set of low-level instructions and linear memory model.
Thanks to the relative ease of implementation, today there are [many](https://wasmer.io/) [high](https://wasmedge.org/) [quality](https://github.com/wasm3/wasm3) [implementations](https://wasmtime.dev/), allowing code targeting WebAssembly to run
anywhere from embedded devices to [browsers](https://developer.mozilla.org/en-US/docs/WebAssembly), [CDNs](https://blog.cloudflare.com/announcing-wasi-on-workers/), and yes- in [databases](https://seafowl.io/docs/guides/custom-udf-wasm) as well.

## WebAssembly as a platform for UDFs

How does WebAssembly as a platform for implementing UDFs compare to scripting languages like Lua or more traditional compiled solutions like Java or C?
Since WebAssembly is designed to be used in very diverse environments, it cannot afford to make many assumptions:
- Memory is a sequence of bytes: there is no support for typed memory.
- As mentioned earlier, there is no garbage collector.
- WebAssembly code has no access to host process resources such as the filesystem, `stdout` or environment variables unless explicitly provided via functions that the WASM module can call at runtime.
- Catering to multi-tenant scenarios, hosts have find-grained control over how much computational power they grant a WASM function.
- The builtin types are limited to 32 and 64 bit floating point and integer numbers, bytes and booleans.

That last point presents a significant challenge when using WebAssembly for authoring UDFs. Many of the [types](https://seafowl.io/docs/reference/types) supported by Seafowl such as `TEXT` and `DECIMAL`
cannot be simply represented solely by scalar numeric values.

One option is to limit UDFs to consuming and returning types natively supported by WebAssembly (this is [ScyllaDB's approach](https://www.scylladb.com/2022/04/14/wasmtime/)).

For Seafowl's UDFs, we wanted users to be able to work with any datatype including strings, decimal numbers and dates.

In WebAssembly, memory is just a sequence of bytes and pointers are just 32 bit integers, similar to an array index.
Any object which can be serialized to a byte stream can be passed by the host to a WebAssembly function by first writing the serialized bytes to WASM memory,
then invoking the function with a pointer to the freshly written memory address as its argument.
Similarly, the WASM function can serialize it's output as a byte stream to which it returns a pointer.

Seafowl uses [MessagePack](https://msgpack.org/index.html) for serialization - think of it like a faster binary version of JSON.

## We're just getting started

Today, Seafowl supports creating UDFs in any programming language which compiles to WASM. Check out our [Rust example UDF repository](https://github.com/splitgraph/seafowl-udf-rust)! Any Seafowl type may be used for input values and return type.

One of the biggest advantages of using UDFs is aggregated records _within the database_. As of 2022 December, Seafowl does not yet support user-defined aggregation functions, but this support is planned. When it does arrive, you can be sure it will also use WebAssembly!

[^1]: There's an interesting discussion on this approach in this [Hacker News thread](https://news.ycombinator.com/item?id=33874842).


How can Seafowl users extend the database's builtin capabilities in a safe, portable and efficient way? The answer is WebAssembly, read on to learn how!

Splitgraph has been acquired by EDB! Read the blog post.

Using Dagster with Seafowl

SQLite file uploads

A Lakehouse by the sea: Migrating Seafowl storage layer to delta-rs

Open Data Monitor

Rust visitor pattern and efficient DataFusion query federation

Deciding if I'm urban with WebAssembly and Seafowl

Extending Seafowl with WebAssembly

Product

Support

Company

Splitgraph

Splitgraph has been acquired by EDB! Read the blog post.

Using Dagster with Seafowl

SQLite file uploads

A Lakehouse by the sea: Migrating Seafowl storage layer to delta-rs

Open Data Monitor

Rust visitor pattern and efficient DataFusion query federation

Deciding if I'm urban with WebAssembly and Seafowl

Extending Seafowl with WebAssembly

Product

Support

Company

Community

Splitgraph