Hive基本命令

2020-12-11 17:46:24 编辑 0 阅读量:470

Hive的一些基本命令：
## 数据库
- 创建数据库
`CREATE DATABASE|SCHEMA [IF NOT EXISTS] <database name>`
- 删除数据库
`DROP DATABASE StatementDROP (DATABASE|SCHEMA) [IF EXISTS] database_name 
[RESTRICT|CASCADE];`
- 查看、使用数据库
```mysql
show databases;
use database_name;
```

## 表
- 创建表
```mysql
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]
# example
> CREATE TABLE IF NOT EXISTS employee ( eid int, name String, salary String, destination String)
> COMMENT ‘Employee details’
> ROW FORMAT DELIMITED
> FIELDS TERMINATED BY ‘\t’  # 一般需指定，否则可能部分行没有数据，默认为null
> LINES TERMINATED BY ‘\n’
> STORED AS TEXTFILE;
```
- 修改表
```mysql
ALTER TABLE name RENAME TO new_name
ALTER TABLE name ADD COLUMNS (col_spec[, col_spec ...])
ALTER TABLE name DROP [COLUMN] column_name
ALTER TABLE name CHANGE column_name new_name new_type
ALTER TABLE name REPLACE COLUMNS (col_spec[, col_spec ...])
# example
hive> ALTER TABLE employee RENAME TO emp;
hive> ALTER TABLE employee CHANGE name ename String; # 修改列名
hive> ALTER TABLE employee CHANGE salary salary Double; # 修改列属性
hive> ALTER TABLE employee ADD COLUMNS (dept STRING COMMENT 'Department name'); # 添加列
hive> ALTER TABLE employee REPLACE COLUMNS (eid INT empid Int, ename STRING name String);
```
- 删除表
`DROP TABLE [IF EXISTS] table_name;`
- 查看表
`SHOW tables;`
- 导入数据
```
# 数据间除了指定分隔符外，最好不要有其他符号（包括空格）
LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]
```

## 分区
Hive组织表到分区。它是将一个表到基于分区列，如日期，城市和部门的值相关方式。使用分区，很容易对数据进行部分查询。
- 添加分区
```mysql
ALTER TABLE table_name ADD [IF NOT EXISTS] PARTITION partition_spec
[LOCATION 'location1'] partition_spec [LOCATION 'location2'] ...;
partition_spec:
: (p_column = p_col_value, p_column = p_col_value, ...)
# example, 假设有一个表叫employee ，拥有如 Id, Name, Salary, Designation, Dept, 和 yoj等字段
hive> ALTER TABLE employee
> ADD PARTITION (year=’2013’)
> location '/2012/part2012';
```
- 重命名分区
```mysql
ALTER TABLE table_name PARTITION partition_spec RENAME TO PARTITION partition_spec;
# example
hive> ALTER TABLE employee PARTITION (year=’1203’)
   > RENAME TO PARTITION (Yoj=’1203’);
```
- 删除分区
```mysql
ALTER TABLE table_name DROP [IF EXISTS] PARTITION partition_spec, PARTITION partition_spec,...;
# example
hive> ALTER TABLE employee DROP [IF EXISTS]
    > PARTITION (year=’1203’);
```

## 内置运算符、函数

- 关系运算符

运算符  |操作   |描述   
------ | ----- | ------------ 
A = B  |所有基本类型   |如果表达A等于表达B，结果TRUE ，否则FALSE
A != B |所有基本类型 |如果A不等于表达式B表达返回TRUE ，否则FALSE。
A < B |所有基本类型 |TRUE，如果表达式A小于表达式B，否则FALSE。
A <= B |所有基本类型 |TRUE，如果表达式A小于或等于表达式B，否则FALSE。
A > B |所有基本类型 |TRUE，如果表达式A大于表达式B，否则FALSE。
A >= B |所有基本类型 |TRUE，如果表达式A大于或等于表达式B，否则FALSE。
A IS NULL |所有类型 |TRUE，如果表达式的计算结果为NULL，否则FALSE。
A IS NOT NULL |所有类型 |FALSE，如果表达式A的计算结果为NULL，否则TRUE。
A LIKE B |字符串 |TRUE，如果字符串模式A匹配到B，否则FALSE。
A RLIKE B |字符串 |NULL，如果A或B为NULL；TRUE，如果A任何子字符串匹配Java正则表达式B；否则FALSE。
A REGEXP B |字符串 |等同于RLIKE.

- 算术运算符

运算符  |操作   |描述   
------ | ----- | ------------ 
A + B |所有数字类型 |A加B的结果
A - B |所有数字类型 |A减去B的结果
A * B |所有数字类型 |A乘以B的结果
A / B |所有数字类型 |A除以B的结果
A % B |所有数字类型 |A除以B.产生的余数
A & B |所有数字类型 |A和B的按位与结果
A &#124; B |所有数字类型 |A和B的按位或结果
A ^ B |所有数字类型 |A和B的按位异或结果
~A |所有数字类型 |A按位非的结果

- 逻辑运算符

运算符  |操作   |描述   
------ | ----- | ------------ 
A AND B |boolean |TRUE，如果A和B都是TRUE，否则FALSE。
A && B |boolean |类似于 A AND B.
A OR B |boolean |TRUE，如果A或B或两者都是TRUE，否则FALSE。
A &#124;&#124; B |boolean |类似于 A OR B.
NOT A |boolean |TRUE，如果A是FALSE，否则FALSE。
!A |boolean |类似于 NOT A.

- 复杂运算符

运算符  |操作   |描述   
------ | ----- | ------------ 
A[n]  | A是一个数组，n是一个int |它返回数组A的第n个元素，第一个元素的索引0。
M[key] |M 是一个 Map<K, V> 并 key 的类型为K |它返回对应于映射中关键字的值。
S.x |S 是一个结构 |它返回S的s字段

- 内置函数

返回类型 |签名 |描述
------ | ----- | ------------ 
BIGINT |round(double a) |返回BIGINT最近的double值。
double |rand(), rand(int seed) |它返回一个随机数，从行改变到行。
string |concat(string A, string B,...) |它返回从A后串联B产生的字符串
string |substr(string A, int start) |它返回一个起始，从起始位置的子字符串，直到A.结束
string |substr(string A, int start, int length) |返回从给定长度的起始start位置开始的字符串。
string |upper(string A) |它返回从转换的所有字符为大写产生的字符串。
string |ucase(string A) |和上面的一样
string |lower(string A) |它返回转换B的所有字符为小写产生的字符串。
string |lcase(string A) |和上面的一样
string |trim(string A) |它返回字符串从A.两端修剪空格的结果
string |ltrim(string A) |它返回A从一开始修整空格产生的字符串(左手侧)
string |rtrim(string A) |rtrim(string A)，它返回A从结束修整空格产生的字符串(右侧)
string |regexp_replace(s |tring A, string B, string C) |它返回从替换所有子在B结果配合C.在Java正则表达式语法的字符串
int |size(Map<K.V>) |它返回在映射类型的元素的数量。
int |size(Array<T>) |它返回在数组类型元素的数量。
value of <type> |cast(<expr> as <type>)	它 |把表达式的结果expr<类型>如cast('1'作为BIGINT)代表整体转换为字符串'1'。如果转换不成功，返回的是NULL。
string |from_unixtime(int unixtime) |转换的秒数从Unix纪元(1970-01-0100:00:00 UTC)代表那一刻，在当前系统时区的时间戳字符的串格式："1970-01-01 00:00:00"
string |to_date(string timestamp) |返回一个字符串时间戳的日期部分：to_date("1970-01-01 00:00:00") = "1970-01-01"
string |get_json_object(string json_string, string path) |提取从基于指定的JSON路径的JSON字符串JSON对象，并返回提取的JSON字符串的JSON对象。如果输入的JSON字符串无效，返回NULL。
BIGINT |count(*), count(expr), |count(*) - 返回检索行的总数。
DOUBLE |sum(col), sum(DISTINCT col) |返回该组或该组中的列的不同值的分组和所有元素的总和。
DOUBLE |avg(col), avg(DISTINCT col) |返回上述组或该组中的列的不同值的元素的平均值。
DOUBLE |min(col) |返回该组中的列的最小值。
DOUBLE |max(col) |返回该组中的列的最大值。

## 视图（略）

## 索引

- 创建索引
```mysql
CREATE INDEX index_name
ON TABLE base_table_name (col_name, ...)
AS 'index.handler.class.name'
[WITH DEFERRED REBUILD]
[IDXPROPERTIES (property_name=property_value, ...)]
[IN TABLE index_table_name]
[PARTITIONED BY (col_name, ...)]
[
   [ ROW FORMAT ...] STORED AS ...
   | STORED BY ...
]
[LOCATION hdfs_path]
[TBLPROPERTIES (...)]
# example
hive> CREATE INDEX inedx_salary ON TABLE employee(salary)
    > AS 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler';
```
- 删除索引
```mysql
DROP INDEX <index_name> ON <table_name>
# example
hive> DROP INDEX index_salary ON employee;
```

## 查询

- where
- order by
- group by
- join
```mysql
表CUSTOMERS：
+----+----------+-----+-----------+----------+ 
| ID | NAME     | AGE | ADDRESS   | SALARY   | 
+----+----------+-----+-----------+----------+ 
| 1  | Ramesh   | 32  | Ahmedabad | 2000.00  |  
| 2  | Khilan   | 25  | Delhi     | 1500.00  |  
| 3  | kaushik  | 23  | Kota      | 2000.00  | 
| 4  | Chaitali | 25  | Mumbai    | 6500.00  | 
| 5  | Hardik   | 27  | Bhopal    | 8500.00  | 
| 6  | Komal    | 22  | MP        | 4500.00  | 
| 7  | Muffy    | 24  | Indore    | 10000.00 | 
+----+----------+-----+-----------+----------+
表ORDERS：
+-----+---------------------+-------------+--------+ 
|OID  | DATE                | CUSTOMER_ID | AMOUNT | 
+-----+---------------------+-------------+--------+ 
| 102 | 2009-10-08 00:00:00 |           3 | 3000   | 
| 100 | 2009-10-08 00:00:00 |           3 | 1500   | 
| 101 | 2009-11-20 00:00:00 |           2 | 1560   | 
| 103 | 2008-05-20 00:00:00 |           4 | 2060   | 
+-----+---------------------+-------------+--------+
```
    - JOIN
```
hive> SELECT c.ID, c.NAME, c.AGE, o.AMOUNT 
    > FROM CUSTOMERS c JOIN ORDERS o 
    > ON (c.ID = o.CUSTOMER_ID);
 # result
+----+----------+-----+--------+ 
| ID | NAME     | AGE | AMOUNT | 
+----+----------+-----+--------+ 
| 3  | kaushik  | 23  | 3000   | 
| 3  | kaushik  | 23  | 1500   | 
| 2  | Khilan   | 25  | 1560   | 
| 4  | Chaitali | 25  | 2060   | 
+----+----------+-----+--------+
```
    - LEFT OUTER JOIN
```
hive> SELECT c.ID, c.NAME, o.AMOUNT, o.DATE 
   > FROM CUSTOMERS c 
   > LEFT OUTER JOIN ORDERS o 
   > ON (c.ID = o.CUSTOMER_ID);
# result
+----+----------+--------+---------------------+ 
| ID | NAME     | AMOUNT | DATE                | 
+----+----------+--------+---------------------+ 
| 1  | Ramesh   | NULL   | NULL                | 
| 2  | Khilan   | 1560   | 2009-11-20 00:00:00 | 
| 3  | kaushik  | 3000   | 2009-10-08 00:00:00 | 
| 3  | kaushik  | 1500   | 2009-10-08 00:00:00 | 
| 4  | Chaitali | 2060   | 2008-05-20 00:00:00 | 
| 5  | Hardik   | NULL   | NULL                | 
| 6  | Komal    | NULL   | NULL                | 
| 7  | Muffy    | NULL   | NULL                | 
+----+----------+--------+---------------------+
```
    - RIGHT OUTER JOIN
```
hive> SELECT c.ID, c.NAME, o.AMOUNT, o.DATE 
   > FROM CUSTOMERS c 
   > RIGHT OUTER JOIN ORDERS o 
   > ON (c.ID = o.CUSTOMER_ID);
# result
+------+----------+--------+---------------------+ 
| ID   | NAME     | AMOUNT | DATE                | 
+------+----------+--------+---------------------+ 
| 3    | kaushik  | 3000   | 2009-10-08 00:00:00 | 
| 3    | kaushik  | 1500   | 2009-10-08 00:00:00 | 
| 2    | Khilan   | 1560   | 2009-11-20 00:00:00 | 
| 4    | Chaitali | 2060   | 2008-05-20 00:00:00 | 
+------+----------+--------+---------------------+
```
    - FULL OUTER JOIN
```
hive> SELECT c.ID, c.NAME, o.AMOUNT, o.DATE 
   > FROM CUSTOMERS c 
   > FULL OUTER JOIN ORDERS o 
   > ON (c.ID = o.CUSTOMER_ID);
# result
+------+----------+--------+---------------------+ 
| ID   | NAME     | AMOUNT | DATE                | 
+------+----------+--------+---------------------+ 
| 1    | Ramesh   | NULL   | NULL                | 
| 2    | Khilan   | 1560   | 2009-11-20 00:00:00 | 
| 3    | kaushik  | 3000   | 2009-10-08 00:00:00 | 
| 3    | kaushik  | 1500   | 2009-10-08 00:00:00 | 
| 4    | Chaitali | 2060   | 2008-05-20 00:00:00 | 
| 5    | Hardik   | NULL   | NULL                | 
| 6    | Komal    | NULL   | NULL                |
| 7    | Muffy    | NULL   | NULL                |  
| 3    | kaushik  | 3000   | 2009-10-08 00:00:00 | 
| 3    | kaushik  | 1500   | 2009-10-08 00:00:00 | 
| 2    | Khilan   | 1560   | 2009-11-20 00:00:00 | 
| 4    | Chaitali | 2060   | 2008-05-20 00:00:00 | 
+------+----------+--------+---------------------+
```

---
#### 参考
- [1][易百教程](http://www.yiibai.com/hive/hiveql_joins.html)

Hive基本命令

附件: