Questions tagged [redshift]
Redshift : Amazon's Column-Oriented Database Product
158
questions
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 ...
- 254
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/...
- 254
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 ...
- 254
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 ...
- 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]...
- 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 ...
- 125
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 ...
- 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.
- 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 ...
- 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 ...
- 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 ...
- 3
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:
...
- 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 ...
- 125
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'...
- 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 ...
-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) ...
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 ...
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 - ...
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 ...
- 254
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
- 133
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 ...
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 ...
- 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 ...
- 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 ...
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 ...
- 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 ...
- 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 ...
- 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 ...
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 | ...
- 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 ...
- 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, ...
- 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 /...
- 254
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 ...
- 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(...
- 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 ...
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 ...
- 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 ...
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?...
- 63
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."...
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 ...
- 11
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 ...
- 237
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....
- 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 ...
- 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 ...
- 21
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 ...
- 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 ...
- 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 ...
- 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 ...
- 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:
[...
- 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(...