Skip to content

Tag: amazon-web-services

Count ROW type item Athena / Presto

I have an Athena query like this and the result is I would like to count the number of records per day per devices to have a result like this EDIT My dataset is actually like this Here the expected results would be : Answer You can cast your json to map and count number of keys: Output: device_id date

How to parsing Json in AWS Athena?

I want to parse the JSON column in Athena but I have a problem in one column. The column includes an escape character. I don’t know if this problem. I want to reach to Message. You can see below sample data and Message under Return. Thanks This is the JSON. Answer If you take only the object which contains return

Proper Case in Athena SQL

I’m new to Athena and trying to convert a field ‘Country’ to Proper Case. For example: united states -> United States Is there a way to do this in Athena? Answer Turning a comment into an answer: Athena engine version 2 gives in its RegEx section as example for regexp_replace(string, pattern, function)

Redshift : Coalesce Can’t Work As Expected

We’re complementing null value to all zero value like ’00’ on Redshift. Sometimes, I found coalesce function can’t work as we expected. If we use case and len, it can work fine as follows; So far, I have no idea why coalesce can’t work. Any advice would be appreciated. Thank you. Answer There is a difference between ” and NULL

Redshift Postgres 8

I’m trying to write a query to solve a logical problem using Redshift Postgres 8. Input column is a bunch of Order IDs and Step Group IDs and desired output is basically a sequence of the IDs as you can see in the screenshot. If you could help me answer this question, that would be great, thanks! This is a