Declares the individual partitions to create. Each partition can be defined individually or, for range partitions, you can use the EVERY clause (with a START and optional END clause) to define an increment pattern to use to create the individual partitions.

DEFAULT PARTITION \<name\> — Declares a default partition. When data does not match to an existing partition, it is inserted into the default partition. Partition designs that do not have a default partition will reject incoming rows that do not match to an existing partition.

PARTITION \<name\> — Declares a name to use for the partition. Partitions are created using the following naming convention: parentname_level#_prt_givenname.

VALUES — For list partitions, defines the value(s) that the partition will contain.

START — For range partitions, defines the starting range value for the partition. By default, start values are INCLUSIVE. For example, if you declared a start date of ’2008-01-01’, then the partition would contain all dates greater than or equal to ’2008-01-01’. Typically the data type of the START expression is the same type as the partition key column. If that is not the case, then you must explicitly cast to the intended data type.

END — For range partitions, defines the ending range value for the partition. By default, end values are EXCLUSIVE. For example, if you declared an end date of ’2008-02-01’, then the partition would contain all dates less than but not equal to ’2008-02-01’. Typically the data type of the END expression is the same type as the partition key column. If that is not the case, then you must explicitly cast to the intended data type.

EVERY — For range partitions, defines how to increment the values from START to END to create individual partitions. Typically the data type of the EVERY expression is the same type as the partition key column. If that is not the case, then you must explicitly cast to the intended data type.

WITH — Sets the table storage options for a partition. For example, you may want older partitions to be append-only tables and newer partitions to be regular heap tables.

TABLESPACE — The name of the tablespace in which the partition is to be created.

SUBPARTITION BY

Declares one or more columns by which to subpartition the first-level partitions of the table. The format of the subpartition specification is similar to that of a partition specification described above.

SUBPARTITION TEMPLATE

Instead of declaring each subpartition definition individually for each partition, you can optionally declare a subpartition template to be used to create the subpartitions. This subpartition specification would then apply to all parent partitions.

Notes

Using OIDs in new applications is not recommended. Avoid assuming that OIDs are unique across tables; if you need a database-wide unique identifier, use the combination of table OID and row OID for the purpose.

Primary key and foreign key constraints are not supported in HAWQ. For inherited tables, table privileges are not inherited in the current implementation.

HAWQ also supports the parquet columnar storage format. Parquet tables can be more efficient for increasing performance on large queries.

Setting Parameters for Parquet Tables

You can set three kinds of parameters for a parquet table.

1. Set the parquet orientation parameter:

   with (appendonly=true, orientation=parquet);

2. Set the compression type parameter. Parquet tables can be compressed using either SNAPPY or GZIP. GZIP supports compression level values between 1 and 9. SNAPPY does not support compression level; providing a compression level when using SNAPPY will cause the create table operation to fail. Specifying a compression level but no compression type when creating a parquet table will default to GZIP compression.

   Note: For best performance with parquet storage, use SNAPPY compression.

3. Set the data storage parameter: By default, the two parameters, PAGESIZE and ROWGROUPSIZE are set to 1MB/8MB for common and partitioned tables.

   Note: The page size should be less than the rowgroup size. This is because rowgroup includes the metadata information of a single page even for a single column table. The parameters PAGESIZE and ROWGROUPSIZE are valid for parquet tables, while BLOCKSIZE is valid for append-only tables

About Parquet Storage

DDL and DML: Most DDL and DML operations are valid for a parquet table. The usage for DDL and DML operations is similar to append-only tables. Valid operations on parquet tables include:

• Parquet table creation (with/without partition, with/without compression type)
• Insert and Select

Compression type and level: You can only set the compression type at the table level. HAWQ does not support setting column level compression. The specified compression type is propagated to the columns. All the columns must have the same compress type and level.

Using SNAPPY compression with parquet files is recommended for best performance.

Data type: HAWQ supports all data types except arrays and user defined types.

Alter table: HAWQ does not support adding a new column to an existing parquet table or dropping a column. You can use ALTER TABLE for a partition operation.

FillFactor/OIDS/Checksum: HAWQ does not support these operations when creating parquet tables. The default value for checksum for a parquet table is false. You cannot set this value or specify fillfactor and oids.

Memory occupation: When inserting or loading data to a parquet table, the whole rowgroup is stored in physical memory until the size exceeds the threshold or the end of the INSERT operation. Once either occurs, the entire rowgroup is flushed to disk. Also, at the beginning of the INSERT operation, each column is pre-allocated a page buffer. The column pre-allocated page buffer size should be min(pageSizeLimit, rowgroupSizeLimit/estimatedColumnWidth/estimatedRecordWidth) for the first rowgroup. For the following rowgroup, it should be min(pageSizeLimit, actualColumnChunkSize in last rowgroup * 1.05), of which 1.05 is the estimated scaling factor. When reading data from a parquet table, the requested columns of the row group are loaded into memory. Memory is allocated 8 MB by default. Ensure that memory occupation does not exceed physical memory when setting ROWGROUPSIZE or PAGESIZE, otherwise you may encounter an out of memory error.

Bulk vs. trickle loads Only bulk loads are recommended for use with parquet tables. Trickle loads can result in bloated footers and larger data files.

Parquet Examples

Parquet Example 1

Create an append-only table using the parquet format:

CREATE TABLE customer ( id integer, fname text, lname text,
    address text, city text, state text, zip text )
WITH (APPENDONLY=true, ORIENTATION=parquet, OIDS=FALSE)
DISTRIBUTED BY (id);

Parquet Example 2

Create a parquet table with twelve monthly partitions:

CREATE TABLE sales (id int, date date, amt decimal(10,2))
WITH (APPENDONLY=true, ORIENTATION=parquet, OIDS=FALSE)
DISTRIBUTED BY (id)
PARTITION BY RANGE (date)
  ( START (date '2016-01-01') INCLUSIVE
    END   (date '2017-01-01') EXCLUSIVE
    EVERY (INTERVAL '1 month')
  );

Parquet Example 3

Add a new partition to the sales table:

ALTER TABLE sales ADD PARTITION
    START (date '2017-01-01') INCLUSIVE
    END (date '2017-02-01') EXCLUSIVE;

AO Examples

Append-only tables support ZLIB and SNAPPY compression types.

AO Example 1: Create a table named rank in the schema named baby and distribute the data using the columns rank, gender, and year:

CREATE TABLE baby.rank ( id int, rank int, year smallint, gender char(1), count int )
DISTRIBUTED BY (rank, gender, year);

AO Example 2: Create table films and table distributors. The first column will be used as the HAWQ distribution key by default:

CREATE TABLE films (
    code char(5), title varchar(40) NOT NULL, did integer NOT NULL,
    date_prod date, kind varchar(10), len interval hour to minute
);
CREATE TABLE distributors (
    did integer,
    name varchar(40) NOT NULL CHECK (name <> '')
);

AO Example 3: Create a snappy-compressed, append-only table:

CREATE TABLE sales (txn_id int, qty int, date date)
WITH (appendonly=true, compresstype=snappy)
DISTRIBUTED BY (txn_id);

AO Example 4: Create a three level partitioned table using subpartition templates and default partitions at each level:

CREATE TABLE sales (id int, year int, month int, day int,
region text)
DISTRIBUTED BY (id)
PARTITION BY RANGE (year)
SUBPARTITION BY RANGE (month)
SUBPARTITION TEMPLATE (
START (1) END (13) EVERY (1),
DEFAULT SUBPARTITION other_months )
SUBPARTITION BY LIST (region)
SUBPARTITION TEMPLATE (
SUBPARTITION usa VALUES ('usa'),
SUBPARTITION europe VALUES ('europe'),
SUBPARTITION asia VALUES ('asia'),
DEFAULT SUBPARTITION other_regions)
( START (2002) END (2010) EVERY (1),
DEFAULT PARTITION outlying_years);

AO Example 5 Create a hash-distributed table named “sales” with 100 buckets.

CREATE TABLE sales(id int, profit float)
WITH (bucketnum=100)
DISTRIBUTED BY (id);

Compatibility

The CREATE TABLE command conforms to the SQL standard, with the following exceptions:

• Temporary Tables — In the SQL standard, temporary tables are defined just once and automatically exist (starting with empty contents) in every session that needs them. HAWQ instead requires each session to issue its own CREATE TEMPORARY TABLE command for each temporary table to be used. This allows different sessions to use the same temporary table name for different purposes, whereas the standard’s approach constrains all instances of a given temporary table name to have the same table structure.

  The standard’s distinction between global and local temporary tables is not in HAWQ. HAWQ will accept the GLOBAL and LOCAL keywords in a temporary table declaration, but they have no effect.

  If the ON COMMIT clause is omitted, the SQL standard specifies that the default behavior as ON COMMIT DELETE ROWS. However, the default behavior in HAWQ is ON COMMIT PRESERVE ROWS. The ON COMMIT DROP option does not exist in the SQL standard.

• Column Check Constraints — The SQL standard says that CHECK column constraints may only refer to the column they apply to; only CHECK table constraints may refer to multiple columns. HAWQ does not enforce this restriction; it treats column and table check constraints alike.

• NULL Constraint — The NULL constraint is a HAWQ extension to the SQL standard that is included for compatibility with some other database systems (and for symmetry with the NOT NULL constraint). Since it is the default for any column, its presence is not required.

• Inheritance — Multiple inheritance via the INHERITS clause is a HAWQ language extension. SQL:1999 and later define single inheritance using a different syntax and different semantics. SQL:1999-style inheritance is not yet supported by HAWQ.

• Partitioning — Table partitioning via the PARTITION BY clause is a HAWQ language extension.

• Zero-column tables — HAWQ allows a table of no columns to be created (for example, CREATE TABLE foo();). This is an extension from the SQL standard, which does not allow zero-column tables. Zero-column tables are not in themselves very useful, but disallowing them creates odd special cases for ALTER TABLE DROP COLUMN, so this spec restriction is ignored.

• WITH clause — The WITH clause is an extension; neither storage parameters nor OIDs are in the standard.

• Tablespaces — The HAWQ concept of tablespaces is not part of the SQL standard. The clauses TABLESPACE and USING INDEX TABLESPACE are extensions.

• Data Distribution — The HAWQ concept of a parallel or distributed database is not part of the SQL standard. The DISTRIBUTED clauses are extensions.

See Also

ALTER TABLE, DROP TABLE, CREATE EXTERNAL TABLE, CREATE TABLE AS