These are my ddls and the select transform query which calls a python
script which returns a json string and date.
I tried it with for a single day it is inserting data properly . Also I
tried using local tables instead of external tables and it works fine.
CREATE EXTERNAL TABLE IF NOT EXISTS t_ft_sessionsin3(jsonstr string)
partitioned by (dt string )
LOCATION 's3n://bucketname' ;
CREATE EXTERNAL TABLE IF NOT EXISTS t_ft_events3
(event_Type string,
event_id int,
Subscriber_id string,
Session_id string,
time_gmt_hit_int int,
time_client_hit string,
time_client_hit_int int,
time_gmt_sessionstart string,
time_gmt_sessionstart_int int,
Session_Duration_mins string,
visit_page_num int,
PageType string,
page_url string,
pagename string,
Channel string,
Title string,
Site_Section string,
Sub_Section string,
Page_Source string,
Article_id string,
Device String,
time_gmt_YearNum string,
time_gmt_MonthNum string,
time_gmt_DayNum string,
time_gmt_HourOfDay string,
ip string,
country string,
language string,
geo_country string,
geo_region string,
geo_city string ) partitioned by (Dt string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LOCATION 's3n://bucketname';
set hive.optimize.s3.query=true;
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.dynamic.partition=true ;
set mapred.reduce.tasks=1;
FROM (
FROM t_ft_events3
SELECT TRANSFORM(session_id, subscriber_id, time_gmt_sessionstart,
session_duration_mins, ip, device, country, title, event_type, page_url,
channel, dt )
USING '/bin/cat'
AS session_id, subscriber_id, time_gmt_sessionstart,
session_duration_mins, ip, device, country, title,
event_type, page_url, channel ,dt
WHERE dt='2011-10-21'
CLUSTER BY session_id) map_output
INSERT OVERWRITE TABLE t_ft_sessionsin3 partition(dt)
SELECT TRANSFORM(map_output.session_id, map_output.subscriber_id,
map_output.time_gmt_sessionstart, map_output.session_duration_mins,
map_output.ip, map_output.device, map_output.country, map_output.title,
map_output.event_type, map_output.page_url, map_output.channel,
map_output.dt )
USING 's3n://analytics.newsint.co.uk/hive-scripts/reducep_debug.py'
AS jsonstr ,dt ;
On Fri, Oct 28, 2011 at 1:44 PM, Thulasi Ram Naidu Peddineni <
thulasiram...@gmail.com> wrote:
> Can you please add ddls of both the tables and insert/CTAS statement ?
>
> I have been using dynamic partitions in S3 since a long time and
> haven't faced any issues.
> -----
> Regards,
> Thulasi Ram P
>
>
>
> On Fri, Oct 28, 2011 at 6:01 PM, Deepak Subhramanian
> <deepak.subhraman...@gmail.com> wrote:
> > I used hive ver 0.71 in Amazon elastic map reduce to insert data from a
> > partitioned table to another partitioned table. But for some reason it is
> > not updating all the data in the partition table. I am running a map
> reduce
> > job to do the insert. The log says it updated 4 million rows . But when
> I do
> > a select query I can see only 40000 rows . The data in output and input
> > tables are partitioned by date. When I ran it for a single day in a map
> > reduce the count matches. But when I do for multiple dates it is only
> > updating only less than 10 percent of the data. Any thoughts ?
> >
> >
> > 2011-10-26 22:00:36,488 Stage-1 map = 100%, reduce = 100% Ended Job =
> > job_201110262111_0001 Loading data to table
> omniture.tmp_session_samplepin
> > partition (dt=null) Loading partition {dt=2011-07-20} Loading partition
> > {dt=2011-07-21} Loading partition {dt=2011-07-22} Loading partition
> > {dt=2011-07-23} Loading partition {dt=2011-07-24} Loading partition
> > {dt=2011-07-25} Loading partition {dt=2011-07-26} Loading partition
> > {dt=2011-07-27} Loading partition {dt=2011-07-28} Loading partition
> > {dt=2011-07-29} Loading partition {dt=2011-07-30} Loading partition
> > {dt=2011-07-31} Loading partition {dt=2011-08-01} Loading partition
> > {dt=2011-08-02} Loading partition {dt=2011-08-03} Loading partition
> > {dt=2011-08-04} Loading partition {dt=2011-08-05} Loading partition
> > {dt=2011-08-06} Loading partition {dt=2011-08-07} Loading partition
> > {dt=2011-08-08} Loading partition {dt=2011-08-09} Loading partition
> > {dt=2011-08-10} Loading partition {dt=2011-08-11} Loading partition
> > {dt=2011-08-12} Loading partition {dt=2011-08-13} Loading partition
> > {dt=2011-08-14} Loading partition {dt=2011-08-15} Loading partition
> > {dt=2011-08-16} Loading partition {dt=2011-08-17} Loading partition
> > {dt=2011-08-18} Loading partition {dt=2011-08-19} Loading partition
> > {dt=2011-08-20} Loading partition {dt=2011-08-21} Loading partition
> > {dt=2011-08-22} Loading partition {dt=2011-08-23} Loading partition
> > {dt=2011-08-24} Loading partition {dt=2011-08-25} Loading partition
> > {dt=2011-08-26} Loading partition {dt=2011-08-27} Loading partition
> > {dt=2011-08-28} Loading partition {dt=2011-08-29} Loading partition
> > {dt=2011-08-30} Loading partition {dt=2011-08-31} Loading partition
> > {dt=2011-09-01} Loading partition {dt=2011-09-02} Loading partition
> > {dt=2011-09-03} Loading partition {dt=2011-09-04} Loading partition
> > {dt=2011-09-05} Loading partition {dt=2011-09-06} Loading partition
> > {dt=2011-09-07} Loading partition {dt=2011-09-08} Loading partition
> > {dt=2011-09-09} Loading partition {dt=2011-09-11} Loading partition
> > {dt=2011-09-12} Loading partition {dt=2011-09-13} Loading partition
> > {dt=2011-09-14} Loading partition {dt=2011-09-15} Loading partition
> > {dt=2011-09-16} Loading partition {dt=2011-09-17} Loading partition
> > {dt=2011-09-18} Loading partition {dt=2011-09-19} Loading partition
> > {dt=2011-09-20} Loading partition {dt=2011-09-21} Loading partition
> > {dt=2011-09-22} Loading partition {dt=2011-09-23} Loading partition
> > {dt=2011-09-24} Loading partition {dt=2011-09-25} Loading partition
> > {dt=2011-09-26} Loading partition {dt=2011-09-27} Loading partition
> > {dt=2011-09-28} Loading partition {dt=2011-09-29} Loading partition
> > {dt=2011-09-30} Loading partition {dt=2011-10-01} Loading partition
> > {dt=2011-10-02} Loading partition {dt=2011-10-03} Loading partition
> > {dt=2011-10-04} Loading partition {dt=2011-10-05} Loading partition
> > {dt=2011-10-06} Loading partition {dt=2011-10-07} Loading partition
> > {dt=2011-10-08} Loading partition {dt=2011-10-09} Loading partition
> > {dt=2011-10-10} Loading partition {dt=2011-10-11} Loading partition
> > {dt=2011-10-12} Loading partition {dt=2011-10-13} Loading partition
> > {dt=2011-10-14} Loading partition {dt=2011-10-15} Loading partition
> > {dt=2011-10-16} Loading partition {dt=2011-10-17} Loading partition
> > {dt=2011-10-18} Loading partition {dt=2011-10-19} Loading partition
> > {dt=2011-10-20} Loading partition {dt=2011-10-21} Loading partition
> > {dt=2011-10-22} Loading partition {dt=2011-10-23} Partition
> > omniture.tmp_session_samplepin{dt=2011-07-20} stats: [num_files: 7,
> > num_rows: 0, total_size: 10434074] Partition
> > omniture.tmp_session_samplepin{dt=2011-07-21} stats: [num_files: 7,
> > num_rows: 0, total_size: 9657307] Partition
> > omniture.tmp_session_samplepin{dt=2011-07-22} stats: [num_files: 7,
> > num_rows: 0, total_size: 12210362] Partition
> > omniture.tmp_session_samplepin{dt=2011-07-23} stats: [num_files: 7,
> > num_rows: 0, total_size: 9156180] Partition
> > omniture.tmp_session_samplepin{dt=2011-07-24} stats: [num_files: 7,
> > num_rows: 0, total_size: 9875831] Partition
> > omniture.tmp_session_samplepin{dt=2011-07-25} stats: [num_files: 7,
> > num_rows: 0, total_size: 12464456] Partition
> > omniture.tmp_session_samplepin{dt=2011-07-26} stats: [num_files: 7,
> > num_rows: 0, total_size: 11322533] Partition
> > omniture.tmp_session_samplepin{dt=2011-07-27} stats: [num_files: 7,
> > num_rows: 0, total_size: 10085319] Partition
> > omniture.tmp_session_samplepin{dt=2011-07-28} stats: [num_files: 7,
> > num_rows: 0, total_size: 9609980] Partition
> > omniture.tmp_session_samplepin{dt=2011-07-29} stats: [num_files: 7,
> > num_rows: 0, total_size: 11087141] Partition
> > omniture.tmp_session_samplepin{dt=2011-07-30} stats: [num_files: 7,
> > num_rows: 0, total_size: 7960950] Partition
> > omniture.tmp_session_samplepin{dt=2011-07-31} stats: [num_files: 7,
> > num_rows: 0, total_size: 9047044] Partition
> > omniture.tmp_session_samplepin{dt=2011-08-01} stats: [num_files: 7,
> > num_rows: 0, total_size: 10821735] Partition
> > omniture.tmp_session_samplepin{dt=2011-08-02} stats: [num_files: 7,
> > num_rows: 0, total_size: 10399366] Partition
> > omniture.tmp_session_samplepin{dt=2011-08-03} stats: [num_files: 7,
> > num_rows: 0, total_size: 10223912] Partition
> > omniture.tmp_session_samplepin{dt=2011-08-04} stats: [num_files: 7,
> > num_rows: 0, total_size: 10282720] Partition
> > omniture.tmp_session_samplepin{dt=2011-08-05} stats: [num_files: 7,
> > num_rows: 0, total_size: 11603154] Partition
> > omniture.tmp_session_samplepin{dt=2011-08-06} stats: [num_files: 7,
> > num_rows: 0, total_size: 8455494] Partition
> > omniture.tmp_session_samplepin{dt=2011-08-07} stats: [num_files: 7,
> > num_rows: 0, total_size: 10532983] Partition
> > omniture.tmp_session_samplepin{dt=2011-08-08} stats: [num_files: 7,
> > num_rows: 0, total_size: 11769965] Partition
> > omniture.tmp_session_samplepin{dt=2011-08-09} stats: [num_files: 7,
> > num_rows: 0, total_size: 11865296] Partition
> > omniture.tmp_session_samplepin{dt=2011-08-10} stats: [num_files: 7,
> > num_rows: 0, total_size: 3585028] Partition
> > omniture.tmp_session_samplepin{dt=2011-08-11} stats: [num_files: 7,
> > num_rows: 0, total_size: 1954069] Partition
> > omniture.tmp_session_samplepin{dt=2011-08-12} stats: [num_files: 7,
> > num_rows: 0, total_size: 3932147] Partition
> > omniture.tmp_session_samplepin{dt=2011-08-13} stats: [num_files: 7,
> > num_rows: 0, total_size: 2605977] Partition
> > omniture.tmp_session_samplepin{dt=2011-08-14} stats: [num_files: 7,
> > num_rows: 0, total_size: 9998480] Partition
> > omniture.tmp_session_samplepin{dt=2011-08-15} stats: [num_files: 7,
> > num_rows: 0, total_size: 2587110] Partition
> > omniture.tmp_session_samplepin{dt=2011-08-16} stats: [num_files: 7,
> > num_rows: 0, total_size: 65480] Partition
> > omniture.tmp_session_samplepin{dt=2011-08-17} stats: [num_files: 7,
> > num_rows: 0, total_size: 1946485] Partition
> > omniture.tmp_session_samplepin{dt=2011-08-18} stats: [num_files: 7,
> > num_rows: 0, total_size: 1844330] Partition
> > omniture.tmp_session_samplepin{dt=2011-08-19} stats: [num_files: 7,
> > num_rows: 0, total_size: 3422826] Partition
> > omniture.tmp_session_samplepin{dt=2011-08-20} stats: [num_files: 7,
> > num_rows: 0, total_size: 11952735] Partition
> > omniture.tmp_session_samplepin{dt=2011-08-21} stats: [num_files: 7,
> > num_rows: 0, total_size: 9689620] Partition
> > omniture.tmp_session_samplepin{dt=2011-08-22} stats: [num_files: 7,
> > num_rows: 0, total_size: 57152] Partition
> > omniture.tmp_session_samplepin{dt=2011-08-23} stats: [num_files: 7,
> > num_rows: 0, total_size: 192655] Partition
> > omniture.tmp_session_samplepin{dt=2011-08-24} stats: [num_files: 7,
> > num_rows: 0, total_size: 3857399] Partition
> > omniture.tmp_session_samplepin{dt=2011-08-25} stats: [num_files: 7,
> > num_rows: 0, total_size: 3155931] Partition
> > omniture.tmp_session_samplepin{dt=2011-08-26} stats: [num_files: 7,
> > num_rows: 0, total_size: 3793219] Partition
> > omniture.tmp_session_samplepin{dt=2011-08-27} stats: [num_files: 7,
> > num_rows: 0, total_size: 115756] Partition
> > omniture.tmp_session_samplepin{dt=2011-08-28} stats: [num_files: 7,
> > num_rows: 0, total_size: 9817508] Partition
> > omniture.tmp_session_samplepin{dt=2011-08-29} stats: [num_files: 7,
> > num_rows: 0, total_size: 3316386] Partition
> > omniture.tmp_session_samplepin{dt=2011-08-30} stats: [num_files: 7,
> > num_rows: 0, total_size: 279793] Partition
> > omniture.tmp_session_samplepin{dt=2011-08-31} stats: [num_files: 7,
> > num_rows: 0, total_size: 2570541] Partition
> > omniture.tmp_session_samplepin{dt=2011-09-01} stats: [num_files: 7,
> > num_rows: 0, total_size: 1038844] Partition
> > omniture.tmp_session_samplepin{dt=2011-09-02} stats: [num_files: 7,
> > num_rows: 0, total_size: 7035963] Partition
> > omniture.tmp_session_samplepin{dt=2011-09-03} stats: [num_files: 7,
> > num_rows: 0, total_size: 1361662] Partition
> > omniture.tmp_session_samplepin{dt=2011-09-04} stats: [num_files: 7,
> > num_rows: 0, total_size: 10539394] Partition
> > omniture.tmp_session_samplepin{dt=2011-09-05} stats: [num_files: 7,
> > num_rows: 0, total_size: 509046] Partition
> > omniture.tmp_session_samplepin{dt=2011-09-06} stats: [num_files: 7,
> > num_rows: 0, total_size: 6378159] Partition
> > omniture.tmp_session_samplepin{dt=2011-09-07} stats: [num_files: 7,
> > num_rows: 0, total_size: 326020] Partition
> > omniture.tmp_session_samplepin{dt=2011-09-08} stats: [num_files: 7,
> > num_rows: 0, total_size: 3612134] Partition
> > omniture.tmp_session_samplepin{dt=2011-09-09} stats: [num_files: 7,
> > num_rows: 0, total_size: 302039] Partition
> > omniture.tmp_session_samplepin{dt=2011-09-11} stats: [num_files: 7,
> > num_rows: 0, total_size: 10408263] Partition
> > omniture.tmp_session_samplepin{dt=2011-09-12} stats: [num_files: 7,
> > num_rows: 0, total_size: 464549] Partition
> > omniture.tmp_session_samplepin{dt=2011-09-13} stats: [num_files: 7,
> > num_rows: 0, total_size: 310105] Partition
> > omniture.tmp_session_samplepin{dt=2011-09-14} stats: [num_files: 7,
> > num_rows: 0, total_size: 167951] Partition
> > omniture.tmp_session_samplepin{dt=2011-09-15} stats: [num_files: 7,
> > num_rows: 0, total_size: 3818614] Partition
> > omniture.tmp_session_samplepin{dt=2011-09-16} stats: [num_files: 7,
> > num_rows: 0, total_size: 541854] Partition
> > omniture.tmp_session_samplepin{dt=2011-09-17} stats: [num_files: 7,
> > num_rows: 0, total_size: 10594696] Partition
> > omniture.tmp_session_samplepin{dt=2011-09-18} stats: [num_files: 7,
> > num_rows: 0, total_size: 10484008] Partition
> > omniture.tmp_session_samplepin{dt=2011-09-19} stats: [num_files: 7,
> > num_rows: 0, total_size: 440114] Partition
> > omniture.tmp_session_samplepin{dt=2011-09-20} stats: [num_files: 7,
> > num_rows: 0, total_size: 650875] Partition
> > omniture.tmp_session_samplepin{dt=2011-09-21} stats: [num_files: 7,
> > num_rows: 0, total_size: 3567028] Partition
> > omniture.tmp_session_samplepin{dt=2011-09-22} stats: [num_files: 7,
> > num_rows: 0, total_size: 1300349] Partition
> > omniture.tmp_session_samplepin{dt=2011-09-23} stats: [num_files: 7,
> > num_rows: 0, total_size: 2651200] Partition
> > omniture.tmp_session_samplepin{dt=2011-09-24} stats: [num_files: 7,
> > num_rows: 0, total_size: 2772420] Partition
> > omniture.tmp_session_samplepin{dt=2011-09-25} stats: [num_files: 7,
> > num_rows: 0, total_size: 10632678] Partition
> > omniture.tmp_session_samplepin{dt=2011-09-26} stats: [num_files: 7,
> > num_rows: 0, total_size: 270883] Partition
> > omniture.tmp_session_samplepin{dt=2011-09-27} stats: [num_files: 7,
> > num_rows: 0, total_size: 123348] Partition
> > omniture.tmp_session_samplepin{dt=2011-09-28} stats: [num_files: 7,
> > num_rows: 0, total_size: 3942798] Partition
> > omniture.tmp_session_samplepin{dt=2011-09-29} stats: [num_files: 7,
> > num_rows: 0, total_size: 3604820] Partition
> > omniture.tmp_session_samplepin{dt=2011-09-30} stats: [num_files: 7,
> > num_rows: 0, total_size: 3410087] Partition
> > omniture.tmp_session_samplepin{dt=2011-10-01} stats: [num_files: 7,
> > num_rows: 0, total_size: 14479785] Partition
> > omniture.tmp_session_samplepin{dt=2011-10-02} stats: [num_files: 7,
> > num_rows: 0, total_size: 10341814] Partition
> > omniture.tmp_session_samplepin{dt=2011-10-03} stats: [num_files: 7,
> > num_rows: 0, total_size: 17654326] Partition
> > omniture.tmp_session_samplepin{dt=2011-10-04} stats: [num_files: 7,
> > num_rows: 0, total_size: 14857341] Partition
> > omniture.tmp_session_samplepin{dt=2011-10-05} stats: [num_files: 7,
> > num_rows: 0, total_size: 13955605] Partition
> > omniture.tmp_session_samplepin{dt=2011-10-06} stats: [num_files: 7,
> > num_rows: 0, total_size: 17842293] Partition
> > omniture.tmp_session_samplepin{dt=2011-10-07} stats: [num_files: 7,
> > num_rows: 0, total_size: 19304223] Partition
> > omniture.tmp_session_samplepin{dt=2011-10-08} stats: [num_files: 7,
> > num_rows: 0, total_size: 16108471] Partition
> > omniture.tmp_session_samplepin{dt=2011-10-09} stats: [num_files: 7,
> > num_rows: 0, total_size: 10930563] Partition
> > omniture.tmp_session_samplepin{dt=2011-10-10} stats: [num_files: 7,
> > num_rows: 0, total_size: 17987157] Partition
> > omniture.tmp_session_samplepin{dt=2011-10-11} stats: [num_files: 7,
> > num_rows: 0, total_size: 16489909] Partition
> > omniture.tmp_session_samplepin{dt=2011-10-12} stats: [num_files: 7,
> > num_rows: 0, total_size: 16922397] Partition
> > omniture.tmp_session_samplepin{dt=2011-10-13} stats: [num_files: 7,
> > num_rows: 0, total_size: 13364996] Partition
> > omniture.tmp_session_samplepin{dt=2011-10-14} stats: [num_files: 7,
> > num_rows: 0, total_size: 14494602] Partition
> > omniture.tmp_session_samplepin{dt=2011-10-15} stats: [num_files: 7,
> > num_rows: 0, total_size: 14919150] Partition
> > omniture.tmp_session_samplepin{dt=2011-10-16} stats: [num_files: 7,
> > num_rows: 0, total_size: 10587640] Partition
> > omniture.tmp_session_samplepin{dt=2011-10-17} stats: [num_files: 7,
> > num_rows: 0, total_size: 13196669] Partition
> > omniture.tmp_session_samplepin{dt=2011-10-18} stats: [num_files: 7,
> > num_rows: 0, total_size: 16978668] Partition
> > omniture.tmp_session_samplepin{dt=2011-10-19} stats: [num_files: 7,
> > num_rows: 0, total_size: 13949476] Partition
> > omniture.tmp_session_samplepin{dt=2011-10-20} stats: [num_files: 7,
> > num_rows: 0, total_size: 16292963] Partition
> > omniture.tmp_session_samplepin{dt=2011-10-21} stats: [num_files: 7,
> > num_rows: 0, total_size: 18786101] Partition
> > omniture.tmp_session_samplepin{dt=2011-10-22} stats: [num_files: 7,
> > num_rows: 0, total_size: 16207979] Partition
> > omniture.tmp_session_samplepin{dt=2011-10-23} stats: [num_files: 7,
> > num_rows: 0, total_size: 11097487] Table omniture.tmp_session_samplepin
> > stats: [num_partitions: 95, num_files: 665, num_rows: 0, total_size:
> > 747615945] 4488000 Rows loaded to tmp_session_samplepin OK Time taken:
> > 2866.067 seconds Command exiting with ret '0'
> >
> > --
> > Deepak Subhramanian
> >
>
--
Deepak Subhramanian
