Questions tagged [redshift]
Redshift : Amazon's Column-Oriented Database Product
170 questions
0
votes
0
answers
58
views
Redshift (Serverless) SVV_TABLE_INFO shows huge storage sizes for tiny tables
My Redshift serverless shows massive storage size usage for tiny tables that so far have had only a couple DDL statements, only inserts, and are overall tiny tables.
When I run select "schema&...
- 121
0
votes
0
answers
54
views
Is there a way to connect to Redshift from dBeaver CE on Mac Using a Configuration Profile
I am trying to connect to RedShift using dBeaver CE from my Mac. One of the other teams in our organization has a document to connect to RedShift using SQLWorkbench, but that is on Windows. I tried ...
- 103
0
votes
0
answers
77
views
Best practice for applying complex business logic during transformation in ETL from Aurora db to Redshift data warehouse
I'll be pushing data from an Amazon Aurora db to a Redshift data warehouse. The painful part is that the transformation portion of the ETL workflow relies heavily on business logic, that lives within ...
- 143
0
votes
1
answer
125
views
Why does Redshift ignore length when grouping by VARCHAR of only spaces?
Imagine I have a column name that is n rows and the nth entry is equal to REPEAT(' ', n). For SELECT DISTINCT name FROM table I would expect to receive n rows because each name is unique by definition....
0
votes
1
answer
476
views
Can't revoke permissions on Redshift
I'm having a issue revoking privileges that is stopping me from droping users
select usename, acl.*
from pg_default_acl as acl join pg_user on usesysid = defacluser
where usename = 'user1';
gives me:
...
- 113
1
vote
2
answers
88
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
153
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, ...
- 533
0
votes
1
answer
628
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
1
answer
938
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 ...
- 353
0
votes
1
answer
530
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
1
answer
2k
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
207
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
142
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
243
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
1
vote
0
answers
231
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
2
answers
1k
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 ...
- 205
1
vote
0
answers
201
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
41
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
3
votes
1
answer
424
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 ...
- 324
0
votes
1
answer
30
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
369
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
24
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? ...
- 101
0
votes
1
answer
652
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 ...
- 353
0
votes
1
answer
19
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 ...
- 533
1
vote
0
answers
5k
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 ...
- 324
1
vote
0
answers
194
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/...
- 324
0
votes
1
answer
80
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 ...
- 324
1
vote
0
answers
377
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
3
votes
1
answer
3k
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]...
- 61
1
vote
1
answer
1k
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
165
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
447
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
166
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:
...
- 353
0
votes
1
answer
536
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
2
votes
1
answer
4k
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'...
- 751
-2
votes
1
answer
488
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
3k
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
- 205
0
votes
0
answers
791
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
2k
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
428
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
3k
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
265
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
180
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
190
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 ...
- 111
0
votes
1
answer
393
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 /...
- 324
1
vote
1
answer
376
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 ...
- 211
0
votes
1
answer
4k
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
682
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 ...
