Property Graph Query Language (PGQL) is a graph pattern-matching query language for the property graph data model. The PGQL plug-in enables execution and translation of PGQL statements from the command line in SQLcl.
The PGQL plug-in for SQLcl is available with Oracle Graph Server and Client Release 20.3 and later releases.
Property Graph Query Language for more information about PGQL.
This chapter covers the following topics:
4.1 Downloading and Installing
To install the PGQL plug-in for SQLcl, you need to unzip the downloaded
plug-in into the
lib/ext directory of your local SQLcl
4.2 About PGQL Commands
To run PGQL statements against a database, start SQLcl and then turn on PGQL mode by using the following command:
pgql auto on;
After PGQL mode is turned on, all subsequent SELECT, INSERT, UPDATE, DELETE, CREATE, and DROP statements are considered as PGQL statements.
You can also provide different arguments when turning on PGQL mode:
pgql auto on [args]
The possible arguments are:
graph <graph_name>: Make queries run against the specified graph.
execute: Turn on PGQL execution.
executeonly: Turn on PGQL execution and do not show PGQL-to-SQL translation.
translate: Show PGQL-to-SQL translation.
translateonly: Show PGQL-to-SQL translation and turn off PGQL execution.
parallel <parallel>: Run (or translate) PGQL queries using the specified parallel value.
dynamic_sampling <dynamic_sampling>: Run (or translate) PGQL queries using the specified dynamic sampling value.
By default, the
graph value is not set, PGQL execution
is turned on, and PGQL to SQL translation is turned off. The default value for
parallel is 0, and the default value for
dynamic_sampling is 6.
To run SQL statements again, turn PGQL mode off:
pgql auto off;
To reset all parameters to their default values, turn PGQL mode off and then turn on again.
This section provides some examples:
Turn On PGQL Mode
This example turns PGQL mode on. The PGQL prompt appears when PGQL is enabled.
SQL> pgql auto on; PGQL Auto enabled for graph=[null], execute=[true], translate=[false] PGQL>
Create a Property Graph and Execute a Query
This example creates a property graph and executes a query against the newly created "scott_hr" graph.
PGQL> CREATE PROPERTY GRAPH scott_hr 2 VERTEX TABLES ( 3 emp KEY(empno) LABEL Employee 4 PROPERTIES ARE ALL COLUMNS EXCEPT ( deptno ), 5 dept KEY(deptno) LABEL Department 6 PROPERTIES ( deptno, dname ) 7 ) 8 EDGE TABLES ( 9 emp AS works_for 10 SOURCE KEY ( empno ) REFERENCES emp 11 DESTINATION KEY ( deptno ) REFERENCES dept 12 NO PROPERTIES 13 ); Graph created PGQL> column name format a15; PGQL> SELECT e.ename AS name 2 FROM MATCH (e:Employee) ON scott_hr 3 ORDER by e.ename 4 LIMIT 4; NAME --------------- ADAMS ALLEN BLAKE CLARK
Define a Graph Parameter
You can define a
graph parameter to run all PGQL
queries against a particular graph. This example sets
scott_hr. Note that the query does not need an ON clause.
PGQL> pgql auto on graph scott_hr; PGQL Auto enabled for graph=[SCOTT_HR], execute=[true], translate=[false] PGQL> column department format a20; PGQL> column employees format a10; PGQL> SELECT d.dname AS department, COUNT(e) AS employees 2 FROM MATCH (e:Employee) -[:works_for]-> (d:Department) 3 GROUP BY d 4 ORDER BY employees 5* LIMIT 3; DEPARTMENT EMPLOYEES -------------------- ---------- ACCOUNTING 3 RESEARCH 5 SALES 6
Show SQL Translation Using Parallel Value 2
This example shows the SQL translation of a PGQL query using a parallel value of 2. Note that the SQL translation has a hint using the defined parallel value.
SQL> pgql auto on translateonly parallel 2; PGQL Auto enabled for graph=[null], execute=[false], translate=[true] PGQL> SELECT id(n) FROM MATCH (n) ON scott_hr; SELECT /*+ parallel(2) */ * FROM(SELECT 7 AS "id(n)$T", to_nchar(T0$0.VID,'TM9','NLS_Numeric_Characters=''.,''') AS "id(n)$V", T0$0.VID AS "id(n)$VN", to_timestamp_tz(null) AS "id(n)$VT" FROM "SCOTT".SCOTT_HRVD$ T0$0);
Turn Off PGQL Mode
This example shows how to turn PGQL mode off.
PGQL> pgql auto off; PGQL Auto disabled SQL>