Questions tagged [redshift]
Redshift : Amazon's Column-Oriented Database Product
170 questions
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 ...
- 443
15
votes
3
answers
23k
views
Redshift table not showing up in tables for schema?
On Redshift, why doesn't my table show up in the following query? It definitely exists, as shown by the next query I run. I want a way to list all tables for a schema:
mydb=# select distinct(...
- 251
14
votes
3
answers
68k
views
How to increase column size in Redshift database tables?
In oracle, I can:
Alter table table_name
modify column_name datatype;
Is this possible in a redshift database?
- 857
13
votes
2
answers
10k
views
Alternative to EAV for dynamic fields in a star schema data warehouse
I need to support dynamic fields and values in a big datawarehouse for storing API requests log, my user case is that I need to store all API requests query string and able to perform query against ...
- 249
9
votes
6
answers
17k
views
Dimensional Modeling and ETL in Redshift
I have been researching Amazon's Redshift database as a possible future replacement for our data warehouse. My experience has always been in using dimensional modeling and Ralph Kimball's methods, so ...
- 655
7
votes
3
answers
5k
views
Storing multiple tags on analytics database
I would like to store user purchase custom tags on each transaction, example if user bought shoes then tags are "SPORTS", "NIKE", SHOES, COLOUR_BLACK, SIZE_12,..
These tags are that seller ...
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 ...
- 637
7
votes
1
answer
9k
views
Simple queries to Redshift really slow
I just started testing AWS Redshift and populated a single node with the AWS sample data.
Querying a table with 10 or ~400 rows takes around 2 seconds, uncached.
I'm not sure if I'm misunderstanding ...
- 173
6
votes
3
answers
14k
views
Is it possible to Clone a RedShift Database?
For some test and development work we would like the ability to routinely clone existing RedShift databases (all schema and all data). Something like a backup-restore.
The best I can find starts by ...
- 232
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 ...
- 61
6
votes
1
answer
2k
views
Median absolute deviation for time series outlier detection in Amazon Redshift
Context
I am tasked with trying to detect outliers in time series data in an Amazon Redshift (PostgreSQL) system. Also known as a public holiday detector around the office. The method I have been ...
- 163
5
votes
5
answers
20k
views
Drop user in redshift which has privilege on some object
I have a pretty standard problem that I can't resolve. I want to remove a user in redshift DROP USER u_A; which returns me: user "u_A" cannot be dropped because the user has a privilege on some object....
- 443
5
votes
1
answer
2k
views
Understand why rank() over doesn't fit to don't select duplicates rows
I would like to understand why I have different results
I have a table called active_transfert where I log image transfert
user_id | image_id | created_at
--------|----------|-----------
1 |1 ...
- 651
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 ...
- 51
4
votes
1
answer
28k
views
Redshift throws Value too long for character type character varying(100) even when values are within the range
I am aware what this error message Value too long for character type character varying(100) means. So I often look for the rows which cause the trouble and fix them appropriately as deemed fit by the ...
- 231
4
votes
2
answers
442
views
Finding earliest connected value over two columns
I have a, perhaps slightly esoteric, use case.
Given a table like the following:
id1 | id2 | timestamp
-----+-----+---------------------
3 | 4 | 2016-03-22 09:52:15
1 | 2 | ...
4
votes
2
answers
6k
views
Amazon Redshift - The difference between Query Slots, Concurrency and Queues?
We are building a business intelligence system, and we have a huge PostgreSQL Database (DB) where we make all the info processing, and a Redshift Data Warehouse (DWH) where we store the data and ...
- 141
4
votes
1
answer
12k
views
Storage size for varchar length in Redshift
So, a lot of databases will store the length prefix for a varchar field in 1 byte if the length is less than 255, and 2 bytes if it is more. For example, the MySQL docs say:
In contrast to CHAR, ...
- 143
4
votes
1
answer
2k
views
Redshift - Optimize Expensive Query
I have two tables in Redshift that I am trying to do a join on to get zip code demographics based on a users normalized ip address. By normalized address, I mean that it is concerted to a uniform ...
- 121
4
votes
1
answer
8k
views
Amazon Redshift "cache lookup failed for aggregate" error when using median aggregate
I recently ran into this error message and am posting an answer here so that the solution is easily searchable in the future.
Problem: The following query returns an error message when run on Amazon ...
- 171
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 ...
- 41
4
votes
0
answers
2k
views
Redshift: InternalError: cache lookup failed for relation
I have been speaking with Amazon support about this but their only solution is to "keep trying," which has not been successful.
The issue is when running our ETL we intermittently get the following ...
- 141
4
votes
0
answers
3k
views
Amazon Redshift: "*SELECT* 1" vs. volt_dt_0 vs. volt_tt_51343b6aa3bd4
I've noticed subqueries in Amazon Redshift can be represented in the explain plan in 3 separate ways:
-> XN Subquery Scan "*SELECT* 1"
-> XN Subquery Scan volt_dt_0
-> XN Seq Scan on ...
- 641
3
votes
1
answer
2k
views
Redshift DISTSTYLE ALL table takes less disk space?
(Self-migrating from StackOverflow.)
We have a fat table of exactly 591 columns in Redshift, distributed by a key.
We were unaware that fat tables chew up space - 2 blocks per column per slice, ...
- 193
3
votes
1
answer
2k
views
Managing code and deployments to Amazon Redshift
I am finding it difficult to manage and deploy schema changes to my Amazon Redshift database. Ideally, I would like is to have the code for my database under source control and use a schema ...
- 390
3
votes
1
answer
11k
views
Redshift: Table columns can be found in information_schema.columns but not in pg_catalog.pg_table_def
I create a table in Redshift. When I tried to search for the table definition, I get back results from information_schema.columns by running the following query:
select * from information_schema....
- 205
3
votes
2
answers
2k
views
Validate id sequence consistency in records in Redshift
I have a backend that log event to Redshift, for each event it generates a unique id. The id is a sequential number.
I have something like (events table):
+-------------------------+------+
| ...
- 651
3
votes
1
answer
1k
views
Best way to store, query and update 300M rows of data
I'm struggling to find a solution (preferably DBaaS) that I can rely on for storing and querying some 300M rows of data (roughly 100GB).
The data in question is pretty much numeric. There is also one ...
- 1,443
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
3
votes
1
answer
3k
views
Redshift: How to fix serializable isolation violation(1023) caused by concurrent MERGE operations?
My use case is to extract, transform and load data incrementally and in real time from x number of Lambda functions. I expect multiple Lambda functions to be running concurrently and Redshift to stay ...
- 131
3
votes
1
answer
1k
views
Diagnosing the error in a Postgresql to Redshift migration through psycopg2
I am running a job class that contains the following:
A Postgresql connection that can issue SQL statements
A Redshift connection that can do the same
an S3 connection to function as an intermediary ...
- 235
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
3
votes
0
answers
973
views
Use or include external schema in search_path on Redshift
Narrative
I have a sql script that creates a bunch of tables in a temporary schema name in Redshift. I don't want to repeat the schema name a bunch of times, so I would like to do something like the ...
- 233
3
votes
0
answers
714
views
Failed to run query: error: 1023 on Redshift
I have a lambda :
'use strict';
const config = require('./redshift_config');
const pg = require('pg');
const redshift_conn = `pg://${config.user}:${config.password}@${config.host}/${config.database}`...
- 651
3
votes
0
answers
246
views
Alternatives for storing 365TB of info
I need to upload daily CSV dumps to a blackbox of my choice, and it should be possible to run queries or create filters over the uploaded data. Daily dumps weight around 1TB and are basically id, ...
- 345
2
votes
1
answer
10k
views
How do I query INFORMATION_SCHEMA.COLUMNS on Amazon RDS?
I received a list of column names from another team. To ensure all field column names are valid inside a specific schema, I put them into a temporary table. I'm now wanting to see if they exist. When ...
- 205
2
votes
2
answers
2k
views
Redshift Spectrum CTE Union
I have a query with a CTE that uses a Spectrum table. When I try to union the CTE with itself:
WITH foo AS (
SELECT col1
FROM spectrum.bar
)
SELECT * FROM foo UNION ALL
SELECT * FROM foo;
I get ...
- 123
2
votes
2
answers
2k
views
Redshift Deep Copy Disk Space
I am trying to perform a deep copy on RS. See http://docs.aws.amazon.com/redshift/latest/dg/performing-a-deep-copy.html
I created the new/temporary table using the SAME DDL with compression settings ...
- 123
2
votes
1
answer
3k
views
How to validate data before loading it to DB
I have a situation where in I have million records in CSV file, what I need to do is to apply some business validation on certain columns of CSV and load data to DB only if particular record is valid.
...
- 21
2
votes
1
answer
72
views
Querying on multiple joined rows
I have a table called visits that is structured like so:
+----+--------------+-------------+
| id | name | visitor_id |
|----+--------------+-------------+
| 1 | event1 | 1 |...
- 637
2
votes
2
answers
15k
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:
[...
- 21
2
votes
1
answer
2k
views
Set default scheme in Redshift
I'm trying to set the default schema to a user in Redshift with alter option, because I do not want it to write to the public schema by default. I am using the following order:
alter user user_1234 ...
- 143
2
votes
1
answer
3k
views
How to change the default timezone in Amazon Redshift?
Setting a timestamp column to SYSDATE by default, stores it as UTC. Is it possible to change the timezone so SYSDATE stores dates and times to a different timezone?
So far, I've checked the SET ...
- 121
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
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 ...
2
votes
0
answers
206
views
How to perform a running count on null values that resets on non-null values
I am trying to calculate a column that performs a running count of consecutive null values, but the running count will reset upon non-null values. Currently I am trying to do it with a window function
...
- 21
2
votes
0
answers
139
views
Copy to file and transform arrays
I'm copying data from a Postgres DB on AWS RDS to a Postgres-like DB on AWS Redshift.
Varchar arrays are being exported as {foo,bar} but Redshift wants them as ["foo","bar"].
Redshift doesn't ...
- 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 ...
- 21
2
votes
0
answers
81
views
Efficient way to create daily summary table
I'm trying to create a daily summary table that captures characteristics of each client known as of every given date that my company has been in business. The challenge is that there are so many ...
- 121
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 ...
- 21