TECHTalksPro
  • Home
  • Business
    • Internet
    • Market
    • Stock
  • Parent Category
    • Child Category 1
      • Sub Child Category 1
      • Sub Child Category 2
      • Sub Child Category 3
    • Child Category 2
    • Child Category 3
    • Child Category 4
  • Featured
  • Health
    • Childcare
    • Doctors
  • Home
  • SQL Server
    • SQL Server 2012
    • SQL Server 2014
    • SQL Server 2016
  • Downloads
    • PowerShell Scripts
    • Database Scripts
  • Big Data
    • Hadoop
      • Hive
      • Pig
      • HDFS
    • MPP
  • Certifications
    • Microsoft SQL Server -70-461
    • Hadoop-HDPCD
  • Problems/Solutions
  • Interview Questions

Friday, August 12, 2016

Managed Tables/Internal tables and External Tables

 Chitchatiq     8/12/2016 07:40:00 PM     Big Data, BigData&Hadoop, external tables, Hive, Internal tables, Managed tables, SQL Server     No comments   



Managed Tables/Internal tables:

     1.     When we create a table in hive, by default Hive will take care of the data
     2.     It means that Hive engine will move the data into its warehouse directory.

CREATE TABLE managed_table (name STRING);
LOAD DATA INPATH '/user/file1.txt' INTO table managed_table ;

So here file1.txt file will be moved to default warehouse directory which we specify in
hive.metastore.warehouse.dir configuration

    3.     Here if we mistakenly or intentional remove the table, then data associated to that table also be removed and we can’t get back that file.

To avoid this data loss we have to go for External table. 
External Table:
    1.     In external table, Hive will refer to the existing file location and if we remove the table then it simply reference will be removed but not data
    2.     To create external table we simply need to place External keyword while creating the data 
hive>   CREATE EXTERNAL TABLE sample (id INT, name STRING) ROW FORMAT
              DELIMITED FIELDS TERMINATED BY ','
             STORED AS TEXTFILE
              LOCATION '/home/user/file1.txt';
    3.     Here /home/user/file1.txt file will not be moved to warehouse directory instead it will be just referenced to the Sample table

   4.     Hive strongly recommends External table 
  • Share This:  
  •  Facebook
  •  Twitter
  •  Google+
  •  Stumble
  •  Digg
Email ThisBlogThis!Share to XShare to Facebook
Newer Post Older Post Home

0 comments:

Post a Comment

Popular Posts

  • SQL SERVER – Part 1 - What is Data,Database and DBMS
    Before we learn about what SQL server is and how SQL will work, first we would like to know what are DATA, DATABASE, and DBMS keywords ...
  • How to find and delete the top 100 most used files in Linux/Unix
    Steps to find and delete the top 100 most used files in Linux/Unix Command to find most disk space used files: find / -xdev -type f...
  • How to create rename delete database in SQL Server 2008,2012,2014,2016 management studio
    Today we will Learn about How to create,rename,delete database in SQL Server  Here I tried to focus on the people who are new to the S...
  • How to Fix ERROR: CANNOT PARALLELIZE AN UPDATE STATEMENT THAT UPDATES THE DISTRIBUTION COLUMNS Printable View
    Error: [DataDirect][ODBC Greenplum Wire Protocol driver][Greenplum]ERROR: Cannot parallelize an UPDATE statement that updates the distri...
  • SQL Server: Row Count Of All The Tables In Particular Database
    Some times we want to find out the row counts for all the tables in a particular database in SQL Server. We can do it in different ways. I...

Facebook

Categories

Best Practices (1) Big Data (5) BigData&Hadoop (6) DAG (1) Error 10294 (1) external tables (1) File Formats in Hive (1) Greenplum (3) Hadoop (5) Hadoop Commands (1) Hive (4) Internal tables (1) interview Questions (1) Managed tables (1) MySQL Installation (1) ORCFILE (1) org.apache.hadoop.hive.ql.exec.MoveTask (1) Powershell (1) Problems&Solutions (15) RCFILE (1) return code 1 (1) SEQUENCEFILE (1) Service 'userhome' (1) Service 'userhome' check failed: java.io.FileNotFoundException (1) SQL Server (27) sqoop (2) SSIS (1) TEXTFILE (1) Tez (1) transaction manager (1) Views (1) What is Hadoop (1)

Blog Archive

  • December (1)
  • November (1)
  • October (2)
  • September (6)
  • August (1)
  • July (3)
  • March (1)
  • February (8)
  • January (4)
  • December (9)
  • August (4)
  • July (1)

Popular Tags

  • Best Practices
  • Big Data
  • BigData&Hadoop
  • DAG
  • Error 10294
  • external tables
  • File Formats in Hive
  • Greenplum
  • Hadoop
  • Hadoop Commands
  • Hive
  • Internal tables
  • interview Questions
  • Managed tables
  • MySQL Installation
  • ORCFILE
  • org.apache.hadoop.hive.ql.exec.MoveTask
  • Powershell
  • Problems&Solutions
  • RCFILE
  • return code 1
  • SEQUENCEFILE
  • Service 'userhome'
  • Service 'userhome' check failed: java.io.FileNotFoundException
  • SQL Server
  • sqoop
  • SSIS
  • TEXTFILE
  • Tez
  • transaction manager
  • Views
  • What is Hadoop

Featured Post

TOP 100 SQL SERVER INTERVIEW QUESTIONS

SQL SERVER INTERVIEW QUESTIONS 1.       What is the Complex task that you handled in your project 2.       What are the diffe...

Pages

  • Home
  • SQL SERVER
  • Greenplum
  • Hadoop Tutorials
  • Contact US
  • Disclaimer
  • Privacy Policy

Popular Posts

  • Greenplum Best Practises
    Best Practices: A distribution key should not have more than 2 columns, recommended is 1 column. While modeling a database,...
  • SQL SERVER – Part 1 - What is Data,Database and DBMS
    Before we learn about what SQL server is and how SQL will work, first we would like to know what are DATA, DATABASE, and DBMS keywords ...

Copyright © TECHTalksPro
Designed by Vasu