Pig Hive Table Layout Report

/*

INPUT for 'raw' is a hdfs dfs -ls -R $pathToTableOnHDFS


(.{11} ) .* (\d*) (\d{4}-\d{2}-\d{2} \d{2}:\d{2}) (\/project\/.*)
(.{11} ) .* (\d*) (\d{4}-\d{2}-\d{2} \d{2}:\d{2}) (\/project\/.*)\d{6}_\d.*
*/

raw = LOAD 'reports/in/$REPORT' using TextLoader();
noStage = FILTER raw BY NOT($0 MATCHES '.*staging.*');
columns = FOREACH noStage GENERATE FLATTEN(REGEX_EXTRACT_ALL($0, '(.{11}) .* (\\d*) (\\d{4}-\\d{2}-\\d{2} \\d{2}:\\d{2}) (\\/project\\/.*)\\d{6}_\\d.*'));
--columns = FOREACH noStage GENERATE FLATTEN(REGEX_EXTRACT_ALL($0, '(.{11}) .* (\\d*) (\\d{4}-\\d{2}-\\d{2} \\d{2}:\\d{2}) (hdfs:\\/\\/hdp2cluster\\/project\\/.*)\\d{6}_\\d.*'));
names = FOREACH columns GENERATE $0 as perms, (biginteger)$1 as bytes, $2 as timstim, $3 as part;
noNullDirs = FILTER names BY NOT(perms is null) and NOT(perms matches 'd.*');

--TOTALS
groupall = GROUP noNullDirs ALL;
avgFileSize = FOREACH groupall GENERATE 'COMPLETE VIEW ', COUNT(noNullDirs.part) as fileCount, MIN(noNullDirs.bytes) as minBytes, AVG(noNullDirs.bytes) as avgBytes, MAX(noNullDirs.bytes) as maxBytes;

--Per Partition Info
groupparts = GROUP noNullDirs by part;
avgFilesPerPart = FOREACH groupparts GENERATE group, COUNT(noNullDirs.part) as fileCount, MIN(noNullDirs.bytes) as minBytes, AVG(noNullDirs.bytes) as avgBytes, MAX(noNullDirs.bytes) as maxBytes, SUM(noNullDirs.bytes) as partitionSize;
mergable = FILTER avgFilesPerPart BY fileCount > 1;


mergeordered = ORDER mergable by fileCount desc;
top25merge = LIMIT mergeordered 25;


--Partition Info
partsProjection = FOREACH noNullDirs GENERATE part;
distparts = DISTINCT partsProjection;
distpartsgroup = GROUP distparts ALL;
totalParts = FOREACH distpartsgroup GENERATE 'TOTAL PARTITION COUNT: ', COUNT(distparts.part);


--Files Under 128mb
smalls = FILTER noNullDirs BY bytes < 128000000;
smallsgroup = GROUP smalls ALL;
suboptFiles = FOREACH smallsgroup GENERATE 'TOTAL FILES UNDER 128MB: ', COUNT(smalls.bytes);


--under 1mb
micos = FILTER noNullDirs BY bytes < 1000000;
micosgroup = GROUP micos ALL;
micossuboptFiles = FOREACH micosgroup GENERATE 'TOTAL FILES UNDER 1MB: ', COUNT(micos.bytes);

--0 byte files
zeros = FILTER noNullDirs BY bytes == 0;
zerosgroup = GROUP zeros ALL;
zerossuboptFiles = FOREACH zerosgroup GENERATE 'TOTAL FILES EQUAL TO 0 BYTES: ', COUNT(zeros.bytes);



--Files over 128mb
bigs = FILTER noNullDirs BY bytes >= 128000000;
bigsgroup = GROUP bigs ALL;
bigssuboptFiles = FOREACH bigsgroup GENERATE 'TOTAL FILES OVER 1MB: ', COUNT(bigs.bytes);


completeReport = UNION avgFileSize, totalParts, mergeordered, suboptFiles, micossuboptFiles, zerossuboptFiles, bigssuboptFiles;

store completeReport into 'reports/out/$REPORT';


/*

TYPE // PARTITION	COUNT	MIN	AVG	MAX	SUM
COMPLETE VIEW 	41030	45752	16841738.95	4390874709
TOTAL FILES UNDER 128MB: 	39454
TOTAL FILES UNDER 1MB: 	34688
TOTAL PARTITION COUNT: 	3085
TOTAL FILES OVER 1MB: 	1576
/hte/region=NA/country=USA/date_x=1970-01-01/	656	47734	411722.3018	172352750	270089830
/hte/region=NA/country=UNKNOWN/date_x=1970-01-01/	591	46928	132484.8376	45530519	78298539

TOTAL FILES UNDER 1MB:  931
TOTAL PARTITION COUNT:  1
TOTAL FILES UNDER 128MB:        945
TOTAL FILES OVER 1MB:   99
TOTAL FILES EQUAL TO 0 BYTES:   926
COMPLETE VIEW   1044    0       108098305.1427203065134099616858238     3364497810

*/