SQL support

Apache Kudu connector SQL support provides the supported SQL statements, table properties, partitioning options, column properties, and procedures for querying and managing Kudu data through Trino.

Supported SQL statements

The connector provides read and write access to data and metadata in Apache Kudu. In addition to standard read operations, the connector supports the following statements:

INSERT — Functions as an UPSERT operation for INSERT INTO ... VALUES and INSERT INTO ... SELECT.
DELETE — Requires a WHERE clause with predicates that can be fully pushed down to the data source.
MERGE — Performs conditional insert, update, or delete operations.
CREATE TABLE — Creates a table in Kudu with explicit primary key and partitioning definitions.
CREATE TABLE AS — Creates a table and populates the table with query results.
DROP TABLE — Removes the table from Kudu.
ALTER TABLE — Modifies the table structure. Adding columns supports column properties. Modifying or dropping columns is restricted to non-primary-key columns.
CREATE SCHEMA — Allowed only when schema emulation is enabled.
DROP SCHEMA — Allowed only when schema emulation is enabled.

Table creation

Creating an Apache Kudu table requires specifying column names, data types, primary keys, and partitioning design. Option settings include column encoding, compression, and replication factor.

CREATE TABLE user_events (
  user_id INTEGER WITH (primary_key = true),
  event_name VARCHAR WITH (primary_key = true),
  message VARCHAR,
  details VARCHAR WITH (nullable = true, encoding = 'plain')
)
WITH (
  partition_by_hash_columns = ARRAY['user_id'],
  partition_by_hash_buckets = 5,
  number_of_replicas = 3
);

The table structure adheres to the following rules:

Primary key columns must be listed first in the column list.
All columns specified in partition definitions must belong to the primary key.
The optional number_of_replicas table property defines the number of table replicas and must be an odd integer. If omitted, the Kudu master default replication factor applies.

Column properties

The following table shows the column properties available when creating or altering tables:

Table 1. Column properties
Column property name	Type	Description
primary_key	`BOOLEAN`	Specifies whether the column belongs to the primary key. Apache Kudu primary keys enforce uniqueness. Inserting duplicate primary keys updates existing rows.
nullable	`BOOLEAN`	Specifies whether the column accepts null values. Primary key columns cannot be nullable.
encoding	`VARCHAR`	Specifies the column encoding to optimize storage and performance. The valid values are `auto`, `plain`, `bitshuffle`, `runlength`, `prefix`, `dictionary`, `group_varint`.
compression	`VARCHAR`	Specifies the compression algorithm for encoded values. The valid values are `default`, `no`, `lz4`, `snappy`, `zlib`.

The following example configures specific encoding and compression properties on columns:

CREATE TABLE example_table (
  name VARCHAR WITH (primary_key = true, encoding = 'dictionary', compression = 'snappy'),
  index BIGINT WITH (nullable = true, encoding = 'runlength', compression = 'lz4'),
  comment VARCHAR WITH (nullable = true, encoding = 'plain', compression = 'default'),
   ...
) WITH (...);

Modifying tables

Add a column to an existing table by using the ALTER TABLE ... ADD COLUMN statement:

ALTER TABLE example_table ADD COLUMN extraInfo VARCHAR WITH (nullable = true, encoding = 'plain')

Renaming or dropping columns using ALTER TABLE ... RENAME COLUMN or ALTER TABLE ... DROP COLUMN is allowed only for columns that are not part of the primary key.