If you’re building a brand new application - something modern and “cloud-ish” - and you’re invested in the Google Cloud, BigQuery will be a good option.

You have one set of credentials and use the same IAM infrastructure within a project, with no additional overhead. You have less compute affinity, less session-continuity, and less data caching.īigQuery has an advantage if you’re building on the Google Cloud Platform. In BigQuery, you don’t have your own compute - everyone shares compute in big clusters. Syntax: expression NOT LIKE ILIKE pattern ESCAPE ‘escapechar’ Where, expression: Often a column name. LIKE is case-sensitive and ILIKE is case-insensitive. It’s a little more expensive - there are auto-suspend and auto-resume features - that can get you around some of that, but you might see a little higher bill with Snowflake. The LIKE operator in Amazon Redshift Regex is used to match the string with the wildcard patterns such as (percent) and (Underscore). Snowflake doesn’t do that.Īs for pros and cons, when you look at these two databases, if you need consistently predictable workloads, Snowflake is the right option. It has easy cloning but the data is being physically moved, so you are owning it twice. It provides flexibility for building dev and QA environments (temporarily or permanently) and creating snapshots of your database at particular points in time for compliance purposes if necessary. Snowflake has a huge advantage here in zero-copy cloning. If you want to remove capacity planning from your project, then Redshift is not for you.īoth Snowflake and BigQuery separate compute storage. A change in compute size, adding additional virtual warehouses for different workloads, configuring cluster sizes to max out at 20 instead of 10 for instance: these things that take seconds to do either using DDL or using a Snowflake console. The big difference with Snowflake is zero capacity planning. Capacity planning - that thing that every consultant hates because it’s almost impossible to predict - where we try to predict: “We think you’ll need this size over the next year.” But it’s impossible to know for sure. Redshift does a lot of that for you, the big difference here between Redshift and Snowflake is from a total cost of ownership perspective. When you think of database backups, maintenance, and operational procedures, running those weekly or monthly processes to keep everything queryable, Snowflake does all that for you. xid = ve.However, with Snowflake, there is truly zero administration. SELECT xid, 'C ' AS tx_action, endtime from stl_commit_stats WHERE node = - 1 SELECT xid, 'C ' AS tx_action, endtime FROM stl_utilitytext WHERE text ILIKE '%commit% ' OR text ILIKE '%end% ' ( SELECT xid, 'R ' AS tx_action, endtime FROM stl_utilitytext WHERE text ILIKE '%rollback% ' OR text ILIKE '%aborted% ' ( SELECT xid, MAX(tx_action) AS tx_action, MAX(endtime) as endtime FROM FROM stlutilitytext WHERE text ILIKE rollback OR text ILIKE aborted. xact_id where aborted = 1 GROUP BY query, tbl Amazon Redshift Utils contains utilities, scripts and view which are useful. table_id AS tbl, 'A ' AS operation FROM stl_query sq LEFT JOIN stl_tr_conflict stc on sq. SELECT query, tbl, 'W ' AS operation FROM stl_insert GROUP BY query, tbl SELECT query, tbl, 'W ' AS operation FROM stl_delete GROUP BY query, tbl ( SELECT query, tbl, 'R ' AS operation FROM stl_scan WHERE type = 2 GROUP BY query, tbl The user will need to check the query manually.Īnother output is the transaction actions (tx_action) which will be: For this view, I added some logic to search not only for (probably) column definitions but also for (possibly unqualified) relation references in from and join clauses too. A if the query statement got aborted this could be due to a serializable isolation violation Some of the materialized views in my Redshift cluster take advantage of a custom searchpath so not all table references included a schema qualifier. W if it is a write operation that was done R if it is a read operation that was done This view will help to see what tables are operated on by transactions and to see how transactions This view should be used with a filter that limits the output for transaction IDs or query IDs.

Purpose: View to get the READ and WRITE operations per table for specific transactions. DROP VIEW admin.v_get_tbl_reads_and_writes

Redshift ilike