DuckDB Cheatsheet

From Open Food Facts wiki
Jump to navigation Jump to search

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';

You can also restrict listing to column_name and column_type:

select column_name, column_type from (describe select * from 'food.parquet');

At the end of the page, we provide the full list of fields based on this query.

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:butters')
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
LIMIT 7;
┌─────┬─────────────────┬──────────────────────────────────────────────┐
│ nb  │ any_value(code) │                allergens_tags                │
├─────┼─────────────────┼──────────────────────────────────────────────┤
│ 337 │ 0205004001439   │ [en:gluten, fr:avoine]                       │
│ 131 │ 3445731105896   │ [fr:non]                                     │
│ 116 │ 3175681118911   │ [en:gluten, en:milk, en:soybeans, fr:avoine] │
│ 104 │ 3175681140776   │ [en:gluten, en:nuts, fr:avoine]              │
│ 104 │ 20936365        │ [en:gluten, en:milk, fr:avoine]              │
│ 56  │ 3222476727046   │ [en:gluten, fr:avoine, fr:avoine]            │
│ 55  │ 3700389705158   │ [fr:non-renseigne]                           │
└─────┴─────────────────┴──────────────────────────────────────────────┘

Playing with dates

Last products created in 2023?

SELECT code
 , strftime(epoch_ms(created_t * 1000), '%Y-%m-%dT%H:%M:%SZ') AS iso_date
 , creator
 , 'https://world.openfoodfacts.org/product/' || code as url
FROM read_parquet('food.parquet')
WHERE true
    AND created_t < epoch (TIMESTAMP '2024-01-01 00:00:00')
    AND created_t > epoch (TIMESTAMP '2023-12-31 00:00:00')
ORDER BY iso_date desc
LIMIT 7;
┌───────────────┬──────────────────────┬──────────────────┬───────────────────────────────────────────────────────┐
│     code      │       iso_date       │     creator      │                          url                          │
├───────────────┼──────────────────────┼──────────────────┼───────────────────────────────────────────────────────┤
│ 0699058466038 │ 2023-12-31T23:59:09Z │ smoothie-app     │ https://world.openfoodfacts.org/product/0699058466038 │
│ 2030503844885 │ 2023-12-31T23:58:38Z │ kiliweb          │ https://world.openfoodfacts.org/product/2030503844885 │
│ 3551720206854 │ 2023-12-31T23:56:51Z │ kiliweb          │ https://world.openfoodfacts.org/product/3551720206854 │
│ 17705288      │ 2023-12-31T23:54:45Z │ smoothie-app     │ https://world.openfoodfacts.org/product/17705288      │
│ 72734452      │ 2023-12-31T23:52:06Z │ insectproductadd │ https://world.openfoodfacts.org/product/72734452      │
│ 3495040348439 │ 2023-12-31T23:51:38Z │ kiliweb          │ https://world.openfoodfacts.org/product/3495040348439 │
│ 8801114140741 │ 2023-12-31T23:50:58Z │ foodless         │ https://world.openfoodfacts.org/product/8801114140741 │
└───────────────┴──────────────────────┴──────────────────┴───────────────────────────────────────────────────────┘

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

Count things

CASE allows to filter things when counting, eg. a given value in a list. Example:

-- count the number of products (nb),
-- the number of products with en:ingredients-photo-selected (nb_ing_photo_selected),
-- the number of products with en:ingredients-photo-to-be-selected (nb_ing_photo_to_be_selected),
-- the number of products with ingredients-photo-selected or ingredients-photo-to-be-selected (nb_ing_photo_tags),
-- and the number of products lacking either en:ingredients-photo-selected
--     or en:ingredients-photo-to-be-selected (ing_photo_tags_lacking)
SELECT 
count(*) as nb,
count(CASE WHEN 'en:ingredients-photo-selected' IN (SELECT unnest(states_tags)) THEN 1 END) AS nb_ing_photo_selected,
count(CASE WHEN 'en:ingredients-photo-to-be-selected' IN (SELECT unnest(states_tags)) THEN 1 END) AS nb_ing_photo_to_be_selected,
nb_ing_photo_selected + nb_ing_photo_to_be_selected as nb_ing_photo_tags,
nb - nb_ing_photo_tags as ing_photo_tags_lacking
FROM read_parquet('food.parquet');
┌─────────┬───────────────────────┬─────────────────────────────┬───────────────────┬────────────────────────┐
│   nb    │ nb_ing_photo_selected │ nb_ing_photo_to_be_selected │ nb_ing_photo_tags │ ing_photo_tags_lacking │
├─────────┼───────────────────────┼─────────────────────────────┼───────────────────┼────────────────────────┤
│ 3621956 │ 951676                │ 1949239                     │ 2900915           │ 721041                 │
└─────────┴───────────────────────┴─────────────────────────────┴───────────────────┴────────────────────────┘

UNNEST allows to play with nested fields such as `images`, `nutriments`, `packagings`, etc.

-- Top 10 languages for the ingredients' images
SELECT n.unnest.key, count(code) as nb
FROM (SELECT code, images FROM read_parquet('food.parquet')) as f,
    UNNEST(f.images) as n
WHERE n.unnest.key like 'ingredients_%'
GROUP BY n.unnest.key
ORDER BY nb DESC
LIMIT 10;
┌────────────────┬────────┐
│      key       │   nb   │
├────────────────┼────────┤
│ ingredients_fr │ 574588 │
│ ingredients_en │ 209511 │
│ ingredients_de │ 106001 │
│ ingredients_es │ 77712  │
│ ingredients_it │ 28467  │
│ ingredients_nl │ 18756  │
│ ingredients_pt │ 10820  │
│ ingredients_pl │ 9465   │
│ ingredients_ro │ 7115   │
│ ingredients_sv │ 6744   │
└────────────────┴────────┘


Annex: parquet schema

column_name column_type
additives_n INTEGER
additives_tags VARCHAR[]
allergens_tags VARCHAR[]
brands_tags VARCHAR[]
brands VARCHAR
categories VARCHAR
categories_tags VARCHAR[]
checkers_tags VARCHAR[]
ciqual_food_name_tags VARCHAR[]
cities_tags VARCHAR[]
code VARCHAR
compared_to_category VARCHAR
complete INTEGER
completeness FLOAT
correctors_tags VARCHAR[]
countries_tags VARCHAR[]
created_t BIGINT
creator VARCHAR
data_quality_errors_tags VARCHAR[]
data_quality_info_tags VARCHAR[]
data_quality_warnings_tags VARCHAR[]
data_sources_tags VARCHAR[]
ecoscore_data VARCHAR
ecoscore_grade VARCHAR
ecoscore_score INTEGER
ecoscore_tags VARCHAR[]
editors VARCHAR[]
emb_codes_tags VARCHAR[]
emb_codes VARCHAR
entry_dates_tags VARCHAR[]
food_groups_tags VARCHAR[]
generic_name STRUCT(lang VARCHAR, "text" VARCHAR)[]
images STRUCT("key" VARCHAR, imgid INTEGER, sizes STRUCT("100" STRUCT(h INTEGER, w INTEGER), "200" STRUCT(h INTEGER, w INTEGER), "400" STRUCT(h INTEGER, w INTEGER), "full" STRUCT(h INTEGER, w INTEGER)), uploaded_t BIGINT, uploader VARCHAR)[]
informers_tags VARCHAR[]
ingredients_analysis_tags VARCHAR[]
ingredients_from_palm_oil_n INTEGER
ingredients_n INTEGER
ingredients_original_tags VARCHAR[]
ingredients_percent_analysis INTEGER
ingredients_tags VARCHAR[]
ingredients_text STRUCT(lang VARCHAR, "text" VARCHAR)[]
ingredients_with_specified_percent_n INTEGER
ingredients_with_unspecified_percent_n INTEGER
ingredients_without_ciqual_codes_n INTEGER
ingredients_without_ciqual_codes VARCHAR[]
ingredients VARCHAR
known_ingredients_n INTEGER
labels_tags VARCHAR[]
labels VARCHAR
lang VARCHAR
languages_tags VARCHAR[]
last_edit_dates_tags VARCHAR[]
last_editor VARCHAR
last_image_t BIGINT
last_modified_by VARCHAR
last_modified_t BIGINT
last_updated_t BIGINT
link VARCHAR
main_countries_tags VARCHAR[]
manufacturing_places_tags VARCHAR[]
manufacturing_places VARCHAR
max_imgid INTEGER
minerals_tags VARCHAR[]
misc_tags VARCHAR[]
new_additives_n INTEGER
no_nutrition_data BOOLEAN
nova_group INTEGER
nova_groups_tags VARCHAR[]
nova_groups VARCHAR
nucleotides_tags VARCHAR[]
nutrient_levels_tags VARCHAR[]
nutriments STRUCT("name" VARCHAR, "value" FLOAT, "100g" FLOAT, serving FLOAT, unit VARCHAR, prepared_value FLOAT, prepared_100g FLOAT, prepared_serving FLOAT, prepared_unit VARCHAR)[]
nutriscore_grade VARCHAR
nutriscore_score INTEGER
nutrition_data_per VARCHAR
obsolete BOOLEAN
origins_tags VARCHAR[]
origins VARCHAR
owner_fields STRUCT(field_name VARCHAR, "timestamp" BIGINT)[]
owner VARCHAR
packagings_complete BOOLEAN
packaging_recycling_tags VARCHAR[]
packaging_shapes_tags VARCHAR[]
packaging_tags VARCHAR[]
packaging_text STRUCT(lang VARCHAR, "text" VARCHAR)[]
packaging VARCHAR
packagings STRUCT(material VARCHAR, number_of_units BIGINT, quantity_per_unit VARCHAR, quantity_per_unit_unit VARCHAR, quantity_per_unit_value VARCHAR, recycling VARCHAR, shape VARCHAR, weight_measured FLOAT)[]
photographers VARCHAR[]
popularity_key BIGINT
popularity_tags VARCHAR[]
product_name STRUCT(lang VARCHAR, "text" VARCHAR)[]
product_quantity_unit VARCHAR
product_quantity VARCHAR
purchase_places_tags VARCHAR[]
quantity VARCHAR
rev INTEGER
scans_n INTEGER
serving_quantity VARCHAR
serving_size VARCHAR
states_tags VARCHAR[]
stores_tags VARCHAR[]
stores VARCHAR
traces_tags VARCHAR[]
unique_scans_n INTEGER
unknown_ingredients_n INTEGER
unknown_nutrients_tags VARCHAR[]
vitamins_tags VARCHAR[]
with_non_nutritive_sweeteners INTEGER
with_sweeteners INTEGER