Changes

Jump to navigation Jump to search

Reusing Open Food Facts Data (view source)

Revision as of 18:47, 6 November 2024

12,319 bytes added ,  6 November
Add Parquet use cases
Line 177: Line 177:     
=== Parquet file hosted on Hugging Face (beta) ===
 
=== Parquet file hosted on Hugging Face (beta) ===
−
This method should not be considered as ready for production. It's just another convenient way to access Open Food Facts data.
+
''The Parquet format is currently in beta, meaning it shouldn't be used in production yet.''
   −
The parquet file is made from JSONL export (the whole database). Then Hugging Face allows different ways to query the data.
+
A simplified version of the JSONL export (the whole database) is also available in the [https://parquet.apache.org Parquet format]. In the conversion process, we filtered out columns that contain duplicate information, are intended for internal debugging, or are not relevant to users.
   −
==== In-browser queries ====
+
The Parquet format has proved to be handy:
−
Just go the dataset's page -- https://huggingface.co/datasets/openfoodfacts/product-database -- and click on the "SQL" yellow botton.
+
 
 +
* Data are organized by column, rather than by row, which saves storage space and speeds up analytics queries, i.e. you can select just the columns you care about, optimizing query performances, even on entry-level computers,
 +
 
 +
* Highly efficient data compression and decompression, making it good for storing and sharing big data of any kind,
 +
 
 +
* Supports complex data types and advanced nested data structures.
 +
 
 +
The dataset is available on the [https://huggingface.co/datasets/openfoodfacts/product-database Hugging Face] plateforme, a collaborative Machine Learning ecosystem where developers and researchers can share models and datasets.
 +
 
 +
In-browser queries
 +
 
 +
Just go the dataset's page -- https://huggingface.co/datasets/openfoodfacts/product-database -- and click on the "SQL" yellow button.
    
You'll see a SQL interface in your browser, where you can perform queries.
 
You'll see a SQL interface in your browser, where you can perform queries.
   −
==== From the command line, thanks to DuckDB ====
+
==== DuckDB to query the database ====
−
Here again, this great tool allows to request remote parquet files thru the command line.
+
Here again, this great tool allows to request remote parquet files with the command line.
 
  $ duckdb :memory: "SELECT * from '<nowiki>https://huggingface.co/datasets/openfoodfacts/product-database/resolve/main/products.parquet'</nowiki> LIMIT 10;"
 
  $ duckdb :memory: "SELECT * from '<nowiki>https://huggingface.co/datasets/openfoodfacts/product-database/resolve/main/products.parquet'</nowiki> LIMIT 10;"
 
The request can be a bit long (~15 seconds).
 
The request can be a bit long (~15 seconds).
   −
==== From the command line, thru the local filesystem ====
+
==== From the command line, through the local filesystem ====
 
If you want faster results, just download the parquet file from Hugging Face. You'll then be able to query the file with DuckDB, with better request times.
 
If you want faster results, just download the parquet file from Hugging Face. You'll then be able to query the file with DuckDB, with better request times.
−
  $ duckdb :memory: "SELECT * from './products.parquet' LIMIT 10;"
+
 
 +
You can easily install DuckDB on your Command Line Interface (CLI) by reading our [https://blog.openfoodfacts.org/en/news/food-transparency-in-the-palm-of-your-hand-explore-the-largest-open-food-database-using-duckdb-%F0%9F%A6%86x%F0%9F%8D%8A blog post].
 +
  $ duckdb :memory: "SELECT * from 'products.parquet' LIMIT 10;"
 +
 
 +
==== How to exploit the Parquet database using DuckDB: Use-Cases ====
 +
The OFF database contains a variety of data in different format, such as '''TEXTS, LISTS, DICTIONNARIES, DATES''', and even more...
 +
 
 +
But no worries, '''DuckDB''' handles any type of data! '''Learn how to use the database by solving the most common use-cases asked by the community:'''
 +
 
 +
===== ''TEXT fields'' =====
 +
 
 +
*  '''Search by Name'''
 +
 
 +
''Ex: search for all product name containing the term "beurre"''
 +
SELECT code, product_name
 +
FROM read_parquet('products.parquet')
 +
WHERE product_name
 +
ILIKE '%beurre%';
 +
┌───────────────┬──────────────────────────────────────────────────────────────┐
 +
│    code      │                        product_name                        │
 +
│    varchar    │                          varchar                            │
 +
├───────────────┼──────────────────────────────────────────────────────────────┤
 +
│ 0000234022960 │ Croissants pur beurre                                        │
 +
│ 0002000013363 │ Grandes galettes au beurre                                  │
 +
│ 0008421369416 │ Brioche Pur Beurre                                          │
 +
│ 00089739      │ Beurre doux                                                  │
 +
│ 0016073123478 │ Beurre d'arachide en poudre                                  │
 +
│      ·      │              ·                                              │
 +
│      ·      │              ·                                              │
 +
│      ·      │              ·                                              │
 +
│ 3560071532741 │ Beurre demi-sel À teneur réduite en matière grasse 60% Mat…  │
 +
│ 77646187      │ Beurre sans sel                                              │
 +
│ 3596710524006 │ Cosmia crème corps nourrissante - beurre de macadamia + ni…  │
 +
│ 3596710523986 │ Cosmia crème mains nourrissante - au beurre de karité et à…  │
 +
│ 4820154481786 │ Petit Beurre                                                │
 +
├───────────────┴──────────────────────────────────────────────────────────────┤
 +
│ 13791 rows (10 shown)                                              2 columns │
 +
└──────────────────────────────────────────────────────────────────────────────┘
 +
 
 +
* '''Search by category'''
 +
 
 +
''Ex:'' ''search for all product belonging to "plant-based food" and "cereals" categories''
 +
SELECT code, product_name
 +
FROM read_parquet('products.parquet')
 +
WHERE categories ILIKE '%plant-based foods%' AND categories ILIKE '%cereal%';
 +
┌───────────────┬───────────────────────────────────────────┐
 +
│    code      │              product_name                │
 +
│    varchar    │                  varchar                  │
 +
├───────────────┼───────────────────────────────────────────┤
 +
│ 0000127534587 │ Today's temptations, lithuanian rye bread │
 +
│ 0000236555909 │ Bakers Best, White Bread                  │
 +
│ 0000236598784 │ Bakers Best, Rye Bread                    │
 +
│ 0000946909078 │ Augason Farms, Vital Wheat Gluten        │
 +
│ 0003026400168 │ Grainaissance, mochi, cashew-date        │
 +
│      ·      │        ·                                │
 +
│      ·      │        ·                                │
 +
│      ·      │        ·                                │
 +
│ 0003003400513 │ Bread, hearty rye                        │
 +
│ 0003003400510 │ Stone Ground Wheat Bread                  │
 +
│ 0003003400514 │ Homestyle potato bread                    │
 +
│ 20043131      │ Sliced Plain Bagel                        │
 +
│ 7020656900144 │ Havregranola Jordbær og bringebær        │
 +
├───────────────┴───────────────────────────────────────────┤
 +
│ 51377 rows (10 shown)                          2 columns │
 +
└───────────────────────────────────────────────────────────┘
 +
 
 +
* '''Who are the biggest contributors?'''
 +
 
 +
''Ex: Top 10 best contributors in OFF''
 +
SELECT creator, count(*) AS count
 +
FROM read_parquet('products.parquet')
 +
GROUP BY creator
 +
ORDER BY count DESC
 +
LIMIT 10;
 +
┌────────────────────────────┬─────────┐
 +
│          creator          │  count  │
 +
│          varchar          │  int64  │
 +
├────────────────────────────┼─────────┤
 +
│ kiliweb                    │ 1883982 │
 +
│ foodvisor                  │  208270 │
 +
│ openfoodfacts-contributors │  199459 │
 +
│ usda-ndb-import            │  169554 │
 +
│ org-database-usda          │  134461 │
 +
│ prepperapp                │  110841 │
 +
│ macrofactor                │  92148 │
 +
│ foodless                  │  87839 │
 +
│ smoothie-app              │  74339 │
 +
│ inf                        │  37999 │
 +
├────────────────────────────┴─────────┤
 +
│ 10 rows                    2 columns │
 +
└──────────────────────────────────────┘
 +
 
 +
===== DATE & ARRAY fields =====
 +
 
 +
* '''Number of added products per year'''
 +
 
 +
--entry_dates_tags is a list of texts. We take the value at position 3: the year
 +
SELECT entry_dates_tags[3] AS year, count(*) AS count
 +
FROM read_parquet('products.parquet')
 +
GROUP BY year
 +
ORDER BY year DESC;
 +
┌─────────┬────────┐
 +
│  year  │ count  │
 +
│ varchar │ int64  │
 +
├─────────┼────────┤
 +
│ 2024    │ 463257 │
 +
│ 2023    │ 361240 │
 +
│ 2022    │ 598326 │
 +
│ 2021    │ 514052 │
 +
│ 2020    │ 466269 │
 +
│ 2019    │ 364272 │
 +
│ 2018    │ 318010 │
 +
│ 2017    │ 279737 │
 +
│ 2016    │  44618 │
 +
│ 2015    │  33968 │
 +
│ 2014    │  12892 │
 +
│ 2013    │  9587 │
 +
│ 2012    │  4267 │
 +
│ 1970    │      3 │
 +
│        │      1 │
 +
├─────────┴────────┤
 +
│    15 rows      │
 +
└──────────────────┘
 +
 
 +
===== DICTIONNARY fields =====
 +
 
 +
* '''Extract macro-nutriments'''
 +
 
 +
''Nutriments are stored as a dictionnary {"proteins": "4.2", "proteins_unit": "g", ...}''
 +
--TRY_CAST attributes the FLOAT type to the extracted value, while preventing errors
 +
SET threads to 4; --avoid out of memory issue by limiting the number of threads
 +
SELECT
 +
  code,
 +
  product_name,
 +
  TRY_CAST(nutriments -> 'proteins' AS FLOAT) as proteins,
 +
  TRY_CAST(nutriments -> 'fat' AS FLOAT) as fat,
 +
  TRY_CAST(nutriments -> 'carbohydrates' AS FLOAT) as carbohydrates
 +
FROM read_parquet('products.parquet')
 +
WHERE
 +
  (nutriments ->> 'proteins_unit') = 'g' AND
 +
  (nutriments ->> 'fat_unit') = 'g' AND
 +
  (nutriments ->> 'carbohydrates_unit') = 'g';
 +
┌───────────────┬──────────────────────────────────────────────┬───────────┬───────┬───────────────┐
 +
│    code      │                product_name                │ proteins  │  fat  │ carbohydrates │
 +
│    varchar    │                  varchar                    │  float  │ float │    float    │
 +
├───────────────┼──────────────────────────────────────────────┼───────────┼───────┼───────────────┤
 +
│ 0000101209159 │ Véritable pâte à tartiner noisettes chocol…  │      8.0 │  48.0 │          36.0 │
 +
│ 0000105000011 │ Lagg's, chamomile herbal tea                │      0.0 │  0.0 │          70.0 │
 +
│ 0000105000042 │ Lagg's, herbal tea, peppermint              │      0.0 │  0.0 │          1.47 │
 +
│ 0000105000059 │ Linden Flowers Tea                          │      0.0 │  0.0 │        53.33 │
 +
│ 0000105000073 │ Herbal Tea, Hibiscus                        │    66.67 │  0.0 │          60.0 │
 +
│      ·      │      ·                                      │        ·  │    ·  │            ·  │
 +
│      ·      │      ·                                      │        ·  │    ·  │            ·  │
 +
│      ·      │      ·                                      │        ·  │    ·  │            ·  │
 +
│ 4711252014016 │ Peanup choco                                │      15.8 │  32.0 │          34.6 │
 +
│ 18785784      │ Not required                                │      1.4 │  16.0 │          17.0 │
 +
│ 4994860304117 │ 塩羊羮                                      │      3.9 │  0.5 │          60.6 │
 +
│ 5601312079989 │ Mini delícias (cobertas com chocolate de l…  │ 6.3333335 │  24.0 │          64.0 │
 +
│ 8017104001033 │ Peperoni grigliati                          │      1.8 │  0.1 │          7.6 │
 +
├───────────────┴──────────────────────────────────────────────┴───────────┴───────┴───────────────┤
 +
│ 2396027 rows (10 shown)                                                                5 columns │
 +
└──────────────────────────────────────────────────────────────────────────────────────────────────┘
 +
''Enjoy playing with the database!''
    
=== MongoDB dump ===
 
=== MongoDB dump ===
Retrieved from "https://wiki.openfoodfacts.org/Special:MobileDiff/24971"

Navigation menu