Do we must make a complex query in PySpark or a simple, and use .filter / .select?

user_13804294 0
Viewed 284

I have a question. Suppose I run a python script on the server where my data are stored. What is the faster way to have a spark dataframe of my data between :

  • Make a complex query with lot of conditions but it return me the exact dataframe I need or
  • Make a simple query and make the dataframe I need with .filter / .select

You can also suppose that the dataframe I need is small enough to fit on my RAM.

Thanks

pyspark apache-spark sql python
3 Answers

IIUC, everything depends on from where you are reading the data, So here are some scenarios

DataSource: RDBMS(oracle, postgres, mysql....)

If you want to read data from RDBMS system then you have to establish a JDBC connection to the database then fetch the results.

Now remember spark is slow when fetching data from relational databases over JDBC and it is recommended you filter most of your records on database side itself as it will allow the minimum data to be transferred over the network

You can control the read speed using some tuning parameters but it is still slow.

DataSource: Redshift, Snowflake

In this scenario if your cluster is large and relatively free then pushdown the query to the cluster itself or if you want to read data using JDBC then it is also fast as BTS it unloads the data to a temp location and then spark reads it as file source.

DataSource: Files

Always try to pushdown the filter as they are there for a reason, so that your cluster needs to do the minimum work as you are reading only the required data.

Bottom line is that you should always try to pushdown the filters on the source to make your spark jobs faster.

user_53527480

The key points to mind is

  1. Restrict/filter data to maximum possible level while loading into dataframe, so as only needed data resides in dataframe

    for non file sources: filtering data at source by using native filter and fetching only needed columns (aim for minimum data transfer).

    for file sources: restricting/modifying data in file source is not feasible. so the first operation is to filter data once loaded

  2. In complex operations first perform narrow transformations (filters, selecting only needed columns) and then perform wide transformations(joins, ordering) which involves shuffle towards the end, so that less data will be shuffled between worker nodes.

The less the shuffles the faster your end dataframe would be.

user_138181450

First of all I think we should be careful when dealing with small data in our Spark programs. It was designed to give you parallel processing for big data. Second, we have something like Catalyst query optimizer and lazy evaluation, which is a good tool for Spark to optimize everything what was put either in SQL query or API call transformations.

user_28139880
Related