2qvtfRJA

1CREATE EXTERNAL TABLE IF NOT EXISTS name_basics_ext (
2    nconst STRING,primaryName STRING,birthYear INT, deathYear INT, primaryProfession STRING, knownForTitles STRING
3)
4ROW FORMAT DELIMITED
5FIELDS TERMINATED BY '\t'
6STORED AS TEXTFILE
7location '${input_dir4}';
8
9CREATE EXTERNAL TABLE IF NOT EXISTS mapreduce_out_ext (
10    nconst STRING,acted INT,directed INT
11)
12ROW FORMAT DELIMITED
13FIELDS TERMINATED BY '\t'
14STORED AS TEXTFILE
15location '${input_dir3}';
16
17CREATE TABLE IF NOT EXISTS people_output(
18primaryName STRING, role STRING, movies INT
19)
20ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.JsonSerDe'
21STORED AS TEXTFILE
22location '${output_dir6}';
23
24INSERT INTO people_output
25SELECT person.primaryName, 'actor', mr.acted FROM name_basics_ext person, mapreduce_out_ext mr
26WHERE person.nconst = mr.nconst AND
27(person.primaryProfession LIKE '%actor%' OR person.primaryProfession LIKE '%actress%')
28ORDER BY mr.acted DESC
29LIMIT 3;
30
31INSERT INTO people_output
32SELECT person.primaryName, 'director', mr.directed FROM name_basics_ext person, mapreduce_out_ext mr
33WHERE person.nconst = mr.nconst AND
34person.primaryProfession LIKE '%director%'
35ORDER BY mr.directed DESC
36LIMIT 3;