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 🦆

🏝️ 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

Top comments (5)

Collapse
 
adriens profile image
adriens
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

AWS Security LIVE!

Tune in for AWS Security LIVE!

Join AWS Security LIVE! for expert insights and actionable tips to protect your organization and keep security teams prepared.

Learn More

👋 Kindness is contagious

Engage with a sea of insights in this enlightening article, highly esteemed within the encouraging DEV Community. Programmers of every skill level are invited to participate and enrich our shared knowledge.

A simple "thank you" can uplift someone's spirits. Express your appreciation in the comments section!

On DEV, sharing knowledge smooths our journey and strengthens our community bonds. Found this useful? A brief thank you to the author can mean a lot.

Okay