Tuesday, July 14, 2015

Redshift: How to alter column datatype in redshift

There is no direct way to change the column datatype in redshift. If we want to change the column name we can use redshift alter statement with rename keyword like,

alter table BDPlayers rename column category to grade;

Thursday, July 2, 2015

Redshift: Store and Querying JSON object in Redshift

Problem: How to store JSON objects in redshift and also apply filter, group by, order by etc on that object?

Solution: Suppose we have a sparse table, where we need to add many columns and it could be exceed the maximum column limit supported by redshift. In that situation we can store the data for a row in key:value pairs in a single JSON string. JSON uses UTF-8 encoded text string so we can store JSON string in redshift text or varchar type column. For demonstration purpose we have created a table and insert some dummy JSON object. Below is the script.