Impala工作常用函数和诡异bug问题总结[1]

一、引入：

Impala是什么在这里我不做解释，只对impala实际工作中所遇到的问题和常用的函数做一个总结。

二、Impala常用函数

<一>drop，truncate，delete区别

1、drop (删除表)：删除内容和定义，释放空间。简单来说就是把整个表去掉.以后要新增数据是不可能的,除非新增一个表。

drop语句将删除表的结构被依赖的约束（constrain),触发器（trigger)索引（index);依赖于该表的存储过程/函数将被保留，但其状态会变为：invalid。

2、truncate (清空表中的数据)：删除内容、释放空间但不删除定义(保留表的数据结构)。与drop不同的是,只是清空表数据而已。

注意:truncate 不能删除行数据,要删就要把表清空。

3、delete (删除表中的数据)：delete 语句用于删除表中的行。delete语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存

以便进行进行回滚操作。

truncate与不带where的delete ：只删除数据，而不删除表的结构（定义）

4、truncate table 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用delete。

如果要删除表定义及其数据，请使用 drop table 语句。

5、对于由foreign key约束引用的表，不能使用truncate table ，而应使用不带where子句的delete语句。由于truncate table 记录在日志中，所以它不能激活触发器。

6、执行速度，一般来说: drop> truncate > delete。

7、delete语句是数据库操作语言(dml)，这个操作会放到 rollback segement 中，事务提交之后才生效；如果有相应的 trigger，执行的时候将被触发。

truncate、drop 是数据库定义语言(ddl)，操作立即生效，原数据不放到 rollback segment 中，不能回滚，操作不触发 trigger。

<二>NVL()函数

NVL（表达式1，表达式2）

如果表达式1为空值，NVL返回值为表达式2的值，否则返回表达式1的值。 该函数的目的是把一个空值（null）转换成一个实际的值。其表达式的值可以是数字型、字符型和日期型。但是表达式1和表达式2的数据类型必须为同一个类型。

<三>instr()和

instr(c1,c2,i,j) 在一个字符串中搜索指定的字符,返回发现指定的字符的位置;

c1 被搜索的字符串

c2 希望搜索的字符串

i 搜索的开始位置,默认为1

j 出现的位置,默认为1

SQL> select instr("abcde",'b');

e",'b');

结果是2，即在字符串“abcde”里面，字符串“b”出现在第2个位置。如果没有找到，则返回0；不可能返回负数

简单一句就是：instr函数返回字符串str中子字符串substr第一次出现的位置，在sql中第一字符的位置是1,如果 str不含substr返回0。

substr()函数

1、作用：用来截取数据库某个字段中的一部分。

2、语法：substr(string,start,length)

string参数：必选。数据库中需要截取的字段。

start参数：必选。正数，从字符串指定位子开始截取；

负数，从字符串结尾指定位子开始截取；

0，在字符串中第一个位子开始截取。1，同理。(特殊)

length参数：可选。需要截取的长度。缺省，即截取到结束位置。

注意：若必选参数为空，那返回的结果也为空。

3、简单示例：substr('123456',3,2); 结果是--34

substr('123456',-3,2); 结果是--45

substr('123456',0,2); 特殊，与下同

substr('123456',1,2); 结果同为--12

4、补充：在各个数据库的函数名称略有差异。

MySQL--substr()或substring()

Oracle--substr()

SQL Server--substring()

5、提示：①与java类中的substring()名称相似，但方法不同，千万别混淆。

②substr()函数截取字符串的*内容*;

与之比较--instr()函数是截取的字符串在源字符串中的*位子*。

<四>时间转换问题 from_unixtime() ，unix_timestamp()

Select unix_timestamp();

这里将会直接返回从格林威治时间1970年01月01日00时00分00秒起至现在的总秒数。

配合from_unixtime()可以转换成需要的日期格式

用法实例1： select from_unixtime(unix_timestamp(),'yyyyMMdd HH:mm:ss') as update_time --更新时间

这个可以用来作为每行记录值更新的时间。

<五>时间函数to_date(timestamp date)

从时间戳值返回日期字段的字符串表示形式。

示例：select to_date(‘2020-05-15 10:45:01’);将会返回2020-05-15

<六>时间函数from_timestamp()

将指定的时间戳转换为具有给定格式的字符串。

示例：

select from_timestamp(to_date('2020-05-15 10:45:01'),'yyyy/MM/dd')

返回：

2020/05/15

<七>对记录值后有备注内容有括号，需要去除备注内容，也就是去括号操作

Select case when cast(instr(user_reason,'（',1) as int)=0 then user_reason else substr(user_reason,1,instr(user_reason,'（',1)-1 ) end

上面这段sql的意思是，如果user_reason该字段没有括号内容，那就返回原字段，如果user_reason该字段有括号，那就去错括号开始后面的所有内容。这段sql语句的操作可以用来作为很多方面对记录值的截取和清晰

实例：如果user_reason=’cat’ 那就上面的sql就直接返回cat

如果 user_reason=’home(mouse)’那就上面的sql就直接返回home

<八>去重问题与及三逻辑语法

1、关于distinct去重：这是针对一张表的内部数据去重。比如使用distinct *，是针对整张表内部数据去重。

2、关于union去重：这是针对两张表之间数据去重，并且排序。并不会去重每张表之间重复的数据。

简单的说就是distinct是对内去重，union是对外去重并且排序，合并表。

Union all 不去重也不排序进行合并。

3、关于两张表数据去重：

where 1 = 1

and not exists( select 1 from 第二张表s2

where 第一张表主键= 第二张表主键 )

这段sql表达，是说如果第一张表内有第二张表的数据，就不要，只取第一张表独有的数据。

4、sql三逻辑语法问题：简单说的就是，sql里并不是非黑即白的逻辑判断。

比如，一个字段test，有三种状态，分别是1，0，null

1>如果语法写：where test <>1

其所过滤的结果是，只会留下0，会过滤1和null值

2>如果语法写：where test not in (1)

其所过滤的结果是，只会留下0，会过滤1和null值

所以sql语法在写时，尽量的枚举所有的状态，而不是取反。如果取反会过滤空值，这会导致数据减少。当然也可以利用取反可以过滤空值的特性来去除空值一种逻辑写法。

三逻辑贯穿整个sql，也包括其他sql引擎

三、Impala诡异bug

<1>偶尔丢失一两条数据问题：

描述：在报表中检查发现一个字段少了一条数据，进行问题排查，追溯到源数据上，发现select查出来有数据,但是在调度里insert插入进去变成空值或没有数据。如果是全部没有数据也好理解，可能系统奔溃了，重新搭建就是。但奇怪的是，其它值插入进去就正常，却偏偏这个字段这一条数据发生了丢失。这简直就是要挠墙的结果啊。

开始我对这个现象很无法理解，因为这是不可能出现的事情，然而发生了。解决肯定要是解决的。查了一下午，最终可以肯定就是查出来这一条数据没有插入进去，变成了空值

后面在检查到建表语句时发现，这个表的存储格式是textfile行式存储，我猜想有没有可能和存储格式有关系呢？于是我就删表重建，采用了parquet列式存储格式。然后重新调度，神奇事情发生了，该字段丢失这一条数据有数据了。

为此可能是Impala对textfile行式存储不大友好，偶尔会发生丢失数据的情况，直接表现就是可以查出来但是就是存入不进去，然后就这么一两条丢失了。这种情况发生时不要慌，先检查建表语句存储格式。建议对所有在impala的表都换成stored as parquet这种格式

<2>substr()截取问题在impala和在hive结果乱码问题

实例：

select substr('【小猫】home',1,4)

比如上面这段sql，这Hive里执行是正常的，但是在impala执行就会乱码。可能你还找不到原因。

这是因为hive里对每个汉字就是一个字符，一个标点也是一个字符；

结果就是：【小猫】

但是impala对每个汉字代表是三个字符，一个英文符号是一个字符如果要保持结果一致的话，就需要这样写：

select substr('【小猫】home',1,8)