Hive SQL中如何进行列之间的模糊比较


=Start=

缘由:

在处理SSO账号安全的问题时,单步核心在于对SSO认证日志的分析(深层次的还需要结合用户日常行为分析,属于安全UEBA的范畴)。这里的大体逻辑在于比较SSO登录账号与IP关联账号和设备关联账号是否相同?是否同组?如果不同就需要关注(当然同组不同人也需要关注,不过相对而言风险低一点,也有一定的业务实际需求在里面,虽然也不太合理,但总体风险可控,可以放在后面处理)。

这里主要说一下在Hive SQL中如何进行多列之间的比较,以实现上面的逻辑。

正文:

参考解答:

现在假设:SSO登录账号为账号a(部门a)、IP关联账号为账号b(部门b)、设备关联账号为账号c(部门c)。账号是否相同(和部门是否相同)直接用字符串比较就行,即 a==b 和 a==c 。但是是否同组这个就不能直接比较了,需要用到类似于字符串查找/匹配来实现,在编程语言中实现起来比较简单,这里简单记录一下在Hive SQL中如何实现,方便以后参考。

假设表里现在主要有以下几个字段:

`timestr`
,`action`
,`ip`
,`sso_user`
,`sso_user_org`
,`ip_user`
,`ip_user_org`
,`device_user`
,`device_user_org`
,`sso_user_base`
,`ip_location`
,`ip_attributes`
,...

其中 xxx_org 类的数据是 l1,l2,l3,l4,l5 这种逗号连接起来的字符串,类似于下面这种

`ip_user_org` = 'l1,l2,l3,l4,l5'

substring_index(`ip_user_org`,',',1) as ip_user_1st_org,    -- l1
substring_index(`ip_user_org`,',',2) as ip_user_2_org,    -- l1,l2
substring_index(`ip_user_org`,',',3) as ip_user_3_org,    -- l1,l2,l3
regexp_extract(`ip_user_org`, '(.+?),([^,]{1,})$', 1) as ip_user_x_org,    -- l1,l2,l3,l4
substring_index(`ip_user_org`,',',-1) as ip_user_last_org,    -- l5

补充一下之前记录的一个字符串截取方法:

Hive SQL中如何截取「变长」「特定分隔符」字符串的除最后一段内容之外的内容?
如果格式确定对话,建议使用 regexp_extract 进行提取
;除此之外,还可以用 substring_index 来操作,从前往后或从后往前取固定数量分隔符的子串,实在不嫌麻烦再加一个 replace 就行。

比较两个(或多个)部门之间的关系,这里以 ip_user_org 和 sso_user_org 为例进行说明:

case
when ip_user_org = sso_user_org then 'a=b'
when ip_user_org like concat(sso_user_org,'%') then 'a_like_b'
when sso_user_org like concat(ip_user_org,'%') then 'a_like_b'
when substring_index(`ip_user_org`,',',3) = substring_index(`sso_user_org`,',',3) then 'a-b_common-3org'
else 'unknown'
end as org_relation,

# 上面这部分的核心在于将 column1/column2 的内容先转成字符串,然后用 concat 函数给添加一个 % 以便进行like模糊匹配。
# 如果要用 rlike 做正则匹配,也类似,需要注意的就是表示正则表达式的那一列内容需要提前处理好转义字符,否则达不到预期目标。

用一些简单的案例来模拟验证上面判断逻辑是否能满足目标:

# 有关系的例子

example1:

ip_user_org = 'l1,l2,l3,l4,l5'
sso_user_org = 'l1,l2,l3,l4,l5'

a=b

example2:

ip_user_org = 'l1,l2,l3,l4'
sso_user_org = 'l1,l2,l3,l4,l5'

ip_user_org like concat(sso_user_org,'%') -> false
sso_user_org like concat(ip_user_org,'%') -> true

example3:

sso_user_org = 'l1,l2,l3,l4'
ip_user_org = 'l1,l2,l3,l4,l5'

ip_user_org like concat(sso_user_org,'%') -> true
sso_user_org like concat(ip_user_org,'%') -> false

example4:

sso_user_org = 'l1,l2,l3,l4,lx'
ip_user_org = 'l1,l2,l3,l4,l5'

ip_user_org like concat(sso_user_org,'%') -> false
sso_user_org like concat(ip_user_org,'%') -> false
substring_index(`ip_user_org`,',',3) = substring_index(`sso_user_org`,',',3) -> true

example5:

sso_user_org = 'l1,l2,l3,l4,lx'
ip_user_org = 'l1,l2,l3,l4,l5'

ip_user_org like concat(sso_user_org,'%') -> false
sso_user_org like concat(ip_user_org,'%') -> false
substring_index(`ip_user_org`,',',3) = substring_index(`sso_user_org`,',',3) -> true

[不及预期]example6:

sso_user_org = 'l1,l2,lx'
ip_user_org = 'l1,l2,l3'

ip_user_org like concat(sso_user_org,'%') -> false
sso_user_org like concat(ip_user_org,'%') -> false
substring_index(`ip_user_org`,',',3) = substring_index(`sso_user_org`,',',3) -> false

# 没有关系的例子

example7:

sso_user_org = 'l1,l2,l3,l4,lx'
ip_user_org = 'a1,a2,a3,a4'

ip_user_org = sso_user_org -> false
ip_user_org like concat(sso_user_org,'%') -> false
sso_user_org like concat(ip_user_org,'%') -> false
substring_index(`ip_user_org`,',',3) = substring_index(`sso_user_org`,',',3) -> false

好了,暂时先记录到这里,后面再视情况写些新的内容。

补充一下网上搜到的方法:

# method 1
CASE
WHEN CONCAT('%',"B",'%') LIKE CONCAT('%',"A",'%')  THEN 1 
WHEN CONCAT('%',"A",'%') LIKE CONCAT('%',"B",'%') THEN 1
ELSE 0
END AS col_relation

# method 2
instr(column1, column2) !=0
参考链接:

从 SSO 账号安全监控说起
https://ixyzero.com/blog/archives/5329.html

How to compare two columns using partially match result to display
https://community.yellowfinbi.com/topic/how-to-compare-two-columns-using-partially-match-result-to-display

hive join tables and extracts value based on column values
https://community.cloudera.com/t5/Support-Questions/hive-join-tables-and-extracts-value-based-on-column-values/td-p/196804

How to compare two columns with different data type groups
https://stackoverflow.com/questions/58240566/how-to-compare-two-columns-with-different-data-type-groups

=END=

,

《“Hive SQL中如何进行列之间的模糊比较”》 有 3 条评论

  1. CAS Protocol
    https://apereo.github.io/cas/6.5.x/protocol/CAS-Protocol.html
    `
    CAS协议是一个简单而强大的基于票据的(ticket-based)协议。

    The CAS Protocol emulates the central ideas behind the Kerberos protocol. CAS协议吸收了Kerberos协议的核心思想。
    `
    Apereo CAS – Identity & Single Sign On for all earthlings and beyond.
    https://github.com/apereo/cas

    How CAS Works
    https://calnetweb.berkeley.edu/calnet-technologists/cas/how-cas-works
    https://calnetweb.berkeley.edu/sites/default/files/styles/panopoly_image_original/public/ProxyAuthSwimLanes.jpg?itok=Gt8bNvac&timestamp=1453698315

    Simple authentication with CAS
    https://calnetweb.berkeley.edu/calnet-technologists/cas/how-cas-works/simple-authentication-cas

    Single Sign On with CAS (Central Authentication Service)
    https://dinika-15.medium.com/single-sign-on-with-cas-central-authentication-service-fd60bae64fa7

  2. 统一认证 – Apereo CAS 简介
    https://mp.weixin.qq.com/s/Rp8g4opEPXiwB0NHShzoWQ

    统一认证 – Apereo CAS 小试
    https://mp.weixin.qq.com/s/TP8D_VhXuU0Xsj6wClhrOQ

    统一认证 – Apereo CAS 客户端的集成以及小结
    https://mp.weixin.qq.com/s/cpEmmAuyk-hTA4iZYUZjZg

    微服务架构下的统一身份认证和授权
    https://mp.weixin.qq.com/s/aWqiZtdpgRy_QRwytmcjiw

    Apereo CAS(一)在本地运行
    https://mp.weixin.qq.com/s/00tAQeHAj2WiO4fo9LGciw

    单点登录(SSO)中的CAS认证
    https://mp.weixin.qq.com/s/XsflVN85cBELmGNHzjxVfQ
    https://www.cnblogs.com/dtux/p/16697761.html

  3. 面试官:来说说单点登录的三种实现方式
    https://mp.weixin.qq.com/s/5pYNsd52gy1UkWWlUonG4g
    `
    # 前言

    在 B/S 系统中,登录功能通常都是基于 Cookie 来实现的。当用户登录成功后,一般会将登录状态记录到 Session 中,或者是给用户签发一个 Token,无论哪一种方式,都需要在客户端保存一些信息(Session ID 或 Token ),并要求客户端在之后的每次请求中携带它们。在这样的场景下,使用 Cookie 无疑是最方便的,因此我们一般都会将 Session 的 ID 或 Token 保存到 Cookie 中,当服务端收到请求后,通过验证 Cookie 中的信息来判断用户是否登录 。

    单点登录(Single Sign On, SSO)是指在同一帐号平台下的多个应用系统中,用户只需登录一次,即可访问所有相互信任的应用系统。举例来说,百度贴吧和百度地图是百度公司旗下的两个不同的应用系统,如果用户在百度贴吧登录过之后,当他访问百度地图时无需再次登录,那么就说明百度贴吧和百度地图之间实现了单点登录。

    单点登录的本质就是在多个应用系统中共享登录状态。如果用户的登录状态是记录在 Session 中的,要实现共享登录状态,就要先共享 Session,比如可以将 Session 序列化到 Redis 中,让多个应用系统共享同一个 Redis,直接读取 Redis 来获取 Session。

    当然仅此是不够的,因为不同的应用系统有着不同的域名,尽管 Session 共享了,但是由于 Session ID 是往往保存在浏览器 Cookie 中的,因此存在作用域的限制,无法跨域名传递,也就是说当用户在 app1.com 中登录后,Session ID 仅在浏览器访问 app1.com 时才会自动在请求头中携带,而当浏览器访问 app2.com 时,Session ID 是不会被带过去的。实现单点登录的关键在于,如何让 Session ID(或 Token)在多个域中共享。

    # 实现方式一:父域 Cookie
    如果将 Cookie 的 domain 属性设置为当前域的父域,那么就认为它是父域 Cookie。Cookie 有一个特点,即父域中的 Cookie 被子域所共享,换言之,子域会自动继承父域中的Cookie。

    总结:此种实现方式比较简单,但不支持跨主域名。

    # 实现方式二:认证中心-CAS
    我们可以部署一个认证中心,认证中心就是一个专门负责处理登录请求的独立的 Web 服务。
    用户统一在认证中心进行登录,登录成功后,认证中心记录用户的登录状态,并将 Token 写入 Cookie。(注意这个 Cookie 是认证中心的,应用系统是访问不到的。)
    应用系统检查当前请求有没有 Token,如果没有,说明用户在当前系统中尚未登录,那么就将页面跳转至认证中心。由于这个操作会将认证中心的 Cookie 自动带过去,因此,认证中心能够根据 Cookie 知道用户是否已经登录过了。如果认证中心发现用户尚未登录,则返回登录页面,等待用户登录,如果发现用户已经登录过了,就不会让用户再次登录了,而是会跳转回目标 URL ,并在跳转前生成一个 Token,拼接在目标 URL 的后面,回传给目标应用系统。
    应用系统拿到 Token 之后,还需要向认证中心确认下 Token 的合法性,防止用户伪造。确认无误后,应用系统记录用户的登录状态,并将 Token 写入 Cookie,然后给本次访问放行。(注意这个 Cookie 是当前应用系统的,其他应用系统是访问不到的。)当用户再次访问当前应用系统时,就会自动带上这个 Token,应用系统验证 Token 发现用户已登录,于是就不会有认证中心什么事了。

    总结:此种实现方式相对复杂,支持跨域,扩展性好,是单点登录的标准做法。

    # 实现方式三:LocalStorage 跨域
    前端通过 iframe+postMessage() 方式,将同一份 Token 写入到了多个域下的 LocalStorage 中,前端每次在向后端发送请求之前,都会主动从 LocalStorage 中读取 Token 并在请求中携带,这样就实现了同一份 Token 被多个域所共享。

    总结:此种实现方式完全由前端控制,几乎不需要后端参与,同样支持跨域。
    `

回复 abc 取消回复

您的电子邮箱地址不会被公开。 必填项已用*标注