HIve：DDL&DML

官网： https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL

基本数据类型

Hive数据类型（红色为常用）	长度	说明
TINYINT	1byte有符号整数	用INT替代
SMALINT	2byte有符号整数	用INT替代
INT	4byte有符号整数
BIGINT	8byte有符号整数
BOOLEAN	布尔类型，true或者false	用0/1替代
FLOAT	单精度浮点数
DOUBLE	双精度浮点数
DECIMAL	高精度浮点数	用于算钱
STRING	字符类型。可以使用单引号或者双引号。
TIMESTAMP	时间类型	用字符类型替代

DDL语法

• CREATE DATABASE/SCHEMA, TABLE, VIEW, FUNCTION, INDEX
• DROP DATABASE/SCHEMA, TABLE, VIEW, INDEX
• TRUNCATE TABLE
• ALTER DATABASE/SCHEMA, TABLE, VIEW
• MSCK REPAIR TABLE (or ALTER TABLE RECOVER PARTITIONS)
• SHOW DATABASES/SCHEMAS, TABLES, TBLPROPERTIES, VIEWS, PARTITIONS, FUNCTIONS, INDEX[ES], COLUMNS, CREATE TABLE
• DESCRIBE DATABASE/SCHEMA, table_name, view_name

DATABASE操作

    创建数据库

数据库在HDFS上的默认存储路径是/user/hive/warehouse/*.db（hive.metastore.warehouse.dir）:

CREATE DATABASE [IF NOT EXISTS] database_name
[COMMENT database_comment] --描述
[LOCATION hdfs_path] --指定HDFS上存放的路径
[WITH DBPROPERTIES (property_name=property_value, ...)]--键值对属性值;

注：避免要创建的数据库已经存在错误，应习惯加上增加 IF NOT EXISTS 判断。

    查看数据库

show databases;
desc database db_hive; --查看数据库的信息
desc database extended db_hive;--显示数据看的详细信息 (extended关键字)
use db_hive; --切换数据库

    删除数据库

DROP DATABASE [IF EXISTS] database_name [RESTRICT|CASCADE];
-------------------------------------------------------------

drop database if exists db_hive2; --删除空数据库（最好加上IF EXISTS判断其是否存在）

drop database db_hive cascade;--如果数据库不为空，可以采用cascade命令，强制删除。（不建议）

    修改数据库

用户可以使用ALTER DATABASE命令为某个数据库的DBPROPERTIES设置键-值对属性值，来描述这个数据库的属性信息。

数据库的其他元数据信息都是不可更改的，包括数据库名和数据库所在的目录位置。修改当前正在使用的数据库，要先退出使用。

alter database db_hive set dbproperties('createtime'='20180830');

 

Table操作

    创建表

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name 
[(col_name data_type [COMMENT col_comment], ...)] 
[COMMENT table_comment] --描述
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] --分区
[CLUSTERED BY (col_name, col_name, ...) --分桶
[ROW FORMAT row_format] 
[STORED AS file_format]  --指定文件类型，如SEQUENCEFILE（二进制序列文件）、TEXTFILE（文本）、RCFILE（列式存储格式文件）
[LOCATION hdfs_path] --指定存放路径

ROW FORMAT

DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char]

        [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]

   | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]

用户在建表的时候可以自定义SerDe（Serialize/Deserilize的简称，用于序列化和反序列化）或者使用自带的SerDe。如果没有指定ROW FORMAT ，将会使用自带的SerDe。在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的SerDe，Hive通过SerDe确定表的具体的列的数据。

 

建表语句示例：

/*指定表结构创建内部表*/
create table if not exists emp(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal double,
comm double,
deptno int
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

/*通过查询出来的结果创建*/
create table if not exists emp as select * from emp;

/*通过已存在的表结构创建表*/
create table if not exists emp2 like emp;

/*创建外部表*/
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 '\t'
location '/d6_hive/external';	

    修改表

/*表名修改*/
ALTER TABLE table_name RENAME TO new_table_name;

/*更新列*/
ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]

/*增加或替换列*/
ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...) --ADD是增加一个字段，REPLACE是替换所有的字段

/*查询表结构*/
DESC table_name

    删除表

DROP TABLE table_name

内部表和外部表

    内部表（MANAGED_TABLE）：

    默认创建的表都是内部表。默认情况下会将这些表的数据存储在由配置项hive.metastore.warehouse.dir(例如，/user/hive/warehouse)所定义的目录的子目录下。当我们删除一个内部表时，Hive也会删除这个表中数据。内部表不适合和其他工具共享数据。

    外部表（EXTERNAL_TABLE）：

    删除该表并不会删除掉这份数据，不过描述表的元数据信息会被删除掉。

    对于一些原始日志文件，同时被多个部门同时操作的时候就需要使用外部表。

 

    总结：Hive创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。

外部表与内部表转换： 

/*修改内部表为外部表*/
alter table emp set tblproperties('EXTERNAL'='TRUE');

/*修改外部表为内部表*/
alter table emp set tblproperties('EXTERNAL'='FALSE');

DML语法

官网：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DML#LanguageManualDML-Loadingfilesintotables

导入数据

通过load方式导入数据：

LOAD DATA [LOCAL] INPATH file_path [OVERWRITE] INTO TABLE table_name [PARTITION (partcol1=val1,…)];

LOCAL :表示从本地加载数据到hive表（复制）；否则从HDFS加载数据到hive表（移动）

OVERWRITE:表示覆盖表中已有数据，否则表示追加 

通过查询方式导入数据：

/**INSERT OVERWRITE将覆盖表或分区中的任何现有数据**/
INSERT OVERWRITE TABLE tablename PARTITION (partcol1[=val1], partcol2[=val2] ...) select_statement FROM from_statement;

/**NSERT INTO将附加到表或分区，保持现有数据不变**/
INSERT INTO TABLE tablename PARTITION (partcol1[=val1], partcol2[=val2] ...) select_statement FROM from_statement;

例： 

insert into order_partition partition(event_month='2014-07') values ('1','4456515616'),('1','4456515616');

/**根据查询条件进行插入**/
insert into emp_ex select * from emp_ex; --查询的数据的schema要和插入的表的一致

注： 生产上一般不用 insert ....values....这种方式插入数据，因为这种方式插入会产生大量的小文件。

其他导入数据的方式：

       1、可以在创建表的时候通过as select导入数据 （见前文）

       2、创建表的时候通过location指定加载数据路径

       3、用import

       4、sqoop

       ………………………………

导出数据

可以使用上面insert语法的略微变化将查询结果插入到文件系统目录中：

INSERT OVERWRITE [LOCAL] DIRECTORY directory1
  [ROW FORMAT row_format] [STORED AS file_format]
  SELECT ... FROM ...

例：

INSERT OVERWRITE LOCAL DIRECTORY '/home/hadoop/tmp/d6/emptmp'
row format delimited fields terminated by ','
SELECT empno,ename FROM emp;

其他导入数据的方式：

一、hdfs命令：

hive (default)> dfs -get /user/hive/warehouse/emp/month=201809/000000_0
/home/hadoop/tmp/emp.txt;

二、shell

bin/hive -e 'select * from d6.emp;' >
 /home/hadoop/tmp/emp.txt;

三、export

四、sqoop

……………………………………………………