Line 198:
Line 198:
==== DuckDB to query the database ====
==== DuckDB to query the database ====
Here again, this great tool allows to request remote parquet files with the command line.
Here again, this great tool allows to request remote parquet files with the command line.
−
$ duckdb :memory: "SELECT * from '<nowiki>https://huggingface.co/datasets/openfoodfacts/product-database/resolve/main/products.parquet'</nowiki> LIMIT 10;"
+
$ duckdb :memory: "SELECT * from 'https://huggingface.co/datasets/openfoodfacts/product-database/resolve/main/food<nowiki/>.parquet' LIMIT 10;"
The request can be a bit long (~15 seconds).
The request can be a bit long (~15 seconds).
Line 205:
Line 205:
You can easily install DuckDB on your Command Line Interface (CLI) by reading our [https://blog.openfoodfacts.org/en/news/food-transparency-in-the-palm-of-your-hand-explore-the-largest-open-food-database-using-duckdb-%F0%9F%A6%86x%F0%9F%8D%8A blog post].
You can easily install DuckDB on your Command Line Interface (CLI) by reading our [https://blog.openfoodfacts.org/en/news/food-transparency-in-the-palm-of-your-hand-explore-the-largest-open-food-database-using-duckdb-%F0%9F%A6%86x%F0%9F%8D%8A blog post].
−
$ duckdb :memory: "SELECT * from 'products.parquet' LIMIT 10;"
+
$ duckdb :memory: "SELECT * from 'food.parquet' LIMIT 10;"
==== How to exploit the Parquet database using DuckDB: Use-Cases ====
==== How to exploit the Parquet database using DuckDB: Use-Cases ====
Line 218:
Line 218:
''Ex: search for all product name containing the term "beurre"''
''Ex: search for all product name containing the term "beurre"''
SELECT code, product_name
SELECT code, product_name
−
FROM read_parquet('products.parquet')
+
FROM read_parquet('food.parquet')
WHERE product_name
WHERE product_name
ILIKE '%beurre%';
ILIKE '%beurre%';
Line 246:
Line 246:
''Ex:'' ''search for all product belonging to "plant-based food" and "cereals" categories''
''Ex:'' ''search for all product belonging to "plant-based food" and "cereals" categories''
SELECT code, product_name
SELECT code, product_name
−
FROM read_parquet('products.parquet')
+
FROM read_parquet('food.parquet')
WHERE categories ILIKE '%plant-based foods%' AND categories ILIKE '%cereal%';
WHERE categories ILIKE '%plant-based foods%' AND categories ILIKE '%cereal%';
┌───────────────┬───────────────────────────────────────────┐
┌───────────────┬───────────────────────────────────────────┐
Line 273:
Line 273:
''Ex: Top 10 best contributors in OFF''
''Ex: Top 10 best contributors in OFF''
SELECT creator, count(*) AS count
SELECT creator, count(*) AS count
−
FROM read_parquet('products.parquet')
+
FROM read_parquet('food.parquet')
GROUP BY creator
GROUP BY creator
ORDER BY count DESC
ORDER BY count DESC
Line 301:
Line 301:
--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
−
FROM read_parquet('products.parquet')
+
FROM read_parquet('food.parquet')
GROUP BY year
GROUP BY year
ORDER BY year DESC;
ORDER BY year DESC;
Line 340:
Line 340:
TRY_CAST(nutriments -> 'fat' AS FLOAT) as fat,
TRY_CAST(nutriments -> 'fat' AS FLOAT) as fat,
TRY_CAST(nutriments -> 'carbohydrates' AS FLOAT) as carbohydrates
TRY_CAST(nutriments -> 'carbohydrates' AS FLOAT) as carbohydrates
−
FROM read_parquet('products.parquet')
+
FROM read_parquet('food.parquet')
WHERE
WHERE
(nutriments ->> 'proteins_unit') = 'g' AND
(nutriments ->> 'proteins_unit') = 'g' AND