Opzetten van AWS Redshift voor Cloud Data warehousing

In deze zelfstudie leg ik uit en begeleid ik hoe je AWS Redshift kunt instellen om Cloud Data Warehousing te gebruiken. Redshift is een volledig beheerde petabyte data warehouse dienst die door Amazon Web Services in de cloud wordt geïntroduceerd. Het werkt door een of meer verzamelingen van rekenhulpbronnen, nodes genoemd, samen te voegen tot een groep, een cluster. Elk cluster draait een Redshift engine en kan een of meer databases bevatten. De architectuur kan in principe als volgt uitgewerkt worden:

1. Hoe werkt het

In principe is Redshift gebaseerd op PostgreSQL als kernmotor, dus de meeste SQL toepassingen kunnen met Redshift werken. Redshift kan ook geïntegreerd worden met een breed scala van toepassingen, waaronder BI, analyse, en ETL (Extract, Transform, Load) tools waarmee analisten of ingenieurs met de gegevens die erin zitten kunnen werken.

Als een gebruiker een Amazon Redshift data warehouse opzet, heeft hij een kerntopologie voor bewerkingen die een cluster heet. Een Redshift cluster bestaat uit 1 of meer compute nodes. Als de gebruiker ervoor kiest om meer dan één compute node te gebruiken, start Redshift automatisch een hoofdknooppunt. Deze leiderknoop is ingesteld om verzoeken en opdrachten van de kant van de cliëntuitvoering te ontvangen en wordt niet door AWS gefactureerd.

Cliënt-applicaties communiceren alleen met de leader node. De rekenknooppunten onder de leader node zijn transparant voor de gebruiker. Wanneer cliënten een query uitvoeren, analyseert de leidende node de query en maakt een optimaal uitvoeringsplan voor uitvoering op de rekenknooppunten, rekening houdend met de hoeveelheid gegevens die op elk knooppunt is opgeslagen.

In deze zelfstudie laat ik je zien hoe je Redhift voor eigen gebruik opzet en configureert. In dit voorbeeld maak ik een account aan en begin met het gratis tier pakket.

2. Configuratie fase

2.1 Voorwaarde

Voor we beginnen met het opzetten van een Amazon Redshift cluster, is er een bepaalde voorwaarde die vervuld moet worden.

Meld je eerst aan bij AWS en ga dan, eenmaal klaar, naar de IAM dienst om een rol aan te maken die we kunnen gebruiken voor Redshift gebruik. Je kunt de schermafbeelding volgen zoals hieronder:

Als dat gedaan is, zou je een screenshot moeten krijgen zoals hieronder vermeld dat de rol met succes is aangemaakt.

2.2 Redshift configuratie instellen

Nu de eerste vereisten gedaan zijn, kunnen we verder gaan met het aanmaken van onze eigen Redshift Cluster. Zoek naar Redshift functies in het zoekvenster en ga van daaruit verder. Hieronder staan de screenshot voorbeelden:

Klik op de knop Cluster maken en ga verder met de benodigde variabelen, merk op dat we aan de kant van de Cluster Toestemming onze IAM rol opnemen die we eerder hebben aangemaakt.

Als dat gedaan is, zou je in het redshift dashboard moeten belanden zoals hierboven. Voor deze tutorial zullen we de netwerk beveiligingslaag uitschakelen door de beveiligingsgroep te veranderen. Om dat te doen ga je naar de onderkant van het dashboard en voeg je de Redshift poort toe in de Inkomende tab. Hieronder staat een voorbeeld:

Als alles klaar is, zou je moeten zien dat het nieuwe cluster dat je gemaakt hebt nu beschikbaar is om gebruikt te worden.

3. Testfase

Laten we nu proberen ons data warehouse te benaderen. Om het uit te testen klik je op EDITOR in het linker deelvenster, neem de nodige variabelen op en klik op Connect to database

Je zou nu op een editor pagina gebracht moeten worden, laten we nu beginnen met het maken van ons eigen testschema. Maak een schema zoals hieronder en voer het uit.

Mooi, laten we nu testen aan de lokale kant van de cliënt. Om dat te doen moet je ofwel JDBC of ODBC verbinding krijgen van de Redshift kant. Om die info te krijgen, klik je op de Config knop in het linkerdeelvenster van het dashboard.

Kies je voorkeur van verbindingstype en download dan de benodigde bibliotheken en kopieer de URL zoals in het onderstaande voorbeeld:

Open vervolgens een van je SQL cliënt gereedschappen en voer de benodigde verbindingsvariabelen in. In ons voorbeeld hier gebruiken we SQL cliënt gereedschappen met de naam DBeaver die je hier kunt krijgen

Je verbinding zou zoals verwacht succesvol moeten zijn. Als je in elk geval op een probleem met de authenticatie stuit, kijk dan bij je configuratie die onder de AWS beveiligingsgroep gemaakt is voor verdere details.

Laten we nu een stel tabellen aanmaken onder ons vorige nieuw aangemaakte schema. Hieronder staat een voorbeeld van tabelaanmaak die we in ons cluster zullen uitvoeren:

 SET search_path = imdb; 

create table users( 
userid integer not null distkey sortkey, 
username char(8), 
firstname varchar(30), 
lastname varchar(30), 
city varchar(30), 
state char(2), 
email varchar(100), 
phone char(14), 
likesports boolean, 
liketheatre boolean, 
likeconcerts boolean, 
likejazz boolean, 
likeclassical boolean, 
likeopera boolean, 
likerock boolean, 
likevegas boolean, 
likebroadway boolean, 
likemusicals boolean); 

create table venue( 
venueid smallint not null distkey sortkey, 
venuename varchar(100), 
venuecity varchar(30), 
venuestate char(2), 
venueseats integer); 

create table category( 
catid smallint not null distkey sortkey, 
catgroup varchar(10), 
catname varchar(10), 
catdesc varchar(50)); 

create table date( 
dateid smallint not null distkey sortkey, 
caldate date not null, 
day character(3) not null, 
week smallint not null, 
month character(5) not null, 
qtr character(5) not null, 
year smallint not null, 
holiday boolean default('N')); 

create table event( 
eventid integer not null distkey, 
venueid smallint not null, 
catid smallint not null, 
dateid smallint not null sortkey, 
eventname varchar(200), 
starttime timestamp); 

create table listing( 
listid integer not null distkey, 
sellerid integer not null, 
eventid integer not null, 
dateid smallint not null sortkey, 
numtickets smallint not null, 
priceperticket decimal(8,2), 
totalprice decimal(8,2), 
listtime timestamp); 

create table sales( 
salesid integer not null, 
listid integer not null distkey, 
sellerid integer not null, 
buyerid integer not null, 
eventid integer not null, 
dateid smallint not null sortkey, 
qtysold smallint not null, 
pricepaid decimal(8,2), 
commission decimal(8,2), 
saletime timestamp); 

Het verwachte resultaat wordt getoond zoals hieronder :-.

Laten we vervolgens proberen om voorbeeldgegevens in ons data warehouse te uploaden. Voor dit voorbeeld heb ik een voorbeeldgegeven geüpload in mijn eigen S3 emmer en gebruik dan het onderstaande script om de gegevens uit het S3 bestand naar Redshift te kopiëren.

 
copy sales from 's3://shahril-redshift01-abcde/sales_tab.txt' 
iam_role 'arn:aws:iam::325581293405:role/shahril-redshift-s3-ro-role' 
delimiter '\t' timeformat 'MM/DD/YYYY HH:MI:SS' region 'eu-west-1'; 

copy dates from 's3://shahril-redshift01-abcde/date2008_pipe.txt' 
iam_role 'arn:aws:iam::325581293405:role/shahril-redshift-s3-ro-role' 
delimiter '|' region 'eu-west-1'; 

Als je tijdens het laden op een of andere manier op een probleem stuit, kun je een query doen uit de redshift woordenboek tabel met de naam stl_load_errors zoals hieronder om een hint te krijgen van het probleem.

 select * from stl_load_errors ; 

Tenslotte zou je, als alles klaar is, de gegevens moeten kunnen extraheren en manipuleren met een willekeurige SQL functie. Hieronder staan enkele voorbeeldscripts van queries die ik voor ons voorbeeld gebruikt heb.

 
-- Get definition for the sales table. 
SELECT * 
FROM pg_table_def 
WHERE tablename = 'sales'; 

-- Find total sales on each day 
SELECT b.caldate days, sum(a.qtysold) FROM sales a, dates b 
WHERE a.dateid = b.dateid 
group by b.caldate ; 

-- Find top 10 buyers by quantity. 
SELECT firstname, lastname, total_quantity 
FROM (SELECT buyerid, sum(qtysold) total_quantity 
FROM sales 
GROUP BY buyerid 
ORDER BY total_quantity desc limit 10) Q, users 
WHERE Q.buyerid = userid 
ORDER BY Q.total_quantity desc; 

-- Find events in the 99.9 percentile in terms of all time gross sales. 
SELECT eventname, total_price 
FROM (SELECT eventid, total_price, ntile(1000) over(order by total_price desc) as percentile 
FROM (SELECT eventid, sum(pricepaid) total_price 
FROM sales 
GROUP BY eventid)) Q, event E 
WHERE Q.eventid = E.eventid 
AND percentile = 1 
ORDER BY total_price desc; 

Duim omhoog! We hebben nu met succes onze eigen Redshift Cluster opgezet voor data warehousing gebruik. Nu gaan we kijken hoe we bestaande gegevens in Redshift Cluster kunnen combineren met een willekeurig flat-file met behulp van Redshift Spectrum.