Introduction
System settings in Firebolt can alter how queries are executed, providing more control over performance and behavior. By utilizing the Python SDK, these settings can be applied to all queries within a connection. This guide demonstrates how to enable and reset these settings effectively. By the end of this article, you will know how to manage system settings using the Firebolt Python SDK to optimize your queries.
TL;DR
-
System settings can be applied to queries via the Python SDK.
-
Settings are enabled for the duration of the connection.
-
Settings can be reset to default values within the connection.
-
Asynchronous query execution is possible using the SDK.
Step-by-Step Guide
Step 1: Establish a Connection
First, establish a connection to your Firebolt database using the necessary credentials. This connection will be used to execute queries and apply system settings.
Example code:
from firebolt.db import connect
from firebolt.client.auth import ClientCredentials
id = "<your_user_id>"
secret = "<your_secred_id>"
engine_name = "<your_engine_name>"
database_name = "<your_database_name>"
account_name = "<your_account_name>"
connection = connect(
database=database_name,
account_name=account_name,
auth=ClientCredentials(id, secret)
)
cursor = connection.cursor()
Step 2: Enable a System Setting
Next, apply a required system setting which will affect all subsequent queries in the current connection. In the example below, the time_zone setting is enabled to specify the session time zone.
Example code:
# This will set the time_zone setting to the required time zone for every query executed in the connection
cursor.execute("SET time_zone = 'UTC';")
cursor.execute("SELECT TIMESTAMPTZ '1996-09-03 11:19:33.123456 Europe/Berlin';")
print(cursor.fetchone()) # Output: 1996-09-03 09:19:33.123456+00
cursor.execute("SET time_zone = 'Jamaica';")
cursor.execute("SELECT TIMESTAMPTZ '2023-1-29 12:21:49';")
print(cursor.fetchone()) # Output: 2023-01-29 12:21:49-05
See full System Settings list.
Step 3 - Optional: Reset System Settings
If you need to revert the settings back to their default values within the connection, you can use the flush_parameters method. This ensures that any specific settings applied are cleared.
Example code:
# This will reset all settings back to default for the connection
cursor.flush_parameters()
cursor.execute("SELECT TIMESTAMPTZ '1996-09-03 11:19:33.123456 Europe/Berlin';")
print(cursor.fetchone()) # Output: 1996-09-03 09:19:33.123456+00
cursor.execute("SELECT TIMESTAMPTZ '2023-1-29 12:21:49';")
print(cursor.fetchone()) # Output: 2023-01-29 12:21:49+00
cursor.close()
Step 4: Execute Queries Asynchronously
Asynchronous Python SDK functionality is used to write concurrent code. Unlike in a synchronous approach, when executing a query is a blocking operation, this approach allows doing other processing or queries while the original query is waiting on the network or the server to respond. This is especially useful when executing slower queries.
Make sure you’re familiar with the Asyncio approach before using asynchronous Python SDK, as it requires special async/await syntax.
Example code:
# This example illustrates a simple query execution via the async Python SDK
from asyncio import run
from firebolt.async_db import connect as async_connect
from firebolt.client.auth import ClientCredentials
async def run_query():
id = "<your_service_account_id>"
secret = "<your_service_account_secret>"
engine_name = "<your_engine>"
database_name = "<your_database>"
account_name = "<your_account>"
query = "select * from games;"
async with await async_connect(
engine_name=engine_name,
database=database_name,
account_name=account_name,
auth=ClientCredentials(id, secret),
) as connection:
cursor = connection.cursor()
# Asyncronously execute a query
rowcount = await cursor.execute(query)
# Asyncronously fetch a result
single_row = await cursor.fetchone()
print(single_row) # Print single row
multiple_rows = await cursor.fetchmany(5)
print(multiple_rows) # Print multiple rows
all_remaining_rows = await cursor.fetchall()
print(all_remaining_rows) # Print all remaining rows
# Run async `run_query` from the synchronous context of your scriptrun(run_query())
Full Example Code
from firebolt.db import connect
from firebolt.client.auth import ClientCredentials
id = "<your_user_id>"
secret = "<your_secret_id>"
engine_name = "<your_engine_name>"
database_name = "<your_database_name>"
account_name = "<your_account_name>"
connection = connect(
database=database_name,
account_name=account_name,
auth=ClientCredentials(id, secret)
)
cursor = connection.cursor()
cursor.execute("SET time_zone = 'UTC';")
cursor.execute("SELECT TIMESTAMPTZ '1996-09-03 11:19:33.123456 Europe/Berlin';")
print(cursor.fetchone()) # Output: 1996-09-03 09:19:33.123456+00
cursor.execute("SET time_zone = 'Jamaica';")
cursor.execute("SELECT TIMESTAMPTZ '2023-1-29 12:21:49';")
print(cursor.fetchone()) # Output: 2023-01-29 12:21:49-05
cursor.flush_parameters()
cursor.execute("SELECT TIMESTAMPTZ '1996-09-03 11:19:33.123456 Europe/Berlin';")
print(cursor.fetchone()) # Output: 1996-09-03 09:19:33.123456+00
cursor.execute("SELECT TIMESTAMPTZ '2023-1-29 12:21:49';")
print(cursor.fetchone()) # Output: 2023-01-29 12:21:49+00
cursor.close()
from asyncio import run
from firebolt.async_db import connect as async_connect
# from firebolt.client.auth import ClientCredentials - already defined in Step 1
async def run_query():
query = "select * from games;"
async with await async_connect(
engine_name=engine_name,
database=database_name,
account_name=account_name,
auth=ClientCredentials(id, secret),
) as connection:
cursor = connection.cursor()
# Asynchronously execute a query
rowcount = await cursor.execute(query)
# Asynchronously fetch a result
single_row = await cursor.fetchone()
print(single_row) # Print single row
multiple_rows = await cursor.fetchmany(5)
print(multiple_rows) # Print multiple rows
all_remaining_rows = await cursor.fetchall()
print(all_remaining_rows) # Print all remaining rows
# Run async `run_query` from the synchronous context of your script
run(run_query())