Vertica的这些事(五)——-谈谈vertica的flex-table

Json格式对于现在所有的软件开发者都不陌生,很多数据格式都用他来存储,我们来看一下vertica是怎么处理json数据的。这就是vertica的flex table!

首先创建一个json文件:

1
2
3
4
5
{"name": "Everest", "type":"mountain", "height":29029, "hike_safety": 34.1}
{"name": "Mt St Helens", "type":"volcano", "height":29029, "hike_safety": 15.4}
{"name": "Denali", "type":"mountain", "height":17000, "hike_safety": 12.2}
{"name": "Kilimanjaro", "type":"mountain", "height":14000 }
{"name": "Mt Washington", "type":"mountain", "hike_safety": 50.6}

然后我们创建一个flex table:

1
2
dbadmin=> CREATE FLEX TABLE start_json();
CREATE TABLE

然后把数据copy进去:

1
2
3
4
5
dbadmin=> COPY start_json FROM '/home/dbadmin/qcfData/*json*' PARSER fjsonparser();
Rows Loaded
-------------
5
(1 row)

查询结果:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
dbadmin=> select * from start_json();
ERROR 4256: Only relations and subqueries are allowed in the FROM clause
dbadmin=> SELECT maptostring(__raw__) FROM start_json;
maptostring
----------------------------------------------------------------------------------------------------------
{
"height" : "29029",
"hike_safety" : "34.1",
"name" : "Everest",
"type" : "mountain"
}

{
"height" : "29029",
"hike_safety" : "15.4",
"name" : "Mt St Helens",
"type" : "volcano"
}

{
"height" : "17000",
"hike_safety" : "12.2",
"name" : "Denali",
"type" : "mountain"
}

{
"height" : "14000",
"name" : "Kilimanjaro",
"type" : "mountain"
}

{
"hike_safety" : "50.6",
"name" : "Mt Washington",
"type" : "mountain"
}

(5 rows)

发现很好的解析了json文件,并且格式化了文件。

查询json数据:

1
2
3
4
5
6
7
8
9
dbadmin=>  SELECT start_json.type,start_json.name FROM start_json;
type | name
----------+---------------
mountain | Everest
volcano | Mt St Helens
mountain | Denali
mountain | Kilimanjaro
mountain | Mt Washington
(5 rows)

此时如果使用 * 查询 会出现乱码:

1
SELECT * FROM start_json;

需要使用函数 compute_flextable_keys

select compute_flextable_keys(‘start_json’);

然后查询就可以有结果

综上,flex table 对json格式的数据提供了很好的存储于展示。

-------------���Ľ�����л�����Ķ�-------------
qinchaofeng wechat
坚持原创技术分享,您的支持将鼓励我继续创作!