Discourse Data Explorer

[Discourse]

Summary

	Summary	Discourse Data Explorer allows you to make SQL queries against your live database, allowing for up-to-the-minute stats reporting.
	Repository Link	https://github.com/discourse/discourse-data-explorer
	Install Guide	How to install plugins in Discourse

As this is an official plugin maintained by the Discourse team, support, bug, ux, and feature requests can be made in the respective categories here on Meta, and tagged with the appropriate plugin tag. Click on a link below to get one started.

 Support  Bug  UX  Feature

Features

After installing the plugin, head to /admin/plugins .

Click on the “Settings” button, and turn on data explorer enabled, then return and reload the page.
There should now be a tab in the stacked nav called “Data Explorer”. Select it or head to /admin/plugins/explorer , then use the + , Import or Run Query buttons to get started.

Configuration

Default Queries

On a fresh install, the data explorer now ships with a bunch of useful queries that can help you draw insights from your forum’s activity. Open any query and click Run Query to try it out.

Here's what the stock queries look like.

Note: Default queries are picked up from this file: discourse-data-explorer/queries.rb at main · discourse/discourse-data-explorer · GitHub

If you have an awesome query that you want everyone to have, make a PR just like this one.

Default queries cannot be edited, feel free to copy the sql and paste it into a new query if you’d like to modify them.

Add your own queries

For an example, let’s import one that I wrote. Download this file, then open the import dialog and select the file to be uploaded or paste the query in the text box. Click Import to save it and then click Run Query to see the results.

most-common-likers.dcquery (1).json (442 Bytes)

Looks like all of the Discourse developers like each other quite a bit

I don’t know any SQL, where do I find more queries?

Our community has got you covered!
Import as many queries as you like from here: What cool data explorer queries have you come up with?

Writing Queries

When you click any of the Edit buttons on a query view, or start a new Explorer Query, you are presented with a screen like the one below:

The top two boxes are the name and description of the query. The name is what you’re selecting in the dropdown box, and the description should be used for things like explaining what to put in the query parameters or to describe what data is being queried.

The left pane is where you write the SQL query. Minor syntax highlighting and checking is provided.

The schema explorer is on the right side of the writing pane. Note the red column names with a warning symbol, such as email , password_hash , salt - these are “Sensitive” columns. Try to avoid querying them if possible, reconsider querying them if you are in a public location, and definitely do not post screenshots of queries checking those columns without thinking about what you’re doing first.

Declaring Parameters in your query

Lets modify our example by using a parameter instead of hard-coding values. If you declare a variable as shown below, a text field will show up where you can input different values! Try it out!

---

-- [params]
-- int :limit = 100
WITH pairs AS (
SELECT p.user_id liked, pa.user_id liker
FROM post_actions pa
LEFT JOIN posts p ON p.id = pa.post_id
WHERE post_action_type_id = 2
)
SELECT liker liker_user_id, liked liked_user_id, count(*)
FROM pairs
GROUP BY liked, liker
ORDER BY count DESC
LIMIT :limit

There are several available kinds of parameters to use in your query, some with specialized input. Any of these parameter types can be prefixed with null to allow an empty input.

• int, bigint, double - Shows a number input, becomes a numeric value. int is restricted to 32-bit numbers, while bigint can be larger.
• int_list - Enter comma-separated integers, becomes comma-separated integers in the query. (See List Parameters below.)
• post_id, topic_id, category_id, group_id, badge_id - Numeric input; ensures the specified post, topic, category, group, or badge exists on the forum before running the query to prevent mistakes.
• boolean - Shows a checkbox, or a dropdown if null boolean is used instead.
• string - Freeform textbox, becomes a text value.
• string_list - Comma-separated freeform text. (See List Parameters below.)
• time, date, datetime - Textbox that is string-casted to the appropriate type in the query. No client-side validation (yet!).
• user_id, user_list - Shows the Discourse user selector box, and becomes the numeric user ID or a comma-separated list of the numeric user IDs. (See List Parameters below.)

Using list parameters

Three of the above parameter types are “lists”. Special care must be taken when using these to avoid syntax errors. Here is an example of correctly using a list parameter:

-- [params]
-- user_list :the_user_ids
SELECT SUM(length(bio_raw))
FROM user_profiles
WHERE user_id IN (:the_user_ids)

Searching in your queries list

If you’ve accumulated too many awesome queries, no problem! The search bar can help you filter through titles and descriptions.

Allow moderators or custom groups to run a query

You can add groups (including moderators) to a query to grant them access to it:

After saving, you can find it from the Groups page for that particular group on the Reports tab:

Click any query to open it and then the Run button to see the results.

Limitations

• If there are more than 1000 results, Run Query will return only the first 1000.
• Clicking Export will let you download the results with a much higher limit of 10,000.

CHANGELOG

TODO