I would like to utilise kubectl connection plugin: https://docs.ansible.com/ansible/latest/plugins/connection/kubectl.html but having struggle to figure out how to actually do that.

The fine manual describes how one uses connection plugins, and while it is possible to use in in tasks, that is unlikely to make any sense unless your inventory started with Pods.

The way I have seen that connection used is to start by identifying the Pods against which you might want to take action, and then run a playbook against a unique group for that purpose:

- hosts: all
  tasks:
  - set_fact:
      # this is *just an example for brevity*
      # in reality you would use `k8s:` or `kubectl get -o name pods -l my-selector=my-value` to get the pod names
      pod_names:
      - nginx-12345
      - nginx-3456
  - add_host:
      name: '{{ item }}'
      groups:
      - my-pods
    with_items: '{{ pod_names }}'

- hosts: my-pods
  connection: kubectl
  tasks:
  # and now you are off to the races
  - command: ps -ef
  # watch out if the Pod doesn't have a working python installed
  # as you will have to use raw: instead
  # (and, of course, disable "gather_facts: no")
  - raw: ps -ef

Maybe you can use like this...

 - shell: |

     kubectl exec -i -n {{ namespace }} {{ pod_name }} -- bash -c 'clickhouse-client --query "INSERT INTO customer FORMAT CSV"
 --user=test --password=test < /mnt/azure/azure/test/test.tbl'

First install k8s collections

ansible-galaxy collection install community.kubernetes

and here is play-book, it will sort all pods and run a command in every pod

---
- 
  hosts: localhost

  vars_files: 
    - vars/main.yaml 

  collections:
    - community.kubernetes    

  tasks:     
    -
      name: Get the pods in the specific namespace
      k8s_info:
        kubeconfig: '{{ k8s_kubeconfig }}'
        kind: Pod
        namespace: test
      register: pod_list


    - 
      name: Print pod names
      debug:
         msg: "pod_list: {{ pod_list | json_query('resources[*].status.podIP')  }} "

    - set_fact:
        pod_names: "{{pod_list|json_query('resources[*].metadata.name')}}"

    - 
      k8s_exec:
        kubeconfig: '{{ k8s_kubeconfig }}'
        namespace: "{{ namespace  }}"
        pod: "{{ item.metadata.name }}"
        command: apt update
      with_items: "{{ pod_list.resources }}"
      register: exec
      loop_control:
        label: "{{ item.metadata.name }}"