Note: This is a revised version of another question – i realized i didnt have complete requirements (sorry about that!) I have a Redshift table called metadata with the following columns: id key value 1001 code1 my value 1001 code2 another 1001 code3 yet another 1002 code1 new one 1002 code2 here 1002 code3 last 1003 code1 hello 1003 code2
Tag: amazon-web-services
How best can I to pivot my redshift table?
I have a Redshift table called metadata with the following columns: id key value 1001 code1 my value 1001 code2 another 1001 code3 yet another 1002 code1 new one 1002 code2 here 1002 code3 last 1003 code1 hello 1003 code2 goodbye 1004 code2 now 1004 code3 then I’d like to have a query that return it as: id code1 code2
AWS Athena custom data format?
I’d like to query my app logs on S3 with AWS Athena but I’m having trouble creating the table/specifying the data format. This is how the log lines look: 2020-12-09T18:08:48.789Z {“reqid”:&…
Redshift regex for before and after certain char
Data :- 1,2,3=20,4,5 select regexp_substr(data,’,3=[^,]*’) output = ,3=20 Desired output = 20 How do I get rid of,3=? I can add split_part(regexp_substr(Data,’,713=[^,]*’),’=’,2) but using the same expression would make sense. Thanks Answer You can extract a pattern using a subexpression:
Calculate Median for each group in AWS Athena table
Below is the schema for the athena table I wish to calculate median for ‘parameter_value’ group by standard_lab_parameter_name & units. For this I followed link : https://docs.aws.amazon.com/redshift/latest/dg/r_MEDIAN.html But on running the query It throws error Any help? Or if some alternative query would be great Answer Athena is based on Presto 0.172 – you can see all supported functions in
How to sum few colums values for each type and calculate percentage?
I have below query which gives me data with three columns – type, amount and total for current week using week_number column. Below is the output I get back as of now with above query: Problem Statement Now I need to calculate the percentage for each type by using below formula: For each type (sum(total) – sum(amount)) / sum(amount). So
Hierarchical SQL query to Athena
I’m trying to create a query in Athena that solves this problem: I have records that look like this which create a hierarchical structure, like a tree but with indeterminate children. I have more than one root, that is, more than one element that is not children of anyone. I want to get the complete structure for one of them.
SQL Server – Add a linked server to AWS RDS instance
From this AWS link it explains how to add a linked server: https://aws.amazon.com/blogs/database/implement-linked-servers-with-amazon-rds-for-microsoft-sql-server/ Specifically these commands The main problem is it seems to be a mix of ‘, `, and ” I get syntax errors, and I assume that’s why. Plus, I don’t know why it has the N before a bunch of them. It looks to me like they
SQL query to get Column A with all Column B
I have a table that has 2 columns customer and city . there are 4 unique cities (w,x,y,z) I want customers that is associated with all cities. So for the table +———-+——+ | Customer | City | +———-+——+ | A | W | | A | X | | A | Y | | A | Z | | B
How to run a SQL query in Cloud Formation template to enable Delayed_Durability in AWS RDS
I have a Cloud Formation template to create a SQL DB in the RDS and want to enable Delayed_Durability feature by default in it by running this query: ALTER DATABASE dbname SET DELAYED_DURABILITY = FORCED; Is there a way to run this query right after db instance is created through CF template? My CF template looks like this: Answer Is