# SQL Processor Deployment

Lenses can be used to define & deploy stream processing applications that read from Kafka and write back to Kafka with SQL. They are based on the Kafka Stream framework. They are known as SQL Processors.

SQL processing of real-time data can run in 2 modes:

* SQL In-Process - the workload runs inside of the Lenses Agent.
* SQL in Kubernetes - the workload runs & scale on your Kubernetes cluster.

Which mode the SQL Processors will run as should be defined within the **lenses.conf** before Lenses is started.

## In-Process Mode <a href="#sql-in-process" id="sql-in-process"></a>

In this mode, SQL processors run as part of the Agent process, sharing resources, memory, and CPU time with the rest of the platform.

{% hint style="warning" %}
This mode of operation is meant to be used for development only.

As such, the agent will not allow the creation of more than 50 SQL Processors in *In Process* mode, as this could impact the platform's stability and performance negatively.

For production, use the `KUBERNETES` mode for maximum flexibility and scalability.
{% endhint %}

Set the execution configuration to `IN_PROC`

```bash
# Set up Lenses SQL processing engine
lenses.sql.execution.mode = "IN_PROC"
```

Set the directory to store the internal state of the SQL Processors:

```bash
lenses.sql.state.dir = "/tmp/sql-kstream-state"
```

### TLS connections to Kafka and Schema Registries <a href="#connections-management" id="connections-management"></a>

SQL processors use the same connection details that Agent uses to speak to Kafka and Schema Registry. The following properties are mounted, if present, on the file system for each processor:

* **Kafka**
  1. SSLTruststore
  2. SSLKeystore
* **Schema Registry**
  1. SSL Keystore
  2. SSL Truststore

The file structure created by applications is the following: **/run/\[lenses\_installation\_id]/applications/**

{% hint style="warning" %}
**Keep in mind**\
Lenses require an installation folder with write permissions. The following are tried:

1. /run
2. /tmp
   {% endhint %}

## Kubernetes Mode <a href="#sql-on-kubernetes" id="sql-on-kubernetes"></a>

Kubernetes can be used to deploy SQL Processors. To configure Kubernetes, set the mode to `KUBERNETES` and configure the location of the kubeconfig file.

When the Agent is deployed inside Kubernetes, the **lenses.kubernetes.config.file** configuration entry should be set to an empty string. The Kubernetes client will auto-configure from the pod it is deployed in.

The SQL Processor docker image is live in Dockerhub.

{% code fullWidth="false" %}

```properties
lenses.sql.execution.mode = KUBERNETES
# kubernetes configuration
lenses.kubernetes.config.file = "/home/lenses/.kube/config"
lenses.kubernetes.service.account = "default"
#lenses.kubernetes.processor.image.name = "" # Only needed if you use a custom image
#lenses.kubernetes.processor.image.tag = ""  # Only needed if you use a custom image

# Only needed if you want to tune the buffer size for incoming events from Kubernetes
#lenses.deployments.errors.buffer.size = 1000

# Only needed if you want to tune the buffer size for incoming errors from Kubernetes WS communication
#lenses.deployments.events.buffer.size = 10000
```

{% endcode %}

### Custom Serdes <a href="#custom-serde" id="custom-serde"></a>

Custom serdes should be embedded in a new Lenses SQL processor Docker image.

To build a custom Docker image, create the following directory structure:

```bash
mkdir -p processor-docker/serde
```

Copy your serde jar files under processor-docker/serde.

Create `Dockerfile` containing:

```dockerfile
FROM lensesioextra/sql-processor:4.2

ADD serde /opt/serde
ENV LENSES_SQL_RUNNERS_SERDE_CLASSPATH_OPTS=/opt/serde
```

Build the Docker.

```bash
cd processor-docker
docker build -t example/lsql-processor .
```

Once the image is deployed in your registry, please set Lenses to use it (lenses.conf):

```bash
lenses.kubernetes.processor.image.name = "your/image-name"
lenses.kubernetes.processor.image.tag = "your-tag"
```

{% hint style="warning" %}
**Don't use the `LPFP_` prefix.**

Internally, Lenses prefixes all its properties with `LPFP_`.

Avoid passing custom environment variables starting with `LPFP_` as it may cause the processors to fail.
{% endhint %}

## Use Role/RoleBinging to deploy Lenses processors

To deploy Lenses Processors in Kubernetes the suggested way is to activate RBAC in Cluster level through Helm `values.yaml`:

```
rbacEnable: true
```

{% hint style="success" %}
If you want to limit the permissions Lenses has against your Kubernetes cluster, you can use `Role/RoleBinding` resources instead.
{% endhint %}

To achieve this you need to create a **Role** and a **RoleBinding** resource in the namespace you want the processors deployed to:

```yaml
kind: Role
apiVersion: rbac.authorization.k8s.io/v1
metadata:
  name: [ROLE_NAME]
  namespace: [PROCESSORS_NAMESPACE]
rules:
- apiGroups: [""]
  resources:
    - namespaces
    - persistentvolumes
    - persistentvolumeclaims
    - pods/log
  verbs:
    - list
    - watch
    - get
    - create
- apiGroups: ["", "extensions", "apps"]
  resources:
    - pods
    - replicasets
    - deployments
    - ingresses
    - secrets
    - statefulsets
    - services
  verbs:
    - list
    - watch
    - get
    - update
    - create
    - delete
    - patch
- apiGroups: [""]
  resources:
    - events
  verbs:
    - list
    - watch
    - get
```

```yaml
kind: RoleBinding
apiVersion: rbac.authorization.k8s.io/v1
metadata:
  name: [ROLE_BINDING_NAME]
  namespace: [PROCESSOR_NAMESPACE]
subjects:
- kind: ServiceAccount
  namespace: [LENSES_NAMESPACE]
  name: [SERVICE_ACCOUNT_NAME]
roleRef:
  kind: Role
  name: [ROLE_NAME]
  apiGroup: rbac.authorization.k8s.io
```

example for:

* Lenses namespace = `lenses-ns`
* Processor namespace = `lenses-proc-ns`

```yaml
kind: Role
apiVersion: rbac.authorization.k8s.io/v1
metadata:
  name: processor-role
  namespace: lenses-proc-ns
rules:
- apiGroups: [""]
  resources:
    - namespaces
    - persistentvolumes
    - persistentvolumeclaims
    - pods/log
  verbs:
    - list
    - watch
    - get
    - create
- apiGroups: ["", "extensions", "apps"]
  resources:
    - pods
    - replicasets
    - deployments
    - ingresses
    - secrets
    - statefulsets
    - services
  verbs:
    - list
    - watch
    - get
    - update
    - create
    - delete
    - patch
- apiGroups: [""]
  resources:
    - events
  verbs:
    - list
    - watch
    - get
```

```yaml
kind: RoleBinding
apiVersion: rbac.authorization.k8s.io/v1
metadata:
  name: processor-role-binding
  namespace: lenses-proc-ns
subjects:
- kind: ServiceAccount
  namespace: lenses-ns
  name: default
roleRef:
  kind: Role
  name: processor-role
  apiGroup: rbac.authorization.k8s.io
```

{% hint style="info" %}
You can repeat this for as many namespaces you may want Lenses to have access to.
{% endhint %}

Finally you need to define in Lenses configuration which namespaces can Lenses access. To achieve this amend `values.yaml` to contain the following:

```toml
lenses:
  append:
    conf: |
      lenses.kubernetes.namespaces = {
        incluster = [
          "[PROCESSORS NAMESPACE]"
        ]
      }      
```

example:

```toml
lenses:
  append:
    conf: |
      lenses.kubernetes.namespaces = {
        incluster = [
          "lenses-processors"
        ]
      }      
```