Cursus
Gegevens manipuleren in SQL
4 uur
336.7K
Als data scientist krijg je vaak te maken met bergen data. Werken met data (die in enorme hoeveelheden aanwezig is) is allesbehalve eenvoudig. Om er zo efficiënt mogelijk mee om te gaan, heb je een duidelijk begrip nodig van hoe de data fysiek is georganiseerd, zodat je die met handige technieken kunt verwerken.
SQL is een onmisbare vaardigheid voor elke moderne software engineer, omdat de meeste software afhankelijk is van een of andere vorm van data en goed integreert met een RDBMS (Relational Database Management System). Of het nu een webapplicatie, een API of een interne applicatie is, een RDBMS is er altijd. En SQL is de taal om een RDBMS te bevragen.
Als data scientist is het cruciaal om SQL en de bijbehorende technieken te kennen. Om een RDBMS te kunnen bevragen en antwoord te krijgen op specifieke vragen die je hebt over de data waarmee je werkt, is SQL de minimale vereiste.
In zijn nieuwste video met DataCamp liet David Robinson (Chief Data Scientist @ DataCamp) zien hoe hij SQL gebruikt in een Data Science-probleem. Kijk ‘m zeker even. Zijn workflow is erg interessant.
Vandaag leer je over een techniek genaamd indexering die zich primair bezighoudt met de organisatie van data binnen een database, en je gaat er een aantal van implementeren met SQL. Dit geeft je een overzicht van hoe indexering kan worden gebruikt om informatie in een database op te slaan en hoe dat kan leiden tot snellere uitvoeringstijden.
N.B.: Voor je aan deze tutorial begint, is het sterk aan te raden om de basis van SQL te leren als je daar niet mee bekend bent. DataCamp’s cursus Intro to SQL for Data Science is een uitstekende bron als je je SQL-basis wilt opfrissen.
Een korte noot over de organisatie van records in een bestand
Voordat je iets over indexering gaat bestuderen, is het essentieel dat je begrijpt hoe data fysiek in bestanden is georganiseerd. Uiteraard ga je niet alle details induiken, maar een goed overzicht van de organisatie helpt je om indexering heel helder te begrijpen. Laten we beginnen.
De organisatie van records/data gaat in het algemeen over hoe records zijn opgeslagen en kan grofweg in twee typen worden verdeeld:
-
Geordende organisatie: Alle records in een bestand zijn geordend volgens een zoeksleutel. Binaire zoekopdrachten worden meestal gebruikt om te zoeken naar een waarde in het geordende bestand. Dit maakt de zoekactie erg efficiënt, omdat de tijdscomplexiteit van de zoekopdracht binnen de logaritmische tijd blijft. Maar invoegen in het bestand wordt een kostbare operatie, omdat je mogelijk het hele bestand moet herorganiseren om het nieuwe item te plaatsen.
-
Ongeordende organisatie: In dit type worden records in het bestand geplaatst waar ruimte is, meestal aan het einde van het bestand. Omdat lineair zoeken wordt gebruikt, is de zoekactie minder efficiënt dan bij de vorige variant, maar invoegen is geen dure operatie.
Maar stel dat je een geordende organisatie van bestanden hebt en de blokgroottes van de bestanden en de grootte van elk record in de bestanden heel groot zijn? Laten we dit verkennen in de volgende sectie.
De noodzaak van indexering
Je begint deze sectie met het leren over de motivatie om indexering te gebruiken om bestanden/informatie efficiënt op te slaan en hoe dit de andere bewerkingen met deze bestanden/informatie verbetert.
Stel dat je een tabel (relatie) hebt met meerdere records in een database. De records zijn verder opgedeeld in 1000 blokken. Schematisch ziet de organisatie er als volgt uit:
Uit de bovenstaande organisatie blijkt dat:
- De ordening is toegepast op de eerste kolom van records (ga ervan uit dat dit een relationeel databasesysteem is waarin data in tabelvorm is georganiseerd)
- Het aantal blokken waarin de totale data is verdeeld: 1000 blokken.
Onthoud dat elk record ook andere kolommen bevat, maar voor deze organisatie is de ordening toegepast op de eerste kolom en zijn de blokken dienovereenkomstig verdeeld. Als je nu een binaire zoekopdracht uitvoert om iets in deze organisatie of records te zoeken, is de totale tijd die dit kost: $\log_{2}1000$, wat gelijk is aan 10 tijdseenheden. Kan deze zoektijd worden verbeterd?
Natuurlijk.
Zie het als een boek lezen zonder indexpagina. Je slaat een willekeurige pagina open (of in het geval van binaire zoekopdracht de middelste pagina van het boek) en bladert naar links en rechts om bij de gewenste pagina te komen. Dat kost zeker tijd. Toch?
Maar als het boek een indexpagina had, zou dit de zoekactie zeker efficiënter maken. Je zou naar de pagina kunnen gaan door gewoon naar de index te kijken. Je kunt indexering op dezelfde manier toepassen op de bovenstaande situatie.
Hier is een samenvatting van hoe je hier indexering kunt toepassen:
- Onderhoud een blokpointer voor elk blok samen met de geordende waarden die in de eerdere organisatie zijn gebruikt.
Dit resulteert in een kleiner aantal blokken dat wordt gebruikt om de databestanden op te slaan. Om nu naar een specifiek record te zoeken, hoef je alleen door deze nieuwe organisatie met een kleiner aantal blokken te zoeken en uiteindelijk krijg je je record (als het al aanwezig is) in veel minder tijd. Laten we dit visualiseren. Als we aannemen dat het aantal nieuw geïndexeerde records heeft geresulteerd in 8 blokken, krijg je een organisatie zoals de volgende:
De zoektijd wordt drastisch verminderd door het kleinere aantal blokken. Stel, je wilt het record 90 zoeken. In dit nieuw geïndexeerde schema moet je eerst de bijbehorende blokpointer lokaliseren, in dit geval 3, en met deze informatie vind je de oorspronkelijke datarecords in één keer.
Dus de zoektijd is in dit geval $\log_{2}8 + 1$ = 4 tijdseenheden, wat aanzienlijk minder is dan eerder.
Het bovenstaande voorbeeld introduceert de noodzaak van indexering. Hier zijn enkele punten over indexering om te overwegen:
- Ordening op de oorspronkelijke datarecords kan worden gedaan met slechts één veld. Vervolgens kunnen de veldwaarden dienovereenkomstig worden geïndexeerd. Alleen als je probeert te zoeken met dat veld, krijg je een verbeterde zoektijd. (Extreem belangrijk)
- De indices zijn ook geordend.
- Het indexrecord bevat twee velden (structuur van het indexbestand):
- De sleutel van het oorspronkelijke bestand
- Pointer naar het blok waar de sleutel beschikbaar is in de oorspronkelijke datarecords
- Binaire zoekopdracht wordt gebruikt om door de indices te zoeken.
- Om toegang te krijgen tot een record met behulp van de geïndexeerde entries is het gemiddelde aantal benodigde bloktoegangen:
$\log_{2}B_i + 1$ , waarbij $B_i$ het aantal blokken in de geïndexeerde records is - Een index kan worden gemaakt op elk veld van de relatie (primaire sleutel, kandidaatsleutels, niet-sleutels).
Afhankelijk van de ordening van de oorspronkelijke datarecords en hoeveel records je gaat bijhouden in het indexbestand, kunnen er veel indexeringsschema’s zijn. In de volgende sectie bestudeer je de meest populaire.
Verschillende indexeringsstrategieën:
- Dense indexing: Als er voor elke zoek sleutelwaarde een indexentry wordt gemaakt, dan is het dense indexing. Bekijk het volgende diagram om dit visueel te begrijpen.
- Sparse indexing: Als er alleen voor sommige records een indexentry wordt gemaakt, dan is het sparse indexing. Hier is een diagram dat dit schematisch beschrijft.
De bovenstaande diagrammen maken beide indexeringsschema’s vrij eenvoudig te begrijpen. Een cruciaal punt dat je hier moet maken, is dat het bovenstaande voorbeeld van sparse indexing een combinatie is van zowel dense als sparse indexing. Dat komt omdat voor elke unieke zoek sleutelwaarde (1, 2 en 3) er een index is, maar niet voor elk datarecord.
Nu bestudeer je de andere typen indexeringsschema’s op basis van het niveau van records. Bij single-level indexing is het aantal indexbestanden slechts één. Maar soms wordt het indexbestand zo groot dat het indexbestand zelf wordt geïndexeerd. In dat geval heet het multi-level indexing. Laten we verder duiken.
Hier is een overzicht van de verdere indexeringsstrategieën op basis van niveaus.
Single-level indexing:
- Primaire indexering
- Geclusterde indexering
- Secundaire indexering
Multi-level indexing:
- B-boom
- B+-boom
Je bestudeert alle indexeringsstrategieën die bij single-level indexing horen. Aan het einde van deze tutorial vind je een link om de multi-level indexeringsschema’s te verkennen als je geïnteresseerd bent. Laten we nu primaire indexering bekijken.
Primaire indexering:
Een primaire index is een geordend bestand waarvan de records een vaste lengte hebben met twee velden:
- Het eerste veld is hetzelfde als de primaire sleutel van het databestand.
- Het tweede veld is een pointer naar het datablock waar de primaire sleutel beschikbaar is. - Bron
Een index die is gemaakt voor het eerste record van elk blok wordt block anchors genoemd. Bij primaire indexering geldt: het aantal indexentries = het aantal oorspronkelijke datablocks. Het gemiddelde aantal bloktoegangen met een primaire index is:
Raadpleeg het volgende diagram om dit duidelijker te begrijpen: 
De meest rechtse figuur verwijst naar de oorspronkelijke datarecords die in verschillende blokken zijn verdeeld. Merk op dat de kolom met de getallen 1, 2, 3, ... , 9 de primaire sleutels zijn. De meest linkse figuur verwijst naar de geïndexeerde entries, waarbij elke entry bestaat uit:
- De eerste entry van elk datablock (block anchor)
- De tweede entry geeft de blokpointer aan.
Neem even de tijd en bedenk welk type indexering dit is (sparse of dense). Gebruik de sectie met comments om je antwoorden te posten.
Nu ga je geclusterde indexering bestuderen.
Geclusterde indexering:
Een geclusterde index wordt gemaakt op een databestand waarvan de filerecords fysiek geordend zijn op een niet-sleutelveld dat geen unieke waarde heeft voor elk record. Dit veld staat bekend als het clusteringveld waarop de indexering wordt uitgevoerd. Vandaar de naam: geclusterde index.
Diagrammen maken het altijd makkelijker te begrijpen. Zoals je ziet zijn de oorspronkelijke datarecords geordend op een niet-sleutelattribuut en voor elke unieke waarde van het niet-sleutelattribuut wordt een indexentry gemaakt. Het gemiddeld aantal bloktoegangen dat nodig is om met dit schema een bepaald record te lokaliseren is $\geq$ $\log_{2}B_i + 1$ , waarbij $B_i$ het aantal blokken in de geïndexeerde records is. Let op het $\geq$-teken hier.
Bij de geclusterde index kan het zijn dat je, nadat je het blok hebt gevonden waarin een bepaalde sleutel voorkomt, ook andere blokken moet doorlopen (wat duidelijk blijkt uit de bovenstaande figuur).
Je kunt nadenken over welk type indexering dit is (sparse of dense). Gebruik de comments-sectie om je antwoorden te posten. Laten we nu secundaire indexering bestuderen.
Secundaire indexering:
Stel, je hebt een tabel genaamd Employee in je database. De tabel heeft de volgende attributen:
- employee_id
- employee_name
- employee_department
- employee_salary
employee_id is de primaire sleutel. Je hebt nu al primaire indexering op deze tabel gemaakt op basis van employee_id. Maar tijdens het ontwikkelen van een applicatie ontdekte je dat de meeste databasequeries het attribuut employee_name gebruiken. In dat geval helpt de primaire indexering niet veel, en is het een goede gewoonte om aparte indexering te onderhouden voor alle waarden die bij employee_name horen. Hoe dan ook, de employee-namen zullen niet in geordende vorm in de database aanwezig zijn. Dus ze indexeren zal de queries met namen ongetwijfeld versnellen.
Dit is een klassiek voorbeeld van secundaire indexering. Laten we ook voor secundaire indexering een passend schema construeren:
Voel je vrij om een betere weergave te posten in de sectie met comments als je die hebt.
Nu zie je hoe je indices kunt maken in PostgreSQL. Volg DataCamp’s cursus Joining Data in PostgreSQL als je de basis onder de knie wilt krijgen.
Indices maken in PostgreSQL
Voordat je indices gaat maken in een PostgreSQL-database, heb je wat data in een tabel nodig. Laten we een eenvoudige tabel Student maken met de volgende records:
- student_id
- student_name
- student_year
Je maakt van student_id de primaire sleutel en je staat niet toe dat de studentnamen en studiejaren null zijn.
De query hiervoor is als volgt:
CREATE TABLE STUDENT(
student_id TEXT PRIMARY KEY,
student_name TEXT NOT NULL,
student_year TEXT NOT NULL
);
Nadat de tabel succesvol is aangemaakt, moet je geschikte data in de tabel invoegen. Voor het gemak kun je deze .csv gebruiken en importeren in Student. Je kunt een compatibel .csv-bestand in een PostgreSQL-tabel importeren met de volgende query:
COPY STUDENT FROM '/path/to/csv/Student.csv' WITH (FORMAT csv);
Bovenstaande query gaat ervan uit dat de naam van het .csv-bestand waarvan data wordt gekopieerd Student is.
Laten we nu naar de data kijken. Het uitvoeren van select * from STUDENT; geeft de volgende records: 
De select-query zou in totaal 86 records moeten teruggeven. Nu ben je in de positie om indices te maken. Je kunt indexen met één kolom maken met de volgende syntax:
CREATE INDEX index_name
ON table_name (column_name);
Laten we een index maken op het veld student_id (primaire indexering).
CREATE INDEX id_index
ON STUDENT (student_id);
Je kunt ook indexen met meerdere kolommen maken:
CREATE INDEX id_index
ON STUDENT (student_id,student_name);
Je kunt een index verwijderen met de volgende syntax:
DROP INDEX index_name;
Het is lastig om goed te voelen wat indexering doet in een kleine tabel zoals STUDENT. Maar als de tabel groot zou zijn (denk aan een databasetabel met vergelijkbare records van een grote universiteit), dan zou indexering ongetwijfeld een vitale rol spelen.
Je hebt het gehaald!
Gefeliciteerd dat je tot het einde bent gekomen. In deze tutorial heb je geleerd over indexering, waarom het nodig is en verschillende indexeringsschema’s. Je hebt ook gezien hoe je eenvoudige indexering in PostgreSQL kunt uitvoeren.
Maar je hebt in deze tutorial niet geleerd over multi-level indexering. De volgende bronnen zijn uitstekend als je die wilt verkennen:
Helpt het gebruik van indexering altijd? Er zijn gevallen waarin indexering niet aan te raden is.
Ik hoop dat deze tutorial je heeft geholpen om de basis van indexering helder te krijgen. Laat me je interessante bevindingen weten in de sectie met comments.
Bekijk de Learn SQL Hub van DataCamp.
Onderwerpen
