Schema Diagram and Graph Statistics
Scale factor 100 of the LDBC Social Network Benchmark, Business Intelligence workload,
loaded from the same CSV files as the Neo4j implementation. Only the
initial_snapshot is loaded; the inserts and
deletes update streams are not applied, because this implementation runs
read-only queries. The graph is therefore a fixed snapshot whose latest
creationDate is 2012-11-29T02:52:42.849+00:00.
TigerGraph has no multi-labels, so each node belongs to exactly one vertex type and the
node total is a plain sum. The label hierarchy of the Neo4j load is flattened into
concrete types: the Message nodes are the Comment and
Post vertices, the Place nodes are City,
Country and Continent, and the Organisation nodes
are University and Company. A Cypher pattern on
(:Message) becomes (Comment|Post) in GSQL.
Node Statistics
| Vertex Type | Count |
|---|---|
Comment |
185,495,476 |
Post |
64,029,217 |
Forum |
4,611,436 |
Person |
473,001 |
Tag |
16,080 |
University |
6,380 |
Company |
1,575 |
City |
1,343 |
Country |
111 |
TagClass |
71 |
Continent |
6 |
Edge Statistics
| Edge | Count |
|---|---|
(:City)-[:IS_PART_OF]->(:Country) |
1,343 |
(:Country)-[:IS_PART_OF]->(:Continent) |
111 |
(:TagClass)-[:IS_SUBCLASS_OF]->(:TagClass) |
70 |
(:Company)-[:IS_LOCATED_IN]->(:Country) |
1,575 |
(:University)-[:IS_LOCATED_IN]->(:City) |
6,380 |
(:Tag)-[:HAS_TYPE]->(:TagClass) |
16,080 |
(:Comment)-[:HAS_CREATOR]->(:Person) |
185,495,476 |
(:Comment)-[:IS_LOCATED_IN]->(:Country) |
185,495,476 |
(:Comment)-[:REPLY_OF]->(:Comment) |
85,829,276 |
(:Comment)-[:REPLY_OF]->(:Post) |
99,666,200 |
(:Forum)-[:CONTAINER_OF]->(:Post) |
64,029,217 |
(:Forum)-[:HAS_MEMBER]->(:Person) |
303,838,931 |
(:Forum)-[:HAS_MODERATOR]->(:Person) |
4,611,436 |
(:Forum)-[:HAS_TAG]->(:Tag) |
15,195,472 |
(:Person)-[:HAS_INTEREST]->(:Tag) |
11,057,039 |
(:Person)-[:IS_LOCATED_IN]->(:City) |
473,001 |
(:Person)-[:KNOWS]->(:Person) |
18,655,515 |
(:Person)-[:LIKES]->(:Comment) |
184,325,690 |
(:Person)-[:LIKES]->(:Post) |
98,423,296 |
(:Post)-[:HAS_CREATOR]->(:Person) |
64,029,217 |
(:Comment)-[:HAS_TAG]->(:Tag) |
238,074,593 |
(:Post)-[:HAS_TAG]->(:Tag) |
78,380,259 |
(:Post)-[:IS_LOCATED_IN]->(:Country) |
64,029,217 |
(:Person)-[:STUDY_AT]->(:University) |
378,582 |
(:Person)-[:WORK_AT]->(:Company) |
1,029,492 |
Every count above matches the Neo4j load of the same dataset exactly, type by type and
pattern by pattern. Two of the 23 source relationship groups land in two TigerGraph
patterns each, because their endpoints are split across concrete types:
Place_isPartOf_Place becomes City→Country and Country→Continent,
and Organisation_isLocatedIn_Place becomes Company→Country and
University→City.
The table lists the 25 forward patterns only. Every directed edge type is
declared with a reverse edge, which TigerGraph materializes as its own type
(15 *_REVERSE types with the same counts as their forward
types). They have no Neo4j counterpart, because Neo4j can traverse any relationship in
both directions; here they are what lets a query walk from a Person to the
messages it created, or from a Country to the messages located in it.
Total vertices: 254,634,696 · Total edges: 1,703,042,944 (excluding reverse edges)
Disk space: pending
Total import time: 1,061.5 s (17m 42s) — the four loading jobs, the delta merge and the secondary indices; see Loading Times for the breakdown.
Data Loading
Overview
The graph is loaded by four GSQL loading jobs that read the very same files as the
Neo4j import: |-delimited CSV with no header row, one
directory of Spark output shards per entity. A loading job accepts a directory and
reads every file in it, so each DEFINE FILENAME points at an entity
directory, and every LOAD sets HEADER="false". That setting
matters: the upstream LDBC TigerGraph loader expects files with a header row, and
reusing it unchanged here would silently drop the first row of all 2,127 part
files.
The jobs run in a fixed order: static vertices, static edges, dynamic vertices,
dynamic edges. Every vertex exists before the first edge is loaded, and every edge load
sets VERTEX_MUST_EXIST="true", so an edge whose endpoint is missing would
be rejected and counted rather than conjuring a bare vertex out of its key. This is the
counterpart of --bad-tolerance=0 on the Neo4j side, and the final counts
show that nothing was rejected.
Three mapping decisions shape the loading jobs:
- Concrete types instead of labels. The
PlaceandOrganisationfiles carry their concrete label in a column, which routes each row toCity,Country,Continent,UniversityorCompany. Their edge files carry no type at all, so those rows are routed by id range (countries are 0–110, cities 111–1453, continents 1454–1459), a rule checked row by row against this dataset. - Millisecond timestamps.
DATETIMEin TigerGraph has second precision, while LDBC timestamps and the values stored by Neo4j carry milliseconds. EverycreationDateandbirthdayis therefore stored asINTepoch milliseconds, converted at load time by thetoMiliSecondsInttoken function. Its output was compared with an independent implementation on 33 million timestamps from this dataset, with no differences. - Directed KNOWS. The queries use
KNOWSboth undirected and with a direction, so it is a directed edge with a reverse edge rather than anUNDIRECTEDedge, which would lose the direction recorded in the source data.
TigerGraph runs here as a six-node cluster, with each vertex and edge type
partitioned across all six machines and stored on their local disks. The source CSV
is read from shared storage by one node (the m1: prefix on each path)
and distributed by the cluster.
The loader reports one or two errors for most file groups. Each one is a zero-byte
file — the _SUCCESS marker Spark leaves in every directory, plus
the occasional empty shard — which TigerGraph counts as an empty line. No data
row is affected: the loaded row counts match the source and the Neo4j load
exactly.
Create Schema
The graph is created empty, and its vertex and edge types are added by a schema change job local to the graph, so the type names cannot clash with other graphs on the same cluster. The aim is to hold exactly the data of the Neo4j load rather than the BI-specific structures of the upstream LDBC TigerGraph schema, which this schema departs from in four ways:
- Every dynamic edge keeps
creationDate, as every dynamic relationship does in the Neo4j load; upstream keeps it only onHAS_MEMBER,KNOWSandLIKES. - Messages located in a country use
IS_LOCATED_IN, as in Neo4j, with a reverse edge; upstream renames the edgeMESG_LOCATED_INand drops its reverse. KNOWSis directed with a reverse edge instead ofUNDIRECTED. An undirected traversal is written(KNOWS>|<KNOWS_REVERSE).- None of upstream's BI precomputation is added (
ROOT_POST,REPLY_COUNT,popularityScore,maxMember,weight19/weight20), andspeaksandemailareLIST<STRING>, which keeps their order like a Neo4j array, rather thanSET.
The static edges of an Organisation have no creationDate in
Neo4j. IS_LOCATED_IN is a shared edge type here, so on those edges the
attribute is 0.
CREATE GRAPH snb100()
USE GRAPH snb100
CREATE SCHEMA_CHANGE JOB snb100_schema FOR GRAPH snb100 {
ADD VERTEX Continent (PRIMARY_ID id UINT, name STRING, url STRING) WITH primary_id_as_attribute="true";
ADD VERTEX Country (PRIMARY_ID id UINT, name STRING, url STRING) WITH primary_id_as_attribute="true";
ADD VERTEX City (PRIMARY_ID id UINT, name STRING, url STRING) WITH primary_id_as_attribute="true";
ADD VERTEX Company (PRIMARY_ID id UINT, name STRING, url STRING) WITH primary_id_as_attribute="true";
ADD VERTEX University (PRIMARY_ID id UINT, name STRING, url STRING) WITH primary_id_as_attribute="true";
ADD VERTEX TagClass (PRIMARY_ID id UINT, name STRING, url STRING) WITH primary_id_as_attribute="true";
ADD VERTEX Tag (PRIMARY_ID id UINT, name STRING, url STRING) WITH primary_id_as_attribute="true";
ADD VERTEX Forum (PRIMARY_ID id UINT, creationDate INT, title STRING) WITH primary_id_as_attribute="true";
ADD VERTEX Person (PRIMARY_ID id UINT, creationDate INT, firstName STRING, lastName STRING,
gender STRING, birthday INT, locationIP STRING, browserUsed STRING,
speaks LIST<STRING>, email LIST<STRING>) WITH primary_id_as_attribute="true";
ADD VERTEX Comment (PRIMARY_ID id UINT, creationDate INT, locationIP STRING, browserUsed STRING,
content STRING, length UINT) WITH primary_id_as_attribute="true";
ADD VERTEX Post (PRIMARY_ID id UINT, creationDate INT, imageFile STRING, locationIP STRING,
browserUsed STRING, language STRING, content STRING, length UINT) WITH primary_id_as_attribute="true";
ADD DIRECTED EDGE IS_PART_OF (FROM City, TO Country | FROM Country, TO Continent) WITH REVERSE_EDGE="IS_PART_OF_REVERSE";
ADD DIRECTED EDGE IS_SUBCLASS_OF (FROM TagClass, TO TagClass) WITH REVERSE_EDGE="IS_SUBCLASS_OF_REVERSE";
ADD DIRECTED EDGE HAS_TYPE (FROM Tag, TO TagClass) WITH REVERSE_EDGE="HAS_TYPE_REVERSE";
ADD DIRECTED EDGE IS_LOCATED_IN (FROM Company, TO Country | FROM University, TO City | FROM Person, TO City
| FROM Comment, TO Country | FROM Post, TO Country,
creationDate INT) WITH REVERSE_EDGE="IS_LOCATED_IN_REVERSE";
ADD DIRECTED EDGE HAS_CREATOR (FROM Comment, TO Person | FROM Post, TO Person, creationDate INT) WITH REVERSE_EDGE="HAS_CREATOR_REVERSE";
ADD DIRECTED EDGE REPLY_OF (FROM Comment, TO Comment | FROM Comment, TO Post, creationDate INT) WITH REVERSE_EDGE="REPLY_OF_REVERSE";
ADD DIRECTED EDGE CONTAINER_OF (FROM Forum, TO Post, creationDate INT) WITH REVERSE_EDGE="CONTAINER_OF_REVERSE";
ADD DIRECTED EDGE HAS_MEMBER (FROM Forum, TO Person, creationDate INT) WITH REVERSE_EDGE="HAS_MEMBER_REVERSE";
ADD DIRECTED EDGE HAS_MODERATOR (FROM Forum, TO Person, creationDate INT) WITH REVERSE_EDGE="HAS_MODERATOR_REVERSE";
ADD DIRECTED EDGE HAS_TAG (FROM Comment, TO Tag | FROM Post, TO Tag | FROM Forum, TO Tag, creationDate INT) WITH REVERSE_EDGE="HAS_TAG_REVERSE";
ADD DIRECTED EDGE HAS_INTEREST (FROM Person, TO Tag, creationDate INT) WITH REVERSE_EDGE="HAS_INTEREST_REVERSE";
ADD DIRECTED EDGE KNOWS (FROM Person, TO Person, creationDate INT) WITH REVERSE_EDGE="KNOWS_REVERSE";
ADD DIRECTED EDGE LIKES (FROM Person, TO Comment | FROM Person, TO Post, creationDate INT) WITH REVERSE_EDGE="LIKES_REVERSE";
ADD DIRECTED EDGE STUDY_AT (FROM Person, TO University, creationDate INT, classYear INT) WITH REVERSE_EDGE="STUDY_AT_REVERSE";
ADD DIRECTED EDGE WORK_AT (FROM Person, TO Company, creationDate INT, workFrom INT) WITH REVERSE_EDGE="WORK_AT_REVERSE";
}
RUN SCHEMA_CHANGE JOB snb100_schema
DROP JOB snb100_schema
Loading Jobs
Four jobs, one LOAD statement per source directory: 8 vertex groups and 23
edge groups, the same 31 groups as the Neo4j import.
USE GRAPH snb100
CREATE LOADING JOB load_static FOR GRAPH snb100 {
DEFINE FILENAME f_Place = "m1:/datasets/prop_datasets/ldbc_100/csv/initial_snapshot/static/Place";
DEFINE FILENAME f_Organisation = "m1:/datasets/prop_datasets/ldbc_100/csv/initial_snapshot/static/Organisation";
DEFINE FILENAME f_TagClass = "m1:/datasets/prop_datasets/ldbc_100/csv/initial_snapshot/static/TagClass";
DEFINE FILENAME f_Tag = "m1:/datasets/prop_datasets/ldbc_100/csv/initial_snapshot/static/Tag";
LOAD f_Place
TO VERTEX Country VALUES ($0, $1, $2) WHERE $3 == "Country",
TO VERTEX City VALUES ($0, $1, $2) WHERE $3 == "City",
TO VERTEX Continent VALUES ($0, $1, $2) WHERE $3 == "Continent"
USING SEPARATOR="|", HEADER="false", EOL="\n";
LOAD f_Organisation
TO VERTEX Company VALUES ($0, $2, $3) WHERE $1 == "Company",
TO VERTEX University VALUES ($0, $2, $3) WHERE $1 == "University"
USING SEPARATOR="|", HEADER="false", EOL="\n";
LOAD f_TagClass
TO VERTEX TagClass VALUES ($0, $1, $2) USING SEPARATOR="|", HEADER="false", EOL="\n";
LOAD f_Tag
TO VERTEX Tag VALUES ($0, $1, $2) USING SEPARATOR="|", HEADER="false", EOL="\n";
}
CREATE LOADING JOB load_static_edge FOR GRAPH snb100 {
DEFINE FILENAME f_Place_isPartOf_Place = "m1:/datasets/prop_datasets/ldbc_100/csv/initial_snapshot/static/Place_isPartOf_Place";
DEFINE FILENAME f_Organisation_isLocatedIn_Place = "m1:/datasets/prop_datasets/ldbc_100/csv/initial_snapshot/static/Organisation_isLocatedIn_Place";
DEFINE FILENAME f_TagClass_isSubclassOf_TagClass = "m1:/datasets/prop_datasets/ldbc_100/csv/initial_snapshot/static/TagClass_isSubclassOf_TagClass";
DEFINE FILENAME f_Tag_hasType_TagClass = "m1:/datasets/prop_datasets/ldbc_100/csv/initial_snapshot/static/Tag_hasType_TagClass";
LOAD f_Place_isPartOf_Place
TO EDGE IS_PART_OF VALUES ($0 City, $1 Country) WHERE to_int($0) > 110,
TO EDGE IS_PART_OF VALUES ($0 Country, $1 Continent) WHERE to_int($0) < 111
USING SEPARATOR="|", HEADER="false", EOL="\n", VERTEX_MUST_EXIST="true";
LOAD f_Organisation_isLocatedIn_Place
TO EDGE IS_LOCATED_IN VALUES ($0 Company, $1 Country, _) WHERE to_int($1) < 111,
TO EDGE IS_LOCATED_IN VALUES ($0 University, $1 City, _) WHERE to_int($1) > 110
USING SEPARATOR="|", HEADER="false", EOL="\n", VERTEX_MUST_EXIST="true";
LOAD f_TagClass_isSubclassOf_TagClass
TO EDGE IS_SUBCLASS_OF VALUES ($0, $1) USING SEPARATOR="|", HEADER="false", EOL="\n", VERTEX_MUST_EXIST="true";
LOAD f_Tag_hasType_TagClass
TO EDGE HAS_TYPE VALUES ($0, $1) USING SEPARATOR="|", HEADER="false", EOL="\n", VERTEX_MUST_EXIST="true";
}
CREATE LOADING JOB load_dynamic_vertex FOR GRAPH snb100 {
DEFINE FILENAME f_Forum = "m1:/datasets/prop_datasets/ldbc_100/csv/initial_snapshot/dynamic/Forum";
DEFINE FILENAME f_Person = "m1:/datasets/prop_datasets/ldbc_100/csv/initial_snapshot/dynamic/Person";
DEFINE FILENAME f_Comment = "m1:/datasets/prop_datasets/ldbc_100/csv/initial_snapshot/dynamic/Comment";
DEFINE FILENAME f_Post = "m1:/datasets/prop_datasets/ldbc_100/csv/initial_snapshot/dynamic/Post";
LOAD f_Forum
TO VERTEX Forum VALUES ($1, toMiliSecondsInt($0), $2) USING SEPARATOR="|", HEADER="false", EOL="\n";
LOAD f_Person
TO VERTEX Person VALUES ($1, toMiliSecondsInt($0), $2, $3, $4, toMiliSecondsInt($5), $6, $7, SPLIT($8, ";"), SPLIT($9, ";"))
USING SEPARATOR="|", HEADER="false", EOL="\n";
LOAD f_Comment
TO VERTEX Comment VALUES ($1, toMiliSecondsInt($0), $2, $3, $4, $5) USING SEPARATOR="|", HEADER="false", EOL="\n";
LOAD f_Post
TO VERTEX Post VALUES ($1, toMiliSecondsInt($0), $2, $3, $4, $5, $6, $7) USING SEPARATOR="|", HEADER="false", EOL="\n";
}
CREATE LOADING JOB load_dynamic_edge FOR GRAPH snb100 {
DEFINE FILENAME f_Comment_hasCreator_Person = "m1:/datasets/prop_datasets/ldbc_100/csv/initial_snapshot/dynamic/Comment_hasCreator_Person";
DEFINE FILENAME f_Comment_hasTag_Tag = "m1:/datasets/prop_datasets/ldbc_100/csv/initial_snapshot/dynamic/Comment_hasTag_Tag";
DEFINE FILENAME f_Comment_isLocatedIn_Country = "m1:/datasets/prop_datasets/ldbc_100/csv/initial_snapshot/dynamic/Comment_isLocatedIn_Country";
DEFINE FILENAME f_Comment_replyOf_Comment = "m1:/datasets/prop_datasets/ldbc_100/csv/initial_snapshot/dynamic/Comment_replyOf_Comment";
DEFINE FILENAME f_Comment_replyOf_Post = "m1:/datasets/prop_datasets/ldbc_100/csv/initial_snapshot/dynamic/Comment_replyOf_Post";
DEFINE FILENAME f_Forum_containerOf_Post = "m1:/datasets/prop_datasets/ldbc_100/csv/initial_snapshot/dynamic/Forum_containerOf_Post";
DEFINE FILENAME f_Forum_hasMember_Person = "m1:/datasets/prop_datasets/ldbc_100/csv/initial_snapshot/dynamic/Forum_hasMember_Person";
DEFINE FILENAME f_Forum_hasModerator_Person = "m1:/datasets/prop_datasets/ldbc_100/csv/initial_snapshot/dynamic/Forum_hasModerator_Person";
DEFINE FILENAME f_Forum_hasTag_Tag = "m1:/datasets/prop_datasets/ldbc_100/csv/initial_snapshot/dynamic/Forum_hasTag_Tag";
DEFINE FILENAME f_Person_hasInterest_Tag = "m1:/datasets/prop_datasets/ldbc_100/csv/initial_snapshot/dynamic/Person_hasInterest_Tag";
DEFINE FILENAME f_Person_isLocatedIn_City = "m1:/datasets/prop_datasets/ldbc_100/csv/initial_snapshot/dynamic/Person_isLocatedIn_City";
DEFINE FILENAME f_Person_knows_Person = "m1:/datasets/prop_datasets/ldbc_100/csv/initial_snapshot/dynamic/Person_knows_Person";
DEFINE FILENAME f_Person_likes_Comment = "m1:/datasets/prop_datasets/ldbc_100/csv/initial_snapshot/dynamic/Person_likes_Comment";
DEFINE FILENAME f_Person_likes_Post = "m1:/datasets/prop_datasets/ldbc_100/csv/initial_snapshot/dynamic/Person_likes_Post";
DEFINE FILENAME f_Person_studyAt_University = "m1:/datasets/prop_datasets/ldbc_100/csv/initial_snapshot/dynamic/Person_studyAt_University";
DEFINE FILENAME f_Person_workAt_Company = "m1:/datasets/prop_datasets/ldbc_100/csv/initial_snapshot/dynamic/Person_workAt_Company";
DEFINE FILENAME f_Post_hasCreator_Person = "m1:/datasets/prop_datasets/ldbc_100/csv/initial_snapshot/dynamic/Post_hasCreator_Person";
DEFINE FILENAME f_Post_hasTag_Tag = "m1:/datasets/prop_datasets/ldbc_100/csv/initial_snapshot/dynamic/Post_hasTag_Tag";
DEFINE FILENAME f_Post_isLocatedIn_Country = "m1:/datasets/prop_datasets/ldbc_100/csv/initial_snapshot/dynamic/Post_isLocatedIn_Country";
LOAD f_Comment_hasCreator_Person
TO EDGE HAS_CREATOR VALUES ($1 Comment, $2 Person, toMiliSecondsInt($0)) USING SEPARATOR="|", HEADER="false", EOL="\n", VERTEX_MUST_EXIST="true";
LOAD f_Post_hasCreator_Person
TO EDGE HAS_CREATOR VALUES ($1 Post, $2 Person, toMiliSecondsInt($0)) USING SEPARATOR="|", HEADER="false", EOL="\n", VERTEX_MUST_EXIST="true";
LOAD f_Comment_isLocatedIn_Country
TO EDGE IS_LOCATED_IN VALUES ($1 Comment, $2 Country, toMiliSecondsInt($0)) USING SEPARATOR="|", HEADER="false", EOL="\n", VERTEX_MUST_EXIST="true";
LOAD f_Post_isLocatedIn_Country
TO EDGE IS_LOCATED_IN VALUES ($1 Post, $2 Country, toMiliSecondsInt($0)) USING SEPARATOR="|", HEADER="false", EOL="\n", VERTEX_MUST_EXIST="true";
LOAD f_Person_isLocatedIn_City
TO EDGE IS_LOCATED_IN VALUES ($1 Person, $2 City, toMiliSecondsInt($0)) USING SEPARATOR="|", HEADER="false", EOL="\n", VERTEX_MUST_EXIST="true";
LOAD f_Comment_replyOf_Comment
TO EDGE REPLY_OF VALUES ($1 Comment, $2 Comment, toMiliSecondsInt($0)) USING SEPARATOR="|", HEADER="false", EOL="\n", VERTEX_MUST_EXIST="true";
LOAD f_Comment_replyOf_Post
TO EDGE REPLY_OF VALUES ($1 Comment, $2 Post, toMiliSecondsInt($0)) USING SEPARATOR="|", HEADER="false", EOL="\n", VERTEX_MUST_EXIST="true";
LOAD f_Comment_hasTag_Tag
TO EDGE HAS_TAG VALUES ($1 Comment, $2 Tag, toMiliSecondsInt($0)) USING SEPARATOR="|", HEADER="false", EOL="\n", VERTEX_MUST_EXIST="true";
LOAD f_Post_hasTag_Tag
TO EDGE HAS_TAG VALUES ($1 Post, $2 Tag, toMiliSecondsInt($0)) USING SEPARATOR="|", HEADER="false", EOL="\n", VERTEX_MUST_EXIST="true";
LOAD f_Forum_hasTag_Tag
TO EDGE HAS_TAG VALUES ($1 Forum, $2 Tag, toMiliSecondsInt($0)) USING SEPARATOR="|", HEADER="false", EOL="\n", VERTEX_MUST_EXIST="true";
LOAD f_Person_likes_Comment
TO EDGE LIKES VALUES ($1 Person, $2 Comment, toMiliSecondsInt($0)) USING SEPARATOR="|", HEADER="false", EOL="\n", VERTEX_MUST_EXIST="true";
LOAD f_Person_likes_Post
TO EDGE LIKES VALUES ($1 Person, $2 Post, toMiliSecondsInt($0)) USING SEPARATOR="|", HEADER="false", EOL="\n", VERTEX_MUST_EXIST="true";
LOAD f_Forum_containerOf_Post
TO EDGE CONTAINER_OF VALUES ($1, $2, toMiliSecondsInt($0)) USING SEPARATOR="|", HEADER="false", EOL="\n", VERTEX_MUST_EXIST="true";
LOAD f_Forum_hasMember_Person
TO EDGE HAS_MEMBER VALUES ($1, $2, toMiliSecondsInt($0)) USING SEPARATOR="|", HEADER="false", EOL="\n", VERTEX_MUST_EXIST="true";
LOAD f_Forum_hasModerator_Person
TO EDGE HAS_MODERATOR VALUES ($1, $2, toMiliSecondsInt($0)) USING SEPARATOR="|", HEADER="false", EOL="\n", VERTEX_MUST_EXIST="true";
LOAD f_Person_hasInterest_Tag
TO EDGE HAS_INTEREST VALUES ($1, $2, toMiliSecondsInt($0)) USING SEPARATOR="|", HEADER="false", EOL="\n", VERTEX_MUST_EXIST="true";
LOAD f_Person_knows_Person
TO EDGE KNOWS VALUES ($1, $2, toMiliSecondsInt($0)) USING SEPARATOR="|", HEADER="false", EOL="\n", VERTEX_MUST_EXIST="true";
LOAD f_Person_studyAt_University
TO EDGE STUDY_AT VALUES ($1, $2, toMiliSecondsInt($0), $3) USING SEPARATOR="|", HEADER="false", EOL="\n", VERTEX_MUST_EXIST="true";
LOAD f_Person_workAt_Company
TO EDGE WORK_AT VALUES ($1, $2, toMiliSecondsInt($0), $3) USING SEPARATOR="|", HEADER="false", EOL="\n", VERTEX_MUST_EXIST="true";
}
Create Indices
Secondary indices are built after the data is loaded. The id uniqueness constraints of
the Neo4j load need no counterpart, because a PRIMARY_ID is unique and
hash-indexed by construction, and the KNOWS.creationDate relationship
index has none either, because TigerGraph indexes vertex attributes only.
USE GRAPH snb100
CREATE SCHEMA_CHANGE JOB snb100_index FOR GRAPH snb100 {
ALTER VERTEX Country ADD INDEX snb100_country_name ON (name);
ALTER VERTEX Person ADD INDEX snb100_person_firstName ON (firstName);
ALTER VERTEX Tag ADD INDEX snb100_tag_name ON (name);
ALTER VERTEX TagClass ADD INDEX snb100_tagclass_name ON (name);
ALTER VERTEX Comment ADD INDEX snb100_comment_creationDate ON (creationDate);
ALTER VERTEX Post ADD INDEX snb100_post_creationDate ON (creationDate);
ALTER VERTEX Forum ADD INDEX snb100_forum_creationDate ON (creationDate);
}
RUN SCHEMA_CHANGE JOB snb100_index
DROP JOB snb100_index
Loading Times
Each phase of the import is timed by wall clock. The figure to compare with
the Neo4j import is the sum of the four loading jobs, the delta merge and the index
build, because neo4j-admin database import builds its indices during the
same pass. Creating the schema and the jobs is a one-off compilation step and is
listed separately.
| Phase | Wall clock |
|---|---|
Check TokenBank for toMiliSecondsInt |
0.9 s |
Create graph and types (schema.gsql) |
36.4 s |
Create loading jobs (load_jobs.gsql) |
24.7 s |
Loading job load_static |
9.6 s |
Loading job load_static_edge |
9.6 s |
Loading job load_dynamic_vertex |
186.6 s (3m 7s) |
Loading job load_dynamic_edge |
802.1 s (13m 22s) |
| All four loading jobs | 1,011.7 s (16m 52s) |
Merge deltas (/rebuildnow) |
13.9 s |
Build secondary indices (indexes.gsql) |
36.0 s |
Queries
The 41 queries are those of the Neo4j implementation, with the same numbering and the same
parameter values, which every query takes as its parameter defaults. Timestamps are passed
as epoch milliseconds, as they are stored. KNOWS is a directed edge with a reverse edge, so
Cypher’s undirected -[:KNOWS]- becomes the pair
(KNOWS|KNOWS_REVERSE).
The BI queries follow the upstream LDBC TigerGraph implementation where it applies. That implementation relies on precomputed edges and attributes (a root-post edge, reply counts, popularity scores, path weights) that this schema deliberately leaves out, so each query computes what it needs itself: the REPLY_OF*0.. pattern, from a message to the post at the root of its thread, is a loop that walks the reply edges. Every query is self-contained and installed as a distributed query.
Select a query below to view its logic and description.
Q1. One-hop friends of a given person
Return the direct KNOWS neighbours of one person: one hop from the start vertex over KNOWS in both directions, the stored edge and its reverse.
CREATE OR REPLACE DISTRIBUTED QUERY q1_one_hop_friends(UINT p_person_id = 100316, INT p_limit = 100)
FOR GRAPH snb100 SYNTAX V1 {
TYPEDEF TUPLE <UINT personId, STRING firstName, STRING lastName> Row;
HeapAccum<Row>(100, personId ASC) @@result;
@@result.resize(p_limit);
persons = {Person.*};
start = SELECT s FROM persons:s WHERE s.id == p_person_id;
friends = SELECT t FROM start:s -((KNOWS|KNOWS_REVERSE):e)-> Person:t
ACCUM @@result += Row(t.id, t.firstName, t.lastName);
PRINT @@result AS result;
}
Q2. Persons within three friendship hops
Return every distinct person reachable within one to three KNOWS hops of a start person. The frontier grows quickly on a social graph, so this measures expansion cost and duplicate elimination across overlapping paths.
CREATE OR REPLACE DISTRIBUTED QUERY q2_three_hop_neighborhood(UINT p_person_id = 100316, INT p_limit = 100)
FOR GRAPH snb100 SYNTAX V1 {
TYPEDEF TUPLE <UINT personId, STRING firstName, STRING lastName> Row;
HeapAccum<Row>(100, personId ASC) @@result;
OrAccum @visited;
INT hops = 0;
@@result.resize(p_limit);
persons = {Person.*};
frontier = SELECT s FROM persons:s WHERE s.id == p_person_id POST-ACCUM s.@visited = TRUE;
WHILE hops < 3 DO
frontier = SELECT t FROM frontier:s -((KNOWS|KNOWS_REVERSE):e)-> Person:t
WHERE NOT t.@visited
POST-ACCUM t.@visited = TRUE, @@result += Row(t.id, t.firstName, t.lastName);
hops = hops + 1;
END;
PRINT @@result AS result;
}
Q3. Persons exactly three friendship hops away
Return the persons reached by paths of length exactly three, rather than everything within three hops. Only the start person is excluded, so a person who is also reachable in fewer hops still appears if a three-hop path to them exists.
CREATE OR REPLACE DISTRIBUTED QUERY q3_exact_three_hop_neighbors(UINT p_person_id = 100316, INT p_limit = 100)
FOR GRAPH snb100 SYNTAX V1 {
TYPEDEF TUPLE <UINT personId, STRING firstName, STRING lastName> Row;
HeapAccum<Row>(100, personId ASC) @@result;
OrAccum @isStart, @hit;
SetAccum<UINT> @firstHops;
@@result.resize(p_limit);
persons = {Person.*};
start = SELECT s FROM persons:s WHERE s.id == p_person_id POST-ACCUM s.@isStart = TRUE;
hop1 = SELECT t FROM start:s -((KNOWS|KNOWS_REVERSE):e)-> Person:t;
hop2 = SELECT t FROM hop1:b -((KNOWS|KNOWS_REVERSE):e)-> Person:t
WHERE NOT t.@isStart
ACCUM t.@firstHops += b.id;
hop3 = SELECT t FROM hop2:c -((KNOWS|KNOWS_REVERSE):e)-> Person:t
WHERE NOT t.@isStart
ACCUM IF c.@firstHops.size() > 1 OR NOT c.@firstHops.contains(t.id) THEN t.@hit += TRUE END
POST-ACCUM IF t.@hit THEN @@result += Row(t.id, t.firstName, t.lastName) END;
PRINT @@result AS result;
}
Q4. Persons within two hops speaking a given language
Expand one to two KNOWS hops and keep only the persons who speak a given language and were born on or after a given date. Each breadth-first level tests the filter as it reaches a person; the speaks attribute is a list, so the test is contains.
CREATE OR REPLACE DISTRIBUTED QUERY q4_two_hop_language_filter(UINT p_person_id = 100316,
STRING p_language = "en",
INT p_birthday = 315532800000,
INT p_limit = 100)
FOR GRAPH snb100 SYNTAX V1 {
TYPEDEF TUPLE <UINT personId, STRING firstName, STRING lastName, INT birthday> Row;
HeapAccum<Row>(100, birthday DESC, personId ASC) @@result;
OrAccum @visited;
INT hops = 0;
@@result.resize(p_limit);
persons = {Person.*};
frontier = SELECT s FROM persons:s WHERE s.id == p_person_id POST-ACCUM s.@visited = TRUE;
WHILE hops < 2 DO
frontier = SELECT t FROM frontier:s -((KNOWS|KNOWS_REVERSE):e)-> Person:t
WHERE NOT t.@visited
POST-ACCUM
t.@visited = TRUE,
IF t.speaks.contains(p_language) AND t.birthday >= p_birthday THEN
@@result += Row(t.id, t.firstName, t.lastName, t.birthday)
END;
hops = hops + 1;
END;
PRINT @@result AS result;
}
Q5. Persons speaking a given language who registered in a period
Filter Person vertices on a list attribute and a creationDate range with no traversal at all: a single vertex scan, measuring predicate evaluation in isolation.
CREATE OR REPLACE DISTRIBUTED QUERY q5_language_and_registration_filter(STRING p_language = "en",
INT p_start = 1277942400000,
INT p_end = 1278547200000,
INT p_limit = 100)
FOR GRAPH snb100 SYNTAX V1 {
TYPEDEF TUPLE <UINT personId, STRING firstName, STRING lastName, INT creationDate> Row;
HeapAccum<Row>(100, creationDate DESC, personId ASC) @@result;
@@result.resize(p_limit);
persons = {Person.*};
matched = SELECT p FROM persons:p
WHERE p.speaks.contains(p_language) AND p.creationDate >= p_start AND p.creationDate <= p_end
ACCUM @@result += Row(p.id, p.firstName, p.lastName, p.creationDate);
PRINT @@result AS result;
}
Q6. Friendship edges created within a given window
Filter KNOWS edges by their creationDate and return both endpoints: one pass over the KNOWS edges with a range predicate on an edge attribute.
CREATE OR REPLACE DISTRIBUTED QUERY q6_knows_edges_in_window(
INT p_start = 1277942400000,
INT p_end = 1278547200000,
INT p_limit = 100)
FOR GRAPH snb100 SYNTAX V1 {
TYPEDEF TUPLE <UINT person1Id, UINT person2Id, INT creationDate> Row;
HeapAccum<Row>(100, creationDate ASC, person1Id ASC) @@result;
@@result.resize(p_limit);
persons = {Person.*};
matched = SELECT t FROM persons:s -(KNOWS:e)-> Person:t
WHERE e.creationDate >= p_start AND e.creationDate <= p_end
ACCUM @@result += Row(s.id, t.id, e.creationDate);
PRINT @@result AS result;
}
Q7. Whether two persons are connected within four friendship hops
Return a single boolean saying whether a bounded KNOWS path exists between two persons. The breadth-first search stops at the first level that reaches the second person, so this measures how early the search can stop rather than full path enumeration.
CREATE OR REPLACE DISTRIBUTED QUERY q7_reachability_within_four_hops(UINT p_person1_id = 10006,
UINT p_person2_id = 2199023261649)
FOR GRAPH snb100 SYNTAX V1 {
OrAccum @visited;
OrAccum @@reachable;
INT hops = 0;
persons = {Person.*};
frontier = SELECT s FROM persons:s WHERE s.id == p_person1_id POST-ACCUM s.@visited = TRUE;
WHILE hops < 4 AND NOT @@reachable AND frontier.size() > 0 DO
frontier = SELECT t FROM frontier:s -((KNOWS|KNOWS_REVERSE):e)-> Person:t
WHERE NOT t.@visited
POST-ACCUM t.@visited = TRUE, IF t.id == p_person2_id THEN @@reachable += TRUE END;
hops = hops + 1;
END;
PRINT @@reachable AS reachable;
}
Q8. Shortest friendship path between two persons
Return the shortest KNOWS path between two persons, bounded at six hops, as the list of person ids along it. A breadth-first search records each newly reached person’s smallest-id parent and its path so far, and stops at the level that reaches the target. The weighted variants of this problem are Q10, Q11 and Q12.
CREATE OR REPLACE DISTRIBUTED QUERY q8_shortest_friendship_path(UINT p_person1_id = 10006,
UINT p_person2_id = 2199023261649, INT p_max_hops = 6)
FOR GRAPH snb100 SYNTAX V1 {
TYPEDEF TUPLE <STRING personIds, INT hopCount> Row;
ListAccum<Row> @@result;
OrAccum @visited;
OrAccum @@found;
SumAccum<INT> @level;
MinAccum<UINT> @parentId;
SumAccum<STRING> @path;
INT hops = 0;
persons = {Person.*};
frontier = SELECT s FROM persons:s WHERE s.id == p_person1_id
POST-ACCUM s.@visited = TRUE, s.@path += to_string(s.id);
WHILE hops < p_max_hops AND NOT @@found AND frontier.size() > 0 DO
hops = hops + 1;
reached = SELECT t FROM frontier:s -((KNOWS|KNOWS_REVERSE):e)-> Person:t
WHERE NOT t.@visited
ACCUM t.@parentId += s.id
POST-ACCUM t.@visited = TRUE, t.@level = hops;
frontier = SELECT t FROM frontier:s -((KNOWS|KNOWS_REVERSE):e)-> Person:t
WHERE t.@level == hops AND s.id == t.@parentId
ACCUM t.@path += s.@path + ", " + to_string(t.id)
POST-ACCUM IF t.id == p_person2_id THEN
@@found += TRUE, @@result += Row("[" + t.@path + "]", hops)
END;
END;
PRINT @@result AS result;
}
Q9. All simple friendship paths between two persons up to length three
Enumerate every path between two persons up to three hops, keeping only simple paths. With the neighbours of both endpoints marked, the one-, two- and three-hop paths are read off directly, and inequality tests drop those that revisit a person. Unlike reachability or shortest path this materialises every result.
CREATE OR REPLACE DISTRIBUTED QUERY q9_simple_paths_up_to_three(UINT p_person1_id = 10006,
UINT p_person2_id = 2199023261649, INT p_limit = 100)
FOR GRAPH snb100 SYNTAX V1 {
TYPEDEF TUPLE <INT hopCount, STRING personIds> Row;
HeapAccum<Row>(100, hopCount ASC, personIds ASC) @@result;
OrAccum @nearA, @nearB, @isA, @isB;
@@result.resize(p_limit);
persons = {Person.*};
a = SELECT s FROM persons:s WHERE s.id == p_person1_id POST-ACCUM s.@isA = TRUE;
b = SELECT s FROM persons:s WHERE s.id == p_person2_id POST-ACCUM s.@isB = TRUE;
nearA = SELECT t FROM a:s -((KNOWS|KNOWS_REVERSE):e)-> Person:t
POST-ACCUM t.@nearA = TRUE,
IF t.@isB THEN
@@result += Row(1, "[" + to_string(p_person1_id) + ", " + to_string(p_person2_id) + "]")
END;
nearB = SELECT t FROM b:s -((KNOWS|KNOWS_REVERSE):e)-> Person:t POST-ACCUM t.@nearB = TRUE;
len2 = SELECT x FROM nearA:x
WHERE x.@nearB AND NOT x.@isB
ACCUM @@result += Row(2, "[" + to_string(p_person1_id) + ", " + to_string(x.id) + ", "
+ to_string(p_person2_id) + "]");
len3 = SELECT y FROM nearA:x -((KNOWS|KNOWS_REVERSE):e)-> Person:y
WHERE NOT x.@isB AND y.@nearB AND NOT y.@isA AND NOT y.@isB
ACCUM @@result += Row(3, "[" + to_string(p_person1_id) + ", " + to_string(x.id) + ", "
+ to_string(y.id) + ", " + to_string(p_person2_id) + "]");
PRINT @@result AS result;
}
Q10. Weighted interaction paths
Find the most trusted path between two persons. Edge weight is derived from how strongly the two interacted inside forums created in a given window (a reply counts 1.0 or 0.5, and the weight is 1/(w+1)). The query walks down the threads of those forums to collect the replies, accumulates the interaction weight on each person, then runs a label-correcting shortest-path search that prunes any path already longer than the best one found.
CREATE OR REPLACE DISTRIBUTED QUERY q10_weighted_interaction_path(UINT p_person1_id = 10006,
UINT p_person2_id = 2199023261649,
INT p_start = 1271376000000,
INT p_end = 1273276800000)
FOR GRAPH snb100 SYNTAX V1 {
MinAccum<DOUBLE> @@best, @dist;
OrAccum @next, @inWindow;
MinAccum<VERTEX<Person>> @creator;
MapAccum<VERTEX<Person>, SumAccum<UINT>> @replyCount, @repliesFrom;
DOUBLE unreachable;
forums = {Forum.*};
forums = SELECT f FROM forums:f WHERE f.creationDate >= p_start AND f.creationDate <= p_end;
posts = SELECT p FROM forums:f -(CONTAINER_OF)-> Post:p ACCUM p.@inWindow += TRUE;
threads (ANY) = posts;
frontier (ANY) = posts;
WHILE frontier.size() > 0 DO
frontier = SELECT c FROM frontier:m -(REPLY_OF_REVERSE)-> Comment:c ACCUM c.@inWindow += TRUE;
threads = threads UNION frontier;
END;
tmp = SELECT m FROM threads:m -(HAS_CREATOR)-> Person:p ACCUM m.@creator += p;
tmp = SELECT parent FROM threads:parent -(REPLY_OF_REVERSE)-> Comment:child
WHERE child.@inWindow
ACCUM
UINT w = 1,
IF parent.type == "Post" THEN w = 2 END,
parent.@repliesFrom += (child.@creator -> w);
tmp = SELECT p FROM threads:m -(HAS_CREATOR)-> Person:p ACCUM p.@replyCount += m.@repliesFrom;
persons = {Person.*};
unreachable = persons.size();
@@best = unreachable;
frontier = SELECT s FROM persons:s WHERE s.id == p_person1_id ACCUM s.@dist = 0;
WHILE frontier.size() > 0 DO
frontier = SELECT t FROM frontier:s -((KNOWS|KNOWS_REVERSE):e)-> Person:t
ACCUM
DOUBLE d = s.@dist + 2.0 / (2 + s.@replyCount.get(t) + t.@replyCount.get(s)),
IF d < @@best AND d < t.@dist THEN
t.@dist += d,
t.@next += TRUE,
IF t.id == p_person2_id THEN @@best += d END
END
HAVING t.@next;
frontier = SELECT s FROM frontier:s ACCUM s.@next = FALSE;
END;
IF abs(@@best - unreachable) < 1 THEN
PRINT -1.0 AS totalCost;
ELSE
PRINT @@best AS totalCost;
END;
}
Q11. Interaction path between cities
Find the cheapest weighted path between persons living in two given cities. Edge weight falls as reply interaction rises (round(40-sqrt(numInteractions)), floored at 1), so heavily interacting pairs sit closer together. The interaction counts are computed from every reply in the graph, then a multi-source search keeps a distance per source person and every pair at the minimum.
CREATE OR REPLACE DISTRIBUTED QUERY q11_interaction_path_between_cities(UINT p_city1_id = 702,
UINT p_city2_id = 967, INT p_batches = 32)
FOR GRAPH snb100 SYNTAX V1 {
TYPEDEF TUPLE <UINT person1Id, UINT person2Id, UINT totalWeight> Row;
HeapAccum<Row>(1, person1Id ASC, person2Id ASC) @@result;
SetAccum<UINT> @friends;
MaxAccum<UINT> @creatorId, @parentCreatorId;
MapAccum<UINT, SumAccum<UINT>> @interactions;
MinAccum<UINT> @dist;
MapAccum<UINT, MinAccum<UINT>> @distFromTarget;
OrAccum @next, @inCity1, @inCity2;
MinAccum<UINT> @@best;
SetAccum<VERTEX<Person>> @@nearestTargets;
UINT b = 0;
@@result.resize(1000);
persons = {Person.*};
withFriends = SELECT t FROM persons:s -((KNOWS|KNOWS_REVERSE):e)-> Person:t ACCUM s.@friends += t.id;
WHILE b < p_batches DO
parents = SELECT m FROM persons:p -(HAS_CREATOR_REVERSE)-> (Comment|Post):m
WHERE p.id % p_batches == b
ACCUM m.@creatorId += p.id;
replies = SELECT c FROM parents:m -(REPLY_OF_REVERSE)-> Comment:c
ACCUM c.@parentCreatorId += m.@creatorId;
repliers = SELECT p FROM replies:c -(HAS_CREATOR)-> Person:p
ACCUM
IF p.@friends.contains(c.@parentCreatorId) THEN
p.@interactions += (c.@parentCreatorId -> 1)
END;
b = b + 1;
END;
cities = {City.*};
city1 = SELECT c FROM cities:c WHERE c.id == p_city1_id;
city2 = SELECT c FROM cities:c WHERE c.id == p_city2_id;
sources = SELECT p FROM city1:c -(IS_LOCATED_IN_REVERSE)-> Person:p ACCUM p.@inCity1 += TRUE, p.@dist += 0;
targets = SELECT p FROM city2:c -(IS_LOCATED_IN_REVERSE)-> Person:p ACCUM p.@inCity2 += TRUE;
@@best += 4294967295;
frontier = sources;
WHILE frontier.size() > 0 DO
frontier = SELECT t FROM frontier:s -((KNOWS|KNOWS_REVERSE):e)-> Person:t
ACCUM
UINT n = s.@interactions.get(t.id) + t.@interactions.get(s.id),
INT weight = 0,
UINT d = 0,
IF n > 0 THEN
weight = round(40 - sqrt(n)),
IF weight < 1 THEN weight = 1 END,
d = s.@dist + weight,
IF d < t.@dist AND d <= @@best THEN
t.@dist += d,
t.@next += TRUE,
IF t.@inCity2 THEN @@best += d END
END
END
HAVING t.@next;
frontier = SELECT s FROM frontier:s ACCUM s.@next = FALSE;
END;
nearest = SELECT p FROM targets:p
WHERE p.@dist == @@best
ACCUM @@nearestTargets += p, p.@distFromTarget += (p.id -> 0);
frontier = nearest;
WHILE frontier.size() > 0 DO
frontier = SELECT t FROM frontier:s -((KNOWS|KNOWS_REVERSE):e)-> Person:t
ACCUM
UINT n = s.@interactions.get(t.id) + t.@interactions.get(s.id),
INT weight = 0,
UINT d = 0,
IF n > 0 THEN
weight = round(40 - sqrt(n)),
IF weight < 1 THEN weight = 1 END,
FOREACH (src, dis) IN s.@distFromTarget DO
d = dis + weight,
IF d <= @@best AND d < t.@distFromTarget.get(src) THEN
t.@distFromTarget += (src -> d),
t.@next += TRUE
END
END
END
HAVING t.@next;
frontier = SELECT s FROM frontier:s ACCUM s.@next = FALSE;
END;
pairs = SELECT p FROM sources:p
ACCUM
FOREACH (targetId, dis) IN p.@distFromTarget DO
IF dis == @@best THEN @@result += Row(p.id, targetId, dis) END
END;
PRINT @@result AS result;
}
Q12. Recruitment
From every current employee of a target company, find the cheapest weighted path to a given candidate. Edge weight is the smallest difference in enrolment year between two persons who studied at the same university (min(abs(classYear difference)) + 1), computed on the fly from each person’s universities. The weighting is symmetric, so one search from the candidate gives every employee’s distance.
CREATE OR REPLACE DISTRIBUTED QUERY q12_recruitment(STRING p_company = "National_Airlines_(5M)",
UINT p_person2_id = 13194139540317)
FOR GRAPH snb100 SYNTAX V1 {
TYPEDEF TUPLE <UINT person1Id, UINT totalWeight> Row;
HeapAccum<Row>(20, totalWeight ASC, person1Id ASC) @@result;
OrAccum @next, @inCompany;
MinAccum<UINT> @dist;
MinAccum<UINT> @@best;
MapAccum<VERTEX<University>, MinAccum<INT>> @classYear;
persons = {Person.*};
tmp = SELECT u FROM persons:p -(STUDY_AT:e)-> University:u ACCUM p.@classYear += (u -> e.classYear);
companies = {Company.*};
workers = SELECT p FROM companies:c -(WORK_AT_REVERSE)-> Person:p
WHERE c.name == p_company AND p.id != p_person2_id
ACCUM p.@inCompany += TRUE;
@@best += persons.size() * 1000;
frontier = SELECT s FROM persons:s WHERE s.id == p_person2_id ACCUM s.@dist += 0;
WHILE frontier.size() > 0 DO
frontier = SELECT t FROM frontier:s -((KNOWS|KNOWS_REVERSE):e)-> Person:t
ACCUM
INT w = -1,
INT diff = 0,
UINT d = 0,
FOREACH (u, y) IN s.@classYear DO
IF t.@classYear.containsKey(u) THEN
diff = abs(y - t.@classYear.get(u)) + 1,
IF w < 0 OR diff < w THEN w = diff END
END
END,
IF w > 0 THEN
d = s.@dist + w,
IF d < t.@dist AND d <= @@best THEN
t.@dist += d,
t.@next += TRUE,
IF t.@inCompany THEN @@best += d END
END
END
HAVING t.@next;
frontier = SELECT s FROM frontier:s ACCUM s.@next = FALSE;
END;
reached = SELECT p FROM workers:p
WHERE p.@dist == @@best
ACCUM @@result += Row(p.id, p.@dist);
PRINT @@result AS result;
}
Q13. Friend triangles
Count friendship triangles inside one country where all three KNOWS edges were created within a given window. The a.id < b.id < c.id ordering deduplicates, so each triangle is counted once rather than six times.
CREATE OR REPLACE DISTRIBUTED QUERY q13_friend_triangles(STRING p_country = "India",
INT p_start = 1348790400000,
INT p_end = 1357776000000)
FOR GRAPH snb100 SYNTAX V1 {
SumAccum<UINT> @@count;
OrAccum @selected;
SetAccum<UINT> @lowerNeighbors;
countries = {Country.*};
country = SELECT c FROM countries:c WHERE c.name == p_country;
cities = SELECT ci FROM country:c -(IS_PART_OF_REVERSE)-> City:ci;
persons = SELECT p FROM cities:ci -(IS_LOCATED_IN_REVERSE)-> Person:p ACCUM p.@selected += TRUE;
P2 = SELECT b FROM persons:a -((KNOWS|KNOWS_REVERSE):e)-> Person:b
WHERE b.@selected AND a.id < b.id AND e.creationDate >= p_start AND e.creationDate <= p_end
ACCUM b.@lowerNeighbors += a.id;
tmp = SELECT c FROM P2:b -((KNOWS|KNOWS_REVERSE):e)-> Person:c
WHERE c.@selected AND b.id < c.id AND e.creationDate >= p_start AND e.creationDate <= p_end
ACCUM
FOREACH a IN b.@lowerNeighbors DO
IF c.@lowerNeighbors.contains(a) THEN @@count += 1 END
END;
PRINT @@count AS triangleCount;
}
Q14. Friendship squares around a given person
Find four-node cycles through a given person: the person, two of their friends, and a fourth person who knows both. All six inequality constraints are stated explicitly so that degenerate matches collapsing two roles onto one person are excluded.
CREATE OR REPLACE DISTRIBUTED QUERY q14_friendship_squares(UINT p_person_id = 100316, INT p_limit = 100)
FOR GRAPH snb100 SYNTAX V1 {
TYPEDEF TUPLE <UINT firstFriendId, UINT bridgeFriendId, UINT secondFriendId> Row;
HeapAccum<Row>(100, firstFriendId ASC, bridgeFriendId ASC, secondFriendId ASC) @@result;
OrAccum @isStart, @nearStart;
SetAccum<UINT> @firstFriends;
@@result.resize(p_limit);
persons = {Person.*};
start = SELECT s FROM persons:s WHERE s.id == p_person_id POST-ACCUM s.@isStart = TRUE;
friends = SELECT t FROM start:s -((KNOWS|KNOWS_REVERSE):e)-> Person:t POST-ACCUM t.@nearStart = TRUE;
bridges = SELECT c FROM friends:b -((KNOWS|KNOWS_REVERSE):e)-> Person:c
WHERE NOT c.@isStart
ACCUM c.@firstFriends += b.id;
tmp = SELECT d FROM bridges:c -((KNOWS|KNOWS_REVERSE):e)-> Person:d
WHERE d.@nearStart
ACCUM
FOREACH b IN c.@firstFriends DO
IF b != d.id THEN @@result += Row(b, c.id, d.id) END
END;
PRINT @@result AS result;
}
Q15. Forums with the most members among those created after a date
Find hub forums: those created after a given date whose membership exceeds a threshold, ranked by member count. HAS_MEMBER is the largest relationship type in the graph at 303.8 million edges and forum membership is heavily skewed, so this is the star pattern under a realistic degree distribution.
CREATE OR REPLACE DISTRIBUTED QUERY q15_forums_with_most_members(
INT p_date = 1338508800000,
INT p_min_degree = 100, INT p_limit = 100)
FOR GRAPH snb100 SYNTAX V1 {
TYPEDEF TUPLE <UINT forumId, STRING title, UINT memberCount> Row;
HeapAccum<Row>(100, memberCount DESC, forumId ASC) @@result;
SumAccum<UINT> @members;
@@result.resize(p_limit);
forums = {Forum.*};
forums = SELECT f FROM forums:f WHERE f.creationDate > p_date;
tmp = SELECT f FROM forums:f -(HAS_MEMBER)-> Person:p
ACCUM f.@members += 1
POST-ACCUM IF f.@members >= p_min_degree THEN @@result += Row(f.id, f.title, f.@members) END;
PRINT @@result AS result;
}
Q16. Groups of four mutually acquainted persons around a person
Find 4-cliques containing a given person, where all six pairs among the four are connected by KNOWS. The b.id < c.id < d.id ordering reports each clique once instead of six times. It is the densest pattern in the set and the most demanding on join ordering.
CREATE OR REPLACE DISTRIBUTED QUERY q16_four_cliques(UINT p_person_id = 100316, INT p_limit = 100)
FOR GRAPH snb100 SYNTAX V1 {
TYPEDEF TUPLE <UINT person2Id, UINT person3Id, UINT person4Id> Row;
HeapAccum<Row>(100, person2Id ASC, person3Id ASC, person4Id ASC) @@result;
OrAccum @nearStart;
SetAccum<UINT> @lowerFriends;
@@result.resize(p_limit);
persons = {Person.*};
start = SELECT s FROM persons:s WHERE s.id == p_person_id;
friends = SELECT t FROM start:s -((KNOWS|KNOWS_REVERSE):e)-> Person:t POST-ACCUM t.@nearStart = TRUE;
tmp = SELECT c FROM friends:b -((KNOWS|KNOWS_REVERSE):e)-> Person:c
WHERE c.@nearStart AND b.id < c.id
ACCUM c.@lowerFriends += b.id;
tmp = SELECT d FROM friends:c -((KNOWS|KNOWS_REVERSE):e)-> Person:d
WHERE d.@nearStart AND c.id < d.id
ACCUM
FOREACH b IN c.@lowerFriends DO
IF d.@lowerFriends.contains(b) THEN @@result += Row(b, c.id, d.id) END
END;
PRINT @@result AS result;
}
Q17. Friend recommendation
For two persons who share an interest in a tag but are not yet connected by KNOWS, count their mutual friends and rank by that count. This is the common-neighbour join T3.5 describes, combined with a negative pattern (NOT (person1)-[:KNOWS]-(person2)).
CREATE OR REPLACE DISTRIBUTED QUERY q17_friend_recommendation(STRING p_tag = "Thomas_More")
FOR GRAPH snb100 SYNTAX V1 {
TYPEDEF TUPLE <UINT person1Id, UINT person2Id, UINT mutualFriendCount> Row;
HeapAccum<Row>(20, mutualFriendCount DESC, person1Id ASC, person2Id ASC) @@result;
OrAccum @interested;
SetAccum<UINT> @interestedFriends;
MapAccum<UINT, SumAccum<UINT>> @mutualFriendCount;
allTags = {Tag.*};
tag = SELECT t FROM allTags:t WHERE t.name == p_tag;
person1 = SELECT p FROM tag:t -(HAS_INTEREST_REVERSE)-> Person:p ACCUM p.@interested += TRUE;
mutual = SELECT m FROM person1:p1 -((KNOWS|KNOWS_REVERSE):e)-> Person:m ACCUM m.@interestedFriends += p1.id;
person2 = SELECT p2 FROM mutual:m -((KNOWS|KNOWS_REVERSE):e)-> Person:p2
WHERE p2.@interested
ACCUM
FOREACH id1 IN m.@interestedFriends DO
IF id1 != p2.id AND NOT p2.@interestedFriends.contains(id1) THEN
p2.@mutualFriendCount += (id1 -> 1)
END
END
POST-ACCUM
FOREACH (id1, n) IN p2.@mutualFriendCount DO
@@result += Row(id1, p2.id, n)
END;
PRINT @@result AS result;
}
Q18. Popular topics in a country
A long chain running Country → City → Person → Forum → Post → Message → Tag → TagClass, with a variable-length REPLY_OF*0.. segment walking the reply tree in the middle, counting the messages per forum that hit a given TagClass.
CREATE OR REPLACE DISTRIBUTED QUERY q18_popular_topics_in_country(STRING p_tag_class = "Saint",
STRING p_country = "India")
FOR GRAPH snb100 SYNTAX V1 {
TYPEDEF TUPLE <UINT forumId, STRING forumTitle, INT forumCreationDate, UINT personId, UINT messageCount> Row;
HeapAccum<Row>(20, messageCount DESC, forumId ASC) @@result;
MinAccum<UINT> @moderatorId;
MinAccum<VERTEX<Forum>> @forum;
OrAccum @tagged;
MapAccum<VERTEX<Forum>, SumAccum<UINT>> @@messageCount;
SumAccum<UINT> @messageCount;
countries = {Country.*};
country = SELECT c FROM countries:c WHERE c.name == p_country;
cities = SELECT ci FROM country:c -(IS_PART_OF_REVERSE)-> City:ci;
persons = SELECT p FROM cities:ci -(IS_LOCATED_IN_REVERSE)-> Person:p;
forums = SELECT f FROM persons:p -(HAS_MODERATOR_REVERSE)-> Forum:f ACCUM f.@moderatorId += p.id;
tagClasses = {TagClass.*};
tagClass = SELECT tc FROM tagClasses:tc WHERE tc.name == p_tag_class;
allTags = SELECT t FROM tagClass:tc -(HAS_TYPE_REVERSE)-> Tag:t;
taggedMessages = SELECT m FROM allTags:t -(HAS_TAG_REVERSE)-> (Comment|Post):m ACCUM m.@tagged += TRUE;
frontier (ANY) = SELECT p FROM forums:f -(CONTAINER_OF)-> Post:p ACCUM p.@forum += f;
WHILE frontier.size() > 0 DO
tagHits = SELECT m FROM frontier:m WHERE m.@tagged ACCUM @@messageCount += (m.@forum -> 1);
frontier = SELECT c FROM frontier:m -(REPLY_OF_REVERSE)-> Comment:c ACCUM c.@forum += m.@forum;
END;
forumRows = SELECT f FROM forums:f
POST-ACCUM
f.@messageCount = @@messageCount.get(f),
IF f.@messageCount > 0 THEN
@@result += Row(f.id, f.title, f.creationDate, f.@moderatorId, f.@messageCount)
END;
PRINT @@result AS result;
}
Q19. Related Topics
Follow a three-hop line from a tag through its messages and their replies to the other tags those replies carry, excluding replies that are themselves tagged with the starting tag. The exclusion is a negative pattern over the same three-hop shape.
CREATE OR REPLACE DISTRIBUTED QUERY q19_related_topics(STRING p_tag = "Jacques_Chirac")
FOR GRAPH snb100 SYNTAX V1 {
TYPEDEF TUPLE <STRING relatedTagName, UINT replyCount> Row;
HeapAccum<Row>(100, replyCount DESC, relatedTagName ASC) @@result;
OrAccum @hasTag;
SumAccum<UINT> @count;
allTags = {Tag.*};
tag = SELECT t FROM allTags:t WHERE t.name == p_tag;
messages = SELECT m FROM tag:t -(HAS_TAG_REVERSE)-> (Comment|Post):m ACCUM m.@hasTag += TRUE;
replies = SELECT c FROM messages:m -(REPLY_OF_REVERSE)-> Comment:c;
replies = SELECT c FROM replies:c WHERE NOT c.@hasTag;
related = SELECT t FROM replies:c -(HAS_TAG)-> Tag:t
ACCUM t.@count += 1
POST-ACCUM @@result += Row(t.name, t.@count);
PRINT @@result AS result;
}
Q20. International dialog
For friend pairs split across two countries, score their interaction: a reply scores 4 or 1 and a like scores 10 or 1. Each of the four cases is a line running person1 → message → message → person2 or person1 → message → person2, accumulated as sets of person ids on person1. Only the highest-scoring pair per city is kept, using a one-element heap on each city.
CREATE OR REPLACE DISTRIBUTED QUERY q20_international_dialog(STRING p_country1 = "India",
STRING p_country2 = "Netherlands")
FOR GRAPH snb100 SYNTAX V1 {
TYPEDEF TUPLE <UINT person1Id, UINT person2Id, STRING city1Name, INT score> Row;
TYPEDEF TUPLE <INT score, UINT person2Id> Best;
HeapAccum<Row>(100, score DESC, person1Id ASC, person2Id ASC) @@result;
HeapAccum<Row>(1, score DESC, person1Id ASC, person2Id ASC) @cityTop;
HeapAccum<Best>(1, score DESC, person2Id ASC) @best;
OrAccum @in1, @in2;
SetAccum<UINT> @friends2;
SetAccum<UINT> @s1, @s2, @s3, @s4;
MinAccum<UINT> @creatorId, @parentCreatorId;
OrAccum @creatorIn1, @creatorIn2, @parentCreatorIn1, @parentCreatorIn2;
SetAccum<UINT> @likers2;
SetAccum<UINT> @repliers2;
countries = {Country.*};
c1 = SELECT c FROM countries:c WHERE c.name == p_country1;
c2 = SELECT c FROM countries:c WHERE c.name == p_country2;
cities1 = SELECT ci FROM c1:c -(IS_PART_OF_REVERSE)-> City:ci;
cities2 = SELECT ci FROM c2:c -(IS_PART_OF_REVERSE)-> City:ci;
P1all = SELECT p FROM cities1:ci -(IS_LOCATED_IN_REVERSE)-> Person:p ACCUM p.@in1 += TRUE;
P2 = SELECT p FROM cities2:ci -(IS_LOCATED_IN_REVERSE)-> Person:p ACCUM p.@in2 += TRUE;
P1 = SELECT p1 FROM P2:p2 -((KNOWS|KNOWS_REVERSE):e)-> Person:p1
WHERE p1.@in1
ACCUM p1.@friends2 += p2.id;
people = P1all UNION P2;
M = SELECT m FROM people:p -(HAS_CREATOR_REVERSE)-> (Comment|Post):m
ACCUM m.@creatorId += p.id, m.@creatorIn1 += p.@in1, m.@creatorIn2 += p.@in2;
replies = SELECT parent FROM M:c -(REPLY_OF)-> (Comment|Post):parent
WHERE parent.@creatorIn1 OR parent.@creatorIn2
ACCUM
c.@parentCreatorId += parent.@creatorId,
c.@parentCreatorIn2 += parent.@creatorIn2,
IF c.@creatorIn2 AND parent.@creatorIn1 THEN parent.@repliers2 += c.@creatorId END;
tmp = SELECT p FROM M:c -(HAS_CREATOR)-> Person:p
WHERE p.@in1 AND c.@parentCreatorIn2
ACCUM p.@s1 += c.@parentCreatorId;
tmp = SELECT p FROM M:m -(HAS_CREATOR)-> Person:p
WHERE p.@in1
ACCUM p.@s2 += m.@repliers2;
tmp = SELECT m FROM P1:p -(LIKES)-> (Comment|Post):m
WHERE m.@creatorIn2
ACCUM p.@s3 += m.@creatorId;
tmp = SELECT m FROM P2:p -(LIKES)-> (Comment|Post):m
WHERE m.@creatorIn1
ACCUM m.@likers2 += p.id;
tmp = SELECT p FROM M:m -(HAS_CREATOR)-> Person:p
WHERE p.@in1
ACCUM p.@s4 += m.@likers2;
P1 = SELECT p1 FROM P1:p1
POST-ACCUM
FOREACH p2 IN p1.@friends2 DO
INT score = 0,
IF p1.@s1.contains(p2) THEN score = score + 4 END,
IF p1.@s2.contains(p2) THEN score = score + 1 END,
IF p1.@s3.contains(p2) THEN score = score + 10 END,
IF p1.@s4.contains(p2) THEN score = score + 1 END,
p1.@best += Best(score, p2)
END;
cities = SELECT ci FROM P1:p1 -(IS_LOCATED_IN)-> City:ci
ACCUM ci.@cityTop += Row(p1.id, p1.@best.top().person2Id, ci.name, p1.@best.top().score)
POST-ACCUM @@result += ci.@cityTop.top();
PRINT @@result AS result;
}
Q21. Most active Posters of a given Topic
For each person posting about a tag, aggregate three per-node degrees — messages created, replies received and likes received — counted on each message and then summed onto its creator, and rank by 1×messages + 2×replies + 10×likes.
CREATE OR REPLACE DISTRIBUTED QUERY q21_most_active_posters(STRING p_tag = "Republic_of_New_Granada")
FOR GRAPH snb100 SYNTAX V1 {
TYPEDEF TUPLE <UINT personId, UINT replyCount, UINT likeCount, UINT messageCount, UINT score> Row;
HeapAccum<Row>(100, score DESC, personId ASC) @@result;
SumAccum<UINT> @likeCount, @replyCount, @messageCount;
allTags = {Tag.*};
tag = SELECT t FROM allTags:t WHERE t.name == p_tag;
messages = SELECT m FROM tag:t -(HAS_TAG_REVERSE)-> (Comment|Post):m;
tmp = SELECT m FROM messages:m -(LIKES_REVERSE)-> Person:p ACCUM m.@likeCount += 1;
tmp = SELECT m FROM messages:m -(REPLY_OF_REVERSE)-> Comment:c ACCUM m.@replyCount += 1;
tmp = SELECT p FROM messages:m -(HAS_CREATOR)-> Person:p
ACCUM p.@replyCount += m.@replyCount, p.@likeCount += m.@likeCount, p.@messageCount += 1
POST-ACCUM @@result += Row(p.id, p.@replyCount, p.@likeCount, p.@messageCount,
p.@messageCount + 2 * p.@replyCount + 10 * p.@likeCount);
PRINT @@result AS result;
}
Q22. How many persons have a given number of posts
Compute the distribution of how many persons created N qualifying messages, where a message must have content, be shorter than a threshold, be newer than a start date, and belong to a post in one of the given languages. This is a per-person degree histogram over HAS_CREATOR.
CREATE OR REPLACE DISTRIBUTED QUERY q22_message_count_distribution(
INT p_start = 1337904000000,
INT p_length_threshold = 135,
STRING p_language1 = "es", STRING p_language2 = "pt", STRING p_language3 = "zh")
FOR GRAPH snb100 SYNTAX V1 {
TYPEDEF TUPLE <UINT messageCount, UINT personCount> Row;
HeapAccum<Row>(1, personCount DESC, messageCount DESC) @@result;
MapAccum<UINT, SumAccum<UINT>> @@personCount;
SumAccum<UINT> @messageCount;
INT zero;
posts = {Post.*};
frontier (ANY) = SELECT p FROM posts:p
WHERE p.language == p_language1 OR p.language == p_language2 OR p.language == p_language3;
messages (ANY) = frontier;
WHILE frontier.size() > 0 DO
frontier = SELECT c FROM frontier:m -(REPLY_OF_REVERSE)-> Comment:c;
messages = messages UNION frontier;
END;
messages = SELECT m FROM messages:m
WHERE m.content != "" AND m.length < p_length_threshold AND m.creationDate > p_start;
counted = SELECT p FROM messages:m -(HAS_CREATOR)-> Person:p
ACCUM p.@messageCount += 1
POST-ACCUM @@personCount += (p.@messageCount -> 1);
persons = {Person.*};
zero = persons.size() - counted.size();
IF zero > 0 THEN @@personCount += (0 -> zero); END;
@@result.resize(@@personCount.size());
FOREACH (n, cnt) IN @@personCount DO
@@result += Row(n, cnt);
END;
PRINT @@result AS result;
}
Q23. Enrolment-year statistics over all study relationships
Count every STUDY_AT relationship and summarise its numeric classYear property with a global average, minimum and maximum. No grouping and no node properties are involved, so it isolates relationship scanning and aggregation.
CREATE OR REPLACE DISTRIBUTED QUERY q23_study_edge_statistics()
FOR GRAPH snb100 SYNTAX V1 {
SumAccum<INT> @@edgeCount, @@sum;
MinAccum<INT> @@minClassYear;
MaxAccum<INT> @@maxClassYear;
persons = {Person.*};
tmp = SELECT u FROM persons:p -(STUDY_AT:e)-> University:u
ACCUM @@edgeCount += 1, @@sum += e.classYear,
@@minClassYear += e.classYear, @@maxClassYear += e.classYear;
PRINT @@edgeCount AS edgeCount, @@sum * 1.0 / @@edgeCount AS avgClassYear,
@@minClassYear AS minClassYear, @@maxClassYear AS maxClassYear;
}
Q24. Enrolment-year statistics by university and gender
The grouped counterpart of the previous query: the same classYear statistics, grouped by a property of the target node (the university name) and one of the source node (the person’s gender), ordered by edge count. It measures multidimensional grouping and top-K materialisation.
CREATE OR REPLACE DISTRIBUTED QUERY q24_study_edge_statistics_by_group(INT p_limit = 100)
FOR GRAPH snb100 SYNTAX V1 {
TYPEDEF TUPLE <STRING universityName, STRING gender, INT edgeCount, DOUBLE avgClassYear,
INT minClassYear, INT maxClassYear> Row;
HeapAccum<Row>(100, edgeCount DESC, universityName ASC, gender ASC) @@result;
GroupByAccum<STRING universityName, STRING gender, SumAccum<INT> edgeCount, SumAccum<INT> sumClassYear,
MinAccum<INT> minClassYear, MaxAccum<INT> maxClassYear> @@groups;
@@result.resize(p_limit);
persons = {Person.*};
tmp = SELECT u FROM persons:p -(STUDY_AT:e)-> University:u
ACCUM @@groups += (u.name, p.gender -> 1, e.classYear, e.classYear, e.classYear);
FOREACH g IN @@groups DO
@@result += Row(g.universityName, g.gender, g.edgeCount, g.sumClassYear * 1.0 / g.edgeCount,
g.minClassYear, g.maxClassYear);
END;
PRINT @@result AS result;
}
Q25. Friendship path counts by length and gender reached
Enumerate KNOWS paths up to three hops from one person and report, for each combination of path length and the gender of the person reached, how many paths there are and how many distinct persons they reach. Grouping by an endpoint property on top of path length is what separates this from a plain path-count query.
CREATE OR REPLACE DISTRIBUTED QUERY q25_path_counts_by_length_and_gender(UINT p_person_id = 100316,
INT p_limit = 100)
FOR GRAPH snb100 SYNTAX V1 {
TYPEDEF TUPLE <INT pathLength, STRING gender, INT pathCount, INT distinctPersonCount> Row;
HeapAccum<Row>(100, pathLength ASC, pathCount DESC) @@result;
GroupByAccum<INT pathLength, STRING gender, SumAccum<INT> pathCount, SumAccum<INT> persons> @@groups;
OrAccum @isStart, @nearStart;
SumAccum<INT> @paths2, @paths3;
@@result.resize(p_limit);
persons = {Person.*};
start = SELECT s FROM persons:s WHERE s.id == p_person_id POST-ACCUM s.@isStart = TRUE;
hop1 = SELECT t FROM start:s -((KNOWS|KNOWS_REVERSE):e)-> Person:t
POST-ACCUM t.@nearStart = TRUE, @@groups += (1, t.gender -> 1, 1);
hop2 = SELECT c FROM hop1:b -((KNOWS|KNOWS_REVERSE):e)-> Person:c
WHERE NOT c.@isStart
ACCUM c.@paths2 += 1
POST-ACCUM @@groups += (2, c.gender -> c.@paths2, 1);
hop3 = SELECT n FROM hop2:c -((KNOWS|KNOWS_REVERSE):e)-> Person:n
ACCUM
INT k = c.@paths2,
IF n.@nearStart THEN k = k - 1 END,
n.@paths3 += k
POST-ACCUM IF n.@paths3 > 0 THEN @@groups += (3, n.gender -> n.@paths3, 1) END;
FOREACH g IN @@groups DO
@@result += Row(g.pathLength, g.gender, g.pathCount, g.persons);
END;
PRINT @@result AS result;
}
Q26. Persons interested in both of two given tags
Return the persons who have a HAS_INTEREST edge to each of two named tags: the intersection of the two sets of interested persons.
CREATE OR REPLACE DISTRIBUTED QUERY q26_persons_interested_in_both_tags(STRING p_tag_a = "Thomas_More",
STRING p_tag_b = "Michelangelo", INT p_limit = 100)
FOR GRAPH snb100 SYNTAX V1 {
TYPEDEF TUPLE <UINT personId, STRING firstName, STRING lastName> Row;
HeapAccum<Row>(100, personId ASC) @@result;
@@result.resize(p_limit);
allTags = {Tag.*};
tagA = SELECT t FROM allTags:t WHERE t.name == p_tag_a;
tagB = SELECT t FROM allTags:t WHERE t.name == p_tag_b;
personsA = SELECT p FROM tagA:t -(HAS_INTEREST_REVERSE)-> Person:p;
personsB = SELECT p FROM tagB:t -(HAS_INTEREST_REVERSE)-> Person:p;
inBoth = personsA INTERSECT personsB;
inBoth = SELECT p FROM inBoth:p ACCUM @@result += Row(p.id, p.firstName, p.lastName);
PRINT @@result AS result;
}
Q27. Degree centrality over the friendship graph
Rank persons by how many friends they have on the undirected KNOWS graph. It is the cheapest of the analytics queries — a single pass over the adjacency structure with no iteration — and gives a baseline against which PageRank’s cost can be read.
CREATE OR REPLACE DISTRIBUTED QUERY q27_degree_centrality()
FOR GRAPH snb100 SYNTAX V1 {
TYPEDEF TUPLE <UINT personId, STRING firstName, STRING lastName, INT degree> Row;
HeapAccum<Row>(10, degree DESC) @@result;
SumAccum<INT> @degree;
persons = {Person.*};
tmp = SELECT p FROM persons:p -((KNOWS|KNOWS_REVERSE):e)-> Person:t
ACCUM p.@degree += 1
POST-ACCUM @@result += Row(p.id, p.firstName, p.lastName, p.@degree);
PRINT @@result AS result;
}
Q28. PageRank over the friendship graph
Rank persons by PageRank on the undirected KNOWS graph to find the most structurally influential accounts. The algorithm iterates until the scores converge or the iteration limit is reached.
CREATE OR REPLACE DISTRIBUTED QUERY q28_pagerank(INT p_max_iterations = 20, DOUBLE p_damping = 0.85)
FOR GRAPH snb100 SYNTAX V1 {
TYPEDEF TUPLE <UINT personId, STRING firstName, STRING lastName, DOUBLE pagerank> Row;
HeapAccum<Row>(10, pagerank DESC) @@result;
SumAccum<DOUBLE> @rank, @nextRank;
SumAccum<INT> @degree;
INT i = 0;
persons = {Person.*};
tmp = SELECT p FROM persons:p -((KNOWS|KNOWS_REVERSE):e)-> Person:t ACCUM p.@degree += 1;
tmp = SELECT p FROM persons:p POST-ACCUM p.@rank = 1.0;
WHILE i < p_max_iterations DO
tmp = SELECT t FROM persons:s -((KNOWS|KNOWS_REVERSE):e)-> Person:t
WHERE s.@degree > 0
ACCUM t.@nextRank += s.@rank / s.@degree;
tmp = SELECT p FROM persons:p
POST-ACCUM p.@rank = (1.0 - p_damping) + p_damping * p.@nextRank, p.@nextRank = 0.0;
i = i + 1;
END;
tmp = SELECT p FROM persons:p POST-ACCUM @@result += Row(p.id, p.firstName, p.lastName, p.@rank);
PRINT @@result AS result;
}
Q29. Weakly connected components of the friendship graph
Find the connected regions of the undirected KNOWS graph and report the largest by size. On a generated social network almost every person falls in one giant component, so the result also shows how many isolated accounts the generator produced.
CREATE OR REPLACE DISTRIBUTED QUERY q29_weakly_connected_components(INT p_max_iterations = 1000)
FOR GRAPH snb100 SYNTAX V1 {
TYPEDEF TUPLE <UINT componentId, INT componentSize> Row;
HeapAccum<Row>(10, componentSize DESC) @@result;
MinAccum<UINT> @component;
MapAccum<UINT, SumAccum<INT>> @@sizes;
INT i = 0;
persons = {Person.*};
changed = SELECT p FROM persons:p POST-ACCUM p.@component = p.id;
WHILE changed.size() > 0 AND i < p_max_iterations DO
changed = SELECT t FROM changed:s -((KNOWS|KNOWS_REVERSE):e)-> Person:t
WHERE s.@component < t.@component
ACCUM t.@component += s.@component;
i = i + 1;
END;
tmp = SELECT p FROM persons:p POST-ACCUM @@sizes += (p.@component -> 1);
FOREACH (c, n) IN @@sizes DO
@@result += Row(c, n);
END;
PRINT @@result AS result;
}
Q30. Strongly connected components of the directed friendship graph
Find the maximal sets of persons that can all reach each other following KNOWS edges in their stored direction. Persons that cannot lie on a cycle are peeled off first, Kahn-style: a person with no remaining incoming edge (then, from the other end, no remaining outgoing edge) is a component of its own, and each round touches only the edges of the persons just removed, so every person and edge is processed once however deep the graph is. Whatever remains contains every cycle and is split by forward coloring and backward search. SNB stores each friendship once, as a single directed edge, so mutual reachability is rare and the components are expected to be mostly singletons.
CREATE OR REPLACE DISTRIBUTED QUERY q30_strongly_connected_components(INT p_max_rounds = 100000)
FOR GRAPH snb100 SYNTAX V1 {
TYPEDEF TUPLE <UINT componentId, INT componentSize> Row;
HeapAccum<Row>(10, componentSize DESC) @@result;
OrAccum @done;
SumAccum<INT> @inRemaining, @outRemaining;
MaxAccum<UINT> @color;
MinAccum<UINT> @component;
MapAccum<UINT, SumAccum<INT>> @@sizes;
INT rounds = 0;
persons = {Person.*};
tmp = SELECT t FROM persons:s -(KNOWS:e)-> Person:t ACCUM s.@outRemaining += 1, t.@inRemaining += 1;
frontier = SELECT v FROM persons:v WHERE v.@inRemaining == 0
POST-ACCUM v.@done = TRUE, v.@component = v.id;
WHILE frontier.size() > 0 AND rounds < p_max_rounds DO
frontier = SELECT t FROM frontier:s -(KNOWS:e)-> Person:t
WHERE NOT t.@done
ACCUM t.@inRemaining += -1
POST-ACCUM IF t.@inRemaining == 0 THEN t.@done = TRUE, t.@component = t.id END
HAVING t.@done;
rounds = rounds + 1;
END;
active = SELECT v FROM persons:v WHERE NOT v.@done POST-ACCUM v.@outRemaining = 0;
tmp = SELECT t FROM active:s -(KNOWS:e)-> Person:t WHERE NOT t.@done ACCUM s.@outRemaining += 1;
frontier = SELECT v FROM active:v WHERE v.@outRemaining == 0
POST-ACCUM v.@done = TRUE, v.@component = v.id;
WHILE frontier.size() > 0 AND rounds < p_max_rounds DO
frontier = SELECT t FROM frontier:s -(KNOWS_REVERSE:e)-> Person:t
WHERE NOT t.@done
ACCUM t.@outRemaining += -1
POST-ACCUM IF t.@outRemaining == 0 THEN t.@done = TRUE, t.@component = t.id END
HAVING t.@done;
rounds = rounds + 1;
END;
active = SELECT v FROM persons:v WHERE NOT v.@done;
WHILE active.size() > 0 AND rounds < p_max_rounds DO
changed = SELECT v FROM active:v POST-ACCUM v.@color = v.id;
WHILE changed.size() > 0 DO
changed = SELECT t FROM changed:s -(KNOWS:e)-> Person:t
WHERE NOT t.@done AND s.@color > t.@color
ACCUM t.@color += s.@color;
END;
roots = SELECT v FROM active:v
WHERE v.@color == v.id
POST-ACCUM v.@done = TRUE, v.@component = v.id;
while_frontier = roots;
WHILE while_frontier.size() > 0 DO
while_frontier = SELECT t FROM while_frontier:s -(KNOWS_REVERSE:e)-> Person:t
WHERE NOT t.@done AND t.@color == s.@color
POST-ACCUM t.@done = TRUE, t.@component = t.@color;
END;
active = SELECT v FROM active:v WHERE NOT v.@done;
rounds = rounds + 1;
END;
tmp = SELECT p FROM persons:p POST-ACCUM @@sizes += (p.@component -> 1);
FOREACH (c, n) IN @@sizes DO
@@result += Row(c, n);
END;
PRINT @@result AS result;
}
Q31. Louvain communities of the friendship graph
Detect communities in the undirected KNOWS graph with Louvain and return the largest ones. The query is a self-contained adaptation of TigerGraph’s tg_louvain from the GSQL graph algorithm library, with unit edge weights.
CREATE OR REPLACE DISTRIBUTED QUERY q31_louvain_communities(INT p_max_iterations = 10)
FOR GRAPH snb100 SYNTAX V1 {
TYPEDEF TUPLE <FLOAT delta_q, FLOAT community_weight, VERTEX center> Move;
TYPEDEF TUPLE <VERTEX communityId, INT communitySize> Row;
SumAccum<FLOAT> @community_degree_sum;
ListAccum<VERTEX> @community_center;
SumAccum<FLOAT> @weighted_degree;
SumAccum<FLOAT> @center_weight;
MapAccum<VERTEX, SumAccum<FLOAT>> @weight_by_neighbor_community;
MaxAccum<Move> @best_move;
ListAccum<VERTEX> @community_members;
SumAccum<INT> @degree;
SumAccum<FLOAT> @@total_edge_weight;
SumAccum<INT> @@changed;
MapAccum<VERTEX, SumAccum<INT>> @@community_sizes;
HeapAccum<Row>(10, communitySize DESC) @@result;
vertices = {Person.*};
initialized = SELECT s
FROM vertices:s -((KNOWS|KNOWS_REVERSE):e)-> Person:t
ACCUM @@total_edge_weight += 0.5,
s.@weighted_degree += 1.0,
s.@center_weight += 1.0,
s.@degree += 1
POST-ACCUM
IF s.@degree > 1 THEN
s.@community_center += s
END;
first_move = SELECT t
FROM vertices:s -((KNOWS|KNOWS_REVERSE):e)-> Person:t
WHERE s.@degree > 1 AND t.@degree > 1
ACCUM
t.@best_move += Move(
1.0 + @@total_edge_weight * t.@weighted_degree *
(t.@weighted_degree - s.@weighted_degree),
-s.@center_weight,
s.@community_center.get(0)
)
POST-ACCUM
IF t.@best_move.delta_q > 0 THEN
IF -t.@best_move.community_weight < t.@center_weight THEN
t.@community_center.clear(),
t.@community_center += t.@best_move.center,
t.@center_weight = -t.@best_move.community_weight,
@@changed += 1
ELSE IF -t.@best_move.community_weight == t.@center_weight
AND getvid(t) < getvid(t.@best_move.center) THEN
t.@community_center.clear(),
t.@community_center += t.@best_move.center,
t.@center_weight = -t.@best_move.community_weight,
@@changed += 1
END
END;
WHILE @@changed > 0 LIMIT p_max_iterations DO
@@changed = 0;
reset_iteration = SELECT s FROM vertices:s
WHERE s.@degree > 1
POST-ACCUM s.@community_degree_sum = 0,
s.@community_members.clear(),
s.@weight_by_neighbor_community.clear();
build_member_lists = SELECT s FROM vertices:s
ACCUM
FOREACH center IN s.@community_center DO
IF getvid(center) != -1 THEN
center.@community_members += s
END
END;
collect_neighbor_communities = SELECT s
FROM vertices:s -((KNOWS|KNOWS_REVERSE):e)-> Person:t
WHERE t.@degree > 1
ACCUM s.@weight_by_neighbor_community += (t.@community_center.get(0) -> 1.0);
sum_community_degrees = SELECT s FROM vertices:s
ACCUM
FOREACH center IN s.@community_center DO
IF getvid(center) != -1 THEN
center.@community_degree_sum += s.@weighted_degree
END
END;
copy_community_degrees = SELECT s FROM vertices:s
ACCUM
FOREACH member IN s.@community_members DO
IF getvid(member) != -1 THEN
member.@community_degree_sum = s.@community_degree_sum
END
END;
choose_best_move = SELECT s
FROM vertices:s -((KNOWS|KNOWS_REVERSE):e)-> Person:t
WHERE t.@degree > 1
ACCUM
FLOAT internal_weight = 0.0,
IF s.@weight_by_neighbor_community.containsKey(s) THEN
internal_weight = s.@weight_by_neighbor_community.get(s)
END,
s.@best_move += Move(
s.@weight_by_neighbor_community.get(t.@community_center.get(0))
- internal_weight
+ (1.0 / @@total_edge_weight) * s.@weighted_degree
* (s.@community_degree_sum - t.@community_degree_sum),
-t.@center_weight,
t.@community_center.get(0)
)
POST-ACCUM
IF s.@best_move.delta_q > 0 THEN
IF -s.@best_move.community_weight < s.@center_weight THEN
s.@community_center.clear(),
s.@community_center += s.@best_move.center,
s.@center_weight = -s.@best_move.community_weight,
@@changed += 1
ELSE IF -s.@best_move.community_weight == s.@center_weight
AND getvid(s.@community_center.get(0)) < getvid(s.@best_move.center) THEN
s.@community_center.clear(),
s.@community_center += s.@best_move.center,
s.@center_weight = -s.@best_move.community_weight,
@@changed += 1
END
END;
END;
degree_one_follow = SELECT s
FROM vertices:s -((KNOWS|KNOWS_REVERSE):e)-> Person:t
WHERE s.@degree == 1 AND t.@degree != 1
ACCUM s.@community_center += t.@community_center.get(0);
degree_one_pairs = SELECT s
FROM vertices:s -((KNOWS|KNOWS_REVERSE):e)-> Person:t
WHERE s.@degree == 1 AND t.@degree == 1
ACCUM
IF getvid(s) <= getvid(t) THEN
s.@community_center += s
ELSE
s.@community_center += t
END;
isolated = SELECT s FROM vertices:s
WHERE s.@degree == 0
ACCUM s.@community_center += s;
counted = SELECT s FROM vertices:s
WHERE s.@community_center.size() > 0
POST-ACCUM @@community_sizes += (s.@community_center.get(0) -> 1);
FOREACH (center, n) IN @@community_sizes DO
@@result += Row(center, n);
END;
PRINT @@result AS result;
}
Q32. Top thread initiators
Count the posts each person started within a window, together with the messages in the whole reply tree beneath them (REPLY_OF*0..) that also fall in the window. It combines two workloads: the bounded-length path enumeration of T4.5, and the ungrouped per-node count of T4.1 applied to the thread initiator, since the result is a count of distinct posts and distinct replies per person rather than a breakdown by path length.
CREATE OR REPLACE DISTRIBUTED QUERY q32_top_thread_initiators(
INT p_start = 1346803200000,
INT p_end = 1354579200000)
FOR GRAPH snb100 SYNTAX V1 {
TYPEDEF TUPLE <UINT personId, STRING firstName, STRING lastName, UINT threadCount, UINT messageCount> Row;
HeapAccum<Row>(100, messageCount DESC, personId ASC) @@result;
MinAccum<VERTEX<Post>> @root;
MapAccum<VERTEX<Post>, SumAccum<UINT>> @@replies;
SumAccum<UINT> @threadCount, @messageCount;
posts = {Post.*};
threads = SELECT p FROM posts:p
WHERE p.creationDate >= p_start AND p.creationDate <= p_end
ACCUM p.@root += p;
frontier (ANY) = threads;
WHILE frontier.size() > 0 DO
frontier = SELECT c FROM frontier:m -(REPLY_OF_REVERSE)-> Comment:c
ACCUM c.@root += m.@root
POST-ACCUM IF c.creationDate >= p_start AND c.creationDate <= p_end THEN
@@replies += (c.@root -> 1)
END;
END;
persons = SELECT p FROM threads:t -(HAS_CREATOR)-> Person:p
ACCUM p.@threadCount += 1, p.@messageCount += 1 + @@replies.get(t)
POST-ACCUM @@result += Row(p.id, p.firstName, p.lastName, p.@threadCount, p.@messageCount);
PRINT @@result AS result;
}
Q33. Posting summary
Build a histogram over every message created before a given instant, grouped by year, by whether the message is a comment, and by length bucket (<40, <80, <160, rest), reporting count, average length, total length and share of all messages. It does not match a defined type because it summarises a node property (length) with no traversal at all, whereas the aggregation types cover degree statistics, edge-property statistics and path counts.
CREATE OR REPLACE DISTRIBUTED QUERY q33_posting_summary(
INT p_datetime = 1280664768387)
FOR GRAPH snb100 SYNTAX V1 {
TYPEDEF TUPLE <INT year, BOOL isComment, INT lengthCategory, INT messageCount,
DOUBLE averageMessageLength, INT sumMessageLength, DOUBLE percentageOfMessages> Row;
HeapAccum<Row>(1, year DESC, isComment ASC, lengthCategory ASC) @@result;
GroupByAccum<INT year, BOOL isComment, INT lengthCategory,
SumAccum<INT> messageCount, SumAccum<INT> sumMessageLength> @@groups;
UINT total;
comments = {Comment.*};
posts = {Post.*};
messages = comments UNION posts;
messages = SELECT m FROM messages:m WHERE m.creationDate < p_datetime;
total = messages.size();
tmp = SELECT m FROM messages:m
WHERE m.content != ""
POST-ACCUM
INT category = 3,
BOOL isComment = (m.type == "Comment"),
IF m.length < 40 THEN category = 0
ELSE IF m.length < 80 THEN category = 1
ELSE IF m.length < 160 THEN category = 2
END,
@@groups += (year(epoch_to_datetime(m.creationDate / 1000)), isComment, category -> 1, m.length);
@@result.resize(@@groups.size());
FOREACH g IN @@groups DO
@@result += Row(g.year, g.isComment, g.lengthCategory, g.messageCount,
g.sumMessageLength * 1.0 / g.messageCount, g.sumMessageLength,
g.messageCount * 1.0 / total);
END;
PRINT @@result AS result;
}
Q34. Tag evolution
For every tag of a given TagClass, compare how many messages referenced it in two adjacent 100-day windows and rank by the difference. The distinguishing work is the comparison of two independent time windows and the arithmetic between them, which no defined type covers.
CREATE OR REPLACE DISTRIBUTED QUERY q34_tag_evolution(
INT p_date = 1276905600000,
STRING p_tag_class = "SoccerPlayer")
FOR GRAPH snb100 SYNTAX V1 {
TYPEDEF TUPLE <STRING tagName, INT countWindow1, INT countWindow2, INT diff> Row;
HeapAccum<Row>(100, diff DESC, tagName ASC) @@result;
SumAccum<INT> @countWindow1, @countWindow2;
INT end1 = p_date + 100 * 86400000;
INT end2 = p_date + 200 * 86400000;
tagClasses = {TagClass.*};
tagClass = SELECT tc FROM tagClasses:tc WHERE tc.name == p_tag_class;
allTags = SELECT t FROM tagClass:tc -(HAS_TYPE_REVERSE)-> Tag:t;
tmp = SELECT m FROM allTags:t -(HAS_TAG_REVERSE)-> (Comment|Post):m
WHERE m.creationDate >= p_date AND m.creationDate < end2
ACCUM IF m.creationDate < end1 THEN t.@countWindow1 += 1 ELSE t.@countWindow2 += 1 END;
allTags = SELECT t FROM allTags:t
POST-ACCUM @@result += Row(t.name, t.@countWindow1, t.@countWindow2,
abs(t.@countWindow1 - t.@countWindow2));
PRINT @@result AS result;
}
Q35. Top message creators in a country
First pick the 100 forums with the most members, then aggregate the message output of those forums’ members, using UNION ALL to keep members with zero messages in the result. It is a two-phase query whose second phase consumes the top-K output of the first, so neither phase alone characterises it.
CREATE OR REPLACE DISTRIBUTED QUERY q35_top_message_creators(
INT p_date = 1264723200000)
FOR GRAPH snb100 SYNTAX V1 {
TYPEDEF TUPLE <UINT personId, STRING personFirstName, STRING personLastName,
INT personCreationDate, UINT messageCount> Row;
HeapAccum<Row>(100, messageCount DESC, personId ASC) @@result;
MinAccum<UINT> @countryId;
MapAccum<UINT, SumAccum<UINT>> @membersByCountry;
MaxAccum<UINT> @maxMember;
OrAccum @inTopForum;
SumAccum<UINT> @messageCount;
countries = {Country.*};
cities = SELECT ci FROM countries:c -(IS_PART_OF_REVERSE)-> City:ci ACCUM ci.@countryId += c.id;
tmp = SELECT p FROM cities:ci -(IS_LOCATED_IN_REVERSE)-> Person:p ACCUM p.@countryId += ci.@countryId;
forums = {Forum.*};
forums = SELECT f FROM forums:f WHERE f.creationDate > p_date;
tmp = SELECT f FROM forums:f -(HAS_MEMBER)-> Person:p
ACCUM f.@membersByCountry += (p.@countryId -> 1)
POST-ACCUM
FOREACH (c, n) IN f.@membersByCountry DO f.@maxMember += n END,
f.@membersByCountry.clear();
topForums = SELECT f FROM forums:f ORDER BY f.@maxMember DESC, f.id ASC LIMIT 100;
members = SELECT p FROM topForums:f -(HAS_MEMBER)-> Person:p ACCUM p.@inTopForum += TRUE;
frontier (ANY) = SELECT p FROM topForums:f -(CONTAINER_OF)-> Post:p;
messages (ANY) = frontier;
WHILE frontier.size() > 0 DO
frontier = SELECT c FROM frontier:m -(REPLY_OF_REVERSE)-> Comment:c;
messages = messages UNION frontier;
END;
tmp = SELECT p FROM messages:m -(HAS_CREATOR)-> Person:p
WHERE p.@inTopForum
ACCUM p.@messageCount += 1;
members = SELECT p FROM members:p
POST-ACCUM @@result += Row(p.id, p.firstName, p.lastName, p.creationDate, p.@messageCount);
PRINT @@result AS result;
}
Q36. Most authoritative users on a given topic
A two-level authority score: person1’s score comes from the person2 who liked their message, and person2’s weight in turn depends on how many likes person2’s own messages received. count(DISTINCT like) ensures each person2’s popularity is added only once per person1. It propagates score through the graph like PageRank but at a fixed depth of two rather than iterating to convergence, so it is neither a degree statistic nor an iterative algorithm.
CREATE OR REPLACE DISTRIBUTED QUERY q36_most_authoritative_users(STRING p_tag = "All_by_Myself")
FOR GRAPH snb100 SYNTAX V1 {
TYPEDEF TUPLE <UINT personId, UINT authorityScore> Row;
HeapAccum<Row>(100, authorityScore DESC, personId ASC) @@result;
SumAccum<UINT> @likeCount, @popularity, @authorityScore;
MapAccum<UINT, MaxAccum<UINT>> @popularityByLiker;
allTags = {Tag.*};
tag = SELECT t FROM allTags:t WHERE t.name == p_tag;
message1 = SELECT m FROM tag:t -(HAS_TAG_REVERSE)-> (Comment|Post):m;
person2 = SELECT p FROM message1:m -(LIKES_REVERSE)-> Person:p;
messages2 = SELECT m FROM person2:p -(HAS_CREATOR_REVERSE)-> (Comment|Post):m;
tmp = SELECT m FROM messages2:m -(LIKES_REVERSE)-> Person:x ACCUM m.@likeCount += 1;
tmp = SELECT p FROM messages2:m -(HAS_CREATOR)-> Person:p ACCUM p.@popularity += m.@likeCount;
tmp = SELECT m FROM message1:m -(LIKES_REVERSE)-> Person:p2
ACCUM m.@popularityByLiker += (p2.id -> p2.@popularity);
person1 = SELECT p1 FROM message1:m -(HAS_CREATOR)-> Person:p1
ACCUM p1.@popularityByLiker += m.@popularityByLiker
POST-ACCUM
FOREACH (p2, score) IN p1.@popularityByLiker DO p1.@authorityScore += score END,
@@result += Row(p1.id, p1.@authorityScore),
p1.@popularityByLiker.clear();
PRINT @@result AS result;
}
Q37. Central Person for a Tag
Score the followers and posters of a tag (100 points for an interest edge, 1 point per message in the window), then add up the same score across each person’s friends and return the highest combined total. The score is a weighted mix of two edge types that is then aggregated over a one-hop neighbourhood, which does not reduce to any single defined type.
CREATE OR REPLACE DISTRIBUTED QUERY q37_central_person_for_tag(STRING p_tag = "Vladimir_Putin",
INT p_start = 1276300800000,
INT p_end = 1277251200000)
FOR GRAPH snb100 SYNTAX V1 {
TYPEDEF TUPLE <UINT personId, UINT score, UINT friendsScore, UINT totalScore> Row;
HeapAccum<Row>(100, totalScore DESC, personId ASC) @@result;
SumAccum<UINT> @score, @friendsScore;
allTags = {Tag.*};
tag = SELECT t FROM allTags:t WHERE t.name == p_tag;
interested = SELECT p FROM tag:t -(HAS_INTEREST_REVERSE)-> Person:p ACCUM p.@score += 100;
messages = SELECT m FROM tag:t -(HAS_TAG_REVERSE)-> (Comment|Post):m
WHERE m.creationDate > p_start AND m.creationDate < p_end;
creators = SELECT p FROM messages:m -(HAS_CREATOR)-> Person:p ACCUM p.@score += 1;
scored = interested UNION creators;
tmp = SELECT p FROM scored:p -((KNOWS|KNOWS_REVERSE):e)-> Person:f ACCUM p.@friendsScore += f.@score;
scored = SELECT p FROM scored:p
POST-ACCUM @@result += Row(p.id, p.@score, p.@friendsScore, p.@score + p.@friendsScore);
PRINT @@result AS result;
}
Q38. Experts in social circle
Among the persons whose KNOWS distance from a start person falls in the ring [minPathDistance, maxPathDistance], find those who live in a given country and have posted about a given TagClass. A breadth-first search marks the persons first reached at distance 3 or 4, and the ring is then joined against a location pattern and a tag pattern.
CREATE OR REPLACE DISTRIBUTED QUERY q38_experts_in_social_circle(UINT p_person_id = 100316,
STRING p_country = "Australia", STRING p_tag_class = "NascarDriver",
INT p_min_path_distance = 3, INT p_max_path_distance = 4)
FOR GRAPH snb100 SYNTAX V1 {
TYPEDEF TUPLE <UINT personId, STRING tagName, UINT messageCount> Row;
HeapAccum<Row>(100, messageCount DESC, tagName ASC, personId ASC) @@result;
OrAccum @visited, @inRing, @isClassTag, @classTagged;
SetAccum<STRING> @tagNames;
MapAccum<STRING, SumAccum<UINT>> @messageCount;
INT hops = 0;
persons = {Person.*};
frontier = SELECT s FROM persons:s WHERE s.id == p_person_id POST-ACCUM s.@visited = TRUE;
WHILE hops < p_max_path_distance DO
hops = hops + 1;
frontier = SELECT t FROM frontier:s -((KNOWS|KNOWS_REVERSE):e)-> Person:t
WHERE NOT t.@visited
POST-ACCUM t.@visited = TRUE, IF hops >= p_min_path_distance THEN t.@inRing = TRUE END;
END;
countries = {Country.*};
country = SELECT c FROM countries:c WHERE c.name == p_country;
cities = SELECT ci FROM country:c -(IS_PART_OF_REVERSE)-> City:ci;
candidates = SELECT p FROM cities:ci -(IS_LOCATED_IN_REVERSE)-> Person:p
WHERE p.@inRing;
tagClasses = {TagClass.*};
tagClass = SELECT tc FROM tagClasses:tc WHERE tc.name == p_tag_class;
classTags = SELECT t FROM tagClass:tc -(HAS_TYPE_REVERSE)-> Tag:t POST-ACCUM t.@isClassTag = TRUE;
messages = SELECT m FROM candidates:p -(HAS_CREATOR_REVERSE)-> (Comment|Post):m;
messages = SELECT m FROM messages:m -(HAS_TAG)-> Tag:t
ACCUM m.@tagNames += t.name,
IF t.@isClassTag THEN m.@classTagged += TRUE END;
messages = SELECT m FROM messages:m WHERE m.@classTagged;
experts = SELECT p FROM messages:m -(HAS_CREATOR)-> Person:p
ACCUM FOREACH name IN m.@tagNames DO p.@messageCount += (name -> 1) END
POST-ACCUM FOREACH (name, n) IN p.@messageCount DO @@result += Row(p.id, name, n) END;
PRINT @@result AS result;
}
Q39. Zombies in a country
Select the zombie accounts of a country by a rate test (fewer than one message per month over the account’s lifetime), then compute for each zombie the share of its likes that came from other zombies. The query first derives a subpopulation and then measures a ratio within it, which no defined type describes.
CREATE OR REPLACE DISTRIBUTED QUERY q39_zombies_in_country(STRING p_country = "India",
INT p_end = 1352592000000)
FOR GRAPH snb100 SYNTAX V1 {
TYPEDEF TUPLE <UINT zombieId, UINT zombieLikeCount, UINT totalLikeCount, DOUBLE zombieScore> Row;
HeapAccum<Row>(100, zombieScore DESC, zombieId ASC) @@result;
OrAccum @zombie;
SumAccum<UINT> @messageCount, @totalLikeCount, @zombieLikeCount;
DATETIME endDate = epoch_to_datetime(p_end / 1000);
INT endYear = year(endDate);
INT endMonth = month(endDate);
countries = {Country.*};
country = SELECT c FROM countries:c WHERE c.name == p_country;
cities = SELECT ci FROM country:c -(IS_PART_OF_REVERSE)-> City:ci;
candidates = SELECT p FROM cities:ci -(IS_LOCATED_IN_REVERSE)-> Person:p WHERE p.creationDate < p_end;
tmp = SELECT p FROM candidates:p -(HAS_CREATOR_REVERSE)-> (Comment|Post):m
WHERE m.creationDate < p_end
ACCUM p.@messageCount += 1;
zombies = SELECT p FROM candidates:p
WHERE p.@messageCount < 12 * (endYear - year(epoch_to_datetime(p.creationDate / 1000)))
+ (endMonth - month(epoch_to_datetime(p.creationDate / 1000))) + 1
POST-ACCUM p.@zombie = TRUE;
messages = SELECT m FROM zombies:z -(HAS_CREATOR_REVERSE)-> (Comment|Post):m;
tmp = SELECT p FROM messages:m -(LIKES_REVERSE)-> Person:p
ACCUM
IF p.@zombie THEN m.@zombieLikeCount += 1 END,
IF p.creationDate < p_end THEN m.@totalLikeCount += 1 END;
tmp = SELECT p FROM messages:m -(HAS_CREATOR)-> Person:p
ACCUM p.@totalLikeCount += m.@totalLikeCount, p.@zombieLikeCount += m.@zombieLikeCount;
zombies = SELECT z FROM zombies:z
POST-ACCUM
IF z.@totalLikeCount > 0 THEN
@@result += Row(z.id, z.@zombieLikeCount, z.@totalLikeCount,
z.@zombieLikeCount * 1.0 / z.@totalLikeCount)
ELSE
@@result += Row(z.id, z.@zombieLikeCount, 0, 0.0)
END;
PRINT @@result AS result;
}
Q40. Fake news detection
For each of two (tag, date) combinations, find the persons who posted about that tag on that day and who have at most maxKnowsLimit friends doing the same, then keep only the persons present in both. The same computation runs twice and the two vertex sets are intersected.
CREATE OR REPLACE DISTRIBUTED QUERY q40_fake_news_detection(STRING p_tag_a = "Marc_Gicquel",
INT p_date_a = 1291248000000,
STRING p_tag_b = "Vladimir_Putin",
INT p_date_b = 1352764800000,
UINT p_max_knows_limit = 5)
FOR GRAPH snb100 SYNTAX V1 {
TYPEDEF TUPLE <UINT personId, UINT messageCountA, UINT messageCountB, UINT totalMessageCount> Row;
HeapAccum<Row>(20, totalMessageCount DESC, personId ASC) @@result;
SumAccum<UINT> @messageCountA, @messageCountB, @knowsCountA, @knowsCountB;
OrAccum @inA, @inB;
INT dayMs = 86400000;
allTags = {Tag.*};
tagA = SELECT t FROM allTags:t WHERE t.name == p_tag_a;
messagesA = SELECT m FROM tagA:t -(HAS_TAG_REVERSE)-> (Comment|Post):m
WHERE m.creationDate >= p_date_a AND m.creationDate < p_date_a + dayMs;
personsA = SELECT p FROM messagesA:m -(HAS_CREATOR)-> Person:p
ACCUM p.@messageCountA += 1 POST-ACCUM p.@inA = TRUE;
tmp = SELECT p FROM personsA:f -((KNOWS|KNOWS_REVERSE):e)-> Person:p
WHERE p.@inA
ACCUM p.@knowsCountA += 1;
personsA = SELECT p FROM personsA:p WHERE p.@knowsCountA <= p_max_knows_limit;
tagB = SELECT t FROM allTags:t WHERE t.name == p_tag_b;
messagesB = SELECT m FROM tagB:t -(HAS_TAG_REVERSE)-> (Comment|Post):m
WHERE m.creationDate >= p_date_b AND m.creationDate < p_date_b + dayMs;
personsB = SELECT p FROM messagesB:m -(HAS_CREATOR)-> Person:p
ACCUM p.@messageCountB += 1 POST-ACCUM p.@inB = TRUE;
tmp = SELECT p FROM personsB:f -((KNOWS|KNOWS_REVERSE):e)-> Person:p
WHERE p.@inB
ACCUM p.@knowsCountB += 1;
personsB = SELECT p FROM personsB:p WHERE p.@knowsCountB <= p_max_knows_limit;
inBoth = personsA INTERSECT personsB;
inBoth = SELECT p FROM inBoth:p
POST-ACCUM @@result += Row(p.id, p.@messageCountA, p.@messageCountB,
p.@messageCountA + p.@messageCountB);
PRINT @@result AS result;
}
Q41. Information propagation analysis
Structurally the heaviest query in the set: it spans two distinct forums, three persons and two variable-length REPLY_OF*0.. segments, then requires message2 to be more than delta hours later than message1 and excludes person1’s membership of forum2. The forum of each tagged message is found by walking up its reply chain, and the rest is accumulated per forum and per message.
CREATE OR REPLACE DISTRIBUTED QUERY q41_information_propagation(STRING p_tag = "Koda_Kumi",
INT p_delta = 10)
FOR GRAPH snb100 SYNTAX V1 {
TYPEDEF TUPLE <UINT personId, UINT messageCount> Row;
HeapAccum<Row>(10, messageCount DESC, personId ASC) @@result;
OrAccum @hasTag, @isCreator;
MinAccum<UINT> @creatorId;
SetAccum<VERTEX> @origins;
MapAccum<VERTEX, MinAccum<VERTEX<Forum>>> @@forumOf;
MinAccum<VERTEX<Forum>> @forum;
MapAccum<VERTEX<Forum>, MapAccum<UINT, MinAccum<INT>>> @@creatorsByForum;
MapAccum<VERTEX<Forum>, SetAccum<UINT>> @@membersOfForum;
SetAccum<VERTEX<Forum>> @memberOf, @creatorForums, @forum1;
SetAccum<UINT> @person1Ids;
MapAccum<UINT, SumAccum<UINT>> @@messageCount;
INT deltaMs = p_delta * 3600000;
allTags = {Tag.*};
tag = SELECT t FROM allTags:t WHERE t.name == p_tag;
tagged = SELECT m FROM tag:t -(HAS_TAG_REVERSE)-> (Comment|Post):m
ACCUM m.@hasTag += TRUE
POST-ACCUM m.@origins += m;
creators = SELECT p FROM tagged:m -(HAS_CREATOR)-> Person:p
ACCUM m.@creatorId += p.id
POST-ACCUM p.@isCreator = TRUE;
roots (ANY) = SELECT m FROM tagged:m WHERE m.type == "Post";
frontier (ANY) = SELECT m FROM tagged:m WHERE m.type == "Comment";
WHILE frontier.size() > 0 DO
up = SELECT p FROM frontier:c -(REPLY_OF)-> (Comment|Post):p ACCUM p.@origins += c.@origins;
reachedPosts = SELECT p FROM up:p WHERE p.type == "Post";
roots = roots UNION reachedPosts;
frontier = SELECT p FROM up:p WHERE p.type == "Comment";
END;
tmp = SELECT f FROM roots:p -(CONTAINER_OF_REVERSE)-> Forum:f
ACCUM FOREACH o IN p.@origins DO @@forumOf += (o -> f) END;
tagged = SELECT m FROM tagged:m POST-ACCUM m.@forum = @@forumOf.get(m);
tmp = SELECT p FROM tagged:m -(HAS_CREATOR)-> Person:p
ACCUM @@creatorsByForum += (m.@forum -> (p.id -> m.creationDate));
forums = {Forum.*};
forum12 = SELECT f FROM forums:f WHERE @@creatorsByForum.containsKey(f);
tmp = SELECT p FROM forum12:f -(HAS_MEMBER)-> Person:p
ACCUM @@membersOfForum += (f -> p.id),
IF p.@isCreator THEN p.@memberOf += f END;
tmp = SELECT m FROM creators:p -(HAS_CREATOR_REVERSE)-> (Comment|Post):m
WHERE m.@hasTag
ACCUM m.@creatorForums += p.@memberOf;
messages2 = SELECT m2 FROM tagged:c -(REPLY_OF)-> (Comment|Post):m2
WHERE m2.@hasTag AND c.@creatorId != m2.@creatorId
ACCUM m2.@forum1 += (m2.@creatorForums INTERSECT c.@creatorForums)
POST-ACCUM m2.@forum1.remove(m2.@forum)
HAVING m2.@forum1.size() > 0;
messages2 = SELECT m2 FROM messages2:m2
ACCUM
FOREACH f IN m2.@forum1 DO
FOREACH (p1, date1) IN @@creatorsByForum.get(f) DO
IF date1 + deltaMs < m2.creationDate
AND NOT @@membersOfForum.get(m2.@forum).contains(p1) THEN
m2.@person1Ids += p1
END
END
END
POST-ACCUM FOREACH p1 IN m2.@person1Ids DO @@messageCount += (p1 -> 1) END;
FOREACH (p1, n) IN @@messageCount DO
@@result += Row(p1, n);
END;
PRINT @@result AS result;
}