Best way to read in part of a huge table to AWS GLUE

user_2375593 0
Viewed 1902

I'm having some trouble loading a large file from my data lake (currently stored in postgres) into AWS GLUE. It is 4.3 Billion rows.

In testing, I've found that the table is too large to be fully read in.

Here's how I'm loading the data frame:

large_dynamic_frame = glueContext.create_dynamic_frame.from_catalog(database = "DBNAME",
                                          table_name = "TABLENAME",
                                          redshift_tmp_dir = args["TempDir"],
                                          transformation_ctx = "TABLECTX")

Important Factors

  • I don't need the whole data frame! I'll ultimately filter based on a couple of attributes and join with smaller tables.

  • I've already tried using a push_down_predicate, but that required the data to be stored in S3 using a specific folder organization and unfortunately I don't get to choose the pre-existing format of this table.

  • I've also tried reading in the table and simply re-organizing it to the S3 folder organization necessary for pushdown_predicate to work, but the process ends with "exit code 1" after 5 hours of running.

Primary Question

How can I read in part of a table without using a pushdown predicate?

aws-glue pyspark apache-spark amazon-web-services python
2 Answers

You can also use pure spark/pyspark code in Glue and take advantage of its read methods. You can see in their documentation, how to read from redshift or in general any SQL DB through JDBC. Even reading data from a query like the following example:

# Read data from a query
df = spark.read \
  .format("com.databricks.spark.redshift") \
  .option("url", "jdbc:redshift://redshifthost:5439/database?user=username&password=pass") \
  .option("query", "select x, count(*) my_table group by x") \
  .option("tempdir", "s3n://path/for/temp/data") \
  .load()

I have found that AWS Glue only implemented a small fraction of spark functionality, so I will recommend going with spark/pySpark when you have something complex to work on.

user_102549100

Unfortunately predicate pushdown works only for S3 as you figured out already.

  1. Regarding the "exit code 1", is your data in S3 in raw CSV format? Can you try create multi-part bzip2 or lz4. In that case, the load will be shared by multiple workers.
  2. How many DPUs you have allocated for the task. This article gives a nice overview of DPU capacity planning.
  3. Or you can create a view in Postgres and use that as source.

Please let me know if that helped.

user_101440000
Related