> ## Documentation Index
> Fetch the complete documentation index at: https://docs.kinetica.com/llms.txt
> Use this file to discover all available pages before exploring further.

# /execute/sql

```
URL: http://<db.host>:<db.port>/execute/sql
```

Execute a SQL statement (query, DML, or DDL).

See [SQL Support](../../sql/) for the complete set of supported SQL
commands.

When a caller wants all the results from a large query (e.g., more than
[max\_get\_records\_size](../../config/#config-main-general) records), they
can make multiple calls to this endpoint using the input parameter *offset* and
input parameter *limit* parameters to page through the results.  Normally, this
will execute the input parameter *statement* query each time. To avoid
re-executing the query each time and to keep the results in the same order, the
caller should specify a *paging\_table* name to hold the results of the query
between calls and specify the *paging\_table* on subsequent calls. When this is
done, the caller should clear the paging table and any other tables in the
*result\_table\_list* (both returned in the response) when they are done paging
through the results.  Output parameter *paging\_table* (and *result\_table\_list*)
will be empty if no paging table was created (e.g., when all the query results
were returned in the first call).

## Input Parameter Description

<ParamField body="statement" type="string">
  SQL statement (query, DML, or DDL) to be executed.
</ParamField>

<ParamField body="offset" type="long">
  A positive integer indicating the number of initial results to skip (this can be useful for paging through the results).

  The default value is 0. The minimum allowed value is 0. The maximum allowed value is MAX\_INT.
</ParamField>

<ParamField body="limit" type="long">
  A positive integer indicating the maximum number of results to be returned, or END\_OF\_SET (-9999) to indicate that the maximum number of results allowed by the server should be returned.  The number of records returned will never exceed the server's own limit, defined by the [max\_get\_records\_size](../../config/#config-main-general) parameter in the server configuration. Use output parameter *has\_more\_records* to see if more records exist in the result to be fetched, and input parameter *offset* and input parameter *limit* to request subsequent pages of results.

  The default value is -9999.
</ParamField>

<ParamField body="encoding" type="string">
  Specifies the encoding for returned records; either 'binary' or 'json'.

  The default value is `binary`.

  The supported values are:

  * binary
  * json
</ParamField>

<ParamField body="request_schema_str" type="string">
  Avro schema of input parameter *data*.

  The default value is ''.
</ParamField>

<ParamField body="data" type="array of bytes">
  An array of binary-encoded data for the records to be binded to the SQL query.  Or use *query\_parameters* to pass the data in JSON format.

  The default value is an empty array ( \[] ).
</ParamField>

<ParamField body="options" type="map of string to strings">
  Optional parameters.

  The default value is an empty map ( \{} ).

  <Expandable title="options">
    <ParamField body="cost_based_optimization">
      If *false*, disables the cost-based optimization of the given query.

      The default value is `false`.

      The supported values are:

      * true
      * false
    </ParamField>

    <ParamField body="distributed_joins">
      If *true*, enables the use of distributed joins in servicing the given query.  Any query requiring a distributed join will succeed, though hints can be used in the query to change the distribution of the source data to allow the query to succeed.

      The default value is `false`.

      The supported values are:

      * true
      * false
    </ParamField>

    <ParamField body="distributed_operations">
      If *true*, enables the use of distributed operations in servicing the given query. Any query requiring a distributed join will succeed, though hints can be used in the query to change the distribution of the source data to allow the query to succeed.

      The default value is `false`.

      The supported values are:

      * true
      * false
    </ParamField>

    <ParamField body="ignore_existing_pk">
      Specifies the record collision error-suppression policy for inserting into or updating a table with a [primary key](../../concepts/tables/#primary-keys), only used when primary key record collisions are rejected (*update\_on\_existing\_pk* is *false*).  If set to *true*, any record insert/update that is rejected for resulting in a primary key collision with an existing table record will be ignored with no error generated.  If *false*, the rejection of any insert/update for resulting in a primary key collision will cause an error to be reported.  If the specified table does not have a primary key or if *update\_on\_existing\_pk* is *true*, then this option has no effect.

      The default value is `false`.

      * **true**: Ignore inserts/updates that result in primary key collisions with existing records.
      * **false**: Treat as errors any inserts/updates that result in primary key collisions with existing records.
    </ParamField>

    <ParamField body="late_materialization">
      If *true*, Joins/Filters results  will always be materialized ( saved to result tables format).

      The default value is `false`.

      The supported values are:

      * true
      * false
    </ParamField>

    <ParamField body="paging_table">
      When specified (or *paging\_table\_ttl* is set), the system will create a paging table to hold the results of the query, when the output has more records than are in the response (i.e., when output parameter *has\_more\_records* is *true*). If the specified paging table exists, the records from the paging table are returned without re-evaluating the query.  It is the caller's responsibility to clear the output parameter *paging\_table* and other tables in the *result\_table\_list* (both returned in the response) when they are done with this query.
    </ParamField>

    <ParamField body="paging_table_ttl">
      Sets the [TTL](../../concepts/ttl/) of the paging table.  -1 indicates no timeout.  Setting this option will cause a paging table to be generated when needed. The output parameter *paging\_table* and other tables in the *result\_table\_list* (both returned in the response) will be automatically cleared after the TTL expires, if set to a positive number. However, it is still recommended that the caller clear these tables when they are done with this query.
    </ParamField>

    <ParamField body="parallel_execution">
      If *false*, disables the parallel step execution of the given query.

      The default value is `true`.

      The supported values are:

      * true
      * false
    </ParamField>

    <ParamField body="plan_cache">
      If *false*, disables plan caching for the given query.

      The default value is `true`.

      The supported values are:

      * true
      * false
    </ParamField>

    <ParamField body="prepare_mode">
      If *true*, compiles a query into an execution plan and saves it in query cache. Query execution is not performed and an empty response will be returned to user.

      The default value is `false`.

      The supported values are:

      * true
      * false
    </ParamField>

    <ParamField body="preserve_dict_encoding">
      If *true*, then columns that were dict encoded in the source table will be dict encoded in the projection table.

      The default value is `true`.

      The supported values are:

      * true
      * false
    </ParamField>

    <ParamField body="query_parameters">
      Query parameters in JSON array or arrays (for inserting multiple rows).  This can be used instead of input parameter *data* and input parameter *request\_schema\_str*.
    </ParamField>

    <ParamField body="results_caching">
      If *false*, disables caching of the results of the given query.

      The default value is `true`.

      The supported values are:

      * true
      * false
    </ParamField>

    <ParamField body="rule_based_optimization">
      If *false*, disables rule-based rewrite optimizations for the given query.

      The default value is `true`.

      The supported values are:

      * true
      * false
    </ParamField>

    <ParamField body="ssq_optimization">
      If *false*, scalar subqueries will be translated into joins.

      The default value is `true`.

      The supported values are:

      * true
      * false
    </ParamField>

    <ParamField body="ttl">
      Sets the [TTL](../../concepts/ttl/) of the intermediate result tables used in query execution.
    </ParamField>

    <ParamField body="update_on_existing_pk">
      Specifies the record collision policy for inserting into or updating a table with a [primary key](../../concepts/tables/#primary-keys). If set to *true*, any existing table record with primary key values that match those of a record being inserted or updated will be replaced by that record. If set to *false*, any such primary key collision will result in the insert/update being rejected and the error handled as determined by *ignore\_existing\_pk*.  If the specified table does not have a primary key, then this option has no effect.

      The default value is `false`.

      * **true**: Replace the collided-into record with the record inserted or updated when a new/modified record causes a primary key collision with an existing record.
      * **false**: Reject the insert or update when it results in a primary key collision with an existing record.
    </ParamField>

    <ParamField body="validate_change_column">
      When changing a column using alter table, validate the change before applying it. If *true*, then validate all values. A value too large (or too long) for the new type will prevent any change. If *false*, then when a value is too large or long, it will be truncated.

      The default value is `true`.

      The supported values are:

      * true
      * false
    </ParamField>

    <ParamField body="current_schema">
      Use the supplied value as the [default schema](../../concepts/schemas/#default-schema) when processing this SQL command.
    </ParamField>
  </Expandable>
</ParamField>

## Output Parameter Description

The Kinetica server embeds the endpoint response inside a standard response structure which contains status information and the actual response to the query.  Here is a description of the various fields of the wrapper:

<ResponseField name="status" type="String">
  'OK' or 'ERROR'
</ResponseField>

<ResponseField name="message" type="String">
  Empty if success or an error message
</ResponseField>

<ResponseField name="data_type" type="String">
  'execute\_sql\_response' or 'none' in case of an error
</ResponseField>

<ResponseField name="data" type="String">
  Empty string
</ResponseField>

<ResponseField name="data_str" type="JSON or String">
  This embedded JSON represents the result of the /execute/sql endpoint:

  <Expandable title="data_str">
    <ResponseField name="count_affected" type="long">
      The number of objects/records affected.
    </ResponseField>

    <ResponseField name="response_schema_str" type="string">
      Avro schema of output parameter *binary\_encoded\_response* or output parameter *json\_encoded\_response*.
    </ResponseField>

    <ResponseField name="binary_encoded_response" type="bytes">
      Avro binary encoded response.
    </ResponseField>

    <ResponseField name="json_encoded_response" type="string">
      Avro JSON encoded response.
    </ResponseField>

    <ResponseField name="total_number_of_records" type="long">
      Total/Filtered number of records.
    </ResponseField>

    <ResponseField name="has_more_records" type="boolean">
      Too many records. Returned a partial set.

      The supported values are:

      * true
      * false
    </ResponseField>

    <ResponseField name="paging_table" type="string">
      Name of the table that has the result records of the query. Valid, when output parameter *has\_more\_records* is *true*.  The caller should clear this and all tables in *result\_table\_list* when they are done querying.
    </ResponseField>

    <ResponseField name="info" type="map of string to strings">
      Additional information.

      The default value is an empty map ( \{} ).

      <Expandable title="info">
        <ResponseField name="count">
          Number of records without final limits applied.
        </ResponseField>

        <ResponseField name="result_table_list">
          List of tables, comma-separated, in addition to the output parameter *paging\_table*, created as result of the query. These should be cleared by the caller when they are done querying.
        </ResponseField>
      </Expandable>
    </ResponseField>
  </Expandable>

  Empty string in case of an error.
</ResponseField>