Store a PgxFrame in Database
You can store a PgxFrame
output to relational database
tables.
The outputs of the property graph machine
learning algorithms are PgxFrame
(s) and this data structure can be
stored in the database. The columns and rows of the PgxFrame
correspond
to the columns and rows of the database table.
The following example converts a PGQL result set to a
PgxFrame
, which is then stored as a table to the database.
var g = session.readGraphByName("BANK_GRAPH", GraphSource.PG_VIEW)
var query = "SELECT s.acct_id FROM MATCH (s) LIMIT 10"
var rs = g.queryPgql(query)
if (rs != null) {
rs.toFrame().write().db()
.tablename("accounts") // name of the DB table
.overwrite(true)
.store();
}
g = session.read_graph_by_name("BANK_GRAPH", "pg_view")
query = "SELECT s.acct_id FROM MATCH (s)"
rs = g.execute_pgql(query)
if (rs != None):
rs.to_frame().write().db().table_name("accounts").overwrite(True).store()
On executing the notebook paragraph, the PgxFrame
data
gets inserted in the appropriate database table. You can verify this by viewing and
querying the database table using Database Actions. See SQL Page in Database Actions for more
information on running SQL statements in Database Actions.
Also, note the following:
- The generated table name and column names are case-sensitive. The
preceding code example creates a database table having a lowercase name
"accounts"
with a column named"acct_id"
.If however, the query is:
and table name is specified as"SELECT s.acct_id as ACCT_ID FROM MATCH (s) limit 10"
tablename("ACCOUNTS")
, then the database table will have a uppercase name"ACCOUNTS"
with a column named"ACCT_ID"
. - If a database table with the same name is already existing, then
you can use the overwrite mode by setting
overwrite(true)
as seen in the preceding example. The previous table gets truncated and the data is then inserted. By default, the value is set tofalse
. - If you are using an Always Free Autonomous Database instance (that
is, one with only 1 OCPU and 20GB of storage), then you must also specify that
only one connection must be used when writing the
PgxFrame
to the table in a Java (PGX) notebook paragraph. For example, you must invokewrite()
as shown:rs.toFrame().write().db().connections(1).tablename("accounts").overwrite(true).store();