2.2.3 Quick Start: Executing PGQL Queries in SQLcl
This tutorial provides you resources to get started on executing PGQL Queries in SQLcl.
You can execute PGQL queries in SQLcl with a plug-in that is available with Oracle Graph Server and Client. See PGQL Plug-in for SQLcl in Oracle SQLcl User’s Guide for more information.
The example in this tutorial helps you get started on executing PGQL queries on a graph in SQLcl. As a prerequisite, to perform the steps in the example, you must set up the bank graph data in your database schema using the sample data provided with the graph server installation. See Using Sample Data for Graph Analysis for more information.
The following example creates a property graph using the PGQL CREATE
PROPERTY GRAPH
statement, executes PGQL queries against the
graph and finally drops the graph using SQLcl.
- Start SQLcl with your database schema credentials. In the
following command, graphuser is the database user used to
connect to
SQLcl.
sql graphuser/<password_for_graphuser>@<tns_alias> SQLcl: Release 21.2 Production on Sun Jan 30 04:30:09 2022 Copyright (c) 1982, 2022, Oracle. All rights reserved. Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production Version 21.3.0.0.0
- Enable PGQL mode as
shown:
SQL> pgql auto on; PGQL Auto enabled for schema=[null], graph=[null], execute=[true], translate=[false]
Note that no arguments are used in the preceding PGQL command.
- Create a property graph view on the bank graph data
tables.
PGQL> CREATE PROPERTY GRAPH bank_graph 2 VERTEX TABLES ( 3 bank_accounts 4 LABEL ACCOUNTS 5 PROPERTIES (ID, NAME) 6 ) 7 EDGE TABLES ( 8 bank_txns 9 SOURCE KEY (from_acct_id) REFERENCES bank_accounts 10 DESTINATION KEY (to_acct_id) REFERENCES bank_accounts 11 LABEL TRANSFERS 12 PROPERTIES (FROM_ACCT_ID, TO_ACCT_ID, AMOUNT, DESCRIPTION) 13* ) OPTIONS(PG_VIEW); Graph created
- Set
bank_graph
as the default graph using thegraph
argument when enabling PGQL mode.PGQL> pgql auto on graph bank_graph; PGQL Auto enabled for schema=[null], graph=[BANK_GRAPH], execute=[true], translate=[false]
- Execute PGQL queries against the default graph. For example, the
following PGQL query retrieves the total number of vertices as
shown:
PGQL> SELECT COUNT(*) AS num_vertices FROM MATCH(n); NUM_VERTICES _______________ 1000
Note that in the preceding query, the graph name is not specified using the
ON
clause as part of theMATCH
clause. - Reconnect to SQLcl as another schema
user.
PGQL> conn system/<password_for_system>@<tns_alias>; Connected.
- Enable PGQL mode using the
schema
argument to set the default schema used for creating the graph. Also, setbank_graph
as the default graph using thegraph
argument :PGQL> pgql auto on schema graphuser graph bank_graph; PGQL Auto enabled for schema=[graphuser], graph=[BANK_GRAPH], execute=[true], translate=[false]
- Execute a PGQL query to retrieve all the edge properties on the graph as
shown:
PGQL> SELECT e.* FROM MATCH (n:accounts) -[e:transfers]-> (m:accounts) LIMIT 10; AMOUNT DESCRIPTION FROM_ACCT_ID TO_ACCT_ID _________ ______________ _______________ _____________ 1000 transfer 178 921 1000 transfer 178 462 1000 transfer 179 688 1000 transfer 179 166 1000 transfer 179 397 1000 transfer 179 384 1000 transfer 179 900 1000 transfer 180 855 1000 transfer 180 984 1000 transfer 180 352 10 rows selected.
Therefore, you can set a default schema and execute PGQL queries against a default graph in SQLcl.
- Finally, drop the graph after executing the required graph
queries.
PGQL> DROP PROPERTY GRAPH bank_graph; Graph dropped
Also, see Execute PGQL Queries in SQLcl for more information.
Parent topic: Quick Start: Interactively Analyze Graph Data