Questions tagged [redshift]
Redshift : Amazon's Column-Oriented Database Product
172
questions
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 ...
- 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, ...
- 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 ...
- 101
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 ...
- 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, ...
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 ...
- 131
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 ...
- 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;
...
- 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 ...
- 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 ...
- 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 ...
- 1
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 ...
- 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 ...
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, ...
- 101
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 ...
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 ...
- 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 ...
- 165
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 ...
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 ...
- 1
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 ...
- 83
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 ...
- 304
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
(
...
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),...
- 1
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? ...
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 ...
- 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 ...
- 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 ...
- 304
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/...
- 304
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 ...
- 304
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 ...
- 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]...
- 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 ...
- 145
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 ...
- 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 ...
- 3
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:
...
- 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 ...
- 145
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'...
- 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) ...
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
- 165
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 ...
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 ...
- 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 ...
- 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 ...
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 ...
- 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 ...
- 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 ...
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 ...
- 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, ...
- 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 /...
- 304
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 ...
- 201