Cursus
Gegevens manipuleren in SQL
4 uur
336.7K
Wat is SQLite?
Als je bekend bent met relationele databasesystemen, is de kans groot dat je zwaargewichten als MySQL, SQL Server of PostgreSQL hebt gehoord. SQLite is echter een andere nuttige RDBMS die heel eenvoudig te installeren en te gebruiken is, en die veel onderscheidende kenmerken heeft ten opzichte van andere relationele databases. Deze kenmerken zijn onder meer:
- Geen servers nodig: Er zijn dus geen serverprocessen die moeten worden gestart, gestopt of geconfigureerd. Er is geen noodzaak voor databasebeheerders om instanties toe te wijzen of gebruikersrechten te beheren.
- Eenvoudige databasebestanden: Een SQLite-database is één gewoon schijfbestand dat in elke map kan worden opgeslagen. Dit maakt delen ook eenvoudig, omdat databasebestanden gemakkelijk naar een usb-stick kunnen worden gekopieerd of per e-mail verzonden.
- Manifest typing: De meeste SQL-database-engines vertrouwen op statisch typen. Dat wil zeggen: je kunt alleen waarden opslaan van hetzelfde gegevenstype dat is gekoppeld aan een bepaalde kolom. SQLite gebruikt echter manifest typing, waardoor je elk type en elke hoeveelheid data in elke kolom kunt opslaan, ongeacht het gedeclareerde gegevenstype van de kolom. Let op dat er enkele uitzonderingen op deze regel zijn. Een daarvan is integer primary key-kolommen, die alleen gehele getallen kunnen opslaan.
Zoals met de meeste dingen zijn er ook enkele beperkingen van SQLite ten opzichte van andere database-engines. Zo heeft SQLite geen optie voor Right of Full Outer Joins, en de serverloze aard kan ook een nadeel zijn voor beveiliging en bescherming tegen datamisbruik. Desondanks is het een zeer krachtig hulpmiddel dat in bepaalde situaties erg goed van pas komt. Zo heb ik in een recent socialmedia-analyseproject een SQLite-database gebruikt om een $edge \space list^{1}$ van miljoenen Twitter-gebruikers op een heel eenvoudige en efficiënte manier op te slaan.
Voor deze beginnerstutorial laat ik zien hoe je met een SQLite-database werkt. We behandelen het instellen van SQLite, het aanmaken van tabellen en bewerkingen, SQLite dot-commando’s en hoe je met eenvoudige SQL-queries de data in een SQLite-database manipuleert.
$^{1}$ Een edge list of adjacency list is een verzameling ongesorteerde lijsten die wordt gebruikt om een graaf te representeren in netwerkanalyse. Elke lijst beschrijft de set buren van een bepaalde node of vertex in de graaf.
Hoe installeer en stel je SQLite in
Het eerste dat we behandelen is hoe je SQLite op je computer krijgt. Dit is heel eenvoudig. Als je kijkt naar de SQLite with Python Tutorial door Sayak Paul, zie je dat er een GUI-tool is genaamd DB Browser for SQLite waarmee je eenvoudig kunt starten. Voor de afwisseling laat ik echter zien hoe je de commandlinetool gebruikt, die ik zelf soms ook liever gebruik.
Als je een Windows-apparaat hebt, ga je eerst naar deze site en download je sqlite-tools-win32-x86-3270200.zip. Nadat je de map hebt uitgepakt, zou je de volgende drie bestanden moeten zien:
Daarna is het starten van de SQLite-commandline zo simpel als klikken op de sqlite3-applicatie, waardoor de commandline als volgt verschijnt:
Je valt nu misschien op dat de standaard voor SQLite een vluchtige in-memorydatabase is. Het overgrote deel van de tijd wil je dat niet gebruiken. In plaats daarvan maken we een nieuwe database met het dot-commando .open gevolgd door de naam die je aan je nieuwe databasebestand wilt geven (zie hieronder):
Zo heb je een nieuwe database gemaakt. Let er ook op dat het essentieel is dat je .db achter je gewenste databasenaam zet om een databasebestand te creëren. We hebben de database Tweet_Data.db genoemd, omdat ik tweets zal gebruiken om de tabellen in de database te vullen.
Tabellen maken
Nu de database is aangemaakt, kunnen we doorgaan met het maken van onze tabel met tweets. Dit kan op twee manieren. Ten eerste kunnen we eenvoudig een .csv-bestand importeren en daar een tabel van maken. Dit doe je door SQLite in csv-modus te zetten met het .mode-dot-commando gevolgd door csv, en met het .import-dot-commando, dat een bestand neemt (in dit geval een csv in dezelfde map, maar het kan elke csv op je computer zijn als je het volledige pad typt) en een tabelnaam naar keuze. Je wilt mogelijk ook de kolomkoppen weergeven met het .headers-dot-commando gevolgd door on. (Zie hieronder)
Als je dit hebt gedaan, kun je het commando .tables gebruiken om alle beschikbare tabellen in een databasebestand weer te geven, evenals het commando .schema gevolgd door een tabelnaam om de structuur van de tabel weer te geven. Verder kun je een eenvoudige query uitvoeren, zoals SELECT $$ FROM Table_Name*, om te verifiëren dat de data er is.
Naast het aanmaken van tabellen door bestanden zoals csv’s te importeren, kun je ook tabellen maken met CREATE TABLE en vervolgens nieuwe rijen invoegen met INSERT INTO. In het volgende voorbeeld zie je hoe je een tabel from scratch kunt maken en twee nieuwe rijen invoegt.
Opmerking: In de SQLite-commandline, als je dezelfde query op de volgende regel wilt blijven schrijven zoals ik in het voorbeeld doe, druk je gewoon op Enter wanneer je een nieuwe regel wilt. De query wordt geëvalueerd nadat je op Enter drukt wanneer er een puntkomma aan het einde staat.
Exporteren uit SQLite
Als je op een gegeven moment besluit dat je de resultaten van een query wilt exporteren naar een extern bestand zoals een csv, kan dat ook met de kracht van SQLite. Voor een schone export naar een csv kun je een proces gebruiken dat lijkt op dat van importeren. Eerst gebruik je .header gevolgd door on en .mode gevolgd door csv. Vervolgens gebruik je het .output-dot-commando gevolgd door de gewenste csv-bestandsnaam en voer je tot slot de SQL-query uit die je wilt exporteren en sluit je SQLite af met het commando .exit. Laten we proberen de nieuwe tabel die we in de vorige stap hebben gemaakt te exporteren naar een csv.
Hier is de nieuw aangemaakte csv met ook de data van Sample_Tweets_2.
Tabellen bijwerken
Behalve tabellen maken en exporteren, kun je ook de SQL-statements UPDATE/SET en DROP TABLE gebruiken om rijen in een tabel bij te werken of om een tabel helemaal te verwijderen. In het volgende voorbeeld werken we de tweede tweet bij in de speelgoedtabel Sample_Tweets_2.
Nu nemen we afscheid van de speelgoedtabel Sample_Tweets_2 en verwijderen we die uit het databasebestand met DROP TABLE:
Een SQLite-database gebruiken voor Twitter-data
Je kent nu zo’n beetje de essentie van werken met een SQLite-database en het manipuleren van tabellen; het enige wat je nog hoeft te doen is nuttige informatie afleiden met SQL-queries. Voor nu laat ik een paar relatief eenvoudige queries zien die ik heb uitgevoerd tijdens mijn socialmedia-analyseproject op Twitter. Je kunt het zien als een mini-casestudy om een realistische toepassing van een SQLite-database te illustreren.
Laten we beginnen met iets eenvoudigs: alle unieke screennamen van Twitter-gebruikers in de tabel Sample_Tweets vinden. In het project waarvoor ik deze tweets gebruikte, was het essentieel om alle unieke gebruikers te vinden van wie ik tweets had verzameld, omdat ik hun volgers moest ophalen om de edge list te maken die ik aan het begin van deze tutorial heb genoemd.
26.860 unieke gebruikers in de database. Dat is een uitstekende steekproef, aangezien de onderwerpen waarnaar ik luisterde websitelokalisatie en vertaling waren. Laten we kijken naar de top 20 gebruikers met de meeste tweets in de steekproef met een iets complexere SQL-query (retweets zijn uitgesloten):
Kijk eens aan, het grootste aantal tweets is van een bot! Het bleek echter dat het laatste lid uit die lijst, UweMuegge, volgens de diverse netwerkstatistieken die ik voor het project berekende, best een influencer is. Hij was echter zeker niet de belangrijkste.
Tot slot bekijken we enkele tweets die UweMuegge schreef met een andere SQL-query:
Twee hoofdonderwerpen zo te zien: vacatures voor vertalers en vertaalconferenties. Het is logisch waarom mensen uit het vak hem op Twitter zouden volgen.
Wat ook opvalt in de bovenstaande screenshot, is dat je een van de beperkingen van de SQLite-commandlineinterface tegenkomt. Als je grote tekstvelden hebt, worden die niet netjes weergegeven en zijn ze moeilijk te lezen. Daarom is het in dit geval een goed idee om de DB Browser for SQLite te gebruiken.
Samenvatting
In deze tutorial heb je geleerd hoe je SQLite-databases maakt en tabellen manipuleert met dot-commando’s en SQL-statements. Daarnaast heb je een kleine real-world casestudy gezien waarin ik een SQLite-database gebruikte om data van Twitter te verwerken. Dat is een hoop, dus gefeliciteerd!
Het is het vermelden waard dat SQLite-databases het meest nuttig zijn in combinatie met R en Python. Je kunt SQLite-databases manipuleren via Python met de sqlite3-module (als je hier meer over wilt leren, zie de tutorial SQLite with Python) of met R via RSQLite. Sterker nog, de edge list van de volgers van Twitter-gebruikers die ik eerder heb genoemd, is opgeslagen in een SQLite-database die ik met RSQLIte heb gemaakt door het volgersnetwerk van elke gebruiker aan een tabel toe te voegen. In deze fase van het project was het gebruik van SQLite cruciaal, omdat dingen als stroomstoringen of automatische Windows-updates je computer geforceerd kunnen afsluiten en je opnieuw laten beginnen met het verzamelen van volgersnetwerken. Dit kan ongeveer 4 weken duren vanwege de rate limits van de Twitter-API, dus opnieuw vanaf 0 beginnen is zeer ongewenst. Als je de volgersnetwerken echter gaandeweg in een SQLite-database opslaat, kun je, zelfs als je computer geforceerd wordt afgesloten, weer verdergaan vanaf de gebruiker na de laatste die je in de database hebt, wat veel tijd bespaart.
Dat is slechts één voorbeeld van hoe SQLite-databases een fantastisch hulpmiddel kunnen zijn in het arsenaal van een data scientist. Ik moedig je aan om verder te onderzoeken naar SQLite-databases en SQL, buiten wat in deze tutorial is behandeld. Blijf leren; de sky is the limit!
Als je meer wilt leren over SQL, volg dan de DataCamp-cursus Introduction to Relational Databases in SQL.
Onderwerpen
