3

I'm coping MySQL databases to Red-Shift with the help of an ETL tool called Matillion, and I'm using the same tool to query the database. Most of the queries I've written are basic select queries with lots of joins, unions and sub-queries.

Since Red-Shift is specialized for analytical processing, I want to transform my basic queries into OLTP queries.

I'll be grateful if someone could point me a direction to learn how to write queries more OLTP way.

Thanks!

CLOUGH
  • 691
  • 11
  • 16
  • please give some practical examples of the OLTP queries that you want to run on redshift? do you want to insert rows etc? please take a step back and explain. – Jon Scott Oct 04 '17 at 11:19

1 Answers1

3

To clarify, Redshift is not an OLAP database (like HANA or SSAS), so you can't query Redshift in an OLAP way.

However, Redshift does of course support the full range of analytic functions, which are very much OLAP-like: http://docs.aws.amazon.com/redshift/latest/dg/c_Window_functions.html

Matillion supports that too, for example with the

Window Calculation Component https://redshiftsupport.matillion.com/customer/portal/articles/1991935-window-calculation-component

You can also search for a Rank Component on the Matillion ETL for Amazon Redshift support portal.

Matillion also has documentation/videos on Data Quality Framework which goes through some of these.

Dayna Shoemaker
  • 46
  • 1
  • 1
    There is no need to use Matillion for this type of thing.Do you work for them? – Jon Scott Oct 06 '17 at 06:36
  • 5
    also - you incorrectly state that Redshift is not an OLAP database.However, it is! (Maybe you meant OLTP?) – Jon Scott Oct 06 '17 at 06:41
  • @JonScott Redshift is not an OLAP database. It is a data warehouse. An OLAP server accepts MDX (or similar analytic language) and can query a binary cube (MOLAP), or a data warehouse (ROLAP). See https://stackoverflow.com/questions/18916682/data-warehouse-vs-olap-cube – Neil McGuigan Nov 06 '19 at 20:12
  • 1
    OLAP is the analytical version of OLTP. Redshift IS an OLAP that supports relational OLAP (ROLAP). ROLAP is a subset of OLAP. An OLAP server that supports MDX is a MOLAP. the link you sent is a DW vs a OLAP CUBE, an OLAP cube is a MOLAP. An OLAP (any type) is just the type of DB (what functionality it has). A data warehouse is one thing that you can choose to perform on a OLAP, probably a ROLAP. Your post on your link is right BTW. Redshift is an OLAP that CAN be used as a data warehouse if you choose. Finally my first question was rhetorical as the user answering this DOES work for Matillion. – Jon Scott Nov 06 '19 at 21:57