CREATE TABLE syntax need HASH keyword explicitly in YugabyteDB YCQL?

Question

[Question posted by a user on YugabyteDB Community Slack]

We are using YSQL on YugabyteDB. Below table syntax create table with hash sharding:

CREATE TABLE customers (
    customer_id bpchar NOT NULL,
    company_name character varying(40) NOT NULL,
    PRIMARY KEY (customer_id HASH)
);

Does below table syntax creates the table with range sharding or hash sharding?

CREATE TABLE customers (
    customer_id bpchar NOT NULL,
    company_name character varying(40) NOT NULL,
    PRIMARY KEY (customer_id)
);

Answer 1

By default, the first column of a PRIMARY KEY or INDEX is taken to be the HASH column unless it is denoted as ASC or DESC which would indicate a RANGE column. The default RANGE column order is ASC if unspecified after the first column.

If the primary key is as shown in below table:

CREATE TABLE IF NOT EXISTS public.table1
(
    customer_id uuid NOT NULL,
    item_id uuid NOT NULL,
    kind character varying(100) NOT NULL,
    details character varying(100) NOT NULL,
    CONSTRAINT table1_pkey PRIMARY KEY (customer_id, kind, item_id)
);

CREATE UNIQUE INDEX  IF NOT EXISTS  unique_customer_id ON table1(customer_id);

CREATE UNIQUE INDEX  IF NOT EXISTS unique_item ON table1(customer_id, kind) WHERE kind='BAD' OR kind='SLOW';

The PRIMARY KEY will be sharded on customer_id, and it will be sorted in kind, item_id order ascending. In the first index unique_customer_id, that index will be sharded on the customer_id field. In the second index unique_item, it will be sharded on customer_id, with the kind field being sorted in ascending order within each customer_id. Your sample query will be slow/bad because you have to scan all customer_id shards for the specified kind field.