Difference between revisions of "Mirabelle"

From Open Food Facts wiki
Jump to navigation Jump to search
(+ category:quality)
(more datasets)
 
Line 1: Line 1:
Mirabelle is a tool to manipulate the data either with forms and facets, either with the SQL language: https://mirabelle.openfoodfacts.org
+
Mirabelle is a tool to manipulate some Open Food Facts' exports or stats:
  
The data in Mirabelle is based on the last daily CSV export (it does not contain all the fields of the database, but still contains more than 130).
+
* either with forms and facets,
 +
* either with the well-known [https://en.wikipedia.org/wiki/SQL SQL language]
 +
 
 +
It is available at: https://mirabelle.openfoodfacts.org
  
 
Mirabelle is a kind of playground. It's just a more convenient way to manipulate the data. Please don't base your own apps on it, as it is not meant to have the same level of availability as the Open Food Facts regular website.
 
Mirabelle is a kind of playground. It's just a more convenient way to manipulate the data. Please don't base your own apps on it, as it is not meant to have the same level of availability as the Open Food Facts regular website.
 
  
 
All the database published with Mirabelle are read-only.
 
All the database published with Mirabelle are read-only.
Line 21: Line 23:
 
== Databases ==
 
== Databases ==
  
* products: all the data from Open Food Facts (imported from today's CSV).
+
* '''_memory''': is a special empty database allowing to build queries mixing multiple databases
* previous: all the data from yesterday's CSV.
+
* '''distrib''': represents past and present data quality errors which can be fixed (at least with an image); this database is used by the data quality team to distribute the products to be fixed
 +
* '''off-stats''': represents time series with stats related to data quality (data_quality_stats table) and products from owners
 +
* '''products''': all the data from Open Food Facts (imported from today's CSV).
 +
* '''products_2023_01''': represent all the data imported on Jannuary 2023
 +
* '''products_2021_09''': represent all the data imported on September 2021
 +
* '''previous''': all the data from yesterday's CSV; it allows to compare the data between today and yesterday
 
* [to be completed]
 
* [to be completed]
  

Latest revision as of 13:56, 8 April 2025

Mirabelle is a tool to manipulate some Open Food Facts' exports or stats:

  • either with forms and facets,
  • either with the well-known SQL language

It is available at: https://mirabelle.openfoodfacts.org

Mirabelle is a kind of playground. It's just a more convenient way to manipulate the data. Please don't base your own apps on it, as it is not meant to have the same level of availability as the Open Food Facts regular website.

All the database published with Mirabelle are read-only.

Features

  • By default, every view has its own URL: you can easily share your requests or views.
  • Facet navigation (close to Open Food Facts).
  • Advanced filtering system.
  • Thanks to the SQL language, very rich queries are possible.
    • REGEXP are possible in queries (see below).
  • CSV or JSON exports. It's easy to create exports by countries.
  • Allow copy and paste from Mirabelle to dozens of other tools (Mediawiki, Github, TSV, etc.).
  • Charts (see the Show charting options button).

Databases

  • _memory: is a special empty database allowing to build queries mixing multiple databases
  • distrib: represents past and present data quality errors which can be fixed (at least with an image); this database is used by the data quality team to distribute the products to be fixed
  • off-stats: represents time series with stats related to data quality (data_quality_stats table) and products from owners
  • products: all the data from Open Food Facts (imported from today's CSV).
  • products_2023_01: represent all the data imported on Jannuary 2023
  • products_2021_09: represent all the data imported on September 2021
  • previous: all the data from yesterday's CSV; it allows to compare the data between today and yesterday
  • [to be completed]

Documentation

REGEXPs

Regexps are possible thanks to two extensions.:

  1. The classic REGEXP extension for SQLite allows to select products which are matching an expression.
  2. The second one, from sqlean, might be a bit slower allows more features: it can extract string. Check its documentation.