hive中的几种表

1.hive中表的类型

• 内部表(受控表)：当删除内部表的时候，hdfs上的数据以及元数据都会被删除。
• 外部表：当删除外部表的时候，HDFS上的数据不会被删除，但是元数据会被删除。
• 临时表(测试环境)：在当前会话期间内存在，会话结束自动消失，生命周期随之session。
• 分区表：将一批数据分成多个目录来存储。
• 分桶表：

2.内部表
  ①创建表的方式有三种：

• 直接创建

CREATE TABLE gfstbl( //table前没有修饰符，说明创建的是一个内部表
  id INT,
  name STRING,
  age INT,
  gfs ARRAY<STRING>,
  address MAP<STRING,STRING>,
  info STRUCT<country:String,province:String,shi:String>
)
ROW FORMAT DELIMITED 
    FIELDS TERMINATED BY ' '  //字段间的分隔符
    COLLECTION ITEMS TERMINATED BY ','  //集合元素之间的分隔符
    MAP KEYS TERMINATED BY ':'   //map中key和value之间的分隔符
    LINES TERMINATED BY '\n';   //行与行之间的分隔符
LOCATION "/test" //可以设置源数据的位置，若不设置默认就在Hive的工作目录区

• 创建一张表和已存在的一张表的结构相同

create table gfstbl1 like gfstbl   //只会创建表结构

• 创建一张表和已存在的一张表的结构相同，并且还可以带有数据

create table gfstbl2 AS SELECT id,name,gfs,address from gfstbl;  //会创建相应的表
结构，并且插入数据。查哪个字段就会插入哪个字段的数据。

  ②查看表描述信息

DESCRIBE [EXTENDED|FORMATTED] table_name
EXTENDED极简的方式显示(默认就是极简的方式)
FORMATTED格式化方式来显示

  ③插入数据的方式

    1、insert 新数据
    2、load
    3、查询其他表数据 insert 到新表中
	模板：
	   insert into rest select count(*) from table;
	习惯写法 from提前  减少SQL代码的冗余
	   from day_hour_table
	   insert into rest 
	   select count(*) ;

  ④查询表中字段的值

• 字段类型为数组，采用 字段名[下标]来获取。
• 字段类型为map，采用 字段名[“map的key”]来获取。
• 字段类型为STRUCT，采用 字段名.STRUCT的属性来获取。

3.临时表
  ①创建临时表(临时表不支持分区)

create TEMPORARY table ttabc(id Int,name String)  //TEMPORARY代表这是一个临时表。

  ②shell
    凡是shell都遵循repl机制。
      r ：read 读
      e：evaluate 计算
      p：print 打印
      l ：loop 循环
  ③使用yarn命令干掉某一个application

yarn application -kill job_1540028621068_0006  // id是在job启动时就给出了。

  ④临时表也是一个内部表，操作和内部表一样。
4.外部表
  ①创建外部表

create external table wc_external   //external代表这是一个外部表
   (word1 STRING, 
   word2 STRING) 
   ROW FORMAT DELIMITED 
   FIELDS TERMINATED BY ' ' 
   location '/test/external'; location可加可不加，不加location默认是在hive的工作目录区

  ②往表中添加数据

 1、将按照wc_external表的规则 创建了一个新的文件
	hello bj
	hello sh
	将这个问题拷贝到这个表的工作目录区中，然后查询这个表数据量增多
2、load data inpath "/test/external/t" into table wc_external;
	将工作目录区中文件再次添加到这个表中，发现数据量没有增量
3、将文件上传到了hdfs的/根目录
	load data inpath "/t" into table wc_external;
	数据量增多了，但是/t文件被剪贴了

5.分区表
  ①为什么创建分区表？
    为了防止暴力扫描全表。可以提高查询效率。
  ②静态分区表
    创建单分区表

create table day_table (id int, content string) 
partitioned by (dt string)  //指定分区
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' ;

    操作单分区表

     insert单条插入的方式往分区表中插入数据：
	insert into day_table partition (dt = "9-26") values(1,"anb");
     load批量插入的方式往分区表中插入数据：
	load data local inpath "/root/ceshi" into table day_table partition (dt="9-27");
     删除Hive分区表中的分区
	ALTER TABLE day_table DROP PARTITION (dt="9-27");

    创建多分区表

create table day_hour_table (id int, content string) 
partitioned by (dt int,hour int)   //指定多个分区
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' ;

    操作多分区表

加载数据：
   insert单条插入的方式往分区表中插入数据：
     insert into day_hour_table partition(dt=9,hour=1) values(1,"a2 bc");
     insert into day_hour_table partition(dt=9,hour=2) values(3,"a2 bc");
     insert into day_hour_table partition(dt=8,hour=1) values(3,"a2 bc");
     insert into day_hour_table partition(dt=8,hour=2) values(3,"a2 bc");
   load批量插入的方式往分区表中插入数据：
     load data local inpath "/root/ceshi" into table day_table partition (dt=10,hour=10);
   删除Hive分区表中的分区
     ALTER TABLE day_table DROP PARTITION (dt=10,hour=10);

    创建/添加分区

创建一个空分区：ALTER TABLE day_hour_table ADD PARTITION (dt=10000, hour=2000);
然后将数据上传到空分区对应的目录下，分区表中就会显示数据
创建一个空分区并且将空分区指向数据位置：
 ALTER TABLE day_hour_table ADD PARTITION (dt=10000, hour=2000) location "/test"

    往分区中添加数据的五种方式

   （1）insert 指定分区
   （2）load data 指定分区
   （3）查询已有表的数据，insert到新表中
	 from day_hour_table insert into table newt partition(dt=01,hour=9898) select id,content
   （4）alter table add partition创建空分区，然后使用HDFS命令往空分区目录中上传数据
   （5）创建分区，并且指定分区数据的位置

  ③动态分区表
    动态分区表的概念

静态分区表，一个文件数据只能导入到某一个分区中，并且分区是用户指定的，这种方式
不够灵活，业务场景比较局限。动态分区可以根据数据本身的特征自动来划分分区，比如
我们可以指定按照数据中的年龄、性别来动态分区。使用动态分区表，要修改两个配置信息
set hive.exec.dynamic.partition=true; //开启动态分区
set hive.exec.dynamic.partition.mode=nostrict; //使用非严格模式。严格模式是指必须要有一个静态分区

    创建动态分区表

创建动态分区表的语句与创建静态分区表的语句是一模一样的，只是在指定分区的时候用表中的字段来指定。
partitioned by (sex string,age INT)

    往动态分区表中加载数据

往动态分区表中加载数据不能使用 load data 。load data只是将数据上传到HDFS指定目录中。
我们之前使用load data往分区表导入数据的时候，都是要指定partition分区的，这样他才会知
道将数据上传到HDFS的哪一个分区。
但是如果我们还是采用load data指定分区的话，那就不是动态分区表，还依然是静态分区表
所以得采用 from insert的方式往动态分区表中插入数据。

  ④查看分区数

show partitions table_name  //动态，静态都可以查看

6.分桶表
  ①分桶表的原理

分桶表是对列值取哈希值的方式，将不同数据放到不同文件中存储,一个文件对应一个桶
由列的哈希值除以桶的个数来决定每条数据划分在哪个桶中
对于hive中每一个表、分区都可以进一步进行分桶。
好处：提高了join的效率；提高了随机抽样的效率。
使用分桶表要修改set hive.enforce.bucketing=true;

  ②创建分桶表

CREATE TABLE psnbucket( id INT, name STRING, age INT) 
CLUSTERED BY (age) INTO 4 BUCKETS  // 指定按照age的哈希值与4取模来分桶
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

  ③往分桶表中插入数据

不能使用load data，只能使用insert 

  ④抽样

select * from psnbucket tablesample(bucket X out of Y on age);
X代表从哪个桶开始抽取数据
Y必须为该表总桶数的倍数或因子，表示步长，