Graphlytic 5.1.1

1. Description

This driver connects to single SeaTable base using Base-Token. To know how to get Base-Token see how to Get Base-Token with API-Token.

2. Connection

2.1. Attributes

Name	Description	Example
id	this etl connection, that will be used as a reference with connection-id within scripts and queries	id=”seatableConnection”
driver	use “seatable” in this case	driver=”seatable”

see “" section in examples for usage.

2.2. Parameters

Name	Description	Example
api_token	generated base api token	`08b2891b1d7aab544957ba10b9acecff03b8a10d`

3. Query

Executes the sql 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, $LABEL, …)

For supported sql statements see SQL in SeaTable.

4. Script

Executes a write sql statement. Used for "write" statements mainly.

Please note that according to seatable documentation: “New rows (INSERT statement) are always added to the big data backend. An error message will be returned, if the big data backend is not activated for the target base.”

For supported sql statements see SQL in SeaTable.

5. Examples

Query example: Scripts executes two queries and creates nodes and relationships in neo4j row by row.

<!DOCTYPE etl SYSTEM "https://scriptella.org/dtd/etl.dtd">
<etl>
    <description>Graphlytic job</description>
                
    <properties>
        job_name=Graphlytic job
    </properties>
 
    <!-- CONNECTIONS -->
    <connection id="seatable1" driver="seatable">
        api_token=123456789abcdef......
    </connection>
 
    <connection id="neo4j" driver="graphConnection">
        project_id=1
    </connection>
 
    <connection id="logInfo" driver="log">
        level=INFO
    </connection>
 
    <!-- JOB STEPS -->
    <script connection-id="logInfo">
        STARTING JOB "$job_name"
    </script>
 
 
    <!-- CLEAR STEP -->
    <script connection-id="logInfo">
        CLEAR NEO4J DATABASE
    </script>
 
    <script connection-id="neo4j">
        MATCH (n) DETACH DELETE n
    </script>
 
    <!-- QUERY SEATABLE AND IMPORT NODES -->
    <query connection-id="seatable1">
        SELECT LABEL, UID, PROPERTY1, PROPERTY2, PROPERTY3 FROM NODES
        <script connection-id="neo4j">
            CREATE (n:$LABEL {uid:'$UID', property1:'$PROPERTY1', property2: '$PROPERTY2', property3:'$PROPERTY3'})
        </script>
    </query>
 
    <!-- QUERY SEATABLE AND IMPORT RELATIONSHIPS -->
    <query connection-id="seatable1">
        SELECT UID, SOURCE_UID, TARGET_UID, TYPE, PROPERTY1, PROPERTY2, PROPERTY3 FROM RELATIONSHIPS
        <script connection-id="neo4j">
            MATCH (n {uid:'$SOURCE_UID'}), (m {uid:'$TARGET_UID'})
            CREATE (n)-[r:`$TYPE` {uid:'$UID', property1:'$PROPERTY1', property2:'$PROPERTY2',
            property3:'$PROPERTY3'}]->(m)
        </script>
    </query>
 
    <!-- LOG RESULTS -->
    <query connection-id="neo4j">
        MATCH (n) RETURN count(n) as cnt
        <script connection-id="logInfo">
            Imported $cnt nodes.
        </script>
    </query>
 
    <query connection-id="neo4j">
        MATCH (n)-[r]->(m) RETURN count(r) as cnt
        <script connection-id="logInfo">
            Imported $cnt relationships.
        </script>
    </query>
 
</etl>

Script example: Insert a row into a base.

<!DOCTYPE etl SYSTEM "https://scriptella.org/dtd/etl.dtd">
<etl>
    <description>Graphlytic job</description>
    <properties>
        job_name=Graphlytic job
    </properties>
        
    <!-- CONNECTIONS -->
    <connection id="seatable1" driver="seatable">
        api_token=123456789abcdef.....
    </connection>
        
    <connection id="neo4j" driver="graphConnection">
        project_id=1
    </connection>
    
    <connection id="logInfo" driver="log">
        level=INFO
    </connection>
                
    <!-- JOB STEPS -->
    <script connection-id="logInfo">
        STARTING JOB "$job_name"
    </script>
    
    <!-- Inert a row into a base -->
    <script connection-id="seatable1">
        INSERT INTO NODES (UID, PROPERTY1, PROPERTY2, PROPERTY3, LABEL) VALUES (21, 'String property', 0, true, 'IMPORTED')
    </script>
    
</etl>