Difference between revisions of "DuckDB Cheatsheet"

From Open Food Facts wiki
Jump to navigation Jump to search
(Added link to explanation on using parquet / duckdb)
 
(6 intermediate revisions by 2 users not shown)
Line 1: Line 1:
−
The OFF database contains a variety of data in different format, such as '''TEXTS, LISTS, STRUCT, DATES''', and even more...
+
The Parquet file dump of the Open Food Facts database contains a variety of data in different format, such as string, list, struct, timestamp,...
  
−
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:'''
+
In this cheatsheet, we will learn how to use the Parquet dump using DuckDB to perform data analysis, by solving some of the most common use-cases asked by the community.
  
−
''Ex: display product name (for main language) of all products of with category '''en:butters'''.
+
We assume that you've downloaded the <code>food.parquet</code> file locally, and that you've launched Duckdb CLI. (see [[Reusing Open Food Facts Data#Parquet file hosted on Hugging Face .28beta.29|Reusing Open Food Facts Data]])
 +
 
 +
==== Display product name (for main language) of all products of with category <code>en:butters</code> ====
 
  SELECT
 
  SELECT
 
   code,
 
   code,
 
   unnest(
 
   unnest(
−
     list_filter (product_name, x -> x.lang = = 'main')
+
     list_filter (product_name, x -> x.lang == 'main')
 
   ) ['text']
 
   ) ['text']
−
FROM
+
FROM
 
   'food.parquet'
 
   'food.parquet'
−
WHERE
+
WHERE
 
   list_contains (categories_tags, 'en:milks')
 
   list_contains (categories_tags, 'en:milks')
−
LIMIT
+
LIMIT
−
   10;';
+
   10;
−
  ┌───────────────┬──────────────────────────────────────────────────────────────┐
+
  ┌───────────────┬─────────────────────────────────────────────────────────────────────┐
−
  │    code      │                         product_name                         │
+
  │    code      │ unnest(list_filter(product_name, (x -> (x.lang = 'main'))))['text'] │
−
  │    varchar    │                           varchar                           │
+
  │    varchar    │                               varchar                               │
−
  ├───────────────┼──────────────────────────────────────────────────────────────┤
+
  ├───────────────┼─────────────────────────────────────────────────────────────────────┤
−
  │ 0000234022960 │ Croissants pur beurre                                        │
+
  │ 0011110807625 │ Quality food centers, salted butter                                │
−
  │ 0002000013363 │ Grandes galettes au beurre                                  │
+
  │ 0011110808998 │ Unsalted Butter                                                    │
−
  │ 0008421369416 │ Brioche Pur Beurre                                          │
+
  │ 0011110842640 │ Ralphs, Salted Sticks, Butter                                      │
−
  │ 00089739      │ Beurre doux                                                  │
+
  │ 0011110852878 │ Salted butter                                                      │
−
  │ 0016073123478 │ Beurre d'arachide en poudre                                  │
+
  │ 0011110854384 │ Qfc, unsalted butter                                                │
−
  │       ·      │             ·                                              │
+
  │ 0011110862600 │ King Soopers City Market, Salted Butter                            │
−
  │       ·      │             ·                                              │
+
  │ 0011110863256 │ Salted Butter                                                      │
−
  │       ·      │             ·                                              │
+
  │ 0011110863270 │ Unsalted Butter                                                    │
−
  │ 3560071532741 │ Beurre demi-sel À teneur réduite en matière grasse 60% Mat…  │
+
  │ 0011110893017 │ Salted Butter                                                      │
−
  │ 77646187      │ Beurre sans sel                                             │
+
  │ 0011110893055 │ Unsalted butter sticks                                             │
−
  │ 3596710524006 │ Cosmia crème corps nourrissante - beurre de macadamia + ni…  │
+
  ├───────────────┴─────────────────────────────────────────────────────────────────────┤
−
  │ 3596710523986 │ Cosmia crème mains nourrissante - au beurre de karité et à…  │
+
  │ 10 rows                                                                   2 columns │
−
│ 4820154481786 │ Petit Beurre                                                │
+
  └─────────────────────────────────────────────────────────────────────────────────────┘
−
├───────────────┴──────────────────────────────────────────────────────────────┤
 
−
│ 13791 rows (10 shown)                                              2 columns │  
 
−
  └──────────────────────────────────────────────────────────────────────────────┘
 
  
−
* '''Search by category'''
+
==== Who are the biggest contributors? ====
−
 
+
''Ex: Top 10 best contributors in OFF''
−
''Ex:'' ''search for all product belonging to "plant-based food" and "cereals" categories''
 
−
SELECT code, product_name
 
−
FROM read_parquet('food.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  
 
  SELECT creator, count(*) AS count  
 
  FROM read_parquet('food.parquet')
 
  FROM read_parquet('food.parquet')
Line 89: Line 61:
 
  └──────────────────────────────────────┘
 
  └──────────────────────────────────────┘
  
−
===== DATE & ARRAY fields =====
+
==== Number of added products per year ====
−
 
 
−
* '''Number of added products per year'''
 
−
 
 
 
  --entry_dates_tags is a list of texts. We take the value at position 3: the 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  
 
  SELECT entry_dates_tags[3] AS year, count(*) AS count  
Line 121: Line 90:
 
  └──────────────────┘
 
  └──────────────────┘
  
−
===== DICTIONNARY fields =====
+
==== How many images where uploaded in 2024? ====
−
 
 
−
* '''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
 
  SELECT
−
   code,
+
   SUM(images_filter_count) AS image_count
−
   product_name,
+
FROM
−
  TRY_CAST(nutriments -> 'proteins' AS FLOAT) as proteins,
+
   (
−
  TRY_CAST(nutriments -> 'fat' AS FLOAT) as fat,
+
    SELECT
−
  TRY_CAST(nutriments -> 'carbohydrates' AS FLOAT) as carbohydrates
+
      len (
−
FROM read_parquet('food.parquet')
+
        list_filter (
−
WHERE
+
          images,
−
  (nutriments ->> 'proteins_unit') = 'g' AND
+
          x -> struct_extract (x, 'uploaded_t') > epoch (TIMESTAMP '2024-01-01 00:00:00')
−
  (nutriments ->> 'fat_unit') = 'g' AND
+
          AND struct_extract (x, 'uploaded_t') < epoch (TIMESTAMP '2025-01-01 00:00:00')
−
  (nutriments ->> 'carbohydrates_unit') = 'g';
+
        )
−
┌───────────────┬──────────────────────────────────────────────┬───────────┬───────┬───────────────┐
+
      ) as images_filter_count
−
│    code     │                product_name                │ proteins  │  fat  │ carbohydrates │
+
     FROM
−
│    varchar    │                  varchar                    │  float  │ float │    float    │
+
      'food.parquet'
−
├───────────────┼──────────────────────────────────────────────┼───────────┼───────┼───────────────┤
+
  );
−
│ 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 │
+
  │ image_count │
−
│ 0000105000042 │ Lagg's, herbal tea, peppermint              │      0.0 │  0.0 │          1.47 │
+
  │   int128   │
−
│ 0000105000059 │ Linden Flowers Tea                          │      0.0 │  0.0 │        53.33 │
+
  ├─────────────┤
−
  │ 0000105000073 │ Herbal Tea, Hibiscus                        │    66.67 │  0.0 │          60.0 │
+
  │      716207 │
−
  │       ·      │      ·                                      │        ·  │    ·  │            ·  │
+
  └─────────────┘
−
  │       ·      │      ·                                      │        ·  │   ·  │            ·  │
+
[[Category:Metrics]]
−
  │      ·      │      ·                                      │        ·  │    ·  │            ·  │
 
−
  │ 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!''
 

Latest revision as of 10:17, 26 November 2024

The Parquet file dump of the Open Food Facts database contains a variety of data in different format, such as string, list, struct, timestamp,...

In this cheatsheet, we will learn how to use the Parquet dump using DuckDB to perform data analysis, by solving some of the most common use-cases asked by the community.

We assume that you've downloaded the food.parquet file locally, and that you've launched Duckdb CLI. (see Reusing Open Food Facts Data)

Display product name (for main language) of all products of with category en:butters

SELECT
 code,
 unnest(
   list_filter (product_name, x -> x.lang == 'main')
 ) ['text']
FROM
 'food.parquet'
WHERE
 list_contains (categories_tags, 'en:milks')
LIMIT
 10;
┌───────────────┬─────────────────────────────────────────────────────────────────────┐
│     code      │ unnest(list_filter(product_name, (x -> (x.lang = 'main'))))['text'] │
│    varchar    │                               varchar                               │
├───────────────┼─────────────────────────────────────────────────────────────────────┤
│ 0011110807625 │ Quality food centers, salted butter                                 │
│ 0011110808998 │ Unsalted Butter                                                     │
│ 0011110842640 │ Ralphs, Salted Sticks, Butter                                       │
│ 0011110852878 │ Salted butter                                                       │
│ 0011110854384 │ Qfc, unsalted butter                                                │
│ 0011110862600 │ King Soopers City Market, Salted Butter                             │
│ 0011110863256 │ Salted Butter                                                       │
│ 0011110863270 │ Unsalted Butter                                                     │
│ 0011110893017 │ Salted Butter                                                       │
│ 0011110893055 │ Unsalted butter sticks                                              │
├───────────────┴─────────────────────────────────────────────────────────────────────┤
│ 10 rows                                                                   2 columns │
└─────────────────────────────────────────────────────────────────────────────────────┘

Who are the biggest contributors?

Ex: Top 10 best contributors in OFF 

SELECT creator, count(*) AS count 
FROM read_parquet('food.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 │
└──────────────────────────────────────┘

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('food.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      │
└──────────────────┘

How many images where uploaded in 2024?

SELECT
  SUM(images_filter_count) AS image_count
FROM
  (
    SELECT
      len (
        list_filter (
          images,
          x -> struct_extract (x, 'uploaded_t') > epoch (TIMESTAMP '2024-01-01 00:00:00')
          AND struct_extract (x, 'uploaded_t') < epoch (TIMESTAMP '2025-01-01 00:00:00')
        )
      ) as images_filter_count
    FROM
      'food.parquet'
  );
┌─────────────┐
│ image_count │
│   int128    │
├─────────────┤
│      716207 │
└─────────────┘
Retrieved from "https://wiki.openfoodfacts.org/index.php?title=DuckDB_Cheatsheet&oldid=25472"