29.13.13 Loading a PgxFrame from a Database

You can load a PgxFrame from relational database tables. Each column of the relational table will correspond to a column in the loaded frame. When loading a PgxFrame from the database, the default behavior is to detect the table columns and load them all. If not specified explicitly, the connection details of the current user and session are used and the columns are detected automatically. Also, you can load a PgxFrame from a database table containing CLOB data type columns.

The following describes the steps to load a PgxFrame from a database table:

1. Create a Session and an Analyst:
   • JShell
   • Java
   • Python

   cd /opt/oracle/graph/
   ./bin/opg4j
   
   opg4j> import static oracle.pgx.api.frames.functions.ColumnRenaming.renaming
   opg4j> import static oracle.pgx.api.frames.schema.ColumnDescriptor.columnDescriptor
   opg4j> import oracle.pgx.api.frames.schema.*
   opg4j> import oracle.pgx.api.frames.schema.datatypes.*
   
   opg4j> var session = Pgx.createSession("my-session")
   opg4j> var analyst = session.createAnalyst()

   import oracle.pgx.api.*;
   import oracle.pgx.api.frames.*;
   import oracle.pgx.api.frames.functions.*;
   import oracle.pgx.api.frames.schema.*;
   import oracle.pgx.api.frames.schema.datatypes.*;
   import static oracle.pgx.api.frames.functions.ColumnRenaming.renaming;
   import static oracle.pgx.api.frames.schema.ColumnDescriptor.columnDescriptor;
   
   PgxSession session = Pgx.createSession("my-session");
   Analyst analyst = session.createAnalyst();

   session = pypgx.get_session(session_name="my-session")
   analyst = session.create_analyst()

2. Load a PgxFrame. The example assumes that you are loading the PgxFrame from the current logged in schema.
   • JShell
   • Java
   • Python

   opg4j> var exampleFrame = session.readFrame().
   ...>       db().
   ...>       name("Transfers").      // name of the frame
   ...>       tablename("T1").        // name of the table from where the data must be loaded
   ...>       connections(16).        // indicates that 16 connections can be used to load in parallel
   ...>       load()

   PgxFrame exampleFrame = session.readFrame()
       .db()
       .name("Transfers")     // name of the frame 
       .tablename("T1")       // name of the table from where the data must be loaded 
       .connections(16)       // indicates that 16 connections can be used to load in parallel 
       .load();

   >>> example_frame = (
   ...     session.read_frame().
   ...     name('Transfers').    # name of the frame
   ...     db().
   ...     table_name('T1').     # name of the table from where the data must be loaded 
   ...     connections(16).      # indicates that 16 connections can be used to load in parallel
   ...     load()
   ...     )

3. If only a subset of the columns must be loaded, then you can specify the columns as shown in the following example. Note that the following example loads the PgxFrame from a different schema.
   • JShell
   • Java
   • Python

   opg4j> session.registerKeystore("<pathToKeystore>", "<keystorePassword>".toCharArray())
   opg4j> var exampleFrame = session.readFrame().
   ...>       db().
   ...>       name("Transfers").  // name of the frame
   ...>       tablename("T1").    // name of the table from where the data must be loaded
   ...>       jdbcUrl("<jdbcUrl>").
   ...>       username("<username>").
   ...>       keystoreAlias("<keystoreAlias>").
   ...>       connections(16).    // indicates that 16 connections can be used to load in parallel
   ...>       columns(
   ...>         columnDescriptor("FROM_ACCT_ID", DataTypes.INTEGER_TYPE),
   ...>         columnDescriptor("TO_ACCT_ID", DataTypes.INTEGER_TYPE)
   ...>       ).                 // columns to load
   ...>       load()

   session.registerKeystore("<pathToKeystore>", "<keystorePassword>".toCharArray())
   PgxFrame exampleFrame = session.readFrame()
       .db()
       .name("Transfers") // name of the frame
       .tablename("T1")   // name of the table from where the data must be loaded
       .jdbcUrl("<jdbcUrl>")
       .username("<username>")
       .keystoreAlias("<keystoreAlias>")
       .connections(16)  // indicates that 16 connections can be used to load in parallel
       .columns(
          columnDescriptor("FROM_ACCT_ID", DataTypes.INTEGER_TYPE),
          columnDescriptor("TO_ACCT_ID", DataTypes.INTEGER_TYPE)
        )               // columns to load
       .load();

   >>> session.register_keystore("<pathToKeystore>", "<keystorePassword>")
   >>> example_frame = (
   ...     session.read_frame().
   ...     name('Transfers').   # name of the frame
   ...     db(). 
   ...     table_name('T1').    # name of the table from where the data must be loaded
   ...     jdbc_url('<jdbc_url>'). 
   ...     username('<username>').
   ...     keystore_alias('<keystore_alias>'). 
   ...     connections(16).     # indicates that 16 connections can be used to load in parallel
   ...     columns(
   ...       [
   ...         ('FROM_ACCT_ID', 'INTEGER_TYPE'),
   ...         ('TO_ACCT_ID', 'INTEGER_TYPE')
   ...       ]
   ...     ).  # columns to load
   ...     load()
   ...     )

   You can also create a graph from the PgxFrame(s). See Creating a Graph from Multiple PgxFrame Objects for more information.