How to log Redshift recent SQL queries with actual parameters values

Question

When I'm logging queries executed on Amazon Redshift by querying STL_QUERY table, I get the prepared statement query, without the parameters values, for example:

SELECT * FROM events WHERE ts=$1

I want to know what are the real parameter values.

Is there a way to know that?

Chemar

my query contains a timestamp, and I suspect that it is not performed on the timestamp I send (probably because of timezone issue). I send the same query with the same timestamp to redshift from different servers, and the results are different. – Chemar

It might be the case as JDBS has its own rules about client side timezone. Did you try to specify the timezone with CONVERT_TIMEZONE function? – Vegetate

I didn't, and I didn't know about this function. I will try it when I'll return to this task (it might take several weeks...) – Chemar

Answer 1

I've noticed that stl_querytext would give you the query after the parameters have been replaced with actual values.

Heresiarch

Answer 2

As @hoodahelll mentioned, you can get the actual query text with parameters replaced with actual values from STL_QUERYTEXT. However, STL_QUERYTEXT has the query text broken into 200 byte chunks.

You can use the following SQL to get information about queries as well as the full SQL text, as long as it the entire query is less than the max VARCHAR size in Redshift (65535 bytes):

SELECT
    DISTINCT
    sq.xid,
    sq.pid,
    sq.query AS query_id,
    sq.starttime AS start_time,
    sq.endtime AS end_time,
    -- this works as long as the total query size isn't > 65535 bytes:
    LISTAGG(sqt.text, '') WITHIN GROUP (ORDER BY sqt.sequence)
        OVER (PARTITION BY sqt.xid, sqt.pid, sqt.query) AS query_text
FROM
    STL_QUERY sq
        JOIN STL_QUERYTEXT sqt ON
            sq.xid = sqt.xid AND sq.pid = sqt.pid AND sq.query = sqt.query
WHERE
   <INSERT WHERE CLAUSE HERE>

Geibel