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.

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.

How to paginate faster in PostgreSQL with big offset values

I was surprised to know, how inefficient pagination could be, when its done with LIMIT and OFFSET.

Everything is good and dandy as long as the OFFSET value is in hundreds and you are dealing with relatively smaller dataset. With huge datasets (5-10 Million+ records) the performance degrades pretty fast as the offset values increase.

The Problem

Offset inefficiency creeps in because of the delay incurred by shifting the results by a large offset. Even in the presence of an index, the database must scan through storage to count rows. To utilise an index we would have to filter a column by a value, but in this case we require a certain number of rows irrespective of their column values.

Moreover Rows could be of different size in the storage and some may be marked for deletion, hence the database cannot use simple arithmetic to find a location on disk to begin reading results.

The Solution

Its best to demonstrate the solution with an example. Lets say for e.g. we have a table called "events" with primary key column "id". We are fetching 30 records per page from it and now want to skip 100000 records and get the next 30 records. The query to do this would look like this

This query would be pretty slow because of reasons mentioned above. To get around this problem we can tweak the query as follows and it should start running faster (unbelievably fast).

Reason for this significant improvement in performance is because of the WHERE clause on "id". Database could go right to the given row and than fetch next 30 records!

There you have it, a simple tweak in query could be the difference between a "crawling query" and a "blazing fast query"!

How to map PostgresSQL JSON column with Hibernate value type and Kotlin

At makkajai, there has been no dearth of challenging problems :). Recently we moved our analytics partner. I will not bore you with details on why we had to move, but what is significantly more interesting is how we executed the move. Some key requirements for the move where:

• Migrate all the data collected by previous analytics partner i.e. around 40 Million events to the new partner.
• Honour concurrency limits of old and new analytics partners. Because if we didn't honour them, they will stop responding for a period of 10 minutes (which would be costly 10 minutes)
• Old analytics partner had a limit of 3 concurrent requests.
• New analytics partner had a limit of sending 1000 events per second. 
• Migration had to be reliable and fault tolerant. For e.g. we could run the migration multiple times during the migration window.

I am not going to go into details of how we solved the whole problem (may be some other time), in this blog I am going to focus on a very small part of the problem. 

The Problem

PostgresSQL JSON column type has great querying features, I wanted to use it to save parts of events JSON response received from our old analytics partner. For this to happen, I needed to map the PostgresSQL JSON column type to Hibernate value type. This blog post is to document the steps needed to achieve this using Kotlin.

The Solution

There are 4 steps involved to make things work.

• Adding a custom PostgreSQL dialect to register the JSON column type with Kotlin String.
• Registering the custom PostgreSQL dialect in application.properties.
• Adding a custom user type class to map kotlin String to PostgreSQL JSON column.
• Annotating the model classes, to use the custom user type class.

Here is the exact code needed to achieve all the 4 steps mentioned above

Above is the custom PostgresSQL Dialect to register the JSON column type with a Kotlin String.

Sample Application properties to register the custom dialect.

Above is the custom user type mapping class. This will be used to map Kotlin String to PostgreSQL JSON column.

Above is the simple UserEvent model class that uses the string property properties and maps it to the PostgreSQL JSON column data type.

Thats about it! When we create the instance of UserEvent class and set the value of properties it will be correctly saved in PostgreSQL JSON column type. PostgreSQL will also validate that its a valid JSON String before saving the information.

How to get Snowplow-Mini running on AWS

While looking at various Analytics engines we came across Snowplow Analytics. We wanted to give it a shot and experience it first hand. Luckily, they have something called as Snowplow-Mini. Its an easily deployable, single instance version of Snowplow. It essentially gives us, a taste of what Snowplow can do for us, as far as data collection, processing and analytics is concerned!

We started with the quick start guide and usage guide, performed all the steps mentioned there to get the Snowplow-Mini instance working. However, we did faced two annoying issues, investigating and fixing them, wasted a few hrs. This post is about those two issues, so that my fellow developers do not have to waste any time on investigating and fixing them.

Unable to: Generate a pair of read/write API keys for the local Iglu schema registry

We followed all steps mentioned in the usage guide but we were unable to generate the keys.

• Navigate to http://<public dns>/iglu-server
• Input the super API key set up in previous step, in the input box in the top right corner
• Expanded the keygen section
• Expanded the POST /api/auth/keygen operation
• Input the appropriate vendor_prefix for this API key
• Click Try it out!

At this, it should have generated the read and write keys for us. But all it did instead was, showed a progress bar and runs forever without return.

Investigating it in Chrome Developer Console revealed that the calls were failing with 401 UnAuthorized. After googling for this error a bit, I found that someone else was also facing a similar problem. Their solution was to do HTTP POST via CURL and that seemed to work. However it didn't work for us either.

I looked around for ways to debug the problem.

• I connected to the Showplow-Mini instance via SSH (refer to AWS documentation on how to do this)
• Checked the config under "snowplow" directory on the instance. Could not spot anything unusual there -- not that I knew much about it anyways :D
• Checked the logs under "/var/logs" directory. Found a few things but could not really solve the problem.
• Connected to PostgreSQL DB on the instance using the following command
• psql --host=localhost --port=5432 --username=snowplow --dbname=iglu
  # Password is "snowplow"
• Ran the query to check the API key
• select * from apikeys;
• What I saw next, made my jaw drop, in disbelief!
• They API key is case-sensitive and the key Snowplow-Mini had saved was all in lowercase, even though when I had given it the key, I had given it in all caps.

• Passing the key in small case and making the following call did result in generating the read/write API keys for local iglu schema registry
• curl http://<IP address of your server>/api/auth/keygen -X POST -H "apikey: <your case sensitive API key>" -d "vendor_prefix=com.makkajai"
• Duh! Yea I know.

• How to connect to PostgreSQL Snowplow-Mini DB, I got to know that from here 

I must have easily wasted an hour trying to fix this problem. I hope others can save that time!

Unable to: See events in Kibana Dashboard

This was a tricky one. After raising sample events, I was unable to see them in Kibana Dashboard. This happens mainly because the "snowplow_stream_enrich" is not able to connect to the "elastic search service".

How Did I figure it out?

• ssh into the Snowplow-Mini instance
• I checked the logs under "/var/logs" directory. 
• The logs seemed to be filled with exceptions like
• Exception in thread “main” java.net.UnknownHostException: ip-xx-xx-xx-xx: ip-xx-xx-xx-xx: unknown
• Googled it a bit, found the solution here 
• Edit the file "/etc/hosts" and add the IP address information in that file as follows.
• sudo vim /etc/hosts 
• xx.xx.xx.xx ip-xx-xx-xx-xx localhost
• xx.xx.xx.xx being the AWS local IP address.
• Save and exit and re-start all services from the Snowplow-Mini console.
• Generate a few events and open Kibana dashboard, and it worked this time!

After these two problems were out of the way, my Snowplow-Mini instance was fully up and running on AWS!