JSON in Oracle

JSON(JavaScript Object Notation,JavaScript对象表示法,读作/ˈdʒeɪsən/)是一种由道格拉斯·克罗克福特构想和设计、轻量级的数据交换语言,该语言以易于让人阅读的文字为基础,用来传输由属性值或者序列性的值组成的数据对象。尽管JSON是JavaScript的一个子集,但JSON是独立于语言的文本格式,并且采用了类似于C语言家族的一些习惯。

JSON 数据格式与语言无关。即便它源自JavaScript,但当前很多编程语言都支持 JSON 格式数据的生成和解析。

相对于传统的关系型数据库,一些基于文档存储的NoSQL非关系型数据库选择JSON作为其数据存储格式,比较出名的产品有:MongoDB。

从12.1版本开始,oracle开始支持JSON格式数据,包括事务、索引和视图等。

创建包含JSON数据的表

oracle并没有增加新的字段类型来专门存放JSON数据,而是将其存放在传统的VARCHAR2或CLOB字段中。另外IS JSON约束表明这个字段包含有效的JSON格式数据。

创建一张表,包含判断数据是否为JSON格式的约束

drop table json_test purge;

create table json_test (
    id raw(16) not null,
    document clob
    CONSTRAINT ensure_json CHECK (document IS JSON)
);

这里的IS JSON约束默认情况下都代表弱约束,如果要做严格JSON语法约束,则换成document IS JSON(STRICT)

插入测试的JSON数据

INSERT INTO json_test
  VALUES (SYS_GUID(),
          '{ "PONumber"             : 1600,
          "Reference"            : "ABULL-20140421",
          "Requestor"            : "Alexis Bull",
          "User"                 : "ABULL",
          "CostCenter"           : "A50",
          "ShippingInstructions" : { "name"   : "Alexis Bull",
                                     "Address": { "street"  : "200 Sporting Green",
                                                  "city"    : "South San Francisco",
                                                  "state"   : "CA",
                                                  "zipCode" : 99236,
                                                  "country" : "United States of America" },
                                     "Phone" : [ { "type" : "Office", "number" : "909-555-7307" },
                                                 { "type" : "Mobile", "number" : "415-555-1234" } ] },
          "Special Instructions" : null,
          "AllowPartialShipment" : false,
          "LineItems"            : [ { "ItemNumber" : 1,
                                       "Part"       : { "Description" : "One Magic Christmas",
                                                        "UnitPrice"   : 19.95,
                                                        "UPCCode"     : 13131092899 },
                                       "Quantity"   : 9.0 },
                                     { "ItemNumber" : 2,
                                       "Part"       : { "Description" : "Lethal Weapon",
                                                        "UnitPrice"   : 19.95,
                                                        "UPCCode"     : 85391628927 },
                                       "Quantity"   : 5.0 } ] }');

如果更新数据为无效的JSON数据时,则会出现错误,这里假设去掉一个大括号

update json_test
    set document = '{"User" : "xb"'
where document.User = 'ABULL';

               *
ERROR at line 3:
ORA-01747: invalid user.table.column, table.column, or column specification

查询JSON数据

虽然例子中JSON数据是存放在CLOB字段当中,但是查询的返回值总是VARCAHR2类型

查询PONumber

SQL> col PONumber for a50
SQL> select a.id,a.document.PONumber from json_test a;

ID                               PONUMBER
-------------------------------- --------------------------------------------------
9AAB58F9BDD3DA0AE0536892A8C06893 1600

查询装运须知的Phone

SQL> col ShippingInstructions for a100
SQL> select a.document.ShippingInstructions.Phone from json_test a;

SHIPPINGINSTRUCTIONS
----------------------------------------------------------------------------------------------------
[{"type":"Office","number":"909-555-7307"},{"type":"Mobile","number":"415-555-1234"}]

查询phone的类型

SQL> select a.document.ShippingInstructions.Phone.type from json_test a;

SHIPPINGINSTRUCTIONS
----------------------------------------------------------------------------------------------------
["Office","Mobile"]

JSON字符集

文本化的JSON数据通常采用UNICODE字符集,从这个方面来说JSON数据比XML数据使用起来更简单,这是JSON数据内部交换格式的重要组成部分。对于Oracle中管理的JSON数据而言,所有需要进行字符集转换的操作都是自动进行。

Oracle内部使用UTF-8来操作JSON数据,包括解析、查询等。如果这些数据被传入或者被输出,必须要以非UTF-8的字符集时,则会自动做字符集之间的转换。

字符集自动转换会影响性能。并且在某些情况下会有信息丢失,将传入的数据转换成UTF-8这段过程是没有丢失的,但是转换成输出时如果在输出的字符集中无法显示字符的话则会丢失数据。

当数据库的字符集为Oracle建议的AL32UTF8时,如果文本化的JSON数据以UNICODE格式储存在数据库当中时则不需要做字符集的转换。

不以文本格式存储的JSON数据,永远不需要字符集转换:因为没有字符用来转换,这表示使用BLOB类型存放的JSON数据不需要经历字符集转换。

如果JSON数据是以非Unicode格式存放,使用非Unicode的VARCHAR2或CLOB字段,则需要考虑下面的方法去避免字符集的转换

  • 对于Oracle SQL函数返回的值使用NUMBER而不是VARCHAR2,例如json_value
  • 在输入时转义特定的Unicode字符

使用JSON的SQL函数和条件

Oracle提供了SQL函数和条件去创建、查询、操作JSON数据,其中一些将JSON路径表达式中作为参数,后面跟着RETURNING子句、WRAPPER子句或者error子句。

  • Oracle SQL Conditions IS JSON and IS NOT JSON 测试数据是否是符合语法的JSON数据,通常作为约束检查
  • Oracle SQL Condition JSON_EXISTS 测试JSON数据里是否存在特定的值
  • Oracle SQL Function JSON_VALUE 从JSON数据中选择一个标量值作为SQL值
  • Oracle SQL Function JSON_QUERY 从JSON数据中选择一个或多个值作为SQL串来表示JSON值,通常用来获取JSON文本的片段,特别是JSON对象或数组
  • Oracle SQL Function JSON_TABLE 将JSON数据映射成关系型的虚拟表,也可以认为是内联的关系型视图

JSON函数和条件中的子句

子句包含returningwrapper、和错误处理。每个子句都可以使用在一个或多个SQL函数和条件json_value, json_query, json_table, is json, is not json, and json_exists.

json_value和json_query允许可选的RETURNING子句,表示通过函数返回数据的类型。
对于json_value来说,可以允许VARCAHR2或者NUMBER类型返回值,而对于json_query,只能使用VARCHAR2。

json_value和json_query允许可选的WRAPPER子句,表示通过json_query返回值或json_table相关列的格式

  • WITH WRAPPER-用一个字符值表示包含所有JSON数据的数组,数组元素顺序不要求。
  • WITHOUT WRAPPER– 用一个字符表示单个JSON对象或者数组,如果路径表达式匹配上一个标量值或者多个值则会报错。
  • WITH CONDITIONAL WRAPPER-对于0个值、单个标量值或多个值与WITH WRAPPER一样,对于单个的JSON对象或数组值,则与WITHOUT WRAPPER一样。

默认是WITHOUT WRAPPER

JSON_QUERY Wrapper子句

JSON Values WITH WRAPPER WITHOUT WRAPPER WITH CONDITIONAL WRAPPER
{“id”: 38327} (single object) [{“id”: 38327}] {“id”: 38327} {“id”: 38327}
[42, “a”, true] (single array) [[42, “a”, true]] [42, “a”, true] [42, “a”, true]
42 [42] Error (scalar) [42]
42, “a”, true [42, “a”, true] Error (multiple values) [42, “a”, true]
none [] Error (no values) []

IS JSON and IS NOT JSON

之前的例子中是将IS JSON写在了约束中,但同时也可以写在查询条件里

drop table json_test purge;

 create table json_test 
 (
 id raw(16) not null,
 data clob,
constraint pk_json primary key(id));

insert into json_test values(sys_guid(), '{"name": "xb"}');
insert into json_test values(sys_guid(), 'dba');
commit;

# JSON_VALUE 使用NULL ON ERROR,对于非JSON数据返回NULL
col name for a10
select id,json_value(a.data, '$.name') as name from json_test a;

ID                               NAME
-------------------------------- ----------
9AFA0E321B112246E0536892A8C0BFD5 xb
9AFA0E321B122246E0536892A8C0BFD5

# 只返回是否包含JSON数据的行
select id,json_value(a.data, '$.name') as name 
from json_test a
where data is json;

ID                               NAME
-------------------------------- ----------
9AFA0E321B112246E0536892A8C0BFD5 xb

select id,json_value(a.data, '$.name') as name 
from json_test a
where data is not json;

ID                               NAME
-------------------------------- ----------
9AFA0E321B122246E0536892A8C0BFD5

JSON_EXISTS

当通过点记法去查询JSON时,不可能分辨出缺失的元素和已有的元素之间的区别,除了一个NULL值以外。以前面第一个例子为例,用户ABULL有一个为NULL的 “Special Instructions”元素,并且没有ADDRESS元素,当通过点记法去查询json_test表时都是返回NULL,而无法判断ADDRESS是不存在的,语句并不会报错。

col user for a10
col PONumber for a20
col Sins for a20
col address for a20
SELECT a.document."User",
       a.document.PONumber,
       a.document."Special Instructions" AS Sins,
       a.document.address AS address
FROM   json_test a
WHERE  a.document."Special Instructions" IS NULL
AND    a.document.address IS NULL;

User       PONUMBER             SINS                 ADDRESS
---------- -------------------- -------------------- --------------------
ABULL      1600

JSON_EXISTS可以分辨出空值和缺失对象的区别

# 查询"Special Instructions"元素存在,但是值为null

SELECT a.document."User",
       a.document.PONumber,
       a.document."Special Instructions" AS Sins
FROM   json_test a
WHERE  a.document."Special Instructions" IS NULL
  and json_exists(a.document, '$."Special Instructions"' FALSE on error);

User       PONUMBER             SINS
---------- -------------------- --------------------
ABULL      1600

# 查询address元素不存在的数据

SELECT a.document."User",
       a.document.PONumber,
       a.document."Special Instructions" AS Sins
FROM   json_test a
WHERE  a.document."Special Instructions" IS NULL
  and not json_exists(a.document, '$.address' FALSE on error)

User       PONUMBER             SINS
---------- -------------------- --------------------
ABULL      1600

默认错误控制为FALSE ON ERROR,另外包含TRUE ON ERROR和ERROR ON ERROR,后者顾名思义表示不会获取任何通过JSON_EXISTS函数生成的错误。

JSON_VALUE

JSON_VALUE函数返回基于JSON路径查询JSON文档的元素

col user for a30
col costcenter for a30

select json_value(a.document, '$."User"') as "user",json_value(a.document, '$.CostCenter') as costcenter from json_test a;

user                           COSTCENTER
------------------------------ ------------------------------
ABULL                          A50

json_value只会返回标量的值,并不会返回嵌套值或数组。默认的错误处理为NULL或ERROR,所以尝试返回非标量值时则返回NULL

col ShippingInstructions for a100
select json_value(a.document, '$.ShippingInstructions') as ShippingInstructions  from json_test a;

SHIPPINGINSTRUCTIONS
----------------------------------------------------------------------------------------------------


# 自定义错误控制
SQL> select json_value(a.document, '$.ShippingInstructions' ERROR ON ERROR) as ShippingInstructions  from json_test a;
select json_value(a.document, '$.ShippingInstructions' ERROR ON ERROR) as ShippingInstructions  from json_test a
                                                                                                     *
ERROR at line 1:
ORA-40456: JSON_VALUE evaluated to non-scalar value


SQL> select a.document.ShippingInstructions from json_test a;

SHIPPINGINSTRUCTIONS
----------------------------------------------------------------------------------------------------
{"name":"Alexis Bull","Address":{"street":"200 Sporting Green","city":"South San Francisco","state":
"CA","zipCode":99236,"country":"United States of America"},"Phone":[{"type":"Office","number":"909-5
55-7307"},{"type":"Mobile","number":"415-555-1234"}]}

JSON支持布尔值,而SQL通常不支持。JSON_VALUE函数转换布尔值为字符串true、false

insert into json_test 
values(sys_guid(), '{"active": false}');
insert into json_test 
values(sys_guid(), '{"active": true}');
commit;

col active for a20
select id,json_value(a.document,'$.active') as active,json_value(a.document,'$.active' returning number) as activenum from json_test a;

ID                               ACTIVE                ACTIVENUM
-------------------------------- -------------------- ----------
9B225A54605370F8E0536892A8C0616F false                         0
9B225A54605470F8E0536892A8C0616F true                          1
9AFA0E321B132246E0536892A8C0BFD5

正如前面所讲,返回值类型可以通过RETURNING来指定,如果没有指定返回或者只指定了VARCHAR2,则默认返回VARCHAR2(4000)

col REFERENCE for a20
col REQUESTOR for a50
select json_value(a.document,'$.Reference' returning varchar2) as Reference,
json_value(a.document,'$.Requestor' returning varchar2(50)) as Requestor from json_test a;

REFERENCE            REQUESTOR
-------------------- --------------------------------------------------


ABULL-20140421       Alexis Bull

JSON_QUERY

JSON_QUERY用来返回一个JSON片段代表一个或多个值

col phone for a200
select json_query(a.document.ShippingInstructions,'$.Phone' WITH WRAPPER) as phone from json_test a; 

PHONE
-------------------------------------------------------------------------------------------------


[[{"type":"Office","number":"909-555-7307"},{"type":"Mobile","number":"415-555-1234"}]]

这里的WITH WRAPPER表示用中括号将片段括起来。

JSON_TABLE

json_table函数合并了JSON_VALUE, JSON_EXISTS和JSON_QUERY的功能,相对来说json_table语法更加复杂些,但是也更加有效率些。

json_table能让json数据看起来更像关系型的数据,这对于基于JSON数据创建view来说更有帮助

create or replace view v_json as
select jt.phone,jt.refrence,jt.name,jt.city,jt.quantity
from json_test,json_table(document,'$'
    COLUMNS (phone varchar2(50 CHAR) PATH '$.PONumber',
            refrence varchar2(50 CHAR) PATH '$.Reference',
            name varchar2(50 CHAR) PATH  '$.ShippingInstructions.name',
            city varchar2(50 CHAR) PATH '$.ShippingInstructions.Address.city',
            quantity varchar2(50 CHAR) PATH '$.LineItems.Quantity'
    )) jt
where id='9AFA0E321B132246E0536892A8C0BFD5';

视图已创建。

col phone for a20
col refrence for a20
col name for a20
col city for a20
col quantity for a20

select * from v_json;

PHONE                REFRENCE             NAME                 CITY                 QUANTITY
-------------------- -------------------- -------------------- -------------------- --------------------
1600                 ABULL-20140421       Alexis Bull          South San Francisco

COLUMNS子句定义了每列数据的定义和展示。也可以在字段中显示JSON格式

col phone for a20
col refrence for a20
col name for a20
col city for a20
col items for a50

select jt.*
from json_test,json_table(document,'$'
    COLUMNS (phone varchar2(50 CHAR) PATH '$.PONumber',
            refrence varchar2(50 CHAR) PATH '$.Reference',
            name varchar2(50 CHAR) PATH  '$.ShippingInstructions.name',
            city varchar2(50 CHAR) PATH '$.ShippingInstructions.Address.city',
            items varchar2(2000 CHAR) format json with wrapper PATH '$.LineItems'
    )) jt
where id='9AFA0E321B132246E0536892A8C0BFD5';

PHONE                REFRENCE             NAME                 CITY                 ITEMS
-------------------- -------------------- -------------------- -------------------- --------------------------------------------------
1600                 ABULL-20140421       Alexis Bull          South San Francisco  [[{"ItemNumber":1,"Part":{"Description":"One Magic
                                                                                     Christmas","UnitPrice":19.95,"UPCCode":1313109289
                                                                                    9},"Quantity":9.0},{"ItemNumber":2,"Part":{"Descri
                                                                                    ption":"Lethal Weapon","UnitPrice":19.95,"UPCCode"
                                                                                    :85391628927},"Quantity":5.0}]]

insufficient privileges in procedure

ORA-01031: insufficient privileges是一个非常常见的错误,就是表示没有权限。但是在存储过程中oracle处理直接赋予的权限和间接通过roles来赋予的权限时会引起误解。通过以下例子来比较两者之间的区别。

下面这个例子以execute immediate来做示范,用于在存储过程中执行动态语句。

首先创建一个用户,赋予基本的角色权限。

create user test identified by test;

grant connect,resource to test;

此时用户test包含了连接数据库、创建表和其他connect、resource角色所赋予其的权限。

在普通会话中,test能正常连接数据库和创建表

SQL> conn test/test@pdb18c
Connected.

SQL> create table test(id number);

Table created.

创建一个简单的存储过程,通过EXECUTE IMMEDAITE来动态创建表

create or replace procedure proc_test
as 
begin
    EXECUTE IMMEDIATE 'create table test1(id number)';
end;
/

执行这个存储过程

SQL> exec proc_test;
BEGIN proc_test; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "TEST.PROC_TEST", line 4
ORA-06512: at line 1

现在得到了一个ORA-01031错误,但是之前确实是有创建表的权限。这里有一个原因:在存储过程中角色是被禁用的,因此任何角色赋予用户的权限都不会生效,对应的权限必须要直接赋予用户。

直接赋予test创建表的权限,然后重新执行存储过程成功

SQL> grant create table to test;

Grant succeeded.

SQL> conn test/test@pdb18c
Connected.

SQL> exec proc_test;

PL/SQL procedure successfully completed.

18c private temporary table

oracle中临时表很常见,它是用来只在一个事务或者会话时间段内存放临时数据的表。临时表中的数据对于会话来说是私有的,每个会话只能看到和处理它自己的临时数据。

临时表

在18c以前,我们所碰到的临时表一般都成为全局临时表,都是通过CREATE TEMPORARY TABLE的方式新建,所有的会话都能看到这个临时表的字典信息。而从18c开始引入了一种新的临时表,成为私有临时表,只有会话自己能看到它的私有临时表的字典信息。

对于这两种临时表的主要区别,可以概况如下:

特性 全局临时表 私有临时表
命名规则 与永久表一样 必须以ORA$PTT_开头
表定义可见 所有会话 只有创建临时表的会话
表定义的存放 磁盘 内存
种类 基于事务(ON COMMIT DELETE ROWS)
基于会话(ON COMMIT PRESERVE ROWS)
基于事务(ON COMMIT DELETE ROWS)
基于会话(ON COMMIT PRESERVE ROWS)

这里其实还有第三种临时表,基于游标周期的临时表,主要应用于oracle为了优化性能自动创建储存在内存中的临时表,比如with as转换过程中生成的。

私有临时表特性

由于私有临时表的字典和数据只能被创建它的会话所看到,基于这个特性,在下面的场景中就很适合建私有临时表

  • 当一个应用需要临时存放数据,只插入一次读取多次,最后事务或会话结束后就删除表
  • 当一个会话处于不确定状态并且必须要给不同的事务创建不同的临时表
  • 当临时表的创建不能重新开启新的事务或者不能提交已有事务
  • 当相同用户的不同会话必须使用同一个临时表的名称
  • 当只读数据库需要临时表

例如在某个报表应用中只只用一个用户,但是应用会使用这个用户建立多个连接去生成不同的报表。每个会话都使用私有临时表来计算不同的事务,每个会话创建的临时表名称都一样。当每个事务提交时,它的临时数据就不再需要了。

私有临时表有两种不同的类型,决定了数据和表定义什么时候会被删除。这里根据提交时的处理的参数不同,可以做如下区分

ON COMMIT设置 含义
DROP DEFINITION 创建基于事务的私有临时表,在事务结束的时候表定义和数据都会被删除
PRESERVE DEFINITION 创建基于会话的私有临时表,在创建这个表的会话结束时表定义和数据才会被删除

创建私有临时表

必须要以ora$ptt_作为开头,否则会报错

SQL> create private temporary table tmp_xb (id number,name varchar2(10));
create private temporary table tmp_xb (id number,name varchar2(10))
*
ERROR at line 1:
ORA-00903: invalid table name

创建基于事务的临时表,会在事务结束的时候删除表定义

create private temporary table ora$ptt_tmp_xb (id number,name varchar2(10))
on commit drop definition;

插入数据,不中断会话

SQL> insert into ora$ptt_tmp_xb values(1,'xb');

1 row created.

SQL> select * from ora$ptt_tmp_xb;

        ID NAME
---------- ----------
         1 xb

SQL> commit;

Commit complete.

SQL> select * from ora$ptt_tmp_xb;
select * from ora$ptt_tmp_xb
              *
ERROR at line 1:
ORA-00942: table or view does not exist

而换成基于会话的时候,就仍然可以查到。只有等到重连的时候表才会被删除

create private temporary table ora$ptt_tmp_xb (id number,name varchar2(10))
on commit preserve definition;

Table created.

SQL> insert into ora$ptt_tmp_xb values(1,'xb');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from ora$ptt_tmp_xb;

        ID NAME
---------- ----------
         1 xb


# 重连
SQL> conn xb/xb@pdb18c

Connected.

SQL> select * from ora$ptt_tmp_xb;
select * from ora$ptt_tmp_xb
              *
ERROR at line 1:
ORA-00942: table or view does not exist

在默认情况下,私有临时表是存放在创建表用户的默认临时表空间下。但是在创建表的时候也可以通过指定TABLESPACE关键字来替代。

同样私有临时表也支持CTAS的创建方式

create private temporary table ora$ptt_tmp_xb 
as 
select * from user_tables;

PL/SQL中的私有临时表

由于私有临时表的定义是无法持久存在的,当在存储过程或函数等中进行编译的时候,表是不存在的。所以如果要在存储过程中使用私有临时表的话,只能通过动态sql的方式。

create or replace function p_test(id in number) return varchar2
as
 v_sql varchar2(3000);
 v_return varchar2(50);
begin
  v_sql :='create private temporary table ora$ptt_tmp_xb (id number,name varchar2(10)) on commit drop definition';
  execute immediate v_sql;
  execute immediate q'{insert into ora$ptt_tmp_xb values (1,'xb')}';
  execute immediate 'select name into :v_return from ora$ptt_tmp_xb where id =:id' into v_return using id;
  return v_return;
 end;
 /

调用这个函数,可以正常返回

set serveroutput on
begin
  dbms_output.put_line('p_test(1) = ' || p_test(1));
end;
/

p_test(1) = xb

PL/SQL procedure successfully completed.

查询视图

因为私有临时表都是存放在内存当中的,所以在数据字典里是无法查到的。但是你可以通过USER_TABLES视图来查询当前会话中的所有私有临时表。

  • DBA_PRIVATE_TEMP_TABLES : 数据库中所有私有临时表
  • USER_PRIVATE_TEMP_TABLES: 当前会话中所有私有临时表

限制

私有临时表除了有全局临时表所有的限制外,还有一些其他的:

  • 必须以PRIVATE_TEMP_TABLE_PREFIX参数设置的前缀作为临时表的开头,默认是ORA$PTT_
  • 持久对象不能直接引用私有临时表
  • 私有临时表不能包含索引、物化视图等
  • 私有临时表不能有主键或者其他索引相关的约束
  • 字段不能有默认值
  • 私有临时表不能通过DBLINK访问

Zabbix API批量新增用户

给zabbix配置完LDAP验证,但是需要提前在zabbix中建好用户,这个就会比较麻烦一点,AD域中的用户好几千人,而涉及到IT的也有2 300人,加上配置邮箱、手机号等,很明显工作量巨大。

由于对python和LDAP都不太熟悉,研究了几天没想出如何从直接从ldap将用户信息同步到zabbix,但刚好在另外一套系统中发现了有将ad域账号落地到数据库的情况,于是退而求其次,从这个数据库取得数据,再参考zabbix api的官方文档,完成了想要的效果。

这里采用python3的版本,通过pyzabbix库进行调用。
参考pyzabbix文档的语法

from pyzabbix import ZabbixAPI

zapi = ZabbixAPI("http://zabbixserver.example.com")
zapi.login("zabbix user", "zabbix pass")
print("Connected to Zabbix API Version %s" % zapi.api_version())

for h in zapi.host.get(output="extend"):
    print(h['hostid'])

获取数据库中ad账号信息

这里存放的临时表,很简单通过pymysql获取

QurySql ="""select * from tmp_user"""
mycursor.execute(QurySql)
data = mycursor.fetchall()

总共取出4个字段,登录账号、别名、手机号、邮箱地址

获取已有账号信息

第一步就是要登陆到zabbix,获取系统里所有的user信息。
请求对象

{
    "jsonrpc": "2.0",
    "method": "user.get",
    "params": {
        "output": "extend"
    },
    "auth": "038e1d7b1735c6a5436ee9eae095879e",
    "id": 1
}

返回值

{
    "jsonrpc": "2.0",
    "result": [
        {
            "userid": "1",
            "alias": "Admin",
            "name": "Zabbix",
            "surname": "Administrator",
            "url": "",
            "autologin": "1",
            "autologout": "0s",
            "lang": "ru_RU",
            "refresh": "0s",
            "type": "3",
            "theme": "default",
            "attempt_failed": "0",
            "attempt_ip": "",
            "attempt_clock": "0",
            "rows_per_page": "50"
        }
    ],
    "id": 1
}

因为alias这里是唯一索引,所以调用user.get方法的时候通过alias进行判断,如果已存在则更新信息,如果不存在则新增用户。

user = zapi.user.get(filter={"alias": row[0]},output='extend')

这里可以根据传入的alias值获取到账户的所有属性,还有个比较重要的返回值叫userid,这个是要作为后面更新的时候的入参。

更新用户

根据前面得到的userid,这里只在属性里配置手机号和邮件地址,这样调用的时候就只会更新配置了的属性,其他保持不变

user_defaults = {'userid': user.userid, "user_medias": [
                {
                    "mediatypeid": "3",
                    "sendto": row[2]
                },
                {
                    "mediatypeid": "4",
                    "sendto": row[3]
                }
            ]}

新增用户

新增用户的内容就比较多一点,会设置短信和邮件的告警级别,和账号的一些其他属性默认密码等

user_defaults = {'alias': row[0], 'name': row[1], 'autologin': 1, 'autologout': 0, 'lang': 'zh_CN', 'type': 1,
                         'usrgrps': [{'usrgrpid': 88}], 'passwd': default_password, "user_medias": [
                {
                    "mediatypeid": "3",
                    "sendto": row[2],
                    "active": 0,
                    "severity": 48,
                    "period": "1-7,00:00-24:00"
                },
                {
                    "mediatypeid": "4",
                    "sendto": row[3],
                    "active": 0,
                    "severity": 8,
                    "period": "1-7,00:00-24:00"
                }
            ]}

完整脚本

#!/usr/bin/env python3
import pymysql
from pyzabbix import ZabbixAPI, ZabbixAPIException

mysql_conn = pymysql.connect("127.0.0.1","zabbix","zabbix","zabbix" )
mycursor = mysql_conn.cursor()


zabbix_server = 'http://zabbixserver.example.com'

zapi = ZabbixAPI(zabbix_server)

# login to the Zabbix API
zapi.login('Admin','zabbix')


QurySql ="""select * from tmp_user"""
mycursor.execute(QurySql)
data = mycursor.fetchall()

default_password = '123456'

for row in data:
    user = zapi.user.get(filter={"alias": row[0]},output='extend')

    if user:
        userid = user[0]["userid"]
        user_defaults = {'userid': userid, "user_medias": [
                {
                    "mediatypeid": "3",
                    "sendto": row[2]
                },
                {
                    "mediatypeid": "4",
                    "sendto": row[3]
                }
            ]}
        zapi.user.update(user_defaults)
        print("更新用户{0}".format(row[1]))

    else:
        user_defaults = {'alias': row[0], 'name': row[1], 'autologin': 1, 'autologout': 0, 'lang': 'zh_CN', 'type': 1,
                         'usrgrps': [{'usrgrpid': 88}], 'passwd': default_password, "user_medias": [
                {
                    "mediatypeid": "3",
                    "sendto": row[2],
                    "active": 0,
                    "severity": 48,
                    "period": "1-7,00:00-24:00"
                },
                {
                    "mediatypeid": "4",
                    "sendto": row[3],
                    "active": 0,
                    "severity": 8,
                    "period": "1-7,00:00-24:00"
                }
            ]}
        zapi.user.create(user_defaults)
        print("新增用户{0}".format(row[1]))

执行效果

[root@zabbix-pri ~]# ./test.py 
更新用户占凯
新增用户王锴

Reference

zabbix告警优化

目前新zabbix系统添加了1300多台监控设备,3W多个触发器,每天的告警也是满天飞,造成了有用的信息通常淹没在了告警风暴当中。由于目前都采用的短信告警,成本上也是一笔不小的开支,所以就很必要对告警进行优化。

告警依赖

有时候一台主机的可用性依赖于另一台主机。如果一台路由器宕机,则路由器后端的服务器将变得不可用。如果这两者都设置了触发器,你可能会收到关于两个主机宕机的通知,然而只有路由器是真正故障的。

这就是主机之间某些依赖关系可能有用的地方,设置依赖关系的通知可能会被抑制,而只发送根本问题的通知。

虽然Zabbix不支持主机之间的直接依赖关系,但是它们可以定义另外一种更加灵活的方式 – 触发器依赖关系。一个触发器可以有一个或多个依赖的触发器。

例如,主机位于路由器2后面,路由器2在路由器1后面。

Zabbix – 路由器1 – 路由器2 – 主机
如果路由器1宕机,显然主机和路由器2也不可达,然而我们不想收到主机、路由器1和路由器2都宕机的3条通知。

因此,在这种情况下我们定义了两个依赖关系:

‘主机宕机’ 触发器依赖于 ‘路由器2宕机’ 触发器
‘路由器2宕机’ 触发器依赖于 ‘路由器1宕机’ 触发器
在改变“主机宕机”触发器的状态之前,Zabbix将会检查相应触发器的依赖关系,如果找到,并且一个触发器处于“异常”状态,则触发器状态不会发生改变,因此不会执行动作,也不会发送通知。

Zabbix递归执行此检查,如果路由器1或路由器2是不可达的状态,那么主机触发器则不会更新。

所以根据zabbix提供的这个功能,对部分有依赖性的触发器之间做了关联,可以减少一部分告警。

告警升级

我给每个不同系统的群组分配了不同的权限,每个人收到的告警相对不会太多,但是作为领导或者权限更大的人来说,收到的告警数量数十倍增加,这个时候就需要对告警进行升级配置。

第一次出现告警的时候消息只发给一线运维人员,如果5分钟还没处理完才会发给级别或权限更大的人员。

告警去重合并

我实际工作中碰过很多次因为网络抖动造成的大面积agent无法访问的告警,或者因为某些原因服务无法访问但是下一次监控周期即恢复正常的情况,这样会导致大量的误报。

针对于此,上面的两种办法都不能有效的应对这种场景,于是想到通过数据库的层面进行一些处理,主要思路是将生成的alert写库以后,再定期写入到一张临时表当中,对于告警发送周期范围内已经恢复正常的告警直接删除。对于agent大面积无法访问的误报进行告警合并。

对于动作的内容有点要求,这里用#符号作为分隔,方便数据库层面去处理字符串,只用配置subject即可

#Operations:
{EVENT.NAME}#{HOST.NAME}#{HOST.IP}#{ITEM.NAME}:{ITEM.VALUE}#{EVENT.DATE} {EVENT.TIME}

#Recovery operations:
{EVENT.NAME}#{HOST.NAME}#{HOST.IP}#{ITEM.NAME}:{ITEM.VALUE}#{EVENT.RECOVERY.DATE} {EVENT.RECOVERY.TIME}

新建两张表

CREATE TABLE `his_alerts` (
  `eventid` bigint(20) DEFAULT NULL,
  `clock` int(20) DEFAULT NULL,
  `sendto` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `subject` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `p_eventid` bigint(20) DEFAULT NULL,
  `status` varchar(2) COLLATE utf8_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

create index idx_his_alerts_eventid on his_alerts(eventid);

CREATE TABLE `tmp_alerts` (
  `eventid` bigint(20) DEFAULT NULL,
  `clock` int(20) DEFAULT NULL,
  `sendto` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `subject` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `p_eventid` bigint(20) DEFAULT NULL,
  `status` varchar(2) COLLATE utf8_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE `all_alerts` (
  `sendto` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `subject` varchar(255) COLLATE utf8_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

在mysql库中新建存储过程

drop PROCEDURE IF EXISTS resize_alerts;
delimiter $$
CREATE PROCEDURE resize_alerts ( ) 
BEGIN
    DECLARE
        v_eventid INT ( 20 );
    DECLARE
        v_cnt INT ( 2 );
    DECLARE
        v_status INT ( 2 );
    DECLARE
        v_sendto VARCHAR ( 255 );
    declare v_name varchar(128);
    DECLARE
        loop_done INT DEFAULT 0;

    start transaction;
    # 归档告警数据
    INSERT INTO his_alerts SELECT
    * 
    FROM
        tmp_alerts;

    # 清空临时表
    TRUNCATE TABLE tmp_alerts;
    truncate table all_alerts;

    # 初始化要处理的告警数据
    INSERT INTO tmp_alerts 
    SELECT
        a.eventid,
        a.clock,
        a.sendto,
        a.`subject`,
        a.p_eventid,
    CASE
            WHEN a.p_eventid IS NULL THEN
            1 ELSE 0 
    END STATUS 
    FROM
        alerts a,
        `events` b 
    WHERE a.clock > UNIX_TIMESTAMP( date_add( now( ), INTERVAL - 2 minute ) ) 
      AND a.eventid not in ( SELECT eventid FROM his_alerts )
        AND STATUS = 1 
        AND mediatypeid = 3 
        AND a.eventid = b.eventid 
        order by 1;


# 删除一分钟内已经恢复的告警
    delete from tmp_alerts where eventid in(select eventid from (       
SELECT a.eventid FROM tmp_alerts a WHERE eventid IN ( SELECT p_eventid FROM tmp_alerts )
union all
SELECT a.eventid FROM tmp_alerts a WHERE p_eventid IN ( SELECT eventid FROM tmp_alerts )) aa);

# 合并多个agent无法访问的告警
    INSERT INTO all_alerts (sendto, subject)
    SELECT
    sendto,
    concat( CASE WHEN STATUS = '0' THEN '【恢复OK】有大量zabbix客户端恢复访问! ' ELSE '【故障PROBLEM】有大量zabbix客户端无法访问,请检查网络! ' END, '主机数量:', cnt ) 
    FROM
        (
        SELECT
            sendto,
            STATUS,
            count( 1 ) cnt 
        FROM
            tmp_alerts 
        WHERE
            `subject` like '%Zabbix agent 无法访问%' 
        GROUP BY
            sendto,
        STATUS 
        HAVING
            count( 1 ) > 2 
        ) aa;

DELETE from tmp_alerts where eventid in(select eventid from (   
    SELECT EVENTID 
    FROM
        tmp_alerts a 
    WHERE
        EXISTS (
        SELECT
            1 
        FROM
            ( SELECT sendto, STATUS FROM tmp_alerts WHERE `subject` like '%Zabbix agent 无法访问%'  GROUP BY sendto, STATUS HAVING count( 1 ) > 2 ) b 
        WHERE
            a.sendto = b.sendto 
            AND a.STATUS = b.STATUS 
        ) 
        AND a.`subject` like '%Zabbix agent 无法访问%' ) aa);

    # 合并相同主机多个告警

    insert into all_alerts (sendto, subject, eventtime)
    select sendto,subject,substr(`subject`,instr(subject,'告警时间:')+5) eventtime from (
    select sendto,
                    concat(case  when status='0' then '【恢复OK】: ' else '【故障PROBLEM】: ' end ,triggername,
                    ' 主机:',hostname,
                    ' 问题详情:',itemvalue,
                    ' 告警时间:',eventtime) subject
    from (select sendto,group_concat(triggername order by eventtime) triggername,hostname,min(itemvalue) itemvalue,min(eventtime) eventtime,status from (
                SELECT 
                        a.sendto,
                        substring_index(subject,'#',1) triggername,
                        substring_index(substring_index(subject,'#',2),'#',-1) hostname,
                        substring_index(substring_index(subject,'#',4),'#',-1) itemvalue,
                        substring_index(subject,'#',-1) eventtime,
                        a.STATUS 
                    FROM
                        tmp_alerts a) dd
        group by sendto,hostname,status
      having count(1) >1
        order by eventtime) aa ) bb;

 # 删除已经合并的相同主机多个告警

    delete from tmp_alerts where eventid in(
select eventid from (
select eventid from tmp_alerts a where EXISTS(select 1 from (
select group_concat(eventid) eventid,sendto,group_concat(triggername) triggername,hostname,min(itemvalue) itemvalue,min(eventtime) eventtime,status from (
                SELECT 
                        a.sendto,
                        eventid,
                        substring_index(subject,'#',1) triggername,
                        substring_index(substring_index(subject,'#',2),'#',-1) hostname,
                        substring_index(substring_index(subject,'#',4),'#',-1) itemvalue,
                        substring_index(subject,'#',-1) eventtime,
                        a.STATUS 
                    FROM
                        tmp_alerts a) dd
        group by sendto,hostname,status
      having count(1) >1
        order by eventtime) aa where a.sendto=aa.sendto and a.status=aa.status and instr(aa.eventid,a.eventid) > 0)) mm);


# 合并剩余的多个主机相同告警

insert into all_alerts (sendto, subject, eventtime)
select sendto,subject,substr(`subject`,instr(subject,'告警时间:')+5) eventtime from (
    select sendto,
                    concat(case  when status='0' then '【恢复OK】: ' else '【故障PROBLEM】: ' end ,triggername,
                    ' 主机:',hostname,case when cnt='1' then '' else concat(' 等',cnt,'台主机') end,
                    ' 问题详情:',itemvalue,
                    ' 告警时间:',eventtime) subject
    from (select sendto,triggername,max(hostname) hostname,min(itemvalue) itemvalue,min(eventtime) eventtime,status,count(1) cnt from (
                SELECT 
                        a.sendto,
                        substring_index(subject,'#',1) triggername,
                        substring_index(substring_index(subject,'#',2),'#',-1) hostname,
                        substring_index(substring_index(subject,'#',4),'#',-1) itemvalue,
                        substring_index(subject,'#',-1) eventtime,
                        a.STATUS 
                    FROM
                        tmp_alerts a) dd
        group by sendto,triggername,status 
        order by eventtime) aa ) bb;


    # 归档已发送短信数据
    insert into all_alerts_history
    select * from all_alerts;

    commit;

END $$

主机上通过python脚本按每分钟定期调用resize_alerts过程,遍历数据然后写入到短信库内

#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Date    : 2019-12-10
# @Author  : Xiong Bin (i@xbdba.com)
# @Link    : http://www.xbdba.com
# @Name    : sendsms.py

import pymysql
import pymssql

mysql_conn = pymysql.connect("127.0.0.1","zabbix","zabbix","zabbix" )
mycursor = mysql_conn.cursor()

ms_conn = pymssql.connect(IP, USERNAME, PASSWORD, DATABASENAME)
mscursor = ms_conn.cursor()

mycursor.callproc('resize_alerts')
QurySql ="""select sendto,subject from all_alerts order by sendto,eventtime"""
mycursor.execute(QurySql)
data = mycursor.fetchall()

sql = "insert into 短信TABLE values (%s, %s, 'Zabbix', 0)"
for row in data:
        mscursor.execute(sql, (row[0], unicode(row[1], 'utf-8')))
ms_conn.commit()

定时任务

*/1  *  *  *  *  /usr/lib/zabbix/alertscripts/sendsms.py 2>&1

expdp导出表ORA-01555错误

有同事碰到一个问题,在导出一个很小的库时有三张表都提示报错,而其他对象都导出正常。

ORA-02354: error in exporting/importing data
ORA-01555: snapshot too old: rollback segment number with name " " too small
ORA-22924: snapshot too old

这里的报错有点奇怪,因为库才10个g,undo表空间配置了32g,导出时基本无业务运行,按常理不会出现回滚段不足的情况。检查这三张表都包含有lob字段,怀疑是跟这有关,后来结果确实是lob字段数据有异常,处理完后即可顺利导出,现在重现这个例子。

新建测试表

drop table t_lob purge;
drop tablespace test_lob including contents and datafiles;
create tablespace test_lob datafile size 4m;

create table t_lob(a number,b clob)
    lob(b) store as (
    tablespace test_lob
    chunk 8k
    PCTVERSION 2
    disable storage in row);


begin
    for i in 1..20 loop
        insert into t_lob values(i,'this is '||i);
        commit;
    end loop;
end;
/

ALTER TABLE t_lob MODIFY LOB (b) ( PCTVERSION 1 );

begin
    for i in 1..10 loop
        update t_lob set b = i||b;
        commit;
    end loop;
end ;
/

这时候查询sql会出现案例中类似的报错

SQL> select * from t_lob as of timestamp timestamp'2019-09-19 16:31:00';
ERROR:
ORA-01555: snapshot too old: rollback segment number  with name "" too small
ORA-22924: snapshot too old

这里就提到一个参数PCTVERSION,它表示LOB存储空间需要为旧版本的LOB页预留的空间,主要是用来维护更新前的一致性读,默认值为10%。如果这个lob字段会有大量的高并发更新,则需要设置一个较高的值。

PCTVERSION可以在创建字段的时候指定,也可以通过ALTER TABLE修改,如果设置小了,当一个会话想要去访问lob字段的旧版本但是这个旧版本又被覆盖时,那么就会出现ORA-01555的错误。PCTVERSION可以避免旧页被覆盖而去强制扩展段空间。

碰到lob字段的值显示<Value Error>时,可以通过下面的脚本来定位那些行和页出现了异常

declare
  error_1578 exception;
  error_1555 exception;
  error_22922 exception;
  pragma exception_init(error_1578,-1578);
  pragma exception_init(error_1555,-1555);
  pragma exception_init(error_22922,-22922);
  n number;
begin
  for cursor_lob in (select rowid r, <LOB_COLUMN> from <TABLE_NAME>) loop
  begin
    n:=dbms_lob.instr(cursor_lob.<LOB_COLUMN>,hextoraw('889911'));
  exception
    when error_1578 then
      insert into corrupt_lobs values (cursor_lob.r, 1578);
      commit;
    when error_1555 then
      insert into corrupt_lobs values (cursor_lob.r, 1555);
      commit;
    when error_22922 then
      insert into corrupt_lobs values (cursor_lob.r, 22922);
      commit;
    end;
  end loop;
end;
/

然后通过上面查到的rowid,对这些lob字段数据进行清空,最后成功导出

SQL> update <TABLE_NAME> set <LOB_COLUMN> = empty_blob()
     where rowid in (select corrupt_rowid from corrupt_lobs);

( for BLOB and BFILE columns use EMPTY_BLOB; for CLOB and NCLOB columns use EMPTY_CLOB )

-- Or export the table without the corrupted row, like:

% expdp system/<PASSWORD> DIRECTORY=my_dir DUMPFILE=<dump_name>.dmp LOGFILE=<logfile_name>.log TABLES=<SCHEMA_NAME>.<TABLE_NAME> QUERY=\"WHERE rowid NOT IN \(\'<corrupt_rowid>\'\)\"