HIVE Query

gistfile1.txt

Creation of table:

CREATE TABLE PCLA
(TICKETNUMBER STRING,ISSUEDATE STRING,ISSUETIME STRING,
METERID STRING,MARKEDTIME STRING,RPSTATEPLATE STRING,
PLATEEXPIRYDATE STRING,VIN VARCHAR(50),MAKE STRING,
BODYSTYLE STRING,COLOR STRING,LOCATION STRING,
ROUTE STRING,AGENCY STRING, VIOLATIONCODE STRING,VIOLATIONDESCRIPTION STRING,
FINEAMOUNT DOUBLE, LATITUDE STRING, LONGITUDE STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE;

Loading into table: 
Load data inpath 'wasb://pratikbhayani@pratsdbrat.blob.core.windows.net/PCLA.csv’ overwrite into table PCLA;

View all rows:
SELECT * FROM PCLA;
 
ISSUE DATE:
DROP TABLE IF EXISTS PCID;
CREATE TABLE PCID AS 
SELECT ISSUEDATE, COUNT (ISSUEDATE)
FROM PCLA
GROUP BY ISSUEDATE;

ISSUE TIME:
DROP TABLE IF EXISTS PCIT;
CREATE TABLE PCIT AS 
SELECT ISSUETIME, COUNT (ISSUETIME)
FROM PCLA
GROUP BY ISSUETIME;

VIOLATION DESCRIPTION:
DROP TABLE IF EXISTS PCVD;
CREATE TABLE PCVD AS
SELECT VIOLATIONDESCRIPTION, COUNT(VIOLATIONDESCRIPTION) as TotalNumberVD
FROM PCLA 
GROUP BY VIOLATIONDESCRIPTION;

FINE AMOUNT:
DROP TABLE IF EXISTS PCFA;
CREATE TABLE PCFA AS
SELECT VIOLATIONDESCRIPTION, SUM(FINEAMOUNT) AS TotalEachFineAmount
FROM PCLA 
GROUP BY VIOLATIONDESCRIPTION;

RPSTATEPLATE:
DROP TABLE IF EXISTS PCRP;
CREATE TABLE PCRP AS
SELECT RPSTATEPLATE, Count(RPSTATEPLATE) AS TotalCount
FROM PCLA 
WHERE RPSTATEPLATE != ‘CA’ 
GROUP BY RPSTATEPLATE;

LOCATION:
DROP TABLE IF EXISTS PCLO;
CREATE TABLE PCLO AS
SELECT RPSTATEPLATE, LOCATION, LATITUDE, LONGITUDE 
FROM PCLA
WHERE RPSTATEPLATE != ‘CA’ ;


NO PARK/STREET CLEAN:
DROP TABLE IF EXISTS PCIV;
CREATE TABLE PCIV AS
SELECT ISSUEDATE, VIOLATIONDESCRIPTION
FROM PCLA
WHERE VIOLATIONDESCRIPTION = ‘NO PARK/STREET CLEAN‘;

Meter Expiry:
DROP TABLE IF EXISTS PCME;
CREATE TABLE PCME AS
SELECT ISSUEDATE, VIOLATIONDESCRIPTION
FROM PCLA
WHERE VIOLATIONDESCRIPTION = ‘METER EXP.’;