I'm using Phoenix 4.4 (HDP 2.4). I want to change some columns in an existing table to be not nullable. I was able to change the nullable property in system.catalog, but I can still upsert nulls into the changed columns.
Here's an example of what I did:
Create a simple test table
CREATE TABLE IF NOT EXISTS TEST_S.TEST_T( ROWKEY VARCHAR NOT NULL PRIMARY KEY, TEST_C BIGINT)
Change nullable to 0 (also tried with is_nullable)
UPSERT INTO SYSTEM.CATALOG (TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, COLUMN_FAMILY, NULLABLE, IS_NULLABLE) VALUES ('TEST_S','TEST_T','TEST_C', '0', 0, 'FALSE')
Upsert values into the table
UPSERT INTO TEST_S.TEST_T (ROWKEY, TEST_C) VALUES ('TEST1', NULL)
The above upsert goes through without any error or warning. When I query the table, I can see null values in the TEST_C column.
I suppose it is related to the fact that it's not possible to execute a create statement like below (neither with or without trying to make TEST_C a PK)
CREATE TABLE IF NOT EXISTS TEST_S.TEST_T( ROWKEY VARCHAR NOT NULL PRIMARY KEY, TEST_C BIGINT NOT NULL --PRIMARY KEY )
Does anybody know if it's possible to work around this problem in Phoenix 4.4 or if the newer versions allow to do such a thing?
If you can clone table, add the particular things and re direct to the cloned table may work.
Let me know how it was ressolved.