hive sql 输入网段输出广播地址和网络地址

最新推荐文章于 2022-06-18 19:30:58 发布

LEGO886

最新推荐文章于 2022-06-18 19:30:58 发布

阅读量2.1k

点赞数

分类专栏：数据开发文章标签： sql hive

本文链接：https://blog.csdn.net/weixin_40925318/article/details/123631546

版权

数据开发专栏收录该内容

3 篇文章 0 订阅

订阅专栏

这篇博客介绍了如何利用HiveSQL从输入的网段地址（如101.132.10.0/24）中提取网络地址和广播地址。通过复杂的SQL转换和位运算，可以计算出网络地址和广播地址的整数形式，并进一步转化为标准的IP地址格式。这种方法对于理解网络地址计算和IP地址范围的确定非常有帮助。

摘要由CSDN通过智能技术生成

需求：输入网段地址，输出网络地址和广播地址

输入：
101.132.10.0/24

输出：
网络地址：101.132.10.0
广播地址：101.132.10.255

有了这两个地址，就可以把所有可用ip求出来了

hivesql：

--  网络地址 整数形式
select cast(conv(CONCAT(lpad(conv(cast(split(split("101.132.10.0/24","/")[0],"\\.")[0] as bigint),10, 2),8,"0") ,lpad(conv(cast(split(split("101.132.10.0/24","/")[0],"\\.")[1] as bigint),10, 2),8,"0") ,lpad(conv(cast(split(split("101.132.10.0/24","/")[0],"\\.")[2] as bigint),10, 2),8,"0") ,lpad(conv(cast(split(split("101.132.10.0/24","/")[0],"\\.")[3] as bigint),10, 2),8,"0") )
,2, 10) as bigint)  & cast(conv(rpad(rpad( "",cast(split("101.132.10.0/24","/")[1] as bigint),"1"),32,"0"),2, 10) as bigint)

-- 广播地址 整数形式
select (cast(conv(CONCAT(lpad(conv(cast(split(split("101.132.10.0/24","/")[0],"\\.")[0] as bigint),10, 2),8,"0") ,lpad(conv(cast(split(split("101.132.10.0/24","/")[0],"\\.")[1] as bigint),10, 2),8,"0") ,lpad(conv(cast(split(split("101.132.10.0/24","/")[0],"\\.")[2] as bigint),10, 2),8,"0") ,lpad(conv(cast(split(split("101.132.10.0/24","/")[0],"\\.")[3] as bigint),10, 2),8,"0") )
,2, 10) as bigint)  & cast(conv(rpad(rpad( "",cast(split("101.132.10.0/24","/")[1] as bigint),"1"),32,"0"),2, 10) as bigint))
+ pow(2, 32 - split("101.132.10.0/24","/")[1] ) - 1