hive 建表语句 hive建表语句案例

语句示范：

create [EXTERNAL] table vv_stat_fact
(
userid  string,
stat_date string,
tryvv int,
sucvv int,
ptime float
)
 PARTITIONED BY ( 非必选；创建分区表
  dt string)
clustered by (userid) into 3000 buckets // 非必选；分桶子
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'  // 必选;指定列之间的分隔符 
STORED AS rcfile   // 非必选；指定文件的读取格式，默认textfile格式
location '/testdata/'; //非必选；指定文件在hdfs上的存储路径，如果已经有文件，会自动加载 ，默认在hive的warehouse下

1.建表1：全部使用默认配置。

CREATE TABLE `emp2`(
      `id` string, 
      `name` string, 
      `job` string, 
      `mgr` string, 
      `hiredate` date, 
      `sal` double, 
      `comm` double, 
      `deptid` string)
     ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ;

如果不指定存储路径，默认在hdfs上/user/hive/warehouse/shujia.db/emp2 hive是读时模式：当查询数据的时候才会校验数据格式，加载的时候忽略。 需要在建表的时候指定分隔符。

2.建表2：指定文件在hdfs上的存储路径

CREATE TABLE `emp`(
      `id` string, 
      `name` string, 
      `job` string, 
      `mgr` string, 
      `hiredate` date, 
      `sal` double, 
      `comm` double, 
      `deptid` string)
     ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
     LOCATION '/hivedata/';//指定文件在hdfs上的存储路径

3.建表3：指定存储文件格式

CREATE TABLE `emp_rc`(
      `id` string, 
      `name` string, 
      `job` string, 
      `mgr` string, 
      `hiredate` date, 
      `sal` double, 
      `comm` double, 
      `deptid` string)
     ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
     STORED AS rcfile;//指定存储文件格式

注意：rcfile存储格式的表，不能直接加载数据。只能通过其他表加载数据。

4.建表4：从其他表中加载数据 create table emp_r as 查询语句 create table emp_r as select job,avg(sal) as s from emp_rc group by job order by s desc; insert into table emp_i 查询语句(前提这emp_i这个表是建过了) insert into table emp_i select job,avg(sal) as s from emp2 group by job order by s desc; 复制表结构,但是不加载数据：like create table emp_l like emp2;

5.外部表：external

CREATE EXTERNAL TABLE `emp_ex`(
      `id` string, 
      `name` string, 
      `job` string, 
      `mgr` string, 
      `hiredate` date, 
      `sal` double, 
      `comm` double, 
      `deptid` string)
     ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

外部表和内部表的区别： 当删除外部表的时候，表会被删除，但是hdfs上的数据不会被删除。 普通表会把表和数据都删除。 两者都会将mysql中的元信息都删除。

6.分区表：必须在建表的时候指定是分区表，如果建表不是分区表，不可以跟改 分区表的好处：避免全表扫描，提高查询效率。可以在where后面指定分区：分区裁剪 场景：事实表用分区表。按天或者按地区进行分区。一般不超过三级。 多级分区，数据必须存放到最后一级目录中。 在向分区表中插入数据的是时候：需要指定具体的分区名称。如果没有指定会报错，分区如果不存在，会自动创建。如果已经存在，直接使用。 insert into emp_b_p partition(dt="2020-10-16") select * from emp_0;

CREATE TABLE `emp_d`(
      `id` string, 
      `name` string, 
      `job` string, 
      `mgr` string, 
      `hiredate` date, 
      `sal` double, 
      `comm` double, 
      `deptid` string)partitioned by (`dt` string,`province` string)
     ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ;

alter table emp_d add partition(dt="2020-10-15",province="anhui");//这个是指定分区的名字，在hdfs上就是目录的名字

注意：如果通过命令在hdfs上复制了一个表的目录和数据，执行查询语句时是没有数据的，因为没有使用alter table emp_d add partition(dt="时间",province="地区名")，此时的数据和表都是存在hdfs中，元信息不是存储在mysql中，如果执行了，元信息就存在mysql中，此时在执行查询语句是可以查到数据的。

7.分桶表： 对文件进一步划分。 作用：提高查询效率，尤其是join查询效率。 一般分桶，分多少？一般可以跟分桶字段预估分区数来判断。

CREATE TABLE `emp_b_p`(
`id` string, 
`name` string, 
`job` string, 
`mgr` string, 
`hiredate` date, 
`sal` double, 
`comm` double, 
`deptid` string)partitioned by (`dt` string)
clustered by (`job`) into 10 buckets
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

执行set hive.enforce.bucketing = true;启动 insert into emp_b_p partition(dt="2020-10-16") select * from emp_0;

8.hive加载数据： load data [local] inpath '对应的绝对路径' into table 表名 [partition(分区名称)] 例子：将本地文件加载到hive中 load data local inpath '/usr/local/soft/data/empldata.csv' into table eme_p partition(dt="2020-10-16"); 例子：将hdfs路径下的数据加载到hive中 load data '/hivedata/emp_0/empldata.csv' into table emp_p partition(dt="2020-10-16"); 注意：从hdfs中加载数据的时候，是将数据移动到hive对应的表目录。是移动不是复制。