DEV Community

Cover image for 🏖️ Noumea's beach water quality data as git revisions, in DuckDb 🦆
adriens
adriens

Posted on

🏖️ Noumea's beach water quality data as git revisions, in DuckDb 🦆

#python #datascience #opendata #tutorial

🏝️ About

Ever wanted to get your favorite beach's water quality like this :

Image description

With this article you'll learn how I could:

  1. Get (scrap) the data
  2. Load data into a database
  3. Backup & export (csv & Apache parquet)
  4. Commit data to GitHub
  5. Query from any terminal from duckdb

🧫 About water quality

Comment sont surveillées les eaux de baignade à Nouméa? - Nouvelle-Calédonie la 1ère

Les fortes pluies peuvent altérer la qualité des eaux de baignade. Après les récents épisodes dépressionnaires, comment savoir si ces dernières sont saines. À Nouméa, des dispositifs permettent de surveiller leur qualité tout au long de l'année.

favicon la1ere.francetvinfo.fr

🍿 Demo

🔖 Source code

GitHub logo adriens / odata-eaux-baignade-noumea

Experience Open Data de la qualité des eaux de baignade à Nouméa

❔ About

See https://www.kaggle.com/code/adriensales/la-qualit-des-eaux-de-baignade-noum-a

⚡ For impatients

Just run the following script in any shell and enjoy:

#!/bin/sh
# Diplay latest flags
sh <(curl https://tea.xyz) +duckdb.org \
duckdb << EOF
INSTALL httpfs;
LOAD httpfs;
select plage,
    flag_color,
    case 
        when (flag_color = 'BLUE')      THEN '🟦'
        when (flag_color = 'YELLOW')    THEN '🟨'
        when (flag_color = 'RED')       THEN '🟥'
    end as flag_color
from read_csv_auto('https://bit.ly/3ZCJ1X5') as latest;
EOF
Enter fullscreen mode Exit fullscreen mode

🦆 DuckDb hacks

First, install duckdb (brew install duckdb) or install_duckdb.sh

INSTALL httpfs;
LOAD httpfs;
.prompt "🦆 🏖️  > "
select * 
from 'https://raw.githubusercontent.com/adriens/odata-eaux-baignade-noumea/main/data/latest.csv';
Enter fullscreen mode Exit fullscreen mode 
INSTALL httpfs;
LOAD httpfs;
.prompt "🦆 🏖️  > "
select *
from 'https://raw.githubusercontent.com/adriens/odata-eaux-baignade-noumea/main/data/historic.csv';
Enter fullscreen mode Exit fullscreen mode

You can also use (nicer) short urls :

duckdb << EOF
-- historic
INSTALL httpfs;
LOAD httpfs;
select *
from
read_csv_auto('https://bit.ly/3mAUIPr') as historic;
EOF
Enter fullscreen mode Exit fullscreen mode 
duckdb << EOF
-- historic
INSTALL httpfs;
LOAD httpfs;
select *
from
…
Enter fullscreen mode Exit fullscreen mode
View on GitHub

Top comments (5)

Collapse
 
adriens profile image
adriens •

Image description

Collapse
 
adriens profile image
adriens •

Adrien Sales on LinkedIn: ✂️ Tea <3 DuckDb

⏱️ Challenge accepted : within less than 14 seconds on any fresh &amp; rootless session on Killercoda🏎️ 1️⃣ Install tea.xyz 2️⃣ Install DuckDB Labs 3️⃣ Get a…

favicon linkedin.com
Collapse
 
adriens profile image
adriens •
Collapse
 
adriens profile image
adriens •
Collapse
 
adriens profile image
adriens •

Read next

grantdotdev profile image

Advent of Code 2024 - Day 15: Warehouse Woes

Grant Riordan -

biswajitfsd profile image

Design Patterns: Your Secret Weapon in Software Engineering

Biswajit Patra -

rcmcintosh profile image

My Python Language Solution to Task 1 from The Weekly Challenge 299

Robert McIntosh -

msnmongare profile image

Essential Use Cases to Master API Development and Integration

Sospeter Mong'are -