Query Ocient
General SQL Syntax

SQL Syntax Conventions

Syntax Conventions

The documentation uses the following conventions for defining SQL statements in syntax blocks, unless otherwise noted.

Syntax symbol

Definition

Example

KEYWORD

Command names, command option names, stored procedure names, utility names, utility flags, and other SQL keywords are in uppercase.

SELECT * FROM <my_table> LIMIT 100;

SELECT, FROM, and LIMIT are all SQL keywords. For that reason, they are in uppercase.

{}

Curly braces indicate that you must choose at least one of the enclosed options.

Do not include the curly braces in your SQL statement.

INSERT INTO table_name [( col1 [, col2 ] [,...] ) ] { <select_statement> | VALUES ( [ val1 [, val2] [,...] ) }

This syntax example has two options to define what data to add using INSERT INTO: either provide specific values, or use an embedded SELECT query. These options are enclosed in curly braces because the user must use one of these options to successfully run an INSERT INTO command.

[] 

Brackets mean the enclosed functionality is optional, not necessary to run the specified command. Do not include the brackets in your SQL query. 

When used with a list of options, the brackets mean that you can choose one of the listed options.

CREATE CLUSTER [ IF NOT EXISTS ] cluster_name ( <create_definition> [, ...] )

This syntax example encloses IF NOT EXISTS in brackets because it is optional for running the CREATE CLUSTER command.





| 

The vertical pipe acts as a delimiter for option lists, meaning you can select only one of the options. 

EXPLAIN [ PROTO | JSON | DEBUG ] <query>

This example shows three options that the EXPLAIN command can use. The vertical pipe indicates that each option is exclusive, meaning only one can be used.

[ , ... ]

The ellipsis means you can choose to add as many of the indicated options or parameters as you like, separating your choices with commas to be typed as part of the command. 

INSERT INTO <table_name> [ ( <column_name> [, ...] ) ] <query>

The INSERT INTO statement uses the ellipsis [, ...] to indicate that an infinite number of additional <column_name> values can be specified.

<syntax_subsection>

For complex syntax blocks, portions are set apart with angle brackets <> to denote there is a separate subsection for a particular portion of the syntax.

Syntax blocks with these subsections usually include complicated options that might distract for the core functionality of a command’s syntax.



Syntax subsections' names are written in lowercase with underscores between the words.



In the syntax block the ::= symbol signifies the subsection.

CREATE TABLE [ IF NOT EXISTS | OR REPLACE ] table_name [ (<create_definition> [, ...] ) ] [ [ WITH ] <create_option> [, ... ] ] [ AS (query) ]



<create_definition> ::= <column_definition> | <index_definition>



<create_option> ::= STORAGESPACE storage_space_name | SEGMENTSIZE segment_value | REDUNDANCY segment_part (redundancy_scheme) | STREAMLOADER_PROPERTIES streamloader_json | CLUSTERING [KEY | INDEX] clustering_index (clustering_column [, ...]) | INDEX index_name (index_column [, ...]) [ USING index_type ]



<column_definition> ::= column_name [ data_type ] [ <column_constraint> [, ...] ]



<index_definition> ::= [ CLUSTERING ] KEY index_name (index_column [, ...] )



<column_constraint> ::= TIME KEY BUCKET(bucket_value [, DAY, HOUR, MINUTE, SECOND ]) | [ NOT ] NULL | DEFAULT literal | COMMENT comment | COMPRESSION GDC [(compression_value), EXISTING schema_name] | MAX SIZE (size_value) | [ NO ] COMPRESSION [ compression_scheme ]



In this example, CREATE TABLE uses subsections for complex options, such as its column definition, options, and column constraints. Including all of these in one block would be difficult for a user to parse, especially if their goal was only to create a basic table.

Note that each subsection is defined with the name of the subsection in angle brackets followed by ::=

Rules for Column and Table Names

Names for tables and columns in Ocient have the following rules:

  • Names must begin with an alphabet letter.
  • Names can contain letters, numbers, and underscores.
  • Column names enclosed in double quotes can contain any characters, except for newlines and carriage returns. To use double quotation in a name, put two double quotation marks back to back
  • Unless names are enclosed in double quotes, they are treated as case insensitive.

Related Links

SQL Reference