=Start=

缘由：

趁着不太忙的时候整理总结一下 Hive SQL 的基本用法，避免遗忘。

正文：

参考解答：

1. Hive SQL中的 inner join 的写法

大部分时候，我还是 left outer join 写的多（用于补充字段），inner join写的少，对于它到底该如何写不是太确定（表关联条件如果位置放错了，会先做笛卡尔积，再过滤），这里借助大模型再系统性的整理总结一下，给我自己后面形成一个统一的模式，就不容易出错了。

在（from之后，where之前）进行join并通过on指定关联条件。

SELECT
a.*,b.*
FROM table_a a
INNER JOIN table_b b ON a.join_key = b.join_key -- 在（from之后，where之前）进行join并通过on指定关联条件
...
WHERE
...

question
Hive SQL里面的多表inner join关联，如果要关联的每一张表都是一个分区表，且分别有一些除了分区之外的过滤条件，SQL该如何写呢？

answer
当你在Hive SQL中对多个分区表进行INNER JOIN关联，并且每张表不仅有基于分区的过滤条件，还有其他非分区字段的过滤条件时，可以在WHERE子句中同时指定这两种类型的过滤条件。这样做不仅可以利用分区来减少查询扫描的数据量，还可以通过其他过滤条件进一步缩小结果集。

假设你有三个分区表：table_a、table_b和table_c，它们分别根据字段partition_field_a、partition_field_b和partition_field_c进行分区。此外，每个表还有一些其他的过滤条件，比如filter_field_a、filter_field_b和filter_field_c。以下是一个示例SQL语句展示如何编写这样的查询：

SELECT a.*, b.*, c.*
FROM table_a a
INNER JOIN table_b b ON a.join_key = b.join_key -- 在（from之后，where之前）进行join并通过on指定关联条件
INNER JOIN table_c c ON b.join_key = c.join_key
WHERE a.partition_field_a = 'desired_value_a'  -- 分区过滤条件
  AND b.partition_field_b = 'desired_value_b'  -- 分区过滤条件
  AND c.partition_field_c = 'desired_value_c'  -- 分区过滤条件
  AND a.filter_field_a = 'some_value_a'        -- 非分区字段过滤条件
  AND b.filter_field_b = 'some_value_b'        -- 非分区字段过滤条件
  AND c.filter_field_c = 'some_value_c';       -- 非分区字段过滤条件

在这个例子中：

• join_key代表用于连接各表的关键字段。
• 'desired_value_a', 'desired_value_b', 和 'desired_value_c'是你为各个表设定的分区值，这确保了只有符合条件的分区会被扫描，从而提高了查询效率。
• 'some_value_a', 'some_value_b', 和 'some_value_c'是应用在非分区字段上的额外过滤条件，帮助进一步精确你的查询结果。

这种方法允许你充分利用分区的优势来优化查询性能，同时也通过添加更多的过滤条件来细化结果集。记得调整表名、字段名及具体值以匹配你的实际情况。这种查询方式对于大规模数据处理尤其重要，因为它可以帮助显著减少需要处理的数据量，提高查询执行的速度。

2. Hive SQL多表联合查询的写法

-- 两表单条件
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
SELECT a.val, b.val, c.val
FROM a
JOIN b ON (a.key = b.key1)
JOIN c ON (c.key = b.key2)

3. 较长正则表达式的处理方法

使用 set 语法先定义一个变量，对正则表达式内容的修改直接在一个地方进行，其它地方直接使用即可，避免内容修改不同步。

/*
一个数据分析场景，找出内部系统中可能和搜索、大数据平台、数据库查询、代理等功能有关的域名

一般情况下就是先根据经验，给出一些关键字去做匹配，然后人工筛选、确认匹配且有效的域名。
所以这里面涉及到2步，第1步，根据正则表达式进行匹配；第2步，根据总结出来的白名单排除一些已知错误的匹配结果。
*/
set match_list='search|data|db|proxy|bi'; -- 后面变量的使用时需要用 ${val_name} 将变量名val_name括起来
set white_list='feedback|feedbcak|sandbox';

with t1 as (SELECT
'data.corp.com,db.corp.com,bi.corp.com,feedback.corp.com,sandbox.corp.com,adb.corp.com' as domain_list
)

SELECT
domain
,if(domain rlike ${match_list} and domain not rlike ${white_list},1,0) as ret
,${match_list},${white_list}
,domain_list
FROM t1
lateral view explode(split(domain_list,',')) domain as domain

where
domain rlike ${match_list}
and domain not rlike ${white_list}

参考链接：

Hive SQL中的full outer join使用学习
https://ixyzero.com/blog/archives/5465.html

[一起学Hive]之十一-Hive中Join的类型和用法
http://lxw1234.com/archives/2015/06/315.htm

Hive SQL学习整理
https://ixyzero.com/blog/archives/4245.html

=END=