CREATE TABLE Syntax

SQL Statements

CREATE [<table_type>] TABLE <table_name> <table_contents_source>

[<logging_option> | <auto_merge_option> | <partition_clause> | <location_clause>]

Syntax Elements

<table_name> ::= [<schema_name>.]<identifier>

For descriptions on schema name and identifer, please refer to sql_identifier.

table_type:

<table_type> ::= COLUMN | ROW

| HISTORY COLUMN | GLOBAL TEMPORARY | LOCAL TEMPORARY

ROW, COLUMN

If the majority of access is through a large number of tuples but with only a few selected attributes, COLUMN-based storage should be used. If the majority of access involves selecting a few records with all attributes selected, ROW-based storage is preferable. The SAP HANA Database uses a combination to enable storage and interpretation in both forms. You can define the type of organization for each table. The default value is ROW.

HISTORY COLUMN

Creates a table with a particular transaction session type called 'HISTORY'. Tables with session type HISTORY support time travel; the execution of queries against historic states of the database is possible.

Timetravel can be done in the following ways.

Session-level timetravel:

SET HISTORY SESSION TO UTCTIMESTAMP = <timestamp>

SET HISTORY SESSION TO COMMIT ID = <commitid>

A database session can be set back to a certain point-in-time. The COMMIT ID variant of the statement takes a commitid as a parameter. The value of the commitid parameter must occur in COMMIT_ID column of the system table SYS.TRANSACTION_HISTORY, otherwise an exception will be thrown. The COMMIT ID is useful when using user defined snapshots. A user defined snapshot can be taken by simply storing the commitid which is assigned to a transaction during the commit phase. The commitid can be retrieved by executing the following query directly after a transaction commit:

SELECT LAST_COMMIT_ID FROM M_TRANSACTIONS WHERE CONNECTION_ID = CURRENT_CONNECTION;

The TIMESTAMP-variant of the statement takes a timestamp as parameter. Internally, the timestamp is used to look up a (commit_time,commit_id)-pair inside the system-table SYS.TRANSACTION_HISTORY where the commit_time is close to the given timestamp (to be more precisely: choose pair where maximal COMMIT_TIME is smaller or equal to the given timestamp; if no such pair is found an exception will be raised). The session then will be restored with the determined commit-id as in the COMMIT ID variant.

To terminate a restored session to switch back to the current session, an explicit COMMIT or ROLLBACK has to be executed on the DB connection.

Statement-level timetravel:

<subquery> AS OF UTCTIMESTAMP <timestamp yyyy:mm:dd hh:mm:ss>

<subquery> AS OF COMMIT ID <commitid>

In order to be able to relate the commitid with the commit time, a system table SYS.TRANSACTION_HISTORY is maintained which stores additional information for each transaction which commits data for history table.

For detailed information on setting session-level timetravel, please refer to SET HISTORY SESSION, and for

For detailed information on setting session-level timetravel, please refer to SET HISTORY SESSION, and for details on <subquery>, please refer to sql_command_subquery.

Note:

. Autocommit has to be turned off when a session should be restored (otherwise an exception will be thrown with an appropriate error message)

. Non-history tables in restored sessions always show their current snapshot . Only data query statement (select) is allowed inside restored sessions.

. A history table must have a primary key

. The session type can be checked from the column, SESSION_TYPE of the system table SYS.TABLES.

GLOBAL TEMPORARY

Table definition is globally available while data is visible only to the current session. The table is truncated at the end of the session.

Metadata in a global temporary table is persistent meaning the metadata exists until the table is dropped and the metadata is shared across sessions. Data in a global temporary table is session-specific meaning only the owner session of the global temporary table is allowed to insert/read/truncate the data, exists for the duration of the session and data from the global temporary table is automatically dropped when the session is terminated. Global temporary table can be dropped only when the table does not have any record in it.

Supported operations on Global Temporary Table:

1. Create without a primary key 2. Rename table

3. Rename column 4. Truncate 5. Drop

6. Create or Drop view on top of global temporary table 7. Create synonym

8. Select

9. Select into or Insert 10. Delete

11. Update 12. Upsert or Replace LOCAL TEMPORARY

The table definition and data is visible only to the current session. The table is truncated at the end of the session.

Metadata exists for the duration of the session and is session-specific meaning only the owner session of the local temporary table is allowed to see. Data in a local temporary table is session-specific meaning only the owner session of the local temporary table is allowed to insert/read/truncate the data, exists for the duration of the session and data from the local temporary table is automatically dropped when the session is terminated.

Supported operations on Global Temporary Table:

1. Create without a primary key 2. Truncate

3. Drop 4. Select

5. Select into or Insert 6. Delete

7. Update 8. Upsert or Replace table_contents_source:

<table_contents_source> ::= (<table_element>, ...)

| <like_table_clause> [WITH [NO] DATA]

| [(<column_name>, ...)] <as_table_subquery> [WITH [NO] D ATA]]

<table_element> ::= <column_definition> [<column_constraint>]

| <table_constraint> (<column_name>, ... )

<column_definition> ::= <column_name> <data_type> [<column_store_data_type>

] [<ddic_data_type>] [DEFAULT <default_value>] [GENERATED ALWAYS AS <expression>]

<column_name> ::= <identifier>

DEFAULT

Default specifies a value to be assigned to the column if an INSERT statement does not provide a value for the column.

DATA TYPE in column definition

Available column store data types are CS_ALPHANUM, CS_INT, CS_FIXED, CS_FLOAT, CS_DOUBLE, CS_DECIMAL_FLOAT, CS_FIXED(p-s,s), CS_SDFLOAT, CS_STRING, CS_UNITEDECFLOAT, CS_DATE, CS_TIME,

CS_FIXEDSTRING, CS_RAW, CS_DAYDATE, CS_SECONDTIME, CS_LONGDATE, and CS_SECONDDATE.

Available DDIC data types are DDIC_ACCP, DDIC_ALNM, DDIC_CHAR, DDIC_CDAY, DDIC_CLNT, DDIC_CUKY, DDIC_CURR, DDIC_D16D, DDIC_D34D, DDIC_D16R, DDIC_D34R, DDIC_D16S, DDIC_D34S, DDIC_DATS, DDIC_DAY, DDIC_DEC, DDIC_FLTP, DDIC_GUID, DDIC_INT1, DDIC_INT2, DDIC_INT4, DDIC_INT8, DDIC_LANG, DDIC_LCHR, DDIC_MIN, DDIC_MON, DDIC_LRAW, DDIC_NUMC, DDIC_PREC, DDIC_QUAN, DDIC_RAW, DDIC_RSTR, DDIC_SEC, DDIC_SRST, DDIC_SSTR, DDIC_STRG, DDIC_STXT, DDIC_TIMS, DDIC_UNIT, DDIC_UTCM, DDIC_UTCL, DDIC_UTCS, DDIC_TEXT, DDIC_VARC, DDIC_WEEK.

GENERATED ALWAYS AS

Specifies the expression to generate the column value in runtime.

<column_constraint> ::= NULL | NOT NULL

| UNIQUE [BTREE | CPBTREE]

| PRIMARY KEY [BTREE | CPBTREE]

NULL | NOT NULL

The NOT NULL constraint prohibits a column value from being NULL.

If NULL is specified it is not considered a constraint, it represents a column that may contain a null value. The default is NULL.

UNIQUE

Specifies a column as a unique key.

A composite unique key enables the specification of multiple columns as a unique key. With a unique constraint, multiple rows cannot have the same value in the same column.

PRIMARY KEY

A primary key constraint is a combination of a NOT NULL constraint and a UNIQUE constraint. It prohibits multiple rows from having the same value in the same column.

BTREE | CPBTREE

Specifies the index type. When column data types are character string types, binary string types, decimal types, or when the constraint is a composite key, or non-unique constraint, the default index type is CPBTREE. Otherwise, BTREE is used.

BTREE keyword has to be used in order to use B+-tree index and the CPBTREE keyword has to be used for the CPB+-tree index.

If the index type is omitted, the SAP HANA Database chooses the appropriate index considering the column data types.

<table_constraint> ::= UNIQUE [BTREE | CPBTREE]

| PRIMARY KEY [BTREE | CPBTREE]

This defines a table constraint which can be used on one or more columns of a table. There are two kinds of a table constraint. They are:

UNIQUE

Specifies a uniqueness constraint for a column. This prevents multiple rows from having the same values in the same column list.

PRIMARY KEY

A primary key constraint is a combination of the NOT NULL and UNIQUE constraints. It creates a unique column that can be always be used to locate rows uniquely within a table.

BTREE | CPBTREE

BTREE keyword has to be used in order to use the B+-tree index and the CPBTREE keyword has to be used for the CPB+-tree index.

If the index type is omitted, the SAP HANA Database chooses the appropriate index considering the column data types.

<like_table_clause> ::= LIKE <like_table_name>

<like_table_name> ::= <table_name>

Creates a table that has the same definition as like_table_name. All the column definitions with constraints and default values are copied from like_table_name. Data is filled from the specified table when WITH DATA option is provided, however, the default value is WITH NO DATA.

<as_table_subquery> ::= AS (<sql_select_query>)

Creates a table and fills it with the data computed by the <sql_select_query>. Only NOT NULL constraints are copied by this clause. If column_names are specified, specified column_names override the column names from <sql_select_query>. The default value is WITH DATA.

WITH [NO] DATA Specifies whether the data is copied from <sql_select_query> or <like_table_clause>.

<logging_option> ::= LOGGING | NO LOGGING [RETENTION retention_period]

LOGGING | NO LOGGING

LOGGING (default value) specifies that table logging is activated.

NO LOGGING specifies that logging is deactivated. A NO LOGGING table means that the definition of the table is persistent and globally available, data is temporary and global.

RETENTION

Specifies the retention time in seconds of the column table created by NOLOGGING. After the specified retention period has elapsed, the table will be dropped if used physical memory of the host reaches above 80%.

<auto_merge_option> ::= AUTO MERGE | NO AUTO MERGE

AUTO MERGE | NO AUTO MERGE

AUTO MERGE (default value) specifies that automatic delta merge is triggered by memwacher.

<partition_clause> ::= PARTITION BY <hash_partition> [, <range_partition> | , <hash _partition>]

| PARTITION BY <range_partition>

| PARTITION BY <roundrobin_partition> [,<range_partition>]

<hash_partition> ::= HASH (<partition_expression> [, ...]) PARTITIONS {<num_par titions> | GET_NUM_SERVERS()}

<range_partition> ::= RANGE (<partition_expression>) (<range_spec>, ...) <roundrobin_partition> ::= ROUNDROBIN PARTITIONS {<num_partitions> | GET_NUM_SE RVERS()} [, <range_partition>]

<range_spec> ::= {<from_to_spec> | <single_spec>} [, ...] [, PARTITION OTHE RS]

<from_to_spec> ::= PARTITION lower_value <= VALUES < upper_value <single_spec> ::= PARTITION VALUE single_value

<partition_expression> ::= <column_name> | YEAR(<column_name>) | MONTH(<col umn_name>)

It is possible to determine the index servers on which the partitions are created. If you specify the LOCATION, the partitions will be created on these instances using round robin. Duplicates in the list will be removed. If you specify exactly the same number of instances as partitions in the partition specification, then each partition will be assigned to the respective instance in the list. All index servers in the list have to belong to the same instance.

If no locations are specified, the partitions will be created randomly. If the number of partitions matches the number of servers - for example by using GET_NUM_SERVERS() - it is ensured that multiple CREATE TABLE calls distribute the partitions in the same way. In case of a multi-level partitioning, this applies for the number of partitions of the first level. This mechanism is useful if several tables are to be created which have a semantic relation to each other.

<location_clause> ::= AT [LOCATION] {'host:port' | ('host:port', ...)}

AT LOCATION

A table can be created in the specified location with host:port. Location list can be specified when creating partitioned tables that are distributed on multiple instances. When location list is provided without

<partition_clause>, the table is created on the first location specified.

If location information is not provided, the table will be automatically assigned to one node. This option can be used for both row store and column store tables in a distributed environment.

Description

The CREATE TABLE statement creates a table. Tables are created without data except when

<as_table_subquery> or <like_table_clause> is used with the WITH DATA option.

Example

CREATE TABLE A (A INT PRIMARY KEY, B INT);

CREATE COLUMN TABLE P1 (U DATE PRIMARY KEY) PARTITION BY RANGE (U) (PARTITION '2010 -02-03' <= VALUES < '2011-01-01', PARTITION VALUE = '2011-05-01');

CREATE COLUMN TABLE P2 (I INT, J INT, K INT, PRIMARY KEY(I, J)) PARTITION BY HASH ( I, J) PARTITIONS 2, HASH (K) PARTITIONS 2;

CREATE COLUMN TABLE C1 LIKE A WITH DATA;

CREATE TABLE C2 AS (SELECT * FROM A) WITH NO DATA;

CREATE TRIGGER

In document hana_sql_en (Page 76-80)