StoredProcedure¶

{
  "$id": "https://open-metadata.org/schema/entity/data/storedProcedure.json",
  "$schema": "http://json-schema.org/draft-07/schema#",
  "title": "StoredProcedure",
  "$comment": "@om-entity-type",
  "description": "A `StoredProcedure` entity that contains the set of code statements with an assigned name and is defined in a `Database Schema`.",
  "type": "object",
  "javaType": "org.openmetadata.schema.entity.data.StoredProcedure",
  "javaInterfaces": [
    "org.openmetadata.schema.EntityInterface"
  ],

  "definitions": {
    "storedProcedureType": {
      "javaType": "org.openmetadata.schema.type.StoredProcedureType",
      "description": "This schema defines the type of the type of Procedures",
      "type": "string",
      "default": "StoredProcedure",
      "enum": [
        "StoredProcedure",
        "UDF",
        "StoredPackage",
        "Function"
      ]
    },
    "storedProcedureCode": {
      "properties": {
        "language": {
          "javaType": "org.openmetadata.schema.type.StoredProcedureLanguage",
          "description": "This schema defines the type of the language used for Stored Procedure's Code.",
          "type": "string",
          "enum": [
            "SQL",
            "Java",
            "JavaScript",
            "Python",
            "External"
          ]
        },
        "code": {
          "javaType": "org.openmetadata.schema.type.StoredProcedureCode",
          "description": "This schema defines the type of the language used for Stored Procedure's Code.",
          "type": "string"
        }
      }
    }
  },

  "properties": {
    "id": {
      "description": "Unique identifier of the StoredProcedure.",
      "$ref": "../../type/basic.json#/definitions/uuid"
    },
    "name": {
      "description": "Name of Stored Procedure.",
      "$ref": "../../type/basic.json#/definitions/entityName"
    },
    "fullyQualifiedName": {
      "description": "Fully qualified name of a Stored Procedure.",
      "$ref": "../../type/basic.json#/definitions/fullyQualifiedEntityName"
    },
    "displayName": {
      "description": "Display Name that identifies this Stored Procedure.",
      "type": "string"
    },
    "description": {
      "description": "Description of a Stored Procedure.",
      "$ref": "../../type/basic.json#/definitions/markdown"
    },
    "storedProcedureCode": {
      "description": "Stored Procedure Code.",
      "$ref": "#/definitions/storedProcedureCode"
    },
    "version": {
      "description": "Metadata version of the Stored Procedure.",
      "$ref": "../../type/entityHistory.json#/definitions/entityVersion"
    },
    "dataProducts": {
      "description": "List of data products this entity is part of.",
      "$ref": "../../type/entityReferenceList.json"
    },
    "updatedAt": {
      "description": "Last update time corresponding to the new version of the entity in Unix epoch time milliseconds.",
      "$ref": "../../type/basic.json#/definitions/timestamp"
    },
    "storedProcedureType": {
      "description": "Type of the Stored Procedure.",
      "$ref": "#/definitions/storedProcedureType",
      "default": "StoredProcedure"
    },
    "updatedBy": {
      "description": "User who made the query.",
      "type": "string"
    },
    "impersonatedBy": {
      "description": "Bot user that performed the action on behalf of the actual user.",
      "$ref": "../../type/basic.json#/definitions/impersonatedBy"
    },
    "href": {
      "description": "Link to this Query resource.",
      "$ref": "../../type/basic.json#/definitions/href"
    },
    "changeDescription": {
      "description": "Change that lead to this version of the entity.",
      "$ref": "../../type/entityHistory.json#/definitions/changeDescription"
    },
    "incrementalChangeDescription": {
      "description": "Change that lead to this version of the entity.",
      "$ref": "../../type/entityHistory.json#/definitions/changeDescription"
    },
    "databaseSchema": {
      "description": "Reference to Database Schema that contains this stored procedure.",
      "$ref": "../../type/entityReference.json"
    },
    "database": {
      "description": "Reference to Database that contains this stored procedure.",
      "$ref": "../../type/entityReference.json"
    },
    "service": {
      "description": "Link to Database service this table is hosted in.",
      "$ref": "../../type/entityReference.json"
    },
    "serviceType": {
      "description": "Service type this table is hosted in.",
      "$ref": "../services/databaseService.json#/definitions/databaseServiceType"
    },
    "deleted": {
      "description": "When `true` indicates the entity has been soft deleted.",
      "type": "boolean",
      "default": false
    },
    "owners": {
      "description": "Owners of this Stored Procedure.",
      "$ref": "../../type/entityReferenceList.json",
      "default": null
    },
    "followers": {
      "description": "Followers of this Stored Procedure.",
      "$ref": "../../type/entityReferenceList.json"
    },
    "votes": {
      "description": "Votes on the entity.",
      "$ref": "../../type/votes.json"
    },
    "code": {
      "description": "SQL Query definition.",
      "$ref": "../../type/basic.json#/definitions/sqlQuery"
    },
    "tags": {
      "description": "Tags for this SQL query.",
      "type": "array",
      "items": {
        "$ref": "../../type/tagLabel.json"
      },
      "default": []
    },
    "extension": {
      "description": "Entity extension data with custom attributes added to the entity.",
      "$ref": "../../type/basic.json#/definitions/entityExtension"
    },
    "sourceUrl": {
      "description": "Source URL of database schema.",
      "$ref": "../../type/basic.json#/definitions/sourceUrl"
    },
    "domains": {
      "description": "Domains the Stored Procedure belongs to. When not set, the Stored Procedure inherits the domain from the database schemna it belongs to.",
      "$ref": "../../type/entityReferenceList.json"
    },
    "lifeCycle": {
      "description": "Life Cycle properties of the entity",
      "$ref": "../../type/lifeCycle.json"
    },
    "certification": {
      "$ref": "../../type/assetCertification.json"
    },
    "sourceHash": {
      "description": "Source hash of the entity",
      "type": "string",
      "minLength": 1,
      "maxLength": 32
    },
    "processedLineage": {
      "description": "Processed lineage for the stored procedure",
      "type": "boolean",
      "default": false
    },
    "entityStatus": {
      "description": "Status of the StoredProcedure.",
      "$ref": "../../type/status.json"
    }
  },

  "required": ["id", "name", "storedProcedureCode"]
}

{
  "@context": {
    "@vocab": "https://open-metadata.org/schema/",
    "om": "https://open-metadata.org/schema/",
    "rdfs": "http://www.w3.org/2000/01/rdf-schema#",
    "xsd": "http://www.w3.org/2001/XMLSchema#",

    "StoredProcedure": "om:StoredProcedure",
    "name": {
      "@id": "om:storedProcedureName",
      "@type": "xsd:string"
    },
    "fullyQualifiedName": {
      "@id": "om:fullyQualifiedName",
      "@type": "xsd:string"
    },
    "displayName": {
      "@id": "om:displayName",
      "@type": "xsd:string"
    },
    "description": {
      "@id": "om:description",
      "@type": "xsd:string"
    },
    "storedProcedureType": {
      "@id": "om:storedProcedureType",
      "@type": "@vocab"
    },
    "storedProcedureCode": {
      "@id": "om:storedProcedureCode",
      "@type": "@id"
    },
    "language": {
      "@id": "om:storedProcedureLanguage",
      "@type": "@vocab"
    },
    "code": {
      "@id": "om:code",
      "@type": "xsd:string"
    },
    "databaseSchema": {
      "@id": "om:belongsToSchema",
      "@type": "@id"
    },
    "database": {
      "@id": "om:belongsToDatabase",
      "@type": "@id"
    },
    "service": {
      "@id": "om:belongsToService",
      "@type": "@id"
    },
    "owners": {
      "@id": "om:ownedBy",
      "@type": "@id"
    },
    "domains": {
      "@id": "om:inDomains",
      "@type": "@id"
    },
    "followers": {
      "@id": "om:hasFollowers",
      "@type": "@id"
    },
    "votes": {
      "@id": "om:hasVotes",
      "@type": "@id"
    },
    "lifeCycle": {
      "@id": "om:hasLifeCycle",
      "@type": "@id"
    },
    "certification": {
      "@id": "om:hasCertification",
      "@type": "@id"
    },
    "processedLineage": {
      "@id": "om:processedLineage",
      "@type": "xsd:boolean"
    },
    "entityStatus": {
      "@id": "om:entityStatus",
      "@type": "@id"
    },
    "tags": {
      "@id": "om:hasTag",
      "@type": "@id",
      "@container": "@set"
    }
  }
}

{
  "@context": "https://open-metadata.org/context/storedProcedure.jsonld",
  "@type": "StoredProcedure",
  "@id": "https://example.com/data/procedures/calculate_order_total",

  "name": "calculate_order_total",
  "fullyQualifiedName": "postgres_prod.ecommerce.public.calculate_order_total",
  "displayName": "Calculate Order Total",
  "description": "Calculates the total amount for an order including tax and shipping",
  "storedProcedureType": "Function",

  "storedProcedureCode": {
    "language": "SQL",
    "code": "CREATE OR REPLACE FUNCTION calculate_order_total(p_order_id BIGINT)\nRETURNS NUMERIC AS $$\nDECLARE\n    v_total NUMERIC;\nBEGIN\n    SELECT SUM(quantity * price) + tax + shipping\n    INTO v_total\n    FROM order_items\n    WHERE order_id = p_order_id;\n    \n    RETURN v_total;\nEND;\n$$ LANGUAGE plpgsql;"
  },

  "databaseSchema": {
    "@id": "https://example.com/data/schemas/public",
    "@type": "DatabaseSchema",
    "name": "public"
  },

  "database": {
    "@id": "https://example.com/data/databases/ecommerce",
    "@type": "Database",
    "name": "ecommerce"
  },

  "service": {
    "@id": "https://example.com/services/postgres_prod",
    "@type": "DatabaseService",
    "name": "postgres_prod"
  },

  "owners": [
    {
      "@id": "https://example.com/users/john.smith",
      "@type": "User",
      "name": "john.smith",
      "displayName": "John Smith"
    }
  ],

  "domains": [
    {
      "@id": "https://example.com/domains/sales",
      "@type": "Domain",
      "name": "Sales"
    }
  ],

  "followers": [],
  "processedLineage": false,

  "tags": [
    {
      "@id": "https://open-metadata.org/tags/BusinessLogic/Critical",
      "tagFQN": "BusinessLogic.Critical"
    }
  ]
}