Hive入门基本：建表、增删查改

最基本的建表语句：

create table student(
id string comment '学号',
name string comment '姓名',
sex string comment '性别',
age string comment '年龄'
) comment '学生表';

show create table student;

CREATE TABLE `student`(
  `id` string COMMENT '学号', 
  `name` string COMMENT '姓名', 
  `sex` string COMMENT '性别', 
  `age` string COMMENT '年龄')
COMMENT '学生表'
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  'hdfs://mycluster/user/hive/warehouse/gld.db/student'
TBLPROPERTIES (
  'COLUMN_STATS_ACCURATE'='{\"BASIC_STATS\":\"true\"}', 
  'numFiles'='0', 
  'numRows'='0', 
  'rawDataSize'='0', 
  'totalSize'='0', 
  'transient_lastDdlTime'='1537867370')

插入样本数据：

insert into student values('1','孙悟空','男','100');
insert into student values('2','明世隐','男','101');
insert into student values('3','高渐离','男','102');
insert into student values('4','孙尚香','女','103');
insert into student values('5','安琪拉','女','104');

$ hdfs dfs -ls /user/hive/warehouse/gld.db/student
Found 5 items
-rwxrwxrwx   3 gld supergroup         20 2018-09-25 18:15 /user/hive/warehouse/gld.db/student/000000_0
-rwxrwxrwx   3 gld supergroup         20 2018-09-25 18:18 /user/hive/warehouse/gld.db/student/000000_0_copy_1
-rwxrwxrwx   3 gld supergroup         20 2018-09-25 18:18 /user/hive/warehouse/gld.db/student/000000_0_copy_2
-rwxrwxrwx   3 gld supergroup         20 2018-09-25 18:19 /user/hive/warehouse/gld.db/student/000000_0_copy_3
-rwxrwxrwx   3 gld supergroup         20 2018-09-25 18:19 /user/hive/warehouse/gld.db/student/000000_0_copy_4

$ hdfs dfs -cat /user/hive/warehouse/gld.db/student/000000_0
1孙悟空男100

$ hdfs dfs -cat /user/hive/warehouse/gld.db/student/000000_0_copy_1
2明世隐男101

导出为CSV格式（会有Tab分隔）：

$ hive -e 'use gld; select * from student' > student.csv

student.id	student.name	student.sex	student.age
1	孙悟空	男	100
3	高渐离	男	102
4	孙尚香	女	103
5	安琪拉	女	104
2	明世隐	男	101

create table student(
id string comment '学号',
name string comment '姓名',
sex string comment '性别',
age string comment '年龄'
) comment '学生表'
row format delimited
fields terminated by '\t';

create table student(
id string comment '学号',
name string comment '姓名',
sex string comment '性别',
age string comment '年龄'
) comment '学生表'
row format delimited
fields terminated by ','
TBLPROPERTIES('serialization.null.format'='');

CREATE TABLE…AS…和CREATE TABLE…LIKE…

CREATE TABLE student_bak AS SELECT * FROM student; -- 完整复制表结构和数据。

CREATE TABLE student2 LIKE student; -- 仅复制表结构，新表数据为空。

加载数据

-- 普通表
load data local inpath '/home/gld/student.csv' overwrite into table student;
 -- 分区表
load data local inpath '/home/gld/student.csv' overwrite into table student partition (inc_day='20190101');

内部表与外部表

外部表使用drop删除表时会删除表的元数据，但存储在HDFS目录下的数据不会删除。
外部表使用rename重命名时HDFS目录下的名字不会改变。

外部表恢复

注意，外部表的恢复需要建表语句的，所以注意要保存好建表语句呀！

（1） 重新运行create建表语句：

CREATE EXTERNAL TABLE student(
id STRING,
name STRING,
sex STRING,
age STRING
)
PARTITIONED BY (pt string)
STORED AS PARQUET;

（2）修复：
MSCK REPAIR TABLE student

内部表转换为外部表
ALTER TABLE student SET TBLPROPERTIES ('EXTERNAL'='TRUE');

create table student stored as parquet as select * from another_table;
这种方式不允许建立外部表，原因暂时不明，占坑。

以HBase作为存储方式建表

建立外部表：这个针对HBase中已有的表

HBase：

hbase> create 'gld:student', 'cf1'

Hive：

SET hbase.zookeeper.quorum=zkNode1,zkNode2,zkNode3;

CREATE DATABASE gld;
USE gld;

CREATE EXTERNAL TABLE student(
id STRING,
name STRING,
sex STRING,
age STRING
) 
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key, cf1:name, cf1:sex, cf1:age")
TBLPROPERTIES("hbase.table.name" = "gld:student");

hbase.zookeeper.quorum：设定HBase中ZooKeeper的地址，格式：ip:port,ip:port,ip:port，端口默认2181，可不填。
hbase.columns.mapping：映射HBase中对应的列，格式：列族名:列名，:key表示HBase中的rowkey。
hbase.table.name：映射HBase中对应的表名，格式：命名空间:表名。

这样之后，就可以在Hive中通过SQL语句操作HBase中的表了：insert，delete，update，select。

注意点：

1. 当HBase已有这个表时，只能通过建外部表进行映射。不能建内部表，否则报以下错误：
   FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:MetaException(message:Table gld:student already exists within HBase; use CREATE EXTERNAL TABLE instead to register it in Hive.)
2. 因为在Hive中这个表是外部表，所以drop命令会删除Hive中的表，但不会删除HBase中的表。
3. 如果建立外部表时这个表在HBase中不存在，则报以下错误：FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:MetaException(message:HBase table gld:student doesn't exist while the table is declared as xternal table.)

建立内部表：这个针对HBase中并没有这个表

CREATE TABLE student(
id STRING,
name STRING,
sex STRING,
age STRING
) 
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key, cf1:name, cf1:sex, cf1:age")
TBLPROPERTIES("hbase.table.name" = "gld:student");

注意点：

1. drop table student命令会同时删除Hive中的表和HBase中表！慎重！因此通常都是用第一种方式建立外部表。