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.
−
'''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.
+
==== Display product name (for main language) of all products of with category <code>en:butters</code> ====
SELECT
SELECT
code,
code,
Line 34:
Line 35:
└─────────────────────────────────────────────────────────────────────────────────────┘
└─────────────────────────────────────────────────────────────────────────────────────┘
−
'''Who are the biggest contributors?'''
+
==== Who are the biggest contributors? ====
−
''Ex: Top 10 best contributors in OFF''
''Ex: Top 10 best contributors in OFF''
Line 61:
Line 61:
└──────────────────────────────────────┘
└──────────────────────────────────────┘
−
'''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 91:
Line 90:
└──────────────────┘
└──────────────────┘
−
'''How many images where uploaded in 2024?'''
+
==== How many images where uploaded in 2024? ====
−
SELECT
SELECT
SUM(images_filter_count) AS image_count
SUM(images_filter_count) AS image_count