Hadoop开发--Hive-HQL

2019-11-29 本文已影响0人无剑_君

一、beeline 简介

HiveServer2 支持一个新的命令行Shell，称为Beeline，它是基于SQLLine CLI的JDBC客户端。
Beeline支持嵌入模式(embedded mode)和远程模式(remote mode)。在嵌入式模式下，运行嵌入式的Hive(类似Hive CLI)，而远程模式可以通过Thrift连接到独立的HiveServer2进程上。从Hive 0.14版本开始，Beeline使用HiveServer2工作时，它也会从HiveServer2输出日志信息到STDERR。

Beeline CLI 支持以下命令行参数:

参数
描述：
--autoCommit=[true/false] ---进入一个自动提交模式：beeline --autoCommit=true    
--autosave=[true/false]   ---进入一个自动保存模式：beeline --autosave=true    
--color=[true/false]    ---显示用到的颜色：beeline --color=true    
--delimiterForDSV= DELIMITER ---分隔值输出格式的分隔符。默认是“|”字符。    
--fastConnect=[true/false]  ---在连接时，跳过组建表等对象：beeline --fastConnect=false    
--force=[true/false]    ---是否强制运行脚本：beeline--force=true    
--headerInterval=ROWS   ---输出的表间隔格式，默认是100: beeline --headerInterval=50    
--help ---帮助  beeline --help    
--hiveconf property=value  ---设置属性值，以防被hive.conf.restricted.list重置：beeline --hiveconf prop1=value1     
--hivevar name=value   ---设置变量名：beeline --hivevar var1=value1    
--incremental=[true/false]  ---输出增量  
--isolation=LEVEL  ---设置事务隔离级别：beeline --isolation=TRANSACTION_SERIALIZABLE    
--maxColumnWidth=MAXCOLWIDTH ---设置字符串列的最大宽度：beeline --maxColumnWidth=25    
--maxWidth=MAXWIDTH ---设置截断数据的最大宽度：beeline --maxWidth=150    
--nullemptystring=[true/false]  ---打印空字符串：beeline --nullemptystring=false    
--numberFormat=[pattern]     ---数字使用DecimalFormat：beeline --numberFormat="#,###,##0.00"    
--outputformat=[table/vertical/csv/tsv/dsv/csv2/tsv2] ---输出格式：beeline --outputformat=tsv     
--showHeader=[true/false]   ---显示查询结果的列名：beeline --showHeader=false    
--showNestedErrs=[true/false] ---显示嵌套错误：beeline --showNestedErrs=true    
--showWarnings=[true/false] ---显示警告：beeline --showWarnings=true    
--silent=[true/false]  ---减少显示的信息量：beeline --silent=true    
--truncateTable=[true/false] ---是否在客户端截断表的列       
--verbose=[true/false]  ---显示详细错误信息和调试信息：beeline --verbose=true    
-d <driver class>  ---使用一个驱动类：beeline -d driver_class    
-e <query>  ---使用一个查询语句：beeline -e "query_string"    
-f <file>  ---加载一个文件：beeline -f filepath  多个文件用-e file1 -e file2  
-n <username>  ---加载一个用户名：beeline -n valid_user    
-p <password>  ---加载一个密码：beeline -p valid_password    
-u <database URL> ---加载一个JDBC连接字符串：beeline -u db_URL

参数说明：
-f 执行SQL文件后就直接退出Beeline客户端，一般编写需要执行的 HQL。
-i 执行SQL文件后进行Beeline客户端。一般为初始化的参数设置 hive 中的变量。

beeline 使用示例

# 以jdbc 的方式连接远程的 hiveserver2
root@master:~# beeline -u jdbc:hive2://192.168.71.130:10000
Connecting to jdbc:hive2://192.168.71.130:10000
Connected to: Apache Hive (version 2.3.6)
Driver: Hive JDBC (version 2.3.6)
Transaction isolation: TRANSACTION_REPEATABLE_READ
Beeline version 2.3.6 by Apache Hive
0: jdbc:hive2://192.168.71.130:10000> select * from users;
OK
+-----------+-------------+
| users.id  | users.name  |
+-----------+-------------+
| 1         | houjianjun  |
| 1         | houjianjun  |
+-----------+-------------+
2 rows selected (0.8 seconds)

常用命令
退出

beeline> !quit

连接

root@master:~# beeline
beeline> !connect jdbc:hive2://192.168.71.130:10000

帮助

!help –显示全部命令列表

查询

!verbose –显示查询追加的明细

二、HiveShell

执行Hive语句

hive>select name from xp;

执行HDFS文件操作
Hive的shell上使用dfs命令可以查看HDFS上的文件。

hive> dfs -ls /user/hive/warehouse;

HDFS上的“/user/hive/warehouse”目录是Hive的数据仓库目录，每个表对应一个以表明命名的目录，目录下存放导入的文件、分区目录、桶目录等数据文件。
Hive的查询日志默认保存在本地文件系统的“/tmp/<user.name>”目录下，
Hive的MapReduce执行计划保存在本地的“/tmp/<user.name>/hive”中。
这三个目录可以分别通过属性：

hive.metastore.metadb.dir：（HDFS上的）数据目录
hive.querylog.location：查询日志存放目录
hive.exec.scratcher：（HDFS上的）临时文件目录

设置和查看临时变量
设置只在当前会话有效，方便切换Hive的执行环境。

# 设置变量
hive> set fs.default.name=hdfs://192.168.71.130:9000;
# 读取变量
hive> set fs.default.name;
fs.default.name=hdfs://192.168.71.129:9000
hive>

三、 Hive sql语法

(一) DDL 操作

1. 数据类型

1)列类型

A. 字符类型：

字符串类型的数据类型可以使用单引号('')或双引号("")来指定。它包含两个数据类型：VARCHAR和CHAR。Hive遵循C-类型的转义字符。
各种CHAR数据类型：

数据类型	长度
VARCHAR	1 to 65355
CHAR	255
STRING

B. 日期类型：

日期

DATE值在年/月/日的格式形式描述 {{YYYY-MM-DD}}.

转换类型	结果
cast(date as date)	Same date value
cast(date as string)	The year/month/day represented by the Date is formatted as a string in the form ‘YYYY-MM-DD’.
cast(date as timestamp)	A timestamp value is generated corresponding to midnight of the year/month/day of the date value, based on the local timezone.
cast(string as date)	If the string is in the form ‘YYYY-MM-DD’, then a date value corresponding to that year/month/day is returned. If the string value does not match this formate, then NULL is returned.
cast(timestamp as date)	The year/month/day of the timestamp is determined, based on the local timezone, and returned as a date value.

时间戳(timestamp)

它支持传统的UNIX时间戳可选纳秒的精度。它支持的java.sql.Timestamp格式“YYYY-MM-DD HH:MM:SS.fffffffff”和格式“YYYY-MM-DD HH:MM:ss.ffffffffff”。

支持传统的UNIX时间戳和可选的纳秒精度。

支持的转化：
整数数字类型：以秒为单位解释为UNIX时间戳
浮点数值类型：以秒为单位解释为UNIX时间戳，带小数精度
字符串：符合JDBC java.sql.Timestamp格式“YYYY-MM-DD HH：MM：SS.fffffffff”（9位小数位精度）

C. 联合类型：

联合是异类的数据类型的集合。可以使用联合创建的一个实例。语法和示例如下：

UNIONTYPE<int, double, array<string>, struct<a:int,b:string>>
{0:1} 
{1:2.0} 
{2:["three","four"]} 
{3:{"a":5,"b":"five"}} 
{2:["six","seven"]} 
{3:{"a":8,"b":"eight"}} 
{0:9} 
{1:10.0}

D. 数值类型

整型

默认情况下，整数型为INT型，当数字大于INT型的范围时，会自动解释执行为BIGINT，或者使用以下后缀进行说明。

类型	后缀	例子
TINYINT	Y	100Y
SMALLINT	S	100S
BIGINT	L	100L

大于BIGINT的数值，需要使用BD后缀以及Decimal(38,0)来处理。
select CAST(18446744073709001000BD AS DECIMAL(38,0)) from my_table limit 1;

Hive的小数型是基于Java BigDecimal做的， BigDecimal在java中用于表示任意精度的小数类型。所有常规数字运算（例如+， - ，*，/）和相关的UDFs（例如Floor，Ceil，Round等等）都使用和支持Decimal。你可以将Decimal和其他数值型互相转换，且Decimal支持科学计数法和非科学计数法。因此，无论您的数据集是否包含如4.004E + 3（科学记数法）或4004（非科学记数法）或两者的组合的数据，可以使用Decimal。 �从Hive 0.13开始，用户可以使用DECIMAL(precision, scale) 语法在创建表时来定义Decimal数据类型的precision和scale。如果未指定precision，则默认为10。如果未指定scale，它将默认为0（无小数位）。

E. 使用示例

# 创建员工表：
0: jdbc:hive2://192.168.71.130:10000> create table employees(name string,sex char(10),age int,salary float,address varchar(50));
OK
No rows affected (0.682 seconds)

# 创建部门表：
0: jdbc:hive2://192.168.71.130:10000> create table deptarment(id int,deptname string,joindate date,bonus decimal(10,2));
OK
No rows affected (0.182 seconds)

常见问题：
1.使用beeline -f 建表SQL comment乱码问题
登陆Mysql数据库切换到Hive库：

mysql> use hive;

修改COLUMNS_V2表：

mysql> alter table COLUMNS_V2 modify column COMMENT varchar(256) character set utf8;

修改TABLE_PARAMS表：

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

修改分区表：

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

解决方法：
1：修改需要执行的文件编码格式
在Vim中直接进行转换文件编码,比如将一个文件转换成utf-8格式

:set fileencoding=utf-8

注意：执行以上操作之后，文件中的中文可能会显示不太正常，请重新编辑一下文件
2： iconv 转换，iconv的命令格式如下：

iconv -f encoding -t encoding inputfile

比如将一个UTF-8 编码的文件转换成GBK编码：

iconv -f GBK -t UTF-8 file1 -o file2

拒绝连接
解决：
在hadoop>etc>hadoop>core-site.xml 中添加如下部分，重启服务即可

<property>
 <name>hadoop.proxyuser.root.hosts</name>
  <value>*</value>
 </property>
 <property>
 <name>hadoop.proxyuser.root.groups</name>
  <value>*</value>
</property>