Questions tagged [redshift]
Redshift : Amazon's Column-Oriented Database Product
140
questions
0
votes
0answers
24 views
Nested WITH statements
I got a query of this form:
WITH a as (WITH b as (
SELECT ROW_NUMBER() OVER(PARTITION BY o.cust_id
ORDER BY
...) as ord_index
o.a,
o.b
FROM main."order" as o
...
0
votes
1answer
29 views
Add a list of names to a generated series
I generated a date series this way:
SELECT generate_series(date '2020-01-01', date '2021-01-21', '1 day');
Additionally I have a list with name (Max Meyer, Anna Smith, Peter Gardner).
How could I add ...
1
vote
1answer
29 views
Redshift - Insert data into a temp table with a condition being column doc_id should be unique and not null
I'm trying to create a temp table and insert data into it in Redshift.
My goal is to create a single record for unique doc_id WHERE doc_id IS NOT NULL.
Here's my code:
-- Creating temp table to load ...
0
votes
1answer
16 views
Redshift postgresql time series data index DISTKEY and SORTKEY
I have around 6 billion rows of data to move to a redshift DB from MSSQL. The data will have three columns.
I plan to move the data one tag at a time as there are 4 years of history at one-minute ...
0
votes
1answer
24 views
missing database from datname [closed]
running select datname FROM pg_database gives me a partial list. I'm missing there the db aa.
How is that be? How can I sort it?
all=# \dt aa.*
List of relations
schema | name | ...
0
votes
0answers
12 views
Using Redshift variable in COPY command
I'm trying to use the variable "key" inside my "COPY FROM" command below to populate a Redshift table. When I use the code below I get a syntax error as the "COPY FROM" ...
1
vote
0answers
13 views
Prevent Certain Types of Queries (Redshift)
Some IDEs will allow users to "peak" at data by hitting some button in a menu, and under the hood the IDE performs a select * without a where clause. This can be a problem because our ...
0
votes
0answers
21 views
SSIS export table with datetime column from SQL Server to Postgres / Redshift fails with “datetime field overflow”
I'm trying to execute a simple export task to move a table stored in SQL Server to a table stored in Amazon Redshift.
The table contains a column that is of type DATETIME in SQL Server, and the column ...
0
votes
1answer
25 views
Monthly summary of active users based on a start/end date roster
I have a table that dictates who was on a project, from a startdate to an enddate. I'd like some help writing a query that will return the number of "active" users at the end of every month, ...
1
vote
0answers
145 views
Does RedShift support computed columns?
In PostgreSQL I can do this:
CREATE TABLE Test(
Effective_Date timestamp NULL,
Max_Age_Date timestamp generated always as dateadd(month, 5, Effective_Date) stored
);
How do I create a computed /...
0
votes
0answers
15 views
How to represent Hierarchical data in Redshift when using OBT denormalised table design
We are loading a fairly simple dataset with 50-60 columns into Redshift in a flat One Big Table denormalised design (with just time dimension and one other split away). It will represent an ...
1
vote
1answer
17 views
User has more privileges in redshift than shown by relevant query
Working in Redshift
dev=# SELECT table_catalog, table_schema, table_name, privilege_type
FROM information_schema.table_privileges
WHERE grantee = 'myser';
table_catalog | table_schema | table_name ...
0
votes
1answer
119 views
Include single quote string in redshift dynamic SQL
I have function named
Schema.func_date(date,varchar)
I wanna do like this(I am using below statement inside Store proc)
Execute 'Select *, ID as Main_Id, ' ||
'schema.func_date(quote_literal(...
0
votes
1answer
18 views
Using caret (^) for pattern list matching negation in SIMILAR TO and ~ queries in Redshift
I'm quite new to using POSIX regex in Redshift. I honestly think that this pattern must be compatible.
[^A-Za-z0-9\\.\\,-]+
However, as I've tried a query like this
SELECT * FROM my_table WHERE ...
2
votes
0answers
32 views
Why late materialization is not triggered for insert queries?
Assume I have table t with columns c1,c2,c3. (not so many records, ~10k) and an empty table tmp_table with same structure.
Talking about columnar store, I want to understand when does it make sense to ...
0
votes
1answer
39 views
Export mutliple query output into one excel sheet using Redshift and SQL Developer
I am connected to Redshift from SQL Developer 19.2.1.
I have a requirement to export multiple query output into a single excel sheet, one query output should be in one Tab. Please advise on how to ...
0
votes
0answers
14 views
How to join on a column that includes the ID but has additional strings (Redshift)
I have 2 tables with the following 2 columns that I would like to use as the key for my join: a column that has the ID of the call and I have another one which is the link to the call, containing the ...
1
vote
1answer
40 views
How can Amazon Redshift be built based on PostgreSQL if Postgres is row-based?
According to Amazon, Redshift is based on PostgreSQL, and is a column-oriented database management system. This looks to me as a very self-conflicting statement, isn't Postgres a row-oriented database?...
0
votes
2answers
33 views
How to use SQL CASE result to compare a column and get another case result
I'm trying to compare my case result (desirecountry) with an existing column (so.country_name) to get another case result (Final_Country_Map_Name):
SELECT subs."user email", subs."...
1
vote
1answer
433 views
Calculate time difference in minutes
I am Amazon Redshift. I tried to calculate the time difference between two time stamp columns in minutes, but the timestampdiff function does not work on it. The error is telling me that minute column ...
0
votes
0answers
21 views
Can Redshift process CTE / with chains without making interim tables?
If you are careful about structuring a CTE chain, is Redshift capable of analyzing it and determining that interim results are being used in a linear fashion? If so, it could drop any interim tables ...
0
votes
0answers
11 views
In Redshift how can I create an user which which has only user creation privileges?
The primary requirement is to automatically create or alter users based on user information stored in a table. I am planning to write a stored procedure / use Databricks notebook to do the automation. ...
0
votes
2answers
72 views
SQL join on col1 if present else use col2
There are two tables T1(col1) and T2(col1, col2) which needs to be joined. But T2 may have col1 as null in which case col2 can be used as backup.
What I want is either join on T1.col1 = T2.col1 or T1....
0
votes
0answers
12 views
Redshift using back slashes in a like statement causing it to fail
So i am attempting to filter a query on a file path field with a like statement using the below
'%text\Text\text%' this returns no results
'%text_Text_Text%' this returns the expected results
To ...
0
votes
1answer
240 views
Substitute DISTINCT ON for query transformation (Postgresql to Redshift)
I need to transform my Postgresql query to Redshift and I working with the following data, here is an excerpt:
In my final output, I would like only to keep rows with the highest rank (in green). And ...
1
vote
1answer
108 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 ...
1
vote
0answers
24 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
228 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
38 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
1answer
331 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
3k 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
57 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
36 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
2answers
111 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
53 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
14 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
131 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
138 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
32 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
205 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)
...
2
votes
0answers
96 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
489 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'
...
3
votes
0answers
416 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
1answer
72 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
8k 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 ...
2
votes
2answers
463 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
715 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
37 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
97 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
1k 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
...
