Tuesday, March 31, 2020

How to access data of Redshift cluster from PostgreSQL RDS

We were using Amazon Redshift as our data warehouse. It is a columnar-oriented petabyte-scale,  OLAP database suitable for analytical queries and applications.

There were many times when we wanted to pull data from Amazon Redshift and power our dashboards. This is basically using an OLAP database for OLTP use case - it was obviously not going to work very well.

Amazon RDS provides a feature called DBLink. DBLink enables us to access Amazon Redshift table directly from Amazon RDS. We don't need to explicitly copy over the data from Redshift to RDS, DBLink handles it for us and moves the data at the block level.

In this post, we will review the steps needed to setup the DBLink on Amazon RDS. This post assumes that you already have a running Amazon Redshift cluster and an Amazon RDS PostgreSQL instance.

Setting up DBLink
  • First we need to enable the DBLink extensions on the PostgreSQL RDS instance.
  • Next step is the main step which creates the link to the Amazon Redshift cluster and PostgreSQL RDS instance.
  • To achieve this we need to create the server link and the user mapping
  • Please note that all the values that are enclosed within <angle brackets> need to be replaced with actual values from your environment.
  • You might have to use the private IP address of your Redshift cluster. It totally depends on your setup.
  • Now that the DBLink is created, its time to query the Redshift cluster from the PostgreSQL to check if everything is setup correctly
  • Everything enclosed within $REDSHIFT$ is the actual query that runs on Redshift and results are sent back to PostgreSQL.
  • To create the cache of the Redshift data, we need to create a materialised view. 
  • Query the view just like you query any other view.
  • To refresh the view, use the following code

Thats about it, these are the simple steps using which you can access data of Redshift cluster from PostgreSQL RDS instance.

Saturday, February 29, 2020

Book Summary: Ikigai - The Japanese Secret to a Long and Happy Life

I truly believe everyone should have reading as one of their habits. It gives us great insights and widens our horizons. Reading takes you places and gives you new ideas. At Makkajai, we have been buying lots of books for office reading. 

We observed that even though we have lots of good books in office, not everyone is reading them or making an effort to read them. To push everyone to read more often, we came-up with a simple idea. Everyone has to write one pager book summary every month! 

It doesn't matter what book you read, it doesn't matter how thick or thin it is. All that matters is that everyone writes a book summary for the book they have read.

Very recently, I had read the book Ikigai: The Japanese Secret to a Long and Happy Life by Hector Garcia and Francesc Miralles. It goes without saying that, this is a pretty awesome book. To keep up with the tradition, I did write a short book summary this book. Turns my team members liked the summary hence, I decided to convert he book summary into a blog post in the hope that, more and more people will find this book interesting and worth reading.

Book Summary

This books demonstrates various topics related to Art of Living. Its the art of living and not art of existence or survival :). The book defines what is ikigai and the rules of ikigai. In Japanese, ikigai (生き甲斐) is a combination of letters that mean "life" and "to be worthwhile". Authors of the book try to unravel the secrets of long and happy life.

How do they do it? They conducted a total of one hundred interviews in Ogimi, Okinawa - Japan to try to understand the longevity secrets of centenarians and super-centenarians i.e. people who have lived for 100 and 110+ years!

The village of Ogimi in Japan has the most number of centenarians and super-centenarians in the world. Authors talk about various experiences while interviewing centenarians and super-centenarians.

So what did they find? 

Ikigai can be illustrated with this diagram very easily. It’s basically an intersection of “What you are good at”, “What you love”, “What the world needs” and “What you can be paid for”!

  • Ikigai roughly translates into “the happiness of always being busy.” 
  • We all have a certain passion inside us, a thing that we love to do and the thing that keeps us going. A unique talent that gives meaning to our days and drives us to share the best of ourselves until the very end. 
  • If we don’t know what this thing is yet, then our mission is to discover it.
  • Everyone’s ikigai is different, but one thing we have in common is that we are all searching for meaning. 
  • When we spend our days feeling connected to what is meaningful to us, we live more fully; when we lose the connection, we feel despair. 
  • Our ikigai is hidden deep inside each of us and finding it requires a patient search. 
  • Once we discover our ikigai, pursuing it and nurturing it every day will bring meaning to your life.
  • One thing that everyone with a clearly defined ikigai has in common is that, they pursue their passion no matter what.

What are the rules for Ikigai?
  • Stay active; don’t retire. Keep doing things that you love to do.
  • Take it slow. Don’t be in a hurry all the time. This is one of the most important thing to remember. Live life fully from today, right now, in this moment, do not wait for some obscure time in the future to do it.
  • Eat less. Only about 80% of your hunger. Obviously its not easy to figure out what is 80% of hunger, but the idea is to not fill up your stomach to 100% of its capacity :)
  • Surround yourself with good friends. If you are to live longer, you will need good company when you are old.
  • Get in shape, exercise and regularly move your body. At least try and do mild exercise 3-5 days in a week.
  • Smile and do it often. This is something that we all can do more of.
  • Reconnect with nature. Appreciate its beauty. Grow a garden in your home, go for a trek!
  • Give thanks and mean it.
  • Live in the moment. A lot of times we are either worried about the future or sad about the past. Don’t do it, Live in the moment.

Avoiding Stress & Existential Crisis

Many people seem older than they actually are. Why do you think that happens?
  • Research into the causes of premature ageing has shown that, stress has a lot to do with it.
  • Existential crisis, on the other hand, is typical of modern societies in which people do what they are told to do, or what others do, rather than what they want to do. Peer pressure, makes us do things that we don't really care about.
  • People often try to fill the gap between what is expected of them and what they want for themselves with economic power or physical pleasure, or by numbing their senses
  • Those who give up the things they love doing, lose their purpose in life.

Be in Flow

Flow is the mental state in which a person performing an activity is fully immersed in a feeling of energised focus, full involvement, and enjoyment in the process of the activity!
  • We have to focus on increasing the time we spend on activities that bring us to state of flow, rather than allowing ourselves to get caught up in activities that offer immediate pleasure.
  • Concentrating on one thing at a time may be the single most important factor in achieving flow. Too many times we try to do things in parallel and divide our attention. This is a sure shot recipe for not achieving the state of flow.
  • Our ability to turn routine tasks into moments of micro-flow, into something we enjoy, is key to our being happy.
  • The happiest people are not the ones who achieve the most. They are the ones who spend more time than others in a state of flow.

Slow Living
  • Being in a hurry is inversely proportional to quality of life. As the old saying goes, ‘Walk slowly and you’ll go far.’ 
  • When we leave urgency behind, life and time take on new meaning.
  • There is a huge difference between being in a rush all the time vs keeping yourselves busy with things that you love to do.
  • Never stop doing the things that you love to do!
  • Slow living doesn't mean that we stop being busy, it means we stop being in hurry!
And finally here are a few secrets of centenarians in their own words:

What should we do to live a long and happy life?
  • Don’t worry
  • Cultivate good habits
  • Nurture your friendships every day
  • Live an unhurried life
  • Be optimistic
  • Eat and sleep, and you’ll live a long time. 
  • Learn to relax.
  • Your mind and your body. You keep both busy, you’ll be here a long time.

Overall, I have learned a lot from this book and pretty sure that everyone can learn many things from it. Do read it and let me know how you liked it.

Friday, January 31, 2020

How To move to Spring Boot 2 and Flyway 5 from Spring Boot 1.5 and Flyway 3

I had to figure this out the hard way, as soon as we upgraded our project from Spring Boot 1.5 to Spring Boot 2, all our existing migrations written using Flyway 3 started bombing.

After reading the docs I realised that the migration schema used by Flyway 3 isn't compatible with Flyway 5. What's more, there is not direct upgrade path from Flyway 3 to Flyway 5.

According to the official documentation we need to do the following:
  • First upgrade your 1.5.x Spring Boot application to Flyway 4 (4.2.0 at the time of writing), see the instructions for Maven and Gradle
  • Once your schema has been upgraded to Flyway 4, upgrade to Spring Boot 2 and run the migration again to port your application to Flyway 5.
This essentially means we will have to do two releases, which I didn't really want to do. Another approach to getting this fixed is described here. I haven't tried it but it should work.

For me, all I wanted was to get my project upgraded and working. I had a few migrations but I also had lots of DB snapshots which we could use to restore the old database. Hence, I decided to take the easy way out. I looked for ways to ignore the existing migrations, in the Flyway documentation we were able to find two settings that could be used. 

# Whether to automatically call baseline when migrate is executed against a non-empty schema with no schema history
# table. This schema will then be initialized with the baselineVersion before executing the migrations.
# Only migrations above baselineVersion will then be applied.
# This is useful for initial Flyway production deployments on projects with an existing DB.
# Be careful when enabling this as it removes the safety net that ensures
# Flyway does not migrate the wrong database in case of a configuration mistake! (default: false)
# The version to tag an existing schema with when executing baseline. (default: 1)

As the documentation suggest, basically it will ignore all the migrations till the version 20200131132000 and thats exactly what we wanted to do.

When using these settings with Spring Boot you need to append "spring." to them hence, the final settings added in application.properites file are:


That did it for us, the project was up and running with Spring Boot 2 and Flyway 5. After this point if we wrote new migrations those worked as well.

Sunday, December 29, 2019

How To Stream data to Redshift via Firehose

We had a backend system that would write data to PostgreSQL RDS. I wanted to copy this data to our Redshift cluster too in near realtime manner. There are many ways to copy data from PostgreSQL RDS instance to Redshift. However, most of them are not realtime solutions. Thankfully there exists Amazon Firehose which could be used to stream data to Redshift.

In this post we will see the steps needed to stream data to Redshift via Firehose using a Kotlin application.

The Solution

This post assumes that you already have an Amazon AWS account.
  • Lets start by creating a new Kinesis Data Firehose delivery streams by clicking here. We will be creating this delivery stream in Oregon region. 
  • Enter the name of stream and select source as Direct PUT and other sources and click Next

  • Next, this screen lets you transform the data into a different format. For simplicity we are not going to do any of this and select Disabled for both options.

  • Next step is by far one of the most important step in the process, so pay attention :D
  • There are three important things to configure in this step. 
    • Choose Amazon Redshift and fill in the connection details for your Redshift cluster

    • Next, you need to provide S3 bucket details where the data will be held temporarily. Create an S3 bucket called test-delivery-streams in the same Oregon region which we will use as our temporary S3 location.

    • And finally, the Amazon Redshift COPY command, here you can specify various options for the Redshift COPY command. Since we will be streaming data in JSON format, you need to put format as json 'auto' in the COPY options - optional section. 

    • Right below it will also show the actual COPY command that will be used, please review this and make sure that the COPY options have been added successfully.
    • Click Next and move to the final step
  • In this step only thing we need to change is the IAM role in the Permissions section.

    • Click that button and a new window will popup which will basically create a new IAM role that will have access to various AWS services needed to stream data to Redshift.
    • No need to change anything here, click Allow
    • After this the popup will close and we can click Next.

  • The Final step will show you all the information you have entered. After you are happy with everything shown there, click on Create Delivery Stream.
  • This creates the Delivery Stream however we are missing one final step, i.e. to create the table in Redshift that will hold the streamed data.
  • This table needs to mimic the JSON data that we are going to ingest into the Kinesis stream. For simplicity we will create a table with 2 columns only. Here is the create table script that we will use

  • Finally our delivery stream is ready to stream data to Redshift. 
  • Here is the sample Kotlin code to PUT data into the Kinesis stream which should eventually end up into Redshift table.
  • After invoking this code, the Kinesis stream will push the data to S3 our temporary location.
  • You can view the files in the location and validate that the data has reached S3

  • After about 5 minutes you should be able to see the same data in Redshift cluster too! 

  • If you click on the Kinesis Delivery Stream, it has a nifty Monitoring tab which shows information like how much data is written to S3 and how much data has been written to Redshift and stuff like that.

Saturday, November 30, 2019

How to do iOS Receipt Validation in Objective-C

Until you do it yourself, receipt validation might feel like one of the most unclear topic, when it comes to verifying In App Purchases (IAP) on iOS platform for an App Developer.

This post documents the exact steps and code needed to perform receipt validation with AppStore.

The Receipt

As soon as the app is installed or updated, Apple puts a purchase receipt (signed by Apple via AppStore) in the main bundle of the app.

Think of the receipt as the trusted record of a purchase. It also includes any in-app purchases that the user might have made.

Receipt Validation

By verifying the receipts, App Developers can protect their revenue and enforce their business model directly in their application. Receipt Validation plays a key role in verifying whether the auto-renewing subscription is currently active or not.

Here are the steps needed to validate the receipt.
  • First step in the process is to load the receipt from apps main bundle
  • If the receipt is not found, we could refresh the receipt using SKReceiptRefreshRequest
    • This is especially helpful during development and debugging.
  • Refreshing the receipt tells the system that the application needs to retrieve a new receipt

  • Before we could send the receipt information to AppStore, we will need the App's Shared Secret.
  • To get this, log on to itunesconnect and navigate to My Apps -> Click on your App -> Click on Features 
  • Click on the In-App Purchases section. On the right side you will see the link App-Specific Shared Secret, click this link to generate the App-Specific Shared Secret. It will pop up a dialog where you could generate a new secret or view the existing secret.
  • Note down the generate secret somewhere, we need to pass this value to AppStore for receipt verification.

  • Next, we need send the receipt details along with App-Specific Shared Secret to AppStore. We have to hit different URL based on whether the app is running in the sandbox environment or production environment.
  • Now we need to send the Base 64 encoded receipt information along with App-Specific shared secret to the AppStore API. The response of this API is a JSON object with details about various purchases user has made on the app.
  • Full list of receipt fields found in this JSON response can be found here
  • "latest_receipt_info" field is part of the JSON response. It is an Array containing the details of IAP and Subscription purchases made by the user on the app. 
  • Each purchase holds information like
    • Which product was purchased: "product_id"
    • When was the purchase made: "original_purchase_date"
    • Whether or not the auto-renewing subscription is running the trial period: "is_trial_period"
    • The date of the auto-renewing subscription expires: "expires_date"
    • What was the intent behind the subscription expiration: "expiration_intent"
  • Now all that is left is to parse the JSON response and iterate through the contents of "latest_receipt_info" field. Here's the code that does it.

Thats about all that is needed to perform receipt validation.

However, please note that, since we can't build a trusted connection between a user’s device and the App Store directly, we should should always call AppStore receipt validation API from a trusted server. Details of how to do that, is out of scope of this post.

Thursday, October 31, 2019

How to upgrade PostgreSQL to 11.4 from 10 on MacOS

I recently updated my PostgreSQL to version 11.4 from 10.0. After the upgrade, I realised that I wasn't able to start my PostgreSQL server. It kept giving me the following error:

The data directory was initialized by PostgreSQL version 10.0, which is not compatible with this version 11.4.

I had to follow bunch of steps to get back my old databases to work with PostgreSQL 11.4. This post is an attempt to document those steps for future reference.

The Solution

  • Install the older version of PostgreSQL using the following command
  • Output would be very similar to these messages
  • Unlink the newly installed older version of PostgreSQL. Brew will spit out the message confirming that the unlinking was successful.
  • Link the latest version of PostgreSQL. As before, brew will spit out the message stating that the linking was successful.
  • Move the data directory from default location to another location
  • Use initdb to initialise a new and empty data directory.
  • Output might look somewhat like this
  • Copy over the timezone and timezonesets directory to /usr/local/share/postgresql10
  • Upgrade the data directory using the following command
  • It will do bunch of things and might spit out messages like these
  • Moment of truth, start the PostgreSQL server
  • If everything goes through fine, you should see a message that states that PostgreSQL server was started successfully.
  • Cleanup steps
Thats about it! We have successfully upgraded PostgreSQL to 11.4 from 10.

Sunday, September 29, 2019

How to setup an Alarm when RDS is running on low free disk space

Yea, that happened to me!

The Problem

My RDS instance suddenly ran out of space and some of our applications started failing left, right and centre. It was a disaster and a fair bit of fire fighting was involved.

I told to myself, how did this happen? I should have put checks in place to ensure this didn't happen. I should have added some sort of alarm to warn when free disk space is low.

To deal with this, we wanted to first setup an alarm to notify the team when RDS instance is running on low free disk space. Looked at AWS console to create the alarm, but - I must admit - we were a bit surprised to see that there isn't like a straightforward way to create this type of an alarm.

The Solution

After a little googling, we found the way to setup the Alarm. This post is to document the steps involved in getting this done so that, I do not forget them :D

We basically need to do the following

  • Create an SNS topic to that can send emails
  • Subscribe the team email address to the SNS topic
  • Confirm the email subscription by clicking on the link that AWS sends.
  • Create a cloud watch alarm to send the alert when the RDS free disk space is lesser than the chosen threshold

That's all there is to it!
Have some Fun!