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}

  

 

  
1
  
2
  
3
  
4
  
5
 

然后我们创建一个flex table：

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

  

 

  
1
  
2
 

然后把数据copy进去：

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

  

 

  
1
  
2
  
3
  
4
  
5
 

查询结果：

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)

  

 

  
 
 

  
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
 

发现很好的解析了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)

  

 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
  
8
  
9
 

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

SELECT * FROM start_json;

  

 

  
1
 

需要使用函数 compute_flextable_keys

select compute_flextable_keys(‘start_json’);

然后查询就可以有结果

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

文章来源: dataclub.blog.csdn.net，作者：数据社，版权归原作者所有，如需转载，请联系作者。

原文链接：dataclub.blog.csdn.net/article/details/51892935