Hive笔记

使用Hive REGEXP_REPLACE()函数删除非字母数字或非数字字符

获取数字

SELECT regexp_replace("7X789", "[^0-9]+", "")
FROM table_name

SELECT regexp_replace("7X789", "[^0-9]", "")
FROM table_name

获取字母

select regexp_replace("a01a@2w3t41y1","[^a-zA-Z]+", "") 
from table_name; 

select regexp_replace("a01a@2w3t41y1","[^a-zA-Z]", "") 
from table_name; 

获取字母和数字

select regexp_replace("a01a@2w3t41y1","[^a-zA-Z0-9]+", "") 
from table_name; 

select regexp_replace("a01a@2w3t41y1","[^a-zA-Z0-9]", "") 
from table_name; 

获取字符符号

字符转化为数值

select cast(salary AS float) from table_name
select cast(salary AS int) from table_name
select cast(salary AS int) from table_name where cast(salary AS int) > 0

外/内部表

desc formatted tablename;
或
describe extended tablename;
如果是内部表/管理表，则会显示 tableType：MANAGD_TABLE
如果是外部表，则会显示tableType:EXTERNAL_TABLE

修改hive表

* 外部表，hdfs目录文件删除，hive中的metastore元数据没有删除
操作：ALTER TABLE <table_name> DROP PARTITION (dt='2020-12-29');
注：MSCK REPAIR TABLE <table_name>; 
Msck不会删除目录在文件系统(metastore)中不存在的分区。它只会通知哪些分区的目录在文件系统(metastore)中不存在。你应该手动运行“alter table drop partition (field='...');

* 外部表，hive中的metastore表drop table <table_name>后，添加相应分区元数据(metastore)
操作：ALTER TABLE <table_name> ADD PARTITION (dt='2020-12-29');
  或：MSCK REPAIR TABLE <table_name>;  

参考链接
参考链接

hive的注释(comment)中文乱码的解决方法

注：修改后新创建的表无问题，旧表需要修改表的comment才能显示正常

创建表的时候，comment说明字段包含中文，表成功创建成功之后，desc的时候中文说明显示乱码.如下图所示:

我们知道hive的元数据是有mysql管理的,所以这是mysql的元数据的问题.下面我们就修改一下字符编码
（1）修改表字段注解和表注解

alter table COLUMNS_V2 modify column COMMENT varchar(256) character set utf8;
alter table TABLE_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8;

（2）修改分区字段注解

alter table PARTITION_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8 ;
alter table PARTITION_KEYS modify column PKEY_COMMENT varchar(4000) character set utf8;

（3）修改索引注解

alter table INDEX_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8;

修改hive/conf/hive-site.xml配置文件

<property>
    <name>javax.jdo.option.ConnectionURL</name>
    <value>jdbc:mysql://IP:3306/db_name?createDatabaseIfNotExist=true&amp;useUnicode=true&amp;characterEncoding=UTF-8</value>
    <description>JDBC connect string for a JDBC metastore</description>
</property>

注(bin/hive启动报错)：Type The reference to entity “useUnicode” must end with the ‘;’ delimiter.

Hive查看表所有分区

show partitions tablename;

hadoop官网

官网：http://hadoop.apache.org/
版本控制：https://hadoop.apache.org/release/

hive操作报错

TaskAttempt 2 failed, info=[AttemptID:attempt_1610356017048_0077_1_00_000001_2 Timed out after 300 secs], TaskAttempt 3 failed, info=[Error: Error while running task ( failure ) : attempt_1610356017048_0077_1_00_000001_3:java.lang.RuntimeException: java.lang.OutOfMemoryError: Java heap space

操作成功解决步骤

/opt/module/hadoop-2.7.2/etc/hadoop/yarn-site.xml 添加如下

yarn.scheduler.minimum-allocation-mb
RM上每个容器请求的最小分配（MB）。低于此值的内存请求将引发InvalidResourceRequestException。

yarn.scheduler.maximum-allocation-mb
RM上每个容器请求的最大分配（MB）。高于此值的内存请求将引发InvalidResourceRequestException。

<property>
    <name>yarn.scheduler.maximum-allocation-mb</name>
    <value>10240</value>
</property>
<property>
    <name>yarn.scheduler.minimum-allocation-mb</name>
    <value>2048</value>
</property>

需分发到其他节点：xsync yarn-site.xml

/opt/module/hadoop-2.7.2/etc/hadoop/mapred-site.xml 添加如下

mapred.child.java.opts
Java选择任务进程。将插入以下符号（如果存在）：@taskid@替换为当前taskid。任何其他出现的'@'都将保持不变。例如，要启用详细gc日志记录到/tmp中以taskid命名的文件，并将堆最大值设置为千兆字节，请传递一个值：-Xmx1024m-详细：gc-Xloggc：/tmp/@任务ID@.gc的用法-Djava.library.path文件如果使用hadoop本机库，可能会导致程序不再运行。这些值应该改为在map/reduce JVM env中使用地图还原.map.env以及mapreduce.reduce.env文件配置设置。

<property>
	<name>mapred.child.java.opts</name>
	<value>-Xmx1024m</value>
</property>

需分发到其他节点：xsync mapred-site.xml
参考链接

hive操作别名

ParseException line 2:38 cannot recognize input near 'as' '"是否使用"' ',' in selection target

解决方法
hive别名不能使用双引号，可以使用单撇号``，具体见hive别名
参考链接

hive环境变量

vim /etc/profile

#HIVE
export HIVE_HOME=/opt/module/hive
export PATH=$PATH:$HIVE_HOME/bin

hive查看版本: hive --version
有hive的版本显现，安装成功

参考链接

hive服务端、客户端配置

参考链接

hive配置update、delete

注：这里把hive的服务端和客户端都放在同一台服务器上了

<!-- update、delete操作开启 -->
<property>
    <name>hive.support.concurrency</name>
    <value>true</value>
</property>
<property>
    <name>hive.enforce.bucketing</name>
    <value>true</value>
</property>
<property>
    <name>hive.exec.dynamic.partition.mode</name>
    <value>nonstrict</value>
</property>
<property>
    <name>hive.txn.manager</name>
    <value>org.apache.hadoop.hive.ql.lockmgr.DbTxnManager</value>
</property>
<property>
    <name>hive.compactor.initiator.on</name>
    <value>true</value>
</property>
<property>
    <name>hive.compactor.worker.threads</name>
    <value>1</value>
</property>
<!-- update、delete操作开启 -->

demo

drop table if exists tablename;
create table if not exists tablename (
    id bigint,
    sex tinyint  COMMENT '性别',
    name String COMMENT '姓名'
) COMMENT '用户表'
partitioned by (year string)
row format delimited fields terminated by '\t'
clustered by (id) into 2 buckets
stored as orc
TBLPROPERTIES('transactional'='true');

参考链接
参考链接

安装了spark后，hive操作界面进入报错

ls: cannot access /opt/module/spark-yarn/lib/spark-assembly-*.jar: No such file or directory

解决方法

从spark升级到2.0.0之后，原有的lib的整个大JAR包已经被分散的小JAR包的替代，所以肯定找不到spark-assembly的JAR包,修改一下配置就行了。hive脚本114行开始

将 /lib/spark-assembly-*.jar 替换成 /jars/spark-*.jar，就不会出现这样的问题
sparkAssemblyPath=`ls ${SPARK_HOME}/jars/spark-*.jar`

参考链接

Name node is in safe mode

bin/hadoop dfsadmin -safemode leave
//在bin下执行
//若配置环境变量，使用以下命令
hadoop dfsadmin -safemode leave

参考链接

Hive 正则表达式 判断字符串是否是纯数字(整数)

-- true，前面需要是两个\\，后面的$必须要带
 SELECT  '123456' rlike '^\\d+$';

HQL语句中包含中文，执行HQL时报错

报错

org.apache.calcite.runtime.CalciteException: Failed to encode '登录' in character set 'ISO-8859-1'

解决方法

set hive.cbo.enable=false;

参考链接