Changes

Jump to navigation Jump to search
+ dates + typos
Line 5: Line 5:  
We assume that you've downloaded the <code>food.parquet</code> file locally, and that you've launched DuckDB CLI. (see [[Reusing Open Food Facts Data#Parquet file hosted on Hugging Face .28beta.29|Reusing Open Food Facts Data]]).
 
We assume that you've downloaded the <code>food.parquet</code> file locally, and that you've launched DuckDB CLI. (see [[Reusing Open Food Facts Data#Parquet file hosted on Hugging Face .28beta.29|Reusing Open Food Facts Data]]).
   −
==== Modify the type of output ====
+
=== Modify the type of output ===
 
By default, DuckDB adopts its own display mode. But this mode truncates the display when the output is too long. There are many others outputs, see [https://duckdb.org/docs/api/cli/output_formats.html DuckDB documentation about them].
 
By default, DuckDB adopts its own display mode. But this mode truncates the display when the output is too long. There are many others outputs, see [https://duckdb.org/docs/api/cli/output_formats.html DuckDB documentation about them].
   −
==== Describe the data ====
+
=== Describe the data ===
 
It's interesting to have the whole list of fields and their characteristics.
 
It's interesting to have the whole list of fields and their characteristics.
 
  .mode box -- comment: change display mode to nice table
 
  .mode box -- comment: change display mode to nice table
Line 18: Line 18:  
At the end of the page, we provide the full list of fields based on this query.
 
At the end of the page, we provide the full list of fields based on this query.
   −
==== Creating a new parquet file for my country ====
+
=== Creating a new parquet file for my country ===
 
  COPY (
 
  COPY (
 
   SELECT * FROM 'food.parquet'
 
   SELECT * FROM 'food.parquet'
Line 25: Line 25:  
  TO 'off-canada.parquet' (FORMAT 'parquet', COMPRESSION 'zstd');
 
  TO 'off-canada.parquet' (FORMAT 'parquet', COMPRESSION 'zstd');
   −
==== Display product name (for main language) of all products of with category <code>en:butters</code> ====
+
=== Display product name (for main language) of all products of with category <code>en:butters</code>===
 
  SELECT
 
  SELECT
 
   code,
 
   code,
Line 52: Line 52:  
  └─────────────────────────────────────────────────────────────────────────────────────┘
 
  └─────────────────────────────────────────────────────────────────────────────────────┘
   −
==== 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 78: Line 78:  
  └──────────────────────────────────────┘
 
  └──────────────────────────────────────┘
   −
==== 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 107: Line 107:  
  └──────────────────┘
 
  └──────────────────┘
   −
==== Search for a string in a list ====
+
=== Search for a string in a list ===
 
Eg. search in the list <code>allergen_tags</code> values containing <code>fr:</code>. You have to convert the array into a string using the <code>array_to_string</code>.
 
Eg. search in the list <code>allergen_tags</code> values containing <code>fr:</code>. You have to convert the array into a string using the <code>array_to_string</code>.
 
  SELECT count(code) as nb, ANY_VALUE(code), allergens_tags
 
  SELECT count(code) as nb, ANY_VALUE(code), allergens_tags
Line 126: Line 126:  
  │ 55  │ 3700389705158  │ [fr:non-renseigne]                          │
 
  │ 55  │ 3700389705158  │ [fr:non-renseigne]                          │
 
  └─────┴─────────────────┴──────────────────────────────────────────────┘
 
  └─────┴─────────────────┴──────────────────────────────────────────────┘
 +
 +
=== Playing with dates ===
 +
 +
==== Last products created in 2023? ====
 +
<pre>
 +
SELECT code
 +
, strftime(epoch_ms(created_t * 1000), '%Y-%m-%dT%H:%M:%SZ') AS iso_date
 +
, creator
 +
, 'https://world.openfoodfacts.org/product/' || code as url
 +
FROM read_parquet('food.parquet')
 +
WHERE true
 +
    AND created_t < epoch (TIMESTAMP '2024-01-01 00:00:00')
 +
    AND created_t > epoch (TIMESTAMP '2023-12-31 00:00:00')
 +
ORDER BY iso_date desc
 +
LIMIT 7;
 +
┌───────────────┬──────────────────────┬──────────────────┬───────────────────────────────────────────────────────┐
 +
│    code      │      iso_date      │    creator      │                          url                          │
 +
├───────────────┼──────────────────────┼──────────────────┼───────────────────────────────────────────────────────┤
 +
│ 0699058466038 │ 2023-12-31T23:59:09Z │ smoothie-app    │ https://world.openfoodfacts.org/product/0699058466038 │
 +
│ 2030503844885 │ 2023-12-31T23:58:38Z │ kiliweb          │ https://world.openfoodfacts.org/product/2030503844885 │
 +
│ 3551720206854 │ 2023-12-31T23:56:51Z │ kiliweb          │ https://world.openfoodfacts.org/product/3551720206854 │
 +
│ 17705288      │ 2023-12-31T23:54:45Z │ smoothie-app    │ https://world.openfoodfacts.org/product/17705288      │
 +
│ 72734452      │ 2023-12-31T23:52:06Z │ insectproductadd │ https://world.openfoodfacts.org/product/72734452      │
 +
│ 3495040348439 │ 2023-12-31T23:51:38Z │ kiliweb          │ https://world.openfoodfacts.org/product/3495040348439 │
 +
│ 8801114140741 │ 2023-12-31T23:50:58Z │ foodless        │ https://world.openfoodfacts.org/product/8801114140741 │
 +
└───────────────┴──────────────────────┴──────────────────┴───────────────────────────────────────────────────────┘
 +
</pre>
    
==== How many images where uploaded in 2024? ====
 
==== How many images where uploaded in 2024? ====
Line 150: Line 177:  
  └─────────────┘
 
  └─────────────┘
   −
==== Count things ====
+
=== Count things ===
    
CASE allows to filter things when counting, eg. a given value in a list. Example:
 
CASE allows to filter things when counting, eg. a given value in a list. Example:
Line 199: Line 226:       −
==== Annex: parquet schema ====
+
=== Annex: parquet schema ===
 
{| class="wikitable"
 
{| class="wikitable"
 
! column_name !! column_type
 
! column_name !! column_type

Navigation menu