一、HiveSQL基本操作

约定不等于承诺〃 2022-05-08 11:28 265阅读 0赞

### 文章目录 ###

*   *  一、Hive数据类型
     *   *  1、基本类型
         *  2、复杂类型
     *  二、存储格式
     *   *  1、textfile
         *  2、SequenceFile
         *  3、RCFile
         *  4、ORCFile
         *  5、Parquet
     *  三、操作数据库
     *   *  1、创建数据库
         *  2、删除数据库
         *  3、进入数据库
     *  四、操作表属性
     *   *  1、显示数据库下面所有表
         *  2、删除表
         *  3、显示建表结果
         *  4、显示表分区
         *  5、修改表属性
         *  6、表重命名
         *  7、新增列
         *  8、删除列
         *  9、修改列
         *  10、删除分区
         *  11、增加分区
         *  12、修改分区、LOCATION
     *  四、创建表
     *  五、LOAD DATA语句
     *  六、hive内置运算符
     *   *  1、算术运算符
         *  2、逻辑运算符
         *  3、复杂运算符
     *  七、hive视图与索引
     *  八、实例
     *   *  1、把本地文件放在新建表中
         *  2、把hdfs文件放在新建表中
         *  3、把查询结果放在一个新建表中
         *  4、把查询结果放在本地文件中
         *  5、把查询结果放在HDFS文件中
         *  6、差集使用左右连接
         *  7、其他
         *  8、shell 运行hive
     *  九、注意

## 一、Hive数据类型 ##

Hive所有数据类型分为两种：

*  基本类型
 *  复杂类型

### 1、基本类型 ###

<table> 
 <thead> 
  <tr> 
   <th align="left">类型</th> 
   <th align="left">描述</th> 
   <th align="left">示例</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">boolean</td> 
   <td align="left">true/false</td> 
   <td align="left">true</td> 
  </tr> 
  <tr> 
   <td align="left">tinyint</td> 
   <td align="left">1字节的有符号整数</td> 
   <td align="left">-127~128</td> 
  </tr> 
  <tr> 
   <td align="left">smallint</td> 
   <td align="left">2个字节的有符号整数，-32768~32767</td> 
   <td align="left">1S</td> 
  </tr> 
  <tr> 
   <td align="left">int</td> 
   <td align="left">4个字节的带符号整数</td> 
   <td align="left">1</td> 
  </tr> 
  <tr> 
   <td align="left">bigint</td> 
   <td align="left">8字节带符号整数</td> 
   <td align="left">1L</td> 
  </tr> 
  <tr> 
   <td align="left">float</td> 
   <td align="left">4字节单精度浮点数</td> 
   <td align="left">1.0</td> 
  </tr> 
  <tr> 
   <td align="left">double</td> 
   <td align="left">8字节单精度浮点数</td> 
   <td align="left">1.0</td> 
  </tr> 
  <tr> 
   <td align="left">deicimal</td> 
   <td align="left">任意精度的带符号小数</td> 
   <td align="left">1.0</td> 
  </tr> 
  <tr> 
   <td align="left">string</td> 
   <td align="left">字符串，变长</td> 
   <td align="left">“a”，‘b’</td> 
  </tr> 
  <tr> 
   <td align="left">varchar</td> 
   <td align="left">变长字符串</td> 
   <td align="left">“a”，‘b’</td> 
  </tr> 
  <tr> 
   <td align="left">char</td> 
   <td align="left">固定长度字符串</td> 
   <td align="left">“a”，‘b’</td> 
  </tr> 
  <tr> 
   <td align="left">binary</td> 
   <td align="left">字节数组</td> 
   <td align="left"></td> 
  </tr> 
  <tr> 
   <td align="left">timestamp</td> 
   <td align="left">时间戳，纳秒精度</td> 
   <td align="left"></td> 
  </tr> 
  <tr> 
   <td align="left">date</td> 
   <td align="left">日期</td> 
   <td align="left">‘2018-04-07’</td> 
  </tr> 
 </tbody> 
</table>

### 2、复杂类型 ###

<table> 
 <thead> 
  <tr> 
   <th align="left">类型</th> 
   <th align="left">描述</th> 
   <th align="left">示例</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">array</td> 
   <td align="left">有序的的同类型的集合</td> 
   <td align="left">array(1,2)</td> 
  </tr> 
  <tr> 
   <td align="left">map</td> 
   <td align="left">key-value：key必须为原始类型，value可以任意类型</td> 
   <td align="left">map(‘a’,1,’b’,2)</td> 
  </tr> 
  <tr> 
   <td align="left">struct</td> 
   <td align="left">字段集合,类型可以不同</td> 
   <td align="left">struct(‘1’,1,1.0), named_stract(‘col1’,’1’,’col2’,1,’clo3’,1.0)</td> 
  </tr> 
 </tbody> 
</table>

## 二、存储格式 ##

Hive会为每个创建的数据库在HDFS上创建一个目录，该数据库的表会以子目录形式存储，表中的数据会以表目录下的文件形式存储。对于default数据库，默认的缺省数据库没有自己的目录，default数据库下的表默认存放在/user/hive/warehouse目录下。

### 1、textfile ###

textfile为默认格式，存储方式为行存储。数据不做压缩，磁盘开销大，数据解析开销大。

### 2、SequenceFile ###

SequenceFile是Hadoop API提供的一种二进制文件支持，其具有使用方便、可分割、可压缩的特点。  
SequenceFile支持三种压缩选择：NONE, RECORD, BLOCK。 Record压缩率低，一般建议使用BLOCK压缩。

### 3、RCFile ###

一种行列存储相结合的存储方式。

### 4、ORCFile ###

数据按照行分块，每个块按照列存储，其中每个块都存储有一个索引。hive给出的新格式，属于RCFILE的升级版,性能有大幅度提升,而且数据可以压缩存储,压缩快 快速列存取。

### 5、Parquet ###

Parquet也是一种列式存储，同时具有很好的压缩性能；同时可以减少大量的表扫描和反序列化的时间。

## 三、操作数据库 ##

### 1、创建数据库 ###

-- 创建数据库语法：
    CREATE DATABASE IF NOT EXISTS database_name;
    
    --例如：
    CREATE DATABASE IF NOT EXISTS userdb;

### 2、删除数据库 ###

-- 删除数据库语法：
    DROP DATABASE StatementDROP (DATABASE|SCHEMA) [IF EXISTS] database_name；
    
    -- 例如：
    DROP DATABASE IF EXISTS userdb;

### 3、进入数据库 ###

-- 进入数据库
    USE database_name;

## 四、操作表属性 ##

### 1、显示数据库下面所有表 ###

SHOW TABLES;

### 2、删除表 ###

DROP TABLE [IF EXISTS] table_name;

### 3、显示建表结果 ###

SHOW CREATE TABLE table_name;

### 4、显示表分区 ###

SHOW PARTITION table_name;

### 5、修改表属性 ###

-- 内部表转外部表
    ALTER TABLE table_name SET TBLPROPERTIES ('EXTERNAL'='TRUE'); 
    
    -- 外部表转内部表
    ALTER TABLE table_name SET TBLPROPERTIES ('EXTERNAL'='FALSE');

### 6、表重命名 ###

ALTER TABLE table_name RENAME TO new_table_name;

### 7、新增列 ###

ALTER TABLE table_name ADD COLUMNS (cloumn_name string);

### 8、删除列 ###

ALTER TABLE name DROP [COLUMN] column_name;

### 9、修改列 ###

ALTER TABLE name CHANGE column_name new_name new_type;

### 10、删除分区 ###

ALTER TABLE table_name DROP IF EXISTS PARTITION(day=);     -- 这里分区是day

### 11、增加分区 ###

-- 分区字段包含:p_hour,p_city,p_loctype
    ALTER TABLE table_name IF NOT EXISTS \
    ADD PARTITION (p_hour='2017113003', p_city='573', p_loctype='MHA');

### 12、修改分区、LOCATION ###

-- 修改location
    ALTER TABLE table_name SET LOCATION "new_location_name";
    
    -- partition修改location路径
    ALTER TABLE table_name PARTITION (dt='2008-08-08') SET LOCATION "new_location_name";
    
    -- 修改分区名
    ALTER TABLE table_name PARTITION (dt='2008-08-08') RENAME TO PARTITION (dt='20080808');

## 四、创建表 ##

语法：

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.] table_name
    
    [(col_name data_type [COMMENT col_comment], ...)]
    [COMMENT table_comment]
    [ROW FORMAT row_format]
    [STORED AS file_format]

举例：

CREATE TABLE IF NOT EXISTS employee ( eid int, name String,
    salary String, destination String)
    COMMENT ‘Employee details’
    ROW FORMAT DELIMITED
    FIELDS TERMINATED BY ‘\t’
    LINES TERMINATED BY ‘\n’
    STORED AS TEXTFILE;

## 五、LOAD DATA语句 ##

语法如下：

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename 
    [PARTITION (partcol1=val1, partcol2=val2 ...)]
    
    -- 说明：
    LOCAL是标识符指定本地路径。它是可选的。
    OVERWRITE 是可选的，覆盖表中的数据
    PARTITION 这是可选的

参数描述：

*  LOCAL是标识符，指本地路径，它是可选的
    
     *  命令中带LOCAL，表示从本地文件系统中加载数据，可以是相对路径，绝对路径，目录，URL(file:///user/hive/project/data)
     *  命令中不带LOCAL，表示从HDFS加载文件，可以是完整的URL方式或者使用fa.default.name定义的值
 *  FILEPATH 文件路径，可以是：
    
     *  相对路径，如：project/data/file.txt
     *  绝对路径，如：/user/hive/project/data/file.txt
     *  完整的URL，如：hdfs://namenode:9000/user/hive/project/data/file.txt
     *  目录，如：/user/project/data；表示加载目录下的所有文件
 *  OVERWRITE：
    
     *  命令中带OVERWRITE，加载数据之前会先清空目标表或分区中的内容
     *  命令中不带OVERWRITE，向目标表后者分区追加内容

例如：

*  本地数据加载到表

load data local inpath '/opt/test/emp.txt' \
    into table emp_part partition (year='2016', month='3');

## 六、hive内置运算符 ##

### 1、算术运算符 ###

### 2、逻辑运算符 ###

### 3、复杂运算符 ###

## 七、hive视图与索引 ##

## 八、实例 ##

### 1、把本地文件放在新建表中 ###

-- 创建表时加载
    CREATE TABLE table_name (year string, temperature int) \
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LOAD DATA LOCAL INPATH 'xxx/input.txt' \
    OVERWRITE INTO TABLE table_name;

### 2、把hdfs文件放在新建表中 ###

-- 创建表时加载
    CREATE TABLE table_name (year string, temperature int) \
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' \
    LOAD DATA INPATH '/emp.txt' INTO TABLE table_name partition (year='2016', month='6');

### 3、把查询结果放在一个新建表中 ###

描述：  
create table 表名 as sql

举例：

create table meiying_rate as \
    select a.pkg_name,b.installed-b.uninstall,a.rank from yinyl.meiying_applist a left outer join user.user_app_stat_daily b \
    on a.pkg_name=b.packageid \
    where b.day=20151101 and a.rank<=100000 \
    order by a.rank;

### 4、把查询结果放在本地文件中 ###

描述：  
insert overwrite local directory ‘路径’ row format delimited fields terminated by ‘|’ sql

举例：

-- 第一种方式
    insert overwrite local directory '${dir_in}rate_${DATE}/' \
    row format delimited fields terminated by '|' 、
    select a.pkg_name,b.installed-b.uninstall,a.rank from yinyl.meiying_applist a left outer join user.user_app_stat_daily b on a.pkg_name=b.packageid where b.day=${
       DATE} and a.rank<=100000 order by a.rank；
    
    -- 第二种方式
    hive>
    hive -e 'select * from testdb2.emp'  >> ./emp_export.txt

### 5、把查询结果放在HDFS文件中 ###

insert overwrite directory '${dir_in}rate_${DATE}/' \
    row format delimited fields terminated by '|' 、
    select a.pkg_name,b.installed-b.uninstall,a.rank from yinyl.meiying_applist a left outer join user.user_app_stat_daily b on a.pkg_name=b.packageid where b.day=${
       DATE} and a.rank<=100000 order by a.rank；

### 6、差集使用左右连接 ###

select from 表a right(left) outer join 表b on 条件 where

create table diff_in_bj_idc_mangguo as SELECT BB.uid FROM (SELECT dw_user_lastlogin.uid  FROM report_dw_mdp.dw_user_lastlogin where dw_user_lastlogin.day=20151028 and  dw_user_lastlogin.app_id ='X0LuNCmE6t6VuvA1b3EyW4') AA RIGHT OUTER JOIN (SELECT user_bj_idc_20151028.uid FROM yuankai.user_bj_idc_20151028) BB ON (AA.uid= BB.uid) WHERE AA.uid IS NULL;

### 7、其他 ###

去重：distinct  
分组：group by  
排序：order by （若有where放在其后）

### 8、shell 运行hive ###

hive>hive -e “insert overwrite local directory ‘$\{dir\_in\}rate\_$\{DATE\}/’ row format delimited fields terminated by ‘|’ select a.pkg\_name,b.installed-b.uninstall,a.rank from yinyl.meiying\_applist a left outer join user.user\_app\_stat\_daily b on a.pkg\_name=b.packageid where b.day=$\{DATE\} and a.rank<=100000 order by a.rank”

## 九、注意 ##

上述实例，创建表时，使用外部表，否则删除表时，会将原始数据删掉；