#sql

Qu’est-ce qu’une CTE ? | LearnSQL.fr
▻https://learnsql.fr/blog/qu-est-ce-qu-une-cte

Clause WITH : utiliser les CTE en SQL

Une expression de table commune, ou CTE, est un ensemble de résultats temporaires nommés, créé à partir d’une simple instruction SQL, qui peut être utilisé dans les instructions suivantes SELECT, DELETE, INSERT ou UPDATE.

– introduction à la clause WITH : ▻https://learnsql.fr/blog/qu-est-ce-que-la-clause-with-en-sql
– 5 exemples d’utilisation de WITH : ▻https://learnsql.fr/blog/5-exemples-pratiques-de-la-clause-with-de-sql
– les CTE récursifs : ▻https://learnsql.fr/blog/qu-est-ce-qu-un-cte-recursif-en-sql

Ca semble pratique pour les requêtes complexes généralement faites avec des sous-requêtes : une clause qui pourrait entrer dans l’API SQL de SPIP ?

#WITH #SQL #sous-requête #SPIP

Je découvre #duckdb
▻https://duckdb.org/docs/archive/0.9.2

Quel merveilleux outil pour travailler avec open data.

Merci @simplicissimus pour ce lien vers la présentation à Toulouse.
▻https://seenthis.net/messages/1038730

Côté pratique c’est mpressionnant

Data Import & Export

CSV Import
CSV Export
Parquet Import
Parquet Export
Query Parquet
HTTP Parquet Import
S3 Parquet Import
S3 Parquet Export
JSON Import
JSON Export
Excel Import
Excel Export
SQLite Import
PostgreSQL Import

Tout sauf du #PHP

There are various client APIs for DuckDB:

C
C++
Java
Julia
Node.js
Python
R
Rust
WebAssembly/Wasm
ADBC API
ODBC API

Additionally, there is a standalone Command Line Interface (CLI) client.

There are also contributed third-party DuckDB wrappers for:

C# by Giorgi
Common Lisp by ak-coram
Crystal by amauryt
Go by marcboeker
Ruby by suketa
Zig by karlseguin

Les arguments pour
▻https://duckdb.org/why_duckdb

... what goals DuckDB has and why and how we try to achieve those goals through technical means. To start with, DuckDB is a relational (table-oriented) DBMS that supports the Structured Query Language (SQL)
...
no external dependencies ...
no DBMS server software to install ...
support for complex queries in SQL with a large function library ...

DuckDB is deeply integrated into Python and R for efficient interactive data analysis. DuckDB provides APIs for Java, C, C++, Julia, Swift, and others.
...
DuckDB is released under the very permissive MIT License
etc.

don’t

High-volume transactional use cases (e.g., tracking orders in a webshop)
Large client/server installations for centralized enterprise data warehousing
Writing to a single database from multiple concurrent processes
Multiple concurrent processes reading from a single writable database

mais

PHP example to integrate DuckDB using PHP-FFI
▻https://github.com/thbley/php-duckdb-integration

Currently there is no PHP extension available for using DuckDB, so I created a small library using PHP-FFI.

DuckDB is an embeddable SQL OLAP database management system. It does not require external servers. Databases are stored in single files (similar to SQLite). Compared to SQLite, DuckDB is much faster. E.g. I imported 16M rows from a CSV file in 5s on my notebook (i5-8250U).

DuckDB can import CSV files with automatic format detection and automatic table creation using:

CREATE TABLE test1 AS SELECT FROM read_csv_auto(’test1.csv’);
CREATE TABLE test2 AS SELECT FROM read_csv_auto(’test2.csv.gz’);

Usage:

php -dffi.enable=1 test.php

or:

docker build -t php-ffi .
docker run -it —rm -v $(pwd):/code php-ffi php /code/test.php

Requirements:

PHP 7.4+ with FFI extension enabled

Question / défi

Directly running DuckDB queries on data stored in SQLite files | Hacker News
▻https://news.ycombinator.com/item?id=30801575

#SQL #open_data

kd2org/karadav: Lightweight NextCloud compatible WebDAV server
▻https://github.com/kd2org/karadav

https://github.com/kd2org/karadav/blob/main/doc/scr_index.jpg?raw=true

This is a simple and lightweight #WebDAV server, allowing to easily set up a #file_sharing server compatible with WebDAV and #NextCloud clients. It has no dependencies and good performance.

It is written in #PHP (8+). The only dependency is #SQLite3 for the database.

Its original purpose was to serve as a demo and test for the KD2 WebDAV library, which we developed for Paheko, our non-profit management solution, but it can also be used as a simple but powerful file sharing server.

If you are looking for an even lighter WebDAV server, try also our other server, PicoDAV ▻https://github.com/kd2org/picodav. It isn’t compatible with NextCloud/ownCloud clients, but it is a single file that you can drop anywhere in your document root, nothing to install!

loupe-php/loupe: A #fulltext #search engine with #tokenization, #stemming, #typo_tolerance, #filters and #geo support based on only #PHP and #SQLite.
▻https://github.com/loupe-php/loupe

An SQLite based, PHP-only fulltext search engine.

Loupe…
–…only requires PHP and SQLite, you don’t need anything else - no containers, no nothing
–…is typo-tolerant (based on the State Set Index Algorithm and Levenshtein)
–…supports phrase search using " quotation marks
–…supports filtering (and ordering) on any attribute with any SQL-inspired filter statement
–…supports filtering (and ordering) on Geo distance
–…orders relevance based on a typical TF-IDF Cosine similarity algorithm
…auto-detects languages
–…supports stemming
–…is very easy to use
–…is all-in-all just the easiest way to replace your good old SQL LIKE %...% queries with a way better search experience but without all the hassle of an additional service to manage. SQLite is everywhere and all it needs is your filesystem.

I Visualized My One-Year-Old’s Relentless Daycare Bugs and It Calmed Me Down
▻https://nightingaledvs.com/daycare-virus-visualization

When viruses started coming home from daycare and making his family sick, this dad made a #Data_Visualization to keep things in perspective.

#Charts #Personal_Data #personal_dataviz #SQL

Pour ceux qui pourraient encore en douter, le #SQL et les bases de données relationnelles (#RDBMS/#SGBDR) restent au cœur des métiers de l’information et de l’#informatique.

▻https://www.dsfc.net/informatique/emploi/importance-bases-donnees-relationnelles-portefeuille-competences

DuckDB redonne une belle jeunesse au langage SQL / Éric Mauvière / Observable
▻https://observablehq.com/@ericmauviere/duckdb-redonne-nouvelle-vie-sql

De vraies bases de données #SQL dans votre navigateur, compatibles avec les nouveaux formats Arrow et Parquet ? 📣 C’est désormais possible avec DuckDB, et de superbe manière !

Démontrons-le avec un exemple concret : l’Insee met à disposition le fichier domicilié des prénoms attribués aux enfants nés en France dans un format CSV (zippé).

Cette table est riche, elle comprend 3 800 000 enregistrements dans sa version publiée en juin 2022, couvrant les années de naissance de 1900 à 2021.

https://pixelfed.zoo-logique.org/storage/m/_v2/493806419159965724/a48ad3dc0-89cf68/HcXIHzuqI0FQ/AfKIK0XRTeFwDI2zxAbZw4WZTrFCN6LhLoyB7bCC.png

démo interactive dans un notebook #Observable

ce qui ne me rajeunit pas ! j’avais réalisé ce genre de choses avec des requêtes MSQuery (du SQL également) sous Excel, il y a looooongtemps …

dolthub/dolt: Dolt – It’s Git for Data
▻https://github.com/dolthub/dolt

Dolt is a #SQL database that you can #fork, clone, branch, merge, push and pull just like a git repository. Connect to Dolt just like any MySQL database to run queries or update the data using SQL commands. Use the command line interface to import CSV files, commit your changes, push them to a remote, or merge your teammate’s changes.

All the commands you know for #Git work exactly the same for Dolt. Git versions files, Dolt versions tables. It’s like Git and MySQL had a baby.

Hosting SQLite databases on Github Pages (or any static file hoster) - Apr 17, 2021

I was writing a tiny website to display statistics of how much sponsored content a Youtube creator has over time when I noticed that I often write a small tool as a website that queries some data from a database and then displays it in a graph, a table, or similar. But if you want to use a database, you either need to write a backend (which you then need to host and maintain forever) or download the whole dataset into the browser (which is not so great when the dataset is more than 10MB).

In the past when I’ve used a backend server for these small side projects at some point some external API goes down or a key expires or I forget about the backend and stop paying for whatever VPS it was on. Then when I revisit it years later, I’m annoyed that it’s gone and curse myself for relying on an external service - or on myself caring over a longer period of time.

Hosting a static website is much easier than a “real” server - there’s many free and reliable options (like GitHub, GitLab Pages, Netlify, etc), and it scales to basically infinity without any effort.

So I wrote a tool to be able to use a real SQL database in a statically hosted website. Here’s a demo using the World Development Indicators dataset - a dataset with 6 tables and over 8 million rows (670 MiByte total).

...

– article ▻https://github.com/phiresky/sql.js-httpvfs
– lib ▻https://phiresky.github.io/blog/2021/hosting-sqlite-databases-on-github-pages

#git #database #sql #api

Grist, a Hacker Friendly Spreadsheet | Grist | getgrist.com
▻https://www.getgrist.com

Grist is a self-contained format, based on SQLite. Grist tables are literally #SQLite tables, which opens up all sorts of possibilities.

The #SQL Murder Mystery
▻http://mystery.knightlab.com

http://mystery.knightlab.com/174092-clue-illustration.png

A crime has taken place and the detective needs your help. The detective gave you the crime scene report, but you somehow lost it. You vaguely remember that the crime was a ​murder​ that occurred sometime on ​Jan.15, 2018​ and that it took place in ​SQL City​. Start by retrieving the corresponding crime scene report from the police department’s database.

#jeu #tutorial

Améliorez votre #SQL : utilisez des index filtrés
▻https://makina-corpus.com/blog/metier/2019/ameliorez-votre-sql-utilisez-des-index-filtres

L’indexation d’une base de données est un vaste sujet, dans cet article nous examinerons une possibilité offerte par #PostgreSQL dans les index et le filtrage de l’index pour qu’il ne s’applique pas à toute la table.

#News_Item

Améliorez votre #SQL : utilisez des invariants dans les conditions
▻https://makina-corpus.com/blog/metier/2019/ameliorez-votre-sql-utilisez-des-invariants-dans-les-conditions

Il suffit parfois de repenser la façon d’exprimer une condition de filtrage dans une requête SQL pour observer des gains de performances impressionnants. Et la meilleure façon de s’en souvenir c’est sans doute de le voir fonctionner en direct.

#PostgreSQL #News_Item

John’s Observation Loft : Adding or removing individual SQL modes in MySQL’s sql_mode variable
▻http://johnemb.blogspot.com/2014/09/adding-or-removing-individual-sql-modes.html

La syntaxe des requêtes MySQL pour ajouter/modifier/supprimer à la volée des valeurs aux sql_mode (défini dans la configuration du serveur)

#sql_mode #MySQL #configuration

Not Only #SQL – Framabook
▻https://framabook.org/not-only-sql

https://framabook.org/wordpress/wp-content/uploads/2019/06/notonlysql-cartouche.png

Halte aux dogmes et aux méthodes standard ! sortez des sentiers battus et préparez-vous à faire vos propres choix éclairés. Vincent Lozano et Étienne Georges vous proposent un parcours riche et complet, tablant sur 50 années d’expérience professionnelle cumulées, de la modélisation d’un univers (Modèle Conceptuel des Données) à sa manipulation en langage SQL, en passant par le codage des informations et la conception d’une base de données (Modèle Relationnel).

De nombreux exemples largement illustrés et finement décortiqués vous permettront d’appréhender en profondeur les problématiques liées aux Systèmes de Gestion de Bases de Données Relationnelles et vous aider à mettre en œuvre ces systèmes de façon optimale. Cet ouvrage didactique, plaisamment agrémenté de petites pointes d’humour, se veut accessible à toute personne éveillée à l’informatique : étudiant curieux ou professionnel averti, amateur d’Excel ou disciple du logiciel libre, profane musical ou fan de Frank Zappa…

#documentation

Why We’re Going to Re-Architect #blockchain!
▻https://hackernoon.com/why-were-going-to-re-architect-blockchain-b617fac5ba4b?source=rss----3a8

https://cdn-images-1.medium.com/max/1024/1*hb3aXGbsK9rIIPvkKPkYTg.jpeg

a whole new world of possibility!•Blockchain for #database storage. ?Instead of storing data in native database forms we may use blockchain to store data which as a result becomes highly secure and decentralized(essence of blockchain).? In general, traditional user accounts can be substituted by public-private keys and addresses in blockchains. Traditional databases are subject to strong access control, almost all data is restricted to authenticated accounts. Moreover, account creation is also of a centralized model in traditional databases, by which a database administrator grants the user an account for access. With blockchains, conversely, key pairs are generated freely by anyone, without the need for centralized administration.? For applications that require some form of central (...)

#sql #decentralization #cryptocurrency

Better to Give and to Receive: Alibaba’s #open-source Contributions to Flink
▻https://hackernoon.com/better-to-give-and-to-receive-alibabas-open-source-contributions-to-flin

https://cdn-images-1.medium.com/max/1024/1*yZb3RkjuMtveB0dcmjCgbg.jpeg

Between its #sql and Runtime layers, Alibaba has helped optimize Apache Flink for large-scale production environments like its ownThis article is part of Alibaba’s Flink series.As an open-source framework for big data #computing, Apache Flink has undergone extensive optimization to meet a range of users’ demands for enhancement. For Alibaba Group, where the framework is deployed in a large-scale production environment, the need for these changes has motivated its real-time computing team to contribute many of Flink’s most valuable optimizations, benefiting the Flink community and Alibaba alike.In this article, we look at Alibaba’s contributions in two key aspects of the framework’s architecture, tracing developments including Flink’s Query Processor, BinaryRow, and AsyncOperator (...)

#apache-flink #big-data

#postgresql’s Exciting features, you should know
▻https://hackernoon.com/postgresqls-exciting-features-you-should-know-a516a441b8c4?source=rss---

https://cdn-images-1.medium.com/max/1000/1*PIOuPjsDGmgRw8jzTFnsJg.jpeg

PostgreSQL is a powerful,object-relational database system that extends the #sql language combined with many features that safely store and scale the most complicated data workloads.PostgreSQL comes with many features aimed to help developers build applications, administrators to protect data integrity and build fault-tolerant environments, and manage data no matter how big or small the dataset.Here, I pick some great PostgreSQL features you may not have taken a look at but really should, since they can help you get code into production faster, make easier and generally get things done with less code and less effort.InheritanceTable #inheritance allows extracting a common set of columns into a parent table with children defining additional fields.CREATE TABLE invoices ( invoice_number (...)

#postgresql-feature #sharding

Learn Data Engineering : My Favorite Free Resources
▻https://hackernoon.com/learn-data-engineering-my-favorite-free-resources-52a29ab999b?source=rss

https://cdn-images-1.medium.com/max/650/1*iVO9gFwzGvkcyl-p7fb-CQ.png

Learn Data Engineering: My Favorite Free Resources For Data EngineersBy Benjamin Rogojan originally posted hereThere are a lot of lists of resources for data science and machine learning. We wanted to create a list of resources for those of you who might not be interested in data science but instead would like to pursue data engineering.Let’s first lay out the basic skills required to be a good data engineer.A data engineer specializes in several specific technical aspects. Data engineers have solid automation/programming skills, ETL design, understand systems, data modeling, #sql, and usually some other more niche skills. For instance, some data engineers start to dabble with R and data analytics. This will also be driven by their specific role. As a data engineer, I have been asked to (...)

#data-engineer-resources #data-engineer #data-engineer-skills #data-science

From zero to graph hero, in 5 days and 5 steps
▻https://hackernoon.com/from-zero-to-graph-hero-in-5-days-and-5-steps-34a1853b75c8?source=rss---

https://cdn-images-1.medium.com/max/468/1*XUbv1vE6qKHhUJDuyZbluA.jpeg

Sharing my playbook for graph #database projects. Will be happy to answer any questions or offer advice.Ask Siri who is the quarterback of the New York Jets and you get a list of the current quarterbacks right away. Ask Siri who is Taylor Swift’s boyfriend, and it will know.Ask who are the members of the Irish band, The Chieftains, and Siri doesn’t know. Even if you ask about who are the members of the Irish Band U2, it doesn’t know. It can provide you information about Bono, but doesn’t have an idea of the members of U2.However, Google Assistant know to answer the questions above. Can you guess what happened?Despite an almost 50% growth YoY of graphs, the tech sees a small fraction of the database market share. Yet it’s power, flexibility and speed of development reamins amongst one of the (...)

#business-intelligence #innovation #business-development #sql

#sql Best Practices — Designing An ETL Video
▻https://hackernoon.com/sql-best-practices-designing-an-etl-video-1933665f9861?source=rss----3a8

https://cdn-images-1.medium.com/max/638/1*ycQhTrWDRHS8LuwT5BjbPA.jpeg

SQL Best Practices — Designing An ETL VideoData engineering has many facets. One of the most common projects a data engineer takes on is developing an ETL pipeline from an operational DB to a data warehouse. Our team wanted to cover the overarching design of an ETL.What are the typical principal components, stages, considerations, etc?We started this by first writing Creating An ETL Part 1(more to come) and we and now have worked on a video that is below that walks through the process. We wanted to discuss why each stage is important and what occurs when data goes from raw to stage, why do we need a raw database and so on.Data engineering is a complex discipline that partners automation, #programming, system design, databases, and #analytics in order to ensure that analysts, data scientists (...)

#big-data #learning

All about #sql JOINs
▻https://hackernoon.com/all-about-sql-joins-ced19ecc4c1d?source=rss----3a8144eabfe3---4

https://cdn-images-1.medium.com/max/1000/1*z99cGlmOQN9ebbeE9ALsdw.jpeg

Joining tables is the first big learning curve after getting your head around SQL basics.More often than not you won’t find everything you need in one table so will need to learn which #join to use when.If you’re familiar with the VLOOKUP function in Excel, you’ll be just fine.T-SQL supports cross, inner and outer JOIN clauses, and UNION operators to combine datasets. While there are plenty of guides to JOINs that use venn diagrams to get the point across, I prefer something more visual.INNER JOINUse an INNER join, or just a JOIN, when you want to find the match between two tables. You need to have a key on both tables that you join ON, and that’s where the match happens. Any results where there is not a match are discarded.select o.order_item, i.inventory_item from orders o inner join (...)

#sql-joins #sql-server #data-science

#data Mastery: #sql — Left & Right Outer Join
▻https://hackernoon.com/data-mastery-sql-left-right-outer-join-e8e2c2ee02ca?source=rss----3a8144

https://cdn-images-1.medium.com/max/353/1*menZERnHfutgWK4GHsdYWQ.png

Data Mastery: SQL — Left & Right Outer JoinThere are so many exciting projects out there in the Data World. Artificial Intelligence, Machine Learning, Neural Nets, Blockchain, and more are sweeping the technology industry. In order to get to the cutting-edge stuff, first and foremost, data needs to be stored, evaluated, and tested. The best place to do that is SQL (or a library that operates with SQL-like commands, see my article on Python’s Pandas library).This series Data Mastery: SQL, will teach you the essential subjects. These are not exhaustive tutorials. Instead they are focused preparation guides — with brevity and efficiency in mind. It is meant for:Software Engineers who want to analyze their creation’s dataProduct Managers, Marketers, and others who want to be (...)

#interview-questions #data-science #programming

#clickhouse, an #analytics #database for the 21st century
▻https://hackernoon.com/clickhouse-an-analytics-database-for-the-21st-century-82d3828f79cc?sourc

Clickhouse is a fairly new column store database. It’s developed by the guys over at Yandex (the Google of Russia), made to scale horizontally reasonably well and run high speed aggregate queries on hundreds of billions of rows of data.It uses it’s own #sql dialect and it matches pl/pgSQL in terms of expressivity and simplicity. It even includes higher order functions for working with nested object within rows, such as arrayMap and arrayFilter.What is Clickhouse used for ?In short, Clickhouse is used to run fast analytics on very large amount of data. It’s rather bad as a transactional database, but it can run aggregate queries on billions of rows in sub second times.It fills the ever increasing niche that technologies like Hadoop, Spark, Druid, Big Query, Redshift, Athena and MonetDb aim (...)

#analytics-database

Introducing the First Natural Language to #sql #api
▻https://hackernoon.com/introducing-the-first-natural-language-to-sql-api-8429229301ce?source=rs

https://cdn-images-1.medium.com/max/1001/1*O0WiQc9hTM8vvL4jLc4FoQ.png

Talk to your data!You can view the API documentation here: ▻http://docs.dhignite.com/What if, instead of running complex SQL scripts, you could simply ask your database a question? What were my sales yesterday? What were my top selling products in June?This natural search capability has become more common over the past year as companies such as ThoughtSpot, Salesforce, and Tableau all develop similar technologies. This trend of “data democratization” is forcing us to leverage existing technology in new ways.The idea that data should be accessible to the average end user, not confined to data analysts or forced to go through a complex queuing system where time and convenience often rule the day, is our new expectation.Data should be accessible beyond the dashboards and beyond analysts. That (...)

#machine-learning #nlp #sql-api