Trafodion 处理JSON半结构化数据

Trafodion不仅可以处理结构化数据，还可以处理半结构化数据及非结构化数据，对于半结构化数据和非结构化数据，主要用到TMUDF功能。
本文通过一个实际的案例详解如何通过自定义一个TMUDF来解析JSON格式文本从而实现对半结构化数据的处理。

1 编写JAVA类实现解析JSON文本，可参考https://github.com/esgyn/code-examples/blob/master/src/main/java/org/trafodion/examples/udrs/udfs/table_valued/json_columnizer/json_columnizer.java
具体用法上述代码已提到如下所述，

/* ===================================================================================     *     * This is a Table Mapping UDF for Trafodion     *     * Input table contains a single VARCHAR/STRING column that is a complete JSON record     * UDF outputs values corresponding to JSON tags passed in the calling statement     * A variable number of tags can be passed in     *     * Tag names must be fully qualified, eg, 'Master.Employee.Middle Initial' for JSON     *  {"MASTER": {"EMPLOYEE": { "Middle Initial": "Q" } } }      *     *     * To invoke this UDF:     *      *     select * from udf(json_column(table( select * from <your json table> ),     *                                         '<tag>', '<tag>', ...) );     *                                              * =================================================================================== */

2 编译并生成JAR包json_columnizer.jar(注意：此处需要把javax.json-1.0.4.jar一起打包进去)

[root@cent-2 udr]# ll json_columnizer.jar-rw-rw-r--. 1 centos centos 380574 Feb  7 11:47 json_columnizer.jar[root@cent-2 udr]# pwd/home/trafodion/esgynDB-2.2.0/udr

3 创建Library及Table_Mapping Function

create library trafodion.seabase.json_columnizer file '/home/trafodion/esgynDB-2.2.0/udr/json_columnizer.jar';create table_mapping function trafodion.seabase.unjson_obj()EXTERNAL NAME 'json_columnizer'LIBRARY trafodion.seabase.json_columnizerLANGUAGE JAVANO SQL;

4 准备样例JSON文本

{  "firstName": "John",  "lastName": "Smith",  "isAlive": true,  "age": 25,  "address": {    "streetAddress": "21 2nd Street",    "city": "New York",    "state": "NY",    "postalCode": "10021-3100"  },  "phoneNumbers": [    {      "type": "home",      "number": "212 555-1234"    },    {      "type": "office",      "number": "646 555-4567"    },    {      "type": "mobile",      "number": "123 456-7890"    }  ],  "children": [],  "spouse": null}

5 创建测试表并插入JSON字符串

SQL>create table json_test ( jsondata varchar(2000));SQL>insert into json_test values('{"firstName":"John","lastName":"Smith","isAlive":true,"age":25,"address":{"streetAddress":"212ndStreet","city":"NewYork","state":"NY","postalCode":"10021-3100"},"phoneNumbers":[{"type":"home","number":"212555-1234"},{"type":"office","number":"646555-4567"},{"type":"mobile","number":"123456-7890"}],"children":[],"spouse":null}');

6 查询解析后的JSON字符串

SQL>select * from udf(trafodion.seabase.unjson_obj(table(select jsondata from trafodion.seabase.json_test),'firstName','lastName'));FIRSTNAME                                                                                            LASTNAME                                                                                           ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------John                                                                                                 Smith                                                                                              --- 1 row(s) selected.