雑文発散

過去の日記

2015-08-11 [長年日記]

▼ [Hive] Hive の COALESCE 関数で NULL を避けてデフォルト値を入れる。でもそれ NVL 関数の方が適切だったよ、たぶん。

Hive で複数のテーブルを LEFT JOIN するとき、左側のテーブルには存在するけど、右側のテーブルには存在しない値があると、そのカラムは NULL になってしまう。

NULL じゃなくて、デフォルト値をいれたいんだよねーと思って方法を探ってみたら、COALESCE という関数にたどり着いた。引数の中から最初に現れた NULL じゃないヤツを返してくれるようだ。Hive の LanguageManual UDF にはこんな感じで書かれている。

Name (Signature)	Description
COALESCE(T v1, T v2, ...)	Returns the first v that is not NULL, or NULL if all v's are NULL.

つまり、次のように書けば、デフォルト値を返すことができる。

SELECT
  COALESCE(table_b.posibble_null_column, 0) -- default value is 0
FROM
  table_a LEFT JOIN table_b ON (table_a.id = table_b.id)
  ...

この COALESCE 関数は、Hive 専用という訳ではなく、PostgreSQL や MySQL にも存在する。というか、標準 SQL なんだな、これ。知らなかったし、なんと読めばいいのか綴りを見ても全く分からないので覚えられない。「MYSQLの関数 COALESCE」によると、読み方は「コーラス」らしい。

後から思いついたんだけど、NULL だったらデフォルト値を入れるという動作なら、オレがよく使う CASE 文での条件分岐でも実現できたし、さらに調べてみると NVL 関数というのもあった。同じく Hive のリファレンスではこのように書かれている。

Name (Signature)	Description
nvl(T value, T default_value)	Returns default value if value is null else returns value (as of HIve 0.11).

なんでリファレンス内の COALESCE は大文字で nvl は小文字なんだ？という疑問はあるのだが、「引数の値が NULL だったらデフォルト値を返す」という期待の動作そのものじゃないか、NVL 関数は。

COALESCE 関数を使ったコード修正は「pull request は作ったけど、まだレビューの依頼を出してない状態」なので、今のうちに NVL 関数で動作確認してみよう。問題がなければ、NVL 関数を使ったコードに修正した上でレビューの依頼を出そうっと。

NVL 関数のほうが「目的」を明確に表しているので、後からコード（HiveQL）を触る場合に、他の人にも未来のオレにも意図が伝わりやすくなるだろうし。

【追記】

Facebook で「NVL 関数は Oracle の方言である」というフィードバックを頂いた。確かにそこは調べていなかった。Oracle / Hive に閉じこもるなら NVL 関数でも良いけど、標準 SQL 的な知識を蓄えるなら COALESCE 関数の方が適切かもしれない。

たいした話じゃなくてもアウトプットしてみると、より良い知見が頂けてしまう流れ、たいへんありがたい。

本日のツッコミ(全10件) [ツッコミを入れる]

▽ Harriet Whish (2018-11-21 03:15)

✅I Will Promote Your Business In Worldwide To Any Niche ... 1.I search in google after the keyward offered by you in order to find sites

▽ Jeramy Coates (2019-02-22 20:02)

Hi there I just checked out your website tdiary.net and your site takes longer than 3 seconds to load :( If your site takes so Long Time To Load you are losing more than half your traffic & 50% of visitors won't return if they have trouble loading a page... That's a really expensive mistake to make, specially if you are paying for traffic. I can help you to improve that. Please contact me by my email for details: wp_optimiser@mail.com Regards, Coates

▽ Reed Kershaw (2019-03-22 18:57)

Hello there I just checked out your website tdiary.net and wanted to find out if you need help for SEO Link Building ? If you aren't using SEO Software then you will know the amount of work load involved in creating accounts, confirming emails and submitting your contents to thousands of websites. With THIS SOFTWARE the link submission process will be the easiest task and completely automated, you will be able to build unlimited number of links and increase traffic to your websites which will lead to a higher number of customers and much more sales for you. IF YOU ARE INTERESTED, We offer you 7 days free trial CONTACT US ==> seosubmitter@mail.com Regards, Kershaw

▽ Roseanne Soutter (2019-04-09 18:44)

Hello NO MATTER WHAT YOU WANT, WE HAVE - so do not hesitate to ask even if it is not listed below !! I provide the following mentioned services. * ADWORDS $100 coupon * BING $100 coupon - now you can use two bing ads coupons per account. * AWS Amazon $150 coupons * Digital ocean $50 coupon * WINDOWS keys lifetime license -version 7,8 and 10 * OFFICE 365 lifetime access * OFFICE 2016 AND 2019 Product Keys * NETFLIX Accounts with 6 months validity * GMAIL accounts * ADWORDS Verified account with $500 credits + VPS * BING verified account with $100 credits + VPS * AMAZON AWS RDP/VPS Free Tier accounts * Google CLOUD accounts with $300 credits. * Lynda.com Accounts * DOMINOS & PIZZA HUT PIZZAS available(for USA) only * EDU emails * AZURE Accounts with $200 credits * ADSENSE Accounts with domain and Website * Quality NICHE Websites/Blogs * CPA Accounts Approval of any company * PAYPAL verified accounts with NO 21 days hold * ANY OTHER SERVICE you are looking for If you are interested in the following services, please let me know here: bestservice@post.com Thanks

▽ Janis Wilkes (2019-07-01 14:58)

Hello Tired of Waiting FOREVER to earn a profit online? NEW Web-App Allows You To Legally Hijack Traffic And Authority From Wikipedia AND YouTube To Earn Affiliate Commissions In 24 Hours Or Less - in ANY Niche! No Previous Skills Or Experience Required. You can literally be a COMPLETE Newbie and Get RESULTS with just 5 minutes of actual “work”.. IF YOU ARE INTERESTED, CONTACT US ==> sayedasaliha748@gmail.com And Of Course, You Also Have A 30-DAY, YOUR-MONEY Back GUARANTEE Once you Join TODAY, You'll Also GET AMAZING BONUSES Regards, TrafficJacker

▽ Sherlene Pelsaert (2019-07-17 11:38)

Hello! You Need Leads, Sales, Conversions, Traffic for tdiary.net ? Will Findet... I WILL SEND 5 MILLION MESSAGES VIA WEBSITE CONTACT FORM Don't believe me? Since you're reading this message then you're living proof that contact form advertising works! We can send your ad to people via their Website Contact Form. IF YOU ARE INTERESTED, Contact us => lisaf2zw526@gmail.com Regards, Pelsaert

▽ Dane Heflin (2019-08-05 13:10)

Good day! You Need Leads, Sales, Conversions, Traffic for tdiary.net ? Will Findet... I WILL SEND 5 MILLION MESSAGES VIA WEBSITE CONTACT FORM Don't believe me? Since you're reading this message then you're living proof that contact form advertising works! We can send your ad to people via their Website Contact Form. IF YOU ARE INTERESTED, Contact us => lisaf2zw526@gmail.com Regards, Heflin

▽ Dorcas Atkinson (2019-09-12 12:22)

Howdy I just checked out your website tdiary.net and wanted to find out if you need help for SEO Link Building ? If you aren't using SEO Software then you will know the amount of work load involved in creating accounts, confirming emails and submitting your contents to thousands of websites. With THIS SOFTWARE the link submission process will be the easiest task and completely automated, you will be able to build unlimited number of links and increase traffic to your websites which will lead to a higher number of customers and much more sales for you. IF YOU ARE INTERESTED, We offer you 7 days free trial CONTACT US ==> seosubmitter@mail.com Regards, Best Seo Software

▽ Sylvester Robert (2020-04-15 18:32)

Discounts on popular products How do you do? Only we have such low prices. Choose what you like. Hot Sales 40% : - Dresses - Men's Sets - Mobile Phone Accessories - Office Software - Video Games - Beads & Jewelry Making - Major Appliances - Bags & Shoes - Puzzles & Games - Sneakers - Bath & Shower - Bathroom Fixtures You can see even more here: https://rb.gy/yk3vte The best offer on the market. Best price and fast delivery.

▽ Rocky Hargrave (2020-04-28 12:03)

Hi there DFY Suite is an established, high-quality social syndication system that allows you to get stunning content syndication for your videos or niche sites WITHOUT having to do ANY of the work yourself. + There is NO software to download or install + There is NO account creation needed on your part + There is NO having to deal with proxies of captchas + There are NO complicated tutorials you have to watch DFY Suite 2 with more advanced features will become your powerful tool to serve the online marketing industry which gets more and more competitive. You will be able to skyrocket your business with very little effort. MORE INFO HERE=> https://bit.ly/2yObYYs Kind Regards, Rocky Hargrave