将IPv4字符串转换为Spark SQL中的bigint表示

Question

如何使用Spark SQL（Hiveish）

Answer 1

这可以使用乘法完成：

sqlContext.sql("""SELECT 
       (cast(split(l.klientip,'\.')[0] as bigint) * 16777216) 
     + (cast(split(l.klientip,'\.')[1] as bigint) * 65536 ) 
     + (cast(split(l.klientip,'\.')[2] as bigint) * 256) 
     + (cast(split(l.klientip,'\.')[3] as bigint) ) ip_add_int 
     from loggik l
     """).show()

或bitshifting（与乘法相同）

sqlContext.sql("""SELECT
      (shiftLeft(cast(split(l.klientip,'\.')[0] as bigint),24) 
        +shiftLeft(cast(split(l.klientip,'\.')[1] as bigint),16)
        +shiftLeft(cast(split(l.klientip,'\.')[2] as bigint),8)
        +cast(split(l.klientip,'\.')[3] as bigint)) ip_shift_int 
     from loggik l
     """).show()