Questions tagged with Amazon Redshift

Content language: English

Sort by most recent

Browse through the questions and answers listed below or filter and sort to narrow down your results.

I want to query data from dynamoDB using GSI and sort key through Amplify Datastore. Is this possible?
0
answers
0
votes
18
views
asked 21 days ago
hi , I created a table in amazon redshift database , " create table sales( salesid integer not null, listid integer not null, sellerid integer not null, primary key(salesid)); " , after successfully creating I inserted values into the table , but the table is accepting the duplicate values for the salesid , hence it is not maintain any primary key rules. ![Enter image description here](/media/postImages/original/IMk1H4dVckQduGu2CMn5VPAw)
3
answers
0
votes
62
views
asked 23 days ago
Hi! I am trying to create a store procedure to test passing multiple IDs to a stored proc to be used in the IN clause. In MySQL we pass json all the time. But I am unable to come up with a proper way to do so in Redshift. I saw some samples on creating some temp tables and perhaps using the dynamic SQL but I was told I could not use temp tables and/or dynamic sql for this project. ``` CREATE OR REPLACE PROCEDURE "pkg_util_test_passing_json"( v_json_ids varchar, INOUT "result" refcursor) LANGUAGE plpgsql AS $$ DECLARE BEGIN OPEN result FOR -- Need to extract all ID values to be used in the IN clause - not just first one. -- SELECT json_extract_path_text( json_extract_array_element_text(v_json_ids, 0, true ) , 'id'); SELECT * FROM mytable WHERE id IN (SELECT * FROM ?????); END; $$ ``` Here's how I am calling it: ``` BEGIN; CALL "pkg_util_test_passing_json"('[{"id":31997051},{"id": 31997052}, {"id": 31997053}]', 'result'); FETCH ALL FROM result; ``` **Many thanks in advance,**
Accepted AnswerAmazon Redshift
2
answers
0
votes
48
views
MZ
asked 24 days ago
I'm following documentation from : 1. https://docs.aws.amazon.com/emr/latest/ReleaseGuide/emr-spark-redshift-readwrite.html 2. https://github.com/spark-redshift-community/spark-redshift My code: ![Enter image description here](/media/postImages/original/IMEbE35B_QRJSB3km3UK5VcQ) Logs: ![Enter image description here](/media/postImages/original/IMvorRP4RaSIa2_4CYIGmOww) I am getting these timeout messages until job reaches it's timeout threshold and fails. Is that IP from log my internal Redshift Serverless address? Am I missing something? I would appreciate any help.
1
answers
0
votes
35
views
asked a month ago
I have a Redshift serverless workgroup inside a VPC, and I want to securely create a federated schema of an RDS Aurora cluster through a security group or another mechanism. The Aurora cluster is in the same VPC and uses the same public subnets as Redshift serverless. I created a security group for Redshift serverless and a different one for the Aurora cluster instance. Connecting only works for me by opening the MySQL port to all IPv4 in the Aurora cluster instance. I use the Redshift Query Editor v2 in the browser to test. Here are some security group rules that don't work in my setup: 1- In Aurora cluster instance: Allow MySQL port for the "VPC CIDR range". 2- In Aurora cluster instance: Allow MySQL port for the "Redshift serverless security group". What do I need to do in Aurora and Redshift serverless to have the security group with restricted access to only Redshift serverless and not open to any IPv4? Can I use the Aurora VPC endpoints with Redshift Serverless? I'm also considering using a Redshift cluster, which I expect to be more expensive but also more secure than Redshift serverless. Any help you can provide is highly appreciated.
0
answers
0
votes
18
views
asked a month ago
(technically I really want to make kinesis talk to redshift, but I'm debugging redshift permissions via my outside IP address) I have enabled external access in the redshift configs. I'm using the redshift endpoint url, which resolves to a public IP I have added a inbound rule to the security group specified in the "Network and security settings" to allow ping. Also for port 5439 But I still cant ping or connect to the JDBC port. What am I missing??
2
answers
0
votes
27
views
asked a month ago
I would like to prefix 0's for employee id column and not sure how can I do this on Athena? It appears LPAD function work differently in Athena vs Redshift. select LPAD(CAST(employee_id As varchar),8,0) as EmployeeID
3
answers
0
votes
31
views
asked a month ago
Hi all, I turned on cluster relocation for one of my redshift cluster which was created in past with accessibility on port 8192. And in doing so I was successful. The Redshift page on console also shows that the cluster relocation is enabled. I am going through the Redshift doc - https://docs.aws.amazon.com/redshift/latest/mgmt/managing-cluster-recovery.html and the limitations section says that I can't turn on relocation for a cluster running on port other than 5439. Where as I was able to successfully do so. Can you please help? Am I missing something here?
0
answers
0
votes
22
views
asked a month ago
Hi! In Oracle I could issue ALTER SYSTEM FLUSH SHARED_POOL; command to clear existing data and re-load fresh data. Is there an alternative for AWS Redshift and AWS Redshift Serverless? I searched but perhaps not using the right terminology. Many thanks in advance, MZ
2
answers
0
votes
94
views
MZ
asked a month ago
Hi ! I have two tables in redshift with a one to many relation, each table has ~ 300K rows If i execute this request : ``` EXPLAIN Select Table1.field, Table2.field FROM Table1 INNER JOIN Table2 On Table1.Reference = Table2.ReferenceTable1 ``` Here is the query plan ``` XN Hash Join DS_DIST_ALL_NONE (cost=3929.08..29669880.27 rows=323034878 width=104) Hash Cond: (("outer".Reference )::text = ("inner".ReferenceTable1)::text) -> XN Seq Scan on Table1 e (cost=0.00..3143.26 rows=314326 width=104) -> XN Hash (cost=3143.26..3143.26 rows=314326 width=17) -> XN Seq Scan on Table2 o (cost=0.00..3143.26 rows=314326 width=17) ``` I don't understand what does it mean rows=323034878 ?
2
answers
0
votes
33
views
asked a month ago
Hi All, Is there any document that illustrates best/must have cloud alarms to keep track of Redshift resource usage/ resource contention/ blocking queries. Background - We have seen instances wherein our Redshift cluster becomes unresponsive until some of the blocking queries are killed. However, this activity is more manual in nature and time consuming. Please note that in most of these instances, we have seen the CPU ultilization / cluster performance parameters is nominal. I have seen that the "Average queue wait time by priority" was around 9 min. Total data scanned looked nominal too. I am looking to see if there is a way to identify such abnormalities via Cloud watch alarms. Any inputs in this regard is greatly appreciated.
1
answers
0
votes
21
views
asked a month ago
Hello. I have an Athena query utilizing UNLOAD to bring data over to my S3 buckets. The query works quite well. However, I do not get the associated header information (column names) in the transferred files. I do not see an explicit parameter that I might be able to use to ensure the header attachment to the compressed (.gz) CSV files. Any help would be appreciated. Thanks. ``` UNLOAD (SELECT * FROM dataplace.datatable WHERE file_date = date '2022-07-01') TO 's3://my/super/bucket' WITH (format='TEXTFILE', field_delimiter = ',') ```
2
answers
0
votes
40
views
asked a month ago