DDL Clauses
TDV supports the following DDL Clauses for certain data sources such as Vertica, Teradata and ComputeDB. Refer to the datasource specific documentation for details about the semantics and usage of these DDL clauses.
BROADCAST
Specifying the BROADCAST clause in the DDL will replicate the table across all nodes in the cluster.
Note: TDV currently supports this DDL clause for Vertica.
Syntax
CREATE TABLE database_name.table_name
(column1 data_type,
column2 data_type,
column3 data_type,
...)
BROADCAST;
Example
CREATE TABLE /shared/test/myorder
(order_id INTEGER,
order_name CHAR(25),
order_date DATE,
reorder_lvl INTEGER)
BROADCAST;
The above DDL creates a table “myorder” in the specified location and this table is replicated across all the nodes in the cluster.
PARTITION BY
Specifying the PARTITION BY clause restricts the table data storage in the partition specified in the clause. Note that this clause is mutually exclusive to the BROADCAST clause.
Note: TDV currently supports this DDL clause for Vertica and ComputeDB.
Syntax
CREATE TABLE database_name.table_name
(column1 data_type,
column2 data_type,
column3 data_type,
...)
PARTITION BY column_name1(, column2);
Example
CREATE TABLE /shared/test/myorder
(order_id INTEGER,
order_name CHAR(25),
order_date DATE,
reorder_lvl INTEGER)
PARTITION BY order_id;
CLUSTER BY
Specifying the CLUSTER BY clause in the DDL will group the data according to the column specified in the CLUSTER BY clause.
Note: TDV currently supports this DDL clause for Vertica.
Syntax
CREATE TABLE database_name.table_name
(column1 data_type,
column2 data_type,
column3 data_type,
...)
CLUSTER BY(column_column);
Example
CREATE TABLE /shared/test/myorder
(order_id INTEGER,
order_name CHAR(25),
order_date DATE,
reorder_lvl INTEGER)
CLUSTER BY (order_id);
In the above example, a table “myorder” is created in the specified location. The dataset is divided into clusters of the column order_id. Specifying CLUSTER BY clause helps improve query performance.
ORDER BY
Indicating the ORDER BY clause in the DDL will order and group the data according to the column specified in the ORDER BY clause.
Note: TDV currently supports this DDL clause for Vertica.
Syntax
CREATE TABLE database_name.table_name
(column1 data_type,
column2 data_type,
column3 data_type,
...)
ORDER BY(column_column);
Example
CREATE TABLE /shared/test/myorder
(order_id INTEGER,
order_name CHAR(25),
order_date DATE,
reorder_lvl INTEGER)
ORDER BY (order_id);
In the above example, a table “myorder” is created in the specified location. The dataset is ordered by the column order_id. Specifying ORDER BY clause improves query performance.
[UNIQUE|NO] PRIMARY INDEX
Use this clause to specify the primary index. A table can have no more than one primary index. If you do not explicitly assign a primary index, TDV will choose a default primary index (unless you specify NO INDEX).
Note: TDV currently supports this DDL clause for Teradata.
Syntax
CREATE TABLE database_name.table_name
(column1 data_type,
column2 data_type,
column3 data_type,
...)
UNIQUE PRIMARY|NO INDEX (primary_index_column);
Example
CREATE TABLE /shared/test/myorder
(order_id INTEGER,
order_name CHAR(25),
order_date DATE,
reorder_lvl INTEGER)
UNIQUE PRIMARY INDEX (order_id);
The above example creates a table called “myorder” in the folder “/shared/test” with a primary index of order_id.