984
edits
Changes
Jump to navigation
Jump to search
Line 73:
Line 73: + + + + + + + + + + + + + + + + + + + + + +
+ Import CSV to DuckDB
* 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 ====