ETL: Neo4j to Mail

images/download/attachments/18219180/etl_4.png

Load data from Neo4j using Cypher query, transform every record of result by Groovy and append every record into result string. Send string as html mail to several recipients.

Used drivers:

  • Neo4j

  • Groovy

  • Mail

<!DOCTYPE etl SYSTEM "http://scriptella.javaforge.com/dtd/etl.dtd">
<etl>
<connection id="neo4j" driver="neo4j" url="bolt://localhost:7687" user="neo4j" password="admin">
query_empty_value=<!-- string that represents empty values from query result. If query_empty_value is not defined and null value is returned from result then name of column is returned instead of null -->
</connection>
<connection id="mail" driver="mail2" url="mailto:someaddress@somecompany.com,otheraddress@somecompany.com" classpath="mail.jar:activation.jar">
mail.smtp.host=smtp.somecompany.com
mail.user=mailuser@somecompany.com
mail.password=somepassword
mail.from=Administrator
type=html
subject=Mail test
</connection>
<connection id="groovy" driver="script">language=groovy</connection>
<script connection-id="groovy">
<![CDATA[
etl.globals['text'] = ''
etl.globals['count'] = 0
]]>
</script>
<script connection-id="groovy">
<![CDATA[
etl.globals['text'] = etl.globals['text'] + '<table>'
]]>
</script>
<query connection-id="neo4j">
match (n1)-[r:RELATED]->(n2) where n1.type = 'Service' and n2.type='Application' RETURN count(n1) as count
<script connection-id="groovy">
<![CDATA[
etl.globals['count'] = count
]]>
</script>
</query>
<query connection-id="neo4j">
match (n1)-[r:RELATED]->(n2) where n1.type = 'Service' and n2.type='Application' return id(n1) as id, n1.logicalName as logicalName
<script connection-id="groovy">
<![CDATA[
etl.globals['text'] = etl.globals['text'] + '<tr>' + '<td>' + id + '</td>' + '<td>' + logicalName + '</td>' + '</tr>'
]]>
</script>
</query>
<script connection-id="groovy">
<![CDATA[
etl.globals['text'] = etl.globals['text'] + '</table>'
]]>
</script>
<script connection-id="mail" if="etl.globals['count'] gt 0">
<![CDATA[
<html>
<head>
<style>
table, th, td {
border: 1px solid black;
}
</style>
</head>
<body>
]]>
match (n1)-[r:RELATED]->(n2) where n1.type = 'Service' and n2.type='Application' return id(n1) as id, n1.logicalName as logicalName
${etl.globals['text']}
<![CDATA[
</body>
</html>
]]>
</script>
</etl>