Graphlytic 5.1.0

Table of Contents

1. Description

Connects mysql database using JDBC driver. MySQL 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=”mysqlRootConnection”
driver	use “mysql” in this case	driver=”mysql”
url	JDBC connection string	url=”jdbc:mysql://localhost:3307/my-database”
user	MySQL username	user=”myMysqlUser”
password	MySQL password	password=”myMysqlPassword”

see “" section inETL: MySQL for example usage

2.2. Parameters

Name	Description	Default
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 mysql 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 and every record from the result is written into a text file. The script element is used in the Query element to process every record of the result.

<!DOCTYPE etl SYSTEM "https://scriptella.org/dtd/etl.dtd">
 <etl>
    <description>Graphlytic job</description>
                
    <properties>
        job_name=Graphlytic job
    </properties>
            
    <!-- CONNECTIONS -->
    <connection id="mysql" driver="mysql" url="jdbc:mysql://localhost:3306/my-database" user="mysqluser" password="mysqlpassword">
         statement.separator=$$ 
    </connection>
            
    <connection id="logInfo" driver="log">
        level=INFO
    </connection>
                
    <!-- JOB STEPS -->
    <script connection-id="logInfo">
        STARTING JOB "$job_name"
    </script>
            
    <query connection-id="mysql">
        SELECT r.ID AS AID, r.name, r.description  FROM records r LIMIT 2
                            
        <script connection-id="logInfo">
            ID: $AID
            Name: $name
            Description: $description
        </script>
    </query>     
</etl>

Script example: Insert a two row 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=mysql-admin-user
        adminDescription=this is a demonstration of property substitution
    </properties>
            
    <!-- CONNECTIONS -->
    <connection id="mysql" driver="mysql" url="jdbc:mysql://localhost:3307/my-database" user="emminex" password="password104">
         statement.separator=$$ 
    </connection>
            
    <connection id="logInfo" driver="log">
        level=INFO
    </connection>
                
    <!-- JOB STEPS -->
    <script connection-id="mysql">
        INSERT INTO records VALUES (NULL, '$adminUsername', '$adminDescription');
    </script>
    
    <script connection-id="mysql">
        INSERT INTO records VALUES (NULL, 'anotherUser', 'some other description');
    </script>
            
    <query connection-id="mysql">
        SELECT count(id) as totalImported FROM records 
                    
        <script connection-id="logInfo">
            Total Imported Entries : $totalImported
        </script>
    </query>     
</etl>