Loading Data into Pre-join Projections

[NiravLangaliya]

Hi All,

I am following link to check that how data is being loaded into Pre-Join Projection :

https://my.vertica.com/docs/7.1.x/HTML/ ... ht=Loading Data into Pre-join Projections

and there is notes that:

Foreign and Primary Key Constraints

To ensure referential integrity, foreign and primary key constraints are enforced on inserts into fact tables of pre-join projections. If a fact row attempts to reference a row that does not exist in the dimension table, the load is automatically rolled back. The load is also rolled back if a fact row references more than one dimension row.

Note: Unless it also has a NOT NULL constraint, a column with a FOREIGN KEY constraint can contain a NULL value even though the dimension table's primary key column does not contain a NULL value. This allows for records to be inserted into the fact table even though the foreign key in the dimension table is not yet known.

what I take from this note is if fact table has foreign key with not null data type in that case we can able to load data even though data is not present into Dimension table but when i tried to create pre - join projection using fact table which has foreign key without not null specified it is giving me error and not allowing me to create it.

DROP TABLE IF EXISTS public.emp_table_dim_nl;

CREATE TABLE public.emp_table_dim_nl (
EmployeeID integer NOT NULL,
FirstName VARCHAR(30),
LastName VARCHAR(30),
Dpt_ID integer
);

DROP TABLE IF EXISTS public.dpt_table_dim_nl;

CREATE TABLE public.dpt_table_dim_nl (
Dpt_ID integer NOT NULL,
Dpt_Name VARCHAR(20)
)
;

Creating Primary Key:
ALTER TABLE public.emp_table_dim_nl ADD CONSTRAINT pk_emp_dimension PRIMARY KEY (EmployeeID);
ALTER TABLE public.dpt_table_dim_nl ADD CONSTRAINT pk_dpt_dimension PRIMARY KEY (Dpt_ID);

Creating Foreign Key:
ALTER TABLE public.emp_table_dim_nl ADD CONSTRAINT fk_dep_id FOREIGN KEY (Dpt_ID) REFERENCES public.dpt_table_dim_nl (Dpt_ID);

INSERT INTO public.dpt_table_dim_nl values (1,'A');
INSERT INTO public.dpt_table_dim_nl values (2,'B');

INSERT INTO public.emp_table_dim_nl values (32,'N','L',1);
INSERT INTO public.emp_table_dim_nl values (36,'Nila','K',2);

CREATE PROJECTION public.EMP_INFO
(EmployeeID, FirstName, LastName, Dpt_Name)
AS (
SELECT
EmployeeID,
FirstName,
LastName,
Dpt_Name
FROM public.emp_table_dim_nl emp,
public.dpt_table_dim_nl dpt
WHERE emp.Dpt_ID = dpt.Dpt_ID
) KSAFE 1;

[CREATE - 0 row(s), 0.000 secs] [Error Code: 5630, SQL State: 42601]
[Vertica][VJDBC](5630) ERROR: Nullable FKs are not allowed in projection definition
... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec [0 successful, 0 warnings, 1 errors]

[JimKnicely]

Hi,

Yeah, you can't have NULL foreign key columns in pre-join projections. One work around is to create a key for NULL values in your look up table. We typically use a key of zero.

Example:

Code: Select all

dbadmin=> \d dim;
                                    List of Fields by Tables
 Schema | Table | Column |     Type     | Size | Default | Not Null | Primary Key | Foreign Key 
--------+-------+--------+--------------+------+---------+----------+-------------+-------------
 public | dim   | pk     | int          |    8 |         | t        | t           | 
 public | dim   | stuff  | varchar(100) |  100 |         | t        | f           | 
(2 rows)

dbadmin=> \d fact;
                                 List of Fields by Tables
 Schema | Table | Column | Type | Size | Default | Not Null | Primary Key |  Foreign Key   
--------+-------+--------+------+------+---------+----------+-------------+----------------
 public | fact  | pk     | int  |    8 |         | t        | t           | 
 public | fact  | dim_pk | int  |    8 |         | f        | f           | public.dim(pk)
(2 rows)

dbadmin=> select * from dim;
 pk |    stuff    
----+-------------
  1 | Some stuff1
  2 | Some stuff2
(2 rows)

dbadmin=> select * from fact;
 pk | dim_pk 
----+--------
  1 |      1
  2 |       
(2 rows)

dbadmin=> create projection fact_dim as select * from fact join dim on fact.dim_pk = dim.pk;
ERROR 5630:  Nullable FKs are not allowed in projection definition

Notice that I could not create the pre-join projection...

The work around:

Code: Select all

dbadmin=> insert into dim values (0, 'Unknown');
 OUTPUT 
--------
      1
(1 row)

dbadmin=> update fact set dim_pk = 0 where dim_pk is null;
 OUTPUT 
--------
      1
(1 row)

dbadmin=> alter table fact alter column dim_pk set not null;
ALTER TABLE

dbadmin=> create projection fact_dim as select * from fact join dim on fact.dim_pk = dim.pk;
WARNING 4468:  Projection <public.fact_dim> is not available for query processing. Execute the select start_refresh() function to copy data into this projection.
          The projection must have a sufficient number of buddy projections and all nodes must be up before starting a refresh
CREATE PROJECTION

dbadmin=> select start_refresh();
             start_refresh              
----------------------------------------
 Starting refresh background process. 

(1 row)

Does that help?