Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange
Join us in building a kind, collaborative learning community via our updated Code of Conduct.

Redshift : Amazon's Column-Oriented Database Product

2
votes
0answers
11 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 ...
0
votes
1answer
16 views

Splitting array of string values into Rows Numbers Table Ref Error

I am trying to create a table that queries individual database records, but splits out values from an associated array into individual rows for the individual database record. So far I have created ...
0
votes
2answers
18 views

Redshift table level security

I have a common scenario where I need to drop and create an Amazon Redshift table or view (more often with views). There are situations where an ALTER is not going to work, which would preserve ...
1
vote
0answers
10 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 ...
1
vote
1answer
23 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 ...
0
votes
1answer
31 views

SQL Query: Column Name as Value

I have a Redshift table that looks like the following: +----------------+---------------+------+ | FieldA | field1 |field2| +----------------+---------------+------+ | ABC | ...
0
votes
2answers
41 views

What's the fastest way to find the first 2 purchases for every customer in Redshift? [duplicate]

I'm looking to find the first 2 purchases for every customer. I have a table that looks something like this: orders With columns: order_id customer_email grand_total purchase_date There are many ...
2
votes
0answers
45 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 ...
0
votes
2answers
94 views

Cons of using a star schema in a columnar database

My organisation is implementing a new data warehouse using a columnar data store (Redshift) and the performance is currently abysmal. There are many causes for this but I think the key reason is ...
2
votes
1answer
90 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 ...
0
votes
1answer
359 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....
0
votes
2answers
33 views

Can AWS Data Migration Service be used to integrate non AWS SQL server with Redshift?

I have a SQL Server 2012 hosted on a standalone machine. I want to migrate it to my AWS Redshift (already existing data warehouse). My question is wether it is possible via AWS Data migration service ...
0
votes
0answers
114 views

Getting Redshift error 1214 during copy

I have the following table in redshift: Column | Type id integer value varchar(255) I'm trying to copy in (using the datapipeline's RedshiftCopyActivity), and the data has the line 1,...
1
vote
1answer
396 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 ...
0
votes
1answer
352 views

Redshift DROP and TRUNCATE hang with no locks

I followed this procedure to check to see if a table lock is preventing a table from being deleted, but finding none. However, I am still unable to drop a table in Redshift. Are there other system ...
0
votes
1answer
112 views

Creating a staging table when the target table has autoincrement

Is it possible to create a staging table when the target table has an autoincrement ID key, and then merge it back in? I've attempted this: CREATE TEMP TABLE stage (like target); insert into ...
1
vote
0answers
107 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 ...
1
vote
1answer
121 views

Use min max value from a CTE to define interval range on a next CTE

I have one table on Redshift and in a query I'm trying to have the list of ids and the list of ids from a sequence generated. WITH missing_rows AS ( SELECT id, created_at FROM logs WHERE ...
1
vote
2answers
235 views

Is it appropriate to use a timestamp as a DISTKEY in Redshift?

I'm having a little trouble understanding how to select a DISTKEY for a table I'm working on. Consider the following table: create table test_table ( country char(2) encode zstd, ...
2
votes
2answers
565 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 ...
0
votes
1answer
626 views

Redshift table occupies exponentially more disk space in production cluster

Production Cluster details: Node Type dc1.8xlarge Nodes 25 2.56TB SSD storage per node Test Cluster details: Node Type ds2.xlarge Nodes 6 2TB HDD storage per node When same table with exactly ...
1
vote
1answer
197 views

Redshift: do with clauses /CMTs ALWAYS create temporary tables?

I like to use "with clauses" as filters: it makes my queries more "functional" in the linearish functional programming style x= a.b().c().filter(condition).sum() sense (obviously, SQL is one giant ...
0
votes
0answers
79 views

Redshift window function for trend % calculation

I'm trying to figure out how i can calculate the trend on my events table using an window function in redshift, but not sure if this is even possible. Currently we execute 2 queries on our events ...
0
votes
1answer
117 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. afsproddb01=# \dt+ *transtable* List of relations schema | name ...
0
votes
1answer
392 views

Hinting to Redshift how to distribute a join?

If I am joining between two tables, A and B, with multiple equality constraints, is there any way that I can hint to Redshift which it should use for distributing the join? For example, in: FROM A ...
3
votes
2answers
257 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): +-------------------------+------+ | ...
7
votes
3answers
1k 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 ...
-1
votes
1answer
272 views

redshift revoke privlage from super_user

I had created a redshift cluster with a dbuser named :- ateam (super_user) Now I have manually created a db user named :- bteam (new super_user) How can I revoke super_user privilege from ateam and ...
1
vote
1answer
456 views

NVARCHAR in SQLServer to VARCHAR in AWS Redshift

I am trying to move a table from SQL server to AWS Redshift. While trying to move NVARCHAR fields, I read that Redshift converts NVARCHAR to VARCHAR internally. So, a NVARCHAR(100) in SQL Server will ...
4
votes
0answers
584 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 ...
0
votes
1answer
800 views

Choosing sort and distkeys for redshift with multiple joins

I have a relatively large Redshift cluster which I am attempting to optimize via the selection of sort and dist keys. I have a recurring situation where multiple tables are joined together via an ...
1
vote
1answer
5k 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?
0
votes
1answer
267 views

Does Amazon Redshift support MVCC?

According to documentation, Amazon Redshift is derived from PostgreSQL 8. PosgreSQL 8 supports Multiversion Concurrency Control (MVCC) which is in my understanding achieved on physical level by ...
2
votes
1answer
51 views

Selecting a proper data warehouse and/or big data approach

We have about 2 TB of data distributed across literally thousand of tables in PostgreSQL 9.4.9, and we think that we should be moving the historic data to a more report-oriented and efficient solution....
1
vote
1answer
110 views

How to Create Live connection between Amazon Redshift and Amazon RDS (MySQL)

I have a transaction table in MySQL which get updated almost every second. The table has about 20 million records and counting. I have been running queries directly on this table in my app development ...
4
votes
1answer
463 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 ...
1
vote
1answer
1k 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 ...
0
votes
2answers
1k views

Convert UTC time to local time with specific format in Redshift

I have a column called created_at with data stored in UTC without timezone like : 2017-02-05 15:43:27.151 I would like to convert to an other timezone like CET in specific format like : 2017-02-08 ...
0
votes
1answer
222 views

Evaluate different EXPLAINs on Redshift

I'm trying to understand EXPLAIN on Redshift. I'm in a case were I have data like this id | user_id | created_at ---|--------------------------------- 1 | 1 | 2017-02-08 14:32:10.96 2 | 1 ...
1
vote
1answer
74 views

Count specific query occurred in Redshift

I wanted to count the number queries executed over one day. I have this working version : SELECT DATE_TRUNC('day', starttime) AS date, COUNT(*) AS COUNT FROM stl_query WHERE querytxt LIKE '...
1
vote
1answer
382 views

row count incorrectly displayed as 0 in redshift

I have a table in redshift with columns user_id and some_property, neither of which are unique. The following query returns 0: SELECT COUNT(DISTINCT user_id) FROM table WHERE user_id NOT IN (SELECT ...
0
votes
0answers
252 views

Very slow select count(*) returning 0

I have a dead simple query: select count(*) from some_table running on an Amazon Redshift database. It takes several seconds to complete, and returns 0. The plan: XN Aggregate (cost=0.88..0.88 ...
2
votes
2answers
905 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 ...
4
votes
0answers
250 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}`...
0
votes
1answer
91 views

Sync SQL Server Transaction Logs to Redshift

I am researching the plausibility of syncing SQL Server logs to an AWS Redshift data warehouse. Redshift has the COPY command to do parallel loads from S3 to Redshift already. Additionally, there are ...
0
votes
1answer
188 views

I am using Amazon REDSHIIFT and want to make my table data case insensitive?

How can I change my Tables data stored in REDSHIFT case insensitive? For example: Bell, BELL, bell in a column should return all the rows when queried.
1
vote
1answer
769 views

Unable to connect to AWS redshift database

I am trying to migrate to a new PostgreSQL database on Amazon Redshift and get the following error: Connection to jdbc:postgresql://redshift-cluster-i-created:5439/dev wasn't established. ERROR: code ...
3
votes
1answer
508 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 ...
0
votes
1answer
176 views

How do you select the rolling, most recent value for each person for each month in a range where that most recent value is less than 6 months old?

I need help writing a SQL query for Redshift (Postgres will do) likely involving WINDOW functions, PARTITIONS, LAST_VALUE and other things above my head. Users can submit survey responses at any ...
2
votes
1answer
3k 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 ...