一、引入:
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)