Questions tagged [redshift]
Redshift : Amazon's Column-Oriented Database Product
122
questions
1
vote
1answer
29 views
SQL (Redshift) SELECT COUNT from CSV column
Hi exchange community,
Problem statement and details:
I am trying to query a column that contains CSV data to produce a count of instances of each value in the table. There are a total of 14 ...
0
votes
0answers
11 views
Redshift - Why do materialized view disappear? (using Matillion)
I can create materialized views in redshift like explained in the documentation:
CREATE MATERIALIZED VIEW tickets_mv AS
select catgroup,
sum(qtysold) as sold
from category c, event e, sales s
...
1
vote
0answers
19 views
Implementing data warehouse on the cloud [closed]
I need to implement a data warehouse on the cloud for a mid-sized company. The database size will be around 200 Gb.
My options are (so far):
AWS with its database Redshift
Azure with its database ...
0
votes
1answer
33 views
In general should we avoid using ORM's with Redshift
My team is considering utilizing Hibernate/Envers for a granular point in time rollback solution for Redshift data. I have heard that using an ORM with Redshift is not a good idea. Are there ...
0
votes
0answers
13 views
Multiple GETDATE() in Redshift
I have a long (and long running, ~30min depending on load) query. It has multiple statements UNION'ed together. Most of the statements are using the GETDATE function[1].
Is the function executed ...
0
votes
0answers
9 views
Creating table with STRING and NUMBER in AWS RedShift
I am trying to create a database in RedShift so I can import from DynamoDB. My table in Dynamo contains STRING, NUMBER and BOOLEAN. I am unable to create the initial database in RedShift using:
...
0
votes
1answer
18 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 ...
0
votes
1answer
296 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 througing an below error.
Command used:
alter table t_name alter column c1 type varchar(300);
Error:
[...
1
vote
2answers
49 views
Is it better to have a CASE expression or a JOIN with a dimensional table?
I have a fact table which has a timestamp and I want to express the day of the week. I could do something like this:
SELECT CASE WHEN date_part(timestamp,'week') = 0 then 'Monday'
WHEN date_part(...
0
votes
0answers
147 views
Creating pure readonly user in redshift
I am trying to create a readonly user in Redshift. I followed the steps given in the below thread:
Grant SELECT on all tables in Redshift
I am listing the steps again for easy readability:
-- Create ...
0
votes
0answers
14 views
Multiple Writes to Same table in Redshift
I have a table in Redshift.
My Application has Multiple Writes/Reads on this Table concurrently.
How I do Make sure Reads won't wait for Writes to Commit/RollBack.
I wanted to know which Lock Mode I ...
0
votes
1answer
37 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 ...
1
vote
1answer
19 views
Data Pipeline load bulk tables in one time
I am using AWS Data Pipeline for copying my RDS MySQL Database to Redshift. I need to create separate pipeline for each table and each pipeline create new EC2 instance. (Process takes time).
Problem
...
0
votes
1answer
11 views
Redshift: Performance wise Is storing delimited values in a single column ever preferable to normalizing them out to another table?
I have been looking at increasing the performance of some tables that have been uploaded to a redshift database. One of the columns in a table has many delimited values which need to be split out and ...
0
votes
0answers
74 views
How to grant a group to another group in Redshift
I am trying to build a matrix of roles to dynamically change priorities for our users. In other database I would be doing something like:
create role role1;
create role role2;
grant role role1 to ...
0
votes
2answers
109 views
New to CTE - How to do a count after making table unique
I'm new to CTE's and usually sub query my SQL. I'm unsure how to get the results I want as I keep finding solutions that would require sub queries.
I have the below table in SQL. I'm trying to write ...
0
votes
1answer
20 views
How to join sparse events for individual users on a “filled in” date ranges in Redshift?
Let's say I have a table that has a record of every change a customer has made to their subscription for an internet service:
+-----+------------+-----------+------------+
| _id | account_id | ...
1
vote
1answer
74 views
Why did a Redshift UNLOAD increase precision of a float4 field in the output?
I created a table in AWS Redshift such as
CREATE TABLE exampleTableName (
id int identity(1,1),
accId varchar(16) encode zstd,
amount float4,
primary key(id)
)
distkey(accId)
...
0
votes
0answers
23 views
How to show users and who created them [Redshift]
I can use this query to list all Redshift users:
SELECT usesysid, usename , usecreatedb , usesuper , valuntil
FROM pg_user
But I am interested to understand which user has created each of them. Any ...
0
votes
0answers
30 views
Dimensional Modeling in Redshift - How wide to go?
I am playing with Redshift as a solution for a data warehouse I am architecting and I am trying to get my head around best practices for dimensional modeling in this case. I am using one source ...
2
votes
0answers
73 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
...
0
votes
1answer
107 views
Combine SQL query with variable
Redshift support export data to CSV by executing this SQL query.
unload ('select * from bhuvi')
to 's3://bhuvi-bucket/folder//user-data.csv'
iam_role 'arn:aws:iam::1111111:role/myredshiftrole'
...
0
votes
0answers
169 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 ...
0
votes
0answers
11 views
AWS Redshift Workload Management
I am planning an AWS Redshift (RS) data warehouse and expect to have mix of longer-term jobs (60%) and short-term jobs (40%). The plan is to use workload management (WLM) and create multiple queues to ...
0
votes
1answer
43 views
What can I do to speed up this query which aggregates over an interval?
I have a star schema database in Redshift. I'm running an aggregation on a fact table order_facts with the following relevant columns:
total - FLOAT - total order cost
payment_date - INTEGER - ...
1
vote
1answer
2k 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 ...
0
votes
0answers
88 views
Redshift/Athena: Most efficient way to cast timestamps of varying precision to date in query?
I'm receiving a stream of log files to an S3 bucket and querying this data through Amazon Athena (Redshift). Contained in the log files are timestamps with the seconds field having decimals to the ...
0
votes
0answers
39 views
how do you query for empty tables in Redshift?
I would like to perform some long overdue DB cleanup, and am trying to analyze table statistics. I'd like to:
Get a list of all empty tables
Get metadata by table relating to the last time data was ...
2
votes
1answer
177 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 ...
1
vote
1answer
416 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 ...
0
votes
1answer
23 views
Redshift is missing in target database list of AWS SCT Schema Convertion Tool
I want to convert schema of SQL Server database to Amazon Redshift using AWS SCT tool
But as target database, Redshift is not listed
When I use PostgreSQL compatible with IP address of the cluster, ...
1
vote
2answers
45 views
Count orders 12 months back from a certain month group by that month
I want to create a table with months counting orders from current month to 12 months back. I have data with a create_date and an order_id. So I need to convert the date to a format of yyyymm and count ...
0
votes
1answer
647 views
Having issue with window function COUNT counting duplicates
I have a query like so:
select
distinct(line_items.email) as email,
count(orders.order_id) over(partition by orders.order_id) as num_orders
from
line_items
inner join orders on
...
1
vote
1answer
336 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 ...
1
vote
0answers
86 views
Best Distkey and Sortkey for the tables in Redshift
I have the following tables:
orders:
id => primary key
customer_id =>
store_id =>
order_date => datetime
order_items:
id => primary key
order_id => FK
The query I usually run ...
1
vote
2answers
134 views
Automating Redshift data dump to a PostgreSQL DB every hour
GIS analyst with minimal experience of this back-end stuff but a big task at hand. I have a redshift database that changes multiple times a day. I want to use this "live" data to make web maps and I ...
1
vote
0answers
64 views
Finding the largest island size (redshift)
Current sql code will sum up all the max island sizes into one. I'm trying to only find what the max island size is.
select deviceid, grp -- min(readdate) as mn, max(readdate) as mx
from ( ...
2
votes
0answers
128 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
749 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
626 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
1answer
149 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
133 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
551 views
SQL Query: Column Name as Value
I have a Redshift table that looks like the following:
+----------------+---------------+------+
| FieldA | field1 |field2|
+----------------+---------------+------+
| ABC | ...
0
votes
2answers
72 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
67 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
886 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
2k 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 ...
3
votes
1answer
7k 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
69 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
797 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,...
