Analyze Sydney property prices with Amazon Redshift Serverless in GA
Tags: aws, tutorial, database, analytics
Published: 2023-01-29 09:57:32


## Are you concerned about Sydney property prices?

With Covid-19, tech workers have the freedom to work remotely permanently or as they return to the office in a hybrid environment.

Thus employees have opted for a lifestyle that includes the beach, country-side, city, suburbia or with more cultural amenities.

With low unemployment figures in Sydney, should upwardly mobile professionals stay in Sydney or move to a different city?

### Lesson Outcomes

* You will learn how to create a data warehouse with Amazon Redshift Serverless (Generally Available)

* Discover data with AWS Glue Data Catalog

* Query your data with Amazon Redshift Serverless (Generally Available)

### What is Amazon Redshift Serverless?

Amazon Redshift Serverless is generally available since 12 July 2022, you may view the announcement [here](https://aws.amazon.com/about-aws/whats-new/2022/07/amazon-redshift-serverless-generally-available/) and this allows you to build a data warehouse without having to provision a Redshift cluster and it can easily scale to your [usage needs](https://docs.aws.amazon.com/redshift/latest/mgmt/serverless-whatis.html).

As a data scientist or data analyst you can use Query Editor v2 to query the data and answer analytic questions.

You may use with Amazon Redshift Severless in GA and access **$300 free AWS credits** for 90 days in the following [AWS regions](https://aws.amazon.com/blogs/aws/amazon-redshift-serverless-now-generally-available-with-new-capabilities/) as at 12 July 2022:

* US East (Ohio, N. Virginia)
* US West (N. California, Oregon)
* Europe (Frankfurt, Ireland)
* Asia Pacific (Tokyo)
* Europe (London)
* Europe (Stockholm)
* Asia Pacific (Seoul)
* Asia Pacific (Singapore)
* Asia Pacific (Sydney)

You may refer to the pricing of Amazon Redshift Serverless [here](https://aws.amazon.com/redshift/pricing/). You only pay for the compute resources that you use with the pay as you go model.

### What are the use cases?

You don't need a data engineer to help you provision data warehouse resources, whether you work for a startup or enterprise you can access:

* Self-service analytics
* Generate insights that can auto scale

### Solution Architecture

This is my summary of the solution architecture for **Amazon Redshift Serverless** and **Amazon Redshift ML**

![serverk](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/91slbqb5vv3t2fexlam6.jpg)


![redshift Ml](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/khyi6h7kyavyumjjkcwb.jpg)
### Dataset

This open source dataset set 'Sydney House Prices' is provided  [here](https://www.kaggle.com/datasets/mihirhalai/sydney-house-prices?resource=download) by Kaggle.com from 2000 to 2019. 

### Data Dictionary



**Column**	**Data Type**

Date,            Date
Id,              String
suburb,          String
postalCode,      String
sellPrice,       Integer
bed,             Integer
bath,            Integer
car,             Integer
propType,        Integer



### Tutorial: Getting started with Amazon Redshift Serverless

This tutorial follows the instructions provided by the documentation [here](https://docs.aws.amazon.com/redshift/latest/mgmt/serverless-known-issues.html).

Step 1: Sign into the AWS Management Console as an IAM user.


![IAM](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mzcecj13g5y797jho8fk.jpg)

Step 2: Change the AWS region to US-East-2 (Ohio)

Step 3. Upload the Sydney house prices dataset into an Amazon S3 bucket.


![bucket](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ood55hz57l4kph1bva4q.jpg)

![bucket uploaded](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/i1ci7b4ucn8jjxwg8kxj.jpg)

Create two folders in the S3 bucket for data transformations name them 'raw data' and 'processed'.


![folders](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fy46hoc25kj8sduwszwm.jpg)

Step 4: Create a crawler from the AWS Glue console by selecting a data source.


![crawler](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8a11l4r9imd8bj61bup3.jpg)

Step 5: I used an existing IAM role for AWS Glue


![IAM](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/u8ajj2qiwdkj1tushk0l.jpg)

Create a database.



![database](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hvoxtd73g6kz0fwmnyel.jpg)


Step 6: Review the Crawler and click **Run Crawler**.


![ review crawler](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/rj8dqiqoeer4xfpk8nzc.jpg)

The processing job of the crawler is completed.


![job completed](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/rufe4xotl2vstkaodlp9.jpg)

Refresh your web browser and navigate to **Database -> Table**

Review the schema created from the raw data.


![created schema](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0wru1ya6x3qc4tyk7moz.jpg)

Edit the schema to change the data type for the variables **Date** and **postalCode** to Date and String respectively.


![edit schemea](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8f89tdahwavfsd8oamg7.jpg)

You may review the updated schema changes.



![schema update](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/eqwpxk4zf3bsd59jr7bt.jpg)

Step 7: Navigate to Amazon Redshift in the search bar and click **Try Amazon Redshift Serverless**

![ohion](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3im3yvyzlvnrcqikltoa.jpg)

Step 8:  Select default settings to create a Redshift cluster.
Because I nominated the AWS region Ohio, I will receive $300 free AWS credits over the 90-day free trial for AWS Redshift Serverless service.

![cluster](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/lc26g4n219sxzse5dkot.jpg)

Under the default settings, a namespace and workgroup will be automatically created by Amazon Redshift Serverless.


![namespace](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wm3dkzjfdjh467fn57fp.jpg)

Step 9: Create a default IAM role which can access any Amazon S3 bucket.



![Iam rileon](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/959htao1wawzb2xt2uov.jpg)

Refresh your web browser, you will be able to see that the IAM policy 'Amazon Redshift Full Access' is successfully associated with the IAM user role. The VPC and security groups have been created.


![Iassociate](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/l8fd5jzkn5hw8btxopc0.jpg)

Step 10: Click **Save Configuration**.

The Redshift cluster will take a few minutes to process.


![dashboard](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/pka518lna6sgc6ds1xzj.jpg)

Note: The 90-day free trial of Amazon Redshift Serverless will expire on 2 April 2023, please take note of your free trial expiration date.


Step 11: Click on **Query Editor** to access **Query Editor v2**


![query edit](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/j0180k6jvssls5vxted2.jpg)


Step 12: Double-click on **Serverless:default** and **dev** database.

Create a table.

```
CREATE TABLE sydney_houseprices (
    date DATE,
    id VARCHAR(40),
    suburb VARCHAR(100),
    postalcode VARCHAR(40),
    sellprice bigint,
    bed bigint,
    bath bigint,
    car bigint,
    proptype VARCHAR(40))
    ;
```
Step 14: Load data into a new table using the COPY command for Amazon Redshift.

```
COPY sydney_house_prices 
FROM 's3://sydney-house-price/processed/SydneyHousePrices1.csv' 
IAM_ROLE 'arn:aws:iam::XXXXXXXXXXX:role/service-role/AmazonRedshift-CommandsAccessRole-XXXXXXXXXXXX' 
delimiter ','
dateformat 'YYYY-MM-DD'
IGNOREHEADER 1;
```

![load data](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/tnondg3m8hzsmwme91o3.jpg)

Note: If you have trouble loading data into the table. Try these tips:

1. Change your input csv file date format from Australian to American date format i.e. 'YYYY-MM-DD'

2. Include the SQL statement 'IGNOREHEADER 1' to skip loading the header

3. Check for load errors:

```
SELECT * 
FROM stl_load_errors
```

Step 15:  Check that data has successfully loaded into the table. Preview the first 10 rows.

```
SELECT * 
FROM sydney_house_prices 
LIMIT 10;
```

Step 16: Query your new table with your own analytical questions.

Q1. What was the most expensive suburb in the year 2000?

```
SELECT MAX(sellprice), suburb, DATEPART(year,date) AS year, postalcode AS postcode, date, id
FROM sydney_house_prices
WHERE year = '2000'
GROUP BY 2, 3, 4, 5,6
;
```
The suburb Bondi Beach had the most expensive property sold in the year 2000 at $3.5 million.

![Bondi Beach](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ml390nj4d8ntws38djro.jpg)

Q2. What was the most expensive suburb in the year 2019?

```
SELECT MAX(sellprice), 
suburb, 
DATEPART(year,date) AS year, 
postalcode AS postcode, 
date, 
id,
proptype,
FROM sydney_house_prices
WHERE year = '2019' AND proptype = 'house'
GROUP BY 2, 3, 4, 5,6,7
ORDER BY 1 DESC
;
```
Rose Bay was the most expensive suburb in the year 2019 with a  property sold for $13.4 million.

![redshift serverless](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/uknt2cnjwbzqriz9civ8.jpg)


Q3. What was the average price of a house in Concord in 2019 with two car park spots and two bathrooms?

```
SELECT AVG(sellprice), 
suburb, 
DATEPART(year,date) AS year, 
proptype,
bed, 
car,
bath,
FROM sydney_house_prices
WHERE year = '2019' AND 
proptype = 'house' 
AND suburb = 'Concord'
AND bath =2
AND car = 2
GROUP BY 2, 3,4,5,6
;
```
The average price of a house in Concord in the year 2019 with two car park spots and two bathrooms is $1,968,000.


![Q3](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9m6is9ylfcww41p7q5us.jpg)

Q4. Which suburb sold the most properties in the year 2019 and what was the average property price?

```
SELECT COUNT(proptype) AS propertycount, 
AVG(sellprice) AS avg_pricesold,
suburb, 
DATEPART(year,date) AS year, 
postalcode AS postcode, 
date,
FROM sydney_house_prices
WHERE year = '2019' 
GROUP BY 3, 4,5,6
ORDER BY 1 DESC
;
```
The suburb Warriewood sold the most properties in 2019 with an average selling price of $1,038,136.


![Q4](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/c20ze6ep86lxcv95tfds.jpg)

Q5. Where can I live with a budget of between $1,200,000 and $1,500,000 in the year 2019 with three bedrooms, two bathrooms and one car park spot?

```
SELECT sellprice, 
suburb, 
DATEPART(year,date) AS year, 
car,
bath,
bed,
FROM sydney_house_prices
WHERE year = '2019' AND 
sellprice BETWEEN 1200000 AND 1500000
AND bed = 3
AND bath = 2
AND car = 1
;
```
Within this budget, in 2019 I can afford to live in the suburbs
Maroubra, Turramurra, Cremorne, Ryde, Sans Souci and Allambie Heights.


![budget](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mnw7scflaxebdjn45ag5.jpg)


### Amazon Redshift Serverless (Generally Available) - Clean Up Resources

If you have finished using Amazon Redshift Serverless, you should follow best practice and clean up your resources by following the steps in the AWS documentation at this [link](https://docs.aws.amazon.com/redshift/latest/gsg/cm-dev-t-clean-up-resources.html).

I encourage you to make the most of your Redshift Serverless free-trial in GA which is 90 days and keep exploring with your $300 AWS credits.

### AWS Innovate - Coming Soon!

If you would like to learn more about AWS data or AI/ML please join one of the FREE online conferences in your city. 

Register at this [link](https://aws.amazon.com/events/innovate-online-conference/)

* Asia Pacific and Japan on 22 February 2023

![APJ](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zyzcf691eg35udtmcg3h.jpg)

* EMEA on 9 March 2023


![EMEA](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/h2cn5mdbqq8ktyl3620k.jpg)

* Americas on 14 March 2023 (this includes Pi Day celebrations)


![march 14](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xb16ub34k7nfulvmtmuj.jpg)

### References

* [Amazon Redshift Serverless documentation](https://docs.aws.amazon.com/redshift/latest/mgmt/working-with-serverless.html)

* [Amazon Redshift Serverless Generally Available](https://aws.amazon.com/about-aws/whats-new/2022/07/amazon-redshift-serverless-generally-available/)

* [AWS News blog](https://aws.amazon.com/blogs/aws/introducing-amazon-redshift-serverless-run-analytics-at-any-scale-without-having-to-manage-infrastructure/)

* [Amazon Redshift Serverless pricing](https://aws.amazon.com/redshift/pricing/)

* [Amazon Redshift Serverless free AWS credits](https://aws.amazon.com/blogs/aws/amazon-redshift-serverless-now-generally-available-with-new-capabilities/)

Until the next lesson, Happy Learning! 😁