Temporary table support

[gabrielmcg44]

Hey all!

I am trying to create and use temporary tables using aiochclient, but I can't find anywhere a way of passing session_id (https://clickhouse.com/docs/en/sql-reference/statements/create/table#temporary-tables)

And therefore whenever I try to query the temporary table in a new query I get:

Unknown table expression identifier 'table_name'

I also tried doing it in a single statement such as

CREATE TEMPORARY TABLE temp_table_name AS
SELECT * FROM table_name;
SELECT * FROM temp_table_name;

but I got

DB::Exception: Syntax error (Multi-statements are not allowed):

Is there a current way of doing this with aiochclient?

[Maksim-Burtsev]

Hi!

When you init ChClient you can pass settings that will be sent in query params in requests to ClickHouse.

So you can pass session_id when init client and then you can create temporary tables and use them.

Here's an example:

import uuid

import asyncio
from aiohttp import ClientSession
from aiochclient import ChClient

CLICKHOUSE_URL = "your_clickhouse_url"


async def main() -> None:
    async with ClientSession() as session:
        client = ChClient(session, url=CLICKHOUSE_URL, session_id=str(uuid.uuid4())) # here you pass session_id
        await client.execute(
            """
            CREATE TABLE IF NOT EXISTS some_table
            (
                `a` Int8,
                `b` String,
                `c` Int8
            )
            ENGINE = MergeTree()
            ORDER BY a
        """
        )
        await client.execute(
            """
            INSERT INTO some_table VALUES
            (1, 'a', 1),
            (2, 'b', 2),
            (3, 'c', 3)
        """
        )

        await client.execute(
            """
            CREATE TEMPORARY TABLE temp_table_name AS
            SELECT * FROM some_table
        """
        )
        rows = await client.fetch("SELECT * FROM temp_table_name")
        print("Query result:", len(rows), rows)
        for row in rows:
            print(row)


if __name__ == "__main__":
    asyncio.run(main())