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 do I get a list of which users use which login methods?
How to look up a user based on a user_id?
Creating Point/Reward System for users
Discourse view file update does not reflect in browser
Beginners Guide to Install Discourse on Ubuntu for Development
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
How to measure active users?
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
Where to find User ID information
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?
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
Get infromation from data explorer and put on user card
(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