Querying Metadata

  • Capella Columnar
  • reference
    +
    You can review information about your Capella Columnar entities by querying the collections in the System.Metadata scope.

    For metadata introspection, the System.Metadata scope contains the following collections:

    • Database

    • Dataverse, for scope metadata

    • Dataset, for collection metadata

    • Function

    • Index

    • Link

    • Synonym

    Each collection contains the queryable metadata for that entity type.

    Each of these collection identifiers is a reserved word. As a result, you must escape these names with backticks (``), as shown in the examples that follow.

    For more information about metadata storage, see Metadata Storage.

    Examples

    This example returns the metadata for a single link object so that you can identify the field names for further querying:

      SELECT * FROM System.Metadata.`Link` LIMIT 1;

    Returns:

      {
        "Link": {
          "Name": "capellaLink",
          "Type": "COUCHBASE",
          "IsActive": true
        }
      }

    To get additional information about a link, you can use a DESCRIBE LINK statement.

    The next example returns the metadata for a specific collection. In its results, you can then verify the values for its Amazon S3 bucket—or ON clause—and path by inspecting the container and definition properties respectively.

      SELECT * from System.Metadata.`Dataset`
        WHERE DatasetName = "rockSongs";

    Returns:

    [
      {
        "Dataset": {
        "DatabaseName": "music",
        "DataverseName": "myPlaylist",
        "DatasetName": "rockSongs",
        "DatatypeDataverseName": "Metadata",
        "DatatypeName": "AnyObject",
        "DatasetType": "EXTERNAL",
        "GroupName": "music.myPlaylist.rockSongs",
        "CompactionPolicy": "",
        "CompactionPolicyProperties": [],
        "ExternalDetails": {
          "DatasourceAdapter": "LINK",
          "Properties": [
            {
              "Name": "container",
              "Value": "music"
            },
            {
              "Name": "name",
              "Value": "musicLink"
            },
            {
              "Name": "format",
              "Value": "json"
            },
            {
              "Name": "database",
              "Value": "music"
            },
            {
              "Name": "definition",
              "Value": "music/myPlaylist/rockSongs"
            },
            {
              "Name": "dataverse",
              "Value": "myPlaylist"
            }
          ],
          "LastRefreshTime": "2024-02-02T15:23:08.508",
          "TransactionState": 0
        },
        "Hints": [],
        "Timestamp": "Fri Feb 02 15:23:08 GMT 2024",
        "DatasetId": 117,
        "PendingOp": 0,
        "DatatypeDatabaseName": "System",
        "DatasetFormat": {
          "Format": "ROW"
          }
        }
      }
    ]

    The next example returns the qualified names of all collections that are not themselves in the System.Metadata scope.

      SELECT VALUE d.DatabaseName || '.' || d.DataverseName || '.' || d.DatasetName
      FROM System.Metadata.`Dataset` d
      WHERE d.DataverseName <> "Metadata";

    Returns:

    [
      "sampleAnalytics.Commerce.customers",
      "sampleAnalytics.Commerce.orders",
      "music.myPlaylist.rockSongs",
      "music.myPlaylist.countrySongs"
    ]