Questions tagged [redshift]
Redshift : Amazon's Column-Oriented Database Product
170 questions
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 ...
CommunityBot
- 1
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'...
CommunityBot
- 1
0
votes
2
answers
673
views
Explicit locks in RedShift
I'm new to AWS Redshift and I wanted to know How i can implement my use-case.
I have a Redshift table which gets lot of Inserts/Selects.
In some cases I need to delete some data from that table. I ...
CommunityBot
- 1
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 ...
CommunityBot
- 1
0
votes
1
answer
531
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 ...
CommunityBot
- 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 ...
CommunityBot
- 1
0
votes
1
answer
621
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 ...
CommunityBot
- 1
2
votes
1
answer
3k
views
Redshift- How to use previous row's calculations in current row
I am using redshift to determine projected inventory for next few week. I have receipts, current inventory and forecast.
ei= ei(previous row) + receipts - forecast
wk bi r f ei
1 100 20 ...
CommunityBot
- 1
5
votes
2
answers
5k
views
Redshift: Return rows if value exists in cell array
How do I return the rows that include a particular value in a column's cell array?
Imagine we have a table like so:
id name phone values
1 Taylor xxx.xxx.xxxx [4,6,5]
2 ...
CommunityBot
- 1
0
votes
0
answers
60
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
2
votes
1
answer
362
views
What code underlies the PG_GET_LATE_BINDING_VIEW_COLS sys information function?
We want to transition to late-binding views, but late-binding view metadata is not presented by some clients (workbench/J, Toad, pgcli) which makes it hard to discover column names in these views. AWS ...
CommunityBot
- 1
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 ...
CommunityBot
- 1
1
vote
1
answer
321
views
How to force Redshift/Postgres to aggregate nth_value?
I'm trying to retrieve the 2nd, 3rd, 4th order/transaction from an orders table using a query that looks like this:
select
customer_email,
nth_value(created_at, 1) over (partition by ...
CommunityBot
- 1
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 ...
- 119
0
votes
0
answers
55
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
7
votes
1
answer
533
views
Deduplicate SELECT statements in relational division
I have a query that does a lot of duplicate work:
SELECT visitor_id, '1'::text AS filter
FROM events
WHERE id IN (SELECT event_id FROM params
WHERE key = 'utm_campaign' AND value ...
- 182k
0
votes
1
answer
688
views
redshift drop table with + and spaces in name
I have a table that a user created which appears to be from a python variable that never interpolated.
proddb01=# \dt+ *transtable*
List of relations
schema | name | ...
- 11
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
127
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....
- 26.9k
0
votes
1
answer
479
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:
...
- 26.9k
1
vote
2
answers
90
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 ...
- 1,762
0
votes
0
answers
155
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
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]...
0
votes
1
answer
629
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, ...
- 211
4
votes
1
answer
3k
views
How do I convert cents retained as integers to dollars as decimals?
I have cent values, retained as integers, that I need to convert to dollars and show them as a decimals.
Example
A value is kept as 1925, so I need to be able to return 19.25.
This is what I had ...
- 111
0
votes
1
answer
939
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 ...
- 4,463
0
votes
1
answer
1k
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 ...
- 90.3k
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 ...
- 86
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 ...
- 1,762
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
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,762
6
votes
4
answers
9k
views
Can't DROP GROUP, cannot be dropped because some objects depend on it
RDS Version: PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3), Redshift 1.0.1161
I've been wrestling with this issue for a few days now, and all ...
- 101
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
CommunityBot
- 1
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
(
...
- 39.5k
0
votes
1
answer
653
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 ...
- 119
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),...
- 193
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? ...
CommunityBot
- 1
0
votes
1
answer
225
views
Get the Replication/Lag time of Redshift
I'm currently running RedShift with pushes of all data from our production Postgresql databases every 10 minutes or so. Periodically, the ETL process from Postgresql to Redshift gets delayed or backed ...
CommunityBot
- 1
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 ...
- 11
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 ...
- 23.6k
16
votes
2
answers
70k
views
Grant SELECT on all tables in Redshift
I am trying to assign SELECT privilege to a group in Redshift. So I created a group and a user in that group:
CREATE GROUP data_viewers;
CREATE USER <user> PASSWORD '<password>' IN GROUP ...
- 182k
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
195
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
81
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 ...
- 176
1
vote
0
answers
379
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 ...
CommunityBot
- 1
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 ...
- 26.9k
0
votes
1
answer
448
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 ...
- 20.3k