Flattened Table Example
In the following example, columns orderFact.cust_name and orderFact.cust_gender are defined as SET USING and DEFAULT columns, respectively. Both columns obtain their values by querying table custDim:
=> CREATE TABLE public.custDim( cid int PRIMARY KEY NOT NULL, name varchar(20), age int, gender varchar(1) );
=> CREATE TABLE public.orderFact( order_id int PRIMARY KEY NOT NULL, order_date timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL, cid int REFERENCES public.custDim(cid), cust_name varchar(20) SET USING (SELECT name FROM public.custDim WHERE (custDim.cid = orderFact.cid)), cust_gender varchar(1) DEFAULT (SELECT gender FROM public.custDim WHERE (custDim.cid = orderFact.cid)), amount numeric(12,2) ) PARTITION BY order_date::DATE GROUP BY CALENDAR_HIERARCHY_DAY(order_date::DATE, 2, 2);
The following INSERT commands load data into both tables:
=> INSERT INTO custDim VALUES(1, 'Alice', 25, 'F'); => INSERT INTO custDim VALUES(2, 'Boz', 30, 'M'); => INSERT INTO custDim VALUES(3, 'Eva', 32, 'F'); => => INSERT INTO orderFact (order_id, cid, amount) VALUES(100, 1, 15); => INSERT INTO orderFact (order_id, cid, amount) VALUES(200, 1, 1000); => INSERT INTO orderFact (order_id, cid, amount) VALUES(300, 2, -50); => INSERT INTO orderFact (order_id, cid, amount) VALUES(400, 3, 100); => INSERT INTO orderFact (order_id, cid, amount) VALUES(500, 2, 200); => COMMIT;
When you query the tables, Vertica returns the following result sets:
=> SELECT * FROM custDim;
cid | name | age | gender
-----+-------+-----+--------
1 | Alice | 25 | F
2 | Boz | 30 | M
3 | Eva | 32 | F
(3 rows)
=> SELECT order_id, order_date::date, cid, cust_name, cust_gender, amount FROM orderFact ORDER BY cid;
order_id | order_date | cid | cust_name | cust_gender | amount
----------+------------+-----+-----------+-------------+---------
100 | 2018-12-31 | 1 | | F | 15.00
200 | 2018-12-31 | 1 | | F | 1000.00
300 | 2018-12-31 | 2 | | M | -50.00
500 | 2018-12-31 | 2 | | M | 200.00
400 | 2018-12-31 | 3 | | F | 100.00
(5 rows)
Vertica automatically populates the DEFAULT column orderFact.cust_gender, but the SET USING column orderFact.cust_name remains NULL. You can automatically populate this column by calling the function REFRESH_COLUMNS on flattened table orderFact. This function invokes the SET USING query for column orderFact.cust_name and populates the column from the result set:
=> SELECT REFRESH_COLUMNS('orderFact', 'cust_name', 'REBUILD');
REFRESH_COLUMNS
-------------------------------
refresh_columns completed
(1 row)
=> COMMIT;
COMMIT
=> SELECT order_id, order_date::date, cid, cust_name, cust_gender, amount FROM orderFact ORDER BY cid;
order_id | order_date | cid | cust_name | cust_gender | amount
----------+------------+-----+-----------+-------------+---------
100 | 2018-12-31 | 1 | Alice | F | 15.00
200 | 2018-12-31 | 1 | Alice | F | 1000.00
300 | 2018-12-31 | 2 | Boz | M | -50.00
500 | 2018-12-31 | 2 | Boz | M | 200.00
400 | 2018-12-31 | 3 | Eva | F | 100.00
(5 rows)
Sometimes, it can be useful to set an entire column to NULL or some other value.
=> ALTER TABLE orderFact ALTER COLUMN cust_name SET USING NULL;
ALTER TABLE
=> SELECT REFRESH_COLUMNS('orderFact', 'cust_name', 'REBUILD');
REFRESH_COLUMNS
---------------------------
refresh_columns completed
(1 row)
=> SELECT order_id, order_date::date, cid, cust_name, cust_gender, amount FROM orderFact ORDER BY cid;
order_id | order_date | cid | cust_name | cust_gender | amount
----------+------------+-----+-----------+-------------+---------
100 | 2018-12-31 | 1 | | F | 15.00
200 | 2018-12-31 | 1 | | F | 1000.00
300 | 2018-12-31 | 2 | | M | -50.00
500 | 2018-12-31 | 2 | | M | 200.00
400 | 2018-12-31 | 3 | | F | 100.00
(5 rows)
You can also remove the SET USING expression from the column, but retain the column's data.
=> ALTER TABLE orderFact ALTER COLUMN cust_name DROP SET USING; ALTER TABLE