最近一段时间在做大数据测试，需要用到hive。一般常用命令都可以网上查询到，但是比较分散。现汇总如下，反面日后查询使用。

基础命令

#常用查询
 1. show databases; # 查看某个数据库
 2. use 数据库实例名;      # 进入某个数据库
 3. show tables;    # 展示所有表
 4. desc 表名;            # 显示表结构
 5. desc formatted table_name # 格式化显示表结构信息
 6. show partitions 表名; # 显示表名的分区 
 7. show create table_name;   # 显示创建表的详细信息
 8. msck repair table cr_cdma_bsi_mscktest;  # hive 刷新分区
 9. ALTER TABLE order_info DROP IF EXISTS PARTITION (dt='20200501'); # 删除分区

# 建表语句
# 内部表
create table xxx like xxx;
# 外部表
use xxdb; create external table xxx;
# 分区表
use xxdb; create external table xxx (l int) partitioned by (dt=xxx string)
# 内外部表转化
alter table table_name set TBLPROPROTIES ('EXTERNAL'='TRUE'); # 内部表转外部表
alter table table_name set TBLPROPROTIES ('EXTERNAL'='FALSE');# 外部表转内部表
# 创建视图  statement 具体的取数逻辑
create view VIEW_NAME  as  select statement； 
# 删除视图
drop view if exists my_view;



# 转换表的数据存储方式
STORED AS TEXTFILE   # 默认
STORED AS SEQUENCEFILE 
STORED AS RCFILE
STORED AS ORC
STORED AS PARQUET
# 先新增表结构-然后数据重新导入
insert into table_orc select * from souce_table;

实践篇

--把其他hadoop机器上的文件下载到本地机器进行测试
--查询hadoop服务器上的HDFS文件，并把文件放在本地
1. 登录Hadoop服务器   ssh  用户名@Hadoop的IP   （172.10.6.8）
2. 输入Hadoop的密码
3. 在Hadoop中跳转到其他集群服务器
4. ssh  用户名@跳转服务器的IP  （172.10.2.5）
5. 输入跳转服务器的密码
6. 查询Hadoop的文件所在的目录  
# hadoop fs -ls  目录名所在路径（/user/wls81/data/nginxlog/2019021910）
7.查看文件的内容
# hadoop fs -cat  文件名所在路径（ /user/wls81/data/nginxlog/2019021910/nginxlogres_2019021910.1550541609783.log）
8.把文件或者目录下载到本地目录（是172.10.2.5这台服务器的本地目录）
# hadoop fs -get  文件名或者目录的路径（/user/wls81/data/nginxlog/2019021910/nginxlogres_2019021910.1550541609783.log）
9.把下载的本地目录的文件复制到第一个hadoop服务所在的本地目录
# scp -r   文件所在目录   用户名@Hadoop的IP：预备拷贝到的目录 （文件不需要加参数 -r）
10.把步骤9下载的文件上载到hadoop上的指定牡蛎
# hdfs dfs -put  文件或目录所在路径   hadoop服务器上所在的目录
11.把步骤10上载到hadoop服务器上的文件映射到表中
# alter table 外表表名称 if not exists 
partition (分区字段 = '2019-02-19', 分区字段 = '15') location '步骤是文件所在目录';

建表语句示例

# 普通外表
CREATE EXTERNAL TABLE `user_info`(
  `id` int COMMENT '',
  `name` string COMMENT '',
  `phone` bigint COMMENT '',
  `email` string COMMENT '',
  `create_at` timestamp COMMENT '',
  `cardid` bigint COMMENT '',
  `postcode` int COMMENT '',
  `address` string COMMENT '',
  `birthday` string COMMENT '',
  `age` int COMMENT '',
  `sex` int COMMENT '',
  `check_digit` int COMMENT '',
  `is_valid` boolean COMMENT '')
ROW FORMAT DELIMITED
  FIELDS TERMINATED BY ','
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  'hdfs://sandbox.hortonworks.com:8020/apps/hive/warehouse/mydata.db/user_info'

# 分区外表
CREATE EXTERNAL TABLE `order_info`(
  `order_id` int COMMENT '',
  `order_date` timestamp COMMENT '',
  `user_id` int COMMENT '',
  `product_id` int COMMENT '',
  `product_number` int COMMENT '',
  `price` int COMMENT '',
  `amount` int COMMENT '')
partitioned by(`dt` string)
ROW FORMAT DELIMITED
  FIELDS TERMINATED BY ','
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  'hdfs://sandbox.hortonworks.com:8020/apps/hive/warehouse/mydata.db/order_info'

# 数据存储格式 orc
# orc 存储的数据格式 比 filetxt存储的格式 小3~4倍
CREATE TABLE `user_info_orc`(
  `id` int COMMENT '',
  `name` string COMMENT '',
  `phone` bigint COMMENT '',
  `email` string COMMENT '',
  `create_at` timestamp COMMENT '',
  `cardid` bigint COMMENT '',
  `postcode` int COMMENT '',
  `address` string COMMENT '',
  `birthday` string COMMENT '',
  `age` int COMMENT '',
  `sex` int COMMENT '',
  `check_digit` int COMMENT '',
  `is_valid` boolean COMMENT '')
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS INPUTFORMAT orc

HIVE中的数据存储格式

--textfile
hive的默认存储格式
存储方式：行存储
磁盘开销大 数据解析开销大
压缩的text文件 hive无法进行合并和拆分

--SequenceFile
二进制文件以key,value的形式序列化到文件中
存储方式：行存储
可分割 压缩
一般选择block压缩
优势是文件和hadoop api中的mapfile是相互兼容的

--rcfile
存储方式：数据按行分块 每块按照列存储
压缩快 快速列存取
读记录尽量涉及到的block最少
读取需要的列只需要读取每个row group 的头部定义。
读取全量数据的操作 性能可能比sequencefile没有明显的优势

--orc
存储方式：数据按行分块 每块按照列存储
压缩快 快速列存取
效率比rcfile高,是rcfile的改良版本

--自定义格式
用户可以通过实现inputformat和 outputformat来自定义输入输出格式

数据格式

存储方式

支持压缩方式

数据特点

TEXTFILE

行存储

Gzip、Bzip2

默认格式，数据不做压缩，磁盘开销大，数据解析开销大；若采用压缩方式时系统自动检查，执行查询时自动解压，但使用这种方式，hive不会对数据进行切分，从而无法对数据进行并行操作

SEQUENCEFILE

行存储

NONE，RECORD，BLOCK

二进制文件,以<key,value>的形式序列化到文件中，是Hadoop API提供的一种二进制文件支持，其具有使用方便、可分割、可压缩的特点

RCFILE

行划分 ,列存储

RCFile文件格式是FaceBook开源的一种Hive的文件存储格式，首先将表分为几个行组，对每个行组内的数据进行按列存储，每一列的数据都是分开存储，正是先水平划分，再垂直划分的理念

ORC

列式存储

支持多种压缩方式

默认采用ZLIB方式压缩，有着很高的压缩比

PARQUET

列存储

默认无压缩

如果从存储的角度对比 ORC 和 PARQUET 明显优于别的格式数据；这两种也是工作中，常用的两种格式；