Difference between revisions of "DuckDB Cheatsheet"

From Open Food Facts wiki
Jump to navigation Jump to search
(Add an example)
(+ describe the data)
Line 1: Line 1:
 
The Parquet file dump of the Open Food Facts database contains a variety of data in different format, such as string, list, struct, timestamp,...
 
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.
+
In this cheat sheet, 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 <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]])
+
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]]).
 +
 
 +
==== Modify the type of output ====
 +
By default, DuckDB adopts its own display mode. But this mode truncates the display when the output is too long. There are many others outputs, see [https://duckdb.org/docs/api/cli/output_formats.html DuckDB documentation about them].
 +
 
 +
==== Describe the data ====
 +
It's interesting to have the whole list of fields and their characteristics.
 +
.mode box -- comment: change display mode to nice table
 +
describe select * from 'food.parquet';
  
 
==== Creating a new parquet file for my country ====
 
==== Creating a new parquet file for my country ====

Revision as of 16:25, 21 January 2025

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 cheat sheet, 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).

Modify the type of output

By default, DuckDB adopts its own display mode. But this mode truncates the display when the output is too long. There are many others outputs, see DuckDB documentation about them.

Describe the data

It's interesting to have the whole list of fields and their characteristics.

.mode box -- comment: change display mode to nice table
describe select * from 'food.parquet';

Creating a new parquet file for my country

COPY (
  SELECT * FROM 'food.parquet'
  WHERE 'en:canada' IN food.countries_tags
  )
TO 'off-canada.parquet' (FORMAT 'parquet', COMPRESSION 'zstd');

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      │
└──────────────────┘

Search for a string in a list

Eg. search in the list allergen_tags values containing fr:. You have to convert the array into a string using the array_to_string.

SELECT count(code) as nb, ANY_VALUE(code), allergens_tags
  FROM  'food.parquet'
where regexp_matches(array_to_string(allergens_tags, ','), 'fr:')
group by allergens_tags
order by nb desc

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 │
└─────────────┘