8,789 questions
-3
votes
2
answers
85
views
I need to perform a validation on a SQL query provided by user for it to only contain select command. How can this be done in C#? [closed]
I am developing an API that takes a SQL query as input and runs it on AWS Redshift. I do not want user to provide commands like update, drop, truncate, delete and ... - I only want the user to use ...
Best practices
0
votes
0
replies
60
views
Materialising tables for multiple end user profiles in Redshift
Imagine there's a reporting tool for which users might have the permission 'Admin' or 'User'.
We have a dimension in our models called admin_view and if the value is true then only users with Admin ...
- 572
0
votes
1
answer
33
views
Redshift PartiQL for unpivoting the keys and values in a Map typed SUPER
AWS documentation for querying Redshift's super is very much all in on supers that are arrays.
But, I happen to want a super that is a map, as I'm sure a number of people do, and I would like to then ...
- 45.6k
1
vote
1
answer
63
views
Redshift : Opening multiple cursors from within the same client connection is not allowed
I have the following procedure written in redshift, which uses 2 for loops to get country code first and then years as per the country codes.
--Procedure : sp_for_loop_test
create or replace procedure ...
- 7,355
0
votes
0
answers
66
views
Redshift - SQL Error: Internal jdbc driver error
I am trying to use for loop within the stored procedure to retrieve the employee id and process further but getting an error:
Error: SQL Error: Internal jdbc driver error
Procedure:
create or replace ...
- 7,355
0
votes
0
answers
36
views
How to connect to Amazon Redshift using AWS IdC access token (M2M) with JDBC driver in Java?
I'm trying to connect to Amazon Redshift using an AWS Identity Center (IdC) access token from a Java application via the Redshift JDBC driver.
I’m using a machine-to-machine (M2M) setup (no browser-...
- 195
0
votes
0
answers
75
views
Power BI DirectQuery to Redshift: ODBC error with pg_catalog.date_add(...) does not exist
I'm using Power BI Desktop with Amazon Redshift in DirectQuery mode. Data loads initially (sometimes at least / sometimes it has errors), but when I scroll down in a visual (e.g., table), I eventually ...
- 1
0
votes
1
answer
84
views
How to setup proxy for redshift_connector
I'm trying to use redshift_connector by AWS to connect to my Redshift cluster.
To be able to connect, I first need to set up a proxy. Since redshift_connector doesn’t seem to have a dedicated ...
1
vote
1
answer
79
views
Computing users based on months and year value using window function
I’ve written a query that gives me what I’m looking for in Redshift, but I want to rewrite the query without using UNION ALL, the idea is to have a cumulative count over a profile_type annual and ...
- 57
1
vote
1
answer
134
views
How to handle idle sessions in Redshift Serverless?
We are using Amazon Redshift Serverless as our primary database, with a maximum of 8 RPUs. To optimize costs, we recently reduced the idle session timeout from the default of 1 hour down to 10 minutes....
1
vote
0
answers
161
views
Why does SQLAlchemy 2.x fail with Redshift due to standard_conforming_strings, and can this be fixed like in RDS?
We are integrating Amazon Redshift with SQLAlchemy and found an incompatibility when moving from SQLAlchemy 1.4.x to 2.x.
Problem
When using SQLAlchemy 2.x, our Redshift connections fail with:
...
- 11
1
vote
0
answers
156
views
Create users using IAM Role in Redshift Serverless
I want to invoke execute_statement API of Redshift Serverless with a create user statement like CREATE USER "john" PASSWORD 'johnDoe1';. However, when I do this via Python script (or ...
- 2,781
0
votes
2
answers
114
views
Redshift cannot parse response from python lambda
I am trying to create and run a lambda function from within Redshift.
I have scaled the lambda_handler back to something very simple
def lambda_handler(event, context):
print("Received event:&...
1
vote
0
answers
30
views
Trust policy issues with 'dms-access-for-endpoint' IAM role
I'm building DMS solution which pulls data from Azure SQL Server to Redshift. I'd like to limit the Trust Policy of the dms-access-for-endpoint role. All works fine with the basic setup, ie:
{
...
- 79
1
vote
0
answers
108
views
Getting permission denied to rls policy in Redshift
I have an employee table which has data for all region and countries. As an admin, I want to enable RLS on this table so that the end user can only view the data he is allowed to. The access ...
- 91
0
votes
0
answers
37
views
recursive sum of totals by org and descendants in redshift
I have a table like this:
total
alert
group_id
hlevel
full_path
parent_id
root_group_id
5100FFFF-60B6-D5CD-FCCD-A8A3E03F0000
1
BizA\DivA
5100FFFF-60B6-D5CD-BFBA-A8A3E03F0000
5100FFFF-60B6-D5CD-FCCD-...
- 1
1
vote
0
answers
23
views
Redshift Query Order in Output [duplicate]
I am selecting a list .
select * from table where id in (3,1,2,4)
Is there a way to force the same order in the output as it is shown in the select above?
- 31
0
votes
0
answers
86
views
How to check if a connection is alive when working with redshift_connector
I'm facing a weird issue while using redshift connector in my python service ( for communicating to a RedShift DB instance ). It's not a consistent behaviour. When the pod is newly created, I was not ...
- 1
0
votes
1
answer
94
views
Cant drop Redshift Copy Job
I've created a Copy job using the following:
COPY public.test_table
FROM 's3://<s3_bucket>/<table_prefix>
IAM_ROLE '...'
format as csv
IGNOREHEADER 1
JOB CREATE copy_test_delete_me AUTO ON ...
- 331
1
vote
1
answer
144
views
Column reference "id" is ambiguous while filtering in a chart
I created a dataset in Superset SQL Lab by joining 2 tables on session_id.
SELECT *
FROM t1.performance as perf
INNER JOIN t1.test_session as session
ON perf.session_id = session.session_id
Now, to ...
- 33
1
vote
1
answer
293
views
Redshift [42804]: ERROR: column "" is of type integer but expression is of type character varying when creating NULL columns despite type casting
Similar to: [Amazon](500310) Invalid operation: column "INVC_RLS_LCTN " is of type integer but expression is of type character varying;
In Redshift I have a view creates a long series of ...
- 13
1
vote
1
answer
55
views
Redshift CRC32 producing different output than MySQL
Redshift's CRC32 function returns a string:
SELECT CRC32('abc');
> 364b3fb7
while MySQL returns an integer:
SELECT CRC32('abc');
> 891568578
From AWS docs:
The CRC32 function returns an 8-...
- 123
0
votes
0
answers
131
views
Grant SELECT on AWS Redshift external table not supported — how to implement principle of least privilege?
I'm working with Amazon Redshift Spectrum and trying to follow the principle of least privilege by granting only the necessary access to users or roles.
I attempted the following command:
GRANT SELECT ...
- 649
2
votes
2
answers
97
views
Transposing the row into a column header and other as column value in Redshift SQL
Source data - table name temp:
Source
value
ID
A
C123
111
B
V123
111
A
C456
222
B
V456
222
I need to convert the column name source output as a new column header and store the column name value output ...
- 91
0
votes
2
answers
81
views
How do I sum over different combinations of variables in DBV?
Following is my table. I want to sum across all different combinations and put the sum in separate columns, not in the same column.
data:
Subject
Var1
Var2
Var3
Var4
Constant1
Constant2
ONE
1
2
1
1
A
...
- 21
0
votes
1
answer
41
views
Springboot with JPA cannot save Auto Increment field in redshift
Tried to store data into redshift table from spring boot. Got an error because of the auto increment ID column. Below is the code and error.
Entity class
@Entity
@Data
@Table(name = "...
- 374
0
votes
2
answers
67
views
Query super in redshift for values with a forward slash
In redshift I have data in a super type column roles like
{"foo":true, "foo/bar":true}
I can query for foo like
WHERE roles.foo = true
I cannot work out how to escape to query ...
- 45
0
votes
2
answers
130
views
Redshift Join on Regex Match
Attempting to join one table (Table a) with a string (field fruit) against another table (Table b) with a list of comma separated strings (field all_fruits) as the join condition. I want to return ...
- 423
0
votes
2
answers
65
views
liquibase redshift setup - "current transaction is aborted" error
I am trying to get Liquibase set up so that it can manage changes to a Redshift database. I have the Liquibase Redshift extension working and am using the Redshift JDBC driver. When I run the update ...
- 106
0
votes
1
answer
37
views
Can't connect to Redshift from Fargate
I have a publicly accessible Redshift cluster in two public subnets with public ips and an IGW attached. Its security group allows access on port 5439 from my own IP and from within another sg. Inside ...
- 71
-2
votes
1
answer
59
views
Concatenating Time format in SQL?
I have a time column:
metric_date
Start_time
2025-04-28
14:30
2025-04-28
1:4 0
But I want the output to be this
metric_date
Start_time
Start_date
2025-04-28
14:30
2025-04-28 14:30:00
2025-04-28
1:4 0
...
- 31
1
vote
1
answer
133
views
Weird query plan for "manually partitioned" table in redshift
Since AWS Redshift doesn't support native table partitioning, I implemented what in some places is called "manual partitioning" (eg.: here); in short:
Instead of creating a big time-series ...
- 23
1
vote
1
answer
46
views
Loading blank values into AWS Redshift
I am trying to load blank values as blank and null values as null from a CSV using COPY command in Redshift. But I see that when I use Data Conversion Parameters like EMPTYASNULL it loads both blanks ...
- 51
0
votes
0
answers
86
views
How to create string literals for queries in redshift? or any way to encapsulate my query for special charecters
I have created a redshift API that my front-end users can use to query the database. For security reasons, The system adds the word Select into before the query returned by user so only select ...
-1
votes
2
answers
52
views
DynamoDB Scan with Filter v/s Redshift query operation
I was going through dynamo DB scan operation and was wondering how it would be different from performing query operation on redshift.
A filter expression is applied after a Scan finishes but before ...
- 43
0
votes
0
answers
52
views
Create Redshift-managed VPC endpoint
I'm trying to create an endpoint for a Redshift serverless cluster in a different account, I granted access to it to the new account and in the form for creating the endpoint (see image I can see in ...
- 812
1
vote
2
answers
116
views
Recursive CTE can only work with temp table as initial SQL in Tableau
I'm trying to use recursive CTE in Tableau. I reazlized this doesn't work when running custom SQL, but I can create a temp table
CREATE TEMP TABLE temp_table as
WITH RECURSIVE missing_dates_cte (...
- 194
0
votes
0
answers
223
views
DBT Redshift BrokenPipe: server socket closed Error
Error: BrokenPipe: server socket closed. Please check that client side networking configurations such as Proxies, firewalls, VPN, etc. are not affecting your network connection.
DBT newbie here. ...
- 102
0
votes
2
answers
151
views
I need to filter records with nested column in Redshift
I have data stored in s3 with parquet format, and have multiple column with array of struct
and also array inside array of struct
Here I am query one category column which have array of struct
Sample ...
- 5,512
-5
votes
1
answer
69
views
Trouble with recursive query in redshift
Considering following test data:
CREATE TABLE products_su
(
country varchar(2),
intprd varchar(20),
period date,
su int
);
INSERT INTO products_su (country, intprd, "period&...
- 21
0
votes
2
answers
89
views
In AWS Redshift, are there errors that can cause a stored procedure to stop, even when having a error handling block?
I wrote a stored procedure that calls other procedures inside multiple transaction blocks (BEGIN…END) with their own error handlers. I expected that if one block encounters an exception, it would go ...
- 1
0
votes
1
answer
113
views
Redshift Materialised View Permissions on Zero-ETL base data
I am creating a table in Redshift Serverless using Zero_ETL from DynamoDB and then creating a materialised view for use by a BI Dashboard, with the intention that the connection can only access the MV....
- 31
1
vote
2
answers
122
views
Multiple recurrence within redshift/postgres recursive query
I'm seeking help with including deeper recurrence then last row with recursive query. Source data:
CREATE TABLE products_su(country, intprd, "period", su)AS VALUES
('GL', 'Medicine', '2019-...
- 21
0
votes
0
answers
33
views
Why does one S3-to-Redshift COPY operation take 50+ seconds while seven identical operations take 2-3 seconds?
A lambda that DELETEs and COPYs 8 parquet files from S3 into 8 Redshift tables (using boto3).
Each table is created from a single file, each with less than 3K rows.
7 of the queries take 2-3 seconds ...
- 71
0
votes
0
answers
131
views
Cannot deserialize table. Missing mandatory field 'InputFormat' in response from external catalog. Add missing field to table and retry
I am trying to run a select statement in Amazon Redshift on a table in my AWS Data Catalog.
The AWS Data Catalog database was created by a crawler connecting to an external Oracle database.
I get the ...
- 3
0
votes
1
answer
163
views
Error calling Redshift stored procedure with refcursor
I’m following this AWS Redsfhit documentation to create a stored procedure that returns a result set using a cursor, but I keep getting the following error:
Number of cells in the row (0) does not ...
2
votes
2
answers
78
views
Identify duplicates within a period of time using Redshift SQL
In a table, I have plan details of customers with their customer_id and enroll_date.
Now, I want to identify duplicate and valid enrollments from the overall data.
Duplicate: If a customer enrolls a ...
0
votes
1
answer
148
views
AWS Redshift Serializable Isolation Violation Error With Snapshot Isolation
I am receiving intermittent Serializable isolation violation on xid errors from a stored proc in Redshift so I followed the guidance in the aws docs (https://docs.aws.amazon.com/redshift/latest/dg/...
- 267
0
votes
1
answer
74
views
Extract specific conditional string SQL Redshift
I want to extract only val2 from the below column1 of a table, I'm not sure how to do it with SQL though I've tried with regexp_subtr/instr, the length of column values are dynamic but the contents of ...
- 146
0
votes
0
answers
20
views
Cross object reference in AWS Redshift
I have to reference the view created in the database b from database c.
Note: I have created a table created in database a.
Consider this is my scenario,
I have created a table in database a, and a ...
