一、一些常用命令
创建数据库
create database if not exists my_base;
数据库里有表格 my_table
show databases; //列出数据库
describe databases my_base; //展示数据库 my_base的信息
use my_base; //使用某个数据库 my_base
set hive.cli.print.current.db=true; //显示列头
set hive.cli.print.current.db=false; //关闭列头
show tables; //展示这个数据库里面的所有表格
show tables in my_base; //展示数据库 my_base 里面的所有表格
show tables like 'm.*'; //通配符 模糊查找，以 m 开头
show create table my_table; //获得表格 my_table 的建表语句，其中包括表格的字段，HDFS的 location 等信息
describe my_table; //展示表格 my_table 的字段以及字段类型
describe formatted my_table; //详细描述表格 my_table，包括表格的结构，所在的 database，owner，location，
//表格的类型（managed table or external table），存储信息等
二、创建表
三种创建 table 的方法
create table test_table (
a INT 【COMMENT 'comment1'】,
b STRING 【COMMENT 'comment2'】
); //创建 test_table 并指定字段类型【、添加字段注释】
create table test1
like
test_table; //只是复制了表的结构，并没有复制内容；
create table test2
as
select * from test_table; //从其他表格查询，再创建表格；
示例（完整建表语句及关键词解释）
CREATE EXTERNAL TABLE my_table( //关键词 EXTERNAL：表示该表为外部表；如果没有 EXTERNAL 关键词，则表示内部表
id INT,
ip STRING COMMENT 'Visitor ip', //关键词 COMMENT：为表和列添加注释
avg_view_depth DECIMAL(5,1),
bounce_rate DECIMAL(6,5)
)
COMMENT 'my table'
PARTITIONED BY (day STRING) //关键词 PARTITIONED BY：表示该表为分区表；这里分区字段为 day，类型为 string
ROW FORMAT DELIMITED //关键词 ROW FORMAT DELIMITED：指定表的分隔符，通常后面要与以下关键字连用：(1)FIELDS TERMINATED BY ‘,’: 指定每行中字段分隔符为逗号;
FIELDS TERMINATED BY ',' //(2)LINES TERMINATED BY ‘\n’:指定行分隔符 COLLECTION;(3)ITEMS TERMINATED BY ‘,’:指定集合中元素之间的分隔符;
//(4)MAP KEYS TERMINATED BY ‘:’ //指定数据中 Map 类型的 Key 与 Value 之间的分隔符
STORED AS textfile //关键词 STORED AS：指定表在 HDFS 上的文件存储格式，可选的文件存储格式有：TEXTFILE：文本，默认值；SEQUENCEFILE：二进制序列文件；
//ORC：列式存储格式文件，比 RCFILE 有更高的压缩比和读写效率；PARQUET：列出存储格式文件
LOCATION '/usr/local/hadoop/examples/input'; //关键词 LOCATION：指定表在HDFS上的存储位置
三、修改表格
alter table my_table add columns (reporttime STRING COMMENT '上报时间'); //为表格增加列
alter table test_table rename to my_table; //修改表名
alter table my_table add partition (day=20200416); //增加分区
alter table my_table drop partition (day=20200416); //删除分区
alter table my_table partiton (day=20200416) rename to partition (day=20200415); //修改分区
load data local inpath '/liming/hivedata/datatest' overwrite into table my_table; //从文件加载数据（覆盖原来数据）
load data local inpath '/liming/hivedata/datatest' into table my_table; //从文件加载数据（添加数据）
insert overwrite directory 'tmp/tmpdata' select a.* from my_table a; //导出数据到文件
四、查询和分析数据
1、基本操作
表格 my_table 的格式：owner（string），key（string），value（int）， day（bigint）
select * from my_table; //查找数据
select * from my_table limit 10; //查找前10行数据
select * from my_table where day=20200416; //查找day=20200416的数据
select * from my_table where day>=20200415 and day<=20200416;
select * from my_table where day=20200416 order by value; //查出来之后按照value的值升序排序
select * from my_table where day=20200416 order by value desc; //降序
insert [overwrite] into table my_table partition (day=20200416) values('20031','key_20032',1); //不使用overwrite是往表格里追加一条数据，使用overwrite就是覆盖整个表格
2、高级操作
- (inner) join: 在表格中至少存在一个匹配时，inner join 的关键字返回行；
- left join: 会从左边的表格返回所有的行，即使在右边的表格中没有匹配的行。
- right join：会从右边的表格返回所有的行，即使在左边的表格中没有匹配的行。
- full join：只要其中的一张表存在匹配，full join 就会返回行。在某些数据库中，full join 也称作 full outer join。
- union：用于合并两个或者多个 select 语句的结果集。
- is NULL & is not NULL：来判断某个字段是否是空集。
一个简单示例：
select * from my_test1 t1
inner join my_teat2 t2
on t1.id=t2.id;
3、group by 与常见聚合函数
-
group by：通常和聚合函数一起使用，根据一个或者多个列对结果进行分组
-
常见的聚合函数有：
函数名 功能 AVG 返回列值的平均值 COUNT 返回列值的数目 MAX/MIN 返回一列值的最大值/最小值 SUM 返回数列值的总和
4、一些数值函数
| 函数 | 功能 |
|---|---|
| MOD(x, y) | 取模：x%y |
| LN(DOUBLE a) | 返回给定数值的自然对数 |
| POWER(DOUBLE a, DOUBLE b) | 返回某数的乘幂 |
| SQRT(DOUBLE a) | 开平方 |
| SIN/COS/ASIN/ACOS | 三角函数 |