Analytics

Queries

You can use the queries resource to manage, update, or retrieve information about a SQL query in a given report. Or, use this resource to get a list of all queries in a report.

Query object

Properties
id required	string
token required	string
raw_query	string
created_at required	string
updated_at required	string
name required	string
last_run_id required	string
data_source_id required	string
explorations_count required	string
report_imports_count required	string
dbt_metric_id required	string
dbt_metric_token	string
mapping_id	string
_links required	object

All resource responses contain a set of links that describe other related resources or actions that you can take on this resource. A link is comprised of two main pieces: its name (which describes its relationship to this resource) and its href (the URL of the related action or resource). All resources have at least a _self link which is the URL that will return a representation of this resource.

charts
creator
metric
new_chart
new_query_table
query_runs
query_tables
report
report_runs
self

{
  "id": 42,
  "token": "hfs8g37",
  "created_at": "YYYY-MM-DDTHH:MM:SS.msZ",
  "name": "aute",
  "last_run_id": 1337,
  "data_source_id": 667,
  "raw_query": "do",
  "mapping_id": 109,
  "_links": {
    "self": {
      "href": "reprehenderit",
      "templated": false
    },
    "report": {
      "href": "adipisicing dolore",
      "templated": false
    },
    "report_runs": {
      "href": "exercitation commodo ad",
      "templated": false
    },
    "charts": {
      "href": "ut enim aliquip",
      "templated": false
    },
    "new_chart": {
      "href": "aute",
      "templated": false
    },
    "new_query_table": {
      "href": "mollit",
      "templated": false
    },
    "query_tables": {
      "href": "incididunt",
      "templated": false
    },
    "query_runs": {
      "href": "commodo enim consequat",
      "templated": false
    }
  },
  "_embedded": {}
}

Get a query

To retrieve information about a single query, send a GET request including the report and query tokens to the queries resource.

URL Params
account required	string	`Account` (`Workspace` or `User`) username
report required	string	`Report` token
query required	string	`Query` token

Responses
200	Query response
401	Unauthorized
404	Report not found

GET /{account}/reports/{report}/queries/{query}

curl --include \
     --header "Content-Type: application/json" \
     --header "Accept: application/hal+json" \
  'https://app.mode.com/api/{account}/reports/{report}/queries/{query}'

require 'http'

username = 'your_api_key'
password = 'your_api_secret'

headers = {
  content_type: 'application/json',
  accept: 'application/hal+json'
}

response = HTTP.basic_auth(user: username, pass: password)
               .headers(headers)
               .get('https://app.mode.com/api/{account}/reports/{report}/queries/{query}')
puts response

from urllib2 import Request, urlopen

headers = {
  'Content-Type': 'application/json',
  'Accept': 'application/hal+json'
}
request = Request('https://app.mode.com/api/{account}/reports/{report}/queries/{query}', headers=headers)

response_body = urlopen(request).read()
print(response_body)

var request = require('request');

request({
  method: 'GET',
  url: 'https://app.mode.com/api/{account}/reports/{report}/queries/{query}',
  headers: {
    'Content-Type': 'application/json',
    'Accept': 'application/hal+json'
  }}, function (error, response, body) {
  console.log('Status:', response.statusCode);
  console.log('Headers:', JSON.stringify(response.headers));
  console.log('Response:', body);
});

List queries

To return a list of all queries for a given Report, send a GET request to the queries resource.

URL Params
account required	string	`Account` (`Workspace` or `User`) username
report required	string	`Report` token

Responses
200	Query collection response
401	Unauthorized
404	Report not found

GET /{account}/reports/{report}/queries

curl --include \
     --header "Content-Type: application/json" \
     --header "Accept: application/hal+json" \
  'https://app.mode.com/api/{account}/reports/{report}/queries'

require 'http'

username = 'your_api_key'
password = 'your_api_secret'

headers = {
  content_type: 'application/json',
  accept: 'application/hal+json'
}

response = HTTP.basic_auth(user: username, pass: password)
               .headers(headers)
               .get('https://app.mode.com/api/{account}/reports/{report}/queries')
puts response

from urllib2 import Request, urlopen

headers = {
  'Content-Type': 'application/json',
  'Accept': 'application/hal+json'
}
request = Request('https://app.mode.com/api/{account}/reports/{report}/queries', headers=headers)

response_body = urlopen(request).read()
print(response_body)

var request = require('request');

request({
  method: 'GET',
  url: 'https://app.mode.com/api/{account}/reports/{report}/queries',
  headers: {
    'Content-Type': 'application/json',
    'Accept': 'application/hal+json'
  }}, function (error, response, body) {
  console.log('Status:', response.statusCode);
  console.log('Headers:', JSON.stringify(response.headers));
  console.log('Response:', body);
});

Create a query

To create a new query, send a POST request to the queries resource.

URL Params
account required	string	`Account` (`Workspace` or `User`) username
report required	string	`Report` token

POST Body Params Object: `query`
data_source_id required	integer	The id of the data source to use
raw_query required	string	The raw query to be executed Example: `SELECT * from the_way_down`
name	string	The name of the new `query` Example: `Turtles`

Responses
200	Query response
400	Bad request
401	Unauthorized
404	Report not found

POST /{account}/reports/{report}/queries

curl --include \
     --request POST \
     --header "Content-Type: application/json" \
     --header "Accept: application/hal+json" \
     --data-binary "{
  \"query\": {
    \"raw_query\": \"SELECT * from the_way_down\",
    \"data_source_id\": 84268941,
    \"name\": \"Turtles\"
  }
}" \
'https://app.mode.com/api/{account}/reports/{report}/queries'

require 'http'

values = {
  query: {
    raw_query: 'SELECT * from the_way_down',
    data_source_id: 84268941,
    name: 'Turtles'
  }
}

headers = {
  content_type: 'application/json',
  accept: 'application/hal+json'
}

response = HTTP.basic_auth(user: username, pass: password)
               .headers(headers)
               .post('https://app.mode.com/api/{account}/reports/{report}/queries', json: values)
puts response

from urllib2 import Request, urlopen

values = """
  {
    "query": {
      "raw_query": "SELECT * from the_way_down",
      "data_source_id": 84268941,
      "name": "Turtles"
    }
  }
"""

headers = {
  'Content-Type': 'application/json',
  'Accept': 'application/hal+json'
}
request = Request('https://app.mode.com/api/{account}/reports/{report}/queries', data=values, headers=headers)

response_body = urlopen(request).read()
print(response_body)

var request = require('request');

request({
  method: 'POST',
  url: 'https://app.mode.com/api/{account}/reports/{report}/queries',
  headers: {
    'Content-Type': 'application/json',
    'Accept': 'application/hal+json'
  },
  body: "{  \"query\": {    \"raw_query\": \"SELECT * from the_way_down\",    \"data_source_id\": 84268941,    \"name\": \"Turtles\"  }}"
}, function (error, response, body) {
  console.log('Status:', response.statusCode);
  console.log('Headers:', JSON.stringify(response.headers));
  console.log('Response:', body);
});

Update a query

To update a query, send a PATCH request including the corresponding report and query tokens to the queries resource.

URL Params
account required	string	`Account` (`Workspace` or `User`) username
report required	string	`Report` token
query required	string	`Query` token

POST Body Params Object: `query`
data_source_id	integer	The id of the data source to use
raw_query	string	The raw query to be executed Example: `SELECT * FROM small_table;`
name	string	The name of the new `query` Example: `All the small things`

Responses
200	Query response
400	Bad request
401	Unauthorized
404	Report not found

PATCH /{account}/reports/{report}/queries/{query}

curl --include \
     --request PATCH \
     --header "Content-Type: application/json" \
     --header "Accept: application/hal+json" \
     --data-binary "{
  \"query\": {
    \"data_source_id\": 91465259,
    \"raw_query\": \"SELECT * FROM small_table;\",
    \"name\": \"All the small things\"
  }
}" \
'https://app.mode.com/api/{account}/reports/{report}/queries/{query}'

require 'http'

values = {
  query: {
    data_source_id: 91465259,
    raw_query: 'SELECT * FROM small_table;',
    name: 'All the small things'
  }
}

headers = {
  content_type: 'application/json',
  accept: 'application/hal+json'
}

response = HTTP.basic_auth(user: username, pass: password)
               .headers(headers)
               .patch('https://app.mode.com/api/{account}/reports/{report}/queries/{query}', json: values)
puts response

from urllib2 import Request, urlopen

values = """
  {
    "query": {
      "data_source_id": 91465259,
      "raw_query": "SELECT * FROM small_table;",
      "name": "All the small things"
    }
  }
"""

headers = {
  'Content-Type': 'application/json',
  'Accept': 'application/hal+json'
}
request = Request('https://app.mode.com/api/{account}/reports/{report}/queries/{query}', json=values, headers=headers)
request.get_method = lambda: 'PATCH'

response_body = urlopen(request).read()
print(response_body)

var request = require('request');

request({
  method: 'PATCH',
  url: 'https://app.mode.com/api/{account}/reports/{report}/queries/{query}',
  headers: {
    'Content-Type': 'application/json',
    'Accept': 'application/hal+json'
  },
  body: "{  \"query\": {    \"data_source_id\": 91465259,    \"raw_query\": \"SELECT * FROM small_table;\",    \"name\": \"All the small things\"  }}"
}, function (error, response, body) {
  console.log('Status:', response.statusCode);
  console.log('Headers:', JSON.stringify(response.headers));
  console.log('Response:', body);
});

Delete a query

To remove a given query from a Report, send a DELETE request to the queries resource.

URL Params
account required	string	`Account` (`Workspace` or `User`) username
report required	string	`Report` token
query required	string	`Query` token

Responses
200	Query response
401	Unauthorized
404	Report not found

DELETE /{account}/reports/{report}/queries/{query}

curl --include \
     --request DELETE \
     --header "Content-Type: application/json" \
     --header "Accept: application/hal+json" \
  'https://app.mode.com/api/{account}/reports/{report}/queries/{query}'

require 'http'

username = 'your_api_key'
password = 'your_api_secret'

headers = {
  content_type: 'application/json',
  accept: 'application/hal+json'
}

response = HTTP.basic_auth(user: username, pass: password)
               .headers(headers)
               .delete('https://app.mode.com/api/{account}/reports/{report}/queries/{query}')
puts response

from urllib2 import Request, urlopen

headers = {
  'Content-Type': 'application/json',
  'Accept': 'application/hal+json'
}
request = Request('https://app.mode.com/api/{account}/reports/{report}/queries/{query}', headers=headers)
request.get_method = lambda: 'DELETE'

response_body = urlopen(request).read()

var request = require('request');

request({
  method: 'DELETE',
  url: 'https://app.mode.com/api/{account}/reports/{report}/queries/{query}',
  headers: {
    'Content-Type': 'application/json',
    'Accept': 'application/hal+json'
  }}, function (error, response, body) {
  console.log('Status:', response.statusCode);
  console.log('Headers:', JSON.stringify(response.headers));
  console.log('Response:', body);
});