vgrabovets
10/31/2016 - 3:31 PM

create external table

create external table

CREATE EXTERNAL TABLE IF NOT EXISTS atos (
  user_id BIGINT,
  country_code STRING,
  object_id BIGINT,
  vodtype_id SMALLINT,
  device_id SMALLINT,
  hits BIGINT,
  duration BIGINT
  )
PARTITIONED BY (month_id INT)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
STORED AS TEXTFILE;

SELECT                    
user_id,      
country_code,                    
object_id,                    
vodtype_id,                    
device_id,                    
sum(hits) hits,                    
sum(duration) duration                   
FROM access_to_objects_stat_new                             
where date >= '2016-10-01' and date <= '2016-10-31'  and user_id > 0                    
group by user_id, object_id, country_code, vodtype_id, device_id;


hadoop fs -mkdir /atos/201511
hadoop fs -put data/201511 /atos/201511

ALTER TABLE atos ADD PARTITION(month_id = 201511)
LOCATION '/atos/201511';

SELECT * FROM atos LIMIT 10;