-- Reset environment.  
-- Drop the tables an views created during the HOL (note, ignore not found errors)
drop table movielog;
drop table recommendation;
drop table movielog_plus;
drop table movieapp_log_json;
drop table movieapp_log_avro;
drop table movieapp_log_month_avro;
drop table movieapp_log_month_parquet;
drop table mylogdata;
drop view  movielog_v;

-- Part 1 - Configuring Oracle Big Data SQL
-- Task 1:  Common and cluster configuration directories

-- Common directory
cd /u01/bigdatasql_config/
cat bigdata.properties

-- bigdatalite cluster 
cd /u01/bigdatasql_config/bigdatalite
ls

-- Task 2: Review Oracle directory objects

SELECT * FROM all_directories where directory_name like 'ORA%_BIGDATA%';

/*  Directory objects created from the following commands
create or replace directory ORACLE_BIGDATA_CONFIG as '/u01/bigdatasql_config';
create or replace directory "ORA_BIGDATA_CL_bigdatalite" as '';
*/

-- Review the Big Data SQL Multi-threaded Agent (MTA) using the following commands:

SELECT * FROM all_db_links WHERE db_link like 'BDSQL%.LOCALDOMAIN';

/* Database links created from the following commands
create public database link BDSQL$_BIGDATALITE.LOCALDOMAIN using 'extproc_connection_data';
create public database link BDSQL$_DEFAULT_CLUSTER.LOCALDOMAIN using 'extproc_connection_data';
*/

-- Part 2 - Create Oracle Table Over Application Log
-- Task 1:  Review file containing log data
hadoop fs -ls /user/oracle/moviework/applog_json
hadoop fs -tail /user/oracle/moviework/applog_json/movieapp_log_json.log

-- Task 2: Create table over log data
CREATE TABLE movielog
  (click VARCHAR2(4000))
  ORGANIZATION EXTERNAL
  (TYPE ORACLE_HDFS
   DEFAULT DIRECTORY DEFAULT_DIR
   LOCATION ('/user/oracle/moviework/applog_json/')
  ) 
  REJECT LIMIT UNLIMITED;

-- Query the movielog table
select * from movielog where rownum < 20;

-- Create table with ACCESS PARAMETERS specified
CREATE TABLE movielog_plus
  (click VARCHAR2(40))
  ORGANIZATION EXTERNAL
  (TYPE ORACLE_HDFS
   DEFAULT DIRECTORY DEFAULT_DIR
   ACCESS PARAMETERS (
                      com.oracle.bigdata.cluster=bigdatalite
                      com.oracle.bigdata.overflow={"action":"truncate"}                     
                     )
   LOCATION ('/user/oracle/moviework/applog_json/')
  ) 
  REJECT LIMIT UNLIMITED;

-- Query and truncate the records  
SELECT * FROM movielog_plus WHERE rownum < 20;

-- Query the table using new JSON syntax
SELECT m.click.custid, m.click.movieid, m.click.genreid, m.click.time
FROM movielog m
WHERE rownum < 20;

-- Combine "click" data with the movie data in Oracle Database.
SELECT f.click.custid, m.title, m.year, m.gross, f.click.rating
FROM movielog f, movie m
WHERE f.click.movieid = m.movie_id
AND f.click.rating > 4; 

-- Define a view to make it easier to query click data
CREATE OR REPLACE VIEW movielog_v AS
SELECT
  CAST(m.click.custid	AS NUMBER) custid,
  CAST(m.click.movieid AS NUMBER) movieid,
  CAST(m.click.activity	AS NUMBER) activity,
  CAST(m.click.genreid AS NUMBER) genreid,
  CAST(m.click.recommended AS VARCHAR2(1)) recommended,
  CAST(m.click.time AS VARCHAR2(20)) time,
  CAST(m.click.rating AS NUMBER) rating,
  CAST(m.click.price AS NUMBER) price
FROM movielog m;

-- How do top rated movies on MoviePlex compare to top grossing movies?
SELECT m.title, m.year, m.gross, round(avg(f.rating), 1)
FROM movielog_v f, movie m
WHERE f.movieid = m.movie_id
GROUP BY m.title, m.year, m.gross
ORDER BY m.gross desc
FETCH FIRST 10 ROWS ONLY; 

-- Part 3 - Leverage the Hive Metastore to Access Data in Hadoop
-- Task 1:  Review tables stored in Hive

-- Enter the beeline Hive CLI
-- "bee" is a shortcut to launching beeline (see /opt/bin/bee)
bee

-- show the tables in the database
show tables;

-- Review the table definition
show create table movielog;

-- Query the first 10 records
select * from movielog limit 10;

-- Review the second table definition
show create table movieapp_log_json;

-- Query the second table - find highly rated movies
SELECT movieid, AVG(rating) AS avg_rating
FROM movieapp_log_json
WHERE rating IS NOT NULL
GROUP BY movieid
ORDER BY avg_rating DESC, movieid ASC
LIMIT 25;

-- Review the third table - which is defined over Oracle NoSQL Database
show create table moviework.recommendation; 

-- Query the table
SELECT * FROM moviework.recommendation LIMIT 20;

-- Exit beeline
!exit

-- Task 2:  Leverage Hive Metadata when Creating Oracle Tables

-- Create table over JSON
CREATE TABLE movieapp_log_json (
    custid      INTEGER ,
    movieid     INTEGER ,
    genreid     INTEGER ,
    time        VARCHAR2 (20) ,
    recommended VARCHAR2 (4) ,
    activity    NUMBER,
    rating      INTEGER,
    price       NUMBER
  )
  ORGANIZATION EXTERNAL
  (
    TYPE ORACLE_HIVE
    DEFAULT DIRECTORY DEFAULT_DIR
  )
  REJECT LIMIT UNLIMITED; 


-- Query the table - find clicks where movies were highly rated movies.
SELECT * FROM movieapp_log_json WHERE rating > 4;

-- Find average ratings for movies
SELECT movieid, AVG(rating) 
FROM movieapp_log_json 
WHERE rating IS NOT NULL 
GROUP BY movieid 
ORDER BY AVG(rating) DESC, movieid ASC
FETCH FIRST 25 ROWS ONLY; 


-- What are these highly rated movies that people purchase? Combine data in HDFS with data in Oracle Database.

SELECT f.custid, m.title, m.year, m.gross, f.rating
FROM movieapp_log_json f, movie m
WHERE f.movieId = m.movie_id
AND f.rating > 4; 


-- Query different types of data - like Avro. Notice Hive/Oracle table names don't match 
CREATE TABLE mylogdata (
    custid      INTEGER ,
    movieid     INTEGER ,
    genreid     INTEGER ,
    time        VARCHAR2 (20) ,
    recommended VARCHAR2 (4) ,
    activity    NUMBER,
    rating      INTEGER,
    price       NUMBER
  )
  ORGANIZATION EXTERNAL
  (
    TYPE ORACLE_HIVE
    DEFAULT DIRECTORY DEFAULT_DIR
    ACCESS PARAMETERS ( com.oracle.bigdata.tablename=default.movieapp_log_avro )
  )
  REJECT LIMIT UNLIMITED;  

-- Query the table
SELECT custid, movieid, time FROM mylogdata;


-- Alter the Hive table definition and query the updated table
bee

ALTER TABLE movieapp_log_json SET LOCATION "hdfs://bigdatalite.localdomain:8020/user/oracle/moviework/two_recs";
SELECT * FROM movieapp_log_json;

-- Query the data thru the Oracle Database
SELECT * FROM movieapp_log_json;

-- Go to the beeline Hive CLI and reset the Hive table to its original location.  Confirm there are more than 2 rows
ALTER TABLE movieapp_log_json SET LOCATION "hdfs://bigdatalite.localdomain:8020/user/oracle/moviework/applog_json"; 
select * from movieapp_log_json limit 10;

-- Access data stored in NoSQL Database.  Create a table using a similar process to the above
CREATE TABLE RECOMMENDATION 
(
  CUSTID NUMBER 
, SNO NUMBER 
, GENREID NUMBER 
, MOVIEID NUMBER 
) 
ORGANIZATION EXTERNAL 
( 
  TYPE ORACLE_HIVE 
  DEFAULT DIRECTORY DEFAULT_DIR 
  ACCESS PARAMETERS 
  ( 
    com.oracle.bigdata.tablename: moviework.recommendation 
  ) 
) 
REJECT LIMIT UNLIMITED;

SELECT * FROM recommendation WHERE rownum <=20;

-- Part 4: Big Data SQL Performance Features
-- Task 1 - Querying Partitioned Hive Tables

-- Launch beeline and review the partitioned table definition
-- At Linux CLI
bee

show create table movieapp_log_month_avro;
show partitions movieapp_log_month_avro;

-- In SQL Developer, create Big Data SQL table over partitioned Hive table
CREATE TABLE MOVIEAPP_LOG_MONTH_AVRO
(
  CUSTID NUMBER
, MOVIEID NUMBER
, ACTIVITY NUMBER
, GENREID NUMBER
, RECOMMENDED VARCHAR2(4)
, TIME VARCHAR2(20)
, RATING NUMBER
, PRICE NUMBER
, POSITION NUMBER
, MONTH VARCHAR2(8)
)
ORGANIZATION EXTERNAL
(
  TYPE ORACLE_HIVE
  DEFAULT DIRECTORY DEFAULT_DIR
  ACCESS PARAMETERS
  (
    com.oracle.bigdata.tablename: default.movieapp_log_month_avro
  )
)
REJECT LIMIT UNLIMITED;

-- Compare query performance of non-partitioned vs partitioned table

-- non-partitioned
SELECT movieid,
  COUNT(*)
FROM mylogdata
WHERE SUBSTR(TIME, 1, 7) = '2012-07'
AND movieid              = 11547
GROUP BY movieid;

-- partitioned
SELECT movieid,
  COUNT(*)
FROM movieapp_log_month_avro
WHERE MONTH = '2012-07'
AND movieid = 11547
GROUP BY movieid;

-- Task 2 - Predicate Pushdown to Intelligent Sources
-- Launch beeline and review the partitioned table definition
-- At Linux CLI
bee

show create table movieapp_log_month_parquet;
show partitions movieapp_log_month_parquet;

-- Create Big Data SQL-enabled table over parquet source
CREATE TABLE MOVIEAPP_LOG_MONTH_PARQUET
(
  CUSTID NUMBER
, MOVIEID NUMBER
, ACTIVITY NUMBER
, GENREID NUMBER
, RECOMMENDED VARCHAR2(4)
, TIME VARCHAR2(20)
, RATING NUMBER
, PRICE NUMBER
, POSITION NUMBER
, MONTH VARCHAR2(8)
)
ORGANIZATION EXTERNAL
(
  TYPE ORACLE_HIVE
  DEFAULT DIRECTORY DEFAULT_DIR
  ACCESS PARAMETERS
  (
    com.oracle.bigdata.tablename: default.movieapp_log_month_parquet
  )
)
REJECT LIMIT UNLIMITED;

-- Query the parquet-based table


-- partitioned parquet table

SELECT movieid,
  COUNT(*)
FROM movieapp_log_month_parquet
WHERE MONTH = '2012-07'
AND movieid = 11547
GROUP BY movieid;


-- Part 5: Apply Oracle Security Policies over Data in Hadoop
-- Task 1 - Redact data sourced from both HDFS and Hive sources

BEGIN
  -- JSON file in HDFS 
  DBMS_REDACT.ADD_POLICY(
    object_schema => 'MOVIEDEMO',
    object_name => 'MOVIELOG_V',
    column_name => 'CUSTID',
    policy_name => 'movielog_v_redaction',
    function_type => DBMS_REDACT.PARTIAL,
    function_parameters => '9,1,7',
    expression => '1=1'
  );

  -- Avro data from Hive
  DBMS_REDACT.ADD_POLICY(
    object_schema => 'MOVIEDEMO',
    object_name => 'MYLOGDATA',
    column_name => 'CUSTID',
    policy_name => 'mylogdata_redaction',
    function_type => DBMS_REDACT.PARTIAL,
    function_parameters => '9,1,7',
    expression => '1=1'
  );
  
  -- Recommendations data from Oracle NoSQL Database
  DBMS_REDACT.ADD_POLICY(
    object_schema => 'MOVIEDEMO',
    object_name => 'RECOMMENDATION',
    column_name => 'CUSTID',
    policy_name => 'recommendation_redaction',
    function_type => DBMS_REDACT.PARTIAL,
    function_parameters => '9,1,7',
    expression => '1=1'
  );  
END;
/

-- Review the redacted data
SELECT * FROM mylogdata WHERE rownum < 20;

-- Join the redacted HDFS table with the redacted customer table
SELECT f.custid, c.last_name, f.movieid, f.time
FROM customer c, movielog_v f
WHERE c.cust_id = f.custid;

-- Customer Recency, Frequency and Monetary query  
  WITH customer_sales AS (
  -- Sales and customer attributes
  SELECT m.cust_id,
         c.last_name,
         c.first_name,
         c.country,
         c.gender,
         c.age,
         c.income_level,
         NTILE (5) over (order by sum(sales)) AS rfm_monetary
  FROM movie_sales m, customer c
  WHERE c.cust_id = m.cust_id
  GROUP BY m.cust_id,         
         c.last_name,
         c.first_name,
         c.country,
         c.gender,
         c.age,
         c.income_level
),
click_data AS (
  -- clicks from application log
  SELECT custid,
       NTILE (5) over (order by max(time)) AS rfm_recency,
       NTILE (5) over (order by count(1))    AS rfm_frequency
  FROM movielog_v
  GROUP BY custid
)
SELECT c.cust_id,
    c.last_name,
    c.first_name,
    cd.rfm_recency,
    cd.rfm_frequency,
    c.rfm_monetary,
    cd.rfm_recency*100 + cd.rfm_frequency*10 + c.rfm_monetary AS rfm_combined,
    c.country,
    c.gender,
    c.age,
    c.income_level
  FROM customer_sales c, click_data cd
  WHERE c.cust_id = cd.custid
  AND c.rfm_monetary >= 4
  AND cd.rfm_recency <= 2
  ORDER BY c.rfm_monetary desc, cd.rfm_recency desc;

-- How is the recommendation engine?  Combine data from: 
--  NoSQL Database (rank how many times movies are recommended)
--  Oracle DB (rank sales revenue)
--  HDFS (rank how many times people have showed interest in the movie - previewed, watched, displayed more info, etc.)
WITH rank_recs AS (
  -- recommendation rank from NoSQL Database
  SELECT movieid,
         RANK () OVER (ORDER BY COUNT(movieid) DESC) AS rec_rank
  FROM recommendation 
  GROUP BY movieid),
rank_sales AS (
  -- sales rank from Oracle Database
  SELECT m.movie_id,
         m.title,
         RANK () OVER (ORDER BY SUM(ms.sales) DESC) as sales_rank
  FROM movie m, movie_sales ms
  WHERE  ms.movie_id = m.movie_id
  GROUP BY m.title, m.movie_id
),
rank_interest AS (
  -- "interest" rank from hdfs logs
  SELECT movieid,
  RANK () OVER (ORDER BY COUNT(movieid) DESC) AS click_rank
  FROM movielog_v
  WHERE activity IN (1,4,5) -- rated, started or browsed the movie
  GROUP BY movieid
)
-- combine the results
SELECT rs.title, 
       sales_rank, 
       rec_rank, 
       click_rank
FROM rank_recs rr, rank_sales rs, rank_interest ri
WHERE rr.movieid = rs.movie_id
  AND ri.movieid = rs.movie_id
ORDER BY rec_rank asc;

-- PATTERN MATCHING content

--  The first example of how to use SQL MATCH_RECOGNIZE clause is based on creating a basic detailed sessionization report....

SELECT *
FROM movieapp_log_json_v
 MATCH_RECOGNIZE
  (PARTITION BY cust_id ORDER BY time_id
   MEASURES MATCH_NUMBER() AS session_id
   ALL ROWS PER MATCH 
   PATTERN (bgn sess+) 
   DEFINE 
         sess as time_id <= PREV(sess.time_id) + interval '2' hour
  )
 WHERE cust_id ='1000693';
 
 
-- Deubgging our pattern matching process using CLASSIFIER() function to show the pattern variable assigned to each row
-- Allows us to check if the pattern is being applied correctly

SELECT
  cust_id,
  session_id,
  time_id,
  TO_CHAR(time_id, 'hh24:mi:ss') AS session_time,
  pattern_id
FROM movieapp_log_json_v
 MATCH_RECOGNIZE
  (PARTITION BY cust_id ORDER BY time_id
   MEASURES MATCH_NUMBER() AS session_id,
            CLASSIFIER() AS pattern_id
   ALL ROWS PER MATCH 
   PATTERN (bgn sess+) 
   DEFINE 
         sess as time_id <= PREV(sess.time_id) + interval '2' hour
 )
WHERE cust_id ='1000693';
 
  
-- Let's look at a summary report that shows us customer id and the session number

SELECT
  cust_id,
  session_id
FROM movieapp_log_json_v
 MATCH_RECOGNIZE
  (PARTITION BY cust_id ORDER BY time_id
   MEASURES MATCH_NUMBER() AS session_id
   ONE ROW PER MATCH
   PATTERN (bgn sess+) 
   DEFINE 
         sess as time_id <= PREV(sess.time_id) + interval '2' hour
 )
WHERE cust_id ='1000693';


 
-- Extending the report to include additional measures to calculate the number of clicks per session (see MEASURE clause) and the session duration
 
SELECT
  cust_id,
  session_id,
  no_of_events,
  start_time,
  end_time,
  mins_duration
FROM movieapp_log_json_v
 MATCH_RECOGNIZE
  (PARTITION BY cust_id ORDER BY time_id
   MEASURES MATCH_NUMBER() AS session_id,
            COUNT(*) AS no_of_events,
            TO_CHAR(FIRST(bgn.time_id),'hh24:mi:ss') AS start_time,
            TO_CHAR(LAST(sess.time_id),'hh24:mi:ss') AS end_time, 
            TO_CHAR(to_date('00:00:00','HH24:MI:SS') + (LAST(sess.time_id)-FIRST(bgn.time_id)),'hh24:mi:ss') AS mins_duration
   ONE ROW PER MATCH
   PATTERN (bgn sess+) 
   DEFINE 
         sess as time_id <= PREV(sess.time_id) + interval '2' hour
 )
WHERE cust_id ='1000693';
 

-- How many unique sessions did we have this month?  We will use a new SQL 
-- feature to approximate count distinct.  This will be computed over a view 
-- called movieapp_analytics_v - which wraps our MATCH_RECOGNIZE analysis from above

SELECT
  time_id,
  SUM(no_of_events) AS tot_events,
  COUNT(DISTINCT cust_id) AS unique_customers,
  APPROX_COUNT_DISTINCT(cust_id) AS est_unique_customers
FROM movieapp_analytics_v
GROUP BY time_id
ORDER BY 1;
 
-- Now we will use another new feature to find the top 1% of customers using the new Top-N syntax

SELECT
  cust_id,
  MAX(session_id) AS no_of_sessions,
  SUM(no_of_events) AS tot_clicks_session,
  TRUNC(AVG(no_of_events)) AS avg_clicks_session,
  MIN(no_of_events) AS min_clicks_session,
  MAX(no_of_events) AS max_clicks_session
FROM movieapp_analytics_v
GROUP BY cust_id
ORDER BY 2 DESC, 3 DESC
FETCH FIRST 1 PERCENT ROWS ONLY;
 
-- By reversing the sort order we can now find the bottom 1%

SELECT
  cust_id,
  MAX(session_id) AS no_of_sessions,
  SUM(no_of_events) AS tot_clicks_session,
  TRUNC(AVG(no_of_events)) AS avg_clicks_session,
  MIN(no_of_events) AS min_clicks_session,
  MAX(no_of_events) AS max_clicks_session
FROM movieapp_analytics_v
GROUP BY cust_id
ORDER BY 2 ASC, 3 ASC
FETCH FIRST 1 PERCENT ROWS ONLY;
