Metadata Query-API

The metadata query API is an interface to execute customer-specific, read-only SQL queries on the metadata SQL views and download the results in various common formats.

Note

In the following examples, the server https://www.myserver.com and the database test are used for illustration purposes (e.g. in the path https://www.myserver.com/api/test/queries/download). The server and the database must be replaced accordingly in all actual requests. Depending on the configuration, the URL may also have to be extended by the context path (e.g. https://www.myserver.com/dataspot/api/test/queries/download).

Note

The metadata query API requires a corresponding user to be configured (see server parameter DATASPOT_POSTGRES_READONLY_USER) with granted read-only permissions for all metadata SQL views. All SQL statements of the metadata query API are executed exclusively with this user.

Metamodel

The SQL queries are based on the metamodel. The metamodel documents all properties and relationships of metadata objects and provides the foundation for the metadata query API. The metamodel is available in the database meta at the following URL:

https://www.myserver.com/web/meta

Note

The database meta can be created in the admin console (https://www.myserver.com/web/admin).

Query API

The metadata query API is a programming interface that (analogous to the metadata query in the user interface) allows customer-specific, read-only SQL queries on the metadata SQL views to be executed:

The properties depend on the respective, custom SQL statement.
The queried metadata can be provided in various formats.

Functionality

The metadata query API executes a custom SQL statement on the metadata SQL views and returns the result in the selected format.

Attention

It is not possible to execute SQL statements that modify data. The transaction in PostgreSQL is started as a read-only transaction and is additionally protected against SQL statements that modify data.

Options

The metadata query API is available at the following URL:

https://www.myserver.com/api/test/queries/download

The SQL statement is embedded in JSON and transferred in the HTTP message (request payload).

{
  "sql": "select * from classifier_view"
}

The options are passed as query parameters in the URL.

Option `format`

The option format defines the format of the queried metadata.

`format`	Format	Media type
`CSV`	CSV	`text/csv`
`JSON`	JSON	`application/json`
`XML`	XML	`application/xml`

/download?format=CSV
/download?format=JSON
/download?format=XML

Option `language`

The option language defines the language.

`language`	Language
	Server default language
`de`	German
`en`	English

/download?language=de
/download?language=en

Note

Alternatively, the language can be defined in the HTTP header attribute Accept-Language, but will be overridden by the option language, if present.

Accept-Language: de
Accept-Language: en

Option `version`

The option version defines a point in time in the history, querying the metadata from the versioned metadata SQL views (e.g. collection_version_view) in the state at the selected point in time.

`version`	Description
	The metadata is queried in the most recent state.
Timestamp (Epoch) in millseconds	The metadata is queried in the state at the selected point in time.

/download?version=1698676897000

The option version is automatically applied to queries from the versioned metadata SQL views (e.g. collection_version_view), i.e. the timestamp does not have to be explicitly specified in the SQL statement.

select * from collection_version_view -- query all collections at the defined point in time

Note

Alternatively, the timestamp can be defined in the HTTP header attribute Accept-Version, but will be overridden by the option version, if present.

Accept-Version: 1698676897000

HTTP methods

The HTTP method PUT or POST defines whether the metadata is queried synchronously or asynchronously.

HTTP method	Description
`PUT`	The metadata query API is called synchronously. The request waits until the query is complete and then returns the queried metdata or an error message.
`POST`	The metadata query API is called asynchronously. The request starts a job that runs in the background and prepares the queried metdata in the selected format. When the job has completed, the queried metadata can be downloaded and the statistics and any existing logs can be queried.

Usage

For the following examples the open source tool curl is used. Alternatively, any library that can communicate over HTTP is also suitable.

Note

For illustration purposes, the HTTP messages in the examples are simplified or abbreviated.

Note

The metadata query API requires appropriate login credentials (e.g. <user> and <password>). Depending on the authentication method (Login ID and password, Single sign-on, Same sign-in, OpenID Connect) and used tools (e.g. curl), different mechanisms (e.g. basic authentication, bearer token) may be necessary to provide the credentials.

The user must exist and have administrator privileges.

Metadata query API (synchronous)

The metadata query API can be called with the HTTP method PUT and the option format to query metadata synchronously. An SQL statement <statement> can be embedded in JSON and transferred in the HTTP message (request payload) using the options -H "Content-Type: application/json" and -d.

curl -X PUT -u <user>:<password> -H "Content-Type: application/json" -d "{\"sql\":\"<statement>\"}" "https://www.myserver.com/api/test/queries/download?format=<format>"

The request waits until the query is complete and then returns the queried metadata or an error message.

Example: Query metadata (synchronous)

curl -X PUT -u jdoe:s3cr3t -H "Content-Type: application/json" -d "{\"sql\":\"select * from classifier_view\"}" "https://www.myserver.com/api/test/queries/download?format=CSV"
curl -X PUT -u jdoe:s3cr3t -H "Accept-Language: en" -H "Content-Type: application/json" -d "{\"sql\":\"select * from attribute_view\"}" "https://www.myserver.com/api/test/queries/download?format=JSON"

Example: Request and error handling (synchronous)

The metadata query API is called with the HTTP method PUT and the option format=CSV to query metadata synchronously. The SQL statement select id, label, date_created from classifier_view is embedded in JSON and transferred in the HTTP message (request payload) using the options -H "Content-Type: application/json" and -d.

curl -X PUT -u jdoe:s3cr3t -H "Content-Type: application/json" -d "{\"sql\":\"select id, label, date_created from classifier_view\"}" "https://www.myserver.com/api/test/queries/download?format=CSV"

PUT /api/test/queries/download?format=CSV HTTP/1.1
Host: www.myserver.com
Content-Type: application/json

{
    "sql": "select id, label, date_created from classifier_view"
}

The request performs the complete query and responds with the queried metadata:

HTTP/1.1 200
Content-Type: text/csv
Content-Disposition: filename="Query.csv"

id,label,date_created
47ba9729-5cf4-4601-9405-cc2e9c528897,Agent,2023-10-04T14:59:45.524
d6cb7df0-0b8f-4183-aa41-019252de17d2,Group,2023-10-04T14:59:45.524
17aeeca2-9ab6-4cc0-ab28-b709e8c295f7,Membership,2023-10-04T14:59:45.524
52349fdd-9e14-4fa2-8f6c-4f70ac85a461,Organization,2023-10-04T14:59:45.524

In case of an error (HTTP status code: 400), the HTTP response contains an error message:

HTTP/1.1 400
Content-Type: application/json

{
  "message": "ERROR: permission denied for relation classifier_view"
}

Metadata query API (asynchronous)

The metadata query API can be called with the HTTP method POST and the option format to query metadata asynchronously. An SQL statement <statement> can be embedded in JSON and transferred in the HTTP message (request payload) using the options -H "Content-Type: application/json" and -d.

curl -X POST -u <user>:<password> -H "Content-Type: application/json" -d "{\"sql\":\"<statement>\"}" "https://www.myserver.com/api/test/queries/download?format=<format>"

The request starts a job and returns the corresponding job ID. The job runs in the background and prepares the queried metadata in the selected format. The status of the job can be polled with the job ID. When the job has completed, the queried metadata can be downloaded and the statistics and any existing logs can be queried.

Example: Query metadata (asynchronous)

curl -X POST -u jdoe:s3cr3t -H "Content-Type: application/json" -d "{\"sql\":\"select * from classifier_view\"}" "https://www.myserver.com/api/test/queries/download?format=CSV"
curl -X POST -u jdoe:s3cr3t -H "Accept-Language: en" -H "Content-Type: application/json" -d "{\"sql\":\"select * from attribute_view\"}" "https://www.myserver.com/api/test/queries/download?format=JSON"

Example: Request and error handling (asynchronous)

The metadata query API is called with the HTTP method POST and the option format=CSV to query metadata asynchronously. The SQL statement select id, label, date_created from classifier_view is embedded in JSON and transferred in the HTTP message (request payload) using the options -H "Content-Type: application/json" and -d.

curl -X POST -u jdoe:s3cr3t -H "Content-Type: application/json" -d "{\"sql\":\"select id, label, date_created from classifier_view\"}" "https://www.myserver.com/api/test/queries/download?format=CSV"

POST /api/test/queries/download?format=CSV HTTP/1.1
Host: www.myserver.com
Content-Type: application/json

{
    "sql": "select id, label, date_created from classifier_view"
}

The request starts a job and responds with the job ID:

HTTP/1.1 200
Content-Type: application/json

{ "id":"90673f8b-bbf4-4379-a428-7feb8ff2a386" }

The status of the job can be polled with the job ID:

curl -X GET -u jdoe:s3cr3t "https://www.myserver.com/api/test/jobs/90673f8b-bbf4-4379-a428-7feb8ff2a386/statistics"

GET /api/test/jobs/90673f8b-bbf4-4379-a428-7feb8ff2a386/statistics HTTP/1.1
Host: www.myserver.com

When the job has completed (HTTP status code: 200), the HTTP response contains the job status COMPLETED and the statistics:

HTTP/1.1 200
Content-Type: application/json

{
  "messages": [
    {
      "level": "INFO",
      "message": "*Export metadata query result* successful (00:00:00)."
    },
    {
      "level": "INFO",
      "message": "4 rows exported."
    }
  ],
  "status": "COMPLETED"
}

In case of an error, the HTTP response contains the job status FAILED and the statistics:

HTTP/1.1 200
Content-Type: application/json

{
  "messages": [
    {
      "level": "ERROR",
      "message": "*Export metadata query result* failed (00:00:00)!"
    },
    {
      "level": "ERROR",
      "message": "1 *error* occured!"
    }
  ],
  "status": "FAILED"
}

The exact cause of the error can be found in the logs:

curl -X GET -u jdoe:s3cr3t "https://www.myserver.com/api/test/jobs/90673f8b-bbf4-4379-a428-7feb8ff2a386/logs"

GET /api/test/jobs/90673f8b-bbf4-4379-a428-7feb8ff2a386/logs HTTP/1.1
Host: www.myserver.com

HTTP/1.1 200
Content-Type: application/json

{
  "totalCount": 1,
  "data": [
    {
      "level": "ERROR",
      "message": "ERROR: permission denied for relation classifier_view"
    }
  ]
}

When the job has completed successfully, the queried metadata can be downloaded.

curl -X GET -u jdoe:s3cr3t "https://www.myserver.com/api/test/jobs/90673f8b-bbf4-4379-a428-7feb8ff2a386/download"

GET /api/test/jobs/90673f8b-bbf4-4379-a428-7feb8ff2a386/download HTTP/1.1
Host: www.myserver.com

HTTP/1.1 200
Content-Type: text/csv
Content-Disposition: filename="Query.csv"

id,label,date_created
47ba9729-5cf4-4601-9405-cc2e9c528897,Akteur,2023-10-04T14:59:45.524
d6cb7df0-0b8f-4183-aa41-019252de17d2,Gruppe,2023-10-04T14:59:45.524
17aeeca2-9ab6-4cc0-ab28-b709e8c295f7,Zugehörigkeit,2023-10-04T14:59:45.524
52349fdd-9e14-4fa2-8f6c-4f70ac85a461,Organisation,2023-10-04T14:59:45.524

Metamodel​

Query API​

Functionality​

Options​

Option format​

Option language​

Option version​

HTTP methods​

Usage​

Metadata query API (synchronous)​

Example: Query metadata (synchronous)​

Example: Request and error handling (synchronous)​

Metadata query API (asynchronous)​

Example: Query metadata (asynchronous)​

Example: Request and error handling (asynchronous)​

Metamodel

Query API

Functionality

Options

Option `format`

Option `language`

Option `version`

HTTP methods

Usage

Metadata query API (synchronous)

Example: Query metadata (synchronous)

Example: Request and error handling (synchronous)

Metadata query API (asynchronous)

Example: Query metadata (asynchronous)

Example: Request and error handling (asynchronous)