create_table_2.sql

CREATE TABLE demo (
  id int,
  name string,
  email string,
  phoneNumber string,
  ts timestamp
)
USING hudi
OPTIONS (
  primaryKey = "id",
  preCombineField = "ts",
  hoodie.precommit.validators = "org.apache.hudi.client.validator.SqlQuerySingleResultPreCommitValidator",
  hoodie.precommit.validators.single.value.sql.queries = 
  "SELECT COUNT(*) FROM <TABLE_NAME> WHERE (name IS NULL OR NOT (substr(name, 1, 1) REGEXP '^[A-Z]+$'))#0;
  
  SELECT COUNT(*) 
  FROM <TABLE_NAME>
  WHERE phoneNumber IS NULL OR (length(phoneNumber) != 10 OR NOT(phoneNumber REGEXP '^[0-9]+$'))#0;
  
  SELECT COUNT(*) 
  FROM <TABLE_NAME> 
  WHERE email NOT LIKE '%@%.%'#0"
);

INSERT INTO demo VALUES (1, 'TestName1', 'test1@gmail.com', '1112223333', CURRENT_TIMESTAMP);
-- Successful as data satisfies all business rules.

INSERT INTO demo VALUES (2, 'testName2', 'test1@gmail.com', '123456789', CURRENT_TIMESTAMP);
-- Failed as Name doesn't start with a capital alphabet

INSERT INTO demo VALUES (2, 'TestName2', 'test1@gmail', '1112223333', CURRENT_TIMESTAMP);
-- Failed as Email is not in the correct format.

INSERT INTO demo VALUES (2, 'TestName2', 'test1@gmail.com', '11122A3333', CURRENT_TIMESTAMP);
-- Failed as the phone number contains alphabetic characters.

INSERT INTO demo VALUES (2, 'TestName2', 'test1@gmail.com', '123456789', CURRENT_TIMESTAMP);
-- Failed as the phone number is not exactly 10 characters.