Line 208:
Line 208:
==== How to exploit the Parquet database using DuckDB: Use-Cases ====
==== 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:'''
+
Look at our [[DuckDB Cheatsheet]] to see DuckDB queries corresponding to use-cases of the community.
−
−
===== ''TEXT fields'' =====
−
−
* '''Search by Name'''
−
−
''Ex: search for all product name containing the term "beurre"''
−
SELECT code, product_name
−
FROM read_parquet('food.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('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
−
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 │
−
└──────────────────────────────────────┘
−
−
===== 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('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 │
−
└──────────────────┘
−
−
===== 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('food.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 ===