Interview Questions   Tutorials   Discussions   Programs   

Teradata - How to find AMP Number using Row-Hash value?




1249
views
asked vishnoiprem August 17, 2014 04:53 AM  

How to find AMP Number using Row-Hash value?


           

1 Answers



 
answered By vishnoiprem   0  
here are three hash-related functions:

- HASHROW to get the hash

- HASHBUCKET to get the first 16/20-bits as an Integer, the so-called hash bucket number

- HASHAMP to get the AMP on which a row is stored (i.e. a number between 0 and the number of AMPs on your system - 1)

SELECT HASHROW(55) AS hr, HASHBUCKET(hr) AS hb, HASHAMP(hb);

HASHROW/HASHBUCKET return the same values on every TD system using the same hash-algorithm, but HASHAMP returns an AMP-number specific to your system.

 So AMP 4 is just an example, on your system it will probably be a different number



 SELECT HASHAMP (HASHBUCKET (HASHROW ())) AS “AMP#”, COUNT (*) FROM GROUP BY 1 ORDER BY 2 DESC;

There are HASHROW, HASHBUCKET, HASHAMP and HASHBAKAMP.
The SQL hash functions are:

  * HASHROW (column(s))
  * HASHBUCKET (hashrow)
  * HASHAMP (hashbucket)
  * HASHBAKAMP (hashbucket)

Example:
SELECT
      HASHROW ('Teradata')  AS "Hash Value"
      , HASHBUCKET (HASHROW ('Teradata')) AS "Bucket Num"
      , HASHAMP (HASHBUCKET (HASHROW ('Teradata'))) AS "AMP Num"
      , HASHBAKAMP (HASHBUCKET (HASHROW ('Teradata'))) AS "AMP Fallback Num" ;

This is really good, by looking into the result set of above written query you can easily find out the Data Distribution across all AMPs in your system and further you can easily identify un-even data distribution

flag   
   add comment

Your answer

Join with account you already have

FF

Preview


Ready to start your tutorial with us? That's great! Send us an email and we will get back to you as soon as possible!

Alert