Pacific-Design.com

    
Home Index

1. Apache Hive

2. Query Aggregation

Apache Hive / Query Aggregation /

Hive Aggregation Query

DROP TABLE visits;
DROP TABLE visits_aggre;
SET mapred.reduce.tasks=60;

-- ------------------------------------------------- --
CREATE EXTERNAL TABLE visits (
        source_ip       STRING,
        dest_url        STRING,
        visit_date      STRING,
        ad_revenue      DOUBLE,
        user_agent      STRING,
        country_code    STRING,
        language_code   STRING,
        search_word     STRING,
        duration        INT 
) 
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' 
STORED AS TEXTFILE 
LOCATION 's3n://kduraj/data/visits';

-- ------------------------------------------------- --
CREATE TABLE visits_aggre ( 
        source_ip       STRING, 
        sum_ad_revenue  DOUBLE
);

-- ------------------------------------------------- --
INSERT OVERWRITE TABLE visits_aggre 
    SELECT source_ip, SUM(ad_revenue) 
    FROM visits 
    GROUP BY source_ip;