https://community.cloudera.com/t5/Support-Questions/Hive-Table-loads-less-rows-than-the-actual-rows-in-the-query/m-p/202352#M164358 <P><A href="https://community.hortonworks.com/questions/116755/hive-table-loads-less-rows-than-the-actual-rows-in.html#">@Bala Vignesh N V</A> </P><P>I loaded the external table using the following query </P><P>_____________________________________________________________________________________</P><P>drop table if exists table1; </P><P>CREATE EXTERNAL TABLE table1 (col1 String,col2 String,col3 String,col4 String,col5 String,col6 String,col7 String) </P><P>ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' lines terminated by '\n' STORED AS TEXTFILE </P><P>LOCATION '/user/maria_dev/'; </P><P>select * from table1; The count of the rows for this table is 4,32,65,807.</P><P>_____________________________________________________________________________</P><P>Then I loaded another table with the same query as above. This table has 3,79,405 rows.</P><P>Now , i joined the two queries using the following query </P><P>SELECT distinct tb1.col1,tb1.col2,tb1.col3,tb1.col4,tb1.col5,tb1.col6, tb2.col1,tb2.col2,tb2.col3,tb2.col4,tb2.col5,tb2.col6 </P><P>FROM table1 tb1 LEFT OUTER JOIN table2 tb2 ON (tb1.col1 = tb2.col1);</P><P>The count for this comes out to be 65618892.</P><P>But when i save this query in a external table as follows the count of rows come out to be 11520062.</P><P>____________________________________________________________________________________</P><P>CREATE EXTERNAL TABLE resultant (col1 STRING,col2 STRING,col3 STRING,col4 STRING,col5 STRING, col6 STRING,col7 STRING,col8 STRING,col9 STRING,col10 STRING,col11 STRING, col12 STRING); </P><P>INSERT OVERWRITE TABLE resultant</P><P>SELECT distinct tb1.col1,tb1.col2,tb1.col3,tb1.col4,tb1.col5,tb1.col6, tb2.col1,tb2.col2,tb2.col3,tb2.col4,tb2.col5,tb2.col6 </P><P>FROM table1 tb1 LEFT OUTER JOIN table2 tb2 ON (tb1.col1 = tb2.col1);</P><P>_____________________________________________________________________________________</P><P>This is how i loaded the tables. And i used count(*) and show tblproperties yourTableName to calculate the rows.</P> Mon, 31 Jul 2017 21:31:43 GMT umairmajeed61 2017-07-31T21:31:43Z