HIVE命令 hive命令查看表中所有数据

最近一段时间在做大数据测试，需要用到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来自定义输入输出格式