execute_sql_and_decode

SQL statement (query, DML, or DDL) to be executed.

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.

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 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.

Specifies the encoding for returned records; either ‘binary’ or ‘json’. Allowed values are:

• binary

• json

The default value is ‘binary’.

Avro schema of input parameter data. The default value is ‘’.

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 list ( [] ). The user can provide a single element (which will be automatically promoted to a list internally) or a list.

Optional parameters. Allowed keys are:

• cost_based_optimization – If false, disables the cost-based optimization of the given query. Allowed values are:

  • true

  • false

  The default value is ‘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. Allowed values are:

  • true

  • false

  The default value is ‘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. Allowed values are:

  • true

  • false

  The default value is ‘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. Allowed values are:

  • 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.

  The default value is ‘false’.

• late_materialization – If true, Joins/Filters results will always be materialized ( saved to result tables format). Allowed values are:

  • true

  • false

  The default value is ‘false’.

• 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.

• paging_table_ttl – Sets the 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.

• parallel_execution – If false, disables the parallel step execution of the given query. Allowed values are:

  • true

  • false

  The default value is ‘true’.

• plan_cache – If false, disables plan caching for the given query. Allowed values are:

  • true

  • false

  The default value is ‘true’.

• 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. Allowed values are:

  • true

  • false

  The default value is ‘false’.

• preserve_dict_encoding – If true, then columns that were dict encoded in the source table will be dict encoded in the projection table. Allowed values are:

  • true

  • false

  The default value is ‘true’.

• 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.

• results_caching – If false, disables caching of the results of the given query. Allowed values are:

  • true

  • false

  The default value is ‘true’.

• rule_based_optimization – If false, disables rule-based rewrite optimizations for the given query. Allowed values are:

  • true

  • false

  The default value is ‘true’.

• ssq_optimization – If false, scalar subqueries will be translated into joins. Allowed values are:

  • true

  • false

  The default value is ‘true’.

• ttl – Sets 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. Allowed values are:

  • 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.

  The default value is ‘false’.

• 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. Allowed values are:

  • true

  • false

  The default value is ‘true’.

• current_schema – Use the supplied value as the default schema when processing this SQL command.

The default value is an empty dict ( ).

The record type expected in the results, or None to determine the appropriate type automatically. If known, providing this may improve performance in binary mode. Not used in JSON mode. The default value is None.

If True, then OrderedDict objects will be returned, where string sub-type columns will have their values converted back to strings; for example, the Python datetime structs, used for datetime type columns would have their values returned as strings. If False, then Record objects will be returned, which for string sub-types, will return native or custom structs; no conversion to string takes place. String conversions, when returning OrderedDicts, incur a speed penalty, and it is strongly recommended to use the Record object option instead. If True, but none of the returned columns require a conversion, then the original Record objects will be returned. Default value is True.

Indicates if the decoded records will be transposed to be column-major or returned as is (row-major). Default value is True.

The number of objects/records affected.

Avro schema of output parameter binary_encoded_response or output parameter json_encoded_response.

Total/Filtered number of records.

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

• True

• False

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.

Additional information. Allowed keys are:

• count – Number of records without final limits applied.

• 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.

The default value is an empty dict ( ).

A list of Record objects which contain the decoded records.