Questions tagged [redshift]
Redshift : Amazon's Column-Oriented Database Product
108
questions
0
votes
0answers
9 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
44 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
23 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
18 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
7 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
30 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
84 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
13 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
13 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
52 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 ...
0
votes
1answer
75 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
10 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
36 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
0answers
32 views
Execute SQL Task timeout setting is being ignored for Redshift ODBC Connection
When I run an SSIS SQL Task (VS 2017) against an ODBC Amazon Redshift (32 bit) connection, the timeout setting is not being respected and the Task/SQL continues to run endlessly.
0
votes
0answers
69 views
Restricting access to tables/views in pg_catalog schema in Redshift
The general advice seems to be that the access to system tables (in pg_catalog schema) should not be restricted from database users.
I couldn't find a succinct explanation exactly why or how to ...
0
votes
1answer
234 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
...
0
votes
0answers
186 views
Can't cancel query in Redshift
I have run a SQL in redshift and it hangs over 1 hour(Even doing explain on the SQL).
I tried to use cancel/pg_cancel_backend/pg_terminate_backend to cancel/terminate the session. It seemed the ...
0
votes
0answers
48 views
Redshift: information_schema.columns table takes long time to respond
The information_schema.columns table takes a long time to return results. Is this something a database performance issue. The bad thing is the query doesn't appear in the AWS console.
Any ...
1
vote
1answer
153 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 ...
0
votes
0answers
215 views
Table missing within information_schema.tables (Amazon Redshift)
We are able to SELECT, INSERT, and DELETE records without an issue for table LOG.TABLE_NAME1
I need to update the DDL but surprisingly cannot seem to find details on this table anywhere except in ...
1
vote
0answers
66 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
55 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
59 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
122 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
482 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
416 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
102 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
71 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
50 views
SQL Query: Column Name as Value
I have a Redshift table that looks like the following:
+----------------+---------------+------+
| FieldA | field1 |field2|
+----------------+---------------+------+
| ABC | ...
0
votes
2answers
64 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
63 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
626 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
1k 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 ...
2
votes
1answer
5k 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
61 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
488 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
2k 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
656 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
295 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
...
2
votes
0answers
500 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
273 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
562 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
858 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
1k 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
328 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
1answer
302 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 ...
1
vote
1answer
812 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
551 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
2k 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
499 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 ...
