] ``` ### Parameters Any existing *table* or *view* with the same name will be dropped before creating this one; mutually exclusive with `IF NOT EXISTS` The *table* will be distributed within the database as a [replicated](/content/concepts/tables#replicated) *table* The *table* will be a [memory-only table](/content/concepts/tables_memory_only); which, among other things, means it will not be persisted (if the database is restarted, the *table* will be removed), but it will have increased ingest performance If a *table* or *view* with the same name exists, do nothing; mutually exclusive with `OR REPLACE` Name of the *schema* that will contain the created *table*; if no *schema* is specified, the *table* will be created in the user's [default schema](/content/concepts/schemas#schema-default) Name of the *table* to create; must adhere to the supported [naming criteria](/content/sql/naming#sql-naming-criteria) Name of a *column* to create within the *table*; must adhere to the supported [naming criteria](/content/sql/naming#sql-naming-criteria) Definition of the column associated with ``; see [Data Definition (DDL)](/content/sql/ddl#sql-ddl) for column format Apply column comment `` to the column associated with `` Modifier for `PRIMARY KEY` that creates a [soft primary key](/content/concepts/tables#soft-primary-key) instead of a standard [primary key](/content/concepts/tables#primary-key) Optional [primary key](/content/concepts/tables#primary-key) specification clause, where `` is a comma-separated list of columns to use as the *primary key* for the *table* Optional [shard key](/content/concepts/tables#shard-key) specification clause, where `` is a comma-separated list of columns to use as the *shard key* for the *table* Optional comma-separated set of [foreign key](/content/concepts/tables#foreign-key) specification clauses, with the following parameters:

Parameter	Description
`\`	Comma-separated list of columns in the table to create that will reference a matching set of primary key columns in another table
`\`	Name of target table referred to in this foreign key
`\`	The primary key columns in the target table referred to in this foreign key, matching the list of columns specified in `\` in the table to create
`AS \`	Optional alias for the foreign key

Manual

The general format for the [manual list partition](/content/concepts/tables#partitioning-by-list-manual) clause is: ```sql title="PARTITION BY LIST Syntax" theme={null} PARTITION BY LIST ( ) [ PARTITIONS ( VALUES ( ), ... VALUES ( ) ) ] ``` The partition definition clause, `PARTITIONS`, is optional, though it is recommended to define partitions at table creation time, when feasible. Defining (adding) partitions after data has been loaded will result in a performance penalty as the database moves existing records targeted for the new partition from the *default partition* into it. For example, to create a *manual list-partitioned* *table* with the following criteria: * partitioned on the date/time of the order * partitions for years: * *2014* - *2016* * *2017* * *2018* * *2019* * records not in that list go to the *default partition* ```sql PARTITION BY LIST (Year) Example theme={null} CREATE TABLE example.customer_order_manual_list_partition_by_year ( id INT NOT NULL, customer_id INT NOT NULL, total_price DECIMAL(10,2), purchase_ts TIMESTAMP NOT NULL ) PARTITION BY LIST (YEAR(purchase_ts)) PARTITIONS ( order_2014_2016 VALUES (2014, 2015, 2016), order_2017 VALUES (2017), order_2018 VALUES (2018), order_2019 VALUES (2019) ) ``` To create a *manual list-partitioned* *table* with a multi-column key and the following criteria: * partitioned on the date/time of the order * each partition corresponds to a unique year & month pair * partitions for years/months: * *February 2016* & *March 2016* * *March 2020* * records not in that list go to the *default partition* ```sql PARTITION BY LIST (Month) Example theme={null} CREATE TABLE example.customer_order_manual_list_partition_by_year_and_month ( id INT NOT NULL, customer_id INT NOT NULL, total_price DECIMAL(10,2), purchase_ts TIMESTAMP NOT NULL ) PARTITION BY LIST (YEAR(purchase_ts), MONTH(purchase_ts)) PARTITIONS ( order_2016_0203 VALUES ((2016, 2), (2016, 3)), order_2020_03 VALUES ((2020, 3)) ) ```

Automatic

The general format for the [automatic list partition](/content/concepts/tables#partitioning-by-list-automatic) clause is: ```sql title="Automatic PARTITION BY LIST Syntax" theme={null} PARTITION BY LIST ( ) AUTOMATIC ``` To create an *automatic list-partitioned* *table* with the following criteria: * partitioned on the date/time of the order * one partition for each unique year & month across all orders * partitions are added as necessary ```sql Automatic PARTITION BY LIST (Month) Example theme={null} CREATE TABLE example.customer_order_automatic_list_partition_by_year_and_month ( id INT NOT NULL, customer_id INT NOT NULL, total_price DECIMAL(10,2), purchase_ts TIMESTAMP NOT NULL ) PARTITION BY LIST (YEAR(purchase_ts), MONTH(purchase_ts)) AUTOMATIC ``` #### Hash Partitioning The general format for the [hash partition](/content/concepts/tables#partitioning-by-hash) clause is: ```sql title="PARTITION BY HASH Syntax" theme={null} PARTITION BY HASH ( ) PARTITIONS ``` To create a *hash-partitioned* *table* with the following criteria: * partitioned on the date/time of the order * distributed among the fixed set of partitions, based on the hash of the year & month of the order * 10 partitions ```sql PARTITION BY HASH Example theme={null} CREATE TABLE example.customer_order_hash_partition_by_year_and_month ( id INT NOT NULL, customer_id INT NOT NULL, total_price DECIMAL(10,2), purchase_ts TIMESTAMP NOT NULL ) PARTITION BY HASH (YEAR(purchase_ts), MONTH(purchase_ts)) PARTITIONS 10 ``` #### Series Partitioning The general format for the [series partition](/content/concepts/tables#partitioning-by-series) clause is: ```sql title="PARTITION BY SERIES" theme={null} PARTITION BY SERIES ( ) [PERCENT_FULL ] ``` The `PERCENT_FULL` should be an integer between *1* and *100*; the default is *50%*. To create a *series-partitioned* *table* with the following criteria: * partitioned on the customer of each order * partitions with *closed* key sets will contain all orders from a set of unique customers * *50%* fill threshold ```sql PARTITION BY SERIES Example theme={null} CREATE TABLE example.customer_order_series_partition_error_default_percent_full ( id INT NOT NULL, customer_id INT NOT NULL, total_price DECIMAL(10,4), purchase_ts TIMESTAMP NOT NULL SHARD KEY(customer_id) ) PARTITION BY SERIES (customer_id) ``` To create a *series-partitioned* *track table* with the following criteria: * partitioned on the *track ID* * partitions with *closed* key sets will contain all points from a unique set of tracks * *25%* fill threshold ```sql PARTITION BY SERIES (Tracks) Example theme={null} CREATE TABLE example.route_series_partition_by_track ( TRACKID VARCHAR NOT NULL, x DOUBLE NOT NULL, y DOUBLE NOT NULL, TIMESTAMP TIMESTAMP NOT NULL ) PARTITION BY SERIES (TRACKID) PERCENT_FULL 25 ``` ### Tier Strategy Clause A *table* can have a [tier strategy](/content/rm/concepts#rm-concepts-tier-strategy) specified at creation time. If not assigned a *tier strategy* upon creation, a [default tier strategy](/content/rm/configuration#rm-config-tier-strategy-default) will be assigned. ```sql title="TIER STRATEGY Clause Syntax" theme={null} TIER STRATEGY ( , ... ) ``` For example, to create a `customer_order` *table* with an above-average [eviction priority](/content/rm/concepts#rm-concepts-eviction-priority) in the [RAM Tier](/content/rm/concepts#rm-concepts-tiers-ram): ```sql TIER STRATEGY Example theme={null} CREATE OR REPLACE TABLE example.customer_order ( id INT NOT NULL, customer_id INT NOT NULL, total_price DECIMAL(10,2), purchase_ts TIMESTAMP, SHARD KEY (customer_id) ) TIER STRATEGY ( ( ( VRAM 1, RAM 7, PERSIST 5 ) ) ) ``` If not specified, the *default tier strategy* will be assigned: ```sql Default TIER STRATEGY Example theme={null} CREATE OR REPLACE TABLE example.customer_order ( id INT NOT NULL, customer_id INT NOT NULL, total_price DECIMAL(10,2), purchase_ts TIMESTAMP, SHARD KEY (customer_id) ) ``` ```sql SHOW TABLE Command to Display TIER STRATEGY theme={null} SHOW CREATE TABLE example.customer_order ``` ```sql SHOW TABLE Command Output theme={null} | CREATE TABLE "example"."customer_order" ( "id" INTEGER NOT NULL, "customer_id" INTEGER (shard_key) NOT NULL, "total_price" DECIMAL (10, 2), "purchase_ts" TIMESTAMP ) TIER STRATEGY ( ( ( VRAM 1, RAM 7, PERSIST 5 ) ) ); | ``` The response to `SHOW TABLE` is a single-record result set with the DDL statement as the value in the `DDL` column, shown here with the column separators returned by kisql. ### Index Clause A *table* can have any number of [indexes](/content/concepts/indexes) applied to any of its columns at creation time. The types of explicit indexes supported are: B-tree index on a column for equality and range filter performance Column index optimized for columns with many duplicate values, using less memory Improves equality-based filtering, especially on partition key columns Improves performance of geospatial functions on geometry columns GPU-accelerated graph-based index for vector similarity search; requires manual refresh Graph-based index for vector similarity search; automatically maintained ```sql title="Index Clause Syntax" theme={null} <[ATTRIBUTE] | CHUNK [SKIP] | LOW CARDINALITY | GEOSPATIAL | CAGRA | HNSW> INDEX () ... <[ATTRIBUTE] | CHUNK [SKIP] | LOW CARDINALITY | GEOSPATIAL | CAGRA | HNSW> INDEX () ``` For example, to create a *table* with the following indexes: * *column index* on `last_name` * *low-cardinality index* on `dept_id` * *chunk skip index* on `id` * *geospatial index* on `work_district` * *geospatial index* on the pair of `office_longitude` & `office_latitude` * *CAGRA index* on `profile` ```sql Index Example theme={null} CREATE TABLE example.employee ( id INTEGER NOT NULL, dept_id INTEGER NOT NULL, manager_id INTEGER, first_name VARCHAR(30), last_name VARCHAR(30), sal DECIMAL(18,4), hire_date DATE, work_district WKT, office_longitude REAL, office_latitude REAL, profile VECTOR(10), PRIMARY KEY (id, dept_id), SHARD KEY (dept_id) ) INDEX (last_name) LOW CARDINALITY INDEX (dept_id) CHUNK SKIP INDEX (id) GEOSPATIAL INDEX (work_district) GEOSPATIAL INDEX (office_longitude, office_latitude) CAGRA INDEX (profile) HNSW INDEX (profile) ``` ### Table Property Clause A subset of *table* properties can be applied to the *table* at creation time. ```sql title="Table Property Clause" theme={null} USING TABLE PROPERTIES ( = , ...

= ) ``` Available table properties include: Size of the blocks of memory holding the data, when loaded; specified as the maximum number of bytes any one column should hold. The size of dictionary-encoded columns is estimated. Size of the blocks of memory holding the data, when loaded; specified as the maximum total number of bytes all columns should hold. The size of dictionary-encoded columns is estimated. Size of the blocks of memory holding the data, when loaded; specified as the maximum number of records each block of memory should hold. The default [compression](/content/concepts/column_compression) type to apply to columns of this table not explicitly given one. Error suppression option, which causes no error to be returned if a *table* with the same name already exists; default is `FALSE`. This is the same option as `IF NOT EXISTS`. The type of [primary key index](/content/concepts/indexes#primary-key-index) to use. The default is `memory`.

Type	Description
`memory`	Primary key index is loaded into memory, occupying RAM but improving performance.
`disk`	Primary key index is stored on disk only, increasing available RAM at the cost of some performance.

The [time-to-live (TTL)](/content/concepts/ttl) for the *table*; if not set, the *table* will not expire. For example, to create a table with up to *1,000,000* records per chunk and that will expire in *15* minutes: ```sql Table Property Example theme={null} CREATE OR REPLACE TABLE example.customer_order ( id INTEGER NOT NULL, customer_id INTEGER NOT NULL, total_price DECIMAL(10,2), purchase_ts TIMESTAMP, SHARD KEY (customer_id) ) USING TABLE PROPERTIES (CHUNK SIZE = 1000000, TTL = 15) ``` ## CREATE EXTERNAL TABLE Creates a new [external table](/content/concepts/external_tables), which is a database object whose source data is located in one or more files, either internal or external to the database. ```sql title="CREATE EXTERNAL TABLE Syntax" theme={null} CREATE [OR REPLACE] [REPLICATED] [TEMP] [LOGICAL | MATERIALIZED] EXTERNAL TABLE [.] [

] ``` For contextualized examples, see [Examples](/content/sql/ddl#sql-create-ext-table-examples). For copy/paste examples, see [Loading Data](/content/snippets/load-data). For an overview of loading data into *Kinetica*, see [Data Loading Concepts](/content/load_data/concepts). The source data can be located in either of the following locations: * in [KiFS](/content/tools/kifs) * on a remote system, accessible via a [data source](/content/sql/ddl#sql-create-data-source) A materialized *external table* (default) that uses a *data source* can perform a one-time load upon creation and optionally subscribe for updates on an interval, depending on the *data source* provider: | Provider | Description | One-Time Load | Subscription | | -------- | ------------------------------------------------------------------------------------------------------------------------------------------- | ------------- | ------------ | | *Azure* | Microsoft blob storage | Yes | Yes | | *GCS* | Google Cloud Storage | Yes | Yes | | *HDFS* | Apache Hadoop Distributed File System | Yes | | | *JDBC* | Java DataBase Connectivity; using a user-supplied driver or one of the drivers on the list [supported list](/content/concepts/jdbc_drivers) | Yes | Yes | | *S3* | Amazon S3 Bucket | Yes | Yes | See [Manage Subscription](/content/sql/ddl#sql-alter-table-manage-sub) for pausing, resuming, canceling, and dropping subscriptions on the *external table*. Although an *external table* cannot use a *data source* configured for *Kafka*, a standard *table* can have *Kafka* data streamed into it via a [LOAD INTO](/content/sql/load#sql-load-file-server) command that references such a *data source*. The use of *external tables* with [ring resiliency](/content/ha) has additional [considerations](/content/ha/ha_configuration#ring-extdata). ### Parameters Any existing *table* or *view* with the same name will be dropped before creating this one. The *external table* will be distributed within the database as a [replicated](/content/concepts/tables#replicated) *table*. The *external table* will be a [memory-only table](/content/concepts/tables_memory_only); which, among other things, means it will not be persisted (if the database is restarted, the *external table* will be removed), but it will have increased ingest performance. External data will **not** be loaded into the database; the data will be retrieved from the source upon servicing each query against the *external table*. This mode ensures queries on the *external table* will always return the most current source data, though there will be a performance penalty for reparsing & reloading the data from source files upon each query. Loads a copy of the external data into the database, refreshed on demand; this is the default *external table* type. Name of the *schema* that will contain the created *external table*; if no *schema* is specified, the *external table* will be created in the user's [default schema](/content/concepts/schemas#schema-default). Name of the *external table* to create; must adhere to the supported [naming criteria](/content/sql/naming#sql-naming-criteria). Optional clause, defining the structure for the *external table* associated with the source data. Source data specification clause, where `` is a SQL query selecting the data which will be loaded. This clause is mutually exclusive with the `FILE PATHS` clause, and is only applicable to JDBC *data sources*. The query should meet the following criteria: * Any column expression used is given a column alias. * The first column is not a `WKT` or unlimited length `VARCHAR` type. * The columns and expressions queried should match the intended order, number, & type of the columns in the target table. Any query resulting in more than *10,000* records will be distributed and loaded in parallel (unless directed otherwise) using the following rule sequence: 1. If `REMOTE_QUERY_NO_SPLIT` is `TRUE`, the query will not be distributed. 2. If a valid `REMOTE_QUERY_PARTITION_COLUMN` is specified, the query will be distributed by partitioning on the given column's values 3. If a valid `REMOTE_QUERY_ORDER_BY` is specified, the query will be distributed by ordering the data accordingly and then partitioning into sequential blocks from the first record 4. If a non-null numeric/date/time column exists, the query will be distributed by partitioning on the first such column's values 5. The query will be distributed by sorting the data on the first column and then partitioning into sequential blocks from the first record Type inferencing is limited by the available JDBC types. To take advantage of Kinetica-specific types and properties, define the table columns explicitly in the [\

](/content/sql/ddl#sql-create-ext-table-def). Source file specification clause, where `` is a comma-separated list of single-quoted file paths from which data will be loaded; all files specified are presumed to have the same format and data types. This clause is mutually exclusive with the `REMOTE QUERY` clause, and is not applicable to JDBC *data sources*. The form of a file path is dependent on the source referenced: * *Data Source*: If a *data source* is specified in the [load options](/content/sql/ddl#sql-create-ext-table-load-opt), these file paths must resolve to accessible files at that *data source* location. A "path prefix" can be specified instead, which will cause all files whose path begins with the given prefix to be included. For example, a "path prefix" of `/data/ge` for `` would match all of the following: * `/data/geo.csv` * `/data/geo/flights.csv` * `/data/geo/2021/airline.csv` If using an HDFS *data source*, the "path prefix" must be the name of an HDFS directory. * [KiFS](/content/tools/kifs): The path must resolve to an accessible file path within *KiFS*. A "path prefix" can be specified instead, which will cause all files whose path begins with the given prefix to be included. For example, a "path prefix" of `kifs://data/ge` would match all of the following files under the *KiFS* `data` directory: * `kifs://data/geo.csv` * `kifs://data/geo/flights.csv` * `kifs://data/geo/2021/airline.csv` Optional indicator of source file type, for file-based data sources; will be inferred from the file extension if not given. Supported formats include:

Keyword	Description
`\[DELIMITED] TEXT`	Any text-based, delimited field data file (CSV, PSV, TSV, etc.); a comma-delimited list of options can be given to specify the way in which the data file(s) should be parsed, including the delimiter used, whether headers are present, etc. Records spanning multiple lines are not supported. See [Delimited Text Options](/content/sql/ddl#sql-create-ext-table-delim-opt) for the complete list of `\`.
`AVRO`	Apache Avro data file
`JSON`	Either a JSON or GeoJSON data file See [JSON/GeoJSON Limitations](/content/load_data/concepts#ingest-json-limitations) for the supported data types.
`PARQUET`	Apache Parquet data file See [Parquet Limitations](/content/load_data/concepts#ingest-parquet-limitations) for the supported data types.
`SHAPEFILE`	ArcGIS shapefile

Source Data String	Representation when Loaded into the Database
`\a`	ASCII bell
`\b`	ASCII backspace
`\f`	ASCII form feed
`\n`	ASCII line feed
`\r`	ASCII carriage return
`\t`	ASCII horizontal tab
`\v`	ASCII vertical tab

For instance, if the escape character is `\`, a `\t` encountered in the data will be converted to a tab character when stored in the database. The escape character can be used to escape the quoting character, and will be treated as an escape character whether it is within a quoted field value or not. There is no default escape character. Use `char` as the source file header field name/property delimiter, when the source file header contains both names and properties. This is largely specific to the Kinetica export to delimited text feature, which will, within each field's header, contain the field name and any associated properties, delimited by the pipe `|` character. An example *Kinetica* header in a CSV file: ``` id|int|data,category|string|data|char16,name|string|data|char32 ``` The default is the `|` (pipe) character. See [Delimited Text Option Characters](/content/sql/load#sql-load-file-server-delim-opt-char) for allowed characters. The `DELIMITER` character will still be used to separate field name/property sets from each other in the header row Declare that the source file(s) will or will not have a header. The default is `TRUE`. Treat `string` as the indicator of a null source field value. The default is the empty string. Use `char` as the source file data quoting character, for enclosing field values. Usually used to wrap field values that contain embedded delimiter characters, though any field may be enclosed in quote characters *(for clarity, for instance)*. The quote character must appear as the first and last character of a field value in order to be interpreted as quoting the value. Within a quoted value, embedded quote characters may be escaped by preceding them with another quote character or the escape character specified by `ESCAPE`, if given. The default is the `"` (double-quote) character. See [Delimited Text Option Characters](/content/sql/ddl#sql-create-ext-table-delim-opt-char) for allowed characters. #### Delimited Text Option Characters For `DELIMITER`, `HEADER DELIMITER`, `ESCAPE`, & `QUOTE`, any single character can be used, or any one of the following escaped characters: | Escaped Char | Corresponding Source File Character | | ------------ | ----------------------------------- | | `''` | Single quote | | `\a` | ASCII bell | | `\b` | ASCII backspace | | `\f` | ASCII form feed | | `\t` | ASCII horizontal tab | | `\v` | ASCII vertical tab | For instance, if two single quotes (`''`) are specified for a `QUOTE` character, the parser will interpret *single quotes* in the source file as *quoting* characters; specifying `\t` for `DELIMITER` will cause the parser to interpret *ASCII horizontal tab* characters in the source file as *delimiter* characters. ### Load Options The following options can be specified to modify the way data is loaded (or not loaded) into the target table. Name of the table containing records that failed to be loaded into the target table. This bad record table will include the following columns:

Column Name	Source Data Format Codes
`line\_number`	Number of the line in the input file containing the failed record
`char\_number`	Position of character within a failed record that is assessed as the beginning of the portion of the record that failed to process
`filename`	Name of file that contained the failed record
`line\_rejected`	Text of the record that failed to process
`error\_msg`	Error message associated with the record processing failure

This option is not applicable for an `ON ERROR` mode of `ABORT`. In that mode, processing stops at the first error and that error is returned to the user. Use an ingest batch size of the given number of records. The default batch size is *50,000*. Use the given type-specific formatting for the given column when parsing source data being loaded into that column. This should be a map of column names to format specifications, where each format specification is map of column type to data format, all formatted as a JSON string. Supported column types include: Apply the given date format to the given column. Common date format codes follow. For the complete list, see [Date/Time Conversion Codes](/content/sql/query#sql-datetime-conversion-codes).

Code	Description
`YYYY`	4-digit year
`MM`	2-digit month, where January is `01`
`DD`	2-digit day of the month, where the 1st of each month is `01`

Apply the given time format to the given column. Common time format codes follow. For the complete list, see [Date/Time Conversion Codes](/content/sql/query#sql-datetime-conversion-codes).

Code	Description
`HH24`	24-based hour, where 12:00 AM is `00` and 7:00 PM is `19`
`MI`	2-digit minute of the hour
`SS`	2-digit second of the minute
`MS`	milliseconds

Apply the given date/time format to the given column. For example, to load dates of the format `2010.10.30` into date column *d* and times of the 24-hour format `18:36:54.789` into time column *t*: ``` { "d": {"date": "YYYY.MM.DD"}, "t": {"time": "HH24:MI:SS.MS"} } ``` This option is not available for *data sources* configured for *JDBC*. Load data from the given [data source](/content/sql/ddl#sql-create-data-source). [Data source connect privilege](/content/sql/security#sql-security-priv-mgmt-ds-grant) is required when loading from a *data source*. Use the given formats for source data being loaded into target table columns with the corresponding column types. This should be a map of target column type to source format for data being loaded into columns of that type, formatted as a JSON string. Supported column properties and source data formats are the same as those listed in the description of the `COLUMN FORMATS` option. For example, to make the default format for loading source data dates like `2010.10.30` and 24-hour times like `18:36:54.789`: ``` { "date": "YYYY.MM.DD", "time": "HH24:MI:SS.MS", "datetime": "YYYY.MM.DD HH24:MI:SS.MS" } ``` This option is not available for *data sources* configured for *JDBC*. Choose a comma-separated list of fields from the source file(s) to ignore, loading only those fields that are not in the identified list in the order they appear in the file. Fields can be identified by either `POSITION` or `NAME`. If ignoring by `NAME`, the specified names must match the source file field names exactly. * Identifying by Name: ``` FIELDS IGNORED BY NAME(Category, Description) ``` * Identifying by Position: ``` FIELDS IGNORED BY POSITION(3, 4) ``` - When ignoring source data file fields, the set of fields that are not ignored must align, in type & number in their order in the source file, with the *external table* columns into which the data will be loaded. - Ignoring fields by `POSITION` is only supported for delimited text files. Choose a comma-separated list of fields from the source file(s) to load, in the specified order, identifying fields by either `POSITION` or `NAME`. If mapping by `NAME`, the specified names must match the source file field names exactly. * Identifying by Name: ``` FIELDS MAPPED BY NAME(ID, Name, Stock) ``` * Identifying by Position: ``` FIELDS MAPPED BY POSITION(1, 2, 5) ``` - When mapping source data file fields, the set of fields that are identified must align, in type & number in the specified order, with the *external table* columns into which data will be loaded. - Mapping fields by `POSITION` is only supported for delimited text files. Specify the policy for handling nested columns within JSON data. The default is `FALSE`.

Value	Description
`TRUE`	Break up nested columns into multiple columns.
`FALSE`	Treat nested columns as JSON columns instead of flattening.

Specify the error suppression policy for inserting duplicate primary key values into a table with a primary key. If the specified table does not have a primary key or the `UPDATE_ON_EXISTING_PK` option is used, then this options has no effect. The default is `FALSE`.

Value	Description
`TRUE`	Suppress errors when inserted records and existing records' PKs match.
`FALSE`	Return errors when inserted records and existing records' PKs match.

Whether to do a full ingest of the data or perform a *dry run* or *type inference* instead. The default mode is `FULL`.

Value	Description
`DRY RUN`	No data will be inserted, but the file will be read with the applied `ON ERROR` mode and the number of valid records that would normally be inserted is returned.
`FULL`	Data is fully ingested according to the active `ON ERROR` mode.
`TYPE INFERENCE`	Infer the type of the source data and return, without ingesting any data. The inferred type is returned in the response, as the output of a `SHOW TABLE` command.

Retrieve this many records at a time from the remote database. Lowering this number will help tables with large record sizes fit into available memory during ingest. The default is *50,000*. This option is only available for *data sources* configured for *JDBC*. The number of remote query partitions to assign each *Kinetica* worker process. The queries assigned to a worker process will be executed by the tasks allotted to the process. To decrease memory pressure, increase the number of splits per rank. The default is *8* splits per rank. This option is only available for *data sources* configured for *JDBC*. The number of tasks to use on each *Kinetica* worker process to process remote queries. The tasks assigned to a worker process will execute any remote query partitions assigned to it. To decrease memory pressure, decrease the number of tasks per rank. The default is *8* tasks per rank. This option is only available for *data sources* configured for *JDBC*. Run the single given statement before the initial load is performed and also before each subsequent reload, if `REFRESH ON START` or `SUBSCRIBE` is `TRUE`. For example, to set the time zone to *UTC* before running each load, use: ``` JDBC_SESSION_INIT_STATEMENT = 'SET TIME ZONE ''UTC''' ``` This option is only available for *data sources* configured for *JDBC*. When an error is encountered loading a record, handle it using either of the following modes. The default mode is `ABORT`.

Value	Description
`SKIP`	If an error is encountered parsing a source record, skip the record.
`ABORT`	If an error is encountered parsing a source record, stop the data load process. Primary key collisions are considered abortable errors in this mode.

Interval, in seconds, at which a *data source* is polled for updates. The number of seconds must be passed as a single-quoted string. The default interval is *60* seconds. This option is only applicable when `SUBSCRIBE` is `TRUE`. Whether to refresh the *external table* data upon restart of the database. Only relevant for *materialized external tables*. The default is `FALSE`. This option is ignored if `SUBSCRIBE` is `TRUE`.

Value	Description
`TRUE`	Refresh the external table's data when the database is restarted.
`FALSE`	Do not refresh the external table's data when the database is restarted.

For a JDBC query *change data capture* loading scheme, the remote query column that will be used to determine whether a record is new and should be loaded or not. This column should have an ever-increasing value and be of an integral or date/timestamp type. Often, this column will be a sequence-based ID or create/modify timestamp. This option is only applicable when `SUBSCRIBE` is `TRUE`. This option is only available for *data sources* configured for *JDBC*. Whether to not distribute the retrieval of remote data and issue queries for blocks of data at time in parallel. The default is `FALSE`. This option is only available for *data sources* configured for *JDBC*

Value	Description
`TRUE`	Issue the remote data retrieval as a single query.
`FALSE`	Distribute and parallelize the remote data retrieval in queries for blocks of data at a time.

Ordering expression to use in partitioning remote data for retrieval. The remote data will be ordered according to this expression and then retrieved in sequential blocks from the first record. This is potentially less performant than using `REMOTE_QUERY_PARTITION_COLUMN`. If `REMOTE_QUERY_NO_SPLIT` is `TRUE`, a valid `REMOTE_QUERY_PARTITION_COLUMN` is specified, or the column given is invalid, this option is ignored. This option is only available for *data sources* configured for *JDBC* Column to use to partition remote data for retrieval. The column must be numeric and should be relatively evenly distributed so that queries using values of this column to partition data will retrieve relatively consistently-sized result sets. If `REMOTE_QUERY_NO_SPLIT` is `TRUE` or the column given is invalid, this option is ignored. This option is only available for *data sources* configured for *JDBC* Whether to subscribe to the [data source](/content/sql/ddl#sql-create-data-source) specified in the `DATA SOURCE` option. Only relevant for *materialized external tables* using *data sources* configured to allow streaming. The default is `FALSE`. If `TRUE`, the `REFRESH ON START` option is ignored. This option is not available for *data sources* configured for *HDFS*.

Value	Description
`TRUE`	Subscribe to the specified streaming data source.
`FALSE`	Do not subscribe to the specified data source.

Specify the string truncation policy for inserting text into `VARCHAR` columns that are not large enough to hold the entire text value. The default is `FALSE`.

Value	Description
`TRUE`	Truncate any inserted string value at the maximum size for its column.
`FALSE`	Reject any record with a string value that is too long for its column.

When making a type inference of the data values in order to define column types for the target table, use one of the following modes. The default mode is `SPEED`.

Value	Description
`ACCURACY`	Scan all available data to arrive at column types that are the narrowest possible that can still hold all the data.
`SPEED`	Pick the widest possible column types from the minimum data scanned in order to quickly arrive at column types that should fit all data values.

Specify the record collision policy for inserting into a table with a primary key. If the specified table does not have a primary key, then this options has no effect. The default is `FALSE`.

Value	Description
`TRUE`	Update existing records with records being inserted, when PKs match.
`FALSE`	Discard records being inserted when existing records' PKs match.

File-Based

To create an external table that loads a CSV file, products.csv, from the *data source* example.product\_ds, into a table named `example.ext_product`: ```sql CREATE EXTERNAL TABLE Data Source File Example theme={null} CREATE EXTERNAL TABLE example.ext_product FILE PATHS 'products.csv' WITH OPTIONS (DATA SOURCE = 'example.product_ds') ```

Query-Based

To create an external table that is the result of a remote query of employees in department *2* from the JDBC *data source* example.jdbc\_ds, into a local table named `example.ext_employee_dept2`: ```sql CREATE EXTERNAL TABLE Data Source Query Example theme={null} CREATE EXTERNAL TABLE example.ext_employee_dept2 REMOTE QUERY 'SELECT * FROM example.employee WHERE dept_id = 2' WITH OPTIONS (DATA SOURCE = 'example.jdbc_ds') ``` #### Change Data Capture

File-Based

To create an external table loaded by a set of order data in a change data capture scheme with the following conditions: * data pulled through a *data source*, example.order\_ds * data files contained with an orders directory * initially, all files in the directory will be loaded; subsequently, only those files that have been updated since the last check will be reloaded * files will be polled for updates every *60* seconds * target table named `example.ext_order` ```sql CREATE EXTERNAL TABLE File Change Data Capture Example theme={null} CREATE EXTERNAL TABLE example.ext_order FILE PATHS 'orders/' WITH OPTIONS (DATA SOURCE = 'example.order_ds', SUBSCRIBE = TRUE) ```

Query-Based

To create an external table loaded from a remote query of orders in a change data capture scheme with the following conditions: * data pulled through a *data source*, example.jdbc\_ds * data contained with an `example.orders` table, where only orders for product with ID *42* will be loaded into the target table * initially, all orders will be loaded; subsequently, only those orders with an `order_id` column value higher than the highest one on the previous poll cycle will be loaded * remote table will be polled for updates every *60* seconds * target table named `example.ext_order_product42` ```sql CREATE EXTERNAL TABLE Query Change Data Capture Example theme={null} -- Load new orders for product 42 continuously into a table -- order_id is an ever-increasing sequence allotted to each new order CREATE EXTERNAL TABLE example.ext_order_product42 REMOTE QUERY 'SELECT * FROM example.orders WHERE product_id = 42' WITH OPTIONS ( DATA SOURCE = 'example.jdbc_ds', SUBSCRIBE = TRUE, REMOTE_QUERY_INCREASING_COLUMN = 'order_id' ) ``` ## CREATE TABLE ... AS Creates a new *table* from the given query in the specified [schema](/content/sql/ddl#sql-create-schema). ```sql title="CREATE TABLE ... AS Syntax" theme={null} CREATE [OR REPLACE] [REPLICATED] [TEMP] TABLE [.] AS (

RENAME TO ``` All dependent [views](/content/sql/ddl#sql-create-view), [materialized views](/content/sql/ddl#sql-create-materialized-view), [streams](/content/sql/ddl#sql-create-stream), and [SQL procedures](/content/sql/procedure#sql-procedures) will be dropped. Any *tables* with [foreign keys](/content/concepts/tables#foreign-key) that target this *table* must be dropped before it can be renamed. ### Move Table A table can be moved from one schema to another. ```sql title="Move Table Syntax" theme={null} ALTER TABLE [.]

< MOVE TO | SET SCHEMA > ``` All dependent [views](/content/sql/ddl#sql-create-view), [materialized views](/content/sql/ddl#sql-create-materialized-view), [streams](/content/sql/ddl#sql-create-stream), and [SQL procedures](/content/sql/procedure#sql-procedures) will be dropped. Any *tables* with [foreign keys](/content/concepts/tables#foreign-key) that target this *table* must be dropped before it can be moved. For example, to move the `sales_2017` table from the `example_olap` schema to the `example_archive` schema: ```sql Move Table Example theme={null} ALTER TABLE example_olap.sales_2017 MOVE TO example_archive ``` ### Set Access Mode A table can have its global accessibility modified for all users in the system, independently from and further restricting any role-based access controls in place. Note that changing the access mode cannot *widen* access for users not already granted access; it can only *narrow* access for those who already have access. This setting will also trump administrative access to a table. ```sql title="Set Table Access Mode Syntax" theme={null} ALTER TABLE [.]

SET ACCESS MODE < NO_ACCESS | READ_ONLY | WRITE_ONLY | READ_WRITE > ``` ### Set TTL A table's [time-to-live (TTL)](/content/concepts/ttl) can be altered. ```sql title="Set Table TTL Syntax" theme={null} ALTER TABLE [.]

SET TTL ``` For example, to set a *TTL* of *7* minutes on a table: ```sql Set Table TTL Example theme={null} ALTER TABLE example.employee SET TTL 7 ``` To set a table to never expire by *TTL* timeout: ```sql Set Table No Expiration Example theme={null} ALTER TABLE example.employee SET TTL -1 ``` ### Add Column A column can be added, specifying a [column definition](/content/sql/ddl#sql-ddl). ```sql title="Add Table Column Syntax" theme={null} ALTER TABLE [.]

ADD [DEFAULT ] ``` A new column can have its values initially populated through the use of the `DEFAULT` keyword. These values can either be a string/numeric constant or the name of an existing column in the table from which values can be copied into the new column. This default value is only in effect for the column creation; the new column will have no default value after that. **Examples** To add, to the `employee` table, a `salary` column that is a non-nullable, 10-digit number field containing 2 decimal places with a default value of `0`: ```sql Add Table Column (Numeric) Example theme={null} ALTER TABLE example.employee ADD salary NUMERIC(10, 2) NOT NULL DEFAULT 0 ``` To add, to the `employee` table, a `category` column that is a nullable, [dictionary-encoded](/content/concepts/dictionary_encoding), 32-character text field: ```sql Add Table Column (CharN/Dictionary-Encoded) Example theme={null} ALTER TABLE example.employee ADD category VARCHAR(32, DICT) ``` To add, to the `employee` table, a `bio` column that is a nullable, [text-searchable](/content/concepts/types#types-data-handling), unrestricted-width text field: ```sql Add Table Column (String/Text-Searchable) Example theme={null} ALTER TABLE example.employee ADD bio VARCHAR(TEXT_SEARCH) ``` ### Rename Column An existing column can be renamed. ```sql title="Rename Table Column Syntax" theme={null} ALTER TABLE [.]

RENAME COLUMN TO ``` All dependent [views](/content/sql/ddl#sql-create-view), [materialized views](/content/sql/ddl#sql-create-materialized-view), [streams](/content/sql/ddl#sql-create-stream), and [SQL procedures](/content/sql/procedure#sql-procedures) will be dropped. Any *tables* with [foreign keys](/content/concepts/tables#foreign-key) that target the column being renamed must be dropped before it can be renamed. ```sql Rename Table Column Example theme={null} ALTER TABLE example.employee RENAME COLUMN bio TO biography ``` ### Modify Column A column can have its [column definition](/content/sql/ddl#sql-ddl) modified, affecting *column type*, *column size*, *column properties*, and nullability. ```sql title="Modify Table Column Syntax" theme={null} ALTER TABLE [.]

MODIFY [COLUMN] ``` ```sql title="Modify Table Column Alternate Syntax" theme={null} ALTER TABLE [.]

ALTER COLUMN ``` All dependent [views](/content/sql/ddl#sql-create-view), [materialized views](/content/sql/ddl#sql-create-materialized-view), [streams](/content/sql/ddl#sql-create-stream), and [SQL procedures](/content/sql/procedure#sql-procedures) will be dropped. Any *tables* with [foreign keys](/content/concepts/tables#foreign-key) that target the column being modified must be dropped before it can be modified. If a column is modified to be non-nullable, it will be populated with default values--empty string for string fields and `0` for numeric fields. **Examples** To change, in the `employee` table, the `first_name` column to one that is a non-nullable, [dictionary-encoded](/content/concepts/dictionary_encoding), 50-character text field: ```sql Modify Table Column Example theme={null} ALTER TABLE example.employee ALTER COLUMN first_name VARCHAR(50, DICT) NOT NULL ``` ### Drop Column An existing column can be removed from a table. ```sql title="Drop Table Column Syntax" theme={null} ALTER TABLE [.]

DROP COLUMN ``` All dependent [views](/content/sql/ddl#sql-create-view), [materialized views](/content/sql/ddl#sql-create-materialized-view), [streams](/content/sql/ddl#sql-create-stream), and [SQL procedures](/content/sql/procedure#sql-procedures) will be dropped. Any *tables* with [foreign keys](/content/concepts/tables#foreign-key) that target the column being dropped must be dropped before it can be dropped. ### Add Column Index A [column (attribute) index](/content/concepts/indexes#column-index) can be added to a table column in order to improve the performance of operations whose expressions contain relational operators against the column. See [Limitations](/content/concepts/indexes#column-index-limitations) for restrictions. ```sql title="Add Table Column Index Syntax" theme={null} ALTER TABLE [.]

ADD INDEX () ``` For example, to index the `employee` table's `last_name` column: ```sql Add Table Column Index Example theme={null} ALTER TABLE example.employee ADD INDEX (last_name) ``` ### Drop Column Index An existing [column (attribute) index](/content/concepts/indexes#column-index) can be removed from a table. ```sql title="Drop Table Column Index Syntax" theme={null} ALTER TABLE [.]

DROP INDEX () ``` For example, to drop the index on the `employee` table's `last_name` column: ```sql Drop Table Column Index Example theme={null} ALTER TABLE example.employee DROP INDEX (last_name) ``` ### Add Low-Cardinality Index A [low-cardinality index](/content/concepts/indexes#low-cardinality-index) can be added to a table column with few distinct values in order to improve the performance of operations whose expressions contain relational operators against the column. See [Limitations](/content/concepts/indexes#column-index-limitations) for restrictions. ```sql title="Add Table Low-Cardinality Index Syntax" theme={null} ALTER TABLE [.]

ADD LOW CARDINALITY INDEX () ``` For example, to index the `employee` table's `dept_id` column: ```sql Add Table Low-Cardinality Index Example theme={null} ALTER TABLE example.employee ADD LOW CARDINALITY INDEX (dept_id) ``` ### Drop Low-Cardinality Index An existing [low-cardinality index](/content/concepts/indexes#low-cardinality-index) can be removed from a table. ```sql title="Drop Table Low-Cardinality Index Syntax" theme={null} ALTER TABLE [.]

DROP LOW CARDINALITY INDEX () ``` For example, to drop the index on the `employee` table's `dept_id` column: ```sql Drop Table Low-Cardinality Index Example theme={null} ALTER TABLE example.employee DROP LOW CARDINALITY INDEX (dept_id) ``` ### Add Chunk Skip Index A [chunk skip index](/content/concepts/indexes#chunk-skip-index) can be added to a table column in order to improve the performance of operations containing equality-based filters against the column. See [Limitations](/content/concepts/indexes#chunk-skip-index-limitations) for restrictions. ```sql title="Add Table Chunk Skip Index Syntax" theme={null} ALTER TABLE [.]

ADD CHUNK [SKIP] INDEX () ``` For example, to index the `employee` table's employee ID column: ```sql Add Table Chunk Skip Index Example theme={null} ALTER TABLE example.employee ADD CHUNK SKIP INDEX (id) ``` ### Drop Chunk Skip Index An existing [chunk skip index](/content/concepts/indexes#chunk-skip-index) can be removed from a table. ```sql title="Drop Table Chunk Skip Index Syntax" theme={null} ALTER TABLE [.]

DROP CHUNK [SKIP] INDEX () ``` For example, to drop the chunk skip index on the `employee` table's employee ID column: ```sql Drop Table Chunk Skip Index Example theme={null} ALTER TABLE example.employee DROP CHUNK SKIP INDEX (id) ``` ### Add Geospatial Index A [geospatial index](/content/concepts/indexes#geospatial-index) can be added to one or more table columns to improve the performance of geospatial functions applied to them. ```sql title="Add Table Geospatial Index Syntax" theme={null} ALTER TABLE [.]

ADD GEOSPATIAL INDEX ([,...]) ``` For example, to index the `employee` table's work district WKT column: ```sql Add Table WKT Geospatial Index Example theme={null} ALTER TABLE example.employee ADD GEOSPATIAL INDEX (work_district) ``` To index the `employee` table's office location coordinate pair columns: ```sql Add Table Coordinate Pair Geospatial Index Example theme={null} ALTER TABLE example.employee ADD GEOSPATIAL INDEX (office_longitude, office_latitude) ``` ### Drop Geospatial Index An existing [geospatial index](/content/concepts/indexes#geospatial-index) can be removed from a table. ```sql title="Drop Table Geospatial Index Syntax" theme={null} ALTER TABLE [.]

DROP GEOSPATIAL INDEX ([,...]) ``` For example, to drop the geospatial index on the `employee` table's work district WKT column: ```sql Drop Table WKT Geospatial Index Example theme={null} ALTER TABLE example.employee DROP GEOSPATIAL INDEX (work_district) ``` To drop the geospatial index on the `employee` table's office location coordinate pair columns: ```sql Drop Table Coordinate Pair Geospatial Index Example theme={null} ALTER TABLE example.employee DROP GEOSPATIAL INDEX (office_longitude, office_latitude) ``` ### Add CAGRA Index A [CAGRA index](/content/concepts/indexes#cagra-index) can be added to a table column in order to improve the performance of [vector searches](/content/vector_search) applied to the column. ```sql title="Add Table CAGRA Index Syntax" theme={null} ALTER TABLE [.]

ADD CAGRA INDEX () ``` For example, to add a CAGRA index on the `employee` table's profile column: ```sql Add Table CAGRA Index Example theme={null} ALTER TABLE example.employee ADD CAGRA INDEX (profile) ``` ### Refresh CAGRA Index An existing [CAGRA index](/content/concepts/indexes#cagra-index) can be refreshed. ```sql title="Refresh Table CAGRA Index Syntax" theme={null} ALTER TABLE [.]

REFRESH CAGRA INDEX ON () ``` For example, to refresh the CAGRA index on the `employee` table's profile column: ```sql Refresh Table CAGRA Index Example theme={null} ALTER TABLE example.employee REFRESH CAGRA INDEX ON (profile) ``` ### Drop CAGRA Index An existing [CAGRA index](/content/concepts/indexes#cagra-index) can be removed from a table. ```sql title="Drop Table CAGRA Index Syntax" theme={null} ALTER TABLE [.]

DROP CAGRA INDEX () ``` For example, to drop the CAGRA index on the `employee` table's profile column: ```sql Drop Table CAGRA Index Example theme={null} ALTER TABLE example.employee DROP CAGRA INDEX (profile) ``` ### Add HNSW Index An [HNSW index](/content/concepts/indexes#hnsw-index) can be added to a table column in order to improve the performance of [vector searches](/content/vector_search) applied to the column. ```sql title="Add Table HNSW Index Syntax" theme={null} ALTER TABLE [.]

ADD HNSW INDEX () ``` For example, add an HNSW index on the `employee` table's profile column: ```sql Add Table HNSW Index Example theme={null} ALTER TABLE example.employee ADD HNSW INDEX (profile) ``` ### Drop HNSW Index An existing [HNSW index](/content/concepts/indexes#hnsw-index) can be removed from a table. ```sql title="Drop Table HNSW Index Syntax" theme={null} ALTER TABLE [.]

DROP HNSW INDEX () ``` For example, to drop the HNSW index on the `employee` table's profile column: ```sql Drop Table HNSW Index Example theme={null} ALTER TABLE example.employee DROP HNSW INDEX (profile) ``` ### Add Foreign Key A [foreign key](/content/concepts/tables#foreign-key) can be added to any column or set of columns with [primary key applicable types](/content/concepts/tables#primary-key-def) in order to improve the performance of [join](/content/sql/query#sql-join) operations between the table being altered and the table referenced in the *foreign key*. ```sql title="Add Table Foreign Key Syntax" theme={null} ALTER TABLE [.]

ADD FOREIGN KEY (,...) REFERENCES [.](,...) [AS ] ``` For example, to add a foreign key on the `employee` table's department ID column, linking it to the `department` table's department ID column: ```sql Add Table Foreign Key Example theme={null} ALTER TABLE example.employee ADD FOREIGN KEY (dept_id) REFERENCES example.department(id) AS fk_emp_dept ``` ### Drop Foreign Key An existing [foreign key](/content/concepts/tables#foreign-key) can be removed from a table, either by the name (alias) given to it during creation or by its definition: ```sql title="Drop Table Foreign Key by Name Syntax" theme={null} ALTER TABLE [.]

DROP FOREIGN KEY ``` ```sql title="Drop Table Foreign Key by Definition Syntax" theme={null} ALTER TABLE [.]

DROP FOREIGN KEY (,...) REFERENCES [.](,...) ``` For example, to drop the *foreign key* on the `employee` table's department ID column: ```sql Drop Table Foreign Key by Name Example theme={null} ALTER TABLE example.employee DROP FOREIGN KEY fk_emp_dept ``` ```sql Drop Table Foreign Key by Definition Example theme={null} ALTER TABLE example.employee DROP FOREIGN KEY (dept_id) REFERENCES example.department(id) ``` ### Add Partition A *partition* can be added to a [range-partitioned](/content/concepts/tables#partitioning-by-range) or [list-partitioned](/content/concepts/tables#partitioning-by-list) table. Defining (adding) partitions after data has been loaded will result in a performance penalty as the database moves existing records targeted for the new partition from the *default partition* into it. #### Range Partition The new *partition* can be given a minimum bound (inclusive) and a maximum bound (exclusive). If the new *partition* would come before an existing *partition*, omitting the maximum bound would cause the new *partition* to take on the nearest following existing *partition's* minimum bound as its maximum bound. If the new *partition* would come after an existing *partition*, omitting the minimum bound would cause the new *partition* to take on the nearest preceding *partition's* maximum bound as its minimum bound. If no *partitions* are present in the table, the new *partition* will have to declare both a minimum and maximum bound. ```sql title="Add Table Range Partition Syntax" theme={null} ALTER TABLE [.]

ADD PARTITION [ MIN ( ) ] [ MAX ( ) ] ``` For example, to add a *partition* to the `customer_order_range_by_year` table, containing all records with a *partition key* less than `2020` and greater than or equal to the maximum bound of the nearest preceding *partition*: ```sql Add Table Range Partition Example theme={null} ALTER TABLE example.customer_order_range_partition_by_year ADD PARTITION order_2020 MAX(2021) ``` #### List Partition The new *partition* can be given a list of values to match against the *partition key* values of incoming records. ```sql title="Add Table List Partition Syntax" theme={null} ALTER TABLE [.]

ADD PARTITION VALUES ( ) ``` For example, to add a *partition* to the `customer_order_manual_list_partition_by_year` table, containing all records from *2020*: ```sql Add Table List Partition (Year) Example theme={null} ALTER TABLE example.customer_order_manual_list_partition_by_year ADD PARTITION order_2020 VALUES (2020) ``` For example, to add a *partition* to the `customer_order_manual_list_partition_by_year_and_month` table, containing all records from *February 2020* & *April 2020*: ```sql Add Table List Partition (Month) Example theme={null} ALTER TABLE example.customer_order_manual_list_partition_by_year_and_month ADD PARTITION order_2020_0204 VALUES ((2020, 2), (2020, 4)) ``` ### Remove Partition An existing *partition* can be removed from a [range-partitioned](/content/concepts/tables#partitioning-by-range) or [list-partitioned](/content/concepts/tables#partitioning-by-list) table, sending all data contained within that *partition* back to the *default partition*. ```sql title="Remove Table Partition Syntax" theme={null} ALTER TABLE [.]

REMOVE PARTITION ``` For example, to remove a *partition* named `order_2017` from the `customer_order_range_by_year` table: ```sql Remove Table Partition Example theme={null} ALTER TABLE example.customer_order_range_partition_by_year REMOVE PARTITION order_2017 ``` ### Delete Partition An existing *partition* can be dropped from a [range-partitioned](/content/concepts/tables#partitioning-by-range) or [list-partitioned](/content/concepts/tables#partitioning-by-list) table, deleting all data contained within it. ```sql title="Delete Table Partition Syntax" theme={null} ALTER TABLE [.]

DELETE PARTITION ``` For example, to drop a *partition* named `order_2014_2016` from the `customer_order_range_by_year` table, deleting all data within that *partition*: ```sql Delete Table Partition Example theme={null} ALTER TABLE example.customer_order_range_partition_by_year DELETE PARTITION order_2014_2016 ``` ### Set Tier Strategy A table's [eviction priorities](/content/rm/concepts#rm-concepts-eviction-priority) can be adjusted by setting its [tier strategy](/content/rm/concepts#rm-concepts-tier-strategy). ```sql title="Set Table Tier Strategy Syntax" theme={null} ALTER TABLE [.]

SET TIER STRATEGY () ``` For example, to set the `customer_order` table's *tier strategy*, to one with a below-average *eviction priority* in the [RAM Tier](/content/rm/concepts#rm-concepts-tiers-ram): ```sql Set Table Tier Strategy Example theme={null} ALTER TABLE example.customer_order SET TIER STRATEGY ( ( ( VRAM 1, RAM 3, PERSIST 5 ) ) ) ``` The *tier strategy* can also be reset to the [system default strategy](/content/rm/configuration#rm-config-tier-strategy-default). ```sql title="Reset Table Tier Strategy Syntax" theme={null} ALTER TABLE [.]

RESET TIER STRATEGY ``` For example, to reset the `customer_order` table's *tier strategy*: ```sql Reset Table Tier Strategy Example theme={null} ALTER TABLE example.customer_order RESET TIER STRATEGY ``` ### Manage Subscription Any *table* that is subscribed to a streaming [external data source](/content/sql/ddl#sql-create-data-source) can have that subscription paused, resumed, canceled, or dropped. ```sql title="Manage External Table Subscription Syntax" theme={null} ALTER TABLE [.]

SUBSCRIPTION ``` Once unsubscribed, there is no way to re-subscribe the table to the *data source* via `ALTER TABLE`. An *external table* will need to be re-created, while a table whose subscription was initiated through the [LOAD INTO](/content/sql/load#sql-load-file-server) command can have that command re-executed. For example, to manage a subscription on the `ext_product` *external table* through the `product_ds` *data source*: ```sql Pause Subscription theme={null} ALTER TABLE example.ext_product PAUSE SUBSCRIPTION example.product_ds ``` ```sql Resume Subscription theme={null} ALTER TABLE example.ext_product RESUME SUBSCRIPTION example.product_ds ``` ```sql Cancel Subscription theme={null} ALTER TABLE example.ext_product CANCEL SUBSCRIPTION example.product_ds ``` ```sql Drop Subscription theme={null} ALTER TABLE example.ext_product DROP SUBSCRIPTION example.product_ds ``` ## REFRESH EXTERNAL TABLE Refreshes the data within a *materialized external table* that is not subscription-based. ```sql title="REFRESH EXTERNAL TABLE Syntax" theme={null} REFRESH [EXTERNAL] TABLE [.]

``` [Data source connect](/content/sql/security#sql-security-priv-mgmt-ds-grant) privilege is required to refresh an *external table* that uses a [data source](/content/sql/ddl#sql-create-data-source). ## REPAIR TABLE Repairs one or more corrupted tables. Tables are specified as a comma-separated list of table names. ```sql title="REPAIR TABLE Syntax" theme={null} REPAIR TABLE[S] [.]

[,...] WITH OPTIONS (REPAIR_POLICY = '') ``` ### Parameters Name of the *schema* containing the table to repair Name of the *table* to repair Specifies the type of *table* repair to perform. The following are available:

Type	Description
`delete\_chunks`	Deletes any corrupted chunks
`shrink\_columns`	Shrinks corrupted chunks to the shortest column
`replay\_wal`	Manually invokes WAL replay on the table

### Examples To manually replay the WAL on two tables: ```sql REPAIR TABLE Example theme={null} REPAIR TABLES example.rt_product, example.rt_order WITH OPTIONS (REPAIR_POLICY = 'replay_wal') ``` ## TRUNCATE TABLE Deletes all the records from a table. ```sql title="TRUNCATE TABLE Syntax" theme={null} TRUNCATE TABLE [.] ``` ## DROP TABLE Removes an existing [table](/content/sql/ddl#sql-create-table). All dependent [views](/content/sql/ddl#sql-create-view), [materialized views](/content/sql/ddl#sql-create-materialized-view), [streams](/content/sql/ddl#sql-create-stream), and [SQL procedures](/content/sql/procedure#sql-procedures) will be dropped. ```sql title="DROP TABLE Syntax" theme={null} DROP TABLE [IF EXISTS] [.]

``` Any *tables* with [foreign keys](/content/concepts/tables#foreign-key) that target a given *table* must be dropped before it can be dropped. ### Parameters Optional error-suppression clause; if specified, no error will be returned if the given *table* does not exist Name of the *schema* containing the *table* to remove Name of the *table* to remove ## SHOW TABLE Outputs the DDL statement required to reconstruct the given *table*. ```sql title="SHOW TABLE Syntax" theme={null} SHOW [[CREATE] TABLE] [.]

``` The response to `SHOW TABLE` is a single-record result set with the DDL statement as the value in the `DDL` column, shown below with the column separators returned by kisql. ### Parameters Optional keyword for clarity Optional clause to avoid ambiguity: * if given, and a *view* with the given name exists instead, the command will return an error * if omitted, and a *view* with the given name exists instead, the command will be interpreted as a `SHOW VIEW` statement Name of the *schema* containing the *table* to show Name of the *table* whose DDL will be output ### Examples To output the DDL for the example table created in the [CREATE TABLE](/content/sql/ddl#sql-create-table) section: ```sql SHOW TABLE Example theme={null} SHOW CREATE TABLE example.various_types ``` ```sql SHOW TABLE Output theme={null} | CREATE TABLE "example"."various_types" ( "i" INTEGER (primary_key) NOT NULL COMMENT 'non-nullable integer, part of primary key (defined at end)', "bi" BIGINT (primary_key, shard_key) NOT NULL COMMENT 'long, part of primary key, shard key, foreign key source (defined at end)', "b" BOOLEAN COMMENT '0s and 1s only', "ub" UNSIGNED BIGINT COMMENT 'native unsigned long', "r" REAL COMMENT 'native float', "d" DOUBLE COMMENT 'native double', "s" VARCHAR (text_search) COMMENT 'string, searchable, only limited in size by system-configured value', "c" VARCHAR (32, dict) COMMENT 'char32 using dictionary-encoding of values', "p" VARCHAR (256, text_search) COMMENT 'char256, searchable', "ip" IPV4 COMMENT 'IP address', "ui" UUID DEFAULT NEW_UUID() COMMENT 'UUID', "ts" TIMESTAMP COMMENT 'timestamp', "td" DATE COMMENT 'simple date', "tt" TIME COMMENT 'simple time', "dt" DATETIME DEFAULT NOW() COMMENT 'date/time', "dc" DECIMAL (28, 0) COMMENT 'integer of up to 27 digits', "dc8" DECIMAL (18, 4) COMMENT '8-byte decimal', "dc12" DECIMAL (27, 18) COMMENT '12-byte decimal', "n" DECIMAL (18, 4) COMMENT 'alias for DECIMAL(18, 4)', "byt" BLOB COMMENT 'BLOB', "w" GEOMETRY COMMENT 'geospatial column for WKT string data', "j" JSON COMMENT 'JSON string', "v" VECTOR (10) COMMENT 'vector column holding 10 floating point values', "ai" INTEGER[3] COMMENT 'array column holding 3 integer values', FOREIGN KEY ("bi") REFERENCES "example"."lookup" ("id") AS "fk" ) TIER STRATEGY ( ( ( VRAM 1, RAM 5, DISK0 5, PERSIST 5 ) ) ) ATTRIBUTE INDEX ("ip") ATTRIBUTE INDEX ("ts"); | ``` ## DESCRIBE TABLE Lists the columns and column types & properties for a given *table*. ```sql title="DESCRIBE TABLE Syntax" theme={null} DESC[RIBE] [TABLE] [.]

``` ### Parameters Optional clause to avoid ambiguity: * if given, and a *view* with the given name exists instead, the command will return an error * if omitted, and a *view* with the given name exists instead, the command will be interpreted as a `DESCRIBE VIEW` statement Name of the *schema* containing the *table* to describe Name of the *table* whose configuration will be output ### Examples To describe the example table created in the [CREATE TABLE](/content/sql/ddl#sql-create-table) section: ```sql DESCRIBE TABLE Example theme={null} DESC example.various_types ``` ```sql DESCRIBE TABLE Output theme={null} +-----------+--------+------------+-----------------------------------+ | Col_num | Name | Null? | Type | +-----------+--------+------------+-----------------------------------+ | 0 | i | NOT NULL | INTEGER (primary_key) | | 1 | bi | NOT NULL | BIGINT (primary_key, shard_key) | | 2 | b | | BOOLEAN | | 3 | ub | | UNSIGNED BIGINT | | 4 | r | | REAL | | 5 | d | | DOUBLE | | 6 | s | | VARCHAR (text_search) | | 7 | c | | VARCHAR (32, dict) | | 8 | p | | VARCHAR (256, text_search) | | 9 | ip | | IPV4 | | 10 | ui | | UUID (init_with_uuid) | | 11 | ts | | TIMESTAMP | | 12 | td | | DATE | | 13 | tt | | TIME | | 14 | dt | | DATETIME (init_with_now) | | 15 | dc | | DECIMAL (28, 0) | | 16 | dc8 | | DECIMAL (18, 4) | | 17 | dc12 | | DECIMAL (27, 18) | | 18 | n | | DECIMAL (18, 4) | | 19 | byt | | BLOB | | 20 | w | | GEOMETRY | | 21 | j | | JSON | | 22 | v | | VECTOR (10) | | 23 | ai | | INTEGER[3] | +-----------+--------+------------+-----------------------------------+ ``` ## CREATE VIEW Creates a new *virtual table* from the given query. ```sql title="CREATE VIEW Syntax" theme={null} CREATE [OR REPLACE] VIEW [.] AS [WITH OPTIONS ( = ''[,...])] [

Constant	Description
`OFF`	(the default) Will prevent the materialized view from being automatically refreshed, but will still allow [manual refreshes](/content/sql/ddl#sql-refresh-view) of the data to be requested
`ON CHANGE`	Will cause the materialized view to be updated any time a record is added, modified, or deleted from the subtending tables in its query
`ON QUERY`	Will cause the materialized view to be updated any time it is queried
`EVERY`	Allows specification of an interval in seconds, minutes, hours, or days, at which the materialized view should be refreshed. By default, the first refresh interval will be one interval's worth of time from the point at which the materialized view creation was requested. This can be modified with the following options: `STARTING AT`: specify a date or timestamp at which refresh cycles should begin `STOP AFTER`: specify a date, timestamp, or time interval after which refresh cycles should end

The query that will define both the structure and content of the created *materialized view* Optional indicator that a comma-delimited list of connection option/value assignments will follow. The following options are available:

Option	Description
`EXECUTE AS`	Executes materialized view refreshes as the given user with that user's privileges, when `EVERY ...` is specified as the `REFRESH` method. If this user doesn't exist at the time of a refresh, the refresh will be executed as the creating user, and, failing that, the system administration user.

Type	Description
`aws\_access\_key`	Authenticate to Amazon Web Services (AWS) via Access Key
`aws\_iam\_role`	Authenticate to Amazon Web Services (AWS) via IAM Role
`azure\_ad`	Authenticate to Microsoft Azure via Active Directory
`azure\_sas`	Authenticate to Microsoft Azure via Shared Access Signature (SAS)
`azure\_storage\_key`	Authenticate to Microsoft Azure via Storage Key
`confluent`	Authenticate to a Confluent Kafka cluster or schema registry
`docker`	Authenticate to a Docker repository
`gcs\_service\_account\_id`	Authenticate to Google Cloud via user ID & private key
`gcs\_service\_account\_keys`	Authenticate to Google Cloud via JSON key
`hdfs`	Authenticate to HDFS
`jdbc`	Authenticate via Java Database Connectivity
`kafka`	Authenticate to a Kafka cluster or schema registry
`nvidia\_api\_key`	Authenticate to the Nvidia AI API
`openai\_api\_key`	Authenticate to the OpenAI API

Provider	Description
`AZURE`	Microsoft Azure blob storage
`CONFLUENT`	Confluent Kafka streaming feed
`GCS`	Google Cloud Storage
`HDFS`	Apache Hadoop Distributed File System
`JDBC`	JDBC connection, where `LOCATION` is the JDBC or CData JDBC URL. See the [supported list](/content/concepts/jdbc_drivers) for the full list of supported drivers, or specify one with `JDBC\_DRIVER\_JAR\_PATH` and `JDBC\_DRIVER\_CLASS\_NAME`.
`KAFKA`	Apache Kafka streaming feed
`S3`	Amazon S3 bucket

| [QUERY] () > [ REFRESH < ON CHANGE | EVERY [STARTING AT ''] > ] [WHERE ] WITH OPTIONS ( < DATASINK_NAME = '' | DESTINATION = '' >,

Constant	Description
`ON CHANGE`	Will cause notifications to be streamed any time a record is added, modified, or deleted from the monitored table
`EVERY`	Allows specification of an interval in seconds, minutes, hours, or days, with the optional specification of a starting time at which the first monitor interval will run; if no start time is specified, the default will be an interval's worth of time from the point at which the stream was created

[WHERE ] WITH OPTIONS (DATASINK_NAME = '[.]') ``` ```sql HTTP theme={null} CREATE STREAM [.] ON [TABLE] [

[WHERE ] WITH OPTIONS (DESTINATION = 'http://[:]') ``` ```sql HTTPS theme={null} CREATE STREAM [.] ON [TABLE] [

[WHERE ] WITH OPTIONS (DESTINATION = 'https://[:]') ``` ```sql Kafka (No auth) theme={null} CREATE STREAM [.] ON [TABLE] [

[WHERE ] WITH OPTIONS ( DESTINATION = 'kafka://:', KAFKA_TOPIC_NAME = '' ) ``` ```sql Local Table theme={null} CREATE STREAM [.] ON [TABLE] [.] [WHERE ] WITH OPTIONS (DESTINATION = 'table://[.]') ``` ## DROP STREAM Removes an existing [stream](/content/sql/ddl#sql-create-stream). ```sql title="DROP STREAM Syntax" theme={null} DROP STREAM [.] ``` ### Parameters Name of the *schema* containing the *stream* to remove Name of the *stream* to remove ### Examples To drop a *stream*, `kin_stream`: ```sql DROP STREAM Example theme={null} DROP STREAM example.kin_stream ``` ## SHOW STREAM Outputs the DDL statement required to reconstruct one or more existing [streams](/content/sql/ddl#sql-create-stream). ```sql title="SHOW STREAM Syntax" theme={null} SHOW STREAM < [.] | .* | * > ``` Listing options: * `[.]` - output the DDL statement of the given *stream* * `.*` - output the DDL statements of all *streams* under the given *schema* * `*` - output the DDL statements of all *streams* The response to `SHOW STREAM` is a single-column result set with the DDL statement as the value in the `DDL` column. ### Parameters Name of the *schema* containing the *stream(s)* to show Name of the *stream* to show ### Examples To output the DDL for a *stream*, `kin_stream`: ```sql SHOW STREAM Example theme={null} SHOW STREAM example.kin_stream ``` ## DESCRIBE STREAM Outputs the configuration of one or more existing [streams](/content/sql/ddl#sql-create-stream). ```sql title="SHOW STREAM Syntax" theme={null} DESC[RIBE] STREAM < [.] | .* | * > ``` Listing options: * `[.]` - output the configuration of the given *stream* * `.*` - output the configuration of all *streams* under the given *schema* * `*` - output the configuration of all *streams* ### Parameters Name of the *schema* containing the *stream(s)* to describe Name of the *stream* to describe ### Response The response to `DESCRIBE STREAM` is a nine-column result set: | Output Column | Description | | --------------------------------- | --------------------------------------------------------------------------------------------------------- | | `MONITOR_ID` | Unique name or topic ID for the *stream* | | `TABLE_NAME` | Name of the table being monitored | | `EVENT` | Table data event that triggers streamed content | | `INCREASING_COLUMN` | Column in table being monitored that contains ever-increasing data values | | `FILTER_EXPRESSION` | Filter used to monitor only data of interest | | `MAX_CONSECUTIVE_FAILURES` | Maximum number of failed notification attempts before suspending the *stream*; `-1` disables auto-suspend | | `FAILED_NOTIFICATIONS_TABLE_NAME` | Name of the table to which failed *stream* notifications are written and replayed when re-enabled | | `DATASINK_NAME` | Name of the *data sink* associated with the consumer | | `STATUS` | Whether the stream is enabled or suspended | ### Examples To show the configuration for a *stream*, `kin_stream`: ```sql DESCRIBE STREAM Example theme={null} DESC STREAM example.kin_stream ``` ## DESCRIBE Lists the contained *tables* and *views* of a given *schema*, or lists the columns and column types & properties for a given *table* or *view*. ```sql title="DESCRIBE Schema Syntax" theme={null} DESC[RIBE] ``` ```sql title="DESCRIBE Table Syntax" theme={null} DESC[RIBE] [.]

``` ### Examples To describe the tables contained in the `demo` schema, into which demo data is usually downloaded: ```sql DESCRIBE Schema Example theme={null} DESC demo ``` ```sql DESCRIBE Schema Output theme={null} +------------+ | Set_name | +------------+ | stocks | | nyctaxi | | flights | +------------+ ``` To describe the example table created in the [CREATE TABLE](/content/sql/ddl#sql-create-table) section: ```sql DESCRIBE Table Example theme={null} DESC example.various_types ``` ```sql DESCRIBE Table Output theme={null} +-----------+--------+------------+-----------------------------------+ | Col_num | Name | Null? | Type | +-----------+--------+------------+-----------------------------------+ | 0 | i | NOT NULL | INTEGER (primary_key) | | 1 | bi | NOT NULL | BIGINT (primary_key, shard_key) | | 2 | b | | BOOLEAN | | 3 | ub | | UNSIGNED BIGINT | | 4 | r | | REAL | | 5 | d | | DOUBLE | | 6 | s | | VARCHAR (text_search) | | 7 | c | | VARCHAR (32, dict) | | 8 | p | | VARCHAR (256, text_search) | | 9 | ip | | IPV4 | | 10 | ui | | UUID (init_with_uuid) | | 11 | ts | | TIMESTAMP | | 12 | td | | DATE | | 13 | tt | | TIME | | 14 | dt | | DATETIME (init_with_now) | | 15 | dc | | DECIMAL (28, 0) | | 16 | dc8 | | DECIMAL (18, 4) | | 17 | dc12 | | DECIMAL (27, 18) | | 18 | n | | DECIMAL (18, 4) | | 19 | byt | | BLOB | | 20 | w | | GEOMETRY | | 21 | j | | JSON | | 22 | v | | VECTOR (10) | | 23 | ai | | INTEGER[3] | +-----------+--------+------------+-----------------------------------+ ``` ## Comments *Comments*, text-based tags, can be applied to a variety of object types. Normally, *comments* are applied to objects after the object is created; however, *comments* can also be applied in-line to table columns within a [CREATE TABLE](/content/sql/ddl#sql-create-table) or [CREATE EXTERNAL TABLE](/content/sql/ddl#sql-create-ext-table) statement. ```sql title="COMMENT Syntax" theme={null} COMMENT ON

Type	Description
`SCHEMA`	Apply a [schema-level](/content/sql/ddl#sql-create-schema) comment; can be viewed in the `ki\_catalog.ki\_schemas` [Kinetica catalog table](/content/catalogs/kinetica)
`TABLE`	Apply a [table-level](/content/sql/ddl#sql-create-table) comment; can be viewed in the `ki\_catalog.ki\_objects` [Kinetica catalog table](/content/catalogs/kinetica)
`VIEW`	Apply a [view-level](/content/sql/ddl#sql-create-view) comment; can be viewed in the `ki\_catalog.ki\_objects` [Kinetica catalog table](/content/catalogs/kinetica)
`COLUMN`	Apply a [column-level](/content/sql/ddl#sql-create-table) comment; can be viewed in the `ki\_catalog.ki\_columns` [Kinetica catalog table](/content/catalogs/kinetica)
`PROCEDURE`	Apply a [procedure-level](/content/sql/procedure) comment; can be viewed in the `ki\_catalog.ki\_objects` [Kinetica catalog table](/content/catalogs/kinetica)
`USER`	Apply a [user-level](/content/sql/security#sql-security-user-mgmt) comment; can be viewed in the `ki\_catalog.ki\_users\_and\_roles` [Kinetica catalog table](/content/catalogs/kinetica)
`ROLE`	Apply a [role-level](/content/sql/security#sql-security-role-mgmt) comment; can be viewed in the `ki\_catalog.ki\_users\_and\_roles` [Kinetica catalog table](/content/catalogs/kinetica)

Name of the object to which the *comment* will be applied; must adhere to the supported [naming criteria](/content/sql/naming#sql-naming-criteria) Text of the *comment* to apply. ### Examples For example, to apply a *comment* to a column in-line with the containing table creation: ```sql In-Line Column Comment Example theme={null} CREATE TABLE comment_schema.comment_table_inline ( id INT COMMENT 'I''m an in-line table column-level comment', name VARCHAR(32) ) ``` To apply a *comment* to a column after the containing table is created: ```sql Post-Creation Column Comment Example theme={null} COMMENT ON COLUMN comment_schema.comment_table_inline.name IS 'I''m a post-creation table column-level comment' ``` ### List Comments To list the *comments* for users & roles: ```sql List User/Role Comments theme={null} SELECT name, comments FROM ki_users_and_roles ``` To list the *comments* for schemas: ```sql List Schema Comments theme={null} SELECT schema_name, comments FROM ki_schemas ``` To list the *comments* for tables, views, & SQL procedures: ```sql List Table/View/Procedure Comments theme={null} SELECT obj_kind, object_name, comments FROM ki_objects ``` To list the *comments* for columns: ```sql List Column Comments theme={null} SELECT schema_name, table_name, column_name, comments FROM ki_columns ``` To show the *comments* for columns within the context of the containing table, use [SHOW TABLE](/content/sql/ddl#sql-show-table) on the table containing the columns.

Provider	Default Host
Azure	`\.blob.core.windows.net`
GCS	`storage.googleapis.com`
S3	`\.amazonaws.com`