Graphlytic 5.2.0

1. Description

This driver connects to a 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, which will be used as a reference with connection-id within scripts and queries	id=”seatableConnection”
driver	Use “seatable”.	driver=”seatable”

See the “" 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. The name of the result parameters corresponds to the select statement. When * was used (eg SELECT * FROM users), then 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 mainly for "write" statements.

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 execute two queries and create 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>