Questions tagged [redshift]
Redshift : Amazon's Column-Oriented Database Product
172
questions
7
votes
1
answer
498
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
1
vote
0
answers
171
views
Single Redshift table vs several mysql/ pgsql (on RDS) tables for small (200 tables * 20 columns * 1 million) monthly aggregation data
We're providing SaaS Data Analytics Dashboard for around 200 clients.
We're using Amazon Redshift for storing data in in around 5 tables.
We're getting around 30 million hits per day.
Currently our ...
- 111
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
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
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
1
vote
0
answers
590
views
Does psql print command results to standard error?
I am running COPY command through psql 9.1.15 as a cronjob to append data in csv file to table1:
psql -q -h $DB_HOST -d $DB_NAME -p $DB_PORT -U $DB_USER <<EOF
copy table1
from '${BUCKET}/${...
- 111
1
vote
1
answer
118
views
What difference between these 2 SQL styles?
If I have a simple table named: conn_log in my Redshift/Postgresql
In the performance of view, what difference between these two commands ?
select t1.wifi_name, (t2.sconn*100)::numeric/t1.ttconn
...
- 13
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
0
votes
1
answer
2k
views
Issue using COPY command with AWS Redshift
I am trying to copy a CSV file from my PC, which has SSH running, to AWS Redshift. I have copied the Redshfit public key to authorized_keys for the appropriate user.
copy contact (id,name)
from 's3:/...
- 171
15
votes
3
answers
22k
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
3
votes
0
answers
245
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
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
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
0
votes
2
answers
1k
views
Whats the best practice when using Amazon Redshift as a cache
I have tested Amazon Redshift as a cache. So I save data by a key. But with the time the cache becomes slower and slower. Once I do a VACCUM on the database it get backs to the speed. But should i run ...
- 117
-1
votes
1
answer
391
views
Why some queries hang while others work
We have our redshift cluster due to some strange reasons it started to be slow. And in the same time if I just query something very simple it will give me results for some tables and will never return ...
- 117
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 ...
- 225
2
votes
0
answers
566
views
Amazon Redshift VACUUM error
I'm trying to run vacuum delete only on a table with big amount of columns (800+) and 4 million rows.
I know it's recommended to use deep copy instead, but still am wondering if running vacuum is ...
- 319
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,423
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
0
votes
0
answers
17
views
In Amazon Redshift, running GRANT as a superuser does not grant privileges on table to a regular user [duplicate]
I'm trying to run a query in Amazon Redshift, but I keep getting the error
permission denied for relation mytable
As far as I understand, this is a privileges issue.
According to this question:
...
- 123
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
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