Ufdjrw

I hope someone out there can help me with this issue. I am currently working on a data pipeline project, my current dilemma is whether to use parquet with Athena or storing it to Redshift

2 Scenarios:
First,

EVENTS --> STORE IT IN S3 AS JSON.GZ --> USE SPARK(EMR) TO CONVERT TO PARQUET --> STORE PARQUET BACK INTO S3 --> ATHENA FOR QUERY --> VIZ

Second,

EVENTS --> STORE IT IN S3 --> USE SPARK(EMR) TO STORE DATA INTO REDSHIFT

Issues with this scenario:

1. Spark JDBC with Redshift is slow


2. Spark-Redshift repo by data bricks have a fail build and was updated 2 years ago

I am unable to find useful information on which method is better. Should I even use Redshift or is parquet good enough?

Also it would be great if someone could tell me if there are any other methods for connecting spark with Redshift because there's only 2 solution that I saw online - JDBC and Spark-Reshift(Databricks)

P.S. the pricing model is not a concern to me also I'm dealing with millions of events data.

Here are some ideas / recommendations

• Don't use JDBC.


• Spark-Redshift works fine but is a complex solution.


• You don't have to use spark to convert to parquet, there is also the option of using hive.
  see
  https://docs.aws.amazon.com/athena/latest/ug/convert-to-columnar.html
  


• Athena is great when used against parquet, so you don't need to use
  Redshift at all


• If you want to use Redshift, then use Redshift spectrum to set up a
  view against your parquet tables, then if necessary a CTAS within
  Redshift to bring the data in if you need to.


• AWS Glue Crawler can be a great way to create the metadata needed to
  map the parquet in to Athena and Redshift Spectrum.

My proposed architecture:

EVENTS --> STORE IT IN S3 --> HIVE to convert to parquet --> Use directly in Athena

and/or

EVENTS --> STORE IT IN S3 --> HIVE to convert to parquet --> Use directly in Redshift using Redshift Spectrum

You MAY NOT need to convert to parquet, if you use the right partitioning structure (s3 folders) and gzip the data then Athena/spectrum then performance can be good enough without the complexity of conversion to parquet. This is dependent on your use case (volumes of data and types of query that you need to run).

Which one to use depends on your data and access patterns. Athena directly uses S3 key structure to limit the amount of data to be scanned. Let's assume you have event type and time in events. The S3 keys could be e.g. yyyy/MM/dd/type/* or type/yyyy/MM/dd/*. The former key structure allows you to limit the amount of data to be scanned by date or date and type but not type alone. If you wanted to search only by type x but don't know the date, it would require a full bucket scan. The latter key schema would be the other way around. If you mostly need to access the data just one way (e.g. by time), Athena might be a good choice.

On the other hand, Redshift is a PostgreSQL based data warehouse which is much more complicated and flexible than Athena. The data partitioning plays a big role in terms of performance, but schema can be designed in many ways to suit your use-case. In my experience the best way to load data to Redshift is first to store it to S3 and then use COPY https://docs.aws.amazon.com/redshift/latest/dg/r_COPY.html . It is multiple magnitudes faster than JDBC which I found only good for testing with small amounts of data. This is also how Kinesis Firehose loads data into Redshift. If you don't want to implement S3 copying yourself, Firehose provides an alternative for that.