hivesqlin查询
❶ sql中的in、exists和join的查询原理
Hive中的谓词下推是一种优化技术，其目标是在查询过程中尽可能地将过滤条件下推到数据源中执行，以减少数据的扫描和处理。然而，这一优化技术有时会导致查询结果的不一致性。
让我们通过四个典型的SQL查询来探讨这一问题及其原因。在这四个查询中，结果的数值有所不同，虽然它们的基本逻辑一致，但条件的放置位置有所不同，从而导致了结果的不一致。
SQL1和SQL3的结果为：20672和9721，这表明它们在处理数据时采用了不同的谓词下推策略。SQL2和SQL4的结果为：9721和184125，这进一步说明了条件放置位置对查询结果的影响。
Hive谓词下推的核心概念是将过滤条件从SQL语句中下推到数据源执行，从而减少了不必要的数据处理。谓词下推在Join操作中的应用主要遵循以下规则：
1. 对于Inner Join和Full outer Join，条件应写在ON语句后或WHERE语句后，谓词下推的性能影响不大。然而，Join操作的性能主要取决于谓词下推的效率。
2. 对于Left outer Join，当左侧表的条件写在WHERE语句中，而右侧表的条件写在ON语句中时，谓词下推可以提高性能。
3. 对于Right outer Join，情况相反，左侧表条件写在ON语句中，而右侧表条件写在WHERE语句中时，谓词下推同样可以提升性能。
这一策略的影响在于控制了数据处理的顺序，从而影响了最终的查询结果。在使用谓词下推时，需要根据查询的预期结果和数据的结构来灵活调整条件的位置，以确保优化的同时不影响查询的正确性。
总结而言，谓词下推是一种强大的查询优化工具，但其应用需要结合具体的数据结构和查询需求来调整。理解谓词下推的规则和限制，有助于在查询优化过程中做出更明智的选择，从而实现更高效、准确的数据查询。
❷ 大数据的核心技术有哪些
大数据技术的体系庞大且复杂，基础的技术包含数据的采集、数据预处理、分布式存储、数据库、数据仓库、机器学习、并行计算、可视化等。
1、数据采集与预处理：FlumeNG实时日志收集系统，支持在日志系统中定制各类数据发送方，用于收集数据；Zookeeper是一个分布式的，开放源码的分布式应用程序协调服务，提供数据同步服务。
2、数据存储：Hadoop作为一个开源的框架，专为离线和大规模数据分析而设计，HDFS作为其核心的存储引擎，已被广泛用于数据存储。HBase，是一个分布式的、面向列的开源数据库，可以认为是hdfs的封装，本质是数据存储、NoSQL数据库。
3、数据清洗：MapRece作为Hadoop的查询引擎，用于大规模数据集的并行计算。
4、数据查询分析：Hive的核心工作就是把SQL语句翻译成MR程序，可以将结构化的数据映射为一张数据库表，并提供HQL(HiveSQL)查询功能。Spark启用了内存分布数据集，除了能够提供交互式查询外，它还可以优化迭代工作负载。
5、数据可视化：对接一些BI平台，将分析得到的数据进行可视化，用于指导决策服务。
❸ 数据库有哪些
目前比较常见的数据库：
SQL是用于访问和处理数据库的标准的计算机语言。
MySQL是小型的开源的关系型数据库管理系统。
SQL Server 是 Microsoft 开发的关系数据库管理系统。
Oracle数据库系统是目前世界上流行的关系数据库管理系统。
DB2是关系型数据库平台，其采用多进程多线索的结构，支持多用户或应用程序在同一条SQL 语句中查询不同数据库和数据。
PostgreSQL 是一个对象-关系数据库服务器，号称 "世界上最先进的开源关系型数据库"。
Hadoop是个很流行的分布式计算解决方案，Hive是基于hadoop的数据仓库工具，hive 构建在基于静态批处理的Hadoop 之上。
GreenPlum采用了MPP(大规模并行处理)，是一个由多个独立的数据库服务组合成关系型数据库集群。
ECharts 是一个使用 JavaScript 实现的开源可视化库，涵盖各行业图表。
R是一种集统计分析与图形显示为一体的统计分析软件，具有很强的互动性。
目前，这些数据库都在树懒学堂有相关教程，可以跟着一步一步学习
❹ hive not in 通过什么实现
目前hive不支持 in或not in 中包含查询子句的语法，所以只能通过left join实现。
假设有一个登陆表login(当天登陆记录,只有一个uid),和一个用户注册表regusers(当天注册用户，字段只有一个uid)，这两个表都包含一个字段,uid。
in查询
如果要查询当天登陆的注册用户，需要用in查询，hive sql如下:
select login.uid from login left outer join regusers on login.uid=regusers.uid where regusers.uid is not null
如果login表和regusers表按天分区，字段是dt，那么查询2013年1月1号当天登陆的注册用户，hive sql如下:
select login.uid from login day_login left outer join
(select uid from regusers where dt='20130101') day_regusers
on day_login.uid=day_regusers.uid where day_login.dt='20130101' and day_regusers.uid is not null
not in查询
如果要查询当天登陆的老用户(这里假设非当天注册用户就是老用户)，需要用not in查询，hive sql如下:
select login.uid from login left outer join regusers on login.uid=regusers.uid where regusers.uid is null;
如果login表和regusers表按天分区，字段是dt，那么查询2013年1月1号当天登陆的老用户，hive sql如下:
select login.uid from login day_login left outer join
(select uid from regusers where dt='20130101') day_regusers
on day_login.uid=day_regusers.uid where day_login.dt='20130101' and day_regusers.uid is null;
Hive join优化
========================================================
由
于 hive 与传统关系型数据库面对的业务场景及底层技术架构都有着很大差异，因此，传统数据库领域的一些技能放到 Hive 中可能已不再适用。关于
hive 的优化与原理、应用的文章，前面也陆陆续续的介绍了一些，但大多都偏向理论层面，本文就介绍一个实例，从实例中一步步加深对 hive
调优的认识与意识。
1、需求
需求我做了简化，很简单，两张表做个 join，求指定城市，每天的 pv，用传统的 RDBMS SQL 写出来就这样的：
?
1
2
3
4
5
6
7
8
9
10
11
SELECT t.statdate,
c.cname,
count(t.cookieid)
FROM tmpdb.city c
JOIN ecdata.ext_trackflow t ON (t.area1= c.cname
OR t.area2 =c.cname
OR t.area3 = c.cname)
WHERE t.statdate>='20140818' and t.statdate<='20140824'
AND platform='pc'
GROUP BY t.statdate,
c.cname;
怎么样？根据 SQL 看懂需求没问题吧？
2、非等值 join 问题
然后把这条 SQL 贴到 hive 中去执行，然后你会发现报错了：
?
1
FAILED: SemanticException [Error 10019]: Line 5:32 OR not supported in JOIN currently 'cname'
这是因为 hive 受限于 MapRece 算法模型，只支持 equi-joins（等值 join），要实现上述的非等值 join，你可以采用笛卡儿积（ full Cartesian proct ）来实现：
?
1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT t.statdate,
c.cname,
count(t.cookieid)
FROM tmpdb.city c
JOIN ecdata.ext_trackflow t
WHERE t.statdate>='20140818'
AND t.statdate<='20140824'
AND platform='pc'
AND (t.area1= c.cname
OR t.area2 =c.cname
OR t.area3 = c.cname)
GROUP BY t.statdate,
c.cname;
然后再拿着这条语句执行下。
3、优化：rece side join VS Cartesian proct
如果你真的把这条语句放到 Hive 上执行，然后恰好你有张表还非常大，那么恭喜你。。。集群管理员估计会找你的麻烦了。。。
友情提示：笛卡儿积这种语句在 Hive 下慎用，大数据场景下的 m * n 映射结果你懂的。。。对此，Hive 特意提供了一个环境变量：hive.mapred.mode=strict; 防止笛卡儿积的执行：
?
1
FAILED: SemanticException [Error 10052]: In strict mode, cartesian proct is not allowed. If you really want to perform the operation, set hive.mapred.mode=nonstrict
从 2 中的观察得知我们在 on 后面跟 join
条件，走的是 rece side join，如果你在 where 后跟则是走 Cartesian proct，但是这里单条 sql
又没法实现 rece side join，还有没有其它办法呢？
4、改写非等值 join：union all
既然不允许非等值 join，那我们换一下思路，多个子查询 union all，然后汇总：
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
SELECT dt,
name,
count(cid)
FROM
(SELECT t.statdate dt,
c.cname name,
t.cookieid cid
FROM tmpdb.city c
JOIN ecdata.ext_trackflow t ON t.area1 =c.cname
WHERE t.statdate>='20140818'
AND t.statdate<='20140824'
AND platform='pc'
UNION ALL SELECT t.statdate dt,
c.cname name,
t.cookieid cid
FROM tmpdb.city c
JOIN ecdata.ext_trackflow t ON t.area2 =c.cname
WHERE t.statdate>='20140818'
AND t.statdate<='20140824'
AND platform='pc'
UNION ALL SELECT t.statdate dt,
c.cname name,
t.cookieid cid
FROM tmpdb.city c
JOIN ecdata.ext_trackflow t ON t.area3 =c.cname
WHERE t.statdate>='20140818'
AND t.statdate<='20140824'
AND platform='pc') tmp_trackflow
GROUP BY dt,
name;
5、优化：map side join
上述语句走的是 rece side join，从我们的需求及业务得知，tmpdb.city 是一张字典表，数据量很小，因此我们可以试试把上述的语句改写成 mapjoin：
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
SELECT dt,
name,
count(cid)
FROM
(SELECT /*+ MAPJOIN(c) */ t.statdate dt,
c.cname name,
t.cookieid cid
FROM tmpdb.city c
JOIN ecdata.ext_trackflow t ON t.area1 =c.cname
WHERE t.statdate>='20140818'
AND t.statdate<='20140824'
AND platform='pc'
UNION ALL SELECT /*+ MAPJOIN(c) */ t.statdate dt,
c.cname name,
t.cookieid cid
FROM tmpdb.city c
JOIN ecdata.ext_trackflow t ON t.area2 =c.cname
WHERE t.statdate>='20140818'
AND t.statdate<='20140824'
AND platform='pc'
UNION ALL SELECT /*+ MAPJOIN(c) */ t.statdate dt,
c.cname name,
t.cookieid cid
FROM tmpdb.city c
JOIN ecdata.ext_trackflow t ON t.area3 =c.cname
WHERE t.statdate>='20140818'
AND t.statdate<='20140824'
AND platform='pc') tmp_trackflow
GROUP BY dt,
name;
6、优化无极限：开启 parallel 和 控制 rece 个数
上述语句执行时，你可以看到执行计划和状态信息，以及结合你的 union all 语句可知，三个 union 语句之间没有依赖关系，其实是可以并行执行的：
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
explain SQL...
...
STAGE DEPENDENCIES:
Stage-11 is a root stage
Stage-1 depends on stages: Stage-11
Stage-2 depends on stages: Stage-1
Stage-3 depends on stages: Stage-2, Stage-6, Stage-9
Stage-12 is a root stage
Stage-5 depends on stages: Stage-12
Stage-6 depends on stages: Stage-5
Stage-13 is a root stage
Stage-8 depends on stages: Stage-13
Stage-9 depends on stages: Stage-8
Stage-0 is a root stage
...
我们在 SQL 前加上如下环境变量选项：
?
1
2
set mapred.rece.tasks=60;
set hive.exec.parallel=true;
让执行计划中的 Stage-11、Stage-12、Stage-13 并行执行，并控制好 rece task 个数。
完整的语句如下：
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
hive -e "
SET mapred.rece.tasks=60;
SET hive.exec.parallel=TRUE;
SELECT dt,
name,
count(cid)
FROM
(SELECT /*+ MAPJOIN(c) */ t.statdate dt,
c.cname name,
t.cookieid cid
FROM tmpdb.city c
JOIN ecdata.ext_trackflow t ON t.area1 =c.cname
WHERE t.statdate>='20140818'
AND t.statdate<='20140824'
AND platform='pc'
UNION ALL SELECT /*+ MAPJOIN(c) */ t.statdate dt,
c.cname name,
t.cookieid cid
FROM tmpdb.city c
JOIN ecdata.ext_trackflow t ON t.area2 =c.cname
WHERE t.statdate>='20140818'
AND t.statdate<='20140824'
AND platform='pc'
UNION ALL SELECT /*+ MAPJOIN(c) */ t.statdate dt,
c.cname name,
t.cookieid cid
FROM tmpdb.city c
JOIN ecdata.ext_trackflow t ON t.area3 =c.cname
WHERE t.statdate>='20140818'
AND t.statdate<='20140824'
AND platform='pc') tmp_trackflow
GROUP BY dt,
name;
" > a1.txt
最后的优化效果是：2 中的语句三个小时没出结果。。。5 比 4 快 8 倍左右，6 比 5 快 2 倍左右，最终 10min 出结果。
❺ HIVE：查询语句：有异常抛出，执行结束。 求大神解决
你的SQL不规范，写有的问题，缺少GROUP关键字。
SQL结尾需要加上 group by user_id