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:
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:
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:
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:
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.
or curl -fsSL https://cldixon.github.io/ursa/install.sh | sh -s -- --nightly. On Windows, in PowerShell:
The download is checked against its SHA-256 checksum, as a stable one is. A nightly names its version, build date and commit:
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:
┌───────┬───────┐
│ 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:
Reading and writing files lists the options.
Open a database¶
Start the shell with a database file:
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:
A graph is a schema with two tables. karate.edges holds the edges, and karate.nodes holds one row per node with its attributes:
┌───────┬────────┐
│ 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:
┌────────────┬─────────┐
│ 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);
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:
┌─────────┬─────────┐
│ 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:
shortest_path returns the path between two nodes, one edge per row, with its step number and the cost so far:
┌───────┬───────┬───────┬─────────┐
│ 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://):
- Environment variables:
AWS_ACCESS_KEY_IDandAWS_SECRET_ACCESS_KEY, plusAWS_SESSION_TOKENfor temporary credentials. -
An AWS profile in
~/.aws/credentialsand~/.aws/config. Ursa reads the profile thatAWS_PROFILEnames, ordefault. The profile can hold:- access keys;
- an SSO login (run
aws sso loginfirst); - a role to assume (
role_arnwithsource_profile); - a
credential_process.
AWS_SHARED_CREDENTIALS_FILEandAWS_CONFIG_FILEpoint to other files. IfAWS_PROFILEnames 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_FILEandAWS_ROLE_ARN, as on EKS. 4. Container credentials:AWS_CONTAINER_CREDENTIALS_RELATIVE_URIorAWS_CONTAINER_CREDENTIALS_FULL_URI, as on ECS. 5. The instance's role, from the EC2 instance metadata service.
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 plainhttp://endpoint, also setAWS_ALLOW_HTTP=true. - A public bucket: set
AWS_SKIP_SIGNATURE=trueand 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://):
- A service account key: a file named by
GOOGLE_SERVICE_ACCOUNT(orGOOGLE_SERVICE_ACCOUNT_PATH), or the key's JSON inGOOGLE_SERVICE_ACCOUNT_KEY. - Application default credentials: the file
GOOGLE_APPLICATION_CREDENTIALSnames, or the onegcloud auth application-default loginwrites to~/.config/gcloud/. - 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:
- An account key:
AZURE_STORAGE_ACCOUNT_KEY. - Workload identity:
AZURE_CLIENT_ID,AZURE_TENANT_IDandAZURE_FEDERATED_TOKEN_FILE, as on AKS. - A service principal:
AZURE_CLIENT_ID,AZURE_CLIENT_SECRETandAZURE_TENANT_ID. - A SAS token:
AZURE_STORAGE_SAS_KEY. - The Azure CLI's login, when
AZURE_USE_AZURE_CLI=true. - 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:
Find out what is available¶
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,
ATTACHand 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.