CODEWITHSUNDEEP
amazon-s3apache-sparkhiveparquetpresto
Aslan N
Aslan N

Reputation: 21

Loading or pointing to multiple parquet paths for data analysis with hive or prestodb

I have couple of spark jobs that produce parquet files in AWS S3. Every once in a while i need to run some ad-hoc queries on a given date range of this data. I don't want to do this in spark because I want our QA team which has no knowledge os spark be able to do this. What i like to do is to spin up an AWS EMR cluster and load the parquet files into HDFS and run my queries against it. I have figured out how to create tables with hive and point it to one s3 path. But then that limits my data to only one day. because each day of date has multiple files under a path like

s3://mybucket/table/date/(parquet files 1 ... n).

So problem one is to figure how to load multiple days of data into hive. ie

s3://mybucket/table_a/day_1/(parquet files 1 ... n). s3://mybucket/table_a/day_2/(parquet files 1 ... n). s3://mybucket/table_a/day_3/(parquet files 1 ... n). ... s3://mybucket/table_b/day_1/(parquet files 1 ... n). s3://mybucket/table_b/day_2/(parquet files 1 ... n). s3://mybucket/table_b/day_3/(parquet files 1 ... n).

I know hive can support partitions but my s3 files are not setup that way.

I have also looked into prestodb which looks like to be the favorite tool for this type of data analysis. The fact it supports ansi SQL makes it a great tool for people that have SQL knowledge but know very little about hadoop or spark. I did install this on my cluster and it works great. But looks like you can't really load data into your tables and you have to rely on Hive to do that part. Is this the right way to use prestodb? I watched a netflix presentation about their use of prestodb and using s3 in place of HDFS. If this works its great but i wonder how the data is moved into memory. At what point the parquet files will be moved from s3 to the cluster. Do i need to have cluster that can load the entire data into memory? how is this generally setup?

Upvotes: 2

Views: 1730

Answers (2)

harunurhan
harunurhan

Reputation: 1604

You can install Hive and create Hive tables with you data in S3, described in the blog post here: https://blog.mustardgrain.com/2010/09/30/using-hive-with-existing-files-on-s3/

Then install Presto on AWS, configure Presto to connect the hive catalog which you installed previously. Then you can query the your data on S3, with Presto by using SQL.

Upvotes: 2

ThisGuy
ThisGuy

Reputation: 2883

Rather than trying to load multiple files, you could instead use the API to concatenate the days you want into a single object, which you can then load through the means you already mention.

AWS has a blog post highlighting how to do this exact thing purely through the API (without downloading + re-uploading the data): https://ruby.awsblog.com/post/Tx2JE2CXGQGQ6A4/Efficient-Amazon-S3-Object-Concatenation-Using-the-AWS-SDK-for-Ruby

Upvotes: 0

Related Questions