PGQL Plug-in for SQLcl

5 PGQL Plug-in for SQLcl

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.

5.1 Downloading and Installing

You can download the plug-in either from Oracle Software Delivery Cloud (search for "Oracle Graph Server and Client") or from Oracle Graph Server and Client Downloads.

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 installation.

5.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.

5.3 Examples

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 graph to 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>