Teradata Hash Functions

access_time 22 days ago visibility7 comment 0

Teradata hash functions are used to decide data distributions for each row in Teradata tables. When data is inserted into a table, row hash of each record will be calculated first and then the bucket will be calculated, at last the AMP number and fallback AMP number (if fallback is enabled) will be calculated to decide which AMP the data will be stored on. 

HASHROW

Function HASHROW can be used to determine row distributions by the proposed primary index column(s).

SELECT id, HASHROW(id) FROM TestDb.test_table;

Example output:

	id	HASHROW(id)
1 5 60-9D-17-15
2 5 60-9D-17-15
3 3 6D-27-DA-A6
4 1 79-B6-9E-37
5 6 DA-53-B5-4C
6 6 DA-53-B5-4C
7 4 E6-E2-78-DD
8 4 E6-E2-78-DD
9 2 F3-71-3C-6E
For the same value(s), the hash result will be the same. 

HASHBUCKET

Function HASHBUCKET is used to calculate the hash bucket of a hash row value.

SELECT id, HASHROW(id) AS ROW_ID, HASHBUCKET(ROW_ID) AS BUCKET FROM TestDb.test_table;

Example output:

	id	ROW_ID	        BUCKET
1	5	60-9D-17-15	395,729
2	5	60-9D-17-15	395,729
3	3	6D-27-DA-A6	447,101
4	1	79-B6-9E-37	498,537
5	6	DA-53-B5-4C	894,267
6	6	DA-53-B5-4C	894,267
7	4	E6-E2-78-DD	945,703
8	4	E6-E2-78-DD	945,703
9	2	F3-71-3C-6E	997,139
For same hash row value, the bucket will be the same.

HASHAMP

Function HASHAMP is used to decide the AMP number (vproc) of each hash bucket.

SELECT id, HASHROW(id) AS ROW_ID, HASHBUCKET(ROW_ID) AS BUCKET, HASHAMP(BUCKET) AS "AMP#" FROM TestDb.test_table;

Sample output:

	id	ROW_ID	        BUCKET	AMP#
1	1	79-B6-9E-37	498,537	0
2	2	F3-71-3C-6E	997,139	0
3	3	6D-27-DA-A6	447,101	0
4	4	E6-E2-78-DD	945,703	0
5	4	E6-E2-78-DD	945,703	0
6	5	60-9D-17-15	395,729	0
7	5	60-9D-17-15	395,729	0
8	6	DA-53-B5-4C	894,267	0
9	6	DA-53-B5-4C	894,267	0
*Note - there are only two AMPs in the Teradata instance the sample code runs on.

HASHBAKAMP

Function HASHBAKAMP is used to calculate the fallback AMP number of each bucket. When the primary bucket AMP is down, the query can fall back to retrieve data from the fallback AMP. 

SELECT id, HASHROW(id) AS ROW_ID, HASHBUCKET(ROW_ID) AS BUCKET, HASHAMP(BUCKET) AS "AMP#",
HASHBAKAMP(BUCKET) AS "Fallback AMP#"
FROM TestDb.test_table;

Sample output:
	id	ROW_ID	BUCKET	AMP#	Fallback AMP#
1	5	60-9D-17-15	395,729	0	1
2	5	60-9D-17-15	395,729	0	1
3	3	6D-27-DA-A6	447,101	0	1
4	1	79-B6-9E-37	498,537	0	1
5	6	DA-53-B5-4C	894,267	0	1
6	6	DA-53-B5-4C	894,267	0	1
7	4	E6-E2-78-DD	945,703	0	1
8	4	E6-E2-78-DD	945,703	0	1
9	2	F3-71-3C-6E	997,139	0	1

Multiple columns

If there are multiple columns, the sequence of columns are not important. For example, the hash values will be the same for the following two expressions. 

SELECT HASHROW('A','B'), HASHROW('B','A')
copyright This page is subject to Site terms.
Like this article?
Share on

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

Follow Kontext

Get our latest updates on LinkedIn or Twitter.

Want to publish your article on Kontext?

Learn more

More from Kontext

visibility 7
thumb_up 0
access_time 22 days ago

In Teradata, error 'no more room in database' can be common especially when data is not evenly distributed (i.e. high skewness). Sometimes when this error occurs, it might be just because of one or a few AMPs are full. For example, assuming a database with permanent space allocated as 100GB and ...

visibility 494
thumb_up 0
access_time 5 months ago

Teradata Parallel Transporter (TPT) provides rich functions to load data into Teradata and to export data. In article Load CSV into Teradata via TPT , it shows how to load CSV files into Teradata. This page provides examples to export data from Teradata to CSV. Use TPT wizard GUI tool to create a ...

visibility 540
thumb_up 0
access_time 11 months ago

From Teradata 16, LEAD and LAG OLAP functions are supported. The LAG function returns data from a row preceding the current row at a specified offset in a window group; the LEAD function accesses data from a row following the current row at a specified offset in a window group. Table  ...