GOAL I would like to add a Redshift SUPER column to and existing redshift table. I need this to store JSON data there CODE This is how Normally I would add a new column. 1. Tried Error running query: ALTER TABLE ADD COLUMN defined as NOT NULL must have a non-null default expression Reviewed Solutions Alter column data type in
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
Query validation for Amazon Athena using AWS SDK
I am using the AWS SDK to execute Amazon Athena queries using Java and what I would like to do is to have some way of ensuring only SELECT queries are executed. This is to make sure queries like DROP or INSERT are not executed. So is there any way I can perform this check using the AWS SDK without
How to join two tables while only selecting the highest day of each month from one table
I have two tables. One with metadata, one with billing data. I need to join those effiently in order to assign metadata to costs. Table 1 (metadata) looks like this: Table 2 (billing data) looks like this: For each combination of year, month, id in Table 2, there is an corresponding ID in Table 1. For each year, month, id
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
Redshift sql identify records based on timestamp which came before a certain record
I’ve a redshift table which has the following structure I’m trying to find 3 sets, all titleIds [preferably entire row, not just the list of titleIds] which have country IN after US, the other way around which have US after IN and all titles which just has an IN entry and nothing else. Now its possible that we have for
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
Where column “c” is not null for each group in column “a” – give group “a” a value of 1
Column “C” is a flag indicator. I want to run a query where I can sum column “c” where it is not null. I would like 1 value for each group of column A. For instance group “Test”, would have a total of 1. Group chance has a total of 1. Group “Play” has a total of 0. I would
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