hive 内部表与外部表的区别

按照管理权限分为：内部表和外部表
区分原理: 真实表数据的管理权限 是 hive 内部所有 ， 还是 hdfs 所有（external table）

hive 默认创建内部表, 若建表指定 external 则创建外部表

两者之间的区别:

删除内部表，删除表元数据和数据
删除外部表，只删除元数据，不删除实际表中的数据

两者使用场景:

如果一份数据仅仅只是使用 Hive 做统计分析，则可以使用内部表
若数据已存储在 HDFS, 需使用 Hive 去分析, 且该数据还有可能要使用其他的计算引擎去计算,则使用外部表

hive 分桶表与分区表

区别简述: (详细研究两表后自然了解)

分区表与分桶表之间区别（面试重点）：

  • 分区在HDFS上的表现形式是一个目录， 分桶在HDFS的表现形式上是一个单独的文件
  • 分桶随机分割数据库, 分区非随机分割数据库
  • 分桶按照列的 hash 函数相对均匀; 分区按照列值分割,容易造成数据倾斜

分区:
细化数据管理，直接读对应目录，缩小mapreduce程序要扫描的数据量

分桶：
1、提高join查询的效率（用分桶字段做连接字段）
2、提高采样的效率

以下是分桶表分区表详解:

分区表（partition）： 根据某些字段进行分区操作

  • hive中查询需要扫描整个表内容, 为了只扫描表中一部分数据, 引出分区概念
  • 分区的方法可以减少一次MapReduce扫描的总数据量, 以此改善性能
  • 分区使用HDFS的子目录功能实现, 每一个子目录包含了分区对应的列名和每一列的值,

注意: HDFS 不支持过多的子目录, 也给分区的使用带来了限制

创建分区表,即在创建表时加入一句话.注意:这里分区字段不能与表中字段重复
partitioned by (pt_d string)  		

向分区中加载数据
load data local inpath '/home/hadoop/Desktop/data' overwrite into table t1 partition ( pt_d = '000000');

查看分区
show partitions table_name;

添加一个分区
alter table t1 add partition (pt_d = ‘000000’);

添加多个分区
正确写法: alter table testljb add partition(age=1,sex='male');
正确写法: alter table testljb add partition(age=3) partition(age=4);
错误写法: alter table testljb add partition(age=5,age=6);
	理解: 对相同分区字段进行添加时,只随机添加一个分区;
		 想要对相同字段进行添加时, 只能写两次partition
		 对不同分区字段可以在同一个partition语法中添加

删除分区: 对于外部表的drop partition不会删除hdfs上的文件
alter table t1 drop partition(pt_d = ‘000000’);

修复分区: 
对内部表的修改会将修改直接同步给元数据，而对外部表的表结构和分区进行修改，则需要修复
msck repair table table_name;

分桶表（bucket）：

知乎大佬资料, 就两字概括, 清晰! https://zhuanlan.zhihu.com/p/65442409

桶的概念就是MapReduce的分区的概念，两者完全相同。物理上每个桶就是目录里的一个文件，一个作业产生的桶（输出文件）数量和reduce任务个数相同

  • 对于每一个表（table）或者分区，Hive 可以进一步组织成桶, 桶是更加细粒度的划分 ;
  • Hive 采用对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中.
  • 把表（或者分区）组织成桶（Bucket）有两个理由：
  • 获得更高的查询处理效率 ;
  • 使取样（Sampling）更高效
查询表的详细信息
desc formatted table_name;

创建分桶表有三种方式:
	直接建表
	CREATE TABLE LIKE 
	CREATE TABLE AS SELECT ，单值分区表不能用 CREATE TABLE AS SELECT 建表

直接建表语法:
	  CREATE [EXTERNAL] TABLE <table_name>
	  (<col_name> <data_type> [, <col_name> <data_type> ...])]
	  [PARTITIONED BY ...] 
	  CLUSTERED BY (<col_name>) 
	      [SORTED BY (<col_name> [ASC|DESC] [, <col_name> [ASC|DESC]...])] 
	      INTO <num_buckets> BUCKETS          
	  [ROW FORMAT <row_format>] 
	  [STORED AS TEXTFILE|ORC|CSVFILE]
	  [LOCATION '<file_path>']    
	  [TBLPROPERTIES ('<property_name>'='<property_value>', ...)];
解释:
	  CLUSTERED BY (<col_name>)：				以哪一列进行分桶
	  SORTED BY (<col_name> [ASC|DESC]：		对分桶内的数据进行排序
	  INTO <num_buckets> BUCKETS：				分成几个桶    

导入数据:
	  因为分桶表在创建的时候只会定义Scheme，且写入数据的时候不会自动进行分桶、排序，
	  所以需要人工先进行分桶、排序后再写入数据。确保目标表中的数据和它定义的分布一致。
	
目前有两种方式往分桶表中插入数据：
方法一：打开enforce bucketing开关
	  SET hive.enforce.bucketing=true;
	  INSERT (INTO|OVERWRITE) TABLE <bucketed_table> SELECT <select_statement>
	  [SORT BY <sort_key> [ASC|DESC], [<sort_key> [ASC|DESC], ...]];

方法二：将reducer个数设置为目标表的桶数，并在 SELECT 语句中用 DISTRIBUTE BY <bucket_key>对查询结果按目标表的分桶键分进reducer中。
	  SET mapred.reduce.tasks = <num_buckets>; 
	  INSERT (INTO|OVERWRITE) TABLE <bucketed_table>
	  SELECT <select_statement>
	  DISTRIBUTE BY <bucket_key>, [<bucket_key>, ...] 
	  [SORT BY <sort_key> [ASC|DESC], [<sort_key> [ASC|DESC], ...]]; 

如果分桶表创建时定义了排序键，那么数据不仅要分桶，还要排序
如果分桶键和排序键不同，且按降序排列，使用Distribute by … Sort by分桶排序
如果分桶键和排序键相同，且按升序排列（默认），使用 Cluster by 分桶排序，即如下：
	  SET mapred.reduce.tasks = <num_buckets>;
	  INSERT (INTO|OVERWRITE) TABLE <bucketed_table>
	  SELECT <select_statement>
	  CLUSTER BY <bucket_sort_key>, [<bucket_sort_key>, ...];
至此导入数据完成;
 
分桶表使抽样更加高效
	 抽样语法 ：tablesample（bucket x out of y）
	 y必须是table总共bucket数的倍数或者因子。Hive根据y的大小，决定抽样的比例。
	 例如：table总共分了64份，
	 当y=32时，抽取2(也就是64/32)个bucket的数据，
	 当y=128时，抽取1/2(也就是64/128)个bucket的数据。
	 x表示从哪个bucket开始抽取。
	 例如：table总共bucket数为32，
	 tablesample(bucket 3 out of 16)表示总共抽取2(也就是32/16)个bucket的数据，
	 分别为第三个bucket和第19(3+16)个bucket的数据。