set hive.support.concurrency=true;
set hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;

CREATE TEMPORARY FUNCTION dboutput AS 'org.apache.hadoop.hive.contrib.genericudf.example.GenericUDFDBOutput';

SELECT
dboutput ( 'jdbc:derby:;databaseName=${system:test.tmp.dir}/test_jdbc_join_mv;create=true','','',
'CREATE TABLE person ("id" INTEGER, "name" VARCHAR(25), "jid" INTEGER, "cid" INTEGER)' );

SELECT
dboutput ( 'jdbc:derby:;databaseName=${system:test.tmp.dir}/test_jdbc_join_mv;create=true','','',
'CREATE TABLE country ("id" INTEGER, "name" VARCHAR(25))' );

CREATE EXTERNAL TABLE person
(
 id int,
 name varchar(25),
 jid int,
 cid int
)
STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler'
TBLPROPERTIES (
                "hive.sql.database.type" = "DERBY",
                "hive.sql.jdbc.driver" = "org.apache.derby.jdbc.EmbeddedDriver",
                "hive.sql.jdbc.url" = "jdbc:derby:;databaseName=${system:test.tmp.dir}/test_jdbc_join_mv;create=true;collation=TERRITORY_BASED:PRIMARY",
                "hive.sql.dbcp.username" = "APP",
                "hive.sql.dbcp.password" = "mine",
                "hive.sql.table" = "PERSON",
                "hive.sql.dbcp.maxActive" = "1"
);

CREATE EXTERNAL TABLE country
(
    id int,
    name varchar(25)
)
    STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler'
    TBLPROPERTIES (
        "hive.sql.database.type" = "DERBY",
        "hive.sql.jdbc.driver" = "org.apache.derby.jdbc.EmbeddedDriver",
        "hive.sql.jdbc.url" = "jdbc:derby:;databaseName=${system:test.tmp.dir}/test_jdbc_join_mv;create=true;collation=TERRITORY_BASED:PRIMARY",
        "hive.sql.dbcp.username" = "APP",
        "hive.sql.dbcp.password" = "mine",
        "hive.sql.table" = "COUNTRY",
        "hive.sql.dbcp.maxActive" = "1"
        );

CREATE TABLE job (
    id int,
    title varchar(20)
) STORED AS ORC TBLPROPERTIES ('transactional'='true');

CREATE MATERIALIZED VIEW mv1 AS SELECT id, title FROM job WHERE title = 'Software Engineer';

explain cbo 
select * 
from person 
join job on person.jid = job.id
join country on person.cid = country.id
where job.title = 'Software Engineer';

DROP MATERIALIZED VIEW mv1;