I can't believe its not Java

The Apache web server powered most sites on the early internet. At the time it was really the only game in town outside of Microsoft's offerings. As years went on some of the folks started a foundation which began incubating projects that were mostly Java frameworks that did stuff with Hadoop or Hadoop adjacent technologies. I'm paraphrasing out of sheer ignorance because I kind of mentally tuned out and developed a subconscious bias towards ignoring anything with the Apache moniker attached to it. Not to disparage any of the people at the foundation or any of those projects, they've just always been things outside my wheelhouse or curiosity.

I've been missing out. For a couple years now things like Apache Arrow, Apache Parquet, and Apache Iceberg had been in my periphery, but nothing I'd ever dove into or had much of a reason to really research.

That's when I happened across Apache Datafusion.

It's an extendable query engine written in Rust. Basically all the tooling that makes traditional rdbms possible exposed as a framework.

Out of the box you get functionality to do things like query csv files using sql, you get dataframes, you even get profiling tools that explain the query and logical plans. It supports querying data on different storage platforms like s3 or on disk or in memory. There's an HTTP store that writes data to web dav.

Mentally, I align really well with the approach its developers have taken. While not an expert in the nitty gritty (yet) I get what they've done and are trying to do.

Philosophy

In years past it was kind of a running joke on nerd social media for developers to describe their job as "turning data from a database into json so it can be sent to a rest endpoint, so it can go into another database".

It sounds absurd, but there is a grain of truth in that.

If you want to use a database you typically have to adopt it's storage engine, its conventions, software, etc and that requires normalizing a lot of data just to be able to ingest and query it.

Datafusion is a bit different. It's a thin layer over all the pieces.

It reminds me of that old idea from lisp where "code is data". It's not quite the same thing but adjacent to the idea, I think. In lisp all code is made up of parentheses (S-expressions). All data and types in lisp are represented using the same convention. This arrangement makes the idea much more obvious after banging out a couple thousand lines of parentheses. It forces you to think about what is code vs what is data.

Objective-C is not that dissimilar. When you write code like

[NSColor grayColor]

That *is* code. It is dispatching a ~objc_msgsend~ call over the Objective-C Runtimeis code. It is dispatching a objc_msgsend call over the [[https://developer.apple.com/documentation/objectivec][Objective-C Runtime]] to a NSColor structure that holds the value for gray behind grayColor.

But is that really code? Or is it data?

You could argue that in the context of objective-c it is and should be code because the operating system has different contexts that would cause that method to return different values.

You're not getting one specific type of gray, you're asking for a specific color value based on if a user has their system set to light mode vs dark mode.

# I need to setup a venv for this document first.  
python3 -m venv .venv

# This installs datafusion, pyarrow, pandas, and matplotlib
./.venv/bin/pip install datafusion pyarrow pandas matplotlib

# Import the library
from datafusion import SessionContext

# Create a session context
ctx = SessionContext()

# Construct an in memory table named "students" from a python dictionary
df = ctx.from_pydict({"id": [1,2,3], "score": [88.5, 92.0, 79.5]}, name="students")
print(df)

# Query the student table to find the average score
results = ctx.sql("SELECT AVG(score) as avg_score FROM students")
print(results)

DataFrame()
+----+-------+
| id | score |
+----+-------+
| 1  | 88.5  |
| 2  | 92.0  |
| 3  | 79.5  |
+----+-------+
DataFrame()
+-------------------+
| avg_score         |
+-------------------+
| 86.66666666666667 |
+-------------------+

import matplotlib.pyplot as plt

pandas_df = df.to_pandas()
ax = pandas_df.plot(kind="bar", title="Student scores")
plt.tight_layout()
plt.savefig("chart.png")
plt.close()

[[file:chart.png]]

In-memory CSV to DataFusion

We can also define CSV data directly in an Org block and pass it into Python as a variable using ~:var~. DataFusion can register this in-memory CSV through PyArrow:var. DataFusion can register this in-memory CSV through [[https://arrow.apache.org/docs/python/][PyArrow]] without writing anything to disk.

name,department,salary,years_exp
Alice,Engineering,115000,6
Bob,Marketing,82000,4
Charlie,Engineering,125000,8
Diana,HR,78000,5
Evan,Marketing,95000,7
Frank,Engineering,105000,3

import io
import pyarrow.csv as pcsv

# 1. Parse CSV string bytes into an Arrow table
arrow_table = pcsv.read_csv(io.BytesIO(csv_text.encode("utf-8")))

# 2. Register table in DataFusion session in-memory
df_employees = ctx.from_arrow(arrow_table, name="employees")
print("Registered Table Schema and Preview:")
print(df_employees)

# 3. Query the in-memory table using SQL
query = ctx.sql("""
    SELECT 
        department,
        COUNT(*) AS headcount,
        AVG(salary) AS avg_salary,
        AVG(years_exp) AS avg_experience
    FROM employees
    GROUP BY department
    ORDER BY avg_salary DESC
""")

print("Query Results:")
print(query)

Registered Table Schema and Preview:
DataFrame()
+---------+-------------+--------+-----------+
| name    | department  | salary | years_exp |
+---------+-------------+--------+-----------+
| Alice   | Engineering | 115000 | 6         |
| Bob     | Marketing   | 82000  | 4         |
| Charlie | Engineering | 125000 | 8         |
| Diana   | HR          | 78000  | 5         |
| Evan    | Marketing   | 95000  | 7         |
| Frank   | Engineering | 105000 | 3         |
+---------+-------------+--------+-----------+
Query Results:
DataFrame()
+-------------+-----------+--------------------+----------------+
| department  | headcount | avg_salary         | avg_experience |
+-------------+-----------+--------------------+----------------+
| Engineering | 3         | 115000.0           | 5.666666666667 |
| Marketing   | 2         | 88500.0            | 5.5            |
| HR          | 1         | 78000.0            | 5.0            |
+-------------+-----------+--------------------+----------------+

Ok slightly more impressive. We defined a csv inside this document, read it into memory, and then queried it.

And another chart. Why not?

pandas_df = query.to_pandas()
ax = pandas_df.plot(kind="bar", title="Average salary")
plt.tight_layout()
plt.savefig("chart2.png")
plt.close()

[[file:chart2.png]]

We can also get an explanation of the query and the plan that went into processing it

print(query.explain())

DataFrame()
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| plan_type     | plan                                                                                                                                                                       |
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| logical_plan  | Sort: avg_salary DESC NULLS FIRST                                                                                                                                          |
|               |   Projection: employees.department, count(Int64(1)) AS count(*) AS headcount, avg(employees.salary) AS avg_salary, avg(employees.years_exp) AS avg_experience              |
|               |     Aggregate: groupBy=[[employees.department]], aggr=[[count(Int64(1)), avg(CAST(employees.salary AS Float64)), avg(CAST(employees.years_exp AS Float64))]]               |
|               |       TableScan: employees projection=[department, salary, years_exp]                                                                                                      |
| physical_plan | SortPreservingMergeExec: [avg_salary@2 DESC]                                                                                                                               |
|               |   SortExec: expr=[avg_salary@2 DESC], preserve_partitioning=[true]                                                                                                         |
|               |     ProjectionExec: expr=[department@0 as department, count(Int64(1))@1 as headcount, avg(employees.salary)@2 as avg_salary, avg(employees.years_exp)@3 as avg_experience] |
|               |       AggregateExec: mode=FinalPartitioned, gby=[department@0 as department], aggr=[count(Int64(1)), avg(employees.salary), avg(employees.years_exp)]                      |
|               |         RepartitionExec: partitioning=Hash([department@0], 64), input_partitions=1                                                                                         |
|               |           AggregateExec: mode=Partial, gby=[department@0 as department], aggr=[count(Int64(1)), avg(employees.salary), avg(employees.years_exp)]                           |
|               |             DataSourceExec: partitions=1, partition_sizes=[1]                                                                                                              |
|               |                                                                                                                                                                            |
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
None

The above is a break down of two things:

1. The LogicalPlan - this describes WHAT the query will do

2. The PhysicalPlan - this describes HOW datafusion will actually execute the query on threads, memory, and cpu cores.

This is incredibly useful as your start querying data in different formats, stored in different locations, with variable resource constraints. In our case it's a breakdown of the execution that's happening to data in memory. From a perf tooling standpoint it should start becoming obvious why this is useful.

Brief recap of things we can do:

• Define "tables" in memory using dictionaries and perform sql queries and calculations on the underlying data

• Read a CSV into memory and perform sql queries and calcs on the underlying data

• Feed the output directly into matplotlib to visualize the data

Things not showcased:

• Reading data from disk or an object store like s3

• ALL of the out of the box formats datafusion provides: csv, json, parquet, avro

• Custom formats

I'm hoping why this is powerful is becoming more clear.

A less conventional table format: Querying an SVG

All of the default formats are standard run of the mill stuff that your average sysadmin or devops type developer is probably more than familiar with.

But what if you have data that isn't represented as a json, csv, parquet or avro file?

Well Datafusion provides crates for accessing data in things like postgres, sqlite, mysql etc Those extensions can be found here: datafusion-table-providers

That's useful, but doesn't really showcase what I'm trying to hit on. What if you wanted to query something like an SVG file?

An SVG file is used to represent graphics and two dimensional vector geometry. It's just XML.

Here is an SVG diagram defined right inside this document:

<svg width="400" height="240" xmlns="http://www.w3.org/2000/svg">
  <rect id="bg" x="0" y="0" width="400" height="240" rx="15" fill="#1e1e2e"/>
  <rect id="card1" x="20" y="20" width="160" height="90" rx="8" fill="#313244" stroke="#89b4fa"/>
  <rect id="card2" x="200" y="20" width="160" height="90" rx="8" fill="#313244" stroke="#a6e3a1"/>
  <circle id="badge" cx="300" cy="180" r="30" fill="#f38ba8"/>
  <text id="t1" x="100" y="70" fill="#cdd6f4" font-size="16">Architecture</text>
  <text id="t2" x="280" y="70" fill="#cdd6f4" font-size="16">Performance</text>
  <text id="t3" x="200" y="210" fill="#fab387" font-size="14">DataFusion Query Engine</text>
</svg>

import xml.etree.ElementTree as ET
import pyarrow as pa

def parse_svg_to_table(svg_str: str) -> pa.Table:
    root = ET.fromstring(svg_str)
    records = []
    for elem in root.iter():
        tag = elem.tag.split("}")[-1] # strip xml namespace
        if tag == "svg":
            continue
        elem_id = elem.attrib.get("id")
        fill = elem.attrib.get("fill")
        stroke = elem.attrib.get("stroke")
        text = elem.text.strip() if elem.text and elem.text.strip() else None
        
        # Calculate bounding area for shapes
        w = float(elem.attrib.get("width", 0)) if "width" in elem.attrib else None
        h = float(elem.attrib.get("height", 0)) if "height" in elem.attrib else None
        r = float(elem.attrib.get("r", 0)) if "r" in elem.attrib else None
        
        area = None
        if w is not None and h is not None:
            area = w * h
        elif r is not None:
            area = 3.14159 * r * r
            
        records.append({
            "tag": tag,
            "id": elem_id,
            "fill": fill,
            "stroke": stroke,
            "text": text,
            "area": area
        })
    return pa.Table.from_pylist(records)

# Register the in-memory SVG elements as a table in DataFusion
svg_arrow = parse_svg_to_table(svg_xml)
ctx.from_arrow(svg_arrow, name="svg_elements")
print("Registered SVG Elements Table:")
print(ctx.sql("SELECT tag, id, fill, stroke, area, text FROM svg_elements"))

Registered SVG Elements Table:
DataFrame()
+--------+-------+---------+---------+----------+-------------------------+
| tag    | id    | fill    | stroke  | area     | text                    |
+--------+-------+---------+---------+----------+-------------------------+
| rect   | bg    | #1e1e2e |         | 96000.0  |                         |
| rect   | card1 | #313244 | #89b4fa | 14400.0  |                         |
| rect   | card2 | #313244 | #a6e3a1 | 14400.0  |                         |
| circle | badge | #f38ba8 |         | 2827.431 |                         |
| text   | t1    | #cdd6f4 |         |          | Architecture            |
| text   | t2    | #cdd6f4 |         |          | Performance             |
| text   | t3    | #fab387 |         |          | DataFusion Query Engine |
+--------+-------+---------+---------+----------+-------------------------+

Now that our SVG document is a registered table in DataFusion, we can query it like any relational dataset!

print(ctx.sql("""
    SELECT DISTINCT fill, count(*) as shape_count 
    FROM svg_elements 
    WHERE fill IS NOT NULL 
    GROUP BY fill
    ORDER BY shape_count DESC
"""))

DataFrame()
+---------+-------------+
| fill    | shape_count |
+---------+-------------+
| #313244 | 2           |
| #cdd6f4 | 2           |
| #1e1e2e | 1           |
| #f38ba8 | 1           |
| #fab387 | 1           |
+---------+-------------+

print(ctx.sql("""
    SELECT id, text, fill 
    FROM svg_elements 
    WHERE text IS NOT NULL
"""))

DataFrame()
+----+-------------------------+---------+
| id | text                    | fill    |
+----+-------------------------+---------+
| t1 | Architecture            | #cdd6f4 |
| t2 | Performance             | #cdd6f4 |
| t3 | DataFusion Query Engine | #fab387 |
+----+-------------------------+---------+

print(ctx.sql("""
    SELECT 
        tag,
        COUNT(*) AS shape_count,
        SUM(area) AS total_area
    FROM svg_elements
    WHERE area IS NOT NULL
    GROUP BY tag
    ORDER BY total_area DESC
"""))

DataFrame()
+--------+-------------+------------+
| tag    | shape_count | total_area |
+--------+-------------+------------+
| rect   | 3           | 124800.0   |
| circle | 1           | 2827.431   |
+--------+-------------+------------+