Azure Data Factory & Hive Nightmare
“Hey… I was monitoring this job in Azure Data Factory, it’s strange…” ping one of my support team members. “Do you mind jumping to call?”.
That’s how my typical day will goes, and usually this doesn’t end well.
So, we have pipeline that we have deployed in Azure Data Factory. It has been working fine for past 2 years! I feel proud of that.
You know… No Error, No slowness, No complain from user, No complain from Finance (Azure can be expensive… but for of that next time).
The pipeline basically run bunch of activities, and one it has “Hive” activity.
It picks up the HQL script from our azure storage, hand it over to HDInsight cluster, and run it. SIMPLE!
HQL file it self is a simple “insert overwrite” table via simple select-from-join-where-aggregate kind of thing. No drama.
The strange thing is… from monitoring we can see that the job only ran for 10 seconds. That’s FAST! RIGHT?! RIGHT!? No……
Apparently the Hive activity only ran 3 parts of HQL script (which initialize parameters and inject some hive magic powder of settings).
The real query itself never get executed. I was like… WT…
I checked hive output from the azure storage… the query is correct… but the output only show 3 configurations executed successfully, the “INSERT OVERWRITE” never get executed. PANIK!
Start digging the HDInsight logs, SSH, cd, view, less, tail, all my toolkit being used that day. but.. nada, nothing… no error, or exception or anything.
We are using Azure Integration Runtime, I was thinking may be something goes wrong with Azure IR (we have ticket with Microsoft about it — in the past Microsoft release bad HIVE ODBC driver, and it kill jobs). So, I told my team to try to use our custom IR, as we can enable ODBC driver logs, and we set to TRACE!
After retrying the job couple of run, we still have the same result, the hql only get executed partially.
Get sgcoder’s stories in your inbox
Join Medium for free to get updates from this writer.
I then asked my team to try to run it in Dbeaver on lower env, to check if it’s ok. And…. YEAP.. it’s ok, working fine, table get overwritten perfectly.
I don't know man…. I don't know… it’s surreal.. i tried to run the same script from beeline from HeadNode of HDInsight, and it’s working…
Something wrong with this thing… all other HQL scripts are working fine…
ARGGH!!!!!
I told my team that I will check again later….
I walk of my chair, brew my coffee… look outside the window and admiring how the tree is beautiful. Isn’t that …. random!?
Anyway, I fire my visual studio to do some code for my pet project. it’s a C# code. write code… press F5 and it complain… fixed it and it’s back.
My brain said… HOLD-UP!!!!
Quickly i was rushing to start notepad++
Load up the damn HQL file…
My hand is moving as fast as it can, my heart is pumping….
I have the feel, the urge, to add ; semicolon… yeah, the damn SEMICOLON! at the end of the script for insert overwrite.
Upload the HQL to lower env azure storage account.
run Azure Data Factory pipeline…
And…… It’s working…
oh my gawd!
I just can’t believe it….
HOW THE HELL…..
Anway… I asked my QA lead and *WHACK* him…
I mean, i asked how on earth this can happen…
yada yada yada years later… he will ensure to check properly… in future…
I don't know… I swear and he (the QA dude) swear that it was working.
but…. hey…. What could go wrong, right? right!?
Welcome to IT world….
