hive.txt

DROP DATABASE IF EXISTS googleplaystore CASCADE;
CREATE SCHEMA IF NOT EXISTS googleplaystore;
DROP TABLE IF EXISTS googleplaystore.apps_temp;
CREATE EXTERNAL TABLE IF NOT EXISTS googleplaystore.apps_temp
(app STRING
,category STRING
,rating FLOAT
,reviews INT
,size FLOAT
,installs INT
,type STRING
,price FLOAT
,content_rating STRING
,genres STRING
,last_updated STRING
,current_ver STRING
,android_ver STRING
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' 
STORED AS TEXTFILE;
SET skip.header.line.count = 1;


CREATE EXTERNAL TABLE IF NOT EXISTS googleplaystore.apps
(app STRING
,category STRING
,rating FLOAT
,reviews INT
,size FLOAT
,installs INT
,type STRING
,price FLOAT
,content_rating STRING
,genres STRING
,last_updated STRING
,current_ver STRING
,android_ver STRING
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' 
STORED AS TEXTFILE;

-- LOAD DATA TO APPS_TEMP
LOAD DATA LOCAL INPATH '/home/hadoopuser/apps.csv' OVERWRITE INTO TABLE googleplaystore.apps_temp;

-- LOAD DATA TO APPS
-- from_unixtime(unix_timestamp('20150101' ,'yyyyMMdd'), 'yyyy-MM-dd')

USE googleplaystore;
INSERT INTO TABLE googleplaystore.apps
SELECT app,category,rating,reviews,size,installs,type,price,content_rating,
genres, 
from_unixtime(unix_timestamp(last_updated,'mm-dd-yyyy'),'yyyy-mm-dd')
, current_ver, android_ver
from googleplaystore.apps_temp;

-- DISPLAY INSERTED DATA
SELECT last_updated FROM googleplaystore.apps LIMIT 2;

-- REMOVE TEMP TABLE
DROP TABLE IF EXISTS googleplaystore.apps_temp;