Big Data Things

In my previous post, we went over the qualitative comparisons between Hive, Spark and Presto . In this post, we will do a more detailed analysis, by virtue of a series of performance benchmarking tests on these three query engines.  Benchmarking Data Set For this benchmarking, we have two tables. Some of the key points of the setup are: - All the query engines are using the Hive metastore for table definitions as Presto and Spark both natively support Hive tables - All the tables are external Hive tables with data stored in S3 - All the tables are using  Parquet  and  ORC  as a storage format Tables : 1. product_sales: It has ~6 billion records 2. product_item: It has ~589k records Hardware Tests were done on the following EMR cluster configurations, EMR Version: 5.8 Spark: 2.2.0 Hive: 2.3.0 Presto: 0.170 Nodes: Master Node:   1x  r4.16xlarge Task nodes:  8 x r4.8xlarge Query Types There are thre...

Though it is a rare combination but there are cases where you would like to connect an MPP database like Redshift to an OLAP solution for analytics solutions. But, there might be scenarios where you would want a cube to power your reports without the BI server hitting your Redshift cluster. In this post I will show you how to connect to a Redshift instance from a SQL Server Analysis Services 2014. Environment Setup In my setup, the Redshift instance is in a VPC while the SSAS server is hosted on an EC2 machine in the same VPC. Access to the Redshift instance and SSAS host machine are controlled by two different security groups. Security group attached to the Redshift cluster has an ingress rule setup for the security group attached to the EC2 machine. I have tried to keep the environment as close to real life setups as possible. In most cases, your environment will be similar to this setup. Steps to Connect Redshift to SSAS 2014 Step 1: Download the PGOLEDB driver for y...

Gather around, kids! We have a new hero in town - Snowflake . Snowflake is killing other databases like Thanos killed spoiler alert  in Avengers . They started with Teradata and Vertica, and are now aiming their guns at AWS Redshift. And they have earned this right. They have really addressed the pain points people are facing while using these other databases. For example, scaling up and down in Redshift is a painful process. On the other hand, in Snowflake you can spin up clusters on the fly without much hassle or effort with almost zero impact on running pipelines.  While this post is not about finding the better one among Snowflake and Redshift, you should still read it if you are one of those thinking about making the move from Redshift to Snowflake. This post lists out the most common functions used in Redshift and points out their Snowflake counterpart. This list is not exhaustive by any means and I will strive to keep on updating it as and when I find new ones. ...