Accessing Your Data Via REST Endpoint (2.12)

InterSystems Data Studio™ supports accessing data via REST endpoints through the use of managed queries. Managed queries are predefined SQL queries that have been saved and made available through the user interface, allowing you to explore and extract data without writing SQL or crafting manual API requests.

Note that the user roles govern both your ability to see queries and your ability to create them, as well as your ability to access the REST endpoints for managed queries. Each user can only see the results of queries they’ve run themselves; results from other users' tests are not shared.

Creating a Query

To create a query via the user interface:

Click the Data Catalog icon in the application menu.
Click Query Management.
Click Create Query.
Type a unique query name in the Query Name text box.

Tip:

The query name must be HTTP compliant because it becomes part of the REST endpoint URL. Avoid spaces or special characters.
Type an SQL SELECT, UPDATE, INSERT, or DELETE statement.
Click Run Query to test the query. This option is also available after the query is saved, or when you're editing an existing query.
Click Submit.

Note:

If the Submit button is disabled, your query is invalid. Check the query for errors and correct them to re-enable the button.

Each managed query is associated with a REST endpoint, which you can call from an external application or script.

To update a query, click its name in the user interface, then click Edit Query to modify the SQL statement.

Accessing a Query via REST

To access tables within Data Studio via REST Endpoint:

Click the Data Catalog icon in the application menu.
Click Query Management.
Click the Menu icon next to the query name.
Click Copy REST URL.

Tip:

To test a managed query REST endpoint directly in a browser, you must already be authenticated to Data Studio in the same browser session.
Send a request to that URL from the API platform of your choice.

Note:

Managed queries are executed over an ODBC connection. In most cases, this behaves just like any other SQL execution, but note that ODBC-specific behavior may apply in some edge cases.

The results are returned as JSON.

Running a Query With Parameters

After creating a query, you can run it and supply temporary parameter values. You can do so either through the UI or REST endpoint. To do so via the UI:

Click the Data Catalog icon in the application menu.
Click Query Management.
Click the name of the query you want to run.
Click Run Query. If the query you are running has parameters, a dialog box appears.
Type values for each parameter in the dialog. When you click a text box, the corresponding part of the query is highlighted on the left side of the dialog, and the placeholder (?) is replaced with your value as you type.
Click Submit. Any rows that match your parameters are returned.

To run a query via REST, do the following:

Get the URL of the REST endpoint you want to use.
Send a GET request to the URL from your API tool of choice. If the query requires parameters, include them as query arguments (for example, ?p1=<value>&p2=<value>).

Tip:

Parameters are positional and must be passed in order.
The response is returned as JSON Lines.

Note:

To run a query via REST, you must have the required SQL privileges on the schema or table referenced in the query. For example, to run SELECT * FROM table.example, at least one of your roles must have the SELECT privilege on the table.example table or on the table schema.