Graphlytic 5.1.0

Table of Contents

1. Description

Connects oracle database using JDBC driver. Oracle JDBC connector have to be located in $MAIN_LIBDIR (default value is ${GRAPHLYTIC_HOME}/lib) folder as a java archive (.jar). JDBC connectors are not bundled within Graphlytic installation package.

2. Connection

2.1. Attributes

Name	Description	Example
id	this etl connection, that will be used as a refference with connection-id within scripts and queries	id=”oracleDatabase1”
driver	use “oracle” in this case	driver=”oracle”
url	JDBC connection string	url=”jdbc:oracle:thin:@//localhost:1521/FREEPDB1”
user	username	user=”graphlytic”
password	password	password=”graphlytic”

see “" section in https://demtec.atlassian.net/wiki/spaces/GLDOC050100/pages/edit-v2/917766145?draftShareId=223ffd9d-d08f-4f13-832a-0cc8a6198472#5.-Examples for example usage

2.2. Parameters

Name	Description	Default
plsql	If true the slash on a single line is used as a separator for SQL statements. This mode is required to create triggers and execute blocks of PL/SQL code.	`false`
statement.cache	Size of prepared statements cache or 0 to disable statement caching.	64
statement.separator	SQL statements separator string. Similar to Ant delimiter property.	`;` (semicolon).
statement.batchSize	Activates batching with specified batch size.	`0` (batching is disabled).
statement.fetchSize	Gives the JDBC driver a hint as to the number of rows that should be fetched from the database when more rows are needed for `ResultSet`.	`0`
flushBeforeQuery	True if flush of current connection need to be performed before query execution. Flushing might be necessary in batch mode, in order to send pending batches before executing a query	`false`
statement.separator.singleline	True if the delimiter should only be recognized on a line by itself. Leading and trailing whitespaces are ignored when searching for a separator in `statement.separator.singleline=true` mode. Similar to Ant delimitertype property.	`false`
keepformat	True if the original SQL formatting should be preserved. This property is similar to Ant keepformat property except that Oracle-style hints (?+ hint /) are always preserved in Scriptella.	`false`, i.e. extra whitespaces and comments removed.
transaction.isolation	Transaction isolation level name or an integer value according to java.sql.Connection javadoc. The valid level names are: `READ_UNCOMMITTED`, `READ_COMMITTED`, `REPEATABLE_READ` and `SERIALIZABLE`.	default value is driver specific.
autocommit	True if connection is in auto-commit mode. If a connection is in auto-commit mode, then all its SQL statements will be executed and committed as individual transactions.See also autocommit.size. Note: In general avoid setting autocommit to true, because in this case an ETL process cannot be rolled back correctly. Use this parameter only for performance critical operations (bulk inserts etc.).	`false`
autocommit.size	If positive, specifies the number of statements to execute before producing implicit commit, i.e. controls how much data is committed in its batches. Notes: In general avoid using autocommit.size, because in this case an ETL process cannot be rolled back correctly. Use this parameter only for performance critical operations (bulk inserts etc.). If the `autocommit` is `true`, then setting `autocommit.size` has no effect	`false`

3. Query

Executes the oracle query and returns the result of the query. Name of result parameters corresponds to select statement. When * was used (eg. SELECT * FROM users), than column names are used as return variable names (for example. $id, $name, …)

You can use any <script> in the body of the query to process every record returned by the query one by one.

4. Script

Executes any read or write statements. Used for "write" statements mainly. Statement in each <script> is executed in a separate transaction. If there is some error during the execution of any <script> then the rollback is called.

5. Examples

Query example: Executes a query. The script element is used in the Query element to process every record of the result writing it into the graphlytic log as an INFO entry.

<!DOCTYPE etl SYSTEM "https://scriptella.org/dtd/etl.dtd">
 <etl>
    <description>Graphlytic job example for oracle database query</description>
                
    <properties>
        job_name=Graphlytic job
    </properties>
            
    <!-- CONNECTIONS -->
    <connection id="oracle" driver="oracle" url="jdbc:oracle:thin:@//localhost:1521/FREEPDB1" user="graphlytic" password="graphlytic">
         statement.separator=$$ 
    </connection>
            
    <connection id="logInfo" driver="log">
        level=INFO
    </connection>
                
    <!-- JOB STEPS -->
    <query connection-id="oracle">
        SELECT count(id) as total FROM ACTORS 
                    
        <script connection-id="logInfo">
            Total Imported Entries : $total
        </script>
    </query>     
</etl>

Script example: Insert two rows into a table, then query the table for a row count and log the result.

<!DOCTYPE etl SYSTEM "https://scriptella.org/dtd/etl.dtd">
 <etl>
    <description>Graphlytic job</description>
                
    <properties>
        job_name=Graphlytic job
        adminUsername=oracle-admin-user
        adminDescription=this is a demonstration of property substitution
    </properties>
            
    <!-- CONNECTIONS -->
    <connection id="oracle" driver="oracle" url="jdbc:oracle:thin:@//localhost:1521/FREEPDB1" user="graphlytic" password="graphlytic">
         statement.separator=$$ 
    </connection>
            
    <connection id="logInfo" driver="log">
        level=INFO
    </connection>
                
    <!-- JOB STEPS -->
    <script connection-id="oracle">
        INSERT INTO records VALUES (NULL, '$adminUsername', '$adminDescription');
    </script>
    
    <script connection-id="oracle">
        INSERT INTO records VALUES (NULL, 'anotherUser', 'some other description');
    </script>
            
    <query connection-id="oracle">
        SELECT count(id) as totalImported FROM records 
                    
        <script connection-id="logInfo">
            Total Imported Entries : $totalImported
        </script>
    </query>     
</etl>