<commit_before>.. _sql_ddl:

========================
Data Definition in Crate
========================


Table Basics
============

To create a table use the ``CREATE TABLE`` command. You must at least specify a name for the
table and names and types of the columns.
See `Data Types` for information about the supported data types.

Let's create a simple table with two columns of type ``integer`` and ``string``::

    cr> create table my_table (
    ...   first_column integer,
    ...   second_column string
    ... )
    CREATE OK (... sec)

A table can be removed by using the ``DROP TABLE`` command::

    cr> drop table my_table
    DROP OK (... sec)

Constraints
===========

Primary Key
-----------

The primary key constraint combines a unique constraint and a not-null constraint. It also defines
the default routing value used for sharding. Example::

    cr> create table my_table1 (
    ...   first_column integer primary key,
    ...   second_column string
    ... )
    CREATE OK (... sec)

Currently primary key's cannot be auto generated and have to be specified if
data is inserted, otherwise an error is returned.

.. note::

  Multiple primary keys are not supported yet.

Data Types
==========

boolean
-------

A basic boolean type. Accepting ``true`` and ``false`` as values. Example::

    cr> create table my_bool_table (
    ...   first_column boolean
    ... )
    CREATE OK (... sec)

    cr> drop table my_bool_table
    DROP OK (... sec)

string
------

A text-based basic type containing one or more character. Example::

    cr> create table my_table2 (
    ...   first_column string
    ... )
    CREATE OK (... sec)


number
------

Crate supports a set of number types: ``integer``, ``long``, ``short``, ``double``,
``float`` and ``byte``. All types have the same ranges as corresponding `Java types`_.
You can insert any number for any type, be it a ``float``, ``integer``, or ``byte``
as long as its within the corresponding range.
Example::

    cr> create table my_table3 (
    ...   first_column integer,
    ...   second_column long,
    ...   third_column short,
    ...   fourth_column double,
    ...   fifth_column float,
    ...   sixth_column byte
    ... )
    CREATE OK (... sec)

timestamp
---------

The timestamp type is a special type which maps to a formatted string. Internally it maps to
the UTC milliseconds since 1970-01-01T00:00:00Z stored as ``long``. They are always returned as ``long``.
The default format is dateOptionalTime_ and cannot be changed currently.
Formatted date strings containing timezone offset information will be converted to UTC.
Formated string without timezone offset information will be treated as UTC.
Timestamps will also accept a ``long`` representing UTC milliseconds since the epoch or
a ``float`` or ``double`` representing UTC seconds since the epoch with milliseconds as
fractions. Example::

    cr> create table my_table4 (
    ...   id integer,
    ...   first_column timestamp
    ... )
    CREATE OK (... sec)

    cr> insert into my_table4 (id, first_column) values (0, '1970-01-01T00:00:00')
    INSERT OK, 1 row affected (... sec)

    cr> insert into my_table4 (id, first_column) values (1, '1970-01-01T00:00:00+0100')
    INSERT OK, 1 row affected (... sec)

    cr> insert into my_table4 (id, first_column) values (2, 0)
    INSERT OK, 1 row affected (... sec)

    cr> insert into my_table4 (id, first_column) values (3, 1.0)
    INSERT OK, 1 row affected (... sec)

    cr> insert into my_table4 (id, first_column) values (3, 'wrong')
    ValidationException[Validation failed for first_column: Invalid timestamp ISODate string]


Sharding
========

Number of shards
----------------

Crate supports sharding natively, it even uses *5* shards by default if not further defined.
The number of shards can be defined by using the ``CLUSTERED INTO <number> SHARDS`` statement on
table creation. Example::

    cr> create table my_table5 (
    ...   first_column int
    ... ) clustered into 10 shards
    CREATE OK (... sec)

.. note::

  The number of shards can only be set on table creation, it cannot be changed later on.

Routing<commit_msg>hotfix: itest fix<commit_after>.. _sql_ddl:

========================
Data Definition in Crate
========================


Table Basics
============

To create a table use the ``CREATE TABLE`` command. You must at least specify a name for the
table and names and types of the columns.
See `Data Types` for information about the supported data types.

Let's create a simple table with two columns of type ``integer`` and ``string``::

    cr> create table my_table (
    ...   first_column integer,
    ...   second_column string
    ... )
    CREATE OK (... sec)

A table can be removed by using the ``DROP TABLE`` command::

    cr> drop table my_table
    DROP OK (... sec)

Constraints
===========

Primary Key
-----------

The primary key constraint combines a unique constraint and a not-null constraint. It also defines
the default routing value used for sharding. Example::

    cr> create table my_table1 (
    ...   first_column integer primary key,
    ...   second_column string
    ... )
    CREATE OK (... sec)

Currently primary key's cannot be auto generated and have to be specified if
data is inserted, otherwise an error is returned.

.. note::

  Multiple primary keys are not supported yet.

Data Types
==========

boolean
-------

A basic boolean type. Accepting ``true`` and ``false`` as values. Example::

    cr> create table my_bool_table (
    ...   first_column boolean
    ... )
    CREATE OK (... sec)

    cr> drop table my_bool_table
    DROP OK (... sec)

string
------

A text-based basic type containing one or more character. Example::

    cr> create table my_table2 (
    ...   first_column string
    ... )
    CREATE OK (... sec)


number
------

Crate supports a set of number types: ``integer``, ``long``, ``short``, ``double``,
``float`` and ``byte``. All types have the same ranges as corresponding `Java types`_.
You can insert any number for any type, be it a ``float``, ``integer``, or ``byte``
as long as its within the corresponding range.
Example::

    cr> create table my_table3 (
    ...   first_column integer,
    ...   second_column long,
    ...   third_column short,
    ...   fourth_column double,
    ...   fifth_column float,
    ...   sixth_column byte
    ... )
    CREATE OK (... sec)

timestamp
---------

The timestamp type is a special type which maps to a formatted string. Internally it maps to
the UTC milliseconds since 1970-01-01T00:00:00Z stored as ``long``. They are always returned as ``long``.
The default format is dateOptionalTime_ and cannot be changed currently.
Formatted date strings containing timezone offset information will be converted to UTC.
Formated string without timezone offset information will be treated as UTC.
Timestamps will also accept a ``long`` representing UTC milliseconds since the epoch or
a ``float`` or ``double`` representing UTC seconds since the epoch with milliseconds as
fractions. Example::

    cr> create table my_table4 (
    ...   id integer,
    ...   first_column timestamp
    ... )
    CREATE OK (... sec)

    cr> insert into my_table4 (id, first_column) values (0, '1970-01-01T00:00:00')
    INSERT OK, 1 row affected (... sec)

    cr> insert into my_table4 (id, first_column) values (1, '1970-01-01T00:00:00+0100')
    INSERT OK, 1 row affected (... sec)

    cr> insert into my_table4 (id, first_column) values (2, 0)
    INSERT OK, 1 row affected (... sec)

    cr> insert into my_table4 (id, first_column) values (3, 1.0)
    INSERT OK, 1 row affected (... sec)

    cr> insert into my_table4 (id, first_column) values (3, 'wrong')
    ValidationException[Validation failed for first_column: Invalid timestamp: Invalid ISO-date string]


Sharding
========

Number of shards
----------------

Crate supports sharding natively, it even uses *5* shards by default if not further defined.
The number of shards can be defined by using the ``CLUSTERED INTO <number> SHARDS`` statement on
table creation. Example::

    cr> create table my_table5 (
    ...   first_column int
    ... ) clustered into 10 shards
    CREATE OK (... sec)

.. note::

  The number of shards can only be set on table creation, it cannot be changed later on.

Routing
{'fraction_non_alphanumeric': 0.12740615976900865, 'fraction_numerical': 0.0170837343599615, 'mean_word_length': 2.912, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 33, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '606247', 'n_tokens_mistral': 2476, 'n_tokens_neox': 2287, 'n_words': 1054}