Hi @sebastian_baer,
Yes, your understanding of the setup is correct.
The VS is a custom development. During “pushdown” requests it calls an external semantic layer service API in order to generate the response SQL. We have found that in the case of IMPORT FROM EXA AT ... STATEMENT ‘select * from vs' the API is getting called 3 times. It gets called 2 times if you specify the column list instead of star.
You can see that behaviour via
--/
CREATE OR REPLACE LUA ADAPTER SCRIPT test.vs_adapter AS
require "cjson"
require "socket"
function adapter_call( json_req )
req = cjson.decode( json_req )
req.capabilities = {''}
req.schemaMetadata = { tables = {{ name = 'T', columns = {{ name = 'C', dataType = { type = 'BOOLEAN' }}}}}}
if req.type == 'pushdown' then
-- Make API call to external semantic layer service
req.sql = 'SELECT TRUE'
log = socket.connect('localhost', '33333')
log:send("REQUEST: "..cjson.encode(req).."\n\n")
log:close()
end
return cjson.encode( req )
end
/
;
CREATE VIRTUAL SCHEMA test_vs USING test.vs_adapter;
-- nc -klp 33333 on localhost
IMPORT FROM EXA AT exasol_local STATEMENT 'SELECT * FROM test_vs.t';
DROP VIRTUAL SCHEMA IF EXISTS test_vs CASCADE;
So I was thinking that since you already make metadata like exa.meta.statement_id available perhaps you could include an exa.meta.execution_mode which would indicate “PREPARE” or “EXECUTE”. That way we could skip the API call during “PREPARE” mode and simply generate a dummy SQL response that has the correct data types.
Or better yet, since the query engine already knows the expected data types (they are in req.pushdownRequest.selectListDataTypes) it doesn’t seem necessary to call the VS framework with a “pushdown” during the “prepare” execution mode.
Edit: I should also include the log output from above test:
REQUEST: {“pushdownRequest”:{“type”:“select”,“from”:{“name”:“T”,“type”:“table”},“selectListDataTypes”:[{“type”:“BOOLEAN”}]},“capabilities”:[“”],“sql”:“SELECT TRUE”,“involvedTables”:[{“name”:“T”,“columns”:[{“name”:“C”,“dataType”:{“type”:“BOOLEAN”}}]}],“schemaMetadataInfo”:{“name”:“TEST_VS”},“type”:“pushdown”,“schemaMetadata”:{“tables”:[{“name”:“T”,“columns”:[{“name”:“C”,“dataType”:{“type”:“BOOLEAN”}}]}]}}
REQUEST: {“pushdownRequest”:{“type”:“select”,“from”:{“name”:“T”,“type”:“table”},“selectListDataTypes”:[{“type”:“BOOLEAN”}]},“capabilities”:[“”],“sql”:“SELECT TRUE”,“involvedTables”:[{“name”:“T”,“columns”:[{“name”:“C”,“dataType”:{“type”:“BOOLEAN”}}]}],“schemaMetadataInfo”:{“name”:“TEST_VS”},“type”:“pushdown”,“schemaMetadata”:{“tables”:[{“name”:“T”,“columns”:[{“name”:“C”,“dataType”:{“type”:“BOOLEAN”}}]}]}}
REQUEST: {“schemaMetadata”:{“tables”:[{“name”:“T”,“columns”:[{“name”:“C”,“dataType”:{“type”:“BOOLEAN”}}]}]},“sql”:“SELECT TRUE”,“schemaMetadataInfo”:{“name”:“TEST_VS”},“pushdownRequest”:{“from”:{“name”:“T”,“type”:“table”},“type”:“select”,“selectListDataTypes”:[{“type”:“BOOLEAN”}]},“capabilities”:[“”],“type”:“pushdown”,“involvedTables”:[{“name”:“T”,“columns”:[{“dataType”:{“type”:“BOOLEAN”},“name”:“C”}]}]}
As you can see the request params are identical so there is currently no way of knowing whether the “execution mode” is “prepare” or “execute”
