/execute/sql

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

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

See SQL Support for the complete set of supported SQL commands.

Input Parameter Description

NameTypeDescription
statementstringSQL statement (query, DML, or DDL) to be executed
offsetlongA 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.
limitlongA 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 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 & input parameter limit to request subsequent pages of results. The default value is -9999.
encodingstring

Specifies the encoding for returned records; either 'binary' or 'json'. The default value is binary. The supported values are:

  • binary
  • json
request_schema_strstringAvro schema of input parameter data. The default value is ''.
dataarray of bytesAn 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 ( [] ).
optionsmap of string to strings

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

Supported Parameters (keys)Parameter Description
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
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
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
ignore_existing_pk

Specifies the record collision error-suppression policy for inserting into or updating a table with a primary key, 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.

Supported ValuesDescription
trueIgnore inserts/updates that result in primary key collisions with existing records
falseTreat as errors any inserts/updates that result in primary key collisions with existing records
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
paging_tableWhen empty or the specified paging table not exists, the system will create a paging table and return when query output has more records than the user asked. If the paging table exists in the system, the records from the paging table are returned without evaluating the query.
paging_table_ttlSets the TTL of the paging table.
parallel_execution

If false, disables the parallel step execution of the given query. The default value is true. The supported values are:

  • true
  • false
plan_cache

If false, disables plan caching for the given query. The default value is true. The supported values are:

  • true
  • false
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
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
query_parametersQuery 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.
results_caching

If false, disables caching of the results of the given query The default value is true. The supported values are:

  • true
  • false
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
ssq_optimization

If false, scalar subqueries will be translated into joins The default value is true. The supported values are:

  • true
  • false
ttlSets the TTL of the intermediate result tables used in query execution.
update_on_existing_pk

Specifies the record collision policy for inserting into or updating a table with a primary key. 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.

Supported ValuesDescription
trueReplace the collided-into record with the record inserted or updated when a new/modified record causes a primary key collision with an existing record
falseReject the insert or update when it results in a primary key collision with an existing record
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
current_schemaUse the supplied value as the default schema when processing this SQL command.

Output Parameter Description

The GPUdb 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:

NameTypeDescription
statusString'OK' or 'ERROR'
messageStringEmpty if success or an error message
data_typeString'execute_sql_response' or 'none' in case of an error
dataStringEmpty string
data_strJSON or String

This embedded JSON represents the result of the /execute/sql endpoint:

NameTypeDescription
count_affectedlongThe number of objects/records affected.
response_schema_strstringAvro schema of output parameter binary_encoded_response or output parameter json_encoded_response.
binary_encoded_responsebytesAvro binary encoded response.
json_encoded_responsestringAvro JSON encoded response.
total_number_of_recordslongTotal/Filtered number of records.
has_more_recordsboolean

Too many records. Returned a partial set. Possible values are:

  • true
  • false
paging_tablestringName of the table that has the result records of the query. Valid, when output parameter has_more_records is true (Subject to config.paging_tables_enabled)
infomap of string to strings

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

Possible Parameters (keys)Parameter Description
countNumber of records in the final table

Empty string in case of an error.