Re: Spark-SQL - Query Hanging, How To Troubleshoot

[Mich Talebzadeh]

OK I use Hive 3.1.1

My suggestion is to put your hive issues to u...@hive.apache.org and for
JAVA version compatibility

They will give you better info.

HTH

Mich Talebzadeh,
Solutions Architect/Engineering Lead
London
United Kingdom


   view my Linkedin profile



 https://en.everybodywiki.com/Mich_Talebzadeh



*Disclaimer:* Use it at your own risk. Any and all responsibility for any
loss, damage or destruction of data or any other property which may arise
from relying on this email's technical content is explicitly disclaimed.
The author will in no case be liable for any monetary damages arising from
such loss, damage or destruction.




On Sun, 13 Aug 2023 at 11:48, Patrick Tucci  wrote:

> I attempted to install Hive yesterday. The experience was similar to other
> attempts at installing Hive: it took a few hours and at the end of the
> process, I didn't have a working setup. The latest stable release would not
> run. I never discovered the cause, but similar StackOverflow questions
> suggest it might be a Java incompatibility issue. Since I didn't want to
> downgrade or install an additional Java version, I attempted to use the
> latest alpha as well. This appears to have worked, although I couldn't
> figure out how to get it to use the metastore_db from Spark.
>
> After turning my attention back to Spark, I determined the issue. After
> much troubleshooting, I discovered that if I performed a COUNT(*) using
> the same JOINs, the problem query worked. I removed all the columns from
> the SELECT statement and added them one by one until I found the culprit.
> It's a text field on one of the tables. When the query SELECTs this column,
> or attempts to filter on it, the query hangs and never completes. If I
> remove all explicit references to this column, the query works fine. Since
> I need this column in the results, I went back to the ETL and extracted the
> values to a dimension table. I replaced the text column in the source table
> with an integer ID column and the query worked without issue.
>
> On the topic of Hive, does anyone have any detailed resources for how to
> set up Hive from scratch? Aside from the official site, since those
> instructions didn't work for me. I'm starting to feel uneasy about building
> my process around Spark. There really shouldn't be any instances where I
> ask Spark to run legal ANSI SQL code and it just does nothing. In the past
> 4 days I've run into 2 of these instances, and the solution was more voodoo
> and magic than examining errors/logs and fixing code. I feel that I should
> have a contingency plan in place for when I run into an issue with Spark
> that can't be resolved.
>
> Thanks everyone.
>
>
> On Sat, Aug 12, 2023 at 2:18 PM Mich Talebzadeh 
> wrote:
>
>> OK you would not have known unless you went through the process so to
>> speak.
>>
>> Let us do something revolutionary here 
>>
>> Install hive and its metastore. You already have hadoop anyway
>>
>> https://cwiki.apache.org/confluence/display/hive/adminmanual+installation
>>
>> hive metastore
>>
>>
>> https://data-flair.training/blogs/apache-hive-metastore/#:~:text=What%20is%20Hive%20Metastore%3F,by%20using%20metastore%20service%20API
>> .
>>
>> choose one of these
>>
>> derby  hive  mssql  mysql  oracle  postgres
>>
>> Mine is an oracle. postgres is good as well.
>>
>> HTH
>>
>> Mich Talebzadeh,
>> Solutions Architect/Engineering Lead
>> London
>> United Kingdom
>>
>>
>>view my Linkedin profile
>> 
>>
>>
>>  https://en.everybodywiki.com/Mich_Talebzadeh
>>
>>
>>
>> *Disclaimer:* Use it at your own risk. Any and all responsibility for
>> any loss, damage or destruction of data or any other property which may
>> arise from relying on this email's technical content is explicitly
>> disclaimed. The author will in no case be liable for any monetary damages
>> arising from such loss, damage or destruction.
>>
>>
>>
>>
>> On Sat, 12 Aug 2023 at 18:31, Patrick Tucci 
>> wrote:
>>
>>> Yes, on premise.
>>>
>>> Unfortunately after installing Delta Lake and re-writing all tables as
>>> Delta tables, the issue persists.
>>>
>>> On Sat, Aug 12, 2023 at 11:34 AM Mich Talebzadeh <
>>> mich.talebza...@gmail.com> wrote:
>>>
 ok sure.

 Is this Delta Lake going to be on-premise?

 Mich Talebzadeh,
 Solutions Architect/Engineering Lead
 London
 United Kingdom


view my Linkedin profile
 


  https://en.everybodywiki.com/Mich_Talebzadeh



 *Disclaimer:* Use it at your own risk. Any and all responsibility for
 any loss, damage or destruction of data or any other property which may
 arise from relying on this email's technical content is explicitly
 disclaimed. The author will in no case be liable for any monetary damages
 arising from such loss, 

Re: Spark-SQL - Query Hanging, How To Troubleshoot

[Patrick Tucci]

I attempted to install Hive yesterday. The experience was similar to other
attempts at installing Hive: it took a few hours and at the end of the
process, I didn't have a working setup. The latest stable release would not
run. I never discovered the cause, but similar StackOverflow questions
suggest it might be a Java incompatibility issue. Since I didn't want to
downgrade or install an additional Java version, I attempted to use the
latest alpha as well. This appears to have worked, although I couldn't
figure out how to get it to use the metastore_db from Spark.

After turning my attention back to Spark, I determined the issue. After
much troubleshooting, I discovered that if I performed a COUNT(*) using the
same JOINs, the problem query worked. I removed all the columns from the
SELECT statement and added them one by one until I found the culprit. It's
a text field on one of the tables. When the query SELECTs this column, or
attempts to filter on it, the query hangs and never completes. If I remove
all explicit references to this column, the query works fine. Since I need
this column in the results, I went back to the ETL and extracted the values
to a dimension table. I replaced the text column in the source table with
an integer ID column and the query worked without issue.

On the topic of Hive, does anyone have any detailed resources for how to
set up Hive from scratch? Aside from the official site, since those
instructions didn't work for me. I'm starting to feel uneasy about building
my process around Spark. There really shouldn't be any instances where I
ask Spark to run legal ANSI SQL code and it just does nothing. In the past
4 days I've run into 2 of these instances, and the solution was more voodoo
and magic than examining errors/logs and fixing code. I feel that I should
have a contingency plan in place for when I run into an issue with Spark
that can't be resolved.

Thanks everyone.


On Sat, Aug 12, 2023 at 2:18 PM Mich Talebzadeh 
wrote:

> OK you would not have known unless you went through the process so to
> speak.
>
> Let us do something revolutionary here 
>
> Install hive and its metastore. You already have hadoop anyway
>
> https://cwiki.apache.org/confluence/display/hive/adminmanual+installation
>
> hive metastore
>
>
> https://data-flair.training/blogs/apache-hive-metastore/#:~:text=What%20is%20Hive%20Metastore%3F,by%20using%20metastore%20service%20API
> .
>
> choose one of these
>
> derby  hive  mssql  mysql  oracle  postgres
>
> Mine is an oracle. postgres is good as well.
>
> HTH
>
> Mich Talebzadeh,
> Solutions Architect/Engineering Lead
> London
> United Kingdom
>
>
>view my Linkedin profile
> 
>
>
>  https://en.everybodywiki.com/Mich_Talebzadeh
>
>
>
> *Disclaimer:* Use it at your own risk. Any and all responsibility for any
> loss, damage or destruction of data or any other property which may arise
> from relying on this email's technical content is explicitly disclaimed.
> The author will in no case be liable for any monetary damages arising from
> such loss, damage or destruction.
>
>
>
>
> On Sat, 12 Aug 2023 at 18:31, Patrick Tucci 
> wrote:
>
>> Yes, on premise.
>>
>> Unfortunately after installing Delta Lake and re-writing all tables as
>> Delta tables, the issue persists.
>>
>> On Sat, Aug 12, 2023 at 11:34 AM Mich Talebzadeh <
>> mich.talebza...@gmail.com> wrote:
>>
>>> ok sure.
>>>
>>> Is this Delta Lake going to be on-premise?
>>>
>>> Mich Talebzadeh,
>>> Solutions Architect/Engineering Lead
>>> London
>>> United Kingdom
>>>
>>>
>>>view my Linkedin profile
>>> 
>>>
>>>
>>>  https://en.everybodywiki.com/Mich_Talebzadeh
>>>
>>>
>>>
>>> *Disclaimer:* Use it at your own risk. Any and all responsibility for
>>> any loss, damage or destruction of data or any other property which may
>>> arise from relying on this email's technical content is explicitly
>>> disclaimed. The author will in no case be liable for any monetary damages
>>> arising from such loss, damage or destruction.
>>>
>>>
>>>
>>>
>>> On Sat, 12 Aug 2023 at 12:03, Patrick Tucci 
>>> wrote:
>>>
 Hi Mich,

 Thanks for the feedback. My original intention after reading your
 response was to stick to Hive for managing tables. Unfortunately, I'm
 running into another case of SQL scripts hanging. Since all tables are
 already Parquet, I'm out of troubleshooting options. I'm going to migrate
 to Delta Lake and see if that solves the issue.

 Thanks again for your feedback.

 Patrick

 On Fri, Aug 11, 2023 at 10:09 AM Mich Talebzadeh <
 mich.talebza...@gmail.com> wrote:

> Hi Patrick,
>
> There is not anything wrong with Hive On-premise it is the best data
> warehouse there is
>
> Hive handles both ORC and Parquet formal well. They are both columnar
> implementations of relational model. What you are