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 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数据：
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格式的数据提供了很好的存储于展示。
