Changes

Jump to navigation Jump to search
+ Import CSV to DuckDB
Line 6: Line 6:     
=== Looking for a selection of products? ===
 
=== Looking for a selection of products? ===
Then use the advanced search. The Open Food Facts advanced search feature allows to download selections of the data. See: https://world.openfoodfacts.org/cgi/search.pl
+
Then use '''the advanced search'''. The Open Food Facts advanced search feature allows to download selections of the data. See: https://world.openfoodfacts.org/cgi/search.pl
    
When your search is done, you will be able to download the selection in '''CSV or Excel format''', just give a try!
 
When your search is done, you will be able to download the selection in '''CSV or Excel format''', just give a try!
 +
 +
Do notice you can download up to 10,000 results only. If you need to download more results you have to use some other methods described below.
    
=== Looking for the whole database? ===
 
=== Looking for the whole database? ===
 
The whole database can be downloaded at https://world.openfoodfacts.org/data
 
The whole database can be downloaded at https://world.openfoodfacts.org/data
   −
It's very big. Open Food Facts hosts more than 2,600,000 products (as of October 2022). So you will probably need skills to reuse the data.
+
It's very big. Open Food Facts hosts more than 2,800,000 products (as of April 2023). So you will probably need skills to reuse the data.
    
You'll be able to find here different kinds of data.
 
You'll be able to find here different kinds of data.
Line 71: Line 73:  
* product countries
 
* product countries
 
* full categories hierarchy imported from the <code>categories.txt</code> taxonomy ([https://github.com/openfoodfacts/openfoodfacts-server/tree/master/taxonomies see])
 
* full categories hierarchy imported from the <code>categories.txt</code> taxonomy ([https://github.com/openfoodfacts/openfoodfacts-server/tree/master/taxonomies see])
 +
 +
==== Import CSV to DuckDB ====
 +
[https://duckdb.org/ DuckDB] is very close to SQLite, except it has higher performances: database size is 3 times lighter, and requests performs 5-10 times better.
 +
# Discard invalid characters
 +
<nowiki>#</nowiki> duckdb doesn't like invalid UTF8. It did not want to read some parquet file as such, with the following error:
 +
<nowiki>#</nowiki> Error: near line 1: Invalid Input Error: Invalid string encoding found in Parquet file: value "........."
 +
<nowiki>#</nowiki> (occuring namely on this product: <nowiki>https://world.openfoodfacts.org/product/9900109008673?rev=4</nowiki> )
 +
<nowiki>#</nowiki> The issue, and its solution below, seems to be well-known: <nowiki>https://til.simonwillison.net/linux/iconv</nowiki>
 +
iconv -f utf-8 -t utf-8 -c en.openfoodfacts.org.products.csv -o en.openfoodfacts.org.products.converted.csv
 +
 +
<nowiki>#</nowiki> Create duckdb database and import data
 +
duckdb products.db <<EOF
 +
CREATE TABLE products AS
 +
<nowiki> </nowiki>   SELECT * FROM read_csv_auto('en.openfoodfacts.org.products.converted.csv', quote=<nowiki>''</nowiki>, sample_size=3000000, delim='\t');
 +
EOF
 +
 +
<nowiki>#</nowiki> Then you can try a SQL request
 +
duckdb products.db -csv <<EOF
 +
SELECT * FROM products
 +
<nowiki> </nowiki> WHERE completeness > 0.99 -- products with a good level of completeness
 +
<nowiki> </nowiki> ORDER BY last_modified_datetime LIMIT 10;
 +
EOF
    
==== Python ====
 
==== Python ====
Line 240: Line 264:  
We are testing a new kind of tool to provide the data: every day an SQL database is fed by the regular daily CSV export, and published online thanks to Datasette tool.
 
We are testing a new kind of tool to provide the data: every day an SQL database is fed by the regular daily CSV export, and published online thanks to Datasette tool.
   −
The tool, called Mirabelle, can be found here: http://mirabelle.openfoodfacts.org/
+
The tool, called ''[[Mirabelle]]'', can be found here: http://mirabelle.openfoodfacts.org/
    
The whole CSV export can be found here: http://mirabelle.openfoodfacts.org/products/all
 
The whole CSV export can be found here: http://mirabelle.openfoodfacts.org/products/all
Line 246: Line 270:  
* The tool supports simple queries with a form, and also facet navigation.
 
* The tool supports simple queries with a form, and also facet navigation.
 
* For those who know SQL language, it allows rich and complex queries.
 
* For those who know SQL language, it allows rich and complex queries.
      
What's different with [https://world.openfoodfacts.org/cgi/search.pl Open Food Facts advanced search]?
 
What's different with [https://world.openfoodfacts.org/cgi/search.pl Open Food Facts advanced search]?
Line 264: Line 287:  
  where countries_en like "%germany%" and unique_scans_n is not null
 
  where countries_en like "%germany%" and unique_scans_n is not null
 
  order by unique_scans_n desc
 
  order by unique_scans_n desc
  -- the limit here displays 20 results; remove it or comment it with "--" when you build your CSV export
+
  -- the limit here displays 20 results; the link "CSV without limit" below allow you to download all the data without limit
 
  limit 20
 
  limit 20
<nowiki>https://mirabelle.openfoodfacts.org/products?sql=--+Products+from+Germany+that+have+been+scanned+at+least+one+time%0D%0Aselect+code%2C+product_name+from+%5Ball%5D%0D%0Awhere+countries_en+like+%22%25germany%25%22+and+unique_scans_n+is+not+null%0D%0Aorder+by+unique_scans_n+desc%0D%0A--+the+limit+here+displays+20+results%3B+remove+it+or+comment+it+with+%22--%22+when+you+build+your+CSV+export%0D%0Alimit+20</nowiki>
+
https://mirabelle.openfoodfacts.org/products?sql=--+Products+from+Germany+that+have+been+scanned+at+least+one+time%0D%0Aselect+code%2C+product_name+from+%5Ball%5D%0D%0Awhere+countries_en+like+%22%25germany%25%22+and+unique_scans_n+is+not+null%0D%0Aorder+by+unique_scans_n+desc%0D%0A--+the+limit+here+displays+20+results%3B+the+link+%22CSV+without+limit%22+below+allows+to+download+all+the+data+without+limit%0D%0Alimit+20
   −
'''2 -- Copy "CSV" link on the result page.'''
+
'''2 -- Click on the link "CSV without limit"'''
   −
'''3 -- If necessary, edit the link to remove the "limit+20" limit to get all the products.'''
+
Maybe you have to wait several seconds. It will download a product.csv file.
   −
Eg. (don't click this link if you don't want to get 90,000+ products) <nowiki>https://mirabelle.openfoodfacts.org/products.csv?sql=--+Products+from+Germany+that+have+been+scanned+at+least+one+time%0D%0Aselect+code%2C+product_name+from+%5Ball%5D%0D%0Awhere+countries_en+like+%22%25germany%25%22+and+unique_scans_n+is+not+null%0D%0Aorder+by+unique_scans_n+desc%0D%0A--+the+limit+here+displays+20+results%3B+remove+it+or+comment+it+with+%22--%22+when+you+build+your+CSV+export%0D%0A&_size=max</nowiki>
+
==== Tips ====
   −
Now you can use this link to download the CSV with your favourite tool (wget, curl, web browser, etc.).
+
* Several fields -- such as <code>countries_en</code>, <code>categories_en</code>, etc. -- contain multiple values. To query a particular value you have to use the operator <code>like</code> and use percents like this: <code>like %italy%</code>.

Navigation menu