CREATE TABLE

Owned by Boaz Raufman
Feb 26, 2019
2 min read

Creates a new table. 

Syntax

CREATE TABLE [schema_name.]table_name 
(
column_name <data_type> [PRIMARY KEY] [,...]
)
[PARTITION BY RANGE(column_name) EVERY (<part_interval>)]

<Data_type> ::=
{
INT | BIGINT | FLOAT | DOUBLE | STRING | TIMESTAMP
}

<part_interval> ::=
{
n      | 
VALUE  | 
INTERVAL 'n' {month | day | hour}
}


CREATE TABLE [schema_name.]table_name MERGE FROM [schema_name.]source-table1, [schema_name.]source-table2[,...]

Description

Create a new table object. Create table statement supports creation of a new empty table or merge of existing source tables into a new table.

Table name and column names must be a valid identifier. For more details on Jethro data types attributes, see Data Types. A table can have one primary key column. Primary keys must contain unique values and cannot contain NULL values.

Clauses Details
ClauseMandatory?Details
PARTITION BY

OPTIONAL

The clause defines range partitioning.

Unlike Hive and Impala, in Jethro the partition column name must be one of the columns previously defined in the table column list.

Partitioning interval specification depends on the data type of the partitioning column:

  • For numeric partition key - use EVERY (#n). 
    • For example: PARTITION BY RANGE(store_sk) EVERY (10)
  • For string partition key - use EVERY (VALUE) without replacing the 'value' with anything. The result would be that each distinct string will have its own partition. 
    • For example: PARTITION BY RANGE(store_name) EVERY (VALUE)
  • For timestamp partition key - use EVERY (INTERVAL 'n'...) 
    • For example: PARTITION BY RANGE(sale_date) EVERY (INTERVAL '3' MONTH)

*The valid range for 'n' is: 0<n<(2^32-1), integer values only.

MERGE FROM

OPTIONAL

The clause declare vertical merge of two or more tables. The newly created merged target table table will hold the columns from all it's source tables. Target table column names are generated from source tables columns


Table merge constraints:


  • All source tables should have identical number of rows
  • Partition column name and type as well as partitions size should be identical for all source tables
  • Column names in all merged table must be unique (expect partition column)
  • User executing the command should have SELECT permission to the source tables, and ALL+INSERT permissions to the target schema.

Important note: during table merge process source tables are eliminated.


Example:

CREATE TABLE NEW_TABLE MERGE FROM T1, T2;

See Also

CREATE EXTERNAL TABLE

DROP TABLE

ALTER TABLE