hadoop记录篇7-hive常用sql统计
一 。参考文档
参考 https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL
二。 数据库操作
》》创建数据库
环境参考 http://blog.csdn.net/liaomin416100569/article/details/78395266hive命令进入hive客户端
hive> create database hello
> ;
OK
Time taken: 2.855 seconds
hive> show databases;
OK
default
hello
Time taken: 0.921 seconds, Fetched: 2 row(s)创建的数据库文件位于 /user/hive/warehouse/数据库名.db是个文件夹
可以自己制定存储的位置 (必须带上hdfs协议 后面可以不指定hdfs的ip和端口 直接指定路径 下面的数据库就会存储在/mydata/world.db下)
hive> create database world comment ‘world’ location 'hdfs:/mydata/world.db';
OK
Time taken: 0.275 seconds》》删除数据库
Drop database 数据库名 cascade如果数据库下有表 不能直接删除必须带上cascade
》》使用数据库
Use 数据库名称其他数据库参考（https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL）
三。表操作
》》数据类型
支持mysql基础所有数据类型（int float decimal date varchar boolean等）
同时支持数组（array_type）map（map_type） 结构(struct_type)连接( union_type)类型
》》文件格式
Hive中表数据是存储在当前数据库hdfs文件夹的同表名文件里 默认是文本文件(TEXTFILE) 行存储
其他文件类型
SEQUENCEFILE 一系列的压缩文件中（二进制文件） 行存储
ORC 支持ACID事务 支持CBO优化器
PARQUET 列式存储 类似java序列化存储 直接对象存储
AVRO AVRO格式存储 (json格式)
RCFILE 结合行和列存储的优点设计》》表格式
内表表示元数据和文件数据被hive内部经常管理 删除内表数据 所有数据都会被删除 默认表都是内表
外表 表示文件数据被外部管理删除外表 外表数据不会被删除 比如多个表引用同一份数据时适用于使用外表
》》DDL
Create [EXTERNAL] table 表名 (列名 类型,。。。。。)
Partitioned by (列名 类型。。。。) --值不同拆成不同目录
CLUSTERED BY （列名 类型。。。。）into 桶个数 --值不同拆分不同文件 has%桶个举例(注意语法中 关键字之间只能使用空格隔开如果有tab键 都无法执行)
CREATE TABLE page_view(
viewTime INT,
userid BIGINT,
page_url STRING,
referrer_url STRING,
ip STRING
)
PARTITIONED BY(dt STRING, country STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
STORED AS SEQUENCEFILE;一般情况 都是大量的数据从文件中导入hive中
假设 /soft目录下 存在 文件a.txt内容
（这些数据 一行数据换行隔开列用空格隔开和表定义结构一致）
2015-12-13 11:56:20 1 www.baidu.com www.qq.com 192.168.99.0
2015-12-13 10:56:20 1 www.baidu.com www.qq.com 192.168.99.1
2015-12-13 9:56:20 1 www.baidu.com www.qq.com 192.168.99.2
2015-12-13 11:56:20 1 www.baidu.com www.qq.com 192.168.99.3
2015-12-13 44:56:20 1 www.baidu.com www.qq.com 192.168.99.4Hive下使用命令
LOAD DATA LOCAL INPATH '/soft/a.txt' INTO TABLE page_view PARTITION(dt='2015-12-13', country='CHINA');LOAD DATA INPATH '/soft/a.txt' INTO TABLE page_view PARTITION(dt='2015-12-13', country='CHINA');表示从本机机器上加载 a.txt 到表 page_view 添加到date='2015-12-13', country='CHINA'分区中
查询结果：
Time taken: 2.253 seconds
hive> select * from page_VIEW;
OK
2015-12-13 11:56:20 1 www.baidu.com www.qq.com 192.168.99.0 2015-12-13 CHINA
2015-12-13 10:56:20 1 www.baidu.com www.qq.com 192.168.99.1 2015-12-13 CHINA
2015-12-13 9:56:20 1 www.baidu.com www.qq.com 192.168.99.2 2015-12-13 CHINA
2015-12-13 11:56:20 1 www.baidu.com www.qq.com 192.168.99.3 2015-12-13 CHINA
2015-12-13 44:56:20 1 www.baidu.com www.qq.com 192.168.99.4 2015-12-13 CHINA
Time taken: 1.109 seconds, Fetched: 5 row(s)查看文件系统
比如arry_type在创建时 可以指定array_type之间的分割父 map_type也可以指定map插入数据时的键值对分隔符
其他ddl参考文档
》》DML
支持标准的insert语句 （启动mapreduce）
上面的
LOAD DATA LOCAL INPATH '/soft/a.txt' INTO TABLE page_view PARTITION(dt='2015-12-13', country='CHINA');就是dml中用于加载本地文件数据到文件系统的例子
复制表数据到另一张表（目标表必须存在）
INSERT OVERWRITE TABLE page_view1 SELECT a.* FROM page_view a;Create table page_view1 as select * from page_view(不存在表创建复制)清空表
Truncate table 表名Hive支持原生态sql查询，关联查询， 聚合函数，分组 排序等具体参考官方文档
在官网dml语句中 有通过正则表达式匹配httd或者nginx日志的数据导入 参考
https://cwiki.apache.org/confluence/display/Hive/GettingStarted#GettingStarted-DMLOperations 的Apache Weblog Data 章节四。索引
参考https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Indexing
五 。函数
具体内置参考https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Indexing
自定义函数
添加maven依赖
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-cli</artifactId>
<version>2.0.0</version>
</dependency>添加类 继承UDF类
public final class MyLength extends UDF {
public int evaluate(final Text s) {
if (s == null) { return 0; }
return s.toString().length();
}
}将该类导出jar包 存储在文件系统中 本地或者hdfs
关于hive函数到java方法有多种方式
方式1：
HIVE_PATH=jar包路径
create temporary function my_length as 'cn.et.MyLength';
Select my_length(page_url) from page_views方式2：
添加jar包到hive环境中
hive> add jar jar包路径;（删除jar delete jar jar路径）
hive> list jars;
create function my_length as 'cn.et.MyLength';Hive本身支持hplsql文档地址http://www.hplsql.org/doc
六。hive jdbc连接操作hiveserver2
添加maven项目 添加依赖
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-cli</artifactId>
<version>2.2.0</version>
</dependency>
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-jdbc</artifactId>
<version>2.2.0</version>
</dependency>添加依赖时一定要注意这个hive-jdbc和hive-cli版本的区别 hiveserver2的版本是什么版本 jdbc依赖就用什么版本否则会报
Caused by: java.lang.IllegalArgumentException: hive configuration hive.server2.thrift.resultset.default.fetch.size does not exists.
at org.apache.hadoop.hive.ql.processors.SetProcessor.setConf(SetProcessor.java:230)
at org.apache.hadoop.hive.ql.processors.SetProcessor.setVariable(SetProcessor.java:171)
at org.apache.hive.service.cli.session.HiveSessionImpl.configureSession(HiveSessionImpl.java:258)
... 27 more很奇葩的错误 调试jdbc发现默认是 10000 最后hiveserver2日志里也报这个错 说明客户端连接时没有告诉hiveserver2这个参数
后面查看了hive中 hive-common的版本
这里是 2.2.0 之前我用的2.3.0所以才报这个错
hive服务 启动 hiveserver2
添加jdbc的测试类
package cn.et;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.apache.hadoop.hive.ql.processors.SetProcessor;
public class TestJdbc {
public static void main(String[] args) throws Exception {
String driverName = "org.apache.hive.jdbc.HiveDriver";
try {
Class.forName(driverName);
} catch (ClassNotFoundException e) {
e.printStackTrace();
System.exit(1);
}
//;fetchSize=10000;hive.server2.thrift.resultset.default.fetch.size=10
Connection con = DriverManager.getConnection("jdbc:hive2://192.168.58.147:10000/default", "root", "");
Statement stmt = con.createStatement();
String tableName = "testtable";
stmt.execute("drop table if exists " + tableName);
stmt.execute("create table " + tableName + " (key int, value string)");
System.out.println("Create table success!");
// show tables
String sql = "show tables '" + tableName + "'";
System.out.println("Running: " + sql);
ResultSet res = stmt.executeQuery(sql);
if (res.next()) {
System.out.println(res.getString(1));
}
}
}
解决这个错误 把jdbc的api都看完了 突然有了个自己实现驱动的想法
浙公网安备 33010602011771号