top of page
  • FC

How to build a data warehouse with AWS Redshift

I've been working on an idea for a while now that needed the ability to store and search through a large amount of data, we are not talking about Big Data but just normal day-to-day Terabytes of data.


First a little background as to what I tried and why it didn't work:


I initially looked at building a normal RDBMS system, something like MySQL or Postgres as they are systems I am already comfortable with, but the downside is that I need this dataset on the move wherever I am in the world. I can build it on my laptop but that means zero space for anything else, and building it onprem on our Petabyte array wouldn't give me the portability I need without exposing ports to the internet. To the cloud I went!


My first attempts involved an Azure VM with SQL running, but this turned out to be unfeasible because of the speed and lack of scalability. So I spoke with two people that are well versed in this areas: @troyhunt (on Setting up Table Storage) and @Scott_Helme (on Hacking Table Storage). Both have massively large datasets that are queried by a webapp, the end goal I had in mind!


They suggested I use a Azure Table Storage. It was fantastic: super easy to chuck data into and the speed was amazing off the bat. But it had one flaw, it doesn't support full SQL queries and the one thing I needed was the "like" query. I abandoned my now third attempt at building this and looked at alternatives. Thanks to @DeathsPirate's suggestion, I turned to Amazon services and Redshift, a data warehouse solution for massive data. This turned out to be exactly what I was looking for.


You need a two things to start with to make your first data warehouse, the first being an Amazon AWS account, the second is your data.


If you're going to give Redshift a go, I highly recommend that you use the 2 month trial if you haven't already got an account.



Redshift is a scale-out rather than a scale-up cluster system, which means you don't keep adding more and more ram or CPU. You add nodes to a cluster to massively parallel the workload, Redshift takes care of all the mechanics of that but for our example we need to build that cluster first.



Select the new cluster button and go with the defaults, there are many types of cluster available but for POC testing the default is more than enough. This builds a Dense Compute cluster with one Lead Node and 2 Cluster Nodes underneath it, each with 2 CPU cores, 15GB of ram and 160GB of SSD storage attached.


Once it is up and running you will be given a summary page with a few key bits of information you need going forward. Specifically the EndPoint (highlighted) and the JDBC URL further down the page.




We now need to ingest data somehow and whilst you might be tempted to think that Insert statements from your SQL days are the easiest, you should consider using the COPY command and S3 Buckets. Obviously your data might be coming from somewhere else but in our example here we are going to be dealing with CSV files.


Setting up an S3 Bucket is incredibly easy on AWS, just select S3 from the services menu and you will be presented with the option to create a new bucket; beyond giving it a name and selecting the region there's not much to configure. I made my Redshift Cluster in US-East-2 (Ohio), so it makes sense I select the same region for speed reasons.



Once we have the S3 bucket running we can upload a file to it, in our case a single CSV file called 'input_headless.csv'.


The next step is to be able to interact with Redshift and create the tables and import the file from S3. There are several ways you can do this, first is to use SQL Workbench/J (note this is not Oracle's SQL Workbench). By using SQL workbench/J we can feed it the JBDC URL from above and connect to the database. The second method is probably easiest for most starting out. The AWS Redshift Console has a new function called Query Editor.




From here or Workbench/J you can create your table. Here is my example one:




Before we can inport data from S3 into Redshift we have to make sure that the permissions are set correctly. Select the IAM service from the services menu in AWS and create a new role for Redshift and select Redshift customisable:



There are loads of premade ones, but we only need readonly access so search for S3 and you will see AmazonS3ReadOnlyAccess:



After the role is created you will see an ARN - copy this as we will need this soon. For now we need to associate this new role with our Redshift Cluster. You do this by going back into Redshift Cluster settings and managing the IAM roles.



Select your new role from the list and finally we are ready to import our data straight from the S3 bucket. We do that by using the COPY command as mentioned earlier:



Once all the data is imported we can now start using the database as normal.



Hopefully this has been helpful in explaining the first steps in getting working with Amazon Redshift.

73 views

Related Posts

See All

3 Comments


Jeffrey Glenn
Jeffrey Glenn
Oct 19, 2023

I appreciate Henry for making me realise the truth to a certified hacker who knows a lot about what he is doing. I strongly recommend you hire him because he’s the best out there and always delivers. I have referred over 10 people to him and all had positive results. He can help you hack into any devices, social networks including – Facebook, Hangout, iMessages, Twitter accounts, Snap chat , Instagram, Whatsapp, wechat, text messages ,smartphone cloning,tracking emails and also any other social media messenger or sites. It’s advisable to hire a professional hacker.Thank me later. Contact him here., Henryclarkethicalhacker@gmail.com and you can text, call and Whatsapp him on +1(201)4305865, or +1(219)7960574.....



Like

Agnes Lizzy
Agnes Lizzy
Oct 14, 2023

Contact him for any type of hacking, he is a professional hacker that specializes in exposing cheating spouses, and every other hacking related issues. he is a cyber guru, he helps catch cheating spouses by hacking their communications like call, Facebook, text, emails, Skype, whats-app and many more. I have used this service before and he did a very good job, he gave me every proof I needed to know that my fiancee was cheating. You can contact him on his email to help you catch your cheating spouse, or for any other hacking related problems, like hacking websites, bank statement, grades and many more. he will definitely help you, he has helped a lot of people, contact him on,…

Like

Janet Lucy
Janet Lucy
Oct 12, 2023

I’m excited to write about Henry Hacker, he is a great and brilliant hacker who penetrated my spouse’s phone without a physical installation app. And I was able to access my spouse’s phone, SMS, Whatsapp, Instagram, Facebook, Wechat, Snapchat, Call Logs, Kik, Twitter and all social media. The most amazing thing there is that he restores all phone deleted text messages. And I also have access to everything including the phone gallery without touching the phone.I can see the whole secret of my spouse. Contact him for any hacking service. He is also a genius in repairing Credit Score, increasing school grade, Clear Criminal Record etc. His service is fast. Contact:, Henryclarkethicalhacker@gmail.com and you can text, call him on whatsapp…


Like
bottom of page