hive 三

最新推荐文章于 2021-12-22 10:50:51 发布
最新推荐文章于 2021-12-22 10:50:51 发布
阅读量713 收藏
点赞数
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/xwcj3/article/details/103646813
版权

一、hiveServer2

HiveServer2 = HS2
HS2 ：Server
默认端口号是10000
能不能改成10086呢？
beeline：Client Hive/Spark
启动beeline时，建议先cd $HIVE_HOME/bin中去
./beeline -u jdbc:hive2://pxj31:10000 -n hadoop

nohup bin/hiveserver2  --hiveconf hive.server2.thrift.port=10086 & 1>/dev/null 2>&1 &
nohup bin/hiveserver2  & 1>/dev/null 2>&1 &
[pxj@pxj31 /home/pxj/app/hive-1.1.0-cdh5.16.2/bin]$./hiveserver2 --hiveconf hive.server2.thrift.port=10086 &
[1] 14136
[pxj@pxj31 /home/pxj/app/hive-1.1.0-cdh5.16.2/bin]$beeline -u jdbc:hive2://pxj31:10086 
which: no hbase in (/home/pxj/app/hive-1.1.0-cdh5.16.2/bin:/home/pxj/app/hadoop/bin:/home/pxj/app/hadoop/sbin:/usr/java/jdk1.8.0_121/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/home/pxj/.local/bin:/home/pxj/bin)
scan complete in 2ms
Connecting to jdbc:hive2://pxj31:10086
Connected to: Apache Hive (version 1.1.0-cdh5.16.2)
Driver: Hive JDBC (version 1.1.0-cdh5.16.2)
Transaction isolation: TRANSACTION_REPEATABLE_READ
Beeline version 1.1.0-cdh5.16.2 by Apache Hive
0: jdbc:hive2://pxj31:10086> show tables;
OK
INFO  : Compiling command(queryId=pxj_20191219014242_4bc74e15-8e39-4313-8aa3-8602ae502d73): show tables
INFO  : Semantic Analysis Completed
INFO  : Returning Hive schema: Schema(fieldSchemas:[FieldSchema(name:tab_name, type:string, comment:from deserializer)], properties:null)
INFO  : Completed compiling command(queryId=pxj_20191219014242_4bc74e15-8e39-4313-8aa3-8602ae502d73); Time taken: 0.624 seconds
INFO  : Concurrency mode is disabled, not creating a lock manager
INFO  : Executing command(queryId=pxj_20191219014242_4bc74e15-8e39-4313-8aa3-8602ae502d73): show tables
INFO  : Starting task [Stage-0:DDL] in serial mode
INFO  : Completed executing command(queryId=pxj_20191219014242_4bc74e15-8e39-4313-8aa3-8602ae502d73); Time taken: 0.218 seconds
INFO  : OK
+----------------------------+--+
|          tab_name          |
+----------------------------+--+
| emp                        |
| emp_dynamic_partition      |
| emp_partition              |
| order_mulit_partition      |
| order_partition            |
| pxj_order_mulit_partition  |
| stu                        |
+----------------------------+--+
7 rows selected (1.306 seconds)
0: jdbc:hive2://pxj31:10086> 
​

join

0: jdbc:hive2://pxj31:10000> select  
. . . . . . . . . . . . . .> e.empno, e.ename, e.deptno, d.dname
. . . . . . . . . . . . . .> from emp e join dept d
. . . . . . . . . . . . . .> on e.deptno = d.deptno;
​
+----------+----------+-----------+-------------+--+
| e.empno  | e.ename  | e.deptno  |   d.dname   |
+----------+----------+-----------+-------------+--+
| 7369     | SMITH    | 20        | RESEARCH    |
| 7499     | ALLEN    | 30        | SALES       |
| 7521     | WARD     | 30        | SALES       |
| 7566     | JONES    | 20        | RESEARCH    |
| 7654     | MARTIN   | 30        | SALES       |
| 7698     | BLAKE    | 30        | SALES       |
| 7782     | CLARK    | 10        | ACCOUNTING  |
| 7788     | SCOTT    | 20        | RESEARCH    |
| 7839     | KING     | 10        | ACCOUNTING  |
| 7844     | TURNER   | 30        | SALES       |
| 7876     | ADAMS    | 20        | RESEARCH    |
| 7900     | JAMES    | 30        | SALES       |
| 7902     | FORD     | 20        | RESEARCH    |
| 7934     | MILLER   | 10        | ACCOUNTING  |
+----------+----------+-----------+-------------+--+
​
支持等值连接  ==》N张表做join，连接条件有N-1个
inner join：只返回连接条件匹配上的数据

0: jdbc:hive2://pxj31:10000> select  
. . . . . . . . . . . . . .> e.empno, e.ename, e.deptno, d.dname
. . . . . . . . . . . . . .> from emp e join dept d
. . . . . . . . . . . . . .> on e.deptno = d.deptno ;
+----------+----------+-----------+-------------+--+
| e.empno  | e.ename  | e.deptno  |   d.dname   |
+----------+----------+-----------+-------------+--+
| 7369     | SMITH    | 20        | RESEARCH    |
| 7499     | ALLEN    | 30        | SALES       |
| 7521     | WARD     | 30        | SALES       |
| 7566     | JONES    | 20        | RESEARCH    |
| 7654     | MARTIN   | 30        | SALES       |
| 7698     | BLAKE    | 30        | SALES       |
| 7782     | CLARK    | 10        | ACCOUNTING  |
| 7788     | SCOTT    | 20        | RESEARCH    |
| 7839     | KING     | 10        | ACCOUNTING  |
| 7844     | TURNER   | 30        | SALES       |
| 7876     | ADAMS    | 20        | RESEARCH    |
| 7900     | JAMES    | 30        | SALES       |
| 7902     | FORD     | 20        | RESEARCH    |
| 7934     | MILLER   | 10        | ACCOUNTING  |
+----------+----------+-----------+-------------+--+

​

Hive复杂数据类型

array 数据类型要一样

create table hive_array(name string, work_locations array<string>)
row format delimited
fields terminated by '\t'
COLLECTION ITEMS TERMINATED BY ',';
load data local inpath '/opt/hive_array.txt' into table hive_array;
0: jdbc:hive2://pxj31:10000> select * from  hive_array
. . . . . . . . . . . . . .
INFO  : Compiling command(queryId=pxj_20191221120707_7fdd15c4-66de-4e99-9a8b-be40c0ac7e71): select * from  hive_array
INFO  : Semantic Analysis Completed
INFO  : Returning Hive schema: Schema(fieldSchemas:[FieldSchema(name:hive_array.name, type:string, comment:null), FieldSchema(name:hive_array.work_locations, type:array<string>, comment:null)], properties:null)
INFO  : Completed compiling command(queryId=pxj_20191221120707_7fdd15c4-66de-4e99-9a8b-be40c0ac7e71); Time taken: 0.043 seconds
INFO  : Concurrency mode is disabled, not creating a lock manager
INFO  : Executing command(queryId=pxj_20191221120707_7fdd15c4-66de-4e99-9a8b-be40c0ac7e71): select * from  hive_array
INFO  : Completed executing command(queryId=pxj_20191221120707_7fdd15c4-66de-4e99-9a8b-be40c0ac7e71); Time taken: 0.0 seconds
INFO  : OK
+------------------+----------------------------------------------+--+
| hive_array.name  |          hive_array.work_locations           |
+------------------+----------------------------------------------+--+
| pk               | ["beijing","shanghai","tianjin","hangzhou"]  |
| jepson           | ["changchu","chengdu","wuhan","beijing"]     |
+------------------+----------------------------------------------+--+
2 rows selected (0.074 seconds)
取值
取值：
0: jdbc:hive2://pxj31:10000> select name,work_locations[0] from hive_array;
INFO  : Compiling command(queryId=pxj_20191221120808_029bd55e-5ea4-466c-8e7a-b720a9d397f5): select name,work_locations[0] from hive_array
INFO  : Semantic Analysis Completed
INFO  : Returning Hive schema: Schema(fieldSchemas:[FieldSchema(name:name, type:string, comment:null), FieldSchema(name:_c1, type:string, comment:null)], properties:null)
INFO  : Completed compiling command(queryId=pxj_20191221120808_029bd55e-5ea4-466c-8e7a-b720a9d397f5); Time taken: 0.141 seconds
INFO  : Concurrency mode is disabled, not creating a lock manager
INFO  : Executing command(queryId=pxj_20191221120808_029bd55e-5ea4-466c-8e7a-b720a9d397f5): select name,work_locations[0] from hive_array
INFO  : Completed executing command(queryId=pxj_20191221120808_029bd55e-5ea4-466c-8e7a-b720a9d397f5); Time taken: 0.001 seconds
INFO  : OK
+---------+-----------+--+
|  name   |    _c1    |
+---------+-----------+--+
| pk      | beijing   |
| jepson  | changchu  |
+---------+-----------+--+
2 rows selected (0.17 seconds)
0: jdbc:hive2://pxj31:10000> select name,array_contains(work,'beijing') from hive_array;
+---------+-------+--+
|  name   |  _c1  |
+---------+-------+--+
| pk      | true  |
| jepson  | true  |
+---------+-------+--+
size
0: jdbc:hive2://pxj31:10000> select * ,size(work_locations) from hive_array;
INFO  : Compiling command(queryId=pxj_20191221145151_8e50e4c5-0820-4598-ab93-c8f2ceb19812): select * ,size(work_locations) from hive_array
INFO  : Semantic Analysis Completed
INFO  : Returning Hive schema: Schema(fieldSchemas:[FieldSchema(name:hive_array.name, type:string, comment:null), FieldSchema(name:hive_array.work_locations, type:array<string>, comment:null), FieldSchema(name:_c1, type:int, comment:null)], properties:null)
INFO  : Completed compiling command(queryId=pxj_20191221145151_8e50e4c5-0820-4598-ab93-c8f2ceb19812); Time taken: 0.205 seconds
INFO  : Concurrency mode is disabled, not creating a lock manager
INFO  : Executing command(queryId=pxj_20191221145151_8e50e4c5-0820-4598-ab93-c8f2ceb19812): select * ,size(work_locations) from hive_array
INFO  : Completed executing command(queryId=pxj_20191221145151_8e50e4c5-0820-4598-ab93-c8f2ceb19812); Time taken: 0.001 seconds
INFO  : OK
+------------------+----------------------------------------------+------+--+
| hive_array.name  |          hive_array.work_locations           | _c1  |
+------------------+----------------------------------------------+------+--+
| pk               | ["beijing","shanghai","tianjin","hangzhou"]  | 4    |
| jepson           | ["changchu","chengdu","wuhan","beijing"]     | 4    |
+------------------+----------------------------------------------+------+--+
2 rows selected (0.251 seconds)
​

MAP

create table hive_map(
id int, name string, members map<string,string>, age int
)
row format delimited
fields terminated by ','
COLLECTION ITEMS TERMINATED BY '#'
MAP KEYS TERMINATED BY ':'
;
0: jdbc:hive2://pxj31:10000> load data local inpath '/opt/hive_map.txt' into table hive_map;
0: jdbc:hive2://pxj31:10000> select * from hive_map;
INFO  : Compiling command(queryId=pxj_20191221150101_3bdb0f80-c46f-4b2f-820d-5ce6f8c19625): select * from hive_map
INFO  : Semantic Analysis Completed
INFO  : Returning Hive schema: Schema(fieldSchemas:[FieldSchema(name:hive_map.id, type:int, comment:null), FieldSchema(name:hive_map.name, type:string, comment:null), FieldSchema(name:hive_map.members, type:map<string,string>, comment:null), FieldSchema(name:hive_map.age, type:int, comment:null)], properties:null)
INFO  : Completed compiling command(queryId=pxj_20191221150101_3bdb0f80-c46f-4b2f-820d-5ce6f8c19625); Time taken: 0.074 seconds
INFO  : Concurrency mode is disabled, not creating a lock manager
INFO  : Executing command(queryId=pxj_20191221150101_3bdb0f80-c46f-4b2f-820d-5ce6f8c19625): select * from hive_map
INFO  : Completed executing command(queryId=pxj_20191221150101_3bdb0f80-c46f-4b2f-820d-5ce6f8c19625); Time taken: 0.0 seconds
INFO  : OK
+--------------+----------------+----------------------------------------------------+---------------+--+
| hive_map.id  | hive_map.name  |                  hive_map.members                  | hive_map.age  |
+--------------+----------------+----------------------------------------------------+---------------+--+
| 1            | zhangsan       | {"father":"xiaoming","mother":"xiaohuang","brother":"xiaoxu"} | 28            |
| 2            | lisi           | {"father":"mayun","mother":"huangyi","brother":"guanyu"} | 22            |
| 3            | wangwu         | {"father":"wangjianlin","mother":"ruhua","sister":"jingtian"} | 29            |
| 4            | mayun          | {"father":"mayongzhen","mother":"angelababy"}      | 26            |
+--------------+----------------+----------------------------------------------------+---------------+--+
4 rows selected (0.1 seconds)
​
0: jdbc:hive2://pxj31:10000> select * ,members['father'] from hive_map;
INFO  : Compiling command(queryId=pxj_20191221151111_711f5ebb-5efd-448f-bac7-a800c056f229): select * ,members['father'] from hive_map
INFO  : Semantic Analysis Completed
INFO  : Returning Hive schema: Schema(fieldSchemas:[FieldSchema(name:hive_map.id, type:int, comment:null), FieldSchema(name:hive_map.name, type:string, comment:null), FieldSchema(name:hive_map.members, type:map<string,string>, comment:null), FieldSchema(name:hive_map.age, type:int, comment:null), FieldSchema(name:_c1, type:string, comment:null)], properties:null)
INFO  : Completed compiling command(queryId=pxj_20191221151111_711f5ebb-5efd-448f-bac7-a800c056f229); Time taken: 0.111 seconds
INFO  : Concurrency mode is disabled, not creating a lock manager
INFO  : Executing command(queryId=pxj_20191221151111_711f5ebb-5efd-448f-bac7-a800c056f229): select * ,members['father'] from hive_map
INFO  : Completed executing command(queryId=pxj_20191221151111_711f5ebb-5efd-448f-bac7-a800c056f229); Time taken: 0.0 seconds
INFO  : OK
+--------------+----------------+----------------------------------------------------+---------------+--------------+--+
| hive_map.id  | hive_map.name  |                  hive_map.members                  | hive_map.age  |     _c1      |
+--------------+----------------+----------------------------------------------------+---------------+--------------+--+
| 1            | zhangsan       | {"father":"xiaoming","mother":"xiaohuang","brother":"xiaoxu"} | 28            | xiaoming     |
| 2            | lisi           | {"father":"mayun","mother":"huangyi","brother":"guanyu"} | 22            | mayun        |
| 3            | wangwu         | {"father":"wangjianlin","mother":"ruhua","sister":"jingtian"} | 29            | wangjianlin  |
| 4            | mayun          | {"father":"mayongzhen","mother":"angelababy"}      | 26            | mayongzhen   |
+--------------+----------------+----------------------------------------------------+---------------+--------------+--+
4 rows selected (0.171 seconds)
​
map_keys

0: jdbc:hive2://pxj31:10000> select *,map_key(members) from  hive_map;
Error: Error while compiling statement: FAILED: SemanticException [Error 10011]: Line 1:9 Invalid function 'map_key' (state=42000,code=10011)
0: jdbc:hive2://pxj31:10000> select *,map_keys(members) from  hive_map;
INFO  : Compiling command(queryId=pxj_20191221152121_f449a11c-4fe0-4213-b422-017db94ebfde): select *,map_keys(members) from  hive_map
INFO  : Semantic Analysis Completed
INFO  : Returning Hive schema: Schema(fieldSchemas:[FieldSchema(name:hive_map.id, type:int, comment:null), FieldSchema(name:hive_map.name, type:string, comment:null), FieldSchema(name:hive_map.members, type:map<string,string>, comment:null), FieldSchema(name:hive_map.age, type:int, comment:null), FieldSchema(name:_c1, type:array<string>, comment:null)], properties:null)
INFO  : Completed compiling command(queryId=pxj_20191221152121_f449a11c-4fe0-4213-b422-017db94ebfde); Time taken: 0.186 seconds
INFO  : Concurrency mode is disabled, not creating a lock manager
INFO  : Executing command(queryId=pxj_20191221152121_f449a11c-4fe0-4213-b422-017db94ebfde): select *,map_keys(members) from  hive_map
INFO  : Completed executing command(queryId=pxj_20191221152121_f449a11c-4fe0-4213-b422-017db94ebfde); Time taken: 0.0 seconds
INFO  : OK
+--------------+----------------+----------------------------------------------------+---------------+--------------------------------+--+
| hive_map.id  | hive_map.name  |                  hive_map.members                  | hive_map.age  |              _c1               |
+--------------+----------------+----------------------------------------------------+---------------+--------------------------------+--+
| 1            | zhangsan       | {"father":"xiaoming","mother":"xiaohuang","brother":"xiaoxu"} | 28            | ["father","mother","brother"]  |
| 2            | lisi           | {"father":"mayun","mother":"huangyi","brother":"guanyu"} | 22            | ["father","mother","brother"]  |
| 3            | wangwu         | {"father":"wangjianlin","mother":"ruhua","sister":"jingtian"} | 29            | ["father","mother","sister"]   |
| 4            | mayun          | {"father":"mayongzhen","mother":"angelababy"}      | 26            | ["father","mother"]            |
+--------------+----------------+----------------------------------------------------+---------------+--------------------------------+--+
4 rows selected (0.259 seconds)
​
map_values
0: jdbc:hive2://pxj31:10000> select *,map_values(members) from  hive_map;
INFO  : Compiling command(queryId=pxj_20191221152424_304d0114-a301-43e2-9215-08e9cee44f34): select *,map_values(members) from  hive_map
INFO  : Semantic Analysis Completed
INFO  : Returning Hive schema: Schema(fieldSchemas:[FieldSchema(name:hive_map.id, type:int, comment:null), FieldSchema(name:hive_map.name, type:string, comment:null), FieldSchema(name:hive_map.members, type:map<string,string>, comment:null), FieldSchema(name:hive_map.age, type:int, comment:null), FieldSchema(name:_c1, type:array<string>, comment:null)], properties:null)
INFO  : Completed compiling command(queryId=pxj_20191221152424_304d0114-a301-43e2-9215-08e9cee44f34); Time taken: 0.101 seconds
INFO  : Concurrency mode is disabled, not creating a lock manager
INFO  : Executing command(queryId=pxj_20191221152424_304d0114-a301-43e2-9215-08e9cee44f34): select *,map_values(members) from  hive_map
INFO  : Completed executing command(queryId=pxj_20191221152424_304d0114-a301-43e2-9215-08e9cee44f34); Time taken: 0.0 seconds
INFO  : OK
+--------------+----------------+----------------------------------------------------+---------------+-------------------------------------+--+
| hive_map.id  | hive_map.name  |                  hive_map.members                  | hive_map.age  |                 _c1                 |
+--------------+----------------+----------------------------------------------------+---------------+-------------------------------------+--+
| 1            | zhangsan       | {"father":"xiaoming","mother":"xiaohuang","brother":"xiaoxu"} | 28            | ["xiaoming","xiaohuang","xiaoxu"]   |
| 2            | lisi           | {"father":"mayun","mother":"huangyi","brother":"guanyu"} | 22            | ["mayun","huangyi","guanyu"]        |
| 3            | wangwu         | {"father":"wangjianlin","mother":"ruhua","sister":"jingtian"} | 29            | ["wangjianlin","ruhua","jingtian"]  |
| 4            | mayun          | {"father":"mayongzhen","mother":"angelababy"}      | 26            | ["mayongzhen","angelababy"]         |
+--------------+----------------+----------------------------------------------------+---------------+-------------------------------------+--+
4 rows selected (0.17 seconds)
​
array_contains
0: jdbc:hive2://pxj31:10000> select *,array_contains(map_keys(members),'father') from hive_map;
INFO  : Compiling command(queryId=pxj_20191221154141_e65481b6-e609-460c-b806-e5e7b4944bba): select *,array_contains(map_keys(members),'father') from hive_map
INFO  : Semantic Analysis Completed
INFO  : Returning Hive schema: Schema(fieldSchemas:[FieldSchema(name:hive_map.id, type:int, comment:null), FieldSchema(name:hive_map.name, type:string, comment:null), FieldSchema(name:hive_map.members, type:map<string,string>, comment:null), FieldSchema(name:hive_map.age, type:int, comment:null), FieldSchema(name:_c1, type:boolean, comment:null)], properties:null)
INFO  : Completed compiling command(queryId=pxj_20191221154141_e65481b6-e609-460c-b806-e5e7b4944bba); Time taken: 0.117 seconds
INFO  : Concurrency mode is disabled, not creating a lock manager
INFO  : Executing command(queryId=pxj_20191221154141_e65481b6-e609-460c-b806-e5e7b4944bba): select *,array_contains(map_keys(members),'father') from hive_map
INFO  : Completed executing command(queryId=pxj_20191221154141_e65481b6-e609-460c-b806-e5e7b4944bba); Time taken: 0.001 seconds
INFO  : OK
+--------------+----------------+----------------------------------------------------+---------------+-------+--+
| hive_map.id  | hive_map.name  |                  hive_map.members                  | hive_map.age  |  _c1  |
+--------------+----------------+----------------------------------------------------+---------------+-------+--+
| 1            | zhangsan       | {"father":"xiaoming","mother":"xiaohuang","brother":"xiaoxu"} | 28            | true  |
| 2            | lisi           | {"father":"mayun","mother":"huangyi","brother":"guanyu"} | 22            | true  |
| 3            | wangwu         | {"father":"wangjianlin","mother":"ruhua","sister":"jingtian"} | 29            | true  |
| 4            | mayun          | {"father":"mayongzhen","mother":"angelababy"}      | 26            | true  |
+--------------+----------------+----------------------------------------------------+---------------+-------+--+
4 rows selected (0.195 seconds)

​

sort_array
0: jdbc:hive2://pxj31:10000> select *,sort_array(map_values(members)) from hive_map;
INFO  : Compiling command(queryId=pxj_20191221154848_9787ab62-a816-4093-8050-e766eb869a8b): select *,sort_array(map_values(members)) from hive_map
INFO  : Semantic Analysis Completed
INFO  : Returning Hive schema: Schema(fieldSchemas:[FieldSchema(name:hive_map.id, type:int, comment:null), FieldSchema(name:hive_map.name, type:string, comment:null), FieldSchema(name:hive_map.members, type:map<string,string>, comment:null), FieldSchema(name:hive_map.age, type:int, comment:null), FieldSchema(name:_c1, type:array<string>, comment:null)], properties:null)
INFO  : Completed compiling command(queryId=pxj_20191221154848_9787ab62-a816-4093-8050-e766eb869a8b); Time taken: 0.16 seconds
INFO  : Concurrency mode is disabled, not creating a lock manager
INFO  : Executing command(queryId=pxj_20191221154848_9787ab62-a816-4093-8050-e766eb869a8b): select *,sort_array(map_values(members)) from hive_map
INFO  : Completed executing command(queryId=pxj_20191221154848_9787ab62-a816-4093-8050-e766eb869a8b); Time taken: 0.0 seconds
INFO  : OK
+--------------+----------------+----------------------------------------------------+---------------+-------------------------------------+--+
| hive_map.id  | hive_map.name  |                  hive_map.members                  | hive_map.age  |                 _c1                 |
+--------------+----------------+----------------------------------------------------+---------------+-------------------------------------+--+
| 1            | zhangsan       | {"father":"xiaoming","mother":"xiaohuang","brother":"xiaoxu"} | 28            | ["xiaohuang","xiaoming","xiaoxu"]   |
| 2            | lisi           | {"father":"mayun","mother":"huangyi","brother":"guanyu"} | 22            | ["guanyu","huangyi","mayun"]        |
| 3            | wangwu         | {"father":"wangjianlin","mother":"ruhua","sister":"jingtian"} | 29            | ["jingtian","ruhua","wangjianlin"]  |
| 4            | mayun          | {"father":"mayongzhen","mother":"angelababy"}      | 26            | ["angelababy","mayongzhen"]         |
+--------------+----------------+----------------------------------------------------+---------------+-------------------------------------+--+
4 rows selected (0.243 seconds)
​ 查询出有兄弟的人以及他的兄弟是谁 使用子查询搞定
0: jdbc:hive2://pxj31:10000> select * , members['brother'] from
. . . . . . . . . . . . . .> (select *,array_contains(map_keys(members), 'brother') flag  from hive_map)t
. . . . . . . . . . . . . .> where flag=true
. . . . . . . . . . . . . .> ;
INFO  : Compiling command(queryId=pxj_20191221155959_0f2a3aa1-2ab8-4c40-974e-1300a1cd6840): select * , members['brother'] from
(select *,array_contains(map_keys(members), 'brother') flag  from hive_map)t
where flag=true
INFO  : Semantic Analysis Completed
INFO  : Returning Hive schema: Schema(fieldSchemas:[FieldSchema(name:t.id, type:int, comment:null), FieldSchema(name:t.name, type:string, comment:null), FieldSchema(name:t.members, type:map<string,string>, comment:null), FieldSchema(name:t.age, type:int, comment:null), FieldSchema(name:t.flag, type:boolean, comment:null), FieldSchema(name:_c1, type:string, comment:null)], properties:null)
INFO  : Completed compiling command(queryId=pxj_20191221155959_0f2a3aa1-2ab8-4c40-974e-1300a1cd6840); Time taken: 0.11 seconds
INFO  : Concurrency mode is disabled, not creating a lock manager
INFO  : Executing command(queryId=pxj_20191221155959_0f2a3aa1-2ab8-4c40-974e-1300a1cd6840): select * , members['brother'] from
(select *,array_contains(map_keys(members), 'brother') flag  from hive_map)t
where flag=true
INFO  : Completed executing command(queryId=pxj_20191221155959_0f2a3aa1-2ab8-4c40-974e-1300a1cd6840); Time taken: 0.0 seconds
INFO  : OK
+-------+-----------+----------------------------------------------------+--------+---------+---------+--+
| t.id  |  t.name   |                     t.members                      | t.age  | t.flag  |   _c1   |
+-------+-----------+----------------------------------------------------+--------+---------+---------+--+
| 1     | zhangsan  | {"father":"xiaoming","mother":"xiaohuang","brother":"xiaoxu"} | 28     | true    | xiaoxu  |
| 2     | lisi      | {"father":"mayun","mother":"huangyi","brother":"guanyu"} | 22     | true    | guanyu  |
+-------+-----------+----------------------------------------------------+--------+---------+---------+--+
2 rows selected (0.14 seconds)

​

STRUCT
内部的数据类型可以不同
0: jdbc:hive2://pxj31:10000> create table hive_struct(
. . . . . . . . . . . . . .> ip string, info struct<name:string,age:int>
. . . . . . . . . . . . . .> )
. . . . . . . . . . . . . .> row format delimited
. . . . . . . . . . . . . .> fields terminated by '#'
. . . . . . . . . . . . . .> COLLECTION ITEMS TERMINATED BY ':'
. . . . . . . . . . . . . .> ;
INFO  : Compiling command(queryId=pxj_20191221160202_68a99e92-73c7-4a44-a982-0cf609d96d42): create table hive_struct(
ip string, info struct<name:string,age:int>
)
row format delimited
fields terminated by '#'
COLLECTION ITEMS TERMINATED BY ':'
0: jdbc:hive2://pxj31:10000> select * from  hive_struct;
INFO  : Compiling command(queryId=pxj_20191221160707_6094fc98-44f7-46ca-b0fa-1655c8f6e97b): select * from  hive_struct
INFO  : Semantic Analysis Completed
INFO  : Returning Hive schema: Schema(fieldSchemas:[FieldSchema(name:hive_struct.ip, type:string, comment:null), FieldSchema(name:hive_struct.info, type:struct<name:string,age:int>, comment:null)], properties:null)
INFO  : Completed compiling command(queryId=pxj_20191221160707_6094fc98-44f7-46ca-b0fa-1655c8f6e97b); Time taken: 0.092 seconds
INFO  : Concurrency mode is disabled, not creating a lock manager
INFO  : Executing command(queryId=pxj_20191221160707_6094fc98-44f7-46ca-b0fa-1655c8f6e97b): select * from  hive_struct
INFO  : Completed executing command(queryId=pxj_20191221160707_6094fc98-44f7-46ca-b0fa-1655c8f6e97b); Time taken: 0.0 seconds
INFO  : OK
+-----------------+-------------------------------+--+
| hive_struct.ip  |       hive_struct.info        |
+-----------------+-------------------------------+--+
| 192.168.1.1     | {"name":"zhangsan","age":40}  |
| 192.168.1.2     | {"name":"lisi","age":50}      |
| 192.168.1.3     | {"name":"wangwu","age":60}    |
| 192.168.1.4     | {"name":"zhaoliu","age":70}   |
+-----------------+-------------------------------+--+

​

collect_set
人 访问的所有ad_id 去重 
select cookie_id, collect_set(ad_id) from click_log group by cookie_id;
select cookie_id,ad_id, collect_set(ad_id) from click_log group by cookie_id
0: jdbc:hive2://pxj31:10000> select
. . . . . . . . . . . . . .> click.cookie_id,click.ad_id,click.amount,ad_list.catalogs
. . . . . . . . . . . . . .> from
. . . . . . . . . . . . . .> (select cookie_id, ad_id, count(1) amount from click_log group by cookie_id, ad_id) click
. . . . . . . . . . . . . .> join ad_list
. . . . . . . . . . . . . .> on ad_list.ad_id = click.ad_id;
+------------------+--------------+---------------+--------------------------------------+--+
| click.cookie_id  | click.ad_id  | click.amount  |           ad_list.catalogs           |
+------------------+--------------+---------------+--------------------------------------+--+
| 11               | ad_101       | 3             | catalog8|catalog1                    |
| 11               | ad_104       | 1             | catalog5|catalog1|catalog4|catalog9  |
| 22               | ad_102       | 1             | catalog6|catalog3                    |
| 22               | ad_103       | 2             | catalog7                             |
| 22               | ad_104       | 1             | catalog5|catalog1|catalog4|catalog9  |
| 33               | ad_101       | 1             | catalog8|catalog1                    |
| 33               | ad_102       | 1             | catalog6|catalog3                    |
| 33               | ad_103       | 1             | catalog7                             |
| 35               | ad_102       | 1             | catalog6|catalog3                    |
| 35               | ad_105       | 1             | NULL                                 |
| 77               | ad_103       | 1             | catalog7                             |
| 77               | ad_104       | 1             | catalog5|catalog1|catalog4|catalog9  |
| 99               | ad_102       | 1             | catalog6|catalog3                    |
| 99               | ad_105       | 1             | NULL                                 |
+------------------+--------------+---------------+--------------------------------------+--+
14 rows selected (42.762 seconds)

​

列转行
0: jdbc:hive2://pxj31:10000> select ad_id, catalog from ad_list lateral view outer explode(split(catalogs,'\\|')) t as catalog;
INFO  : Compiling command(queryId=pxj_20191221164343_37d78b35-e1d8-4a68-8545-d14bddb9cf67): select ad_id, catalog from ad_list lateral view outer explode(split(catalogs,'\\|')) t as catalog
INFO  : Semantic Analysis Completed
INFO  : Returning Hive schema: Schema(fieldSchemas:[FieldSchema(name:ad_id, type:string, comment:null), FieldSchema(name:catalog, type:string, comment:null)], properties:null)
INFO  : Completed compiling command(queryId=pxj_20191221164343_37d78b35-e1d8-4a68-8545-d14bddb9cf67); Time taken: 0.136 seconds
INFO  : Concurrency mode is disabled, not creating a lock manager
INFO  : Executing command(queryId=pxj_20191221164343_37d78b35-e1d8-4a68-8545-d14bddb9cf67): select ad_id, catalog from ad_list lateral view outer explode(split(catalogs,'\\|')) t as catalog
INFO  : Completed executing command(queryId=pxj_20191221164343_37d78b35-e1d8-4a68-8545-d14bddb9cf67); Time taken: 0.0 seconds
INFO  : OK
+---------+-----------+--+
|  ad_id  |  catalog  |
+---------+-----------+--+
| ad_101  | catalog8  |
| ad_101  | catalog1  |
| ad_102  | catalog6  |
| ad_102  | catalog3  |
| ad_103  | catalog7  |
| ad_104  | catalog5  |
| ad_104  | catalog1  |
| ad_104  | catalog4  |
| ad_104  | catalog9  |
| ad_105  | NULL      |
+---------+-----------+--+
10 rows selected (0.156 seconds)

​

去掉out
0: jdbc:hive2://pxj31:10000> select ad_id, catalog from ad_list lateral view  explode(split(catalogs,'\\|')) t as catalog;
INFO  : Compiling command(queryId=pxj_20191221164545_6d5fc853-6503-45d7-973e-444f3a9bebaf): select ad_id, catalog from ad_list lateral view  explode(split(catalogs,'\\|')) t as catalog
INFO  : Semantic Analysis Completed
INFO  : Returning Hive schema: Schema(fieldSchemas:[FieldSchema(name:ad_id, type:string, comment:null), FieldSchema(name:catalog, type:string, comment:null)], properties:null)
INFO  : Completed compiling command(queryId=pxj_20191221164545_6d5fc853-6503-45d7-973e-444f3a9bebaf); Time taken: 0.093 seconds
INFO  : Concurrency mode is disabled, not creating a lock manager
INFO  : Executing command(queryId=pxj_20191221164545_6d5fc853-6503-45d7-973e-444f3a9bebaf): select ad_id, catalog from ad_list lateral view  explode(split(catalogs,'\\|')) t as catalog
INFO  : Completed executing command(queryId=pxj_20191221164545_6d5fc853-6503-45d7-973e-444f3a9bebaf); Time taken: 0.0 seconds
INFO  : OK
+---------+-----------+--+
|  ad_id  |  catalog  |
+---------+-----------+--+
| ad_101  | catalog8  |
| ad_101  | catalog1  |
| ad_102  | catalog6  |
| ad_102  | catalog3  |
| ad_103  | catalog7  |
| ad_104  | catalog5  |
| ad_104  | catalog1  |
| ad_104  | catalog4  |
| ad_104  | catalog9  |
+---------+-----------+--+
9 rows selected (0.115 seconds)
​

常用内置函数

查看：desc function extended date

0: jdbc:hive2://pxj31:10000> desc function extended  date
. . . . . . . . . . . . . .> ;
INFO  : Compiling command(queryId=pxj_20191221165555_3d62b58b-7db9-4077-adf8-a4de423ead58): desc function extended  date
INFO  : Semantic Analysis Completed
INFO  : Returning Hive schema: Schema(fieldSchemas:[FieldSchema(name:tab_name, type:string, comment:from deserializer)], properties:null)
INFO  : Completed compiling command(queryId=pxj_20191221165555_3d62b58b-7db9-4077-adf8-a4de423ead58); Time taken: 0.007 seconds
INFO  : Concurrency mode is disabled, not creating a lock manager
INFO  : Executing command(queryId=pxj_20191221165555_3d62b58b-7db9-4077-adf8-a4de423ead58): desc function extended  date
INFO  : Starting task [Stage-0:DDL] in serial mode
INFO  : Completed executing command(queryId=pxj_20191221165555_3d62b58b-7db9-4077-adf8-a4de423ead58); Time taken: 0.008 seconds
INFO  : OK
+----------------------------------------------------+--+
|                      tab_name                      |
+----------------------------------------------------+--+
| CAST(<Date string> as DATE) - Returns the date represented by the date string. |
| date_string is a string in the format 'yyyy-MM-dd.'Example: |
|    > SELECT CAST('2009-01-01' AS DATE) FROM src LIMIT 1; |
|   '2009-01-01'                                     |
+----------------------------------------------------+--+
4 rows selected (0.029 seconds)
​
select userid,movie,rate,time,
from_unixtime(cast(time as bigint)) as ts,
year(from_unixtime(cast(time as bigint))) as year,
month(from_unixtime(cast(time as bigint))) as month,
day(from_unixtime(cast(time as bigint))) as day,
hour(from_unixtime(cast(time as bigint))) as hour,
minute(from_unixtime(cast(time as bigint))) as minute,
second(from_unixtime(cast(time as bigint))) as second
from (select json_tuple(json, 'movie','rate','time','userid') as (movie, rate,time, userid) from rating_json	) t limit 10;
panxuej_shenx
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
hive-3
leezsj的博客
07-13 550
⼆级分区 ⼆级分区指的是在⼀张表中有两个分区,创建和插⼊时都要指定两个分区名,最常⻅的就 是下⾯案例的年和⽉,创建的语法和流程都是和⼀级分区⼀样,只是多⼀个分区⽽已. create table if not exists part2( id int, name string ) partitioned by (year string,month string) row format delimited fields terminated by ','; load data local inpath
hive3
weixin_44374374的博客
04-05 1648
作业： 1.自己建立多级分区，并观察hdfs上的目录 以分区顺序建立文件目录 2.lateral view的用法 平铺数据，解析数组 select case when phone is not null then phone else '000000' end as phone from json LATERAL VIEW json_tuple(line,'phone') t as phone 3.json_tuple和get_json_object的区别，尤其是性能方面 select json_tupl
Hive 报错 Invalid column reference 列名
热门推荐
木下瞳的博客
04-02 4万+
两张表 当我执行 select m.movieid,m.moviename,substr(m.moviename,-5,4) as years,avg(r.rate) as avgScore FROM t_movie as m join t_rating as r on m.movieid = r.movieid GROUP by m.movieid HAVING avgSc...
hive中使用round报错 Invalid column reference...
七七的博客
11-01 6892
hive查询sql必须加GROUP BY，否则选择列表中的列 col 无效，因为该列没有包含在聚合函数或 GROUP BY子句中。 通常来说count、round这种应该算作是聚合函数，不用 GROUP BY； 但是hive对round使用不认为其为聚合函数，使用其中的字段应该添加到GROUP BY中，round内使用的字段需要加GROUP BY； 否则报错 Invalid column reference ...
Hadoop 1.X & Hadoop 2.X 默认端口对比
二十三小时
11-02 2068
1，Hadoop 2.X 默认端口 组件 节点 默认端口 配置 用途说明 HDFS DataNode 50010 dfs.datanode.address datanode服务端口，用于数据传输 HDFS DataNode 50075 dfs.datanode.http.address http服务的端口 HDFS Data
Hive 编程专题 一 ： 使用正则指定输出列
wujiandao的专栏
11-04 1424
环境： Hive: 2.7.7 Oracle SQL Developer Cloudera JDBC Driver 案例： select type,nameobject,`*date` from tblobj2 limit 10 ; 错误: 1 - 使用 Oracle SQL Developer 执行上述 HQL 语句报错： [Cloudera][HiveJDBCDriver](500051...
hive3.x编译spark3.x包
06-07
标题“hive3.x编译spark3.x包”表明我们要讨论的是关于如何在Hive 3.x版本上编译与之兼容的Spark 3.x版本的二进制包。这个过程通常涉及到以下步骤： 1. **环境准备**：首先确保你已经安装了Java开发环境（JDK），...
第6章：Hive性能优化及Hive3新特性1
08-08
【Hive性能优化及Hive3新特性】 在大数据处理领域，Hive作为一个基于Hadoop的数据仓库工具，常用于大规模数据处理和分析。本章节主要探讨如何优化Hive的性能，以及Hive3引入的新特性。 1. **分区表与分桶表优化** ...
大数据：hive3
01-07
hive里面的类型转换 select '1'+2 , cast( '1' as int ) +2; OK 3.0 3 ------------------------------------------------------------------------------------------- 显示数据库 show databases; OK default ...
Hive常见错误及解决方案.docx
10-26
Hive常见错误及解决方案.docx
Hadoop（HDFS、YARN、HBase、Hive和Spark等）默认端口表
10-29
端口 作用 9000 fs.defaultFS，如：hdfs://172.25.40.171:9000 9001 dfs.namenode.rpc-address，DataNode会连接这个端口 50070 dfs.namenode.http-address 50470 dfs.namenode.https-address 50100 dfs.namenode.backup.address 50105 dfs.namenode.backup.http-address 50090 dfs.namenode.secondary.http-address，如：172.25.39.166:50090 50091 dfs.namenode.secondary.https-address，如：172.25.39.166:50091 50020 dfs.datanode.ipc.address 50075 dfs.datanode.http.address 50475 dfs.datanode.https.address 50010 dfs.datanode.address，DataNode的数据传输端口 8480 dfs.journalnode.rpc-address 8481 dfs.journalnode.https-address 8032 yarn.resourcemanager.address
Hive3.1.2编译源码
05-20
使用hive3.1.2和spark3.0.0配置hive on spark的时候，发现官方下载的hive3.1.2和spark3.0.0不兼容，hive...除了兼容spark3.0.0外，还将hive3.1.2的guava的版本进行了提升，和hadoop3.x保持一致，以便兼容hadoop3.1.3。
cdh6.3.2升级hive至3.x后登陆HiveServer2连接失败.doc
最新发布
03-01
### CDH 6.3.2 升级 Hive 至 3.x 后登录 HiveServer2 连接失败问题分析及解决方法 #### 一、问题背景与现象 在将 Cloudera Data Hub (CDH) 6.3.2 版本中的 Hive 升级到 3.x 版本后，用户在尝试通过 JDBC 连接到 ...
Hive SQL语法报错 及相应解决方法
大数据开发实践者
02-21 1万+
Hive SQL语法与经常用的mysql语法具有一定差异性，按照写mysql的习惯写出的sql经常报错，且报错很难看出问题原因，因此在此记录出现问题的现象和解决方式 Error: Error while compiling statement: FAILED: ParseException line 3:2 cannot recognize input near '(' 'SELECT' 'a'...
遇到一个hive-2.1的bug
北方的大数据之路
11-19 1112
sql语句 create table temp.tmp_c1a62d85598c47e0a23a4620348385f4xray202011181 STORED AS TEXTFILE as select userid from (select userid ,count(distinct poolid) as pool_cnt from dwd_pay_market where poolid in (1839,1840
工作中遇到的hive常见报错及问题
CSDN
11-25 1万+
1，执行#hive命令进入Hive CLI时报如下错误： Exception in thread "main" java.lang.RuntimeException: java.lang.RuntimeException: Unable to instantiate org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient at org.apache.hadoop.hive.ql.session.SessionState....
HIVE修改默认端口
weixin_43159039的博客
12-22 6049
HIVE修改默认端口
【FLINK】flink 读取Kafka数据写入Hive表中
Zsigner的博客
05-09 5527
背景：对实时的历史数据持久化保存到Hdfs中，可以使用Hive Sql 追溯历史数据给业务参考多维度指标 版本信息：Flink 1.11.2，Scala 2.11，Kafka 2.12，Hive 2.3.7 1、引入pom.xml内容 <?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://ww...
"Hive性能优化及Hive3新特性学习目标与表设计优化
同时，还介绍了Hive中三种索引类型的选择和使用场景。了解了索引的设计和应用场景后，可以在实际使用中选择适合的索引类型，提高查询效率。 在Hive中文件格式与数据压缩的优化方面，本章介绍了Hive中常用的文件格式...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值