Hive query performance for high cardinality field

by
  1. Use ORC with bloom filters:
    CREATE TABLE employee (
      employee_id bigint,
      name STRING
    ) STORED AS ORC 
    TBLPROPERTIES ("orc.bloom.filter.columns"="employee_id")
    ;
  1. Enable PPD with vectorizing, use CBO and Tez:
    SET hive.optimize.ppd=true;
    SET hive.optimize.ppd.storage=true;
    SET hive.vectorized.execution.enabled=true;
    SET hive.vectorized.execution.reduce.enabled = true;
    SET hive.cbo.enable=true;
    set hive.stats.autogather=true;
    set hive.compute.query.using.stats=true;
    set hive.stats.fetch.partition.stats=true;
    set hive.execution.engine=tez;
    set hive.stats.fetch.column.stats=true;
    set hive.map.aggr=true;
    SET hive.tez.auto.reducer.parallelism=true;

Ref: https://community.cloudera.com/t5/Community-Articles/Optimizing-Hive-queries-for-ORC-formatted-tables/ta-p/248164

  1. Tune proper parallelism on mappers and reducers:

    –example for mappers:

     set tez.grouping.max-size=67108864;
 set tez.grouping.min-size=32000000;

    –example settings for reducers:

     set hive.exec.reducers.bytes.per.reducer=67108864; --decrease this to increase the number of reducers

Change these figures to achieve optimal performance.

More Related Contents:

  1. HIVE select count(*) non null returns higher value than select count(*)
  2. Execute Hive Query with IN clause parameters in parallel
  3. Counting DISTINCT over multiple columns
  4. SQL: How to properly check if a record exists
  5. Subquery v/s inner join in sql server
  6. Optimize groupwise maximum query
  7. Find last day of a month in Hive
  8. How to Optimize Queries in a Database – The Basics
  9. PostgreSQL – fetch the rows which have the Max value for a column in each GROUP BY group
  10. Hive – Unpivot functionality in hive
  11. How to find out what is locking my tables?
  12. SELECT DISTINCT is slower than expected on my table in PostgreSQL
  13. Oracle , != , ^= operators
  14. Concatenate multiple result rows of one column into one, group by another column [duplicate]
  15. sql ORDER BY multiple values in specific order?
  16. ColdFusion adding extra quotes when constructing database queries in strings
  17. Favourite performance tuning tricks [closed]
  18. Get month name from date in Oracle
  19. Query a database based on result of query from another database
  20. How do I import .sql files into SQLite 3?
  21. How to delete the top 1000 rows from a table using Sql Server 2008?
  22. SQL Error: ORA-00942 table or view does not exist
  23. What are the use cases for selecting CHAR over VARCHAR in SQL?
  24. How to return multiple rows from the stored procedure? (Oracle PL/SQL)
  25. How to extract week number in sql
  26. Why do multiple-table joins produce duplicate rows?
  27. How do I format date and time on ssrs report?
  28. How to export data from Excel spreadsheet to Sql Server 2008 table
  29. Query a Table’s Foreign Key relationships
  30. How to get the next number in a sequence

Leave a Comment