Executing Queries

This notebook shows how you can create and post queries through the ThanoSQL client. First, initialize a ThanoSQL client with your API token and engine URL.

from thanosql import ThanoSQL

client = ThanoSQL(
    api_token="THANOSQL_API_TOKEN",
    engine_url="THANOSQL_ENGINE_URL"
)

Direct Queries

You can use the client to execute any ThanoSQL or PSQL queries. By default, the client will assume that the query is in ThanoSQL, which can also interpret PSQL queries. If you know you are about to execute queries in PSQL, you can change query_type to psql.

# Refer to https://docs.thanosql.ai/1.5/en/how-to_guides/ThanoSQL_query/BUILD_MODEL_SYNTAX/ for more information

thanosql_query = """
BUILD MODEL my_mnist_simclr
USING SimCLR
OPTIONS (
    image_col='image_path',
    max_epochs=1,
    overwrite=True
    )
AS 
SELECT * 
FROM mnist_train
"""
thanosql_res = client.query.execute(thanosql_query)
thanosql_res

# Refer to https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-create-table/ for more information

psql_query = """
CREATE TABLE accounts (
    user_id SERIAL PRIMARY KEY, 
    username VARCHAR (50) UNIQUE NOT NULL, 
    password VARCHAR (50) NOT NULL, 
    email VARCHAR (255) UNIQUE NOT NULL, 
    created_at TIMESTAMP NOT NULL, 
    last_login TIMESTAMP
);
"""
psql_res = client.query.execute(query=psql_query, query_type="psql")
psql_res

If the query request is successfully sent, the JSON dump version of the response will look like:

# res.model_dump(mode='json')

{
  "query_id": "string",
  "statement_type": "string",
  "start_time": "2024-03-21T07:25:13.060Z",
  "end_time": "2024-03-21T07:25:13.060Z",
  "query": "string",
  "referer": "string",
  "state": "string",
  "destination_table_name": "string",
  "destination_schema": "string",
  "error_result": "string",
  "created_at": "2024-03-21T07:25:13.060Z",
  "records": {
    "data": [
      {}
    ],
    "total": 0
  }
}

regardless of whether the query itself is successful or not. If the query encounters an error, the error_result field will record the error. Otherwise, error_result will be empty. If the query produces a table, the table will be saved to destination_table_name in destination_schema. If the query is not a CREATE statement, a random name will be given to the table, unless specified beforehand. You can do this by setting schema and table_name. You can also set overwrite to allow the new table to replace old one with the same name. By default, this behavior is not allowed and will result in an error. Additionally, you can set max_results to a positive value in order to show the records in the table created by the query. By default, max_results has a value of 100 and there is a maximum limit. In the following example:

select_query = "SELECT * FROM accounts"
select_res = client.query.execute(query=select_query, schema="my_schema_1", table_name="my_table_1", overwrite=True, max_results=100)

If the table accounts is nonempty, we can get its records by

select_res.records

The results will be presented as a Records class, which consists of the data contents and total number. We can optionally select to view the records as a Pandas DataFrame by using the to_df() method of the Records class.

df = select_res.records.to_df()
df

Querying Using Templates

You can also utilize query templates to create templates with a certain pattern. For example, if we have the following template:

# (Refer to https://docs.thanosql.ai/1.5/en/how-to_guides/ThanoSQL_query/PRINT_SYNTAX/ for more information)

template_query = """
PRINT {{ print_type.upper() }}
OPTIONS (
    {{ print_type }}_col='{{ col_name }}'
    )
AS
SELECT *
FROM {{ table_name }}
"""

There are three parameters:

parameters = ["print_type", "col_name", "table_name"]

We can use the template by passing in the required parameters. Let’s say that the template is saved with template_name my_query_template_1 with template_id 42. We can do the following:

params = {
    "print_type": "image",
    "col_name": "image",
    "table_name": "image_table"
}
res = client.query.execute(template_id=42, parameters=params)

We can check whether the parameters are passed successfully by inspecting the response of the query execution.

completed_query = """
PRINT IMAGE
OPTIONS (
    image_col='image'
    )
AS
SELECT *
FROM image_table
"""

assert res.query == completed_query

Note that you can also do the following instead:

res = client.query.execute(template_name="my_query_template_1", parameters=params)

but you cannot use both template_id and template_name at the same time, even if both refer to the same template. In order to run this example, replace my_query_template_1 and 42 with the name and ID of the query template you want to use. For more information on how to manage query templates, head over to the query templates tutorial section.

You can even use query templates directly, without having to save them to database beforehand. However, note that when query and parameters are both non-empty, template_id and template_name should not be set.

client.query.execute(query=template_query, parameters=params)

Listing Query Logs

You can see the list of past query logs using the client. You can use the search parameter to search for keywords in the queries, offset to specify a results offset, and limit to set the limit of results returned. You can also call list() without any parameters.

res = client.query.log.list(search="keyword", offset=1, limit=10)
print(f"Total query logs: {res['total']}")
for query_log in res["query_logs"]:
    print(f"Query #{query_log.query_id}: {query_log.query}")