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" ]