Hive——基本DDL语句

Hive——基本DDL语句

DDL:Data Definition Language(数据定义语言，与关系型数据库相似)

官方手册：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL

HiveQL DDL包括以下内容：

HiveQL DDL statements are documented here, including:

    ●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

PARTITION statements are usually options of TABLE statements, except for SHOW PARTITIONS.

1、创建库

(Hive的数据都是存储在HDFS上的，默认有一个根目录，默认值为/user/hive/warehouse.也可以在hive-site.xml配置中由参数hive.metastore.warehouse.dir指定)

CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
  [COMMENT database_comment]
  [LOCATION hdfs_path]
  [WITH DBPROPERTIES (property_name=property_value, ...)];

小例子：create database hive1;

hive> create database hive1;
OK
Time taken: 1.503 seconds

此时在/user/hive/warehouse中就会多了hive1数据库,数据库的存储路径：${hive.metastore.warehouse.dir}/databasename.db

drwxr-xr-x   - hadoop supergroup          0 2018-09-05 05:53 /user/hive/warehouse/hive1.db

(默认数据库是没有显示成warehouse.db的，它就是在/user/hive/warehouse中)

查看数据库：show databases;

hive> show databases;
OK
default
hive1
Time taken: 0.522 seconds, Fetched: 2 row(s)

2、删除库

DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];

默认情况下，Hive不允许删除一个里面有表存在的数据库，如果想删除数据库，要么先将数据库中的表全部删除，要么可以使用CASCADE关键字，使用该关键字后，Hive会自己将数据库下的表全部删除。RESTRICT关键字就是默认情况，即如果有表存在，则不允许删除数据库。

3、修改库

ALTER (DATABASE|SCHEMA) database_name SET DBPROPERTIES (property_name=property_value, ...);  
  
ALTER (DATABASE|SCHEMA) database_name SET OWNER [USER|ROLE] user_or_role;   
   
ALTER (DATABASE|SCHEMA) database_name SET LOCATION hdfs_path;

4、使用库

USE database_name;
USE DEFAULT;

5、查看库

show databases;//查看库
desc database [extended] dbname;//显示数据库的详细属性信息
select current_database(); //查看正在使用哪个库
show create database t3; //查看创建库的详细语句

6、创建表(hive中的表对应的就是hdfs中的一个文件夹)

详细语法看太多，看官网：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL

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, ...)
　　　　[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
　　[ROW FORMAT row_format]
　　[STORED AS file_format]
　　[LOCATION hdfs_path]

•CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXIST 选项来忽略这个异常
•EXTERNAL 关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION）
•LIKE 允许用户复制现有的表结构，但是不复制数据
•COMMENT可以为表与字段增加描述
•PARTITIONED BY 指定分区
•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 或者使用自带的 SerDe。如果没有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED，将会使用自带的 SerDe。在建表的时候，
用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的 SerDe，Hive 通过 SerDe 确定表的具体的列的数据。 
•STORED AS 
　　SEQUENCEFILE //序列化文件
　　| TEXTFILE //普通的文本文件格式
　　| RCFILE　　//行列存储相结合的文件
　　| INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname //自定义文件格式
　　如果文件数据是纯文本，可以使用 STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCE 。
•LOCATION指定表在HDFS的存储路径

例子：创建book表（字段分隔符使用' '）

hive> create table book(
  >   name string,
  >   price double,
  >   publisher string
  >   )row format delimited fields terminated by '	';
OK
Time taken: 0.558 seconds

hive> load data local inpath '/home/hadoop/data/book.txt' into table book;
Loading data to table default.book
Table default.book stats: [numFiles=1, totalSize=106]
OK
Time taken: 1.189 seconds

hive> select * from book;
OK
Harry Potter    115.7   JK Rowling
Lord of the Rings       123.9   JRR Tolkein
Great Expectations      44.9    Charles Dickens
Time taken: 0.41 seconds, Fetched: 3 row(s)

跟传统关系型数据库一样，也可以采用as select 创建表（底层会跑mr作业的）：

create table book1 as select * from book;

使用like table 只会创建表结构：

create table book2 like book;

7、查看表

show tables;//查看当前使用的数据库中有哪些表
show tables in test;//查看非当前使用的数据库中有哪些表
show tables like 'student_c*';//查看数据库中以xxx开头的表
desc student;//查看表的信息
desc extended student;//查看表的详细信息（格式不友好）
desc formatted student;//查看表的详细信息（格式友好）
show partitions student_ptn;//查看分区信息
show create table student_ptn;//查看表的详细建表语句

8、删除表

DROP TABLE [IF EXISTS] table_name [PURGE];

9、清空表

TRUNCATE TABLE table_name [PARTITION partition_spec];

10、修改表

alter table student rename to new_student;//修改表
alter table new_student add columns (score int);// 增加一个字段
alter table new_student change name new_name string;//修改一个字段的定义
alter table new_student replace columns (id int, name string, address string);//替换所有字段
alter table student_ptn add partition(city="chongqing");//添加分区
alter table student_ptn add partition(city="chongqing2") partition(city="chongqing3") 
    partition(city="chongqing4");//添加多个分区
alter table student_ptn drop partition (city='beijing');//删除分区
alter table student_ptn partition (city='beijing') set location '/student_ptn_beijing';//修改分区存储目录