Personalized PageRank
UDTF: cugraph_personalized_pagerank
Official cuGraph reference: C API
Rank vertices with PageRank while biasing random-walk restarts toward explicitly weighted personalization vertices.
Quickstart
The call below supplies edges from registered relation target_edges with canonical src and dst columns and may include weight, plus the registered relation ppr_seeds (the personalization relation with columns vertex, value).
SELECT *
FROM cugraph_personalized_pagerank(
edges => (SELECT src, dst FROM target_edges),
personalization => (SELECT vertex, value FROM ppr_seeds)
);
Inputs
Every relation is a named parenthesized SELECT subquery. The required edges role uses canonical src and dst columns. Every role, its canonical columns, and their accepted Arrow types are listed under Relation arguments. Metadata validation resolves registered tables named in its JSON request.
The vertex-ID types this UDTF accepts, the numeric types a per-call codebook maps to native ids, and its string-key limits are listed in Vertex ID support; the concrete call-specific schema comes from gpu_validate_call.
Arguments and options
Relation arguments
Vertex columns of personalization must use the same vertex domain as edges: the integer type of src and dst, or any listed string type when the endpoints are strings. Every edge_id column must have the integer type of src and dst; string-keyed graphs accept no edge IDs.
Named value arguments
Graph construction options
Graph construction follows the shared defaults (directed=true, renumbering, python_cugraph policy) documented in Graph Construction Options.
Output
These are generic descriptor schemas. Run gpu_validate_call to get the concrete, table-specific output schema.
Examples
- MerRec
- Citation network
- EMBER2024 import review
Which brands grew closer to Barbie's audience?
The query compares each brand's affinity to Barbie's audience from May through June against July through August 2023 and ranks the relative change. A later check of product names tests whether the change coincides with Barbie-related listings.
Each period has a graph of distinct user-brand likes. The after_release
flag is true for July to August likes. The split at 1 July falls 20 days
before the Barbie film opened on 21 July. User vertices are -(user_id + 1)
and brand vertices are brand_id values. Both columns are non-negative in
this data, so users occupy negative IDs and la.vertex > 0 keeps only brands
(brand 0 is excluded).
Personalized PageRank starts from Barbie; ordinary PageRank supplies each
brand's global graph score. Their ratio, lift, describes relative graph
affinity: 1 means the two scores are equal. All four calls use the default
options (alpha 0.85, epsilon 1e-5, max_iterations 100), and each
converged before the iteration limit. Lifts from the two periods were
therefore computed to one tolerance. The query ranks the change in that ratio
and requires at least 1,000 fans (distinct users) per brand in both periods.
WITH likes AS (
SELECT DISTINCT -(e.user_id + 1) AS src, CAST(i.brand_id AS BIGINT) AS dst,
e.event_time >= TIMESTAMP '2023-07-01 00:00:00' AS after_release
FROM merrec_events e JOIN merrec_items i ON i.item_id = e.item_id
WHERE e.event_type = 'item_like' AND i.brand_id <> 0
AND e.event_time >= TIMESTAMP '2023-05-01 00:00:00'
AND e.event_time < TIMESTAMP '2023-09-01 00:00:00'
), lift_before AS (
SELECT p.vertex, p.value / r.value AS lift
FROM cugraph_personalized_pagerank(
edges => (SELECT src, dst FROM likes WHERE NOT after_release),
personalization => (SELECT 2595 AS vertex, 1.0 AS value),
directed => false) p
JOIN cugraph_pagerank(
edges => (SELECT src, dst FROM likes WHERE NOT after_release), directed => false) r
ON r.vertex = p.vertex
), lift_after AS (
SELECT p.vertex, p.value / r.value AS lift
FROM cugraph_personalized_pagerank(
edges => (SELECT src, dst FROM likes WHERE after_release),
personalization => (SELECT 2595 AS vertex, 1.0 AS value),
directed => false) p
JOIN cugraph_pagerank(
edges => (SELECT src, dst FROM likes WHERE after_release), directed => false) r
ON r.vertex = p.vertex
), fans AS (
SELECT dst AS brand_id,
SUM(CASE WHEN after_release THEN 0 ELSE 1 END) AS fans_before,
SUM(CASE WHEN after_release THEN 1 ELSE 0 END) AS fans_after
FROM likes GROUP BY dst
), brands AS (
SELECT CAST(brand_id AS BIGINT) AS brand_id, MIN(brand_name) AS brand_name
FROM merrec_items WHERE brand_id <> 0 GROUP BY brand_id
)
SELECT b.brand_name, f.fans_before, f.fans_after,
lb.lift AS lift_before, la.lift AS lift_after, la.lift / lb.lift AS lift_change
FROM lift_after la
JOIN lift_before lb ON lb.vertex = la.vertex
JOIN fans f ON f.brand_id = la.vertex
JOIN brands b ON b.brand_id = la.vertex
WHERE la.vertex > 0 AND la.vertex <> 2595
AND f.fans_before >= 1000 AND f.fans_after >= 1000
ORDER BY lift_change DESC, b.brand_name
LIMIT 25;
The first four rows are the largest increases. Taylor Swift is included as a comparison for the product-name check below.
The time filters, four graph calls and brand joins ran natively in one plan. Over Flight SQL, the first run on a fresh server took 3.72 s and runs 2 to 4 a median of 3.05 s. On the same host, with the files in the page cache, an equivalent CPU pipeline combining 32-thread DuckDB with igraph took a median of 23.3 s (2.3 s SQL extraction, 13.0 s renumbering and graph construction, 7.7 s for four PageRank calls, 0.3 s final join) with identical top-25 rankings. Device execution avoids transferring and renumbering the 23 million edge pairs across host memory.
Follow-up: check product names. The graph query did not search names.
This separate SQL counts like events on listings whose names contain
"Barbie", using the same periods. Each of the five brand names checked here
maps to one brand_id, matching the brands in the graph result.
WITH selected_items AS (
SELECT item_id, brand_name,
LOWER(COALESCE(name, '')) LIKE '%barbie%' AS is_barbie
FROM merrec_items
WHERE brand_name IN ('NYX', 'Amc', 'Primark', 'ALDO', 'Taylor Swift', 'Stanley', 'OPI', 'ZARA')
), likes AS (
SELECT i.brand_name, i.is_barbie,
e.event_time < TIMESTAMP '2023-07-01 00:00:00' AS may_june
FROM merrec_events e JOIN selected_items i ON i.item_id = e.item_id
WHERE e.event_type = 'item_like'
AND e.event_time >= TIMESTAMP '2023-05-01 00:00:00'
AND e.event_time < TIMESTAMP '2023-09-01 00:00:00'
)
SELECT brand_name,
SUM(CASE WHEN may_june THEN 1 ELSE 0 END) AS total_likes_may_june,
SUM(CASE WHEN NOT may_june THEN 1 ELSE 0 END) AS total_likes_july_august,
SUM(CASE WHEN may_june AND is_barbie THEN 1 ELSE 0 END) AS barbie_likes_may_june,
SUM(CASE WHEN NOT may_june AND is_barbie THEN 1 ELSE 0 END) AS barbie_likes_july_august
FROM likes
GROUP BY brand_name
ORDER BY brand_name;
NYX's records include "LIMITED EDITION NYX Barbie Flip Phone Mirror". Taylor Swift's graph score also increased, but only 135 of 164,499 later likes were on matching listings. Its change remains unexplained by this check. Name matches can include seller descriptions and color names.
Follow-up: change the seed. A rise in graph affinity could reflect a
broad summer trend rather than Barbie. Rerunning the query with
another seed brand tests that. Hot Wheels is a Mattel brand whose collector
audience overlaps Barbie's; Nike is a large brand with no July event. Each
control replaces Barbie's brand_id (2595) with the seed's (9877 for Hot
Wheels, 15987 for Nike) in the personalization vertex and the la.vertex <>
exclusion. The rest of the SQL is unchanged.
With Nike as the seed, none of the four brands appears among the 25 largest changes. With Hot Wheels, NYX, ALDO and Primark appear with smaller changes and Amc is absent, consistent with the Mattel collector audience sharing part of the Barbie movement. Item attributes are the latest retained values.
The graph capture contains all 25 brands, query IDs and timings. The listing capture and control captures contain the follow-up SQL and full results.
What should I read to understand BERT beyond its own bibliography?
A reading list can include work several citation links beyond a paper's direct references. This query starts from the 2018 BERT record in the citation network, then excludes BERT and the papers it cites so the results focus on more distant references. The same title appears on a 2019 conference record. The seed also filters by year.
CREATE OR REPLACE VIEW bert_seed AS
SELECT paper_id AS vertex, CAST(1.0 AS DOUBLE) AS value
FROM papers
WHERE title = 'BERT: Pre-training of Deep Bidirectional Transformers for Language Understanding'
AND year = 2018;
SELECT ROUND(r.value, 6) AS ppr, p.year, p.title
FROM cugraph_personalized_pagerank(
edges => (SELECT src, dst FROM citation_edges),
personalization => (SELECT vertex, value FROM bert_seed)) r
JOIN papers p ON p.paper_id = r.vertex
WHERE NOT EXISTS (SELECT 1 FROM bert_seed s WHERE s.vertex = r.vertex)
AND NOT EXISTS (SELECT 1 FROM citation_edges e JOIN bert_seed s ON s.vertex = e.src
WHERE e.dst = r.vertex)
ORDER BY r.value DESC
LIMIT 8;
The returned list includes work on speech recognition, statistical machine translation, and the Penn Treebank.
What connects these files to the seed?
The seed is the unlabeled Win32 file 20005740, with 58 retained imports.
The query returns candidates with related imports and the symbols behind
each match. The seed was selected by SHA-256 order among files with 10 to 100
retained imports and at least five neighbors in a separate histogram graph.
Family labels were not used to select it.
Personalized PageRank creates a 25-file queue. Jaccard ranks import overlap within that queue, and relational joins retrieve shared symbols and file metadata. The sample-to-symbol edges were prepared before SQL, retaining symbols occurring in 3 to 100 Win32 challenge files.
WITH candidates AS (
SELECT p.vertex
FROM cugraph_personalized_pagerank(
edges => (
SELECT src_id AS src, dst_id AS dst
FROM ember_48_challenge_import_edges
),
personalization => (
SELECT id AS vertex, CAST(1.0 AS DOUBLE) AS value
FROM ember_49_challenge_graph_seed
),
directed => false
) p
JOIN ember_48_challenge_import_sample_counts c ON c.id = p.vertex
CROSS JOIN ember_49_challenge_graph_seed seed
WHERE p.vertex <> seed.id AND c.retained_import_count >= 5
ORDER BY p.value DESC, p.vertex
LIMIT 25
), similarities AS (
SELECT * FROM cugraph_jaccard(
edges => (
SELECT src_id AS src, dst_id AS dst
FROM ember_48_challenge_import_edges
),
vertex_pairs => (
SELECT seed.id AS first, c.vertex AS second
FROM candidates c CROSS JOIN ember_49_challenge_graph_seed seed
),
directed => false
)
)
SELECT j.second AS candidate_id, m.sha256, m.family,
counts.retained_import_count, j.similarity,
COUNT(*) AS shared_rare_imports,
MIN(symbols.symbol) AS example_shared_import
FROM similarities j
JOIN ember_48_challenge_import_edges seed_edges ON seed_edges.src_id = j.first
JOIN ember_48_challenge_import_edges candidate_edges
ON candidate_edges.src_id = j.second AND candidate_edges.dst_id = seed_edges.dst_id
JOIN ember_48_challenge_import_symbols symbols ON symbols.symbol_id = seed_edges.dst_id
JOIN ember_metadata_challenge m ON m.id = j.second
JOIN ember_48_challenge_import_sample_counts counts ON counts.id = j.second
GROUP BY j.second, m.sha256, m.family, counts.retained_import_count, j.similarity
ORDER BY j.similarity DESC, j.second
LIMIT 15;
The first four candidates share 54 imports with the seed. Each has 130
retained imports, giving Jaccard 54 / (58 + 130 - 54) = 0.403.
The query took 0.10 s.
One shared symbol is msvbvm60.dll!MSVBVM60.DLL:ordinal521.
All 15 returned family fields are empty.
The common msvbvm60.dll runtime can account for strong import overlap
without establishing a shared malware family or campaign. The symbol join
lets an analyst inspect what produced a graph match before assigning meaning
to its score. An empty family field is missing attribution.
Edges exist for only 926 of 3,225 Win32 challenge files; 2,299 files have no retained edges and are outside this candidate search. Changing the import frequency bounds requires preparing a new edge table.
The capture records all results, preparation counts and CPU checks.
Limits
- calls without an edge weight column repeat between runs; with one, scores can differ in the last digits because out-weight sums accumulate with floating-point atomics