ksqlDB

Kpow's integration with ksqlDB offers the full capabilities of ksqlDB backed by Kpow's enterprise features such as Role Based Access Control and Data policies.

Like all other actions performed within Kpow, an Data governance (Audit log) is kept for data governance when issuing any ksqlDB action.

All ksqlDB features seamlessly integrate with other Kpow managed resources such as Connect clusters and Schema registries.

This documentation is for the usage of ksqlDB features within Kpow. For setting up and configuring ksqlDB with Kpow, see: ksqlDB.

Executing SQL Statements

With Kpow's SQL console, users can effortlessly execute SQL statements against a ksqlDB server, enjoying the benefits of a fully featured editor with syntax highlighting, linting, and autocomplete, all while being secured by Kpow's robust role-based access control integration.

Refer to ksqlDB's documentation for a quick reference on available SQL statements you can execute.

To execute SQL statements, navigate to the "Execute" tab, select "Statement" as the type.

Import SQL from file

You can also import a ksql file into the query editor by clicking on Load SQL from file.

And then follow the instructions on the dialog box.

RBAC configuration

The KSQLDB_EXECUTE action allows users to execute statements against a ksqlDB server. For example CREATE STREAMS and DROP STREAMS statements.

This action will not allow users to execute query statements (push or pull, eg SELECT ...) against a ksqlDB server. Querying is controlled by the KSQLDB_QUERY action.

Simple Access Controls

If you are using Simple Access Control (environment variables) you will need to set ALLOW_KSQLDB_EXECUTE=true to enable executing of SQL statements.

RBAC Policy

If you are using a Role Based Access Control yaml file, you will need to set a policy like the following:

  - actions:
      - KSQLDB_EXECUTE
    effect: Allow
    resource: ["ksqldb", "ksqldb-prod-uat"],
    role: "kafka-users"

The above RBAC policy will allow any user with the role kafka-users to execute SQL statements against the ksqldb-prod-uat ksqlDB resource.

RBAC Notes

The KSQLDB_EXECUTE action is only as granular as a ksqlDB server. Unlike querying, there is no way to allow ksqlDB statements to only be executed against a specified source (eg, topic or stream).

Queries

Push Queries

Push queries are identified by the EMIT CHANGES clause. By running a push query, the client (Kpow) will receive a message for every change that occurs on the stream.

Push query functionality is coming to Kpow soon.

Pull Queries

Pull queries return the current state to the client (Kpow), and then terminate. In that sense they are similar to Kpow's Data inspect queries that are also bounded.

Refer to ksqlDB's quick reference for more information about SELECT (pull query).

To execute a pull query, navigate to the "Execute" tab and select "Pull Query" as the type.

RBAC configuration

The KSQLDB_QUERY action allows users to execute push or pull SQL statements against a ksqlDB server.

Simple Access Controls

If you are using Simple Access Control (environment variables) you will need to set ALLOW_KSQLDB_QUERY=true to enable executing of SQL queries.

RBAC Policy

If you are using a Role Based Access Control yaml file you will need to set a policy like the following:

  - actions:
      - KSQLDB_QUERY
    effect: Allow
    resource: ["ksqldb", "*", "ksqldb-source", "QUERYABLE_GRADES"]
    role: "kafka-users"

The above policy will allow anyone with the role kafka-users to execute a SQL query against the source (eg, table or stream) named QUERYABLE_GRADES.

RBAC Notes

The KSQLDB_QUERY action works against a ksqldb-source (that is, a table or stream) and not on the underlying Kafka topic the ksqlDB source writes its data to.

For example, consider the following stream:

CREATE STREAM PAYMENTS (ID STRING, AMOUNT INTEGER, CODE STRING)
WITH (KAFKA_TOPIC='payments_topic', KEY_FORMAT='KAFKA', PARTITIONS=2, VALUE_FORMAT='AVRO');

Any RBAC policies applied to the underlying Kafka topic (payments_topic) will not impact the KSQLDB_QUERY action. You would use an RBAC resource like ["ksqldb", "*", "ksqldb-source", "PAYMENTS"] instead.

All ksqldb-source values are expected to be in uppercase. Similar to all other RBAC actions, you can use wildcard for partial matches on sources, for example: ["ksqldb", "*", "ksqldb-source", "PAYMENTS*"] would match any ksqlDB source starting with "PAYMENTS".

Data Masking

Kpow's data masking feature is provided for all SQL queries performed. Refer to our Data policies documentation for more details on this feature.

Example configuration

  - name: Credit Card (ksqldb)
    category: PII
    resources:
      - [ 'ksqldb', '*', 'ksqldb-source', 'PAYMENTS']
    redaction: ShowLast4
    fields: [ credit_card, creditcard, pan ]

The above data masking policy will apply the ShowLast4 redaction against any field named credit_card, creditcard or pan. This redaction will only be applied to the PAYMENTS table or stream.

Note: the fields attribute is case insensitive, and the above example will also redact on PAN, CREDIT_CARD and CREDITCARD.

Similar to the KSQLDB_QUERY action, data masking policies are applied to a ksqldb-source and not their underling Kafka topic. Source names must be in uppercase.

Data masking will also be applied in the case of aliased fields. For example:

SELECT PAN AS P FROM PAYMENTS;

Will still apply the ShowLast4 redaction to the column aliased as P.

Terminating Stream Queries

You can terminate any running push queries (eg, queries where the data is streaming to a client) within Kpow's UI.

Navigate to the push query you wish to terminate, click on the 'actions' icon and click 'Terminate Query'.

RBAC Configuration

The KSQLDB_TERMINATE_QUERY action allows users to terminate push queries.

Simple Access Controls

If you are using Simple Access Control (environment variables) you will need to set ALLOW_KSQLDB_TERMINATE_QUERY=true to enable termination of push queries.

RBAC Policy

  - actions:
      - KSQLDB_TERMINATE_QUERY
    effect: Allow
    resource: ["ksqldb", "*", "ksqldb-query", "transient_*"]
    role: "kafka-users"

The above RBAC policy will allow the role kafka-users to terminate all transient queries.

Inserting Rows

You can insert rows into a ksqlDB source (stream or table) using Kpow's insert UI.

This interface is structured similar to Data produce, except simplified to only insert JSON-formatted rows into a ksqlDB table or stream.

The accepted data format is JSON in the text areas. The key fields are case insensitive.

Importing from file

Kpow supports importing rows from a file. Supported file formats include .csv, .json and .edn.

CSV files

Kpow will accept a CSV document formatted with headers as the first row.

For example, this is a valid CSV file to import:

ID,GRADE,RANK
10,"A",100
11,"B",90

Kpow will try to infer the types (eg, numeric and boolean data) of each CSV column. For example, the above CSV will result in the following JSON:

[{"ID": 10, "GRADE": "A", "RANK": 100}, {"ID": 11, "GRADE": "B", "RANK": 90}]

JSON files

Kpow will accept a JSON document that is a collection of rows.

For example, this is a valid JSON file to import:

[{"ID": 10, "GRADE": "A", "RANK": 100}, {"ID": 11, "GRADE": "B", "RANK": 90}]

EDN files

Kpow will accept a EDN document that is a collection of rows.

For example, this is a valid EDN file to import:

[{:id 10 :grade "A" :rank 100} {:id 11, :grade "B", :rank 90}]

Keys can either be keywords or strings.

RBAC Configuration

The KSQLDB_INSERT action allows users to insert rows into a ksqlDB source (table or stream).

Simple Access Controls

If you are using Simple Access Control (environment variables) you will need to set ALLOW_KSQLDB_INSERT=true to enable inserting of ksqlDB rows.

RBAC policy

If you are using a {% link id="role-based-access-control product="kpow-ee" /%} yaml file, you will need to set a policy like the following:

  - actions:
      - KSQLDB_INSERT
    effect: Allow
    resource: ["ksqldb", "*", "ksqldb-source", "GRADES"]
    role: "kafka-users"

The above policy will allow anyone with the role kafka-users to execute a insert rows the source named GRADES.

Similar to querying, RBAC resources for the KSQLDB_INSERT action work against the ksqldb-source resource.