HIVE的内部表跟外部表区别

建表语法：

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name    -- (Note: TEMPORARY available in Hive 0.14.0 and later)
  [(col_name data_type [COMMENT col_comment], ... [constraint_specification])]
  [COMMENT table_comment]
  [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
  [CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
  [SKEWED BY (col_name, col_name, ...)                  -- (Note: Available in Hive 0.10.0 and later)]
     ON ((col_value, col_value, ...), (col_value, col_value, ...), ...)
     [STORED AS DIRECTORIES]
  [
   [ROW FORMAT row_format] 
   [STORED AS file_format]
     | STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)]  -- (Note: Available in Hive 0.6.0 and later)

加载数据到表中语法：

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]
LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)] [INPUTFORMAT 'inputformat' SERDE 'serde'] (3.0 or later)

LOCAL：从本地系统  linux
不带LOCAL: 从Hadoop文件系统  HDFS
OVERWRITE 数据覆盖
不带OVERWRITE  追加

导出数据到本地系统或者HDFS

INSERT OVERWRITE [LOCAL] DIRECTORY directory1
  [ROW FORMAT row_format] [STORED AS file_format]   SELECT ... FROM ...
hive (default)> insert overwrite local directory '/home/hadoop/emp3.txt' row format DELIMITED FIELDS TERMINATED BY '\t' select * from emp03;
在对应的系统目录可以看到导出的结果文件

1.内部表

默认语法是创建内部表
create table emp03(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal double,
comm double,
deptno int)
row format delimited fields terminated by ',';
创建好表之后，需要导入数据
hive (default)> load data local inpath '/home/hadoop/emp.txt' overwrite into table emp03;
Loading data to table default.emp03
Table default.emp03 stats: [numFiles=1, totalSize=845]
OK
Time taken: 0.248 seconds
但删除内部表，将删除元数据库中的元数据和hdfs上的数据 

2.外部表

创建外部表需要添加 external声明，最好指明在HDFS创建的目录
create external table emp_external(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal double,
comm double,
deptno int
)row format delimited fields terminated by ','
LOCATION '/d7_externel/emp/' ;
外部表不需要加载数据，直接将对应的数据文件放到HDFS的目录就可以
[hadoop@hadoop001 ~]$ hdfs dfs -put emp.txt /d7_externel/emp/
[hadoop@hadoop001 ~]$ hdfs dfs -ls /d7_externel/emp
Found 1 items
-rw-r--r--   1 hadoop supergroup        845 2019-07-18 11:22 /d7_externel/emp/emp.txt
删除外部表，只删除元数据中的元数据，数据文件还存在，建议使用外部表