How to query json data ?
i’ve insert GA(google analytic) data on aws athena and then i need to query data to do a ETL and visualization.
i found error in quicksight can’t read array data types.
i have to use data in column event_param.
GA data source
Error in Quicksight
Thanks for your help.
Hi @Siriphon - Can you use json_extract to read JSON data. Please see the link - Extracting data from JSON - Amazon Athena for reference.
Regards - Sanjeeb
Hi @Siriphon ,
The blog provides an example and details on querying json data , unnesting it. You can use the same techniques for your requirements : Analyze and visualize nested JSON data with Amazon Athena and Amazon QuickSight | AWS Big Data Blog
1/You could create a view in Athena and then directly connect that view in QuickSight.
2/You could also write custom sql in QuickSight connecting to the Athena table
i would like to ask more question.
- how to query from nested data i want page_location data?
- how to split text and get value from sc_camp ?
this is my query
eventparams.value.string_value, * FROM “scbpt_dl_persistent”.“ga_dib_history”
CROSS JOIN UNNEST(event_params) as t(eventparams) limit 1000
please see detail in photo below.
Hi @Siriphon ,
Please check athena+sql related queries in repost :
To extract values from your string, you can look into regular expressions : Functions in Amazon Athena - Amazon Athena
Would you please recommend Amz athena online course , i would like to learn more ?