Skip to content

Instantly share code, notes, and snippets.

@michael-simons

michael-simons/WriteNeo4j.sql

Last active March 18, 2025 14:33
Show Gist options
  • Save michael-simons/3f75b5ab9f8dc4d07bec03d80260a3c9 to your computer and use it in GitHub Desktop.
Save michael-simons/3f75b5ab9f8dc4d07bec03d80260a3c9 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. michael-simons revised this gist Mar 18, 2025. 1 changed file with 2 additions and 2 deletions.
    4 changes: 2 additions & 2 deletions neo4j_jdbc_meets_snowflake.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -101,7 +101,7 @@ $$;

    select * from
    table(READ_NEO4J(
    object_construct('url', 'jdbc:neo4j+s://xyz.databases.neo4j.io/databasename', 'use_secrets', 'true'),
    {'url': 'jdbc:neo4j+s://xyz.databases.neo4j.io/xyz', 'use_secrets': 'true'},
    $$
    MATCH (n:Movie)<-[:ACTED_IN]-(p:Person)
    WHERE n.released >= 2000
    @@ -112,7 +112,7 @@ $$));

    select * from
    table(READ_NEO4J(
    object_construct('url', 'jdbc:neo4j+s://xyz.databases.neo4j.io/databasename?enableSQLTranslation=true', 'use_secrets', 'true'),
    {'url': 'jdbc:neo4j+s://xyz.databases.neo4j.io/databasename?enableSQLTranslation=true', 'use_secrets': 'true'},
    $$
    select p.name as name, m.title as title
    from Person p natural join ACTED_IN natural join Movie m
  2. michael-simons revised this gist Mar 18, 2025. 1 changed file with 16 additions and 0 deletions.
    16 changes: 16 additions & 0 deletions using_write_and_read.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,16 @@
    select write_neo4j(
    {'url': 'jdbc:neo4j+s://xyz.databases.neo4j.io/xyz', 'use_secrets': 'true'},
    transform(ARRAY_GENERATE_RANGE(1,20), v -> {'labels': ['Label1', 'Label2'], 'properties': {'item': v, 'other': 'some other value'}})
    );


    select * from
    table(READ_NEO4J(
    {'url': 'jdbc:neo4j+s://xyz.databases.neo4j.io/xyz', 'use_secrets': 'true'},
    $$
    MATCH (n:Label1:Label2)
    RETURN n.item
    ORDER BY n.item
    $$
    )
    );
  3. michael-simons revised this gist Mar 18, 2025. 1 changed file with 53 additions and 0 deletions.
    53 changes: 53 additions & 0 deletions WriteNeo4j.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,53 @@
    CREATE OR REPLACE FUNCTION write_neo4j(jdbcConfig OBJECT, nodes VARIANT)
    RETURNS ARRAY(INTEGER) NULL
    LANGUAGE JAVA
    RUNTIME_VERSION = '17'
    PACKAGES = ('com.snowflake:snowpark:1.8.0')
    IMPORTS = ('@NEO4J_JARS/drivers/neo4j-jdbc-full-bundle-6.2.0.jar')
    EXTERNAL_ACCESS_INTEGRATIONS = (ext_neo4j_access_integration)
    SECRETS = ('cred' = ext_neo4j_credentials )
    HANDLER = 'WriteNeo4j.createNodes'
    AS $$
    import java.sql.DriverManager;
    import java.sql.SQLException;
    import java.util.Map;
    import java.util.stream.Collectors;

    import com.snowflake.snowpark_java.types.SnowflakeSecrets;
    import com.snowflake.snowpark_java.types.Variant;

    public class WriteNeo4j {

    public int[] createNodes(Map<String, String> jdbcConfig, Variant nodes) throws SQLException {

    String jdbcUrl = jdbcConfig.get("url");
    String username;
    String password;

    if ("true".equals(jdbcConfig.get("use_secrets"))) {
    SnowflakeSecrets sfSecrets = SnowflakeSecrets.newInstance();
    var secret = sfSecrets.getUsernamePassword("cred");
    username = secret.getUsername();
    password = secret.getPassword();
    } else {
    username = jdbcConfig.get("username");
    password = jdbcConfig.get("password");
    }

    try (var connection = DriverManager.getConnection(jdbcUrl, username, password);
    var statement = connection.prepareStatement("CREATE (n:$($1)) SET n = $2");
    ) {
    for (var node : nodes.asArray()) {
    var labels = node.asMap().get("labels").asList().stream().map(Variant::asString);
    var properties = node.asMap().get("properties").asMap().entrySet()
    .stream().collect(Collectors.toMap(Map.Entry::getKey, e -> e.getValue().asString()));
    statement.setObject(1, labels);
    statement.setObject(2, properties);
    statement.addBatch();
    }

    return statement.executeBatch();
    }
    }
    }
    $$;
  4. michael-simons renamed this gist Mar 14, 2025. 1 changed file with 57 additions and 8 deletions.
    65 changes: 57 additions & 8 deletions gistfile1.txt → neo4j_jdbc_meets_snowflake.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,24 +1,48 @@
    CREATE OR REPLACE FUNCTION READ_JDBC(OPTION OBJECT, query STRING)
    CREATE OR REPLACE NETWORK RULE ext_neo4j_network_rule
    MODE = EGRESS
    TYPE = HOST_PORT
    VALUE_LIST = ('xyz.databases.neo4j.io:7687');


    CREATE OR REPLACE SECRET ext_neo4j_credentials
    TYPE = password
    USERNAME = 'xxx'
    PASSWORD = '*******';


    CREATE OR REPLACE EXTERNAL ACCESS INTEGRATION ext_neo4j_access_integration
    ALLOWED_NETWORK_RULES = (ext_neo4j_network_rule)
    ALLOWED_AUTHENTICATION_SECRETS=(ext_neo4j_credentials)
    ENABLED = true;


    CREATE OR REPLACE FUNCTION read_neo4j(OPTION OBJECT, query STRING)
    RETURNS TABLE(data OBJECT)
    LANGUAGE JAVA
    RUNTIME_VERSION = '17'
    IMPORTS = ('@NEO4J_JARS/drivers/neo4j-jdbc-full-bundle-6.2.0.jar')
    // EXTERNAL_ACCESS_INTEGRATIONS = (external_database_network_rule_ext_int)
    SECRETS = ('cred' = external_database_cred )
    EXTERNAL_ACCESS_INTEGRATIONS = (ext_neo4j_access_integration)
    SECRETS = ('cred' = ext_neo4j_credentials )
    HANDLER = 'JdbcDataReader'
    AS $$
    import java.io.PrintWriter;
    import java.io.StringWriter;
    import java.sql.DriverManager;
    import java.sql.SQLException;
    import java.util.ArrayList;
    import java.util.HashMap;
    import java.util.Map;
    import java.util.stream.Stream;

    import com.snowflake.snowpark_java.types.SnowflakeSecrets;

    public class JdbcDataReader {

    record OutputRow(Map<String, String> data) {
    public Map<String, String> getData() {
    return data;
    static final class OutputRow {
    public final Map<String, String> data;

    OutputRow(Map<String, String> data) {
    this.data = data;
    }
    }

    @@ -64,8 +88,33 @@ public class JdbcDataReader {
    return streamBuilder.build();
    } catch (SQLException ex) {
    ex.printStackTrace();
    return Stream.of(new OutputRow(Map.of("ERROR", ex.toString())));
    var sw = new StringWriter();
    var pw = new PrintWriter(sw);
    ex.printStackTrace(pw);
    pw.flush();
    return Stream.of(new OutputRow(Map.of("ERROR", sw.toString())));
    }
    }
    }
    $$;
    $$;


    select * from
    table(READ_NEO4J(
    object_construct('url', 'jdbc:neo4j+s://xyz.databases.neo4j.io/databasename', 'use_secrets', 'true'),
    $$
    MATCH (n:Movie)<-[:ACTED_IN]-(p:Person)
    WHERE n.released >= 2000
    RETURN p.name, n.title AS movies
    ORDER BY p.name
    $$));


    select * from
    table(READ_NEO4J(
    object_construct('url', 'jdbc:neo4j+s://xyz.databases.neo4j.io/databasename?enableSQLTranslation=true', 'use_secrets', 'true'),
    $$
    select p.name as name, m.title as title
    from Person p natural join ACTED_IN natural join Movie m
    order by p.name
    $$));
  5. michael-simons created this gist Mar 14, 2025.
    71 changes: 71 additions & 0 deletions gistfile1.txt
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,71 @@
    CREATE OR REPLACE FUNCTION READ_JDBC(OPTION OBJECT, query STRING)
    RETURNS TABLE(data OBJECT)
    LANGUAGE JAVA
    RUNTIME_VERSION = '17'
    IMPORTS = ('@NEO4J_JARS/drivers/neo4j-jdbc-full-bundle-6.2.0.jar')
    // EXTERNAL_ACCESS_INTEGRATIONS = (external_database_network_rule_ext_int)
    SECRETS = ('cred' = external_database_cred )
    HANDLER = 'JdbcDataReader'
    AS $$
    import java.sql.DriverManager;
    import java.sql.SQLException;
    import java.util.ArrayList;
    import java.util.HashMap;
    import java.util.Map;
    import java.util.stream.Stream;

    public class JdbcDataReader {

    record OutputRow(Map<String, String> data) {
    public Map<String, String> getData() {
    return data;
    }
    }

    public static Class<?> getOutputClass() {
    return OutputRow.class;
    }

    public Stream<OutputRow> process(Map<String, String> jdbcConfig, String query) {
    String jdbcUrl = jdbcConfig.get("url");
    String username;
    String password;

    if ("true".equals(jdbcConfig.get("use_secrets"))) {
    SnowflakeSecrets sfSecrets = SnowflakeSecrets.newInstance();
    var secret = sfSecrets.getUsernamePassword("cred");
    username = secret.getUsername();
    password = secret.getPassword();
    } else {
    username = jdbcConfig.get("username");
    password = jdbcConfig.get("password");
    }

    try (var connection = DriverManager.getConnection(jdbcUrl, username, password);
    var statement = connection.createStatement();
    var resultSet = statement.executeQuery(query);
    ) {
    var metaData = resultSet.getMetaData();
    var columnNames = new ArrayList<String>();
    var columnCount = metaData.getColumnCount();
    for (int i = 1; i <= columnCount; i++) {
    columnNames.add(metaData.getColumnName(i));
    }

    var streamBuilder = Stream.<OutputRow>builder();
    while (resultSet.next()) {
    Map<String, String> rowMap = new HashMap<>();
    for (String columnName : columnNames) {
    String columnValue = resultSet.getString(columnName);
    rowMap.put(columnName, columnValue);
    }
    streamBuilder.add(new OutputRow(rowMap));
    }
    return streamBuilder.build();
    } catch (SQLException ex) {
    ex.printStackTrace();
    return Stream.of(new OutputRow(Map.of("ERROR", ex.toString())));
    }
    }
    }
    $$;