NOT IN replacement in elasticsearch sql query

Elastic Stack Elasticsearch
1

I have an sql query which filters tables with conditions like not in, i want the same query for elasticsearch sql access.

SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'AND TABLE_SCHEMA NOT IN(" + schema + ") AND TABLE_NAME LIKE '%" + text + "%' ORDER BY TABLE_NAME ASC".

Can anyone please provide any suitable solution. I learnt that in , not in or subqueries of advanced filtering are also not supported.

2

Hi,

You could try if rlike (see docs) is fast enough for your requirements:

SELECT
  TABLE_SCHEMA, TABLE_NAME
FROM 
  INFORMATION_SCHEMA.TABLES
WHERE
  TABLE_TYPE = 'BASE TABLE' AND
  TABLE_SCHEMA NOT RLIKE " + schema + " AND
  TABLE_NAME LIKE '%" + text + "%'
ORDER BY
  TABLE_NAME ASC

where schema is a string similar to: SYS|SCHEMA1|SCHEMA2

Best regards
Wolfram

3

No, I don't think rlike will suit my requirement, I want the extracted table to me not present in the specific schema..rlike is something like regex filtering for like..I don't want that

My current basic sql query is -- SHOW TABLES LIKE '%{table_name}%'

4

Then I am either missing something or your example is wrong - my code does exactly the same as yours:

TABLE_SCHEMA NOT IN(" + schema + ")

TABLE_SCHEMA NOT RLIKE " + schema + "

5

Hi,

The thing is the query I have given initially doesn't work in my elasticsearch sql access engine at all, see I am currently only able to use query like SHOW TABLES or SHOW CATALOG, and when I give the rlike query in support with SHOW TABLES as SHOW TABLES NOT RLIKE {1}, or SELECT TABLES NOT RLIKE {1}, And i also need to combine this with a like %sample_text% ,.. it provides an exception like this---
"type": "parsing_exception",
"reason": "line 1:13: mismatched input 'NOT' expecting {, 'ANALYZE', 'ANALYZED', 'CATALOG', 'CATALOGS', 'COLUMNS', 'CURRENT_DATE', 'CURRENT_TIME', 'CURRENT_TIMESTAMP', 'DAY', 'DEBUG', 'EXECUTABLE', 'EXPLAIN', 'FIRST', 'FORMAT', 'FULL', 'FUNCTIONS', 'GRAPHVIZ', 'HOUR', 'INCLUDE', 'INTERVAL', 'LAST', 'LIKE', 'LIMIT', 'MAPPED', 'MINUTE', 'MONTH', 'OPTIMIZED', 'PARSED', 'PHYSICAL', 'PIVOT', 'PLAN', 'RLIKE', 'QUERY', 'SCHEMAS', 'SECOND', 'SHOW', 'SYS', 'TABLES', 'TEXT', 'TOP', 'TYPE', 'TYPES', 'VERIFY', 'YEAR', IDENTIFIER, DIGIT_IDENTIFIER, TABLE_IDENTIFIER, QUOTED_IDENTIFIER, BACKQUOTED_IDENTIFIER}"
}. Can you tell me something that will suit this.. or am I wrong here somewhere