0
0
During the last post we have seen, snowflake hits the Public API via External functions. API returns the response in the JSON format. Therefore, As part of this discussion we will store this response in Snowflake table. Later on will transform/Parse API JSON Response to Relational format i.e. Rows and Columns.
Hence, To parse the JSON ,we have used LATERAL FLATTEN function. Say we have created the following table in snowflake holding the API response.
create or replace table raw_source as Select SRC_TYPE as SRC_TYPE ,src_url as src_url , demo_db.public.EXT_UDF_fetchHTTPData( src_url ) PAYLOAD_RESPONSE from API_INFO;
After that, we have to parse the data in PAYLOAD_RESPONSE column.
Firstly:
SRC_TYPE = crypto;
However, Below query has been used to PARSE the JSON data for crypto source.
select
empd.value:base_asset::string,
empd.value:change_24h::string,
empd.value:created_at::string,
empd.value:exchange_id::string,
empd.value:price::string,
empd.value:price_unconverted::string,
empd.value:status::string,
empd.value:symbol::string,
empd.value:volume_24h::string,
payload_response:response.next::string,
payload_response:url::string
from raw_source emp,
lateral flatten(input=>emp.payload_response:response.markets) empd
where src_type = 'crypto'
Secondly:
SRC_TYPE = BitCoinPrice;
Moreover, Below query has been used to PARSE the JSON data for BitCoinPrice source.
select
empd.value:code::string as Currency,
empd.value:description::string as Description,
empd.value:rate::string as Rate,
empd.value:rate_float::string as Floating_Rate,
empd.value:symbol::string as Symbol,
payload_response:response.chartName::string as Chart,
payload_response:response.time.updated::string as Updated_Time,
payload_response:response.time.updatedISO::string as Update_ISO,
payload_response:response.time.updateduk::string as Updated_Duk,
payload_response:url::string
from raw_source emp,
lateral flatten(input=>emp.payload_response:response.bpi) empd
where src_type = 'BitCoinPrice'
Finally, we have completed an end-to-end use case where we created External function inside Snowflake. Then hit the Public API and retrieve the response using AWS lambda. Later on we stored JSON inside the Snowflake table. At last, Parse the response to Rows and Column using Lateral Flatten.
Moreover, Click here to know how to create External functions.
