Hive - shelll编程

灰太狼 2022-03-22 05:56 155阅读 0赞

**目录：**

1.  HIVE - sql
    
    1.  数据基本类型
    2.  Hive sql分类
2.  HIVE - DDL之数据库的创建
3.  HIVE - DDL之建表和表操作
4.  HIVE分区与分桶
5.  HIVE- DDL之修改表
6.  HIVE- DML之数据管理
7.  HIVE- DQL之数据查询
8.  使用navicat作为mysql可视化工具查看数据库和表，也可以在hdfs中查看

[官方文档][Link 1]

## ##

### 一.HIVE - sql ###

**1.Hive数据类型**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lpZ3VhbmdfODIw_size_16_color_FFFFFF_t_70][]

**2.Hive sql分类**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lpZ3VhbmdfODIw_size_16_color_FFFFFF_t_70 1][]

## ##

### 二.HIVE - DDL之数据库的创建 ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lpZ3VhbmdfODIw_size_16_color_FFFFFF_t_70 2][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lpZ3VhbmdfODIw_size_16_color_FFFFFF_t_70 3][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lpZ3VhbmdfODIw_size_16_color_FFFFFF_t_70 4][]

## ##

### 三.HIVE - DDL之建表和表操作 ###

**建表和基本表操作**

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name    -- (Note: TEMPORARY available in Hive 0.14.0 and later)
      [(col_name data_type [COMMENT col_comment], ... [constraint_specification])]
      [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]
      [SKEWED BY (col_name, col_name, ...)                  -- (Note: Available in Hive 0.10.0 and later)]
         ON ((col_value, col_value, ...), (col_value, col_value, ...), ...)
         [STORED AS DIRECTORIES]
      [
       [ROW FORMAT row_format] 
       [STORED AS file_format]
         | STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)]  -- (Note: Available in Hive 0.6.0 and later)
      ]
      [LOCATION hdfs_path]
      [TBLPROPERTIES (property_name=property_value, ...)]   -- (Note: Available in Hive 0.6.0 and later)
      [AS select_statement];   -- (Note: Available in Hive 0.5.0 and later; not supported for external tables)

**说明：**

1.创建最简单的一张表：create table  if not exists 表名（字段 字段类型）；  
2.TEMPORARY:临时表  
3.EXTERNAL:外部表  
4.在hive中表的类型有哪些  
5.如何查看哪些是内部表、哪些是外部表？  
6.创建一张表到指定数据库:两种方式  
7.创建一张表，带有备注信息，同时创建多个字段  
8.创建一张带有集合类型的表  
9.创建一张外部表，指定存储位置（不在默认的位置中）  
一般情况下内部表是在默认的位置下，外部表一般情况下不在默认的位置下。  
create external table employees（name1 string comment "aaa",name2 string comment "bbb"）location "/hive\_data/table03";

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lpZ3VhbmdfODIw_size_16_color_FFFFFF_t_70 5][]

10.如何加载文本进行数据的插入？  
11.在创建表时，如何指定字段的分隔符？  
![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lpZ3VhbmdfODIw_size_16_color_FFFFFF_t_70 6][]

![20190127234020746.png][]

12.关于集合类型的值的加载

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lpZ3VhbmdfODIw_size_16_color_FFFFFF_t_70 7][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lpZ3VhbmdfODIw_size_16_color_FFFFFF_t_70 8][]

操作：

1.创建一个文本 table05\_1.txt  
2.将此文本加载到数据表  
![20190128095215952.png][]  
3.查看表数据  
![20190128095231695.png][]  
![20190128095305800.png][]

13.like、as的使用  
![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lpZ3VhbmdfODIw_size_16_color_FFFFFF_t_70 9][]

14.在cli中可以直接执行linux命令  
![20190128101856790.png][]

例如：  
![20190128100007490.png][]

### 四.分区和分桶 ###

1.  分区
    
    1.  概念  
        hive select 查询中一般会扫描整个表内容，会消耗很多时间做没有必要的工作。  
        而分区表指的是在创建表的时候指定partition的分区空间。
        
            create table tablename(
                  name string
            )
            partitioned by(column type[,column type]
    2.  分区的理解  
        ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lpZ3VhbmdfODIw_size_16_color_FFFFFF_t_70 10][]
    3.  操作  
        步骤1：创建一个最简单的分区表  
        步骤2：创建一个数据文本  
        步骤3：将数据文本加载到分区表中  
        步骤4：查看分区表数据  
        ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lpZ3VhbmdfODIw_size_16_color_FFFFFF_t_70 11][]
    4.  其他操作  
        ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lpZ3VhbmdfODIw_size_16_color_FFFFFF_t_70 12][]  
        说明：  
        ![20190128123612592.png][]
    5.  创建多个分区字段  
        ![20190128123210924.png][]  
        并为其加载数据  
        ![20190128123310953.png][]  
        说明：  
        ![20190128123342870.png][]
    6.  动态分区：[hive动态分区][hive]
2.  分桶
    
    1.  概念  
        ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lpZ3VhbmdfODIw_size_16_color_FFFFFF_t_70 13][]  
        ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lpZ3VhbmdfODIw_size_16_color_FFFFFF_t_70 14][]
    2.  理解  
        ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lpZ3VhbmdfODIw_size_16_color_FFFFFF_t_70 15][]
    3.  操作  
        1.根据表创建分桶  
        ![20190128125641276.png][]  
        2.根据区创建分桶  
        ![20190128125844275.png][]
3.  分区和分桶的区别
    
    1.  分区是对某个字段设置一定的规则
    2.  分桶是对列哈希取值，然后除以桶的个数的方式决定记录放在哪个桶中，分桶是比分区更细粒度的操作。

## ##

### 五.HIVE- DDL之修改表 ###

修改表结构

alter table table\_name \[partition partition\_spsc\] change \[column\] col\_old\_name col\_new\_name column\_type  
\[comment col\_commnet\]  \[first|alter column\_name\] \[cascade|restrict\];

1.  添加列：添加一个字段  
    alter table emloyee add columns(test String);
2.  修改列名  
    alter table employee change old\_name new\_name String;  
    alter table employee change old\_name new\_name String after age;//修改列名，并改变name列的位置，如果此列有数据的情况下，数据不会发生交换。  
    注意：在改变字段名位置的时候，类型需要匹配。  
    这里只是将字段的名称进行了交换，如果表中已有数据不会进行交换。

###    六.HIVE- DML之数据管理 ###

1.  **导入数据**
    
    1.  从本地加载数据到hive表  
        LOAD DATA LOCAL INPATH ‘employee.txt’ OVERWRITE INTO TABLE company.employee patition(sex,age);
    2.  从另一个表加载数据到hive表  
        ![20190828184910307.png][]  
        注意：  
        ![20190828185602763.png][]
    3.  从HDFS中导入数据（有数据还原的功能）  
        ![20190828184936632.png][]
2.  **导出数据**
    
    1.  导出数据到HDFS（有数据备份的功能）  
        ![20190828185120799.png][]
3.  **其他**
    
    1.  将某张的数据清空  
        TRUNCATE TABLE table04;

###    七.HIVE- DQL之数据查询 ###

*  HiveSQL 支持的几种排序各代表什么意思（Sort By/Order By/Cluster By/Distrbute By）  
    [https://blog.csdn.net/lzm1340458776/article/details/43306115][https_blog.csdn.net_lzm1340458776_article_details_43306115]

###    ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lpZ3VhbmdfODIw_size_16_color_FFFFFF_t_70 16][]   说明：   ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lpZ3VhbmdfODIw_size_16_color_FFFFFF_t_70 17][] ###

2.这三者的区别  
![20190128131928155.png][]

1.  **执行效果上：  **  
    count(\*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL    
    count(1)包括了所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL    
    count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是只空字符串或者0，而是表示**null**）的计数，即某个字段值为NULL时，不统计  .
2.  **执行效率上：  **  
    列名为主键，count(列名)会比count(1)快    
    列名不为主键，count(1)会比count(列名)快    
    如果表多个列并且没有主键，则 count（1） 的执行效率优于 count（\*）    
    如果有主键，则 select count（主键）的执行效率是最优的    
    如果表只有一个字段，则 select count（\*）最优

### 八.使用navicat作为mysql可视化工具查看数据库和表，也可以在hdfs中查看 ###

[Navicat Premium安装教程（激活）][Navicat Premium]

**navcat查看 **

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lpZ3VhbmdfODIw_size_16_color_FFFFFF_t_70 18][]  
![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lpZ3VhbmdfODIw_size_16_color_FFFFFF_t_70 19][]

**在hdfs的web界面查看**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lpZ3VhbmdfODIw_size_16_color_FFFFFF_t_70 20][]

[Link 1]: https://cwiki.apache.org/confluence/display/Hive/LanguageManual
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lpZ3VhbmdfODIw_size_16_color_FFFFFF_t_70]: /images/20220322/77903c6a43724e56924ee7c9d41fd0b7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lpZ3VhbmdfODIw_size_16_color_FFFFFF_t_70 1]: /images/20220322/c0c703b9a8ad4969995457d6cc4177e7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lpZ3VhbmdfODIw_size_16_color_FFFFFF_t_70 2]: /images/20220322/e34617e09295480c87a5d142a284848f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lpZ3VhbmdfODIw_size_16_color_FFFFFF_t_70 3]: /images/20220322/a9d19877473c441297c052d5df23df1a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lpZ3VhbmdfODIw_size_16_color_FFFFFF_t_70 4]: /images/20220322/a3cffa66af634b84b3cdb6c343e28e3e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lpZ3VhbmdfODIw_size_16_color_FFFFFF_t_70 5]: /images/20220322/1d2fc53dd2134ce4b8a5d1135764401f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lpZ3VhbmdfODIw_size_16_color_FFFFFF_t_70 6]: /images/20220322/f92e4d913b1e4ed8a7c0511382a66576.png
[20190127234020746.png]: /images/20220322/d51dfcdf01384f1da1cc387ceedbba1e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lpZ3VhbmdfODIw_size_16_color_FFFFFF_t_70 7]: /images/20220322/be71f89b2ffd4184ac795f838aba8db5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lpZ3VhbmdfODIw_size_16_color_FFFFFF_t_70 8]: /images/20220322/ce9544e1c3824aaeaeb00fd145851a6a.png
[20190128095215952.png]: /images/20220322/28874f00205846d7811d6ca3e6eb9572.png
[20190128095231695.png]: /images/20220322/77e059122de040c698444577f1d04c48.png
[20190128095305800.png]: /images/20220322/6b7f9d8429864a329f7e3cb31fd7eaf0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lpZ3VhbmdfODIw_size_16_color_FFFFFF_t_70 9]: /images/20220322/51cf444daa23419583612bc5fd1d2192.png
[20190128101856790.png]: /images/20220322/f48e9d46e96b4112bcaaaf20aedee9f2.png
[20190128100007490.png]: /images/20220322/fb02ac06608c4f23a93ee090362c66c0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lpZ3VhbmdfODIw_size_16_color_FFFFFF_t_70 10]: /images/20220322/09b82074589c42ddacaba24e151dad6f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lpZ3VhbmdfODIw_size_16_color_FFFFFF_t_70 11]: /images/20220322/e51ab31dc0984897a6ad64d0e0547412.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lpZ3VhbmdfODIw_size_16_color_FFFFFF_t_70 12]: /images/20220322/5504e9f58a9c4ee59b407c4b851a979a.png
[20190128123612592.png]: /images/20220322/8e08f7d06231455097e07f0af9d9d71b.png
[20190128123210924.png]: /images/20220322/ad9b5c0ea182443ea672dcc6f585add7.png
[20190128123310953.png]: /images/20220322/01b1e210d6f745ad8407484758f381ae.png
[20190128123342870.png]: /images/20220322/a3650f77079b4550a05c1d5fcc475e86.png
[hive]: https://www.cnblogs.com/sunpengblog/p/10396442.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lpZ3VhbmdfODIw_size_16_color_FFFFFF_t_70 13]: /images/20220322/dd649bdb14d14778ac1a041297f97983.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lpZ3VhbmdfODIw_size_16_color_FFFFFF_t_70 14]: /images/20220322/d07927dfa1f54497b00006491775a2f8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lpZ3VhbmdfODIw_size_16_color_FFFFFF_t_70 15]: /images/20220322/cdac849a06b64e529a633fed4d82321f.png
[20190128125641276.png]: /images/20220322/b4297534fa3e4202930b91dcf8b9978b.png
[20190128125844275.png]: /images/20220322/1c3c258ed5df43f8966709d92ceece36.png
[20190828184910307.png]: /images/20220322/149644ed25c7444db42ee2481a66691f.png
[20190828185602763.png]: /images/20220322/2df076d080c94f7ea233614f627b07a4.png
[20190828184936632.png]: /images/20220322/5ed7cf0b99db42468e09545f33f698c3.png
[20190828185120799.png]: /images/20220322/479cde5d4d74483fa231b0d79d13fca1.png
[https_blog.csdn.net_lzm1340458776_article_details_43306115]: https://blog.csdn.net/lzm1340458776/article/details/43306115
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lpZ3VhbmdfODIw_size_16_color_FFFFFF_t_70 16]: https://img-blog.csdnimg.cn/20190128132353804.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lpZ3VhbmdfODIw,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lpZ3VhbmdfODIw_size_16_color_FFFFFF_t_70 17]: /images/20220322/a3ec9a39fbff4cb7951abe703b30a1fa.png
[20190128131928155.png]: /images/20220322/956fc913173749218c6e7f2889e8e776.png
[Navicat Premium]: https://blog.csdn.net/geerniya/article/details/80046391
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lpZ3VhbmdfODIw_size_16_color_FFFFFF_t_70 18]: /images/20220322/a552987671914a6e8480850350b57a54.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lpZ3VhbmdfODIw_size_16_color_FFFFFF_t_70 19]: /images/20220322/a7021a0c3910443ab613268ccc936b12.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lpZ3VhbmdfODIw_size_16_color_FFFFFF_t_70 20]: /images/20220322/76870739ac7f4b4db2876b48452fc40b.png