DEV Community

Cover image for 🛡️ Data quality, SQL, duckdb and http_client on CI🦆
adriens
adriens

Posted on

🛡️ Data quality, SQL, duckdb and http_client on CI🦆

#devops #ci #database #productivity

💭 CI, duckdb et and data protection

To efficiently yet effortlessly manage data quality, we created a GitHub Action to install duckdb :

opt-nc

🦆 Effortless Data Quality w/duckdb on GitHub ♾️

adriens for opt-nc ・ Jul 25 '23

#duckdb #githubactions #data #automation

... but recently I had to face an another challenge : as part of our CI, I had the need to validate data... that were relying on web resources.

I needed to be sure that a GitHub Account was really existing (for example to avoid typos) as part of our CI.

In this very short article, I'll show how to use DuckDB with the http_client extension to verify GitHub handles stored in a table, for example to lint data as part of a CI pipeline thanks to GitHub Duckdb Action... and do the job with a very simple SQL script and CHECK constraints.

🍿 For impatients

🔖 Resources

🕹️ Do the job with SQL

First install extension:

INSTALL http_client FROM community; LOAD http_client;
Enter fullscreen mode Exit fullscreen mode

Then create a table with some example data:

create or replace table person_gh_member ( sam_accountname varchar primary key, gh_member varchar not null ); -- insert rows INSERT INTO person_gh_member (sam_accountname, gh_member) values ('adriens', 'adriens'), ('jdoe', 'johndoe'), ('asmith', 'annasmithRRRRR');
Enter fullscreen mode Exit fullscreen mode

Finally, run a query to check the status of each GitHub handle:

create or replace view v_person_gh_status as select sam_accountname, gh_member, 'https://github.com/' || gh_member as gh_url, cast(http_get(gh_url).status as integer) as http_gh_status from person_gh_member; --where gh_status <> 200; from v_person_gh_status;
Enter fullscreen mode Exit fullscreen mode

Now, to lint, we can add a check to see if any status is not 200:

from v_person_gh_status where http_gh_status <> 200;
Enter fullscreen mode Exit fullscreen mode 
create or replace table lint_gh_handle( gh_handle varchar primary key, gh_url varchar not null unique check (gh_url like 'https://github.com/%'), gh_status integer check (gh_status = 200) ); insert into lint_gh_handle( gh_handle, gh_url, gh_status) select gh_member, 'https://github.com/' || gh_member as gh_url, cast(http_get(gh_url).status as integer) as gh_status from person_gh_member;
Enter fullscreen mode Exit fullscreen mode

Top comments (4)

Collapse
 
parag_nandy_roy profile image
Parag Nandy Roy •

Very cool use of DuckDB..

Collapse
 
adriens profile image
adriens •

duckdb is such a productivity tool 🤩

Collapse
 
adriens profile image
adriens •

Collapse
 
adriens profile image
adriens •

🎁 Thank you for your extension (blog and video gift) #24

adriens avatar
adriens posted on

Hi guys,

we are maintaining ℹ️ Setup Duckdb Github Action... and we recently had the need to protect some data on our CI. I discovered your extension... which did a great job 😃

So I decided to document this use case, so here is below some feedback :

Thanks a lot for the great work, it will save us lot of efforts 🦥

View on GitHub