Data Compare and Update using SQL Server Database Project

Synchronizing two database is a common scenario but the task sometimes take so much time and effort to synchronize two databases perfectly. Some developers maintain script to sync the databases and runs those script time to time. I found it works well but maintain the script properly is a difficult job if more than one developer works in a single database. In this blog post we will discuss how we can sync two databases using SQL Server Database Project, its advantages and disadvantages.

Redshift: Upload Data to Redshift from SQL Server using Python Script

Download Executable Attachment

Sometimes we need to upload data to Redshift from SQL Server. But there is no straightforward way to do that. And we have to do the following task manually to import the data to redshift.

1. Export data from SQL Server to a text file.
2. Compressed the file to GZIP.
3. Upload the file to S3.
4. Execute copy command in redshift.

SSIS: Perform upsert (Update/Insert) using SSIS Package

It is a common scenario, when we need a script, package or something else to perform insert and update in a table simultaneously. Sometimes the task seems easy and sometimes it takes so much time to perfectly synchronize the databases. In this blog post we will try to create a SSIS package that performs the synchronization task by performing upsert (Insert/Update) using SSIS lookup transformation.

Traditional (Row-Oriented) Database Vs Columnar Database

What is the real difference of columnar database over traditional (row-oriented) database? 

Columnar database is a database system that stores data in a column-wise fashion while in traditional database data has stored in row-wise fashion. Let’s see an example so that we can visualize how data will be stored for these two approaches. Below image shows our example record set.

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;

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.

Redshift: Some Interesting behaviour of Redshift Query

I have found some interesting behaviour of redshift query. It is interesting in a sense that the query runs perfectly in redshift but not in other database (SQL Server or PostgreSQL). Let’s see the behaviour. First one describes in http://www.sqlhaven.com/interesting-aggregate-query-result-using-clause-redshift/ this blog post by brother Khorshed Alam.