Gotchas with Altering Table Schemas in Redshift

As I learned recently the hard way, simply altering tables in Redshift to perform a simple operation of adding columns doesn't behave the same way in a data warehouse as it does in regular relational databases.

Running the following SQL statement in Redshift actually has a lot more issues than you would expect:

ALTER TABLE rs_table ADD COLUMN new_column;

Despite the simple nature of this statement, many issues begin to emerge.

One of these is table locking. If an application is connecting to it via JDBC, connections would end up being queued because one Redshift cluster only allows 500 concurrent connections as per their online documentation.

Moreover, it was noticed that there were a number of duplicate records when inserts occurred on a matching key instead of updating the existing record or deleting the old record. This was most likely due to the fact that it was in the process of being altered during an insert and the key match could not be found.

This meant that an alternative approach was needed in order to prevent the locking and concurrency.

The following steps were tried and tested, and proved to work well:

• Create the new table with the new schema suffixed with _new
• Copy data from the old table to the new table
• Rename old table suffixed with _old
• Rename new table removing _new suffix

This is done in SQL as follows:

-- Create new table with new schema


CREATE TABLE rs_table_new


(


    id numeric(10),


    name varchar(250),


    date timestamp,


    new_column numeric(10)


)
SORTKEY(dbid, date, clientid);



-- Copy data from old table to new


INSERT INTO rs_table_new


SELECT


    id,


    name,


    date,


    NULL


FROM rs_table;



-- Rename table


ALTER TABLE rs_table RENAME TO rs_table_old;

ALTER TABLE rs_table_new RENAME TO rs_table;

Amazingly, the total execution time for a table with over 10 million records completed in under 2 minutes as opposed to the initial 2 hours.

Don't Always Believe Your Data

As erroneous as that statement might seem, not trusting your data might be the most prudent thing you do. In this new age of data-driven development where data has become a precious commodity, the need to unlock valuable information that businesses hold about customer interactions has become a crucial part of business success, and hence the investment for better ways to store and extract data has evolved over the last few years more than it ever had over the previous two decades.

The options are endless now with experts recommending vast arrays of strategies to tackle your storage and analysis techniques. But it is more than just the sum of all knowledge about these alternatives that makes you a Data Scientist. The reason we store data in the first place is to provide information that can remove ambiguity in our decision making process. That requires a broader outlook towards investigating industry trends and changes in technology on top of our expertise on how well we persist and describe our business data.

There was a time when relational data stores ruled the earth and with them came the ability to analyse large amounts of data using OLAP cubes. But as the need grew for a more efficient Extract Transform Load (ETL) process the limitations became obvious. Analyzing records that were aggregated nightly because there was “too much data” to extract no longer became a legitimate excuse. Businesses want it now, and they want a lot of it.

With that, storage capabilities evolved to scale horizontally with the map-reduce patterns of document stores like Mongo, key-value stores like Riak, graph stores like Neo4j, and columnar stores like Cassandra. Add into the mix the likes of Big Data storage such as Hadoop, and Event Streams for real time processing like kafka and Amazon Kinesis. Although competition is always a welcome dynamic in any industry, with every contender vying for feature capabilities over their competitors, the choice becomes difficult.

Storage is just one part of the puzzle however. Next would come analysis and forecast. Once a business makes sense of the data it holds with a myriad of techniques for analysis, predicting change comes packaged for us with Machine Learning. Apache Spark jumped on this wagon early commercially with its MLlib offering and Google has also been a popular choice with TensorFlow. There is a degree of expertise required in handling these tools including understanding the type of learning you want your machine to perform, whether its supervised learning using labeled datasets (for fraud detection and recommendations), unsupervised and semi-supervised learning with unlabeled datasets (for image and voice recognition), or reinforcement learning (for artificial intelligence).

There is more to it than knowing your alternatives though. Your data can only tell you as much information as your business keeps about itself, and so Data Science is not just about how to unlock your data with the available tools, its about causation. It's about why.

Identifying peaks or troughs in a graph might help you determine with a guarantee that your tools can help you see patterns or changes, but not the reasons why these patterns and changes exist. And thus starts the real investigation.

This was a lesson that Andrea Burbank from Pinterest learnt as she explained at the YOW! Conference held in Sydney late 2016, and she was kind enough to share her challenges in trying to successfully formulate behavioral trends from Pinterest's massive data storage. The results of her findings were very interesting.

By measuring "daily active users", Pinterest were able to determine which unique customers engaged on their site. Using kafka, they were able to stream logs of response data that told the user's story, but that was just basic counting.

In late October of 2013, they found a sudden step change in growth rate specifically with iPhone users, which seemed likely to be the result of Pinterest being featured in the App Store. What they found though was that it was merely a coincidence because not long before a new feature was introduced to iOS 7 called "Background App Refresh" to prevent apps hanging in suspended animation.

Burbank realized that the statistics they generated weren't telling the full story and after digging further found that one of their endpoints, particularly the home feed endpoint, was giving unnatural background hits even when the app wasn't appearing in the multitask tray, and that obviously had no correlation to daily active users.

This lead to what Burbank defined as the "Spectrum of Certainty" where correlation is found at one end of the spectrum while causation is found at the other. Most of the time Data Scientists believe they are closer to the causation end of the spectrum, when in actual fact they sit very close to the correlation side.

The goal is to ensure data analysis moves towards causal inference, in effect requiring much more investigative techniques about events that occur outside the business domain which may have a strong impact on how data is represented within the business itself. Only then can you truly believe your data to a certain degree.

Reference: Data science as software, Andrea Burbank, Pinterest, YOW! Conference 2016 Sydney

INNER JOIN vs WHERE EXISTS Sub-Queries in MSSQL

Currently working at WiseTech, I've learn't a great deal about the pros and cons of monolithic applications, and more so about why microservices have been a massive talking point in the industry today. There are many specialists in the technology stack here and recently I got into a discussion about SQL performance tuning.

For a long time I always thought there was a standard golden rule when it came to arguing against sub-queries, and it was almost as sacrilege as cursors, however there have been a few circumstances where the difference may actually vary very little.

Here is a very simple statement that contains one INNER JOIN:

SELECT *

FROM StmNote

INNER JOIN JobDeclaration ON
JE_PK = ST_ParentID

WHERE ST_Description = 'Justification Note'

       AND
ST_Table <> 'JobDeclaration';

And below is an identical query that returns the same results, only with the difference being that it contains a WHERE EXISTS sub-query clause instead of an INNER JOIN:

SELECT *

FROM StmNote

WHERE ST_Description = 'Justification Note'

       AND
ST_Table <> 'JobDeclaration'

       AND EXISTS (SELECT 1 FROM JobDeclaration WHERE
JE_PK = ST_ParentID);

The below execution plan proves there is absolutely no difference in the decision path that the query optimizer makes.

This could easily make one argue that the difference in both statements would simply come down to a matter of preference.

However as we know the query optimizer behaves very differently when considering the number of rows involved in an index-seek/table-scan and the statistics it has to work with in determining and efficient execution path.

So what if there were other joins or larger result sets which could ultimately mean more variations in the execution path for query optimizer?

Below is a more complex scenario involving multiple joins that in the first case uses the following query:

SELECT bna.* 

FROM BmncnAttachment bna

INNER JOIN BmncnShape bns

       ON bns.BNS_PK = bna.BNA_BNS_FromShape OR
bns.BNS_PK =
bna.BNA_BNS_ToShape OR
bns.BNS_PK =
bna.BNA_BNS_Owner

INNER JOIN ProcessHeader fh

       ON bns.BNS_FH_ProcessHeader =
fh.FH_PK

INNER JOIN WorkItem wki

       ON wki.WKI_PK IS NOT NULL

WHERE wki.WKI_PK = fh.FH_ParentId

       AND wki.WKI_Summary LIKE 'Issue %'

       AND wki.WKI_Status IN ('ASN', 'OPN', '');

While the second case uses the following query with a WHERE EXISTS sub-query clause:

SELECT bna.* 

FROM BmncnAttachment bna

INNER JOIN BmncnShape bns

       ON bns.BNS_PK = bna.BNA_BNS_FromShape OR
bns.BNS_PK =
bna.BNA_BNS_ToShape OR
bns.BNS_PK =
bna.BNA_BNS_Owner

INNER JOIN WorkItem wki

       ON wki.WKI_PK IS NOT NULL

WHERE wki.WKI_Summary LIKE 'Issue %'

       AND wki.WKI_Status IN ('ASN', 'OPN', '')

       AND EXISTS (SELECT 1 FROM ProcessHeader fh WHERE
bns.BNS_FH_ProcessHeader = fh.FH_PK AND wki.WKI_PK = fh.FH_ParentId);

And here is the varying execution plans for both:

It seems they differ significantly on the final Hash Matching where it is probing the hash key fh.FH_ParentId and taking 62% or the total time longer than the standard inner join. The is mainly because you lose parallelism when using the sub-query.

So basically to sum it all up, even though the first statement may show identical results, performance issues would exists if there were multiple joins, because the optimizer could choose the sub-query over another join which would otherwise have been more efficient.

Conversely however, performance could be effected for joins that return duplicate rows and that may be another variable to consider when making the decision between WHERE EXIST sub-query clauses and JOINS, and is definitely something to keep in mind when making the final decision on what type of statement to use.