REGEXP_SPLIT_TO_TABLE doesn't work

Analytics
Enthusiast

Re: REGEXP_SPLIT_TO_TABLE doesn't work

Hi   :)

I have question-

 

lets assume that dbc.qrylog has only one row with the expression 'my_db' that looks this way-

"select * from my_db.first_occurence a, my_db.second_occurence b".

 

(I want to get the first word after some expression- which is my_db in this case )

so  i know i can use this regexp to get the expression- "first_occurence",    

SELECT DISTINCT REGEXP_SUBSTR(querytext , '(?<=my_db\.)(.*?)(?=\W|$)' , 1 , 1 , 'i' )
FROM dbc.qrylog

where .... 

and this one to get the expession- "second_occurence"

 

SELECT DISTINCT REGEXP_SUBSTR(querytext , '(?<=my_db\.)(.*?)(?=\W|$)' , 1 , 2 , 'i' )
FROM dbc.qrylog

where .... 

 

i was thinking of solution that i can use regexp_split_to_table in order to get all the occurences of expressions like in my regexp_substr in seperated rows...

but it didnt worked for me (probably something with the join of the qrylog and the regexp_split_to_table)..

can you please help me ? :)

 

 

(The final goal is to get all the table names of tables that was used in particular db)

 

thanks a lot,

Adi

 

Junior Contributor

Re: REGEXP_SPLIT_TO_TABLE doesn't work


(The final goal is to get all the table names of tables that was used in particular db)


Why reinventing the wheel?

BEGIN QUERY LOGGING OBJECTS does exactly what you want.

If you want to restrict the size of dbc.DBQLObjTbl (and don't need column information) use WITH NO COLUMNS OBJECTS

 

Regarding RegExp_Split_To_Table, you need to use a Common Table Expression to prepare the base data and you must cast the BigInt (not supported) QueryId to a string. But this splits the text whenever there's 'my_db.', i.e you get the text between every 'my_db.' and must apply another RegExp-Substr to extract the table name from it:

 

WITH cte AS
 (
   SELECT Trim(QueryID) AS QueryId, QueryText 
   FROM dbc.qrylog SAMPLE 10
   WHERE QueryText LIKE '%open_data.%'
 )
SELECT SELECT id, tokennum, RegExp_Substr(token, '.+?\b')
FROM
   TABLE (RegExp_Split_To_Table(Trim(cte.QueryID), cte.QueryText, 'my_db\.', 'i')
   RETURNS (id VARCHAR(18), tokennum INTEGER, token VARCHAR(1000) CHARACTER SET Unicode)) AS dt

But this will fail to return data for a quoted "my_db" or an unqualifed table name (or false positives if there's a table name 'my_db').

 

 

Enthusiast

Re: REGEXP_SPLIT_TO_TABLE doesn't work

Thanks.

But using BEGIN QUERY LOGGING OBJECTS will consume a lot of CPU and storage which we can't afford right now + i need to check usage of tables in the past..

 Do you know any way i can insert to table / volatile table the results? 

 

something like 

insert into my_tab
WITH cte AS ( SELECT Trim(QueryID) AS QueryId, QueryText FROM dbc.qrylog SAMPLE 10 WHERE QueryText LIKE '%open_data.%' ) SELECT SELECT id, tokennum, RegExp_Substr(token, '.+?\b') FROM TABLE (RegExp_Split_To_Table(Trim(cte.QueryID), cte.QueryText, 'my_db\.', 'i') RETURNS (id VARCHAR(18), tokennum INTEGER, token VARCHAR(1000) CHARACTER SET Unicode)) AS dt




thanks!
Teradata Employee

Re: REGEXP_SPLIT_TO_TABLE doesn't work

It is incorrect to say that the logging will consume "a lot of cpu": we need to put this "myth" to rest.

 

YOUR defined process will consume a lot more cpu on the system than logging ever will and if you keep "re-evaluating" dbql then your usage goes up even more with each iteration.

 

Logging requires some disk space but you can manage how much you keep to a manageable level.

 

thanks

 

Dave

 

Junior Contributor

Re: REGEXP_SPLIT_TO_TABLE doesn't work

As David already wrote, DBQL does not consume lots of CPU and using NO COLUMNS not much space. Don't you log at least the base log and StepInfo for performance analysis or how do you find & fix bad queries? And you got the data to check usage of tables in the past.

 

Of course you can Insert the result of the Select into a table, why don't you try it?

Enthusiast

Re: REGEXP_SPLIT_TO_TABLE doesn't work

Ok thanks you :)

I will consider this option after reaserch a bit acout it..

But still- i need to check Queries that already executed in the past.

 

Dnoeth- 

the Insert command for example i wrote here doenst work.

Is there any special syntax to make queries like insert to table with cte... ?

Or can i do select something minus <the  with select> ?

Thanks a lot

Junior Contributor

Re: REGEXP_SPLIT_TO_TABLE doesn't work

This syntax works since at least Teradata 13. What error message do you get?