I have two tables:
items
CREATE TABLE items (
ID int,
TXT string,
CODE string
);
INSERT INTO items VALUES (1,'AA BB CC','ZZ-100');
INSERT INTO items VALUES (2,'BB CC DD','ZZ-200');
INSERT INTO items VALUES (3,'AA CC EE','ZZ-300');
INSERT INTO items VALUES (4,'EE FF GG','ZZ-400');
INSERT INTO items VALUES (5,'CC HH II','ZZ-500');
+----+----------+--------+
| id | txt | code |
+----+----------+--------+
| 1 | AA BB CC | ZZ-100 |
| 2 | BB CC DD | ZZ-200 |
| 3 | AA CC EE | ZZ-300 |
| 4 | EE FF GG | ZZ-400 |
| 5 | CC HH II | ZZ-500 |
+----+----------+--------+
And regex_table:
CREATE TABLE regex_table (
ID int,
REGEXSTR string,
CODE string
);
INSERT INTO regex_table VALUES(1,'AA','ZZ-100');
INSERT INTO regex_table VALUES(1,'CC','ZZ-100');
INSERT INTO regex_table VALUES(2,'AA','ZZ-100');
INSERT INTO regex_table VALUES(2,'BB','ZZ-200');
INSERT INTO regex_table VALUES(2,'CC','ZZ-200');
INSERT INTO regex_table VALUES(3,'DD','ZZ-100');
INSERT INTO regex_table VALUES(3,'DD','ZZ-300');
+----+----------+--------+
| id | regexstr | code |
+----+----------+--------+
| 1 | AA | ZZ-100 |
| 1 | CC | ZZ-100 |
| 2 | BB | ZZ-200 |
| 2 | AA | ZZ-100 |
| 2 | CC | ZZ-200 |
| 3 | DD | ZZ-100 |
| 3 | DD | ZZ-300 |
+----+----------+--------+
I would like to replace items.txt with search string in regex_table.regexstr depending on if the id and code are equal.
For example:
Scenario 1: If id=1, the code is ZZ-100, therefore the search string is AA|CC:
SELECT id,regexp_replace(txt,'AA|CC','<NA>'),code from items where id=1;
+----+--------------------------------------+--------+
| id | regexp_replace(txt, 'aa|cc', '<na>') | code |
+----+--------------------------------------+--------+
| 1 | <NA> BB <NA> | ZZ-100 |
+----+--------------------------------------+--------+
Scenario 2: If id=2, the code is ZZ-200, therefore the search string is BB|CC:
SELECT id,regexp_replace(txt,'BB|CC','<NA>'),code from items where id=2;
+----+--------------------------------------+--------+
| id | regexp_replace(txt, 'bb|cc', '<na>') | code |
+----+--------------------------------------+--------+
| 2 | <NA> <NA> DD | ZZ-200 |
+----+--------------------------------------+--------+
Scenario 3: If id=4, the code is ZZ-300, therefore the search string is DD:
SELECT id,regexp_replace(txt,'DD','<NA>'),code from items where id=3;
+----+-----------------------------------+--------+
| id | regexp_replace(txt, 'dd', '<na>') | code |
+----+-----------------------------------+--------+
| 3 | AA CC EE | ZZ-300 |
+----+-----------------------------------+--------+
So basically the search string has to be dynamic depending on id and code from another table.
Is there a way to do this in one query either in Impala (important) and Hive (less important)?
NOTE:
The id and code could be dynamic and added in both tables (so no way to hardcode into SQL). It has to be queried.
I try to avoid doing JOIN. I am wondering if there is a way to do subquery.
One idea is to pass a full string that contains concat Regex search string and then use some Regex tricks to remove 'id' and 'code' not relevant to the row.
UPDATE 1
I tried this:
SELECT i.id, regexp_replace(txt, pattern, '<NA>'), i.code FROM items i INNER JOIN (SELECT id, group_concat('|', regexstr) AS pattern, regex_table.code FROM regex_table GROUP BY regex_table.id, regex_table.code) r ON r.id = i.id AND r.code = i.code;
And got this:
+----+----------------------------------------------+--------+
| id | regexp_replace(txt, pattern, '<na>') | code |
+----+----------------------------------------------+--------+
| 1 | <NA>A<NA>A<NA> <NA>B<NA>B<NA> <NA> | ZZ-100 |
| 3 | <NA>A<NA>A<NA> <NA>C<NA>C<NA> <NA>E<NA>E<NA> | ZZ-300 |
| 2 | <NA>B<NA>B<NA> <NA> <NA>D<NA>D<NA> | ZZ-200 |
+----+----------------------------------------------+--------+
UPDATE 2
I got it to work
SELECT o.id,
o.code,
items.txt,
o.regexstr,
IF(o.regexstr IS NOT NULL, regexp_replace(items.txt, o.regexstr,
'<NA>'), items.txt) masked
FROM items
LEFT JOIN (SELECT i.id id,
i.code code,
group_concat(r.regexstr, '|') regexstr
FROM items i
left join (SELECT id,
regexstr,
regex_table.code
FROM regex_table) r
ON r.id = i.id
AND r.code = i.code
GROUP BY i.id,
i.code) o
ON items.id = o.id
AND items.code = o.code;
Output:
+----+--------+----------+----------+--------------+
| id | code | txt | regexstr | masked |
+----+--------+----------+----------+--------------+
| 5 | ZZ-500 | CC HH II | NULL | CC HH II |
| 2 | ZZ-200 | BB CC DD | BB|CC | <NA> <NA> DD |
| 4 | ZZ-400 | EE FF GG | NULL | EE FF GG |
| 3 | ZZ-300 | AA CC EE | DD | AA CC EE |
| 1 | ZZ-100 | AA BB CC | CC|AA | <NA> BB <NA> |
+----+--------+----------+----------+--------------+
But it seems rather "complicated". Any idea to make it more concise?
SELECT o.id,
o.code,
items.txt,
o.regexstr,
IF(o.regexstr IS NOT NULL, regexp_replace(items.txt, o.regexstr,
'<NA>'), items.txt) masked
FROM items
LEFT JOIN (SELECT i.id id,
i.code code,
group_concat(r.regexstr, '|') regexstr
FROM items i
left join (SELECT id,
regexstr,
regex_table.code
FROM regex_table) r
ON r.id = i.id
AND r.code = i.code
GROUP BY i.id,
i.code) o
ON items.id = o.id
AND items.code = o.code;
Output:
+----+--------+----------+----------+--------------+
| id | code | txt | regexstr | masked |
+----+--------+----------+----------+--------------+
| 5 | ZZ-500 | CC HH II | NULL | CC HH II |
| 2 | ZZ-200 | BB CC DD | BB|CC | <NA> <NA> DD |
| 4 | ZZ-400 | EE FF GG | NULL | EE FF GG |
| 3 | ZZ-300 | AA CC EE | DD | AA CC EE |
| 1 | ZZ-100 | AA BB CC | CC|AA | <NA> BB <NA> |
+----+--------+----------+----------+--------------+