What is the best way to read Hive Table through Spark SQL?

Question

I execute Spark SQL reading from Hive Tables and it is lengthy in execution(15 min). I am interested in optimizing the query execution so I am asking about if the execution for those queries uses the execution engine of Hive and by this way it is similar to executing the queries in Hive editor, or Spark use the Hive Metastore only to know the locations of the files and deals with the files after that directly?

import os import findspark findspark.init() from pyspark.sql import SparkSession spark = SparkSession.builder \ .master("yarn") \ .appName("src_count") \ .config('spark.executor.cores','5') \ .config('spark.executor.memory','29g') \ .config('spark.driver.memory','16g') \ .config('spark.driver.maxResultSize','12g')\ .config("spark.dynamicAllocation.enabled", "true")\ .config("spark.shuffle.service.enabled", "true")\ .getOrCreate() sql = "SELECT S.SERVICE, \ COUNT(DISTINCT CONTRACT_KEY) DISTINCT_CNT, \ COUNT(*) CNT ... " df.toPandas()

why you want to convert it into pandas dataframe? is there any specific need for that? — Vikrant Singh Rana
– Vikrant Singh Rana, Commented Jul 29, 2019 at 10:39
Spark sql engine uses hive in general even if you dont directly work with hive — Ilya Brodezki
– Ilya Brodezki, Commented Jul 29, 2019 at 11:52
@vikrantrana it is aggregation query and return limited number of records less than 20 record. — Ahmed Gamal
– Ahmed Gamal, Commented Jul 30, 2019 at 8:33
@IlyaBrodezki does it use it as a metastore only and execute it as sort of RDD or DataFrames for example, or use Hive server for the execution as if I am executing it in the Hive editor? — Ahmed Gamal
– Ahmed Gamal, Commented Jul 30, 2019 at 8:36
you can use spark in-built functions to improve the performance.. choose pandas or python function if something cannot be done using spark inbuilt functions.. — Vikrant Singh Rana
– Vikrant Singh Rana, Commented Jul 30, 2019 at 9:58

beavis11111 · Accepted Answer · 2021-02-08 11:31:14Z

You can read the HIVE table as follows:

Read Entire HIVE Table

df = spark.table(<HIVE_DB>.<HIVE_TBL>)

You can read the partial table based on SQL query.

df = spark.sql(<YOUR_SQL_Query>)

Also, in your question you are trying to convert the Spark DataFrame to Python DataFrame which is not recommended. Because, in this case you are sending all data from worker to driver which trafer lof of data across the network and slowdown the application and also your driver will be overloaded since it will have entire dataset and it may go to OOM as well.

Collectives™ on Stack Overflow

What is the best way to read Hive Table through Spark SQL?

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related