QUERY_GRAPH - Kinetica Docs

Queries an existing graph using unique query identifiers and combinations. The QUERY_GRAPH function can be called either within a SELECT statement as a table function or within an EXECUTE FUNCTION call.

SELECT * FROM TABLE
(
	QUERY_GRAPH
	(
		GRAPH => '[<schema name>.]<graph name>',
		QUERIES => <INPUT_TABLE(<table/query>) | INPUT_TABLES((<table/query>)[,...])>,
		RINGS => <integer>,
		[RESTRICTIONS => <INPUT_TABLE(<table/query>) | INPUT_TABLES((<table/query>)[,...])>,]
		[OPTIONS => KV_PAIRS('<option name>' = '<option value>'[,...])]
	)
)

EXECUTE FUNCTION QUERY_GRAPH
(
	GRAPH => '[<graph schema name>.]<graph name>',
	QUERIES => <INPUT_TABLE(<table/query>) | INPUT_TABLES((<table/query>)[,...])>,
	ADJACENCY_TABLE => '[<table schema name>.]<table name>',
	RINGS => <integer>,
	[RESTRICTIONS => <INPUT_TABLE(<table/query>) | INPUT_TABLES((<table/query>)[,...])>,]
	[OPTIONS => KV_PAIRS('<option name>' = '<option value>'[,...])]
)

Parameters

GRAPH

Name of the graph to query

QUERIES

Nodes or edges to be queried specified using the SQL INPUT_TABLE or INPUT_TABLES function; review Query Identifiers and Query Identifier Combinations for more information.

The input can be either the name of a table with appropriately named columns; or a query, to use specific columns or other SQL features).

ADJACENCY_TABLE

Name of the table to store the resulting adjacent edges in [schema_name.]table_name format, using standard name resolution rules and meeting table naming criteria; a table storing the resulting matching nodes will also be created, named <ADJACENCY_TABLE>_nodes

Only applicable when using the EXECUTE FUNCTION syntax

RINGS

Sets the number of rings around (hops from) the queried node(s) to return matching edges & nodes

Only applicable when querying on nodes; e.g., giving NODE_NAME or NODE_LABEL

RESTRICTIONS

Additional restrictions to apply to the nodes/edges of an existing graph specified using the SQL INPUT_TABLE or INPUT_TABLES function; review Components and Identifiers and Identifier Combinations for more information.

The input can be either the name of a table with appropriately named columns; or a query, to use specific columns or other SQL features).

Only applicable when querying on nodes; e.g., giving NODE_NAME or NODE_LABEL

OPTIONS

Optional indicator that a comma-delimited list of connection option/value assignments will follow.

Option	Description
`and_labels`	If `true`, the result of the query has entities that satisfy all the target labels instead of any of the labels. The default is `false`.
`find_common_labels`	If `true`, adds a `LABELS` column to the output tables specified by `ADJACENCY_TABLE`. When `RINGS` is 0, all labels of queried nodes & edges will be listed in this column; otherwise, (for many-to-many queries and multi-level traversals), all common labels between the source & target nodes, as well as common edge labels in each path will be listed. The default is `false`.
`force_undirected`	If `true`, all inbound & outbound edges relative to the node will be returned. If set to `false`, only outbound edges relative to the node will be returned. The default is `false`. Consult Directed Graphs for more details. Only applicable when querying on nodes on a directed graph
`limit`	When specified, limits the number of query results. The size of the corresponding node table will also be limited by this value.
`output_charn_length`	When specified, limits the size of string columns in the output tables. The default is `64`. Valid values are between `1` & `256`.
`output_wkt_path`	If true, adds a `QUERY_EDGE_WKTLINE` column to the specified `ADJACENCY_TABLE` so the solution can be viewed via WMS; for social and non-geospatial solutions, the `QUERY_EDGE_WKTLINE` column will be populated with spatial coordinates derived from a flattening layout algorithm so the solution can still be viewed. The default is `false`.
`result_table_index`	Indicates which result table to return results from: `1` - Return edge results (default) `2` - Return node results Only applicable when using the table function syntax
`server_id`	Indicates which graph server(s) to send the request to. Default is to send to the server, amongst those containing the corresponding graph, that has the most computational bandwidth.

Examples

Using the EXECUTE FUNCTION syntax necessitates the ADJACENCY_TABLE parameter to store the query results, where using the table function syntax will return the results directly.

SELECT * FROM TABLE
(
	QUERY_GRAPH
	(
		GRAPH => 'sample_graph',
		QUERIES => INPUT_TABLE(SELECT ST_GEOMFROMTEXT('POINT(0 0)') AS NODE_WKTPOINT),
		RINGS => 1,
		OPTIONS => KV_PAIRS(output_wkt_path = 'true', result_table_index = '2')
	)
)

SELECT * FROM TABLE
(
	QUERY_GRAPH
	(
		GRAPH => 'sample_graph',
		QUERIES => INPUT_TABLE(SELECT ST_GEOMFROMTEXT('POINT(0 0)') AS NODE_WKTPOINT),
		RINGS => 1,
		OPTIONS => KV_PAIRS(output_wkt_path = 'true')
	)
)

EXECUTE FUNCTION QUERY_GRAPH
(
	GRAPH => 'sample_graph',
	QUERIES => INPUT_TABLE(SELECT ST_GEOMFROMTEXT('POINT(5 5)') AS NODE_WKTPOINT),
	RINGS => 2,
	ADJACENCY_TABLE => 'sample_graph_node_query',
	OPTIONS => KV_PAIRS(output_wkt_path = 'true')
)

Querying by Hops

Queries can make use of a HOP_ID identifier to match nodes & edges at a given distance from the source node or edge. A graph of movies and their actors & directors can be queried, for instance, to return the titles & directors of movies in which James Spader has acted. Here, the query starts with the James Spader actor node and uses HOP_ID filters to:

move across acted edges in the 1st hop to all the nodes in which James Spader has acted
find the director nodes connected to those movies in the 2nd hop; note the -2 for the HOP_ID allows one-way edges to be traversed in reverse—the directed edge connecting directors to the movies they directed is one-way, from director to movie, but the query needs to move from movie to director of that movie, thus the -2 designation for the 2nd hop to follow those edges in reverse

Since hop-based queries return all edges along the path, the query ends by filtering the James Spader node out of the result set.

Query by Hop ID Example

SELECT
	QUERY_NODE1_NAME AS Movie,
	QUERY_NODE2_NAME AS Director
FROM TABLE
(
	QUERY_GRAPH
	(
		GRAPH => 'example_graph_label.imdb',
		QUERIES => INPUT_TABLES
		(
			(SELECT 'James Spader'           AS NODE_NAME),
			(SELECT  1 AS HOP_ID, 'acted'    AS EDGE_LABEL),
			(SELECT -2 AS HOP_ID, 'directed' AS EDGE_LABEL),
			(SELECT 'director'               AS TARGET_NODE_LABEL)
		),
		RINGS => 2
	)
)

Querying for Commonalities

Queries can make use of the find_common_labels option to list labels shared by both source & target node, as well as labels shared by edges in the result paths. A graph of movies and their actors & directors can be queried, for instance, to return the common occupations within filmmaking between Al Pacino and those who have directed movies in which he has acted. Here, the query starts with the Al Pacino actor node, looks within two hops for directors (Al Pacino->movie->directed by), and returns those directors along with occupations shared by Al Pacino and each one:

Query for Common Labels Example

EXECUTE FUNCTION QUERY_GRAPH
(
	GRAPH => 'example_graph_label.imdb',
	QUERIES => INPUT_TABLES
	(
		(SELECT 'Al Pacino' AS NODE_NAME),
		(SELECT 'director' AS TARGET_NODE_LABEL)
	),
	RINGS => 2,
	ADJACENCY_TABLE => 'example_graph_label.imdb_fcl',
	OPTIONS => KV_PAIRS
	(
	    force_undirected = 'true',
	    find_common_labels = 'true'
	)

Supplementing Queried Graph Data

As graph queries ultimately produce standard SQL result sets, they can be used in larger SQL queries as joins or sub-selects to add data to result sets or to filter data out of them. A graph of movies and their actors & directors can be queried, for instance, to return the actors in Jean Reno movies, and that result can be joined with a table of actor biographical data to also return the ages of those actors. Here, the query starts with the Jean Reno actor node, looks within two hops for actors (Jean Reno->movie->acted in), and returns those actors along with their respective current ages by subtracting their corresponding date of birth (dob column) in the biographical table (person) from the current date:

Supplementing Graph Query Example

SELECT
	QUERY_NODE_NAME_TARGET AS Actor,
	DATEDIFF(YEAR, p.dob, CURRENT_DATE()) AS Age
FROM TABLE
(
	QUERY_GRAPH
	(
		GRAPH => 'example_graph_label.imdb',
		QUERIES => INPUT_TABLES
		(
			(SELECT 'Jean Reno' AS NODE_NAME),
			(SELECT 'actor' AS TARGET_NODE_LABEL)
		),
		RINGS => 2,
		OPTIONS => KV_PAIRS
		(
			force_undirected = 'true',
			result_table_index = '2'
		)
	)
)

​Parameters

​Examples

​Querying by Hops

​Querying for Commonalities

​Supplementing Queried Graph Data

Parameters

Examples

Querying by Hops

Querying for Commonalities

Supplementing Queried Graph Data