Skip to content

Getting started with Ursa SQL

Ursa is a SQL engine for graph analytics. It reads Parquet, CSV, JSON and Arrow files where they are, on your disk or in object storage, and runs graph algorithms over them with SQL. There is no server and nothing to load first. An edge table is a graph.

Install

On Linux or macOS, run the install script:

curl -fsSL https://cldixon.github.io/ursa/install.sh | sh

It downloads the newest ursa for your machine from the releases page, checks it against its SHA-256 checksum, and puts it in ~/.local/bin. It needs no sudo. If ~/.local/bin is not on your PATH, it prints the line to add. To choose the version or the directory, set URSA_VERSION or URSA_INSTALL_DIR after the pipe:

curl -fsSL https://cldixon.github.io/ursa/install.sh | URSA_VERSION=0.11.0 URSA_INSTALL_DIR=/opt/bin sh

On Windows, in PowerShell. This installs to %LOCALAPPDATA%\ursa\bin and adds it to your user PATH:

irm https://cldixon.github.io/ursa/install.ps1 | iex

The prebuilt binaries cover Linux (x86_64 and aarch64, glibc 2.35 or later), macOS (Intel and Apple silicon) and Windows (x64). On anything else, install with Cargo. You need Rust 1.94 or later:

cargo install ursa-cli

Or download a prebuilt binary yourself from the releases page. Each release has one archive per platform:

Platform Archive
Linux x86_64 ursa-cli-v0.11.0-x86_64-unknown-linux-gnu.tar.gz
Linux aarch64 ursa-cli-v0.11.0-aarch64-unknown-linux-gnu.tar.gz
macOS (Intel) ursa-cli-v0.11.0-x86_64-apple-darwin.tar.gz
macOS (Apple silicon) ursa-cli-v0.11.0-aarch64-apple-darwin.tar.gz
Windows x64 ursa-cli-v0.11.0-x86_64-pc-windows-msvc.zip

Each archive has a .sha256 file next to it. For example, on an Apple silicon Mac:

curl -LO https://github.com/cldixon/ursa/releases/download/ursa-cli-v0.11.0/ursa-cli-v0.11.0-aarch64-apple-darwin.tar.gz
tar xzf ursa-cli-v0.11.0-aarch64-apple-darwin.tar.gz
sudo mv ursa-cli-v0.11.0-aarch64-apple-darwin/ursa /usr/local/bin/

The binary is called ursa. Check it runs:

ursa --version
ursa 0.11.0

Nightly builds

The installers have two channels. Stable, the default, is the newest release, the same version as on crates.io. Nightly is the newest build of main, rebuilt on every merge. It has features before they are released, including ones these docs mark "New in" a version that isn't out yet. Those features may still change or break.

curl -fsSL https://cldixon.github.io/ursa/install.sh | URSA_CHANNEL=nightly sh

or curl -fsSL https://cldixon.github.io/ursa/install.sh | sh -s -- --nightly. On Windows, in PowerShell:

$env:URSA_CHANNEL = 'nightly'; irm https://cldixon.github.io/ursa/install.ps1 | iex

The download is checked against its SHA-256 checksum, as a stable one is. A nightly names its version, build date and commit:

ursa 0.12.0-nightly.20261119 (a1b2c3d4e)

Only the newest nightly is kept, on the nightly release. To go back to stable, run the installer again without the nightly option (in PowerShell, Remove-Item Env:URSA_CHANNEL first). When you report a bug, include the output of ursa --version: it says which channel and commit you have. To build main yourself instead: cargo install --git https://github.com/cldixon/ursa ursa-cli.

Query a file

This guide uses Zachary's karate club: 34 members of a club, and 78 friendships between them. Download the two files:

curl -LO https://raw.githubusercontent.com/cldixon/ursa/main/python/ursa/datasets/data/karate_edges.csv
curl -LO https://raw.githubusercontent.com/cldixon/ursa/main/python/ursa/datasets/data/karate_nodes.csv

Any file is a table. Name it in FROM:

ursa -c "SELECT * FROM 'karate_edges.csv' LIMIT 3"
┌───────┬───────┐
│  src  │  dst  │
│ Int64 │ Int64 │
├───────┼───────┤
│     0 │     1 │
│     0 │     2 │
│     0 │     3 │
└───────┴───────┘

The format comes from the extension: .parquet, .csv, .json, .jsonl and .ndjson (JSON, one object per line; .jsonl and .ndjson are new in 0.7), and .arrow. CSV and JSON may be compressed (.csv.gz, .jsonl.zst). A directory or a glob reads every file in it: 'edges/*.parquet'.

New in 0.7: read_parquet, read_csv and read_json read a file in the format they name, whatever its extension, with options, as in DuckDB:

ursa -c "SELECT * FROM read_csv('karate_edges.csv', header => true, delim => ',') LIMIT 3"

Reading and writing files lists the options.

Open a database

Start the shell with a database file:

ursa club.ursa

The file is created if it does not exist. It keeps the tables, views and graphs you define, and the graph indexes Ursa builds, so the next session (or the next ursa -c against the same file) starts where you left off. Without a file name, ursa runs in memory and forgets everything when it exits. The database file has the details.

The rest of this guide runs in that shell.

Define a graph

First, name the files as tables:

CREATE VIEW members AS SELECT * FROM 'karate_nodes.csv';
CREATE VIEW ties    AS SELECT * FROM 'karate_edges.csv';

A view reads the file each time it is queried, so it always sees the file's current contents. Nothing is copied into the database.

Then define the graph. Ursa uses the CREATE PROPERTY GRAPH statement from the SQL standard (SQL/PGQ):

CREATE PROPERTY GRAPH karate
  VERTEX TABLES (members KEY (id))
  EDGE TABLES (
    ties SOURCE KEY (src) REFERENCES members (id)
         DESTINATION KEY (dst) REFERENCES members (id)
  );

Every row of ties is an edge from src to dst. Every row of members is a node, keyed by id. The node table defines the graph's nodes, as in SQL/PGQ and DuckPGQ (new in 0.7; in 0.6.0 the algorithms see only the edges' endpoints): a member with no ties is a node all the same, of degree 0, and every algorithm returns a row for it. A tie whose src or dst is not an id of members is left out of the graph, and describe counts such ties in its n_dropped_edges column.

The node table is optional. Without it, the nodes are the endpoints of the edges:

CREATE PROPERTY GRAPH karate_edges_only
  EDGE TABLES (ties SOURCE KEY (src) DESTINATION KEY (dst));

A graph is a schema with two tables. karate.edges holds the edges, and karate.nodes holds one row per node with its attributes:

SELECT * FROM karate.nodes LIMIT 3;
┌───────┬────────┐
│  id   │  club  │
│ Int64 │  Utf8  │
├───────┼────────┤
│     0 │ Mr. Hi │
│     1 │ Mr. Hi │
│     2 │ Mr. Hi │
└───────┴────────┘

Run an algorithm

An algorithm is a table function. Its first argument is the graph. It returns one row per node: the node's id, and a column named after the algorithm.

SELECT id, round(pagerank, 4) AS pagerank
FROM pagerank(karate, direction => 'both')
ORDER BY pagerank DESC
LIMIT 5;
┌───────┬──────────┐
│  id   │ pagerank │
│ Int64 │ Float64  │
├───────┼──────────┤
│    33 │   0.1009 │
│     0 │    0.097 │
│    32 │   0.0717 │
│     2 │   0.0571 │
│     1 │   0.0529 │
└───────┴──────────┘

Parameters are named, with =>:

SELECT * FROM louvain(karate, seed => 1);
SELECT * FROM pagerank(karate, damping => 0.9, direction => 'both');

Combine algorithms and attributes

algorithms runs several algorithms over one graph and returns one row per node with a column for each. Name a column with name =>; otherwise it is named after the algorithm. Join it to the node table on id. This query finds the five best-connected members, with their club, how many triangles they are part of, and their community:

WITH top AS (
    SELECT * FROM algorithms(karate,
                             degree(direction => 'both'),
                             triangles => triangle_count(),
                             community => louvain(seed => 1))
    ORDER BY degree DESC
    LIMIT 5
)
SELECT id, club, degree, triangles, community
FROM top JOIN karate.nodes USING (id)
ORDER BY degree DESC;
┌───────┬─────────┬────────┬───────────┬───────────┐
│  id   │  club   │ degree │ triangles │ community │
│ Int64 │  Utf8   │ UInt32 │  UInt32   │  UInt32   │
├───────┼─────────┼────────┼───────────┼───────────┤
│    33 │ Officer │     17 │        15 │         2 │
│     0 │ Mr. Hi  │     16 │        18 │         0 │
│    32 │ Officer │     12 │        13 │         2 │
│     2 │ Mr. Hi  │     10 │        11 │         0 │
│     1 │ Mr. Hi  │      9 │        12 │         0 │
└───────┴─────────┴────────┴───────────┴───────────┘

Each algorithm also works on its own, as in the previous section, and those results join on id too.

Take the top k, then join the attributes

The query takes the five rows it wants from algorithms(...) first, and joins the club to those five. Written the other way round, FROM karate.nodes JOIN algorithms(...) USING (id) ORDER BY degree DESC LIMIT 5 gives the same rows, but the ORDER BY ... LIMIT is not moved below the join, so every node's attributes are carried through the sort. On a graph of millions of nodes that is about twice the memory, and slower.

This is safe when the join keeps every row, as a join to the graph's own node table does: one row for each node. A join that drops rows, such as a WHERE on an attribute or a GRAPH_TABLE, must come before the LIMIT.

All three algorithms run over one index of the graph. Ursa builds it the first time a query needs it and stores it in club.ursa. The next query, in this session or a later one, uses the stored index. If karate_edges.csv changes, Ursa notices and builds a new one.

Aggregate over neighbours

neighbors_agg aggregates a value of each node over its neighbours. It returns one row per node. Take the karate members again. Some of them have friends in both clubs. This query counts them, by club:

SELECT club, count(*) AS members
FROM karate.nodes
JOIN neighbors_agg(karate, attribute => club, agg => 'n_unique', direction => 'both') USING (id)
WHERE neighbors_agg = 2
GROUP BY club
ORDER BY club;
┌─────────┬─────────┐
│  club   │ members │
│  Utf8   │  Int64  │
├─────────┼─────────┤
│ Mr. Hi  │       6 │
│ Officer │       7 │
└─────────┴─────────┘

There are two ways to give the value of each node:

  • attribute => takes an expression over the graph's node table.
  • values => takes any query of two columns, node ids then values. The values can even come from another algorithm, like each member's friends' mean degree:
SELECT id, round(neighbors_agg, 2) AS friends_degree
FROM neighbors_agg(karate,
                   values => (SELECT id, degree FROM degree(karate, direction => 'both')),
                   agg => 'mean', direction => 'both')
ORDER BY friends_degree DESC, id
LIMIT 3;
┌───────┬────────────────┐
│  id   │ friends_degree │
│ Int64 │    Float64     │
├───────┼────────────────┤
│    11 │           16.0 │
│    14 │           14.5 │
│    15 │           14.5 │
└───────┴────────────────┘

The aggregations are mean (the default), sum, min, max, count and n_unique. count and n_unique return integers. Strings, dates and times take count, n_unique, min and max, and min and max return a value of their type. (In 0.6.0, count and n_unique return floats, and strings take only those two.) A node whose neighbours have no values gets NULL for mean, min and max, and 0 for the others.

Direction

Every edge is stored from src to dst. Algorithms that walk edges take a direction:

Value Follows
'out' (the default) edges from a node
'in' edges into a node
'both' edges in either direction

The karate edges are stored once per friendship, so direction => 'both' gives the undirected answer. Some algorithms only make sense one way and take no direction: triangle_count, clustering_coefficient, louvain and label_propagation read the graph as undirected.

Weights

A weight is a column of the edge table, or an expression over its columns. Les Misérables has a weight column counting how often two characters appear together:

CREATE VIEW lesmis_edges AS SELECT * FROM 'lesmis_edges.csv';
CREATE PROPERTY GRAPH lesmis
  EDGE TABLES (lesmis_edges SOURCE KEY (src) DESTINATION KEY (dst));

SELECT * FROM pagerank(lesmis, weight => weight, direction => 'both')
ORDER BY pagerank DESC
LIMIT 5;
┌──────────┬──────────────────────┐
│    id    │       pagerank       │
│   Utf8   │       Float64        │
├──────────┼──────────────────────┤
│ Valjean  │  0.09956068175644767 │
│ Marius   │ 0.051667656904091475 │
│ Myriel   │  0.03923416880292588 │
│ Cosette  │  0.03691018363299517 │
│ Enjolras │ 0.036614491459848165 │
└──────────┴──────────────────────┘

The weight can be any expression over the edge columns:

SELECT * FROM louvain(lesmis, weight => log10(1 + weight), seed => 7)
ORDER BY id
LIMIT 5;
┌────────────┬─────────┐
│     id     │ louvain │
│    Utf8    │ UInt32  │
├────────────┼─────────┤
│ Anzelma    │       3 │
│ Babet      │       3 │
│ Bahorel    │       5 │
│ Bamatabois │       1 │
│ BaronessT  │       4 │
└────────────┴─────────┘

Weights must be non-negative and not null. The graph index does not depend on the weight, so trying several weightings reuses one index.

Subgraphs

A subgraph is a graph over a view that filters the edges:

CREATE VIEW strong_ties AS SELECT * FROM lesmis_edges WHERE weight >= 3;

CREATE PROPERTY GRAPH lesmis_strong
  EDGE TABLES (strong_ties SOURCE KEY (src) DESTINATION KEY (dst));

SELECT count(DISTINCT connected_components) AS groups
FROM connected_components(lesmis_strong);
┌────────┐
│ groups │
│ Int64  │
├────────┤
│      3 │
└────────┘

Without a node table, the nodes of a subgraph are the endpoints of the edges it keeps. A character whose every tie is weaker than 3 is not in lesmis_strong.

With a node table (new in 0.7), the node table defines the nodes, so a subgraph keeps every one of them, as a filtered edge frame in Python keeps its parent's nodes. This subgraph of the karate club keeps the ties from members 5 and up. Ten members, 0 to 4 among them, have no ties left, and are still nodes:

CREATE VIEW late_ties AS SELECT * FROM ties WHERE src >= 5;

CREATE PROPERTY GRAPH karate_late
  VERTEX TABLES (members KEY (id))
  EDGE TABLES (
    late_ties SOURCE KEY (src) REFERENCES members (id)
              DESTINATION KEY (dst) REFERENCES members (id)
  );

SELECT n_nodes, n_edges FROM describe(karate_late);
┌─────────┬─────────┐
│ n_nodes │ n_edges │
│  Int64  │  Int64  │
├─────────┼─────────┤
│      34 │      41 │
└─────────┴─────────┘

nodes => 'touched' describes only the nodes at an end of a kept edge:

SELECT n_nodes, n_edges FROM describe(karate_late, nodes => 'touched');
┌─────────┬─────────┐
│ n_nodes │ n_edges │
│  Int64  │  Int64  │
├─────────┼─────────┤
│      24 │      41 │
└─────────┴─────────┘

Traversals

Traversals are table functions too, and they return rows of their own. hop returns the nodes within n steps of a set of seed nodes, one (seed, id, distance) row per seed and node reached: id is the node and distance the fewest steps from the seed to it. The seeds can be a list or a query. Member 0 has 16 friends, so this returns 16 rows:

SELECT * FROM hop(karate, seeds => [0], n => 1, direction => 'both');

SELECT * FROM hop(karate,
                  seeds => (SELECT id FROM karate.nodes WHERE club = 'Officer'),
                  n => 2);

The second query returns 39 rows: each node within two steps of an Officer member, following edges in their stored direction, once for each member that reaches it. The seeds query runs as part of the same plan, so a filter in it is pushed into the scan of karate_nodes.csv. A seed that is not in the graph reaches nothing.

random_walk returns walks as rows: one per step, with the walk's number and position. Give it a seed for walks you can reproduce:

SELECT * FROM random_walk(karate, start => [0, 1], steps => 4, walks_per_node => 2, seed => 7);

shortest_path returns the path between two nodes, one edge per row, with its step number and the cost so far:

SELECT * FROM shortest_path(karate, source => 0, target => 33, direction => 'both');
┌───────┬───────┬───────┬─────────┐
│  src  │  dst  │  hop  │  cost   │
│ Int64 │ Int64 │ Int64 │ Float64 │
├───────┼───────┼───────┼─────────┤
│     0 │     8 │     0 │     1.0 │
│     8 │    33 │     1 │     2.0 │
└───────┴───────┴───────┴─────────┘

Files in object storage

Anything that works with a local path works with a URL. Credentials come from the standard environment variables (AWS_*, GOOGLE_*, AZURE_*) or the usual config files (see Credentials below):

CREATE VIEW follows AS
  SELECT * FROM 's3://my-bucket/social/follows/*.parquet';

CREATE PROPERTY GRAPH social
  EDGE TABLES (follows SOURCE KEY (follower) DESTINATION KEY (followee));

SELECT * FROM pagerank(social) ORDER BY pagerank DESC LIMIT 10;

gs://, az:// and https:// work the same way. Ursa reads only the columns a query needs, and stores the graph index in the database file, so the second query does not read the edges again.

Credentials

Ursa finds credentials where each cloud's own tools do. It looks in the places below in order, and uses the first that has credentials. Nothing is stored in the database file.

S3 (s3://):

  1. Environment variables: AWS_ACCESS_KEY_ID and AWS_SECRET_ACCESS_KEY, plus AWS_SESSION_TOKEN for temporary credentials.
  2. An AWS profile in ~/.aws/credentials and ~/.aws/config. Ursa reads the profile that AWS_PROFILE names, or default. The profile can hold:

    • access keys;
    • an SSO login (run aws sso login first);
    • a role to assume (role_arn with source_profile);
    • a credential_process.

    AWS_SHARED_CREDENTIALS_FILE and AWS_CONFIG_FILE point to other files. If AWS_PROFILE names a profile that has no credentials, the query fails. Ursa doesn't go on to the sources below. 3. Web identity: AWS_WEB_IDENTITY_TOKEN_FILE and AWS_ROLE_ARN, as on EKS. 4. Container credentials: AWS_CONTAINER_CREDENTIALS_RELATIVE_URI or AWS_CONTAINER_CREDENTIALS_FULL_URI, as on ECS. 5. The instance's role, from the EC2 instance metadata service.

AWS_PROFILE=analytics ursa -c "SELECT count(*) FROM 's3://my-bucket/follows/*.parquet'"

The region comes from AWS_REGION or AWS_DEFAULT_REGION. If neither is set, Ursa asks S3 where the bucket is. If S3 doesn't answer, Ursa uses the profile's region.

Some settings for other cases:

  • An S3-compatible store (MinIO, R2): set AWS_ENDPOINT. For a plain http:// endpoint, also set AWS_ALLOW_HTTP=true.
  • A public bucket: set AWS_SKIP_SIGNATURE=true and no credentials are needed.

Ursa reads profiles through aws-config, the AWS SDK's own configuration loader, in the aws-profile feature of ursa-cli. The feature is on by default. A build without it (--no-default-features) skips step 2.

Google Cloud Storage (gs://):

  1. A service account key: a file named by GOOGLE_SERVICE_ACCOUNT (or GOOGLE_SERVICE_ACCOUNT_PATH), or the key's JSON in GOOGLE_SERVICE_ACCOUNT_KEY.
  2. Application default credentials: the file GOOGLE_APPLICATION_CREDENTIALS names, or the one gcloud auth application-default login writes to ~/.config/gcloud/.
  3. The instance's service account, from the GCE metadata server.

Azure Blob Storage (az://, abfs://, abfss://, adl://). Set the account with AZURE_STORAGE_ACCOUNT_NAME, then the credentials:

  1. An account key: AZURE_STORAGE_ACCOUNT_KEY.
  2. Workload identity: AZURE_CLIENT_ID, AZURE_TENANT_ID and AZURE_FEDERATED_TOKEN_FILE, as on AKS.
  3. A service principal: AZURE_CLIENT_ID, AZURE_CLIENT_SECRET and AZURE_TENANT_ID.
  4. A SAS token: AZURE_STORAGE_SAS_KEY.
  5. The Azure CLI's login, when AZURE_USE_AZURE_CLI=true.
  6. A managed identity, from the instance metadata service.

HTTP (https://, http://) URLs are read without credentials.

Output

The shell prints tables. From the command line, choose a format with --format:

ursa club.ursa --format json -c "SELECT * FROM pagerank(karate) ORDER BY pagerank DESC LIMIT 3"
ursa club.ursa --format csv -c "SELECT * FROM louvain(karate, seed => 1)" > communities.csv

The formats are table (the default), csv, tsv, json, ndjson and arrow. To write a file from SQL, use COPY. It prints how many rows it wrote:

COPY (SELECT * FROM pagerank(karate)) TO 'pagerank.parquet';
┌────────┐
│ count  │
│ UInt64 │
├────────┤
│     34 │
└────────┘

Find out what is available

SHOW GRAPHS;
DESCRIBE GRAPH karate;
SELECT * FROM ursa_functions();

ursa_functions() lists every graph function with its parameters, their defaults, the NetworkX function it matches, and a one-line description.

Next steps

  • The function reference lists every function and exactly what it computes.
  • Statements covers defining, describing and dropping graphs, ATTACH and the settings.
  • The shell covers dot-commands, init files and history.
  • The Python library runs the same algorithms with a DataFrame API, and gives the same values.