# Running SQL Queries in Sisense

You can run SQL queries to extract data from Sisense Datamodels. You can either use the SQL Runner, or use a REST API. Both options are explained below.

Note

This feature is only available to Administrators. You can allow additional user roles to access it by customizing user roles.

# SQL Syntax & Limitations

  • The syntax is standard SQL
  • Sisense supports SELECT queries only. You cannot use this interface for INSERT, UPDATE, DELETE and other SQL operations.
  • The table and column names are the same names that appear in your Datamodel

Example

Given the following Datamodel:

You could run this SQL statement:

SELECT "first name", "last name"
FROM customers

And get the following JSON response:

{
	"headers": [
		"first name",
		"last name"
	],
	"values": [
		[
			"Britney",
			"Britton"
		],
		[
			"Candace",
			"Horton"
		],
		[
			"Annie",
			"Thorisdottir"
		],
		[
			"Amy",
			"Ricana"
		]
	]
}

# Using REST API

You can run SQL queries using API calls from your own REST client such as Postman, or a script/application.

This is done via a GET HTTP request to the SQL endpoint's path:

GET /api/datasources/<datasource>/sql?query=<query>

Parameters

Name Location Type Required Details Example
datasource path string Yes Your Datamodel's title laptopsales
query query string Yes Your SQL query as a single-line string See below
format query string No Response format. Defaults to json and also accepts csv

The endpoint returns results in JSON format.

Example

To execute the following SQL query:

SELECT city, count(*)
FROM sales
GROUP BY city

against the laptopsales Datamodel, use the following URi:

http://localhost:8081/api/datasources/laptopsales/sql?query=select sales.city count(*) from sales group by sales.city

# Using SQL Runner

Sisense includes a simple UI to execute SQL queries, which can be useful for one-off operations and debugging.

To access the SQL Runner:

  1. Open Sisense web in a browser and log in.
  2. In your browser, navigate to /app/sqleditor#/datasource=<your datasource name>.
    For example:
    http://localhost:8081/app/sqleditor#/datasource=Training
    
  3. Enter your SQL query in the left panel and click execute. Results will be returned in the right panel

# Usage Analytics

Sisense Usage Analytics collects usage information for your Sisense system. This information is collected and stored in a CSV file. You can download this file with the SQL Runner with the following call:

http://localhost:8081/api/datasources/LocalHost/Usage%20Analytics%20Model/sql?query=select%20*%20from%20usage&format=csv 

In this example, LocalHost is the name of the server where the Usage Analytics ElastiCube is located. If you are connecting remotely, change this to the IP address or name of the server as defined in the Data Sources table in the Admin page of Sisense.