一、需求
数据示例:
需求描述:
将x_forwared_for字段中的ip地址按逗号进行分割,转为多行数据,效果如下:
二、mysql
创建序列表table_id
CREATE TABLE `table_id` (`id` VARCHAR(255) DEFAULT NULL) ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO table_id VALUES ('1'),('2'),('3');
原表与序列表连接
SELECT
test.attack_name,
test.attack_ip,
SUBSTRING_INDEX(
SUBSTRING_INDEX(test.x_forwared_for,',',table_id.id)
,',',-1) AS XFF_ip
FROM test JOIN table_id ON table_id.id < (LENGTH(test.x_forwared_for) - LENGTH( REPLACE(test.x_forwared_for,',',''))+ 2)
三、spark SQL
原表与序列表连接
SELECT
test.attack_name,
test.attack_ip,
SUBSTRING_INDEX(
SUBSTRING_INDEX(test.x_forwared_for,',',table_id.id)
,',',-1) AS XFF_ip
FROM test JOIN table_id ON table_id.id < (LENGTH(test.x_forwared_for) - LENGTH( REGEXP_REPLACE(test.x_forwared_for,',',''))+ 2)