Reading Data Source Metadata

If you want to retrieve instance data from a source but are unsure about the data model, schema, or the exact names of columns and their data types, you can use the Graph Data Interface (GDI) to explore the source's metadata. The GDI can be used to return a list of the catalogs (schemas), models, columns, data types, and other data source information.

This topic describes the metadata query syntax and provides several example queries.

Metadata Query Syntax
Metadata Query Examples

Metadata Query Syntax

The following query syntax shows the structure of a metadata query. The clauses, patterns, and placeholders in blue are described below.

# PREFIX Clause 
PREFIX s:      <http://cambridgesemantics.com/ontologies/DataToolkit#>
PREFIX rdf:    <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX rdfs:   <http://www.w3.org/2000/01/rdf-schema#>
PREFIX xsd:    <http://www.w3.org/2001/XMLSchema#>
PREFIX owl:    <http://www.w3.org/2002/07/owl#>
PREFIX anzo:   <http://openanzo.org/ontologies/2008/07/Anzo#>
PREFIX zowl:   <http://openanzo.org/ontologies/2009/05/AnzoOwl#>
PREFIX dc:     <http://purl.org/dc/elements/1.1/> 

# Result Clause
SELECT *
WHERE
{
      # SERVICE Clause: Include the following service call
        SERVICE <http://cambridgesemantics.com/services/DataToolkit>
    {
        [] s:select ?metadata .

        ?data a s:source_type ;
            s:url "string" ;
            [ s:username "string" ; ]
            [ s:password "string" ; ]

        ?metadata a s:MetadataSource ;
            s:from ?data ;
		
      # The metadata selector below specifies the type of metadata to return.
        ?catalogs | ?fields | ?models [
            ?metadata_type datatype ;
            ... ;
        ] .
    }
}

Option	Data Type	Description
PREFIX Clause	N/A	The PREFIX clause declares the standard and custom prefixes for GDI queries. Generally, queries include the following prefixes (or a subset of them) plus any data-specific declarations: PREFIX s: <http://cambridgesemantics.com/ontologies/DataToolkit#> PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#> PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#> PREFIX xsd: <http://www.w3.org/2001/XMLSchema#> PREFIX owl: <http://www.w3.org/2002/07/owl#> PREFIX anzo: <http://openanzo.org/ontologies/2008/07/Anzo#> PREFIX zowl: <http://openanzo.org/ontologies/2009/05/AnzoOwl#> PREFIX dc: <http://purl.org/dc/elements/1.1/>
Result Clause	N/A	The result clause defines the results to return. For metadata queries, the result clause is typically `SELECT *`.
SERVICE Clause		Include the required GDI SERVICE call in the WHERE clause. The rest of the WHERE clause defines the patterns to look for in the source.
[] s:select ?metadata	N/A	Include this required triple pattern in metadata queries. The select property specifies the source that should be used to return data.
source_type	object	The `?data a s:source_type` triple pattern specifies the type of data source that the query will run against. For example, `?data a s:DbSource`, specifies that the source type is a database. The list below describes the available types: DbSource to connect to any type of database. FileSource for flat files. The supported file types are CSV and TSV, JSON, NDJSON, XML, Parquet, and SAS (SAS Transport XPT and SAS7BDAT formats) . The GDI automatically determines the file type from the file extensions. HttpSource to connect to HTTP endpoints. ElasticSource to connect to Elasticsearch indexes on an Elasticsearch server. KafkaSource to connect to Kafka streaming sources. Certain connection and input parameters are available based on the specified source type. For details about the options for your source, see Source-Specific Properties.
url	string	This property specifies the URL for the data source, such as the database URL, Elasticsearch URL, or HTTP endpoint URL. For file-based sources, the `url` property specifies the file system location of the source file or directory of files. For security, it is a best practice to reference connection information (such as the url, username, and password) from a Query Context so that the sensitive details are abstracted from any requests. In addition, using a Query Context makes connection details reusable across queries. See Using a Query Context for more information. For example, the triple patterns below reference keys from a Query Context: ?data a s:DbSource ; s:url "{{@Somedb.url}}" ; s:username "{{@Somedb.user}}" ; s:password "{{@Somedb.password}}" ;
username	string	If authentication is required to access the source, include this property to specify the user name.
password	string	This property lists the password for the given username.
catalogs	variable	This selector narrows the results to schema-related metadata such as the schema names. Even when additional metadata types (metadata_type datatype) are specified as objects, only catalog (schema) information is returned.
fields	variable	This selector is the broadest and most flexible option. Using the `fields` selector enables users to return any and all of the source metadata information, depending on the specified metadata types (metadata_type datatype).
models	variable	This selector narrows the results to model-related metadata such as the model names. Even when additional metadata types (metadata_type datatype) are specified as objects, only model information is returned.
metadata_type datatype	N/A	The triple patterns in the array for the metadata selector specify the type of metadata to return as well as the data type for the return value. The following list shows all of the valid options. You can include any combination of properties. The results that are returned depend on the type of data source and whether the information exists in the source. The parentheses around the data type are not required but are included in this document for readability. ?model (xsd:string): Returns model names in string format. For file sources, this property returns file names. ?field (xsd:string): Returns column names. ?catalog (xsd:string): Returns schema names. ?datatype (owl:Thing): Returns the data types of the columns. ?keys (xsd:string): Returns primary and foreign key columns. For compound keys, the GDI returns a comma-separated list of columns comprising the key. ?format (xsd:string): Returns the format of the source. ?cardinality (xsd:string): Returns the cardinality of relationships between tables: optional, many, or required. ?count (xsd:int): Returns the number of times the field appears in the source. ?order (xsd:int): Returns the order in which the field was encountered.

Metadata Query Examples

This section includes sample metadata queries that run against different types of data sources.

List Database Schemas
Explore a Database Schema
Explore a Directory of SAS Files
Explore an HTTP Endpoint
Explore a Directory of CSV Files

List Database Schemas

The query below sends a metadata query to a MySQL database to return a list of the schemas that are available:

PREFIX s:      <http://cambridgesemantics.com/ontologies/DataToolkit#>
PREFIX rdf:    <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX rdfs:   <http://www.w3.org/2000/01/rdf-schema#>
PREFIX xsd:    <http://www.w3.org/2001/XMLSchema#>
PREFIX owl:    <http://www.w3.org/2002/07/owl#>
PREFIX anzo:   <http://openanzo.org/ontologies/2008/07/Anzo#>
PREFIX zowl:   <http://openanzo.org/ontologies/2009/05/AnzoOwl#>
PREFIX dc:     <http://purl.org/dc/elements/1.1/>

SELECT *
WHERE
{
	SERVICE <http://cambridgesemantics.com/services/DataToolkit>
    {
	   [] s:select ?metadata .

	   ?data a s:DbSource ;
		 s:url "jdbc:mysql://10.100.2.9:5555/?user=root&password=Mysql1@#" .

	   ?metadata a s:MetadataSource ;
		 s:from ?data ;
	   ?catalogs [
		 ?catalog (xsd:string) ;
		 ?order (xsd:int) ;
	   ] .
   }
}
ORDER BY ?catalog