title: Confusion with the external tables in hive
body: <p>I have created the hive external table using below command:</p>
  
  <pre><code> use hive2;
  
  create external table depTable (depId int comment 'This is the unique id for each dep', depName string,location string) comment 'department table' row format delimited fields terminated by "," 
  stored as textfile location '/dataDir/';  
  </code></pre>
  
  <p>Now, when I view the HDFS I can see the db but there is no <code>depTable</code> inside the warehouse.  </p>
  
  <pre><code>[cloudera@quickstart ~]$ hadoop fs -ls /user/hive/warehouse/hive2.db
  [cloudera@quickstart ~]$ 
  </code></pre>
  
  <p>Above you can see that there is no table created in this DB. As far as I know, external tables are not stored in the hive warehouse.So am I correct ?? If yes then where is it stored ??<br>
  But if I create external table first and then load the data then I am able to see the file inside <code>hive2.db</code>.   </p>
  
  <pre><code>hive&gt; create external table depTable (depId int comment 'This is the unique id for each dep', depName string,location string) comment 'department table' row format delimited fields terminated by "," stored as textfile;
  OK
  Time taken: 0.056 seconds
  hive&gt; load data inpath '/dataDir/department_data.txt' into table depTable;
  Loading data to table default.deptable
  Table default.deptable stats: [numFiles=1, totalSize=90]
  OK
  Time taken: 0.28 seconds
  hive&gt; select * from deptable;
  OK
  1001    FINANCE SYDNEY
  2001    AUDIT   MELBOURNE
  3001    MARKETING   PERTH
  4001    PRODUCTION  BRISBANE
  </code></pre>
  
  <p>Now, if I fire the <code>hadoop fs</code> query I can see this table under database as below:  </p>
  
  <pre><code>[cloudera@quickstart ~]$ hadoop fs -ls /user/hive/warehouse/hive2.db
  Found 1 items
  drwxrwxrwx   - cloudera supergroup          0 2019-01-17 09:07 /user/hive/warehouse/hive2.db/deptable  
  </code></pre>
  
  <p>If I delete the table still I am able to see table in the HDFS as below:</p>
  
  <pre><code>[cloudera@quickstart ~]$ hadoop fs -ls /user/hive/warehouse/hive2.db
  Found 1 items
  drwxrwxrwx   - cloudera supergroup          0 2019-01-17 09:11 /user/hive/warehouse/hive2.db/deptable  
  </code></pre>
  
  <p>So, what is the exact behavior of the external tables ?? When I create using <code>LOCATION</code> keyword where does it get stored and when I create using <code>load</code> statement why it is getting stored in the <code>HDFS</code> and after deleting why it doesn't get deleted. </p>