Hive(十)--数据类型转换、字符串函数、条件判断

我会带着你远行 2022-06-17 00:24 504阅读 0赞

数据类型转换

同Java语言一样，Hive也包括 隐式转换（implicit conversions）和显式转换（explicitly conversions）。  
　　Hive在需要的时候将会对numeric类型的数据进行隐式转换。比如我们对两个不同数据类型的数字进行比较，假如一个数据类型是INT型，另一个 是SMALLINT类型，那么SMALLINT类型的数据将会被隐式转换地转换为INT类型，这个到底和Java中的一样；但是我们不能隐式地将一个 INT类型的数据转换成SMALLINT或TINYINT类型的数据，这将会返回错误，除非你使用了CAST操作。

任何整数类型都可以隐式地转换成一个范围更大的类型。TINYINT,SMALLINT,INT,BIGINT,FLOAT和STRING都可以隐式 地转换成DOUBLE；是的你没看出，STRING也可以隐式地转换成DOUBLE！但是你要记住，BOOLEAN类型不能转换为其他任何数据类型！

下标列出了Hive内置的数据类型之间是否可以进行隐式的转换操作:

<table style="margin:0px; padding:0px; color:rgb(50,62,50); font-family:'Microsoft YaHei','Helvetica Neue',SimSun; font-size:14px; border:1px solid rgb(255,102,0); text-align:center"> 
 <tbody> 
  <tr> 
   <th style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif">&nbsp;</th> 
   <th style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif">bl</th> 
   <th style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif">tinyint</th> 
   <th style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif">si</th> 
   <th style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif">int</th> 
   <th style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif">bigint</th> 
   <th style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif">float</th> 
   <th style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif">double</th> 
   <th style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif">dm</th> 
   <th style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif">string</th> 
   <th style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif">vc</th> 
   <th style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif">ts</th> 
   <th style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif">date</th> 
   <th style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif">ba</th> 
  </tr> 
  <tr> 
   <th style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif">boolean</th> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> true</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
  </tr> 
  <tr> 
   <th style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif">tinyint</th> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> true</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> true</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> true</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> true</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> true</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> true</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> true</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> true</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> true</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
  </tr> 
  <tr> 
   <th style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif">smallint</th> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> true</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> true</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> true</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> true</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> true</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> true</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> true</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> true</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
  </tr> 
  <tr> 
   <th style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif">int</th> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> true</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> true</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> true</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> true</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> true</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> true</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> true</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
  </tr> 
  <tr> 
   <th style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif">bigint</th> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> true</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> true</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> true</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> true</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> true</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> true</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
  </tr> 
  <tr> 
   <th style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif">float</th> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> true</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> true</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> true</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> true</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> true</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
  </tr> 
  <tr> 
   <th style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif">double</th> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> true</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> true</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> true</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> true</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
  </tr> 
  <tr> 
   <th style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif">decimal</th> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> true</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> true</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> true</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
  </tr> 
  <tr> 
   <th style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif">string</th> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> true</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> true</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> true</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> true</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
  </tr> 
  <tr> 
   <th style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif">varchar</th> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> true</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> true</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> true</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> true</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
  </tr> 
  <tr> 
   <th style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif">ts</th> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> true</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> true</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> true</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
  </tr> 
  <tr> 
   <th style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif">date</th> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> true</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> true</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> true</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
  </tr> 
  <tr> 
   <th style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif">binary</th> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> false</td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> true</td> 
  </tr> 
 </tbody> 
</table>

注：由于表格比较大，这里对一些比较长的字符串进行缩写，ts是timestamp的缩写,bl是boolean的缩写,sl是smallint的缩写,dm是decimal的缩写,vc是varchar的缩写,ba是binary的缩写。

我们可以用CAST来显式的将一个类型的数据转换成另一个数据类型。如何使用？CAST的语法为cast(value AS TYPE)。举个例子：假如我们一个员工表employees，其中有name、salary等字段；salary是字符串类型的。有如下的查询：

<table style="margin:0px; padding:0px"> 
 <tbody> 
  <tr> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> <code>1</code></td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> <code>SELECT name, salary FROM employees</code></td> 
  </tr> 
 </tbody> 
</table>

<table style="margin:0px; padding:0px"> 
 <tbody> 
  <tr> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> <code>2</code></td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> <code>WHERE cast(salary AS FLOAT) &lt;&lt;/code&gt;</code>&nbsp;100000.0<code>;</code></td> 
  </tr> 
 </tbody> 
</table>

这样salary将会显示的转换成float。如果salary是不能转换成float，这时候cast将会返回NULL！  
　　对cast有一下几点需要说明的：  
　　(1)、如果将浮点型的数据转换成int类型的，内部操作是通过round()或者floor()函数来实现的，而不是通过cast实现！  
　　(2)、对于BINARY类型的数据，只能将BINARY类型的数据转换成STRING类型。如果你确信BINARY类型数据是一个数字类型(a number)，这时候你可以利用嵌套的cast操作，比如a是一个BINARY，且它是一个数字类型，那么你可以用下面的查询：

<table style="margin:0px; padding:0px"> 
 <tbody> 
  <tr> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> <code>1</code></td> 
   <td style="margin:0px; padding:0px; font-family:Verdana,宋体,sans-serif; line-height:18px"> <code>SELECT (cast(cast(a as string) as</code>&nbsp;<code>double</code><code>)) from src;</code></td> 
  </tr> 
 </tbody> 
</table>

我们也可以将一个String类型的数据转换成BINARY类型。  
　　(3)、对于Date类型的数据，只能在Date、Timestamp以及String之间进行转换。下表将进行详细的说明：

字符串函数

字符串长度函数：length

Java代码 ![收藏代码][icon_star.png]

1.  语法: length(string A) 
2.  返回值: **int**
3.  说明：返回字符串A的长度 
4.  举例： 
5.  hive> select length(‘abcedfg’) from dual; 
6.  7

字符串反转函数：reverse

Java代码 ![收藏代码][icon_star.png]

1.  语法: reverse(string A) 
2.  返回值: string 
3.  说明：返回字符串A的反转结果 
4.  举例： 
5.  hive> select reverse(‘abcedfg’) from dual; 
6.  gfdecba

字符串连接函数：concat

Java代码 ![收藏代码][icon_star.png]

1.  语法: concat(string A, string B…) 
2.  返回值: string 
3.  说明：返回输入字符串连接后的结果，支持任意个输入字符串 
4.  举例： 
5.  hive> select concat(‘abc’,'def’,'gh’) from dual; 
6.  abcdefgh

带分隔符字符串连接函数：concat\_ws

Java代码 ![收藏代码][icon_star.png]

1.  语法: concat\_ws(string SEP, string A, string B…) 
2.  返回值: string 
3.  说明：返回输入字符串连接后的结果，SEP表示各个字符串间的分隔符 
4.  举例： 
5.  hive> select concat\_ws(‘,’,'abc’,'def’,'gh’) from dual; 
6.  abc,def,gh

字符串截取函数：substr,substring

Java代码 ![收藏代码][icon_star.png]

1.  语法: substr(string A, **int** start),substring(string A, **int** start) 
2.  返回值: string 
3.  说明：返回字符串A从start位置到结尾的字符串 
4.  举例： 
5.  hive> select substr(‘abcde’,3) from dual; 
6.  cde 
7.  hive> select substring(‘abcde’,3) from dual; 
8.  cde 
9.  hive> select substr(‘abcde’,-1) from dual; （和ORACLE相同） 
10. e

字符串截取函数：substr,substring

Java代码 ![收藏代码][icon_star.png]

1.  语法: substr(string A, **int** start, **int** len),substring(string A, **int** start, **int** len) 
2.  返回值: string 
3.  说明：返回字符串A从start位置开始，长度为len的字符串 
4.  举例： 
5.  hive> select substr(‘abcde’,3,2) from dual; 
6.  cd 
7.  hive> select substring(‘abcde’,3,2) from dual; 
8.  cd 
9.  hive>select substring(‘abcde’,-2,2) from dual; 
10. de

字符串转大写函数：upper,ucase

Java代码 ![收藏代码][icon_star.png]

1.  语法: upper(string A) ucase(string A) 
2.  返回值: string 
3.  说明：返回字符串A的大写格式 
4.  举例： 
5.  hive> select upper(‘abSEd’) from dual; 
6.  ABSED 
7.  hive> select ucase(‘abSEd’) from dual; 
8.  ABSED

字符串转小写函数：lower,lcase

Java代码 ![收藏代码][icon_star.png]

1.  语法: lower(string A) lcase(string A) 
2.  返回值: string 
3.  说明：返回字符串A的小写格式 
4.  举例： 
5.  hive> select lower(‘abSEd’) from dual; 
6.  absed 
7.  hive> select lcase(‘abSEd’) from dual; 
8.  absed

去空格函数：trim

Java代码 ![收藏代码][icon_star.png]

1.  语法: trim(string A) 
2.  返回值: string 
3.  说明：去除字符串两边的空格 
4.  举例： 
5.  hive> select trim(‘ abc ‘) from dual; 
6.  abc

左边去空格函数：ltrim

Java代码 ![收藏代码][icon_star.png]

1.  语法: ltrim(string A) 
2.  返回值: string 
3.  说明：去除字符串左边的空格 
4.  举例： 
5.  hive> select ltrim(‘ abc ‘) from dual; 
6.  abc

右边去空格函数：rtrim

Java代码 ![收藏代码][icon_star.png]

1.  语法: rtrim(string A) 
2.  返回值: string 
3.  说明：去除字符串右边的空格 
4.  举例： 
5.  hive> select rtrim(‘ abc ‘) from dual; 
6.  abc

正则表达式解析函数：regexp\_extract

其中的index，是按照正则字符串（）的位置

Java代码 ![收藏代码][icon_star.png]

1.  语法: regexp\_extract(string subject, string pattern, **int** index) 
2.  返回值: string 
3.  说明：将字符串subject按照pattern正则表达式的规则拆分，返回index指定的字符。注意，在有些情况下要使用转义字符 
4.  举例： 
5.  hive> select regexp\_extract(‘foothebar’, ‘foo(.\*?)(bar)’, 1) from dual; 
6.  the 
7.  hive> select regexp\_extract(‘foothebar’, ‘foo(.\*?)(bar)’, 2) from dual; 
8.  bar 
9.  hive> select regexp\_extract(‘foothebar’, ‘foo(.\*?)(bar)’, 0) from dual; 
10. foothebar

函数parse\_url,解析URL字符串

Java代码 ![收藏代码][icon_star.png]

1.  parse\_url(url, partToExtract\[, key\]) - extracts a part from a URL 
2.  解析URL字符串，partToExtract的选项包含\[HOST,PATH,QUERY,REF,PROTOCOL,FILE,AUTHORITY,USERINFO\]。 
3.  
4.  举例： 
5.  \* parse\_url('http://facebook.com/path/p1.php?query=1', 'HOST')返回'facebook.com'
6.  \* parse\_url('http://facebook.com/path/p1.php?query=1', 'PATH')返回'/path/p1.php'
7.  \* parse\_url('http://facebook.com/path/p1.php?query=1', 'QUERY')返回'query=1'， 
8.  可以指定key来返回特定参数，例如 
9.  \* parse\_url('http://facebook.com/path/p1.php?query=1', 'QUERY','query')返回'1'， 
10. 
11. \* parse\_url('http://facebook.com/path/p1.php?query=1\#Ref', 'REF')返回'Ref'
12. \* parse\_url('http://facebook.com/path/p1.php?query=1\#Ref', 'PROTOCOL')返回'http'

json解析函数：get\_json\_object

语法: get\_json\_object(string json\_string, string path)

Java代码 ![收藏代码][icon_star.png]

1.  返回值: string 
2.  说明：解析json的字符串json\_string,返回path指定的内容。如果输入的json字符串无效，那么返回NULL。 
3.  举例： 
4.  hive> select get\_json\_object(‘\{“store”: 
5.  > \{“fruit”:\\\[\{ "weight":8,"type":"apple"\},\{ "weight":9,"type":"pear"\}\], 
6.  > “bicycle”:\{“price”:19.95,”color”:”red”\} 
7.  > \}, 
8.  > “email”:”amy@only\_for\_json\_udf\_test.net”, 
9.  > “owner”:”amy” 
10. > \} 
11. > ‘,’$.owner’) from dual; 
12. amy

使用实例：

Java代码 ![收藏代码][icon_star.png]

1.  select get\_json\_object('\{"store":\{"fruit":\\\["aa","bb","cc"\]\},"owner":"amy"\}','$.store.fruit\[0\]') from test\_msg limit 1;

空格字符串函数：space

语法: space(int n)

Java代码 ![收藏代码][icon_star.png]

1.  返回值: string 
2.  说明：返回长度为n的字符串 
3.  举例： 
4.  hive> select space(10) from dual; 
5.  hive> select length(space(10)) from dual; 
6.  10

重复字符串函数：repeat

语法: repeat(string str, int n)

Java代码 ![收藏代码][icon_star.png]

1.  返回值: string 
2.  说明：返回重复n次后的str字符串 
3.  举例： 
4.  hive> select repeat(‘abc’,5) from dual; 
5.  abcabcabcabcabc

首字符ascii函数：ascii

语法: ascii(string str)

Java代码 ![收藏代码][icon_star.png]

1.  返回值: **int**
2.  说明：返回字符串str第一个字符的ascii码 
3.  举例： 
4.  hive> select ascii(‘abcde’) from dual; 
5.  97

左补足函数：lpad

语法: lpad(string str, int len, string pad)

Java代码 ![收藏代码][icon_star.png]

1.  返回值: string 
2.  说明：将str进行用pad进行左补足到len位 
3.  举例： 
4.  hive> select lpad(‘abc’,10,’td’) from dual; 
5.  tdtdtdtabc

与GP，[Oracle][]不同，pad 不能默认

右补足函数：rpad

语法: rpad(string str, int len, string pad)

Java代码 ![收藏代码][icon_star.png]

1.  返回值: string 
2.  说明：将str进行用pad进行右补足到len位 
3.  举例： 
4.  hive> select rpad(‘abc’,10,’td’) from dual; 
5.  abctdtdtdt

分割字符串函数: split

语法: split(string str, string pat)

Java代码 ![收藏代码][icon_star.png]

1.  返回值: array 
2.  说明: 按照pat字符串分割str，会返回分割后的字符串数组 
3.  举例： 
4.  hive> select split(‘abtcdtef’,'t’) from dual; 
5.  \["ab","cd","ef"\]

集合查找函数: find\_in\_set

语法: find\_in\_set(string str, string strList)

Java代码 ![收藏代码][icon_star.png]

1.  返回值: **int**
2.  说明: 返回str在strlist第一次出现的位置，strlist是用逗号分割的字符串。如果没有找该str字符，则返回0
3.  举例： 
4.  hive> select find\_in\_set(‘ab’,'ef,ab,de’) from dual; 
5.  2
6.  hive> select find\_in\_set(‘at’,'ef,ab,de’) from dual; 
7.  0
8.

条件判断

## CONDITIONAL FUNCTIONS IN HIVE ##

Hive supports three types of conditional functions. These functions are listed below:  
  
**IF( Test Condition, True Value, False Value )**   
The IF condition evaluates the “Test Condition” and if the “Test Condition” is true, then it returns the “True Value”. Otherwise, it returns the False Value.  
Example: IF(1=1, 'working', 'not working') returns 'working'  
  
**COALESCE( value1,value2,... )**  
  
The COALESCE function returns the fist not NULL value from the list of values. If all the values in the list are NULL, then it returns NULL.  
Example: COALESCE(NULL,NULL,5,NULL,4) returns 5  
  
**CASE Statement**  
  
The syntax for the case statement is:

> CASE \[ expression \]  
> WHEN condition1 THEN result1  
> WHEN condition2 THEN result2  
> ...  
> WHEN conditionn THEN resultn  
> ELSE result  
> END

Here expression is optional. It is the value that you are comparing to the list of conditions. (ie: condition1, condition2, ... conditionn).  
  
All the conditions must be of same datatype. Conditions are evaluated in the order listed. Once a condition is found to be true, the case statement will return the result and not evaluate the conditions any further.  
  
All the results must be of same datatype. This is the value returned once a condition is found to be true.  
  
IF no condition is found to be true, then the case statement will return the value in the ELSE clause. If the ELSE clause is omitted and no condition is found to be true, then the case statement will return NULL  
  
Example:

> CASE Fruit  
> WHEN 'APPLE' THEN 'The owner is APPLE'  
> WHEN 'ORANGE' THEN 'The owner is ORANGE'  
> ELSE 'It is another Fruit'  
> END

The other form of CASE is

> CASE  
> WHEN Fruit = 'APPLE' THEN 'The owner is APPLE'  
> WHEN Fruit = 'ORANGE' THEN 'The owner is ORANGE'  
> ELSE 'It is another Fruit'  
> END

[icon_star.png]: /images/20220617/e8216951ec134b73ad92ef4d03c2412b.png
[Oracle]: http://lib.csdn.net/base/oracle