SQL Joins

-
Image
Joins are used to combine rows from two or more tables based on related columns between them. Joins allow you to retrieve data from multiple tables simultaneously, enabling you to create complex queries that fetch data from different sources. There are different types of joins in SQL, including: INNER JOIN Returns only the rows that have matching values in both tables based on the specified join condition. It discards non-matching rows from both tables. Example:           create table t1(x int); insert into t1 values(1); insert into t1 values(1); insert into t1 values(0); create table t2(y int); insert into t2 values(0); insert into t2 values(1); insert into t2 values(1);           select * from t1 inner join t2 on t1.x = t2.y Output: 2. LEFT JOIN (or) LEFT OUTER JOIN Returns all the rows from the left (or first) table and the matching rows from the right (or second) table. If there is no match, NULL values are
Post a Comment

SortKey, DistKey & Compression

-
Sortkeys

Compound Sortkeys


Effecient when used in the where conditions are in same order as specified in the sortkey.
This is the default sortkey .

ex : sortkey (column1, column2)

where column1='somevalue' and column2='somevalue'  give better performance than where column2='somevalue'


Interleaved Sortkeys


Gives equal weightage to all the columns specified in the sortkey. Gives better results than the compound sortkey in the circumstances when cannot able to identity columns used in the where conditions.

ex : interleaved sortkey (column1, column2)

As time grows the data added to the existing table grows , it will degrade the performance on both the compound and interleved sortkeys , but it has greter effect on interleaved key.Need to Vaccum Reindex sort key tables whenever new data added.

http://docs.aws.amazon.com/redshift/latest/dg/r_vacuum-decide-whether-to-reindex.html


Distribution Key


Defines how rows should be stored across the nodes.Usually moving the data between the nodes during joins consumes more time, so we have to more careful in defining distribution keys.
we can define only one distkey per fact table.

if sort and distkey uses the same column , it would help the query optimizer to choose effective MERGE JOIN step instead of other join types.

In some cases we cannot come to the conclusion in identifying the distkey , in that case we can use diststyle even , it would randomly distribute the rows across the nodes.

for the dimension table if data are minimal then use diststyle all, it would copy the entire table across all the nodes , so it would avoid the cost effective DB_BDCAST,DB_DB_INNER.

Compresssion Encoding


Compression techniques helps to reduce the data size in disk , so during the join operation the size of the data moved between nodes will get reduced , it will gradually increase the performance.
We can enable the compression encoding for all the columns in table.
Its better to avoid enabling the encoding for the sort keys , so while processing it has to decode the sort column and where conditions will be applied it will cause performance degrading.
We can enable compression either manually or automatically.
If its manual , then we need to analyze the columns before enabling encoding.

analyze compression tablename

will do this.But with sufficient data it would suggest more accurate encoding type.

The best option is to leave the finding of perfect encoding type to redshift by enabling endoing during copy like

COPY ... COMPUPDATE ON


Comments

Post a Comment

Popular posts from this blog

Machine Learning Foundations - Deep Learning Summary - Quiz

-
1) Which of the following statements are true? (Check all that apply) a) Linear classifiers are never useful, because they cannot represent XOR. b) Linear classifiers are useful, because, with enough data, they can represent anything. c) Having good non-linear features can allow us to learn very accurate linear classifiers. d) none of the above 2) A simple linear classifier can represent which of the following functions? (Check all that apply) Hint: If you are stuck, see https://www.coursera.org/learn/ml-foundations/module/nqC1t/discussions/AAIUurrtEeWGphLhfbPAyQ a) x1 OR x2 OR NOT x3 b) x1 AND x2 AND NOT x3 c) x1 OR (x2 AND NOT x3) d) none of the above 3) Which of the the following neural networks can represent the following function? Select all that apply. (x1 AND x2) OR (NOT x1 AND NOT x2) Hint: If you are stuck, see https://www.coursera.org/learn/ml-foundations/module/nqC1t/discussions/AAIUurrtEeWGphLhfbPAyQ d 4) Which of the following statements is true? (Che
Read more

Machine Learning Foundation - Deep Learning - Programming Assignment

-
1) What’s the least common category in the training data? a) bird b) dog c) cat d) automobile 2) Of the images below, which is the nearest ‘cat’ labeled image in the training data to the the first image in the test data (image_test[0:1])? f 3) Of the images below, which is the nearest ‘dog’ labeled image in the training data to the the first image in the test data (image_test[0:1])? d 4) For the first image in the test data, in what range is the mean distance between this image and its 5 nearest neighbors that were labeled ‘cat’ in the training data? a) 33 to 35 b) 35 to 37 c) 37 to 39 d) 39 to 41 e) Above 41 5) For the first image in the test data, in what range is the mean distance between this image and its 5 nearest neighbors that were labeled ‘dog’ in the training data? a) 33 to 35 b) 35 to 37 c) 37 to 39 d) 39 to 41 e) Above 41 6) On average, is the first image in the test data closer to its 5 nearest neighbors in the ‘cat’ data or in the ‘dog’ dat
Read more

Machine Learning Foundations - Recommender System - Quiz

-
1) Recommending items based on global popularity can (check all that apply): a) provide personalization b) capture context (e.g., time of day) c) none of the above 2) Recommending items using a classification approach can (check all that apply): a) provide personalization b) capture context (e.g., time of day) c) none of the above 3) Recommending items using a simple count based co-occurrence matrix can (check all that apply): a) provide personalization b) capture context (e.g., time of day) c) none of the above 4) Recommending items using featurized matrix factorization can (check all that apply): a) provide personalization b) capture context (e.g., time of day) c) none of the above 5) Normalizing co-occurrence matrices is used primarily to account for: a) people who purchased many items b) items purchased by many people c) eliminating rare products d) none of the above 6) A store has 3 customers and 3 products. Below are the learned feature vectors for
Read more