Creating the tables and view

You see how to create simple tables, insert the data, and join the tables using a materialized view. You run the query, and the optimizer takes advantage of the precomputation performed by the materialized view to speed response time.

  1. On the options menu of your Hive VW, click Open Hue.
  2. Select a database, for example default.
  3. Create a managed table.
    For example, create this emps table in the Hue editor:
    CREATE TABLE emps (
    empid INT,
    deptno INT,
    name VARCHAR(256),
    salary FLOAT,
    hire_date TIMESTAMP);                   
  4. Create another table.
    For example, create this depts table:
    CREATE TABLE depts (
    deptno INT,
    deptname VARCHAR(256),
    locationid INT);
  5. Insert data into the tables by copy/pasting the following statements one-by-one and executing them one-by-one.
    INSERT INTO TABLE emps VALUES (10001,101,CAST('jane doe' as VARCHAR(256)),250000,'2018-01-10');
    INSERT INTO TABLE emps VALUES (10002,100,CAST('somporn klailee' as VARCHAR(256)),210000,'2017-12-25');
    INSERT INTO TABLE emps VALUES (10003,200,CAST('jeiranan thongnopneua' as VARCHAR(256)),175000,'2018-05-05');
    
    INSERT INTO TABLE depts VALUES (100,CAST('HR' as VARCHAR(256)),10);
    INSERT INTO TABLE depts VALUES (101,CAST('Eng' as VARCHAR(256)),11);
    INSERT INTO TABLE depts VALUES (200,CAST('Sup' as VARCHAR(256)),20);  
  6. Create a materialized view to join the tables:
    CREATE MATERIALIZED VIEW mv
    AS SELECT empid, deptname, hire_date
    FROM emps JOIN depts
    ON (emps.deptno = depts.deptno)
    WHERE hire_date >= '2017-01-01';