HashKey in Synapse – CONVERT(char(64),HASHBYTES(‘SHA2_256’,UPPER(‘1’ + ‘|’ + ISNULL(CAST([SourceSystem] AS NVARCHAR(MAX)),’UNKNOWN’) + ‘|’ + ISNULL(CAST([Vendor] AS NVARCHAR(MAX)),’UNKNOWN’) )),2)

HashKey in Synapse – CONVERT(char(64),HASHBYTES(‘SHA2_256’,UPPER(‘1’ + ‘|’ + ISNULL(CAST([SourceSystem] AS NVARCHAR(MAX)),’UNKNOWN’) + ‘|’ + ISNULL(CAST([Vendor] AS NVARCHAR(MAX)),’UNKNOWN’) )),2)

So I need to convert this:

So I need to convert this:

I am unnesting some JSON in Redshift.
The below code works but …
… if a record does not have a ‘tags’ element then the entire row is ignored as is effectively and ‘inner’ join.

I have an incrementally auto refreshed materialized view in redshift, I manually refreshed it and svv_mv_info shows it as is_stale=f. A few hours later, it shows is_stale=t. I checked sys_query_history (as a sysadmin), and the only thing between the refresh and now is select statements. These are vanilla tables, nothing is adding to them automatically behind the scenes. Could some kind of auto statistic recompute or vacuum cause the mat view to trip to stale? Anyone ever experience this before?

When debugging queries in Redshift, it’s useful to look at SVL_COMPILE to see where queries are triggering a compile, which obviously gobbles up quite a lot of time if you’re doing it by accident. However, all you get in SVL_COMPILE is some idea of ‘segment’ and ‘locus’. Is there any way to map these numbers to actual parts of a query’s text? Or at least, how can one reason about what they might refer to? The segments don’t seem to match the number of nodes in an EXPLAIN of the query, for example.

Per aws documentation (https://docs.aws.amazon.com/redshift/latest/dg/r_SVV_REDSHIFT_COLUMNS.html), SVV_REDSHIFT_COLUMNS is the table to use but it is extremely slow if I have to retrieve all columns for all tables.

Recently, the vacuum; command takes forever to complete. It was working before but since 3 weeks ago, it never finishes. I increased the timeout to 8 hours but it still times out! It used to finish in less than an hour before.
Is there any way to troubleshoot this and find out what’s going on?
Thanks

I need a way to copy the list of all the users and their account details from pg_user_info of Redshift database. I need different details like if the account is active or not etc.