dataflow论文阅读笔记 Polysh的安装使用 SnappyData排序函数比较 Squirrel-sql客户端连接SnappyData手册 在虚拟机里显示Hello World spark学习博客推荐 SnappyData学习博客和官网文章 Docker常用命令 MyBatis支持的OGNL语法 mysql性能优化 mysql性能优化-优化Sql语句 java各版本新特性 mac上命令行操作 explain输出格式 从文件中读取zk配置——ZooKeeper编程技能(1) git进阶经验-从项目中删除移除的目录 Mongodb 学习之shell命令操作(3) mysql命令 git进阶经验-从多模块项目中分理子模块 从零学hadoop-搭建基础(单点)的Hdfs环境 ZooKeeper集群操作脚本 Vue安装使用 2 初学JVM之问答式记住类加载机制 2 初学JVM之问答式记住虚拟机性能监控与故障处理工具 2 初学JVM之问答式记住垃圾收集器 log4j2 按天生成日志文件 1 初学JVM之问答式记住java自动内存管理 MapReduce学习心得之MapReduce初识 log4j2 日志发送到kafka配置实战 log4j2 日志配置实战 Mongodb 学习之shell命令操作(二) Mongodb 学习之linux版本安装(一) Dubbo的初级使用 ServiceLoader内部实现分析 ServiceLoader 初级应用 log4j日志发送邮件配置实战 红黑树笔记 IDEA首次使用之前的配置 java源码学习之Enum java源码学习之String 自定义Spring tag标签 编写一键发布脚本 记一次Spring Scheduler莫名不执行的一次堆栈分析 kafka的基本操作 nginx 5:Nginx内部变量 nginx 4:Nginx日志管理 提高hadoop配置效率的shell脚本 Hive编程指南之一 Hive的安装 Ambari服务器安装 Ambari服务器管理集群 HBase分布式安装 windows下Eclipse远程调试运行MR程序 基于MapReduce新的API的编程Demo-wordCount window下Eclipse远程只读HDFS上的文件 YARN上节点标签 编写第一个MapReduce的wordcount程序 NodeManager的重启 搭建JStorm集群 YARN上的web应用代理 YARN上的ResourceManager的高可用方案 配置vmware中的虚拟机使用宿主机的共享网络 YARN架构简述 HDFS 架构 Spring的统一异常处理机制 Tomcat 配置服务 HDFS的viewfs指南 HDFS的Federation之路 HDFS基于QJM的HA之路 nginx 3:Nginx反向代理 mybatis操作主体流程 1.正则表达式学习-基础篇 log4j日志配置详解 mysql的时间函数 nginx 2:Nginx模块配置理论及实战 HashMap相关解析和测试文章 工作一年后的面试 用私有构造器或枚举类型强化Singleton属性 java中比较重要的图 mybatis处理枚举类 mybatis集成进spring Spring比较重要的几个截图 21.hadoop-2.7.2官网文档翻译-使用NFS的HDFS高可用性 20.hadoop-2.7.2官网文档翻译-使用仲裁日志管理器的HDFS高可用性 markdown在jekyll中支持的一些操作 Spring项目中配置sl4j和log4j的日志配置 19.hadoop-2.7.2官网文档翻译-HDFS命令指南 Spring的profile机制介绍 mybatis-generator反向生成 18.hadoop-2.7.2官网文档翻译-HDFS用户指南 17.hadoop-2.7.2官网文档翻译-实现Hadoop中Dapper-like追踪 16.hadoop-2.7.2官网文档翻译-Hadoop的KMS(key 管理服务器)-文档集 15.hadoop-2.7.2官网文档翻译-Hadoop的http web认证 14.hadoop-2.7.2官网文档翻译-服务级别的授权指南 13.hadoop-2.7.2官网文档翻译-安全模式中的Hadoop 09.hadoop-2.7.2官网文档翻译-Hadoop命令行微型集群 12.hadoop-2.7.2官网文档翻译-机架感知 11.hadoop-2.7.2官网文档翻译-代理用户-超级用户对其他用户的代表 10.hadoop-2.7.2官网文档翻译-原生库指南 08.hadoop-2.7.2官网文档翻译-文件系统规范 07.hadoop-2.7.2官网文档翻译-Hadoop接口类别 (转)浅析 Decorator 模式,兼谈 CDI Decorator 注解 06.hadoop-2.7.2官网文档翻译-Hadoop的兼容性 05.hadoop-2.7.2官网文档翻译-文件系统命令 04.hadoop-2.7.2官网文档翻译-Hadoop命令指南 03.hadoop-2.7.2官网文档翻译-集群安装 02.hadoop-2.7.2官网文档翻译-单节点集群安装 01.hadoop-2.7.2官网文档翻译-概述 Http 协议相应状态码大全及常用状态码 IDEA快捷键 JDBC Type与Java Type redis 12:redis 操作集合 mybatis-generator错误集合 redis 11:redis 错误集合 nginx 1:nginx的安装 redis 10:redis cluster命令操作 redis 9:redis实例集群安装 java设计模式 hadoop集群学习笔记(1) Apache Shiro 简介 vim编辑神器的进阶命令 Eclipse配置 Eclipse快捷键 Linux 测试题 Linux脚本学习(1) Linux启动简要过程 Centos7上安装Mysql hadoop集群学习笔记(1) (转)分布式发布订阅消息系统 Kafka 架构设计 maven 命令 Kafka集群安装 Kafka初步使用 redis 8:redis server 和 scripting命令操作 redis 7:redis transaction 和 connection命令操作 redis 6:redis hash 命令操作 redis 5:redis sorted_set 命令操作 搭建本地Jekyll+Markdown+Github的开发环境 Spring源码阅读笔记(2) redis 4:redis set命令操作 Spring添加任务调度配置 redis 3:Redis list命令操作 redis 2:redis 一般命令操作 redis 1:redis单机安装笔记 redis 0:redis配置属性描述 Spring源码阅读笔记(1) spark 错误集锦 spark集群安装 Linux 基本命令操作 Hadoop错误信息处理 Hadoop代码拾忆 从零开始搭建spring-springmvc-mybatis-mysql和dubbo项目 java知识点札记 java排错 Google Java Style 中文版 git进阶经验 github使用经验 MongoDB用户角色授权与AUTH启用 MongoDB 命令 MongoDB 特定规范 Spring MVC实现跳转的几种方式 史上最全最强SpringMVC详细示例实战教程 Spring 零星笔记 js中(function(){…})()立即执行函数写法理解 如何解决跨域问题 创建ajax简单过程 前端定位 设置MYSQL允许通过IP访问 mybatis异常 :元素内容必须由格式正确的字符数据或标记组成 如何为 WordPress 绑定多个域名的方法s WordPress工作原理之程序文件执行顺序(传说中的架构源码分析) Spring源码导入Eclipse中 基于PHPnow搭建Eclipse开发环境 解决wordpress首页文章内容截断处理的几种方法 ZooKeeper理论知识 ZooKeeper集群安装配置 Git常用命令速查表 Linux 4:磁盘与文件系统管理 Linux 3:文件与目录管理 Linux 2:文件权限与目录配置 Markdown输入LaTeX数学公式
从零学hadoop-搭建基础(单点)的Hdfs环境 MapReduce学习心得之MapReduce初识 Ambari服务器安装 Ambari服务器管理集群 windows下Eclipse远程调试运行MR程序 基于MapReduce新的API的编程Demo-wordCount window下Eclipse远程只读HDFS上的文件 YARN上节点标签 编写第一个MapReduce的wordcount程序 NodeManager的重启 YARN上的web应用代理 YARN上的ResourceManager的高可用方案 YARN架构简述 HDFS 架构 HDFS的viewfs指南 HDFS的Federation之路 HDFS基于QJM的HA之路 21.hadoop-2.7.2官网文档翻译-使用NFS的HDFS高可用性 20.hadoop-2.7.2官网文档翻译-使用仲裁日志管理器的HDFS高可用性 19.hadoop-2.7.2官网文档翻译-HDFS命令指南 18.hadoop-2.7.2官网文档翻译-HDFS用户指南 17.hadoop-2.7.2官网文档翻译-实现Hadoop中Dapper-like追踪 16.hadoop-2.7.2官网文档翻译-Hadoop的KMS(key 管理服务器)-文档集 15.hadoop-2.7.2官网文档翻译-Hadoop的http web认证 14.hadoop-2.7.2官网文档翻译-服务级别的授权指南 13.hadoop-2.7.2官网文档翻译-安全模式中的Hadoop 09.hadoop-2.7.2官网文档翻译-Hadoop命令行微型集群 12.hadoop-2.7.2官网文档翻译-机架感知 11.hadoop-2.7.2官网文档翻译-代理用户-超级用户对其他用户的代表 10.hadoop-2.7.2官网文档翻译-原生库指南 08.hadoop-2.7.2官网文档翻译-文件系统规范 07.hadoop-2.7.2官网文档翻译-Hadoop接口类别 06.hadoop-2.7.2官网文档翻译-Hadoop的兼容性 05.hadoop-2.7.2官网文档翻译-文件系统命令 04.hadoop-2.7.2官网文档翻译-Hadoop命令指南 03.hadoop-2.7.2官网文档翻译-集群安装 02.hadoop-2.7.2官网文档翻译-单节点集群安装 01.hadoop-2.7.2官网文档翻译-概述 hadoop集群学习笔记(1) hadoop集群学习笔记(1) Hadoop错误信息处理 Hadoop代码拾忆

Hive编程指南之一

2016年12月20日
摘要:hive使用sql语法,简化学习坡度。

Hive QL详解

表的操作语句,数据类型不区分大小写,但SerDe和属性名区分大小写,注释是单引号的字符串

DDL操作

1) 创建表

语法:

Create [external] table [if not exists]  table_name
[(
Col_name data_type [comment col_comment]
)] [comment table_comment];
  1. 如果需要存储为纯文本文件,使用STORED AS TEXTFILE
  2. 如果数据需要压缩,使用STORED AS SEQUENCEFILE
  3. INPUTFORMATOUTPUTFORMAT定义一个与inputformat和outputformat类相对应的名字作为字符串
  4. Hive支持带有分区(partition)的表,创建的时候使用PARTITIONED BY语句,可有多个分区,每个分区一个目录。
  5. 表和分区都能对某列CLUSTERED BY操作,将若干个列放在一个桶中。
  6. 可以利用SORT BY列存储数据,提高查询性能

示例:

重点:

  1. 不区分大小写
  2. 每次创建表都会在hive库的TBLS表中创建一条表的描述,并在HDFS的/user/hive/warehouse下创建对应表名的目录。
  • 例1:创建普通表
CREATE TABLE page2 (
	pageId INT,
	pageUrl string,
	userId BIGINT,
	reg_url string,
	ip string COMMENT 'id address'
) COMMENT 'this is the page1 table';
  • 例2:添加表分区,并用制表符区分同一行的不同字段
CREATE TABLE page3 (
	pageId INT,
	pageUrl string,
	userId BIGINT,
	reg_url string,
	ip string COMMENT 'id address'
) COMMENT 'this is the page1 table' 
partitioned BY (dt string, country string) 
ROW format delimited 
FIELDS TERMINATED BY '\001' 
stored AS sequencefile;
  • 例3:添加聚类存储,将列按照userid进行分区并划分到不同的桶中, 按照pageid值得大小进行排序存储。这样允许用户通过userid属性高效的对集群列进行采样。
CREATE TABLE page7 (
	pageId INT,
	pageUrl string,
	userid BIGINT,
	referrer_url string,
	ip string COMMENT "IP address of the user"
) COMMENT 'this is the page1 table' 
PARTITIONED BY (dt STRING, country STRING) 
CLUSTERED BY (userid) SORTED BY (pageid) INTO 32 BUCKETS 
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\001' 
COLLECTION ITEMS TERMINATED BY '\002' 
MAP KEYS TERMINATED BY '\003' 
STORED AS SEQUENCEFILE;
  • 例4:通过LOCATION,指定存储路径

指定位置为:hdfs://h2m1:8220/user/hive/warehouse/external_cus,会自动在指定为值创建指定目录external_cus

外部表与其他表都存储在TBLS表中,字段信息存储在COLUMNS_V2表中,只是TBL_Type字段设置为EXTERNAL_TABLE,其他为MANAGED_TABLE

CREATE external TABLE page_view (
	viewTime INT,
	userId BIGINT,
	page_url string,
	ref_url string,
	ip string COMMENT "ip addr",
	country string
) COMMENT "this is the staging page view table" 
ROW format delimited 
FIELDS TERMINATED BY '\054' 
stored AS textfile 
location 'hdfs://h2m1:8220/user/hive/warehouse/external_cus';

2) 删除表

语法:Drop table table_name;

用于删除表的元数据和数据,如果配置trash,数据删除到Trash/Current目录,元数据完全丢失。 当删除external定义的表时,表中的数据不会从文件系统中删除

示例:

  • 例1:drop table p7;

3) 修改表

示例:

  • 例1:重命名表

alter table page7 rename to p7;

  • 例2:改变列名、类型、位置、注释

将列名userid改为uid,数据类型为string

alter table page1 change column pageid pId string comment "pageId";

将列名uid改为userid,数据类型为string,并置于ip后

alter table page1 change column uid userid string comment 'user id' after ip ;

  • 例3:增加\更新列

为表page1增加name和remark列,类型都为string。

alter table page2 add columns (name string comment "user name" ,remark string comment "remark");

使用指定的列修改表,其他没有指定的列就会丢失了。

alter table page2 replace columns (name string comment "user name" ,other string comment "remark");

  • 例5:改变表文件格式和组织(只修改表的物理存储属性)
Alter table table_name set FILEFORMAT file_format
Alter table table_name CLUSTERED BY (col_name,col_name,…)
[SORTED BY (col_name,…)] INTO num_buckets BUCKETS

这些命令只能修改hvie的元数据,不能重组或格式化现有的数据,用户应根据实际数据的分布设置符合元数据的定义。

4) 表分区操作语句

查询时会对整个表扫描,为了只对关心的部分数据进行扫描,hive引入分区、相当于简单的索引功能 创建时指定模式、字段指定虚拟列,表中不存在、可指定多级结构,相当于对目录嵌套、在创建完成后使用之前还需通过alter table 语句添加具体的分区目录才能使用

Hive表分区的命令主要包括创建分区、增加分区和删除分区

  • 例1:增加分区
    Alter table table_name add partition_spec [LOCATION ‘ location1’ ] partition_spec [LOCATION ‘ location1’ ]
    Partition_spec:
    PARTITION (partition_col=partition_col_value, partition_col=partition_col_value,…) 分区名是字符串时加引号。
ALTER TABLE page3 ADD PARTITION (
	dt = '2016-12-22',
	country = 'cn'
) location 'viewfs://hadoop-cluster-jingzz/user/hive/warehouse/partition';
  • 例2:删除分区

Alter table page_view drop PATITION (de=’2015-2-4’,country=’us’) PS:1.当没有声明表模式的时候不能为表指定具体的分区 2.分区名不能与表属性名重复

5) 创建、删除视图

Create view [if not exists] view_name [(col_name [comment col_comment],…)] As select

视图是只读的,不能用于LOAD\INSERT\ALTER 的目标 视图可能包含order by或limit子句,如果一个引用了视图的查询也包含了这些子句,那么在执行这些子句时,首先查看视图子句,然后返回结果按视图中语句执行 删除视图: Drop view view_name; 6) 创建、删除函数 Create temporary function function_name; 该语句创建了一个由类名实现的函数,在hive中可以持续使用该函数查询,也可以使用Hive类路径中的任何类。用户可以执行add files 语句将函数类添加到类路径中 Drop temporary function function_name; 7) 展示描述语句 1) 显示表 Show tables identifier_with_wildcards 2) 显示分区 Show partitions table_name; 3) 显示表、分区扩展 Show table external [in | from databases_name] like identifier_with_wildcards [partition(partition_desc)] 4) 显示函数 Show functions; 5) 描述表、列 Describe [extended] table_name [dot col_name] Describe [extended] table_name [dot col_name([DOT field_name] | [DOT ‘$elem$’] | [DOT $key$’]|[DOT ‘$value$’])*] 通常只用于调试,不用于平时使用中。 [DOT ‘$elem$’] 数组元素 [DOT $key$’]  图的主键 [DOT ‘$value$’])  图的属性

6) 描述分区 Describe [extended] table_name partition_spec; 数据操作(DML) 1) 向数据表中加载文件 不会对数据进行任何转换,Load只是将数据复制或移动到Hive表对应的位置 语法: Load data [LOCAL] INPATH ‘filepath’ [OVERWRITE] INTO TABLE table_name [PARTITION (partcol1=val1,part2=val2,…)] PS:1.指定LOCAL,load命令会查找本地文件系统的filepath, 2.filepath:支持相对路径、绝对路径、完整URI,目标可以是表或分区; 如果表包含分区,必须指定每个分区名; Filepath可以引用文件或目录。 3.没有指定LOCAL,filepath 指向URI,Hive会直接使用URI,没有schema或authority,Hive会使用hadoop配置文件中定义的schema或authority,fs.default.name  指定nameNode的URI; 如果是相对路径,Hive会相对/user/解释。 4.使用OVERWRITE,目标表中的内容会被删除,并将filepath指向的文件\目录中的内容添加到表(或分区)中,如果目标表(或分区)中有文件,且与filepath中文件名冲突,现有文件会被替换。 ‘’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’ 2) 将查询结果插入Hive表中 语法: INSERT OVERWRITE TABLE table_name [PARTITION (partcol1=val1,partcol2=val2,…)] Select_statement1 from from_statement Hive extension (multiple inserts) FROM from_statement INSERT OVERWRITE TABLE table_name1 [PARTITION (partcol1=val1,partcol2=val2,…)] select_statement1 [INSERT OVERWRITE TABLE table_name2 [PARTITION (partcol1=val1,partcol2=val2,…)]] Hive extension (dynamic partition inserts): INSERT OVERWRITE TABLE tableName PARTITION (partcol1=[val1], partcol2=[val2],…) select_statement1 from from_statement 插入可以针对一个表或分区操作,如果对表进行了划分,在插入时要指定划分列的属性值确定分区。每个select 语句的结果会被写入选择的表或分区中,OVERWRITE 关键字会强制将输出结果写入,输出格式和序列化方式由表的元数据决定。在Hive进行多表插入可以减少数据扫描的次数。 3) 将查询的结果写入到文件系统 INSERT OVERWRITE [LOCAL] DIRECTORY directory1 SELECT … FROM …. Hive extension (multiple inserts) FROM from_statement INSERT OVERWRITE [LOCAL] DIRECTORY directory1 select_statement1 [INSERT OVERWRITE [LOCAL] DIRECTORY directory1 select_statement2]… 目录可以是完整的URI,如果使用LOCAL,Hive会将数据写入到本地文件系统中。 PS:数据写入文件系统时会进行文本序列化,并且每列^A区分,换行表示一行数据结束。如果任一列不是原始类型,这些列会被序列化为json格式 Sql 操作(标准的select语句) 语法: SELECT [ALL | DISTINCT] select_expr,select_expr,… FROM table_reference [WHERE where_condition] [GROUP BY col_list] [ CLUSTER BY col_list | [DISTRIBUTE BY col_list] [SORT BY col_list] ] [LIMIT number]

PS:

  1. table_reference 指明查询的输入,可以是表、视图或子查询 select * from t1
  2. WHERE Where_condition 是一个布尔表达式。跟sql中的格式一样
  3. ALL和DISTINCT ALL 和DISTINCT 可以定义重复的行是否返回,默认为ALL。
  4. LIMIT LIMIT 可以控制输出的记录数,随机选取检索结果中相应数目输出: Select * from t1 limit 5 输出最多5条记录
  5. 使用正则表达式 Select ‘(ds|hr)?+.+’ from sales
  6. 基于区分的查询 如果一个表是使用PARTITIONED BY 语句生成的,查询可以对输入进行’剪枝’,只对表的相关部分扫描。Hive现只where中指定分区剪枝。 例1:如果表page_view按照date列的值进行分区,查询检索日期 2010-03-01 — 2010-03-31。 Select page_view.* from page_view where page_view.date >= ‘2010-03-01’ and page_view.date <= ‘2010-03-31’
  7. HAVING Hive 不支持having 语句,但可以使用子查询实现
  8. Group by和JOIN的作用与SQL相同。

Hive网络(WEB UI)接口 (1) 分离查询的执行 命令行下,要执行多个查询就要打开多个终端,通过网络接口,可以同时执行多个查询,网络接口可以在网络服务器上管理会话(session) (2) 远程执行Hive WEB UI,需要在Hive服务端配置文件中配置IP地址和端口号

(3) 启动hive网络接口服务 #hive –service hwi
(4) 通过浏览器访问:http://masterIP:port/hwi 本例中HTTP://192.168.100.11:9999/hwi 单击Browse Schema 可以查看当前hive的数据库 Hive查询:首先创建一个session会话,可通过list session连接查看所有的session。 当hive重启后,session信息将全部丢失,会话与认证关联。 在list session处点击action项进行查询操作。