[jira] [Created] (PHOENIX-3550) Allow global user to use views created by Tenants

["Jeremy Huffman (JIRA)" <ji...@apache.org>]

Jeremy Huffman created PHOENIX-3550:
---------------------------------------

             Summary: Allow global user to use views created by Tenants
                 Key: PHOENIX-3550
                 URL: https://issues.apache.org/jira/browse/PHOENIX-3550
             Project: Phoenix
          Issue Type: New Feature
            Reporter: Jeremy Huffman
            Priority: Minor


As discussed in the mailing list, I would like it to be possible for the global user to be able to query views created by individual tenants. Using the views should make it possible to use the indexes defined on those views, which may not exist on columns in the base table. 

The use case here is Tenant 1 has a reference table that they want to "share" -  the global user can use that table along with Tenant 2's table to create a single report (perhaps on behalf of Tenant 2 - the actual sharing would be out-of-band in the application layer).

create table items (tenant_id varchar not null, 
                    item_type varchar not null, 
                    item_key varchar
                    constraint pk primary key (tenant_id, item_type, item_key)
                   ) multi_tenant = true;

--connect with TenantID '1'
create view vin_manufacturers (manufacturer varchar) as
select * from items where item_type = 'vin_manufacturers';

create local index vin_manufacturers_idx on vin_manufacturers (manufacturer);
--key is vin prefix
upsert into vin_manufacturers (item_key, manufacturer) values ('1FA', 'Ford Motor Company');
upsert into vin_manufacturers (item_key, manufacturer) values ('1B3', 'Dodge');

--connect with TenantID '2'

create view car_models (vin_prefix varchar(3), model varchar) as
select * from items where item_type = 'car_models';

create local index car_models_idx on car_models (vin_prefix) include (model);

upsert into car_models (item_key, vin_prefix, model) values ('1FAMustangGT', '1FA', 'Mustang GT');
upsert into car_models (item_key, vin_prefix, model) values ('1B3Charger', '1B3', 'Charger');

-- connected as global user (no TenantID) i can see all the records in base table

select * from items where item_type in ('vin_manufacturers', 'car_models');

-- I would like global user to be able to do this and have it use the vin_manufacturers_idx
-- presently fails with 'Table undefined. tableName=CAR_MODELS'
select md.model
from car_models md
inner join vin_manufacturers vm on (md.vin_prefix = vm.item_key)
where vm.manufacturer = 'Ford Motor Company';



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)