{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Executing Queries\n",
    "\n",
    "This notebook shows how you can create and post queries through the ThanoSQL client.\n",
    "First, initialize a ThanoSQL client with your API token and engine URL."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from thanosql import ThanoSQL\n",
    "\n",
    "client = ThanoSQL(\n",
    "    api_token=\"THANOSQL_API_TOKEN\",\n",
    "    engine_url=\"THANOSQL_ENGINE_URL\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Direct Queries"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "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`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Refer to https://docs.thanosql.ai/1.5/en/how-to_guides/ThanoSQL_query/BUILD_MODEL_SYNTAX/ for more information\n",
    "\n",
    "thanosql_query = \"\"\"\n",
    "BUILD MODEL my_mnist_simclr\n",
    "USING SimCLR\n",
    "OPTIONS (\n",
    "    image_col='image_path',\n",
    "    max_epochs=1,\n",
    "    overwrite=True\n",
    "    )\n",
    "AS \n",
    "SELECT * \n",
    "FROM mnist_train\n",
    "\"\"\"\n",
    "thanosql_res = client.query.execute(thanosql_query)\n",
    "thanosql_res"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Refer to https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-create-table/ for more information\n",
    "\n",
    "psql_query = \"\"\"\n",
    "CREATE TABLE accounts (\n",
    "    user_id SERIAL PRIMARY KEY, \n",
    "    username VARCHAR (50) UNIQUE NOT NULL, \n",
    "    password VARCHAR (50) NOT NULL, \n",
    "    email VARCHAR (255) UNIQUE NOT NULL, \n",
    "    created_at TIMESTAMP NOT NULL, \n",
    "    last_login TIMESTAMP\n",
    ");\n",
    "\"\"\"\n",
    "psql_res = client.query.execute(query=psql_query, query_type=\"psql\")\n",
    "psql_res"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If the query request is successfully sent, the JSON dump version of the response will look like:\n",
    "\n",
    "```python\n",
    "# res.model_dump(mode='json')\n",
    "\n",
    "{\n",
    "  \"query_id\": \"string\",\n",
    "  \"statement_type\": \"string\",\n",
    "  \"start_time\": \"2024-03-21T07:25:13.060Z\",\n",
    "  \"end_time\": \"2024-03-21T07:25:13.060Z\",\n",
    "  \"query\": \"string\",\n",
    "  \"referer\": \"string\",\n",
    "  \"state\": \"string\",\n",
    "  \"destination_table_name\": \"string\",\n",
    "  \"destination_schema\": \"string\",\n",
    "  \"error_result\": \"string\",\n",
    "  \"created_at\": \"2024-03-21T07:25:13.060Z\",\n",
    "  \"records\": {\n",
    "    \"data\": [\n",
    "      {}\n",
    "    ],\n",
    "    \"total\": 0\n",
    "  }\n",
    "}\n",
    "```\n",
    "\n",
    "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:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "select_query = \"SELECT * FROM accounts\"\n",
    "select_res = client.query.execute(query=select_query, schema=\"my_schema_1\", table_name=\"my_table_1\", overwrite=True, max_results=100)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If the table `accounts` is nonempty, we can get its records by\n",
    "\n",
    "```python\n",
    "select_res.records\n",
    "```\n",
    "\n",
    "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."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = select_res.records.to_df()\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Querying Using Templates"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can also utilize [query templates](./managing_query_templates.ipynb) to create templates with a certain pattern. For example, if we have the following template:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# (Refer to https://docs.thanosql.ai/1.5/en/how-to_guides/ThanoSQL_query/PRINT_SYNTAX/ for more information)\n",
    "\n",
    "template_query = \"\"\"\n",
    "PRINT {{ print_type.upper() }}\n",
    "OPTIONS (\n",
    "    {{ print_type }}_col='{{ col_name }}'\n",
    "    )\n",
    "AS\n",
    "SELECT *\n",
    "FROM {{ table_name }}\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are three parameters:\n",
    "\n",
    "```python\n",
    "parameters = [\"print_type\", \"col_name\", \"table_name\"]\n",
    "```\n",
    "\n",
    "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:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "params = {\n",
    "    \"print_type\": \"image\",\n",
    "    \"col_name\": \"image\",\n",
    "    \"table_name\": \"image_table\"\n",
    "}\n",
    "res = client.query.execute(template_id=42, parameters=params)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can check whether the parameters are passed successfully by inspecting the response of the query execution."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "completed_query = \"\"\"\n",
    "PRINT IMAGE\n",
    "OPTIONS (\n",
    "    image_col='image'\n",
    "    )\n",
    "AS\n",
    "SELECT *\n",
    "FROM image_table\n",
    "\"\"\"\n",
    "\n",
    "assert res.query == completed_query"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that you can also do the following instead:\n",
    "\n",
    "```python\n",
    "res = client.query.execute(template_name=\"my_query_template_1\", parameters=params)\n",
    "```\n",
    "\n",
    "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](./managing_query_templates.ipynb) section."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "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."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "client.query.execute(query=template_query, parameters=params)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Listing Query Logs"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "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."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "res = client.query.log.list(search=\"keyword\", offset=1, limit=10)\n",
    "print(f\"Total query logs: {res['total']}\")\n",
    "for query_log in res[\"query_logs\"]:\n",
    "    print(f\"Query #{query_log.query_id}: {query_log.query}\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "name": "python",
   "version": "3.11.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}