preprocessor, parameter tokens

drumcircle
Rising Star

Metadata calls from Exasol are seen by the preprocessor as:

 

SELECT AC.COLUMN_NAME , AC.COLUMN_TYPE , AC.COLUMN_MAXSIZE , AC.COLUMN_NUM_PREC , AC.COLUMN_NUM_SCALE , AC.COLUMN_IS_NULLABLE , AC.COLUMN_DEFAULT , AC.COLUMN_IS_VIRTUAL , AC.COLUMN_IDENTITY , AC.COLUMN_IS_DISTRIBUTION_KEY , AC.COLUMN_COMMENT , AC.COLUMN_PARTITION_KEY_ORDINAL_POSITION FROM SYS.EXA_ALL_COLUMNS AC WHERE AC.COLUMN_TABLE = ? AND AC.COLUMN_OBJECT_TYPE = ? AND AC.COLUMN_SCHEMA = ? ORDER BY AC.COLUMN_ORDINAL_POSITION

 

I need to be able to look at the parameters to filter columns based on schema.  Are query parameter values exposed to the preprocessor?

4 REPLIES 4

mwellbro
Xpert

Hi drumcircle,

I´ve been giving this one a bit of thought - I´m afraid only the statement text is exposed to the preprocessor so getting to the contents of bind variables is probably not possible.

I tried to "re-route" given binds alá (not in good form, I just typed that down without regard for proper separation of calls etc,etc):

CREATE or replace LUA SCRIPT "PEEK_BINDS" () RETURNS ROWCOUNT AS
function ParseAndChange(sql_text)
local tx     = sqlparsing.getsqltext()

if ( tx.find(tx,'?') and tx.find(tx,'FOO_TGT') ) then

res = query([[INSERT INTO FOO_TGT2 VALUES(?,?,?)]])

sqlparsing.setsqltext(tx)

return 0 
end

sqlparsing.setsqltext(tx)

return 0
end

ParseAndChange('a')
/

The preprocessor took my stmt:
/* preprocessing: */ EXECUTE SCRIPT PERFORMANCE.PEEK_BINDS(); /* preprocessing: INSERT INTO FOO_TGT(KEY_1,KEY_2,PAYLOAD) VALUES ( ?, ?, ?) */

But that only yielded me an error message in the form of:

query has external parameters and can only be used as prepared statement

 

I was a bit surprised at that, since you could even change the actual command type of a prepared statement with the preprocessor before the statement gets executed and still keep the bind variables ( i.e. you could turn an UPDATE x SET y=? into an INSERT INTO z VALUES(?) and once the preprocessor hands back control to the SQL-engine you´d be executing an INSERT instead of an UPDATE ).

Since I don´t know of a way to actually execute the previously prepared statement from within lua ( which I don´t think is possible since we´d have to somehow get the addr and handle of the statement and what-not ) I won´t be much help here...
I think that someone from exa should take this up for a definitive answer, maybe I´m overlooking something here....
@exa-MathiasHo , maybe you know someone well versed in the intricacies of the exa-preprocessor ?

Cheers,
Malte

PeterK
Xpert

Hi @drumcircle,

Virtual schemas have access to the query parameters so in theory you could have the preprocessor do a simple find/replace of SYS.EXA_ALL_COLUMNS to something like VSYS.EXA_ALL_COLUMNS and then implement a virtual schema VSYS having a table EXA_ALL_COLUMNS and the pushdown handler could rewrite the query referencing SYS.EXA_ALL_COLUMNS and with the additional custom filters you require.

Of course this is a complex and somewhat fragile solution and there is performance overhead with all virtual schema access so I wouldn't necessarily recommend it ... but it's technically possible 🙂

Regards,

Peter

 

mwellbro
Xpert

Hi @PeterK ,

came across something I´d like to pick your brain about as you mentioned "Virtual schemas have access to the query parameters" : this means that the adapter script would be able to get the bind values into it´s own arrays and variables, correct ?

You wouldn´t happen to have a sample that could point me in the direction of where to look for this ?

This might come in handy as a troubleshooting instrument when some pesky proprietary tool won´t tell you were exactly
something goes "off the rails" 🙂

Cheers,
Malte

PeterK
Xpert

@mwellbro  - Yes I do have a sample!  It's based on https://github.com/GrantStreetGroup/exasol-multi-tenant-virtual-schema-python and is rather long so I've attached it to this comment.

Enjoy