Research:Quarry

Query execution. A screenshot of the Quarry SQL writing interface after execution completed. The results set appears directly beneath the query writing pane.

Quarry

2021 Coolest Tool
Award Winner

in the category
Developer

Quarry is a public querying interface for Wiki Replicas, a set of live replica SQL databases of public Wikimedia Wikis. Quarry is designed to make running queries against Wiki Replicas easy. Quarry also provides a means for researchers to share and review each other's queries. Users of Quarry are required to agree to Cloud Services Terms of use.

quarry.wmcloud.org

Writing & executing SQL queries

Choosing a database

Quarry requires you to enter a wikireplica database in the upper left box just above the SQL box. The name of the database is usually the language code concatenated with the wiki family (wiki for Wikipedia, wikibooks etc.). For multi-language wikis it's the name of the wiki and wiki, e.g. commonswiki, wikidatawiki. See https://quarry.wmflabs.org/query/53805 for a fairly complete list. Long-time users are aware that wikireplicas databases that are publicly accessible also end the name of the database with _p added to the name at the end. Please note that the database entry box does not require you to add _p to the end, but it will also work if you do. The database you are querying behind the scenes does have that appended as all public versions of the databases do. This routes your query to the correct wikireplicas server and database. You can only use the database you selected for most intents and purposes.

Advanced use cases and joining wiki databases

You technically can query a database that shares a section with the database you selected (see the database layout). If you know for sure that you are querying against a different database that does share a section number what you've entered, this is done by prefixing your query with USE <dbname>_p; or by specifically referencing the database in a join. Cross-wiki joins are only possible within the same section. For example, the following SQL returns the count of revisions saved by "EpochFail" in metawiki (metawiki is on s7, so you would have needed to connect to something else there such as arwiki or centralauth).

USE metawiki_p;
SELECT COUNT(*)
FROM revision_userindex 
WHERE
    rev_actor = (SELECT actor_id
                 FROM actor
                 WHERE actor_name = "EpochFail") AND 
    rev_timestamp >= "20140101";

In general, you should just connect to the database that you wish to query against unless you have an advanced use case like that which requires joining wikis that are in the same section number--and you know what that means.

Available tables and columns

For an overview of the available tables and columns see MediaWiki database layout. Some extensions add additional tables which can be queried, too, these should be documented on the extension's documentation page. The Toolforge SQL Optimizer has a database browser that helps to find the needed table and understand its format. Note that not all tables are available. The following data are missing:

• The table text containing the actual content of the revisions is missing.
• Private data is suppressed.

Note that this means that the revision and logging tables do not have indexes on user columns. You can use revision_userindex and logging_userindex instead (see wikitech:Help:Toolforge/Database#Tables_for_revision_or_logging_queries_involving_user_names_and_IDs)

To get a list of all tables you can run the query (replace enwiki_p with the respective wiki):

SHOW TABLES FROM enwiki_p;

To get the schema of a table, you can use DESCRIBE (e.g. https://quarry.wmflabs.org/query/585)

Downloading a resultset

Download data button. 

Quarry lets you download the result of a query. A data download button appears right before the resultset to get query results in CSV, TSV, JSON or wikitable formats. You can also request resultsets programmatically from wiki tools.

Format

https://quarry.wmflabs.org/run/query_run_ID/output/resultset_id/format

Example

https://quarry.wmflabs.org/run/1534/output/0/json

Response

{"rows": [[88]], "meta": {"rev_id": 1534, "query_id": 541, "run_id": 1534}, "headers": ["COUNT(*)"]}

Note that query_run_ID (e.g. /run/10140) it's not the same as query number (e.g. /query/153) which is visible in the URL. The query run ID is given in the HTML source code of the query page as "qrun_id".

To download a result set from the latest run of a query:

Format

https://quarry.wmflabs.org/query/query_ID/result/latest/resultset_id/format

Example

https://quarry.wmflabs.org/query/541/result/latest/0/csv

Response

{"rows": [[88]], "meta": {"rev_id": 1536, "query_id": 541, "run_id": 1536}, "headers": ["COUNT(*)"]}

Example queries

• Published Quarry queries are a good source of examples (try using Google site search for specific topics)
• The Toolforge documentation contains various examples

Interwiki link

The interwiki map link quarry: exists to provide an alternate linking means to queries. eg.

• https://quarry.wmflabs.org/run/1534/output/0/json; or
• quarry:run/1534/output/0/json

See also

• MediaWiki database layout
• Query library -- for ideas of queries to run
• Privacy policy
• Quarry source code
• Quarry bug tracker
• Cloud Services Terms of use
• Toolforge database access documentation
• Grants:Evaluation/Wikiresearch webinars - a series of tutorials on using Quarry and other tools
• Toolforge SQL Optimizer - explains a query execution plan
• Wiki Replicas 2020 Redesign - explains recent changes to database selection