// ---
// jupyter:
//   jupytext:
//     text_representation:
//       extension: .js
//       format_name: light
//       format_version: '1.5'
//       jupytext_version: 1.14.4
//   kernelspec:
//     display_name: Arc
//     language: javascript
//     name: arc
// ---

// %conf 
numRows=10 
truncate=100

// %env 
ETL_CONF_JOB_URL=/home/jovyan/examples/movie_graph
ETL_CONF_BASE_URL=https://raw.githubusercontent.com/arangodb/example-datasets/master/Graphs

{
  "type": "HTTPExtract",
  "name": "get nodes",
  "environments": [
    "production",
    "test"
  ],
  "inputURI": ${ETL_CONF_BASE_URL}"/IMDB/nodes.json",
  "outputView": "nodes_raw",
  "persist": true
}

{
  "type": "SQLTransform",
  "name": "split the body",
  "environments": [
    "production",
    "test"
  ],
  "inputURI": ${ETL_CONF_JOB_URL}"/split_table.sql",
  "outputView": "nodes_split",
  "sqlParams": {
    "inputView": "nodes_raw"
  }
}

{
  "type": "JSONExtract",
  "name": "users",
  "environments": ["production", "test"],
  "inputView": "nodes_split",
  "inputField": "col",
  "outputView": "nodes",
  "persist": true
}

// %sql outputView=person
SELECT 
  _key AS id
  ,label AS name
  ,_key AS key
FROM nodes
WHERE type = 'Person'

// %sql outputView=movie
SELECT 
  _key AS id
  ,label AS name
  ,_key AS key
  ,from_unixtime(releaseDate/1000) AS releaseDate
FROM nodes
WHERE type = 'Movie'

{
  "type": "HTTPExtract",
  "name": "get edges",
  "environments": [
    "production",
    "test"
  ],
  "inputURI": ${ETL_CONF_BASE_URL}"/IMDB/edges.json",
  "outputView": "edges_raw",
  "persist": true  
}

{
  "type": "SQLTransform",
  "name": "split the body",
  "environments": [
    "production",
    "test"
  ],
  "inputURI": ${ETL_CONF_JOB_URL}"/split_table.sql",
  "outputView": "edges_split",
  "sqlParams": {
    "inputView": "edges_raw"
  }
}

{
  "type": "JSONExtract",
  "name": "users",
  "environments": ["production", "test"],
  "inputView": "edges_split",
  "inputField": "col",
  "outputView": "edges",
  "persist": true
}

// %sql outputView=directed
SELECT DISTINCT
  _key AS id
  ,_from as source
  ,_to AS target
FROM edges
WHERE `$label`='DIRECTED'

// %sql outputView=acts_in
SELECT DISTINCT
  _key AS id
  ,_from as source
  ,_to AS target
FROM edges
WHERE `$label`='ACTS_IN'

{
  "type": "GraphTransform",
  "name": "create imdb graph",
  "environments": [
    "production",
    "test"
  ],
  "nodes": [
    {"label": "Person", "view": "person"},
    {"label": "Movie", "view": "movie"},
  ],
  "relationships": [
    {"type": "ACTS_IN", "view": "acts_in"},
    {"type": "DIRECTED", "view": "directed"}
  ],
  "outputGraph": "imdb",
  "persist": true
} 

// %cypher
FROM session.imdb
MATCH (p0:Person)-[:ACTS_IN]->(m:Movie)<-[:ACTS_IN]-(p1:Person)
WHERE p0.name = '<NAME>'
AND p0.key <> p1.key
RETURN p0.name, p1.name
ORDER BY m.releaseDate



StarcoderdataJupyterScriptsDedupFiltered