Run Data Explorer queries with the Discourse API

[Discourse]

This guide explains how to use the Discourse API to create, run, and manage queries with the Data Explorer plugin.

Required user level: Administrator

Virtually any action that can be performed through the Discourse user interface can also be triggered with the Discourse API.

This document provides a comprehensive overview for utilizing the API specifically in conjunction with the Data Explorer plugin.

For a general overview of how to find the correct API request for an action, see: Reverse engineer the Discourse API .

Running a Data Explorer query

To run a Data Explorer query via the API, make a POST request to /admin/plugins/discourse-data-explorer/queries/<query-id>/run. You can find the query ID by visiting it through your Discourse site and checking the id parameter in the address bar.

Below is an example query with an ID of 20 that returns topics by views on a specified date:

--[params]
-- date :viewed_at

SELECT
topic_id,
COUNT(1) AS views_for_date
FROM topic_views
WHERE viewed_at = :viewed_at
GROUP BY topic_id
ORDER BY views_for_date DESC

This query can be run from a terminal with:

curl -X POST "https://your-site-url/admin/plugins/discourse-data-explorer/queries/20/run" \
-H "Content-Type: multipart/form-data;" \
-H "Api-Key: <api-key>" \
-H "Api-Username: system" \
-F 'params={"viewed_at":"2019-06-10"}'

Note that you’ll need to replace the <api-key>, <your-site-url> with your API key and domain.

Handling large datasets

The Data Explorer plugin limits JSON results to 1000 rows by default (controlled by the hidden data_explorer_query_result_limit site setting). You can override this per-request by passing a limit parameter, up to a maximum of 10,000:

curl -X POST "https://your-site-url/admin/plugins/discourse-data-explorer/queries/20/run" \
-H "Content-Type: multipart/form-data;" \
-H "Api-Key: <api-key>" \
-H "Api-Username: system" \
-F "limit=5000"

For datasets larger than 10,000 rows, you’ll need to paginate at the SQL level. You can use the example query below:

--[params]
-- integer :limit = 100
-- integer :page = 0
SELECT * 
FROM generate_series(1, 10000)
OFFSET :page * :limit 
LIMIT :limit

To fetch the results page-by-page, increment the page parameter in the request:

curl -X POST "https://your-site-url/admin/plugins/discourse-data-explorer/queries/27/run" \
-H "Content-Type: multipart/form-data;" \
-H "Api-Key: <api-key>" \
-H "Api-Username: system" \
-F 'params={"page":"0"}'

Stop when result_count is zero.

For additional information about handling large datasets, see: Result Limits and Exporting Queries

Removing relations data from the results

When Data Explorer queries are run through the user interface, a relations object is added to the results. This data is used for rendering the user in UI results, but you are unlikely to need it when running queries via the API.

To remove that data from the results, add a download=true parameter with your request:

curl -X POST "https://your-site-url/admin/plugins/discourse-data-explorer/queries/27/run" \
-H "Content-Type: multipart/form-data;" \
-H "Api-Key: <api-key>" \
-H "Api-Username: system" \
-F 'params={"page":"0"}' \
-F "download=true"

API authentication

Details about generating an API key for the requests can be found here: Create and configure an API key .

If the API key is only going to be used to run Data Explorer queries, you can select “Granular” from the Scope drop down menu, then select the “run queries” scope.

FAQs

Is there any api endpoint I can use to get the list of reports and the ID numbers? I want to build a dropdown with the list in it?

Yes, you can make an authenticated GET request to /admin/plugins/discourse-data-explorer/queries.json to get a list of all queries on the site.

Is it possible to create queries through the api?

Yes. Documentation on how to do that are at Create a Data Explorer query using the API

Is it possible to send parameters with the post request?

Yes, include SQL parameters using the -F option, as shown in the examples.

Is CSV export for queries supported by the API?

Yes. Append .csv to the run endpoint URL to get results in CSV format:

curl -X POST "https://your-site-url/admin/plugins/discourse-data-explorer/queries/20/run.csv" \
-H "Api-Key: <api-key>" \
-H "Api-Username: system" \
-F 'params={"viewed_at":"2019-06-10"}'

CSV responses default to returning up to 10,000 rows. You can pass a limit parameter to reduce this.

Additional resources

• Create a Data Explorer query using the API
• Create and configure an API key
• Reverse engineer the Discourse API
• GitHub - lee-dohm/execute-discourse-query: Executes a Discourse data-explorer query via the API · GitHub

[37Rb (Ryan)]

@simon

CSV 내보내기에서 limit=ALL 지원

이 댓글은 API에서 CSV 내보내기를 할 수 있다는 것을 암시하는 것 같습니다. 가능한가요? CSV 형식으로 데이터를 필요로 해서 궁금합니다. JSON으로 받아서 CSV로 변환할 수도 있지만, 내장된 방식으로 CSV를 얻을 수 있다면 조금 더 편할 것 같습니다.

[francis_exalate (Francis Martens (Exalate))]

최근 50초 내에 생성되거나 업데이트된 항목을 조회하는 쿼리를 작성할 수 있을까요?

[Canapin (Coin-coin le Canapin)]

AI가 말합니다

-- [params]
-- int :seconds = 50

SELECT
    p.id AS post_id,
    p.created_at,
    p.updated_at,
    p.raw AS post_content,
    p.user_id,
    t.title AS topic_title,
    t.id AS topic_id
FROM posts p
INNER JOIN topics t ON t.id = p.topic_id
WHERE
    (EXTRACT(EPOCH FROM (NOW() - p.created_at)) <= :seconds
    OR EXTRACT(EPOCH FROM (NOW() - p.updated_at)) <= :seconds)
    AND p.deleted_at IS NULL
    AND t.deleted_at IS NULL
ORDER BY p.created_at DESC
LIMIT 50

간단히 테스트해 보니 작동하는 것 같습니다

[Moin]

쿼리를 사용해서 게시글에 추가한 "like "가 보이지 않습니다.

post_actions를 사용해야 할 것 같습니다.

--[params]
--string :timespan = 50 seconds

SELECT post_id,
       user_id, 
       created_at, 
       updated_at, 
       deleted_at
FROM  post_actions
WHERE post_action_type_id=2 AND updated_at > NOW() - INTERVAL :timespan

더 깔끔한 결과물을 제공하는 버전

--[params]
--string :timespan = 50 seconds
--boolean :include_in_timespan_deleted = false

SELECT 
  post_id, 
  user_id, 
  CASE
    WHEN EXTRACT(EPOCH FROM (NOW() - created_at)) < 60 THEN CONCAT(ROUND(EXTRACT(EPOCH FROM (NOW() - created_at))), ' seconds ago')
    WHEN EXTRACT(EPOCH FROM (NOW() - created_at)) < 3600 THEN CONCAT(ROUND(EXTRACT(EPOCH FROM (NOW() - created_at)) / 60), ' minutes ago')
    WHEN EXTRACT(EPOCH FROM (NOW() - created_at)) < 86400 THEN CONCAT(ROUND(EXTRACT(EPOCH FROM (NOW() - created_at)) / 3600), ' hours ago')
    ELSE CONCAT(ROUND(EXTRACT(EPOCH FROM (NOW() - created_at)) / 86400), ' days ago')
  END AS relative_created_at,
  CASE
    WHEN EXTRACT(EPOCH FROM (NOW() - updated_at)) < 60 THEN CONCAT(ROUND(EXTRACT(EPOCH FROM (NOW() - updated_at))), ' seconds ago')
    WHEN EXTRACT(EPOCH FROM (NOW() - updated_at)) < 3600 THEN CONCAT(ROUND(EXTRACT(EPOCH FROM (NOW() - updated_at)) / 60), ' minutes ago')
    WHEN EXTRACT(EPOCH FROM (NOW() - updated_at)) < 86400 THEN CONCAT(ROUND(EXTRACT(EPOCH FROM (NOW() - updated_at)) / 3600), ' hours ago')
    ELSE CONCAT(ROUND(EXTRACT(EPOCH FROM (NOW() - updated_at)) / 86400), ' days ago')
  END AS relative_updated_at,
  CASE
    WHEN deleted_at IS NULL THEN 'no'
    WHEN EXTRACT(EPOCH FROM (NOW() - deleted_at)) < 60 THEN CONCAT(ROUND(EXTRACT(EPOCH FROM (NOW() - deleted_at))), ' seconds ago')
    WHEN EXTRACT(EPOCH FROM (NOW() - deleted_at)) < 3600 THEN CONCAT(ROUND(EXTRACT(EPOCH FROM (NOW() - deleted_at)) / 60), ' minutes ago')
    WHEN EXTRACT(EPOCH FROM (NOW() - deleted_at)) < 86400 THEN CONCAT(ROUND(EXTRACT(EPOCH FROM (NOW() - deleted_at)) / 3600), ' hours ago')
    ELSE CONCAT(ROUND(EXTRACT(EPOCH FROM (NOW() - deleted_at)) / 86400), ' days ago')
  END AS relative_deleted_at
FROM 
  post_actions
WHERE 
  post_action_type_id = 2 
  AND updated_at > NOW() - INTERVAL :timespan
  AND (
    :include_in_timespan_deleted = false 
    OR (deleted_at IS NOT NULL AND deleted_at > NOW() - INTERVAL :timespan)
  )

[Canapin (Coin-coin le Canapin)]

아, 내가 잘못 읽었네! 문장을 "최근 50초 안에 생성되었거나 업데이트된 항목을 조회하는 쿼리를 실행할 수 있는가?"라고 읽었거든.

(단일 인용표의 위치를 잘 살펴봐)

[francis_exalate (Francis Martens (Exalate))]

모두 감사합니다.

ask.discourse에서 이곳으로 안내받아서 여기에 게시했습니다.
제가 찾고 있는 것은 API가 이 기능을 제공하는지 여부입니다.

[Canapin (Coin-coin le Canapin)]

네, 물론이죠.

데이터 탐색기에서 쿼리를 작성한 후, 이 가이드에 설명된 대로 API를 통해 쿼리를 실행합니다.

방금 Moin의 쿼리를 API를 통해 실행해 보았는데, 예상된 결과가 올바르게 반환되었습니다.

[tanya_byrne (kukulaka)]

저도 이 부분에 대해 궁금했습니다. API를 통해 데이터를 내보내는 방식이 JSON뿐인가요, 아니면 Data Explorer에서도 CSV 내보내기를 지원하나요?

[francis_exalate (Francis Martens (Exalate))]

모두 감사합니다.

늦게 답장드려 죄송합니다. 잠시 오프라인 상태였습니다.

현재 저는 오늘 생성된 모든 주제/게시글을 검색한 후, 해당 타임스탬프 이전에 업데이트된 주제/게시글을 필터링하여 제외하는 작업을 수행하고 있습니다.

[Canapin (Coin-coin le Canapin)]

@francis_exalate

현재 제가 하고 있는 작업은 오늘 생성된 모든 주제/게시글을 검색한 뒤, 타임스탬프보다 이전에 업데이트된 주제/게시글을 필터링하는 것입니다.

직접 코드를 다루고 싶지 않다면 ask.discourse.com의 봇에게 질문할 수 있습니다. Discourse 관련 SQL 쿼리에 대해서는 대체로 정확도가 높습니다(하지만 무조건 정확하다고 가정하지 말고, 코드를 확인하여 검증하세요).

[srinivas.chilukuri]

@Discourse

-H "Content-Type: multipart/form-data;" \

---

Content-Type 헤더가 올바른가요?
개발자 도구를 사용하여 매개변수가 포함된 Data Explorer 쿼리를 검사할 때 Content-Type 헤더는 다음과 같이 표시됩니다:

Content-Type: application/x-www-form-urlencoded; charset=UTF-8

그러나 현재 cURL 명령에는 다음이 포함되어 있습니다:

-H "Content-Type: multipart/form-data;"

---

[blake (Blake Erickson)]

• multipart/form-data
• application/x-www-form-urlencoded
• application/json

위 내용은 API 요청 시 사용할 수 있는 모든 유효한 콘텐츠 타입입니다.

[srinivas.chilukuri]

@blake
language python
library requests
API 샘플 Data Explorer 쿼리를 하나 제공해 주실 수 있을까요? 세 개의 매개변수를 포함한 쿼리여야 합니다.

참고: Topic에 따르면, 매개변수 값은 반드시 이중 따옴표로 묶어야 한다고 합니다.

[blake (Blake Erickson)]

네, 파이썬을 사용한 예제는 다음과 같습니다:

import json
import requests

API_KEY      = "YOUR_API_KEY"
API_USERNAME = "system"
QUERY_ID     = 20
SITE_URL     = "https://your-site-url"

# 모든 값은 문자열이어야 합니다
params = {
    "user_id":   "2",
    "viewed_at": "2019-06-10",
    "limit":     "5"
}

# Data Explorer는 매개변수를 JSON 인코딩된 문자열로 기대합니다
payload = {
    "params": json.dumps(params)
}

url = f"{SITE_URL}/admin/plugins/explorer/queries/{QUERY_ID}/run"
headers = {
    "Api-Key":       API_KEY,
    "Api-Username":  API_USERNAME,
    "Content-Type":  "application/json"
}

r = requests.post(url, headers=headers, json=payload)
r.raise_for_status()
print(r.json())