broadcast_join.sql

-- a reference table 
CREATE TABLE states ( 
code char(2) PRIMARY KEY, 
full_name text NOT NULL, 
general_sales_tax numeric(4,3) 
); 
-- distribute it to all workers 
SELECT create_reference_table('states');

--large_table
CREATE TABLE logs(
id int,
user_id int,
state_code char(2) PRIMARY KEY,
...,
....,
)
--distribute the table logs on say user_id(could be anything)
SELECT create_distributed_table('logs','user_id');
--Query - Get me the number of logs grouped on state.
SELECT s.name, count(1) from logs l JOIN states s ON l.state_code=s.code GROUP BY s.code, s.name;
-- The above query uses the replicated states for the join operation even though the join is not on the partition column.