Questions tagged [redshift]

Redshift : Amazon's Column-Oriented Database Product

Filter by
Sorted by
Tagged with
0 votes
0 answers
7 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 ...
user avatar
0 votes
0 answers
8 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/...
user avatar
0 votes
1 answer
9 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 ...
user avatar
0 votes
0 answers
7 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 ...
user avatar
  • 1
2 votes
1 answer
34 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]...
user avatar
  • 41
0 votes
0 answers
13 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 ...
user avatar
0 votes
0 answers
10 views

How to clone Redshift table including grants

I want to replicate (exact clone) a table structure in Redshift. What I'm currently doing is: CREATE TABLE a.b_new(LIKE a.b); After doing this, when checking the created a.b_new schema, it is missing ...
user avatar
  • 1
-2 votes
0 answers
7 views

Data is deleted in a table in redshift cluster

I found that some records are deleted from a table in a redshift cluster. How we can find who has deleted those rows.
user avatar
  • 1
0 votes
0 answers
7 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 ...
user avatar
  • 101
0 votes
0 answers
14 views

Unable to drop a user in AWS Redshift

I want to drop user foo from an AWS Redshift db DROP USER foo; ERROR: user "foo" cannot be dropped because some objects depend on it DETAIL: 1 object in database bar 1 object in database ...
user avatar
  • 171
0 votes
1 answer
36 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 ...
user avatar
1 vote
0 answers
16 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: ...
user avatar
  • 265
0 votes
1 answer
19 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 ...
user avatar
0 votes
0 answers
177 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'...
user avatar
  • 702
0 votes
0 answers
15 views

Does cancelling a VACUUM SORT in Redshift make all the work done useless?

I've tried to run a VACUUM SORT on a massive table for several weekends now, starting them Friday night and praying that they finish by Monday morning, but every time have had to kill it, because it ...
user avatar
-2 votes
1 answer
55 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) ...
user avatar
0 votes
0 answers
32 views

Job failure message: ERROR: QUERY ID SHOULD BE STRICTLY INCREASING:last query id 2000000000, update query id 1197

I get the below error while executing a redshift stored procedure - error - Job failure message: ERROR: QUERY ID SHOULD BE STRICTLY INCREASING:last query id 2000000000, update query id 1197. Not ...
user avatar
0 votes
0 answers
32 views

Redshift extract function not being consistent

I'm trying to move Postgres(v 11) queries to redshift(v 8). I encountered a problem with date_part. When I try to run this, I'm getting the below error. select date_part('hour', modified_on - ...
user avatar
0 votes
0 answers
23 views

Default permissions / permission inheritance or some other common sense way to control permissions in Redshift

It blows my mind how clunky Redshift is. Maybe its PostgreSQL in general. In any case, it's ridiculous. I have users connecting to a Redshift cluster with SAML authentication. The user is created on ...
user avatar
0 votes
1 answer
285 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
user avatar
0 votes
0 answers
79 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 ...
user avatar
0 votes
1 answer
255 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 ...
user avatar
  • 221
0 votes
0 answers
49 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 ...
user avatar
  • 109
1 vote
1 answer
259 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 ...
user avatar
0 votes
0 answers
12 views

AWS glue - load data by a dynamic condition

I have a concern to apply AWS Glue to my project for below case to see is it possiple on Glue -I have a AWS RDS Postgresql db and a Resdhift DW -I want to load data from Postgresql to Redshift but not ...
user avatar
  • 13
0 votes
1 answer
42 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 ...
user avatar
  • 1
1 vote
1 answer
259 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 ...
user avatar
  • 133
0 votes
1 answer
55 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 ...
user avatar
0 votes
1 answer
25 views

missing database from datname [closed]

running select datname FROM pg_database gives me a partial list. I'm missing there the db aa. How is that be? How can I sort it? all=# \dt aa.* List of relations schema | name | ...
user avatar
  • 383
1 vote
0 answers
33 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 ...
user avatar
  • 11
0 votes
1 answer
55 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, ...
user avatar
  • 103
1 vote
0 answers
525 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 /...
user avatar
1 vote
1 answer
66 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 ...
user avatar
  • 171
0 votes
1 answer
990 views

Include single quote string in redshift dynamic SQL

I have function named Schema.func_date(date,varchar) I wanna do like this(I am using below statement inside Store proc) Execute 'Select *, ID as Main_Id, ' || 'schema.func_date(quote_literal(...
user avatar
  • 103
0 votes
1 answer
89 views

Using caret (^) for pattern list matching negation in SIMILAR TO and ~ queries in Redshift

I'm quite new to using POSIX regex in Redshift. I honestly think that this pattern must be compatible. [^A-Za-z0-9\\.\\,-]+ However, as I've tried a query like this SELECT * FROM my_table WHERE ...
user avatar
2 votes
0 answers
42 views

Why late materialization is not triggered for insert queries?

Assume I have table t with columns c1,c2,c3. (not so many records, ~10k) and an empty table tmp_table with same structure. Talking about columnar store, I want to understand when does it make sense to ...
user avatar
  • 121
0 votes
1 answer
178 views

Export mutliple query output into one excel sheet using Redshift and SQL Developer

I am connected to Redshift from SQL Developer 19.2.1. I have a requirement to export multiple query output into a single excel sheet, one query output should be in one Tab. Please advise on how to ...
user avatar
1 vote
1 answer
72 views

How can Amazon Redshift be built based on PostgreSQL if Postgres is row-based?

According to Amazon, Redshift is based on PostgreSQL, and is a column-oriented database management system. This looks to me as a very self-conflicting statement, isn't Postgres a row-oriented database?...
user avatar
0 votes
2 answers
63 views

How to use SQL CASE result to compare a column and get another case result

I'm trying to compare my case result (desirecountry) with an existing column (so.country_name) to get another case result (Final_Country_Map_Name): SELECT subs."user email", subs."...
user avatar
1 vote
1 answer
1k views

Calculate time difference in minutes

I am Amazon Redshift. I tried to calculate the time difference between two time stamp columns in minutes, but the timestampdiff function does not work on it. The error is telling me that minute column ...
user avatar
0 votes
0 answers
68 views

Can Redshift process CTE / with chains without making interim tables?

If you are careful about structuring a CTE chain, is Redshift capable of analyzing it and determining that interim results are being used in a linear fashion? If so, it could drop any interim tables ...
user avatar
0 votes
2 answers
168 views

SQL join on col1 if present else use col2

There are two tables T1(col1) and T2(col1, col2) which needs to be joined. But T2 may have col1 as null in which case col2 can be used as backup. What I want is either join on T1.col1 = T2.col1 or T1....
user avatar
  • 101
0 votes
1 answer
464 views

Substitute DISTINCT ON for query transformation (Postgresql to Redshift)

I need to transform my Postgresql query to Redshift and I working with the following data, here is an excerpt: In my final output, I would like only to keep rows with the highest rank (in green). And ...
user avatar
  • 33
1 vote
1 answer
180 views

SQL (Redshift) SELECT COUNT from CSV column

Hi exchange community, Problem statement and details: I am trying to query a column that contains CSV data to produce a count of instances of each value in the table. There are a total of 14 ...
user avatar
1 vote
0 answers
30 views

Implementing data warehouse on the cloud [closed]

I need to implement a data warehouse on the cloud for a mid-sized company. The database size will be around 200 Gb. My options are (so far): AWS with its database Redshift Azure with its database ...
user avatar
  • 51
0 votes
1 answer
452 views

In general should we avoid using ORM's with Redshift

My team is considering utilizing Hibernate/Envers for a granular point in time rollback solution for Redshift data. I have heard that using an ORM with Redshift is not a good idea. Are there ...
user avatar
  • 273
0 votes
0 answers
43 views

Multiple GETDATE() in Redshift

I have a long (and long running, ~30min depending on load) query. It has multiple statements UNION'ed together. Most of the statements are using the GETDATE function[1]. Is the function executed ...
user avatar
  • 365
0 votes
1 answer
529 views

Is it possible to spin up a free development / test instance of an AWS Redshift server to run test queries on?

I have a traditional MS SQL Server background and am interested in looking into AWS Redshift. Microsoft offers a free download of their development version installer of SQL Server, and I was wondering ...
user avatar
  • 24.2k
1 vote
2 answers
5k views

ALTER TABLE ALTER COLUMN cannot run inside a transaction block;

I am trying to alter column in redshift from varchar(30) to varchar(100) Its not working and throwing an error as below: Command used: alter table t_name alter column c1 type varchar(300); Error: [...
user avatar
  • 11
1 vote
2 answers
116 views

Is it better to have a CASE expression or a JOIN with a dimensional table?

I have a fact table which has a timestamp and I want to express the day of the week. I could do something like this: SELECT CASE WHEN date_part(timestamp,'week') = 0 then 'Monday' WHEN date_part(...
user avatar