114
edits
Changes
Jump to navigation
Jump to search
Line 198:
Line 198: − + Line 205:
Line 205: − + Line 218:
Line 218: − + Line 246:
Line 246: − + Line 273:
Line 273: − + Line 301:
Line 301: − + Line 340:
Line 340: − +
→Parquet file hosted on Hugging Face (beta)
==== 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).
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 ====
''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%';
''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%';
┌───────────────┬───────────────────────────────────────────┐
┌───────────────┬───────────────────────────────────────────┐
''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
--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;
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