Data Explorer Plugin

official
data-explorer

(Kane York) #1

The Data Explorer plugin allows you to make SQL queries against your live database, allowing for up-to-the-minute stats reporting.

Usage

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, then use the + and Import buttons to create or import queries.

For an example, let’s import one that I wrote. Download this file, then open the import dialog and drag the file onto the dialog.

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


Click Import to save it to the server. Select the query from the drop-down box, and try running it with the Run Query button. (Note: For the purposes of demonstration, I checked the “Include query plan” box.)

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

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

TODO

-- [params]
-- null string_list :words
-- null string_list :categories
-- user_id :user_id
-- int :limit = 150

WITH words AS (
 SELECT unnest(string_to_array( :words, ',' )) word
),
cats1 AS (
 SELECT unnest(string_to_array( :categories, ',')) cat
),
-- ...
AND p.user_id = :user_id
-- ...
LIMIT :limit

Installation

If you’re on a hosted Discourse forum and the data explorer doesn’t show up at /admin/plugins, email us at team@discourse.org to ask us to add it for you.

For self-hosted discourse forums, follow the Install A Plugin guide using https://github.com/discourse/discourse-data-explorer.git .


Daily graph of user time online
Visual forum statistics
What cool data explorer queries have you come up with?
Network chart of your forum - data visualization
Extracting email addresses for a group
Visual map of users
Can I see in the statistics which post received the most likes
How do I download a record of top posts or most clicked links?
Can staff be able to see real author of anonymous posts
How to look up a user based on a user_id?
Get infromation from data explorer and put on user card
Creating Point/Reward System for users
Discourse view file update does not reflect in browser
How to measure active users?
How do I clear Warnings?
Bug with [code]anything[/code] on a single line by itself
Exporting all Topic titles and IDs?
Is there a Data Explorer for Dummies article somewhere?
Display Data Explorer query results in WordPress (with TwigAnything)
Tracking referrals? Encouraging users to invite others?
Query to find out who marked the solution as solved
Add an Admin subtab for Inactive users
SQL: The most N used words per user (speak their language!)
User Summary - Most Liked By user attributions are incorrect
Maximum Number of Search Results?
Data analytics by complex networks and text mining
Error thrown when importing a query in Data Explorer
Zero search results searching private messages for specific keyword
Where to find User ID information
Discourse Narrative Bot Data Explorer Queries :robot:
Who messaged the most?
Who messaged the most?
How to open hyperlinks in a new tab
Working with plugin_store_rows with API, Possible?
Run report of digest Unsubscribes
Need log the who downloaded attachments
Redesign of use profile
Poll problem - exact vote numbers needed
Telegram Notifications Plugin (discourse-telegram-notifications)
Get last :heart: via API
Is it possible to keep the votes history of a poll?
Monitoring Moderator Activity
See All User Activity As An Admin
What plugins do you use - and why?
What cool badge queries have you come up with?
CRM Integration
Can I access my Discourse forum database?
Counting and reporting post views for commercial forum users (so we can charge them per-view)
Top user list public
How do I audit votes for fraudulent behavior
Beginners Guide to Install Discourse on Ubuntu for Development
Can I make a plug-ins to view a certain group of people?
Sorting all users by total posts?
Shopify Integration
Possible to run a report for most posts within community?
Which user has the most badges?
Is there a log of user visits?
Can I access Discourse forum Database to use for PowerBI reporting?
The metrics on dashboard, do they include activities from administrators?
Exporting data-- is it possible?
Group forum members based on topics viewed?
Likes on a post
Discourse - What do admins see on the Analytics dashboard?
Best Practices on reply-by-email
Get a user object by username or id javascript
Poll: Export poll results in CSV for poll creator?
Database's Data Model?
Retort - a reaction-style plugin for Discourse
How do I see Facebook signups?
External Data Analysis
How to check the uniqueness of users in "hot link" badge?
API search list user by array of user names
About the data-explorer category
Retrieving user information via REST API
Importing from phpBB3
Discourse disk space limits per user?
Discourse disk space limits per user?
Specify user by external id
Get only top level posts in topic via discourse API
Questions regarding Discourse features
Remove Facebook as login option
How can I email all recently active users?
Tags: how can admin see who is following what tags?
See what categories new users (& other trust levels) post in most
Create report for amount of interactions per user
What would you like to see on your Discourse 2.0 Admin Dashboard?
(Erlend Sogge Heggen) #4

105 posts were split to a new topic: Data Explorer - Legacy replies


(Jeff Atwood) #80

16 posts were split to a new topic: Strange problem with Data Explorer


Strange problem with Data Explorer
(Jeff Atwood) #84

A post was split to a new topic: Find old topics with images?


(Joshua Rosenfeld) #86

3 posts were merged into an existing topic: Admin Statistics Report


Strange problem with Data Explorer
(Erlend Sogge Heggen) #109

A post was split to a new topic: Improve UX of drop-down


(Erlend Sogge Heggen) #110

A post was split to a new topic: Add alphabetic ordering


(Erlend Sogge Heggen) #111

7 posts were split to a new topic: Access to query data from API endpoints


(Erlend Sogge Heggen) #126

8 posts were split to a new topic: Make data explorer available to moderators


(Erlend Sogge Heggen) #134

9 posts were split to a new topic: Download result of queries into Google Spreadsheet


(Erlend Sogge Heggen) #135

Please post replies to the #plugin:data-explorer category.


(Phạm Quyết Nghị) #136

I have a query

UPDATE tag_search_data
SET raw_data = 'c ++'
WHERE
  tag_id = 21
RETURNING tag_id,
  raw_data

When clicked run query has the following error

**PG :: ReadOnlySqlTransaction: ERROR: can not execute SELECT in a read-only transaction**

how to write to the database


(Stephen Chung) #137

You cannot. You have to go into the postgresql database to execute update queries. No in this plugin.


(Phạm Quyết Nghị) #138

thank you for the suggestion


(Kim Miller) #139

Is there a common, preferred postgresql front end you might suggest?

Is it reasonable to use such an approach to, say, change which Categories a group of users are Watching? Or are there simpler ways to go about that kind of update other than manually drilling into each user’s preferences from the UI?


(Sam Saffron) #140

Absolutely, the one I would suggest is avoiding at all costs “front end”

Use the Discourse API when you need to script stuff around Discourse it is far safer.


(Kim Miller) #141

Ah, the API. I keep forgetting about the API. So as an example, say we want to add a new Category to all user’s preferences to set those users to Watching this category. I go to the API and search user preference but don’t see anything obvious.

Where’s a good place to go to find example scripts for basic tasks to get one started?


(Sam Saffron) #142

This is still a highly recommended way of figuring stuff out on the fly:

There is also https://docs.discourse.org/


(Kim Miller) #143

Very helpful, Sam. Looks practically fun. Thanks so much!


(AstonJ) #144

Hi @riking

Great job on this plugin!

It reminds me of a plugin from the vB world called WebTemplates - it consisted of two parts (WebTemplates and WebQueries), the WebQueries side is basically like your Data Explorer Plugin! The WebTemplates part is where you can create pages to use saved queries from the WebQueries section.

Here’s what the WebTemplates page looks like for a specific page:

Towards the bottom you can specify which queries you want available for that page - then in the fields above you specify the HTML and where the result of those queries would show.

We used this on a vB site to power pages like homepage, news homepage, articles homepage, etc then each news item or each article was simple a forum thread that was styled differently for the first post and subsequent posts (using simple statements like if $post == 1 etc). So the entire site was powered by vB.

I’d love to see something like this for DC. I reckon it could take on the publishing world quite easily!!

Anyway just posting here in case it’s of interest to you.