AWSTemplateFormatVersion: 2010-09-09
Description: Create Glue catalog tables for Neptune Athena Connector sample

Parameters:
  GlueDatabaseNameLPG:
    Type: String
    Default: graph-database
  GlueDatabaseNameRDF:
    Type: String
    Default: graph-database-rdf

Resources:

  GlueDatabaseLPG:
    Type: "AWS::Glue::Database"
    Properties:
      DatabaseInput:
        Name: !Ref GlueDatabaseNameLPG
        Description: nep_athena_connector_db
      CatalogId: !Ref AWS::AccountId
  GlueDatabaseRDF:
    Type: "AWS::Glue::Database"
    Properties:
      DatabaseInput:
        Name: !Ref GlueDatabaseNameRDF
        Description: nep_athena_connector_db
      CatalogId: !Ref AWS::AccountId

  AirportRDF:
    Type: "AWS::Glue::Table"
    DependsOn: GlueDatabaseRDF
    Properties:
      TableInput:
        Description: "airport rdf"
        TableType: "EXTERNAL_TABLE"
        Parameters: {
            "prefix_prop": "http://kelvinlawrence.net/air-routes/datatypeProperty/",
            "prefix_class": "http://kelvinlawrence.net/air-routes/class/",
            "componenttype": "rdf",
            "querymode": "class",
            "classuri": "class:Airport",
            "subject": "id",
            "strip_uri": "true",
            "preds_prefix": "prop",
            "separatorChar": ",",
          }
        StorageDescriptor:
          StoredAsSubDirectories: False
          Columns:
            - Name: id
              Type: string
            - Name: type
              Type: string
            - Name: code
              Type: string
            - Name: icao
              Type: string
            - Name: desc
              Type: string
            - Name: region
              Type: string
            - Name: runways
              Type: int
            - Name: longest
              Type: int
            - Name: elev
              Type: int
            - Name: country
              Type: string
            - Name: city
              Type: string
            - Name: lat
              Type: double
            - Name: lon
              Type: double
          Compressed: False
        Name: airport_rdf
      DatabaseName: !Ref GlueDatabaseNameRDF
      CatalogId: !Ref AWS::AccountId

  RouteRDF:
    Type: "AWS::Glue::Table"
    DependsOn: GlueDatabaseRDF
    Properties:
      TableInput:
        Description: "route rdf"
        TableType: "EXTERNAL_TABLE"
        Parameters: {
            "separatorChar": ",",
            "componenttype": "rdf",
            "querymode": "sparql",
            "sparql": "select ?incode ?outcode ?dist where {  ?resin op:route ?resout . GRAPH ?route { ?resin op:route ?resout } .  ?route prop:dist ?dist  . ?resin prop:code ?incode .?resout prop:code ?outcode . } ",
            "strip_uri": "true",
            "prefix_prop": "http://kelvinlawrence.net/air-routes/datatypeProperty/",
            "prefix_op": "http://kelvinlawrence.net/air-routes/objectProperty/",
          }
        StorageDescriptor:
          StoredAsSubDirectories: False
          Columns:
            - Name: incode
              Type: string
            - Name: outcode
              Type: string
            - Name: dist
              Type: int
          Compressed: False
        Name: route_rdf
      DatabaseName: !Ref GlueDatabaseNameRDF
      CatalogId: !Ref AWS::AccountId

  RouteRDFNoPfx:
    Type: "AWS::Glue::Table"
    DependsOn: GlueDatabaseRDF
    Properties:
      TableInput:
        Description: route route_rdf_nopfx
        TableType: "EXTERNAL_TABLE"
        Parameters: {
            "separatorChar": ",",
            "componenttype": "rdf",
            "querymode": "sparql",
            "sparql": "PREFIX prop: <http://kelvinlawrence.net/air-routes/datatypeProperty/>  PREFIX op: <http://kelvinlawrence.net/air-routes/objectProperty/> select ?incode ?outcode ?dist where {  ?resin op:route ?resout . GRAPH ?route { ?resin op:route ?resout } .  ?route prop:dist ?dist  . ?resin prop:code ?incode .?resout prop:code ?outcode . } ",
            "strip_uri": "true",
            "prefix_prop": "http://kelvinlawrence.net/air-routes/datatypeProperty/",
            "prefix_op": "http://kelvinlawrence.net/air-routes/objectProperty/",
          }
        StorageDescriptor:
          StoredAsSubDirectories: False
          Columns:
            - Name: incode
              Type: string
            - Name: outcode
              Type: string
            - Name: dist
              Type: int
          Compressed: False
        Name: route_rdf_nopfx
      DatabaseName: !Ref GlueDatabaseNameRDF
      CatalogId: !Ref AWS::AccountId

  Airport:
    Type: "AWS::Glue::Table"
    DependsOn: GlueDatabaseLPG
    Properties:
      TableInput:
        Description: "airport"
        TableType: "EXTERNAL_TABLE"
        Parameters: {
            "componenttype": "vertex",
            "separatorChar": ",",
            "glabel":"airport",
          }
        StorageDescriptor:
          StoredAsSubDirectories: False
          Columns:
            - Name: id
              Type: string
            - Name: type
              Type: string
            - Name: code
              Type: string
            - Name: icao
              Type: string
            - Name: desc
              Type: string
            - Name: region
              Type: string
            - Name: runways
              Type: int
            - Name: longest
              Type: int
            - Name: elev
              Type: int
            - Name: country
              Type: string
            - Name: city
              Type: string
            - Name: lat
              Type: double
            - Name: lon
              Type: double
          Compressed: False
        Name: airport
      DatabaseName: !Ref GlueDatabaseNameLPG
      CatalogId: !Ref AWS::AccountId

  Country:
    Type: "AWS::Glue::Table"
    DependsOn: GlueDatabaseLPG
    Properties:
      TableInput:
        Description: "country"
        TableType: "EXTERNAL_TABLE"
        Parameters: {
            "componenttype": "vertex",
            "separatorChar": ",",
            "glabel":"country",
          }
        StorageDescriptor:
          StoredAsSubDirectories: False
          Columns:
            - Name: id
              Type: string
            - Name: code
              Type: string
            - Name: desc
              Type: string
          Compressed: False
        Name: country
      DatabaseName: !Ref GlueDatabaseNameLPG
      CatalogId: !Ref AWS::AccountId

  Continent:
    Type: "AWS::Glue::Table"
    DependsOn: GlueDatabaseLPG
    Properties:
      TableInput:
        Description: "continent"
        TableType: "EXTERNAL_TABLE"
        Parameters: {
            "componenttype": "vertex",
            "separatorChar": ",",
            "glabel":"continent",
          }
        StorageDescriptor:
          StoredAsSubDirectories: False
          Columns:
            - Name: id
              Type: string
            - Name: code
              Type: string
            - Name: desc
              Type: string
          Compressed: False
        Name: continent
      DatabaseName: !Ref GlueDatabaseNameLPG
      CatalogId: !Ref AWS::AccountId

  Route:
    Type: "AWS::Glue::Table"
    DependsOn: GlueDatabaseLPG
    Properties:
      TableInput:
        Description: "route"
        TableType: "EXTERNAL_TABLE"
        Parameters: {
            "separatorChar": ",",
            "componenttype": "edge",
            "glabel": "route",
          }
        StorageDescriptor:
          StoredAsSubDirectories: False
          Columns:
            - Name: id
              Type: string
            - Name: out
              Type: string
            - Name: in
              Type: string
            - Name: dist
              Type: int
          Compressed: False
        Name: route
      DatabaseName: !Ref GlueDatabaseNameLPG
      CatalogId: !Ref AWS::AccountId

  CustomView:
    Type: "AWS::Glue::Table"
    DependsOn: GlueDatabaseLPG
    Properties:
      TableInput:
        Description: "custom view"
        TableType: "EXTERNAL_TABLE"
        Parameters: {
            "separatorChar": ",",
            "componenttype": "view",
            "query": "g.V().hasLabel(\"airport\").as(\"source\").out(\"route\").as(\"destination\").select(\"source\",\"destination\").by(\"code\").limit(10)",
          }
        StorageDescriptor:
          StoredAsSubDirectories: False
          Columns:
            - Name: source
              Type: string
            - Name: destination
              Type: string
          Compressed: False
        Name: customairport
      DatabaseName: !Ref GlueDatabaseNameLPG
      CatalogId: !Ref AWS::AccountId

Outputs:
  GlueDatabaseLPG: 
    Value: !Ref GlueDatabaseLPG
  GlueDatabaseRDF: 
    Value: !Ref GlueDatabaseRDF