Vertica的这些事<三>—— 谈谈vertica的flex table

来源：互联网发布：access数据库教程视频编辑：程序博客网时间：2024/06/05 21:57

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

首先创建一个json文件：

{"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：

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

然后把数据copy进去：

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

查询结果：

dbadmin=> select * from start_json();ERROR 4256:  Only relations and subqueries are allowed in the FROM clausedbadmin=>  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数据：

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)

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

0 0