hive substr截取尾字符串 hive中substr用法

转载

mob64ca140530fb 2023-09-04 08:54:48

文章标签 hive substr截取尾字符串 Hive hive 数据 文章分类 Hive 大数据

1. Hive简介

1.1 Hive数据仓库软件提供对存储在分布式中的大型数据集的查询和管理，它本身是建立在Apache Hadoop之上，主要提供以下功能：

它提供了一系列的工具，可用来对数据进行提取/转化/加载（ETL）；
是一种可以存储、查询和分析存储在HDFS（或者HBase）中的大规模数据的机制；
查询是通过MapReduce来完成的，但并不是所有的查询都要通过MapReduce完成；

因此，Hive是建立Hadoop文件系统之上的数据仓库架构，并对储存在HDFS上的数据进行管理。

1.2 MapReduce原理

mapreduce分为3个阶段

1. input/map/partition/sort/spill

2.mapper端merge

3.reducer端merge/reduce/output

mapper端逻辑：

1. 将key/value/partition写入到内存缓冲区中；

2. 当缓冲区使用量到达一定阙值，将其spill到disk上，spill前，需进行排序操作

3. 排序时先按照partition排序，再按照key排序，默认排序算法是快速排序

* 在内存中进行排序时，数据本身不用移动，仅对索引进行排序

hive substr截取尾字符串 hive中substr用法_hive substr截取尾字符串

map的merge操作：对生成的spill文件进行归并排序最终形成一个大文件

* 在进行归并时，不是一次性归并，而是归并成中间件后再次归并（顺序已定）

hive substr截取尾字符串 hive中substr用法_hive_02

Reducer端的merge和reduce

1. 当有新的MapTask事件完成，拷贝线程从指定的机器上拷贝数据（数据量较小，写入内存；数据量大，写入硬盘）；

2. 不同机器文件归并成一个文件，在拷贝文件过程中也会进行归并操作；

2. Hive入门

2.1 基本操作

CREATE TABLE Orson (foo INT, bar STRING); #创建表
CRATE TABLE new_table LIKE records; #创建一个新表，结构与其他一样
create table logs(ts bigint,line string) partitioned by (dt String,country String); #创建分区表
load data local inpath '/home/hadoop/input/hive/partitions/file1' 
	into table logs partition (dt='2001-01-01',country='GB'); #加载分区表数据
show partitions logs; #展示表中有多少分区
SHOW TABLES; #展示所有表
SHOW TABLES '.*s'; #展示所有表
DESCRIBE invites; #显示表的结构信息
ALTER TABLE source RENAME TO target; #更新表的名称
ALTER TABLE invites ADD COLUMNS (new_col2 INT COMMENT 'a comment'); #添加新一列
DROP TABLE records; #删除表
LOAD DATA LOCAL INPATH '/home/hadoop/input/ncdc/micro-tab/sample.txt' 
	OVERWRITE INTO TABLE records; #从本地文件加载数据
show functions; #显示所有函数
describe function substr; #查看函数用法
select col1[0],col2['b'],col3.c from complex; #查看数组、map、结构
SELECT sales.*, things.* FROM sales JOIN things ON (sales.id = things.id); #内连接
Explain SELECT sales.*, things.* FROM sales JOIN things ON (sales.id = things.id); #查看hive为某个查询使用多少个MapReduce作业
SELECT sales.*, things.* FROM sales LEFT OUTER JOIN things ON (sales.id = things.id);
SELECT sales.*, things.* FROM sales RIGHT OUTER JOIN things ON (sales.id = things.id);
SELECT sales.*, things.* FROM sales FULL OUTER JOIN things ON (sales.id = things.id); #外连接
SELECT * FROM things LEFT SEMI JOIN sales ON (sales.id = things.id); #in查询：Hive不支持，但可以使用LEFT SEMI JOIN
SELECT /*+ MAPJOIN(things) */ sales.*, things.* 
	FROM sales JOIN things ON (sales.id = things.id); #Map连接：Hive可以把较小的表放入每个Mapper的内存来执行连接操作
INSERT OVERWRITE TABLE stations_by_year SELECT year, COUNT(DISTINCT station) GROUP BY year #新表预先存在
CREATE TABLE target AS SELECT col1,col2 FROM source; #新表表预先不存在
CREATE VIEW valid_records AS SELECT * FROM records2 WHERE temperature !=9999; #创建视图
DESCRIBE EXTENDED valid_records; #查看视图详细信息

2.2 HQL语法详解

2.2.1 DDL

1.创建新表

内部表的删除只会删除HDFS的数据而不会对表本身造成影响，hive默认创建的是内部表

1.1 创建外部表

CREATE EXTERNAL TABLE page_view(viewTime INT, userid BIGINT,
     page_url STRING, referrer_url STRING,
     ip STRING COMMENT 'IP Address of the User',
     country STRING COMMENT 'country of origination')
COMMENT 'This is the staging page view table'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\054'
STORED AS TEXTFILE
LOCATION '<hdfs_location>';

1.2 创建分区表

CREATE TABLE par_table(viewTime INT, userid BIGINT,
     page_url STRING, referrer_url STRING,
     ip STRING COMMENT 'IP Address of the User')
COMMENT 'This is the page view table'
PARTITIONED BY(date STRING, pos STRING)
ROW FORMAT DELIMITED ‘\t’
   FIELDS TERMINATED BY '\n'
STORED AS SEQUENCEFILE;

1.3 创建bucket表

CREATE TABLE par_table(viewTime INT, userid BIGINT,
     page_url STRING, referrer_url STRING,
     ip STRING COMMENT 'IP Address of the User')
COMMENT 'This is the page view table'
PARTITIONED BY(date STRING, pos STRING)
CLUSTERED BY(userid) SORTED BY(viewTime) INTO 32 BUCKETS
ROW FORMAT DELIMITED ‘\t’
   FIELDS TERMINATED BY '\n'
STORED AS SEQUENCEFILE;

2.2.2 修改表结构

ALTER TABLE pokes ADD COLUMNS (new_col INT); #表添加一列
ALTER TABLE invites ADD COLUMNS (new_col2 INT COMMENT 'a comment'); #表添加一列+注释
ALTER TABLE events RENAME TO 3koobecaf; #表名更改
ALTER TABLE table_name ADD [IF NOT EXISTS] partition_spec [ LOCATION 'location1' ] partition_spec [ LOCATION 'location2' ] ...
    partition_spec:
    : PARTITION (partition_col = partition_col_value, partition_col = partiton_col_value, ...) #增加分区
ALTER TABLE table_name DROP partition_spec, partition_spec,... #删除分区
ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name] #修改列信息
ALTER TABLE table_name SET TBLPROPERTIES table_properties table_properties:
    :[property_name = property_value…..] #增加表的元数据信息
ALTER TABLE table_name SET FILEFORMAT file_format
ALTER TABLE table_name CLUSTERED BY(userid) SORTED BY(viewTime) INTO num_buckets BUCKETS #改变表文件格式与组织

2.2.3 DML

Hive DML操作详解

2.2.4 DQL

Hive DQL操作详解

* Join

只支持等值join
•SELECT a.* FROM a JOIN b ON (a.id = b.id)
•SELECT a.* FROM a JOIN b
ON (a.id = b.id AND a.department = b.department)
•可以 join 多于 2 个表，例如
  SELECT a.val, b.val, c.val FROM a JOIN b
ON (a.key = b.key1) JOIN c ON (c.key = b.key2)

•如果join中多个表的 join key 是同一个，则 join 会被转化为单个 map/reduce 任务
LEFT，RIGHT和FULL OUTER
•SELECT a.val, b.val FROM a LEFT OUTER JOIN b ON (a.key=b.key)
•如果你想限制 join 的输出，应该在 WHERE 子句中写过滤条件——或是在 join 子句中写
•容易混淆的问题是表分区的情况
• SELECT c.val, d.val FROM c LEFT OUTER JOIN d ON (c.key=d.key)
  WHERE a.ds='2010-07-07' AND b.ds='2010-07-07‘
•如果 d 表中找不到对应 c 表的记录，d 表的所有列都会列出 NULL，包括 ds 列。也就是说，join 会过滤 d 表中不能找到匹配 c 表 join key 的所有记录。这样的话，LEFT OUTER 就使得查询结果与 WHERE 子句无关
•解决办法
•SELECT c.val, d.val FROM c LEFT OUTER JOIN d
  ON (c.key=d.key AND d.ds='2009-07-07' AND c.ds='2009-07-07')

LEFT SEMI JOIN
•LEFT SEMI JOIN 的限制是， JOIN 子句中右边的表只能在 ON 子句中设置过滤条件，在 WHERE 子句、SELECT 子句或其他地方过滤都不行
•SELECT a.key, a.value
  FROM a
  WHERE a.key in
(SELECT b.key
FROM B);
   可以被重写为：
   SELECT a.key, a.val
FROM a LEFT SEMI JOIN b on (a.key = b.key)