on 03-12-2018 8:45 PM
Maybe u come across a subject like this as well.
Transfer data from a big JSON to a relational table in HANA and second consume the stored information with SQL..
1. Create a table in HANA to store the JSON information.
2. Extract the data with SQL and to a result table.
Here is my solution:
Given a JSON file below I want to access information by using some SQL in HANA.
{
"metadata": {
"language": "de-DE",
"transaction_id": "1511335024960:1650367502",
"version": "1",
"latitude": 37.54,
"longitude": 5.58,
"units": "m",
"expire_time_gmt": 1511335872,
"status_code": 200
},
"forecasts": [
{
"class": "fod_long_range_daily",
"expire_time_gmt": 1511335872,
"fcst_valid": 1511330400,
"night": {
"fcst_valid": 1511373600,
"fcst_valid_local": "2017-11-22T19:00:00+0100",
"day_ind" : "N"
},
"day": {
"fcst_valid": 1511330400,
"fcst_valid_local": "2017-11-22T07:00:00+0100",
"day_ind" : "D"
}
}
]
}
');
I created a table "CREATE TABLE <SCHEMA>.FoD (A INT, B CLOB);" in HANA and dumped the JSON to it.
The nested information can be accessed by using this SQL:
-- select from an ordinality column with nested columns
SELECT *
FROM JSON_TABLE (
<SCHEMA>.FoD.B, '$'
COLUMNS (
RN FOR ORDINALITY,
language_code NVARCHAR(50) PATH '$.metadata.language',
transaction_id NVARCHAR(50) PATH '$.metadata.transaction_id',
version NVARCHAR(50) PATH '$.metadata.version',
latitude NVARCHAR(50) PATH '$.metadata.latitude',
longitude NVARCHAR(50) PATH '$.metadata.longitude',
units NVARCHAR(50) PATH '$.metadata.units',
expire_time_gmt NVARCHAR(50) PATH '$.metadata.expire_time_gmt',
status_code NVARCHAR(50) PATH '$.metadata.status_code',
-- Extract the information from section "forecasts"
NESTED PATH '$.forecasts[*]'
COLUMNS (
class NVARCHAR(50) PATH '$.class',
-- Extract the information from section "night" as part of section "forecasts"
NESTED PATH '$.night'
COLUMNS (
fcst_valid_local_night NVARCHAR(24) PATH '$.fcst_valid_local',
night_ind NVARCHAR(1) PATH '$.day_ind' )
,
-- Extract the information from section "day" as part of section "forecasts"
NESTED PATH '$.day'
COLUMNS (
fcst_valid_local_day NVARCHAR(24) PATH '$.fcst_valid_local',
day_ind NVARCHAR(1) PATH '$.day_ind' )
)
)
) AS JT;
To avoid multiple columns for "fcst_valid_local" with the extension "_day" and "_night" from above I used to combine extractions from the "day" and "night" section of "forecast" by using UNION.
SELECT
*
FROM (
SELECT
*
FROM JSON_TABLE ( <SCHEMA>.FoD.B,
'$' COLUMNS ( RN FOR ORDINALITY,
language_code NVARCHAR(50) PATH '$.metadata.language',
transaction_id NVARCHAR(50) PATH '$.metadata.transaction_id',
version NVARCHAR(50) PATH '$.metadata.version',
latitude NVARCHAR(50) PATH '$.metadata.latitude',
longitude NVARCHAR(50) PATH '$.metadata.longitude',
units NVARCHAR(50) PATH '$.metadata.units',
expire_time_gmt NVARCHAR(50) PATH '$.metadata.expire_time_gmt',
status_code NVARCHAR(50) PATH '$.metadata.status_code',
-- Extract the information from section "forecasts"
NESTED PATH '$.forecasts[*]' COLUMNS ( class NVARCHAR(50) PATH '$.class',
-- Extract the information from section "night" as part of section "forecasts"
NESTED PATH '$.night' COLUMNS ( fcst_valid_local NVARCHAR(24) PATH '$.fcst_valid_local',
ind NVARCHAR(1) PATH '$.day_ind' ) ) ) ) AS JT_night
UNION
SELECT
*
FROM JSON_TABLE ( <SCHEMA>.FoD.B,
'$' COLUMNS ( RN FOR ORDINALITY,
language_code NVARCHAR(50) PATH '$.metadata.language',
transaction_id NVARCHAR(50) PATH '$.metadata.transaction_id',
version NVARCHAR(50) PATH '$.metadata.version',
latitude NVARCHAR(50) PATH '$.metadata.latitude',
longitude NVARCHAR(50) PATH '$.metadata.longitude',
units NVARCHAR(50) PATH '$.metadata.units',
expire_time_gmt NVARCHAR(50) PATH '$.metadata.expire_time_gmt',
status_code NVARCHAR(50) PATH '$.metadata.status_code',
-- Extract the information from section "forecasts"
NESTED PATH '$.forecasts[*]' COLUMNS ( class NVARCHAR(50) PATH '$.class',
-- Extract the information from section "day" as part of section "forecasts"
NESTED PATH '$.day' COLUMNS ( fcst_valid_local NVARCHAR(24) PATH '$.fcst_valid_local',
ind NVARCHAR(1) PATH '$.day_ind' ) ) ) ) AS JT_day )
Any idea how this can be achieved in an more elegant way?
Regards
Dio.
User | Count |
---|---|
83 | |
24 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.