Questions tagged [redshift]

Redshift : Amazon's Column-Oriented Database Product

Filter by
Sorted by
Tagged with
1 vote
2 answers
49 views

Is there a "correct" way for an optimizer to process a subquery if the outer query has additional where filters?

This topic came up for me as the result of investigating an error in Redshift. We have a table containing a column of numbers represented as text, but for rows where no value is present a single ...
MattB's user avatar
  • 111
0 votes
0 answers
47 views

Having the same charset and collation between Redshift and MySQL

I looking to migrate data between Redshift table and MySQL that has a string column. There are a few differences between each engine: Redshift is case sensitive, and by default MySQL isn't. MySQL, ...
Nir's user avatar
  • 513
0 votes
0 answers
23 views

Redshift Serializable isolation violation error

Our setup: We have a Redshift cluster as our data warehouse. We have a Postgres OLTP DB from which a number of write-heavy tables are getting replicated into Redshift using DMS. We use dbt to then ...
Steve Robinson's user avatar
0 votes
0 answers
47 views

Redshift: Any difference in performance between # and temporary tables?

Is there any difference between the below two styles of using temporary tables in Redshift -- in terms of cluster performance (or) R/W into and from the table? I am new to Redshift, hence bit unaware ...
ranit.b's user avatar
  • 131
0 votes
1 answer
145 views

Get ERD from my Redshift database

I am a newbie in database management/governance. The problem: I have my companies Postgres RDB in AWS Redshift. So this is not a private project, but enterprise environment. I add new tables/columns, ...
hunsnowboarder's user avatar
0 votes
0 answers
31 views

AWS Redshift: Error in fetching Last ID column value after an insert statement

I have asked this question in SO. But reposting here due to appropriateness. I want to fetch the last ID number after an INSERT event in AWS Redshift. For this, I have written a Stored Procedure in ...
pythondumb's user avatar
0 votes
1 answer
330 views

Is there a way to reference a column that's ambiguous?

This isn't an actual problem I'm having, just curiosity after a discussion with a colleague. Given: SELECT 1 as foo, 2 as foo; How can you select values from one column: SELECT foo FROM (SELECT 1 as ...
Kit Sunde's user avatar
  • 313
0 votes
0 answers
17 views

Can you aggregate SUM etc as the same type?

In PG and RedShift when you aggregate an int4 column, it's returned as an int8 column, makes perfect sense but how can you keep the types? For example: SELECT SUM(int4_colum) AS int8_column FROM foo; ...
Kit Sunde's user avatar
  • 313
0 votes
1 answer
232 views

Strange error in Redshift stored procedure

I have a stored procedure in Redshift that is loading data from a bunch of S3 JSON files in a loop. Every time a file is loaded, its name is stored into a log table on Redshift side so i can link it ...
Oleksandr's user avatar
  • 111
0 votes
0 answers
44 views

CDC on postgreSQL to Redshift

I am trying to implement CDC on PostgreSQL to Redshift and looking for some approaches. I had a look at Debezium but it looks like it's not ideal for my requirement. I want to read all delta changes ...
Bab's user avatar
  • 1
0 votes
1 answer
1k views

Dynamic data used as column name in Amazon Redshift

I am trying to use a year-month dynamic function to use it as a column name in Amazon Redshift. I don't want to write a hardcode for that. The database tool I'm using is DBeaver Ultimate and the code ...
vinifts's user avatar
1 vote
0 answers
101 views

Limit disk space consumption of Redshift queries

How can I limit the disk space used by queries in Redshift to prevent the cluster from running out of disk space? Simple queries on a large table can take up a huge amount of space. I ran out of disk ...
phobic's user avatar
  • 111
0 votes
0 answers
99 views

Describe table from Athena data source in DataGrip fails

Whenever I attempt to Describe an AWS Athena table in DataGrip it fails. The error states that is is a permissions error but I am fairly certain it is not. In fact, I can describe tables using ...
Mattboy91's user avatar
0 votes
0 answers
130 views

Redshift / Postgresql query multiple tables faster than Natural Full Join

I have been given a requirement to query 8(+) different Redshift tables and return some of the fields where email = provided email address. Current implementation: (select product_name, email, ...
AylaWinters's user avatar
0 votes
0 answers
108 views

JSON_SERIALIZE potential limitations due to VARCHAR size limit

I have this table below in Redshift where the column "Customer" may grow in information in the future as more details/ prediction output are added: Id Customer ...
Kusisi Karem's user avatar
1 vote
0 answers
129 views

Redshift User password change permission denied

Redshift user not able to change its own password getting below error: ALTER USER or_user WITH PASSWORD 'md5<hash>'; ERROR:permission denied Also tried granting 'ALTER USER' privileges using ...
Aadu's user avatar
  • 11
0 votes
1 answer
669 views

How to query redshift SUPER for non empty objects

Context: I have a column called event_properties which has a lot of values of {} which is a redshift SUPER datatype. A lot of values are of empty object such as: Objective: Filter out values that ...
Andrew Wei's user avatar
1 vote
0 answers
68 views

Restart a Running Sum after Static Threshold

I've been stuck trying to solve the following problem for some time: I essentially want to restart the rolling sum if the rolling sum exceeds 3600, else continue summing. I've sort of have it working ...
WhimsicalWhale's user avatar
0 votes
0 answers
34 views

Optimization of google query with multiple LEFT JOIN and OR operators!

I have a SQL query that runs twice daily and ends up spilling disk space, the query selects data from left joining three tables and use multiple OR operators to fetch results and then insert all ...
Greg 's user avatar
0 votes
0 answers
225 views

Getting ODBC general error in DMS while using postgres read replica as source

I'm getting below error in AWS DMS while migrating from Postgres read replica to Redshift. I'm using full load only.The DMS task will run for few mins and some tables will be loaded to target and then ...
Sumant Kumar's user avatar
3 votes
1 answer
317 views

Is Redshift translating "dbo" schema to "public" schema?

We have a strange issue which has appeared in the last couple of days. We have two Amazon Redshift clusters. One cluster has our production workload, and the other cluster has our non-production ...
Appleoddity's user avatar
0 votes
1 answer
29 views

How to insert "C:\" in text column, PL/pgSQL

I'm currently facing a strange issue on my PL/pgSQL query to insert string in text column, i will explain: I have a table with following schema: CREATE TABLE IF NOT EXISTS Loading_Info ( ...
user3040157's user avatar
0 votes
0 answers
302 views

Query to select distinct pairs of value from one column in a table and distinct value from another column in another table

Table A Col 1 Col 2 A B C D Table B Col1 Col2 E F G H Expected Output Col1 Col2 A E B G They should be distinct in each of the column of combined table, it should not be: (A, E) and (A, G),...
Sql_Find's user avatar
0 votes
0 answers
21 views

Is there a difference in performance loading different datatypes

Context: loading (COPY) a high volume of data to a warehouse (Redshift specifically however curious about all) Question: Is there a performance difference in the different datatypes that I am loading? ...
OctaveParango's user avatar
0 votes
1 answer
479 views

Is it possible to drop a RedShift auto materialized view?

I'm trying to alter a table definition, but it's dependent on a materialized view which is managed by RedShift Auto Materialization and not by us. So attempting ALTER TABLE my_table ALTER COLUMN ...
Kit Sunde's user avatar
  • 313
0 votes
1 answer
18 views

How to make the first match in a group a different group

I need to group by a certain column and to make the first occurrence into another group. select * from table; id currency amount 1 USD 1 2 USD 2 3 EUR 3 4 IL 4 5 EUR 5 6 USD 6 So something ...
Nir's user avatar
  • 513
1 vote
0 answers
4k views

How do I loop through a cursor in Redshift?

Rather than pollute this thread with the dozens of different things I've tried, including using a stored procedure, dynamic SQL, etc., all of which results in one error or another, I will ask what is ...
Appleoddity's user avatar
0 votes
0 answers
127 views

Why won't this config use DECLARE / FETCH on Amazon Redshift?

In my home directory I have created: .odbcinst.ini [ODBC Drivers] Amazon Redshift (x64)=Installed [Amazon Redshift (x64)] Description=Amazon Redshift ODBC Driver(64-bit) Driver=/opt/amazon/...
Appleoddity's user avatar
0 votes
1 answer
44 views

How to loadtest network throughput between two systems when one is a cloud database server?

I am troubleshooting query performance issues when running large queries against an AWS Redshift cluster. The majority of the time is spent transferring the data over the network and processing the ...
Appleoddity's user avatar
1 vote
0 answers
264 views

How to optimise a Redshift table with a SUPER data type column?

We currently have a small table in Redshift with 3 columns col1 (id): varchar(36) col2: varchar(36) col3: SUPER Where the SUPER column contains a dictionary which can vary in length. The total size ...
Bex's user avatar
  • 11
2 votes
1 answer
2k views

Redshift Spectrum Implicit Unnest of List in Join

I have data that is stored in S3 and I am querying it using SQL via Redshift Spectrum. One of the columns is stored as a list in the S3 file like so: Table_ABC column_a items_list a. [item_1, item_2]...
peegee's user avatar
  • 41
1 vote
1 answer
500 views

Redshift - is it possible to run query that select from external table - only if table exist

what i am try to do is something like this: select case when (/* here should come sql statement that check if table exist */) then (select count(*) from my_external_table) else 0 end I know ...
Eyal leshem's user avatar
0 votes
0 answers
144 views

Redshift PG_TABLE_DEF table listing unknown tables

When I count number of tables in my schema manually, it is 220. When I count number of views in my schema manually, it is 23. 220 + 23 = 243 However, this query: select count(distinct tablename) from ...
Nasrudeen's user avatar
  • 101
0 votes
1 answer
308 views

SQL | Shuffle order set by row_number based on another table

I need to manually shuffle the order set by row_number in a table based order mentioned in the different table - Table A Name A B C D Table B name manual_order_no C 1 D 3 Expected result ...
Deep4Web's user avatar
1 vote
0 answers
141 views

Is it possible to find more details on svv_vacuum_progress status Failed jobs in RedShift?

Running SELECT * FROM svv_vacuum_progress returns... table_name status time_remaining_estimate foo_table Failed The table appears to be most recent in stl_vacuum, but they're marked as completed: ...
Kit Sunde's user avatar
  • 313
0 votes
1 answer
387 views

Is pg_catalog is available for aws redshift data-share consumer

I just started to read about aws redshift data-sharing feature. And I wonder if Data consumers has access to "pg_catalog"/INFOMATION_SCHEMA of tables that exposed to them? (the goal is that ...
Eyal leshem's user avatar
1 vote
1 answer
3k views

Unexpected Redshift support for the ISNULL() function. Is this documented?

As far as I know, the ISNULL() function is only compatible with T-SQL. Yet, I am working on a Redshift cluster, using Datagrip, that accepts the ISNULL() function and returns results without error. I'...
dev_etter's user avatar
  • 741
-2 votes
1 answer
366 views

SQL Subquery not working in Redshift

I'm trying to calculate report for ratio per week. Ratio = Sales_Dollars/Inventory_Dollars Sales_dollars per week and Inventory_dollars per week are calculated separately in 2 different queries (A, B) ...
maayya007's user avatar
1 vote
1 answer
2k views

How to GROUP by month & year with an epoch timestamp in AWS Redshift

Assuming a very simple schema as pictured below. (unfortunately the column is actually named timestamp) How do I simply get a count on records grouped by YEAR & MONTH
Andrew Wei's user avatar
0 votes
0 answers
565 views

What's the equivalent of URLencode for Redshift?

Using Redshift, I need to encode a string before I pass it as a GET string in a URL. Is there an equivalent of URLencode() for Redshift SQL? I am passing data from Redshift to an email marketing ...
Polyvinyl's user avatar
0 votes
1 answer
1k views

AWS Redshift: COPY command fails with 'Role needs to be owned by the same account with cluster'

I have a problem with COPY in redshift - it's the first time I use it, so probably a beginner's error. I have uploaded a CSV file to S3 and created the matching table in redshift - I tried the ...
j4nd3r53n's user avatar
  • 231
0 votes
0 answers
95 views

JOIN vs WITH differences

apologize if there is the same question. When I started my career, my manager told me that you don't need to use "WITH", you can do anything with "JOIN". Now I am wondering is it ...
Axis's user avatar
  • 109
2 votes
2 answers
1k views

SELECT inside SELECT in Redshift

select t.col1, t.col2, a.col1, b.somecolumn VQ.a, VQ.b, VQ.e, VQ.d, VQ.f, (select t.status as a, p.id as b, p.permit as c, p.des as d, p.error_code ...
Swathi Ravindran's user avatar
0 votes
1 answer
212 views

Add a list of names to a generated series

I generated a date series this way: SELECT generate_series(date '2020-01-01', date '2021-01-21', '1 day'); Additionally I have a list with name (Max Meyer, Anna Smith, Peter Gardner). How could I add ...
wladi's user avatar
  • 1
1 vote
1 answer
1k views

Redshift - Insert data into a temp table with a condition being column doc_id should be unique and not null

I'm trying to create a temp table and insert data into it in Redshift. My goal is to create a single record for unique doc_id WHERE doc_id IS NOT NULL. Here's my code: -- Creating temp table to load ...
GeoSal's user avatar
  • 133
0 votes
1 answer
150 views

Redshift postgresql time series data index DISTKEY and SORTKEY

I have around 6 billion rows of data to move to a redshift DB from MSSQL. The data will have three columns. I plan to move the data one tag at a time as there are 4 years of history at one-minute ...
Brenton Collins's user avatar
1 vote
0 answers
150 views

Prevent Certain Types of Queries (Redshift)

Some IDEs will allow users to "peak" at data by hitting some button in a menu, and under the hood the IDE performs a select * without a where clause. This can be a problem because our ...
ngb's user avatar
  • 11
0 votes
1 answer
343 views

Monthly summary of active users based on a start/end date roster

I have a table that dictates who was on a project, from a startdate to an enddate. I'd like some help writing a query that will return the number of "active" users at the end of every month, ...
James's user avatar
  • 103
1 vote
0 answers
2k views

Does RedShift support computed columns?

In PostgreSQL I can do this: CREATE TABLE Test( Effective_Date timestamp NULL, Max_Age_Date timestamp generated always as dateadd(month, 5, Effective_Date) stored ); How do I create a computed /...
Appleoddity's user avatar
1 vote
1 answer
327 views

User has more privileges in redshift than shown by relevant query

Working in Redshift dev=# SELECT table_catalog, table_schema, table_name, privilege_type FROM information_schema.table_privileges WHERE grantee = 'myser'; table_catalog | table_schema | table_name ...
pkaramol's user avatar
  • 201