JobPlus
JobPlus知识库 IT DBA 文章
【Hive】HiveQL：数据定义
个人头像
章学共
2018-06-27 2505人阅读 0人打赏

HiveQL是Hive查询语言，和普遍使用的所有SQL方言一样，它不完全遵守任一种ANSI SQL标准的修订版。Hive不支持行级插入操作、更新操作和删除操作，Hive也不支持事务，Hive增加了在Hadoop背景下的可以提供更高性能的扩展，以及一些个性化的扩展，甚至还增加了一些外部程序。

一 Hive中的数据库操作

Hive中数据库的概念本质上仅仅是表的一个目录或者命名空间，然而，对于具有很多组和用户的大集群来说，这是非常有用的，因为可以避免表命名冲突，通常会使用数据库来将生产表组织成逻辑组。

如果用户没有显式指定数据库，那么将会使用默认数据块default。

1 Create Database

1）语法结构

[sql] 

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

2）示例

[sql] 

  1. --创建数据库  
  2. hive> create database hive;  
  3. OK  
  4. Time taken: 0.193 seconds  
  5. --查看创建的数据库文件  
  6. hive> dfs -ls /user/hive/warehouse/;  
  7. Found 2 items  
  8. drwxrwxrwx   - hadoop supergroup          0 2018-06-20 17:23 /user/hive/warehouse/hive.db  

Hive会为每个数据库创建一个目录，数据库中的表将会以这个数据库目录的子目录的形式存储，有一个例外是default数据库中的表，因为这个数据库本身没有自己的目录。

数据库所在的目录位于配置文件hive-site.xml属性hive.metastore.warehouse.dir所指定的顶层目录之后，默认配置为/user/hive/warehouse。

2 Show Database

1）查看所有数据库

[sql]

  1. hive> show databases;  
  2. OK  
  3. database_name  
  4. default  
  5. hive  
  6. test  
  7. test1  
  8. Time taken: 0.027 seconds, Fetched: 4 row(s)  

2）模糊匹配，显示符合条件的数据库

[sql] 

  1. hive> show databases like 'te*';  
  2. OK  
  3. database_name  
  4. test  
  5. test1  
  6. Time taken: 0.027 seconds, Fetched: 2 row(s)  

3 Drop Database

1）语法结构

[sql] 

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

2）示例

[sql] 

  1. --当数据库中存在表时，删除会报错，需要使用Cascade  
  2. hive> drop database hive;  
  3. FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. InvalidOperationException(message:Database hive is not empty. One or more tables exist.)  
  4. hive> drop database hive cascade;  
  5. OK  
  6. Time taken: 2.279 seconds  
  7. --如果数据库为空，直接删除  
  8. hive> drop database test1;  
  9. OK  
  10. Time taken: 0.186 seconds  

4 ALTER Database

1）语法结构

[sql] 

  1. ALTER (DATABASE|SCHEMA) database_name SET DBPROPERTIES (property_name=property_value, ...);   -- (Note: SCHEMA added in Hive 0.14.0)  
  2.    
  3. ALTER (DATABASE|SCHEMA) database_name SET OWNER [USER|ROLE] user_or_role;   -- (Note: Hive 0.13.0 and later; SCHEMA added in Hive 0.14.0)  
  4.     
  5. ALTER (DATABASE|SCHEMA) database_name SET LOCATION hdfs_path; -- (Note: Hive 2.2.1, 2.4.0 and later)  

2 示例

[sql]

  1. hive> alter database hive set DBPROPERTIES('Created by '='Alen','Create Date'='2018-06-20');  
  2. OK  
  3. Time taken: 0.188 seconds  

5 Use Database

1）语法结构

[sql] 

  1. USE database_name;  
  2. USE DEFAULT;  

2）示例

[sql] 

  1. hive> use hive;  
  2. OK  
  3. Time taken: 0.048 seconds  
  4. hive> select current_database();  
  5. OK  
  6. _c0  
  7. hive  
  8. Time taken: 0.165 seconds, Fetched: 1 row(s)  

注：可通过 set hive.cli.print.current.db=true;设置提示。

二 Hive中的表操作

1 创建表

1）语法结构

[sql] 

  1. CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name    -- (Note: TEMPORARY available in Hive 0.14.0 and later)  
  2.   [(col_name data_type [COMMENT col_comment], ... [constraint_specification])]  
  3.   [COMMENT table_comment]  
  4.   [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]  
  5.   [CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]  
  6.   [SKEWED BY (col_name, col_name, ...)                  -- (Note: Available in Hive 0.10.0 and later)]  
  7.      ON ((col_value, col_value, ...), (col_value, col_value, ...), ...)  
  8.      [STORED AS DIRECTORIES]  
  9.   [  
  10.    [ROW FORMAT row_format]   
  11.    [STORED AS file_format]  
  12.      | STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)]  -- (Note: Available in Hive 0.6.0 and later)  
  13.   ]  
  14.   [LOCATION hdfs_path]  
  15.   [TBLPROPERTIES (property_name=property_value, ...)]   -- (Note: Available in Hive 0.6.0 and later)  
  16.   [AS select_statement];   -- (Note: Available in Hive 0.5.0 and later; not supported for external tables)  
  17.    
  18. CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name  
  19.   LIKE existing_table_or_view_name  
  20.   [LOCATION hdfs_path];  

2）示例

[sql] 

  1. hive> create table emp(id int,name string);  
  2. OK  
  3. Time taken: 0.238 seconds  

3）查看表结构信息

[sql]

  1. --简化表结构  
  2. hive> desc emp;  
  3. OK  
  4. col_name    data_type   comment  
  5. id                      int                                           
  6. name                    string                                        
  7. Time taken: 0.105 seconds, Fetched: 2 row(s)  
  8. --可添加formatted查看更多信息  
  9. hive> desc formatted emp;  
  10. OK  
  11. col_name    data_type   comment  
  12. # col_name              data_type               comment               
  13.            
  14. id                      int                                           
  15. name                    string                                        
  16.            
  17. # Detailed Table Information           
  18. Database:               hive                       
  19. Owner:                  hadoop                     
  20. CreateTime:             Wed Jun 20 17:56:05 CST 2018       
  21. LastAccessTime:         UNKNOWN                    
  22. Retention:              0                          
  23. Location:               hdfs://localhost:9000/user/hive/warehouse/hive.db/emp      
  24. Table Type:             MANAGED_TABLE              
  25. Table Parameters:          
  26.     COLUMN_STATS_ACCURATE   {\"BASIC_STATS\":\"true\"}  
  27.     numFiles                0                     
  28.     numRows                 0                     
  29.     rawDataSize             0                     
  30.     totalSize               0                     
  31.     transient_lastDdlTime   1529488565            
  32.            
  33. # Storage Information          
  34. SerDe Library:          org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe     
  35. InputFormat:            org.apache.hadoop.mapred.TextInputFormat       
  36. OutputFormat:           org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat     
  37. Compressed:             No                         
  38. Num Buckets:            -1                         
  39. Bucket Columns:         []                         
  40. Sort Columns:           []                         
  41. Storage Desc Params:           
  42.     serialization.format    1                     
  43. Time taken: 0.136 seconds, Fetched: 31 row(s)  

4）管理表

上面创建的表是管理表，也称为内部表，因为这种表，Hive会（或多或少）控制着数据的生命周期，默认情况下，HIve会将这些表存储在由配置项hive.metastore.warehouse.dir所定义的目录的子目录下。

当删除管理表时，也会删除这个表中的数据。

5）外部表

使用关键字external创建，location子句用于指示Hive数据存放于哪个路径下，因为表是外部的，所以HIve并非完全拥有这份数据，因此，删除表并不会删除数据，只是删除了表的元数据信息。

2 删除表

[sql] 

  1. hive> drop table if exists emp;  
  2. OK  
  3. Time taken: 4.664 seconds  

3 修改表

大多数表属性可以通过alter table语句进行修改，这种操作会修改元数据，但不会修改数本身。

1）表重命名

[sql] 

  1. hive> alter table emp rename to t_emp;  
  2. OK  
  3. Time taken: 0.654 seconds  

2）增加列

[sql]

  1. hive> alter table t_emp add columns (sex string);  
  2. OK  
  3. Time taken: 0.938 seconds  
  4. hive> select *from t_emp;  
  5. OK  
  6. t_emp.id    t_emp.name  t_emp.sex  
  7. 3   peter   NULL  
  8. 1   alen    NULL  
  9. Time taken: 0.682 seconds, Fetched: 2 row(s)  

3）修改列信息

用户可以对某个字段进行重命名、并修改其位置、类型或注释：

[sql] 

  1. hive> alter table t_emp change column sex sex string  
  2.     > comment 'The column type changed by alen'  
  3.     > after id;  
  4. OK  
  5. Time taken: 0.502 seconds  
  6. hive> select *from t_emp;  
  7. OK  
  8. t_emp.id    t_emp.sex   t_emp.name  
  9. 3   peter   NULL  
  10. 1   alen    NULL  
  11. Time taken: 0.638 seconds, Fetched: 2 row(s)  

注：该命令修改的只是元数据信息。

[sql] 

  1. hive> alter table t_emp change column sex sex string after name;  
  2. OK  
  3. Time taken: 0.493 seconds  
  4. hive> select *from t_emp;  
  5. OK  
  6. t_emp.id    t_emp.name  t_emp.sex  
  7. 3   peter   NULL  
  8. 1   alen    NULL  
  9. Time taken: 0.572 seconds, Fetched: 2 row(s)  

4）删除或替换列

[sql] 

  1. hive> alter table t_emp replace columns(  
  2.     > eid int comment 'id of emp',  
  3.     > ename string comment 'name ');  
  4. OK  
  5. Time taken: 0.304 seconds  
  6. hive> select *from t_emp;  
  7. OK  
  8. t_emp.eid   t_emp.ename  
  9. 3   peter  
  10. 1   alen  
  11. Time taken: 0.845 seconds, Fetched: 2 row(s)  
  12. hive> alter table t_emp replace columns(  
  13.     > id int comment 'id of emp',  
  14.     > name string comment 'name of emp',  
  15.     > age int);  
  16. OK  
  17. Time taken: 0.325 seconds  
  18. hive> select *from t_emp;  
  19. OK  
  20. t_emp.id    t_emp.name  t_emp.age  
  21. 3   peter   NULL  
  22. 1   alen    NULL  
  23. Time taken: 0.551 seconds, Fetched: 2 row(s)  

5）修改表属性

用户可以增加附加的表属性或修改已存在的属性，但无法删除属性。

[sql] 

  1. hive> alter table emp set tblproperties(  
  2.     > 'notes'='Created by alen',  
  3.     > 'Cdate'='2018-06-21');  
  4. OK  
  5. Time taken: 0.344 seconds  



如果觉得我的文章对您有用，请随意打赏。您的支持将鼓励我继续创作！

¥ 打赏支持
368人赞 举报
分享到
用户评价(0)

暂无评价，你也可以发布评价哦:)

11 人收藏了这篇文章
个人头像 个人头像 个人头像 个人头像 个人头像 个人头像 ...

相关文章

腾讯云数据库性能卓越稳定可靠，为您解决数据库运维难题
广告

关于我们

公司介绍

人才招聘

联系方式

法律声明

服务条款

权利声明

隐私声明

帮助中心

使用攻略

建议反馈

联系客服

网站地图

二维码
JobPlus官方公众号
微博
JobPlus官方微博
Copyright © jobplus.com.cn All rights reserved
苏州聘十信息技术有限公司 版权所有苏ICP备16046992号
扫码APP

扫描使用APP

扫码使用

扫描使用小程序