CODEWITHSUNDEEP
apache-sparkapache-spark-sql
Zhenyu Zhang
Zhenyu Zhang

Reputation: 63

Can sort() and cache() combined in spark increase filter speed like creating index column in SQL?

We know in SQL, an index can be created on a column if it is frequently used for filtering. Is there anything similar I can do in spark? Let's say I have a big table T containing a column C I want to filter on. I want to filter 10s of thousands of id sets on the column C. Can I sort/orderBy column C, cache the result, and then filter all the id sets with the sorted table? Will it help like indexing in SQL?

Upvotes: 2

Views: 848

Answers (1)

Matt Andruff
Matt Andruff

Reputation: 5135

You should absolutely build the table/dataset/dataframe with a sorted id if you will query on it often. It will help predicate pushdown. and in general give a boost in performance.

When executing queries in the most generic and basic manner, filtering happens very late in the process. Moving filtering to an earlier phase of query execution provides significant performance gains by eliminating non-matches earlier, and therefore saving the cost of processing them at a later stage. This group of optimizations is collectively known as predicate pushdown.

Even if you aren't sorting data you may want to look at storing the data in file with 'distribute by' or 'cluster by'. It is very similar to repartitionBy. And again only boosts performance if you intend to query the data as you have distributed the data.

If you intend to requery often yes, you should cache data, but in general there aren't indexes. (There are file types that help boost performance if you have specific query type needs. (Row based/columnar based))

You should look at the Spark Specific Performance tuning options. Adaptive query is a next generation that helps boost performance, (without indexes)

If you are working with Hive: (Note they have their own version of partitions) Depending on how you will query the data you may also want to look at partitioning or :

[hive] Partitioning is mainly helpful when we need to filter our data based on specific column values. When we partition tables, subdirectories are created under the table’s data directory for each unique value of a partition column. Therefore, when we filter the data based on a specific column, Hive does not need to scan the whole table; it rather goes to the appropriate partition which improves the performance of the query. Similarly, if the table is partitioned on multiple columns, nested subdirectories are created based on the order of partition columns provided in our table definition.

Hive Partitioning is not a magic bullet and will slow down querying if the pattern of accessing data is different than the partitioning. It make a lot of sense to partition by month if you write a lot of queries looking at monthly totals. If on the other hand the same table was used to look at sales of product 'x' from the beginning of time, it would actually run slower than if the table wasn't partitioned. (It's a tool in your tool shed.)

Another hive specific tip: The other thing you want to think about, and is keeping your table stats. The Cost Based Optimizer uses those statistics to query your data. You should make sure to keep them up to date. (Re-run after ~30% of your data has changed.)

ANALYZE TABLE [db_name.]tablename [PARTITION(partcol1[=val1], partcol2[=val2], ...)]  -- (Note: Fully support qualified table name

since Hive 1.2.0, see HIVE-10007.) COMPUTE STATISTICS [FOR COLUMNS] -- (Note: Hive 0.10.0 and later.) [CACHE METADATA] -- (Note: Hive 2.1.0 and later.) [NOSCAN];

Upvotes: 1

Related Questions