How to Use System Settings in REST API

Introduction

System Settings in Firebolt can alter how queries are executed, providing more control over performance and behavior. This is particularly useful when you need to override default settings for specific queries. By the end of this article, you will know how to incorporate system settings into your REST API calls to Firebolt.

TL;DR

  • System settings control query execution behavior.

  • Override settings parameters in the REST API.

  • Settings are applied per query, not globally.

Step-by-Step Guide

Step 1: Understanding System Settings

Firebolt system settings can adjust various aspects of query execution, such as time zone, parsing for literal strings, and limit the number of result rows. Typically, the default settings are adequate for most use cases, but you have the flexibility to apply custom setting if needed. See full System Settings list.

Step 2: Changing System Settings

To change a system setting like the timezone, you would typically use the SET command in SQL. However, for REST API calls, this command must be embedded in the URL since standalone SET commands are not supported in the API.

Step 3: Embedding Settings in REST API URL

When using the REST API, you embed the system setting in the URL. For example, to set the time_zone to UTC, you modify the API call URL to include this parameter.

Note that the Authentication Token as well as User Engine URL should be filled as part of the request. More about retrieving those can be find on Use tokens for authentication and Get a user engine URL.

Example API call:


curl --location 'https://<user engine URL>?engine=<engine_name>&database=<database_name>&time_zone=UTC' \

--header 'Authorization: Bearer <authentication_token>' \

--data "SELECT TIMESTAMPTZ '1996-09-03 11:19:33.123456 Europe/Berlin'"
## Output: 1996-09-03 09:19:33.123456+00

Step 4: Applying Settings Per Query

Settings applied in the URL are only effective for the duration of that specific query execution. Each subsequent API call must include the necessary settings if they need to be applied again.