Support macros in sql queries

[andrey-mikhailov]

ClickHouse macros are extremely useful when you're working on clustered environment. This library is great, but it doesn't support them.

Steps to reproduce:

1. Configure clustered ClickHouse. I use 2 shards and 2 replicas.
2. Run the code

import asyncio
from aiochclient import ChClient
from aiohttp import ClientSession

async def main():
    async with ClientSession() as s:
        client = ChClient(s)
        await client.execute("drop table if exists user_shard on cluster '{cluster}'")

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

The string drop table if exists user_shard on cluster '{cluster}' is a valid sql code.

Expected behaviour: no errors, user_shard table is created on all nodes.

Observed behaviour There is an exception

Traceback (most recent call last):
  File "test.py", line 11, in <module>
    asyncio.run(main())
  File "/usr/local/Cellar/[email protected]/3.9.7_1/Frameworks/Python.framework/Versions/3.9/lib/python3.9/asyncio/runners.py", line 44, in run
    return loop.run_until_complete(main)
  File "/usr/local/Cellar/[email protected]/3.9.7_1/Frameworks/Python.framework/Versions/3.9/lib/python3.9/asyncio/base_events.py", line 642, in run_until_complete
    return future.result()
  File "test.py", line 8, in main
    await client.execute("drop table if exists user_shard on cluster '{cluster}'")
  File "venv/lib/python3.9/site-packages/aiochclient/client.py", line 233, in execute
    async for _ in self._execute(
  File "venv/lib/python3.9/site-packages/aiochclient/client.py", line 145, in _execute
    query = query.format(**query_params)
KeyError: 'cluster'

P.S. The library automatically tries to replace variables in curly braces. I found the following code in client.py file

    async def _execute(
        self,
        query: str,
        *args,
        json: bool = False,
        query_params: Optional[Dict[str, Any]] = None,
        query_id: str = None,
        decode: bool = True,
    ) -> AsyncGenerator[Record, None]:
        query_params = self._prepare_query_params(query_params)
        query = query.format(**query_params)

Is it possible to add some parameter to execute method to make such transformation optionally? If I comment the string "query = query.format(**query_params)" the method will work fine.

As a temporary solution I use the following trick

import asyncio
from aiochclient import ChClient
from aiohttp import ClientSession


async def main():
    async with ClientSession() as s:
        client = ChClient(s)
        await client.execute("drop table if exists user_shard on cluster {cluster}",
                             params={"cluster": "{cluster}"})


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

but it's not good because sql code is invalid. Creating Replicated* table is a more complicated case

CREATE TABLE IF NOT EXISTS user_ on cluster '{cluster}'
(
    userid       UInt64,
    emailaddress String,
)
 ENGINE = ReplicatedMergeTree('/clickhouse/{installation}/{cluster}/tables/{shard}/{database}/{table}', '{replica}')

[andrey-mikhailov]

The another solution is to escape curly braces by double braces. This code works

import asyncio
from aiochclient import ChClient
from aiohttp import ClientSession


async def main():
    async with ClientSession() as s:
        client = ChClient(s)
        await client.execute("drop table if exists user_shard on cluster '{{cluster}}'")


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

But in my real project I'm going to use Jinja templates for sql queries and double curly braces are reserved characters.