hive-2. 使用

128 阅读6分钟

Hive连接方式

  1. Hive CLI
hive -n hive
hive --service cli --help
hive --hiveconf hive.root.logger=INFO,console
hive -e "shwo tables"
hive --hiveconf hive.cli.print.header=true -f $sql_filename >> $output_filename
  1. Beeline hive --service beeline --help beeline -u jdbc:hive2://HS2HOST:HS2_HOST:HS2_PORT kinit -kt /soft/conf/hive/hive.keytab  hive/v0107-c0a80236.4ead5.c.local@HADOOP.COM beeline --verbose=true !connect jdbc:hive2://192.168.2.54:10000/default;principal=hive/v0107-c0a80236.4ead5.c.local@HADOOP.COM; Please make sure it is the hive’s principal, not the user’s. And when you kinit, it should be kinit user’s keytab, not the hive’s keytab.
  2. 查看信息
---查看当前登陆用户
select current_user();
---设置引擎
set hive.execution.engine=tez;
---设置打印表头
set hive.cli.print.header=true;
--设置文件格式
SET hive.default.fileformat=ORC

Hive数据类型

Types

DDL

database sql

--创建数据库
create database if not exists db_name;
create database db_name COMMENT 'testing' LOCATION '/user/testuser/test1.db';
--查看Hive中包含数据库
show databases;
--查看hive数据库位置等信息
desc database db_name;
--为hive设置键值对属性
alter database hive set dbproperties;
--切换到hive数据库下
use hive;
--删除不含表的数据库
drop database if exists hive;
--删除数据库和它中的表
drop database if exists hive cascade;

table sql

--创建内部表(管理表)
create table if not exists hive.usr(
name string comment 'username',
pwd string comment 'password',
address struct<street:string,city:string,state:string,zip:int> comment 'home address',
identify map<int,tinyint> comment 'number,sex')
PARTITIONED BY (dt string)
row format delimited --分割符设置开始语句
fields terminated by ',' --设置字段与字段之间的分隔符
collection items terminated by '-'
map keys terminated by ':'
NULL DEFINED AS ''
STORED AS orc
comment 'description of the table'
tblproperties('creator'='me','time'='2020.1.1');
--创建外部表
create external table if not exists usr2(
name string,
pwd string,
address struct<street:string,city:string,state:string,zip:int>,
identify map<int,tinyint>)
row format delimited 
fields terminated by ','
location '/user/market/app.db/usr2';
--创建hbase外部表
--主要是配置hbase.table.name和hbase.columns.mapping,一个是hbase表名,一个是hbase字段和hive字段的一一映射,然后就可以从hive中读写hbase数据:
CREATE EXTERNAL TABLE hive_hbase_table(
key string,
name string,
desc string
)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES
("hbase.columns.mapping" = ":key,columfamily:name,columnfamily:desc")
TBLPROPERTIES("hbase.table.name" = "hbase_table");
--创建相同结构的表
create table iris like zhangyu_out;
--创建视图
CREATE VIEW test_view(
id,
name_length
)
AS SELECT id,length(name) FROM test;
--修改表操作
alter table table_name add columns(hobby string);
alter table table_name change column_name new_name new_type
alter table table_name replace columns(uname string);
alter table table_name set tblproperties('creator'='liming'); 
alter table table_name partition(city="beijing",state="China")
alter table table_name set fileformat orc;
alter table table_name set tblproperties ('key1'='value1');
ALTER TABLE table_name SET SERDE serde_class_name [WITHSERDEPROPERTIESserde_properties]
ALTER TABLE table_name SET SERDEPROPERTIES ('field.delim' = '\t');
ALTER TABLE table_name CLUSTERED BY (col_name, col_name, ...) [SORTEDBY(col_name, ...)] INTO num_buckets BUCKETS
--分区
alter table table_name add partition(dt='2020-06-22') location "hdfs://ns1/user/test";
ALTER TABLE table_name PARTITION(dt='2020-06-22') SET LOCATION 'hdfs://user/user1/some_table/2012';
alter table table_name drop partition(dt='2020-06-22');
msck repair table table_name;--修复分区 修复分区就是重新同步hdfs上的分区信息
alter table table_name add columns(col1 string);--如果数据表table_name已经有旧的分区(例如:dt=20190101),则该旧分区中的col1将为空且无法更新,即便insert overwrite该分区也不会生效。
alter table tb add columns(col1 string) cascade;--cascade的中文翻译为“级联”,也就是不仅变更新分区的表结构(metadata),同时也变更旧分区的表结构。
--手动更新旧分区表结构的方法
ALTER TABLE table_name SET TBLPROPERTIES('EXTERNAL'='TRUE'); 
drop table table_name;--(only metadata will be removed).
create EXTERNAL table table_name -- using updated DDL with types changed and with the same LOCATION.
MSCK REPAIR TABLE tablename;
ALTER TABLE tablename SET TBLPROPERTIES('EXTERNAL'='FALSE'); 
--展示表详细信息
show partitions table_name;
show create table table_name;
desc formatted table_name partition (dt='2020-06-02',cate_id='880');
--统计表数据
analyze table table_name compute statistics;
analyze table table_name compute statistics for columns;
analyze table table_name partition(dt=day,hr=num) compute statistics noscan;
--清空表
truncate table table_name;
--删除表
drop table if exists table_name;
--创建索引
create index t1_index ontable t1(id) as 'compact' with deferred rebuild;
--UDF
ADD jar /var/lib/hive/hive-1.0-SNAPSHOT.jar;
list jars;
CREATE TEMPORARY FUNCTION STRIP AS 'com.nathan.bigdata.hive.Strip';
select strip(' hadoop') from t1;
show functions;

DML

--插入语句
insert into table test values (1, "nathan");
insert into table test partition(dt='2020-05-22') values ("1", "nathan");
insert into table test PARTITION (ds='2020-04-08') values ("nathan");
--从其他表导入
insert OVERWRITE TABLE test2 select * from test;

set hive.support.quoted.identifiers=none;
set hive.resultset.use.unique.column.names=false;
set hive.cli.print.header=True;
insert OVERWRITE TABLE table_name PARTITION(dt='$PARTITION') select \\`(dt)?+.+\\` from ${MARKET}_tmp.$tmp_table
--导出表数据
export table dmc_dev.test_table to "hdfs://xxxx";

INSERT OVERWRITE LOCAL DIRECTORY './a' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' select * from table_name where dt='2019-05-09'
--加载数据
load data local inpath '/var/lib/hive/test' overwrite into table t1 partition (pt_d = '201701');

--查询
WITH t1 AS (
		SELECT *
		FROM test
	)
SELECT *
FROM t1;
--内连接使用比较运算符根据每个表共有的列的值匹配两个表中的行
select stu.*, course.* from stu join course on(stu.id=course.sid);
--左连接的结果集包括"LEFT OUTER"子句中指定的左表的所有行, 而不仅仅是连接列所匹配的行.如果左表的某行在右表中没有匹配行, 则在相关联的结果集中右表的所有选择列均为空值
select stu.*, course.* from stu left outer join course on(stu.id=course.sid);
--右连接是左向外连接的反向连接,将返回右表的所有行 如果右表的某行在左表中没有匹配行,则将为左表返回空值
select stu.*, course.* from stu right outer join course on(stu.id=course.sid);
--全连接返回左表和右表中的所有行
select stu.*, course.* from stu full outer join course on(stu .id=course.sid);
--半连接是 Hive 所特有的, Hive 不支持 in 操作,但是拥有替代的方案; left semi join, 称为半连接, 需要注意的是连接的表不能在查询的列中,只能出现在 on 子句中
select stu.* from stu left semi join course on(stu .id=course .sid);

hive -e  "set hive.support.quoted.identifiers=none;insert OVERWRITE TABLE ${table_list[i]} PARTITION(dt='$PARTITION') select \\\`(dt)?+.+\\\` from ${MARKET}_tmp.$tmp_table"
hive -e "select regexp_replace('2000万及以上','(.*)(及以上)','≥\$1');"

--所有行进行统计,包括NULL行
select count(*) from tbl;
--所有行进行统计,包括NULL行
select count(1) from tbl;
--对column1中非Null进行统计
select count(column1) from tbl;

行列转换

explode(a) - separates the elements of array a into multiple rows, or the elements of a map into multiple rows and columns
    可以将数组炸开成多行,或者将map炸开成多行多列,是Hive内置的UDTF

split(str, regex) - Splits str around occurances that match regex
    按照正则规则去切割字符串
    
collect_list(x) - Returns a list of objects with duplicates
    返回不去重的集合
    
collect_set(x) - Returns a set of objects with duplicate elements eliminated
    返回一个去重的集合

concat_ws(separator, [string | array(string)]+) - returns the concatenation of the strings separated by the separator
    返回一个特定分隔符的拼接字符串

max(expr) - Returns the maximum value of expr
    返回表达式的最大值

udf

select concat_ws(',', COLLECT_SET(SKU.col1)) AS col2 from test;

bucket sql

Hive 中 table 可以拆分成 Partition table 和 桶(BUCKET), 对于Table或者Partition, Hive可以进一步组织成桶,也就是说桶Bucket是更为细粒度的数据范围划分. Bucket是对指定列进行hash,然后根据hash值除以桶的个数进行求余,决定该条记录存放在哪个桶中. 桶操作是通过 Partition 的 CLUSTERED BY 实现的,BUCKET 中的数据可以通过 SORT BY 排序.

优点①:获得更高的查询处理效率.桶为表加上了额外的结构,Hive 在处理有些查询时能利用这个结构. 具体而言,连接两个在相同列上划分了桶的表,可以使用 Map-side Join 的高效实现.

优点②:抽样(sampling)可以在全体数据上进行采样,这样效率自然就低,它还是要去访问所有数据. 而如果一个表已经对某一列制作了bucket,就可以采样所有桶中指定序号的某个桶,这就减少了访问量.

create table bucket_table(id string) clustered by(id) into 4 buckets;

权限sql

By default, the Metastore uses the HadoopDefaultAuthenticator for determing user -> group mappings metastore默认会以metastore所在的机器根据用户名获取用户所属的组,而不是客户端的组。

Role

hive的用户、组就是执行hive客户端进程的用户以及他所在的linux上的组。(可以使用whoami 来看进程,也可以使用bash -c groups来看该用户所属的组) 可以使用grant命令对用户,组明确的授予对表,database 上的具体权限。 例如Select, Update, ALL等。  Hive支持的权限有Users,Groups,Roles,一个角色可以包含组或者用户

CREATE ROLE role_name
DROP ROLE role_name

SHOW GRANT ROLE principal_specification
CREATE ROLE youxi_role;
GRANT ROLE youxi_role TO USER fatkun;

权限

Hive的权限支持全局权限(Global)、数据库、表、分区、列。 priv_type 权限名称    含义 ALL    所有权限 ALTER    允许修改元数据(modify metadata data of object)—表信息数据 UPDATE    允许修改物理数据(modify physical data of object)—实际数据 CREATE    允许进行Create操作 DROP    允许进行DROP操作 INDEX    允许建索引(目前还没有实现) LOCK    当出现并发的使用允许用户进行LOCK和UNLOCK操作 SELECT    允许用户进行SELECT操作 SHOW_DATABASE    允许用户查看可用的数据库

GRANT ALL TO USER fatkun;
GRANT ALL ON DATABASE DEFAULT TO USER fatkun;
GRANT ALL ON TABLE test TO GROUP kpi;
REVOKE ALL ON TABLE test FROM GROUP kpi;
REVOKE ALL FROM fatkun;--授权还是挺麻烦的。。你在哪一级别授权,只能在那个级别收回授权。
--查看权限
SHOW GRANT GROUP kpi ON TABLE test;