15.00 - Cogroups - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)
Last Update
2018-09-24

Cogroups

Cogroups are used with protected and non-protected C/C++ and Java table operators.

Cogroups are used for table operators that contain multiple ON clauses. By default, the input from the ON clauses is pooled together into one group as long as the input has the same value as the partition key. Input that does not qualify is ignored to ensure that the results make sense.

For information about the use of multiple ON clauses, see SQL Data Manipulation Language.

The cogroup feature is on by default, but if necessary, you can turn off this functionality by calling FNC_TblOpDisableCoGroup() in the contract function.

For information about FNC calls, see SQL External Routine Programming.

Example : Cogroup Used for a Table Operator with Multiple Inputs

This example demonstrates the grouping operation used with multiple inputs to a table operator, called a cogroup. All of the inputs that satisfy the condition are grouped together in a single group. Cogroup matches the partition keys of multiple inputs, thus ensuring that the results are correct. Using a cogroup enhances the ability of table operators to work with multiple input streams and dimension tables.

Suppose that you have the following two tables:

 
 

You run the following query:

 SELECT cookie, cart_amt, adname, action 
     FROM attribute_sales (
       ON (SELECT cookie,cart_amt FROM weblog 
           WHERE page = 'thankyou' ) as W PARTITION BY cookie 
       ON adlog as S PARTITION BY cookie) as result1 ;

The inputs are grouped together similar to the following table:

 

Grouped and Nested Relations With the Cogroup on Cookie

Cookie

WebLog

AdLog

AAAA

AAAA,$60, thankyou

AAAA,$140,thankyou

AAAA,champs,impression

AAAA,puppies,click

BBBB

BBBB,$100,thankyou

BBBB,apples,click

CCCC

CCCC,$200,thankyou

CCCC,baseball,impression

CCCC,apples,click

DDDD

DDDD,$100,thankyou

 

The output of the query is similar to the following:

 

adname

attr_revenue

champs

$40

puppies

$160

apples

$240

baseball

$40

Each time you invoke the table operator, only the inputs that have the same partition key values participate in the invocation.

If there are no rows of the PARTITION BY ANY and PARTITION BY key inputs on an AMP and the query involves DIMENSION input, the table operator is not invoked even though the DIMENSION table has rows on that AMP.