All Questions
Tagged with amazon-redshift mysql
177 questions
2
votes
1
answer
59
views
Yet another percentage question in SQL / redshift
Here is where I am now....
table1 looks like this (yes like most posts here, this is a gross simplification of the actual tables)
userID
type
12345
small
67893
medium
32389
small
88733
medium
...
- 21
0
votes
0
answers
46
views
Tombstones per Update query for redshift sink connector?
Goal: Sync data between MySQL and Redshift via redshift sink connector and debezium cdc source connector
Exploring Redshift Sink Connector
We're able to see inserts and deletes queries in mysql being ...
0
votes
1
answer
934
views
Boto 3 Redshift client execute_statement lagging
I'm trying to copy some data over from MySQL to RS. The table is 650,000-ish rows, so i'm doing it in batches of 10,000 or so and also using python's multiprocessing module. The first few iterations ...
- 1,689
0
votes
0
answers
88
views
federated mysql query with a reserved word column name fails to run in Redhsift
I have a remote connection into a mysql database from Redshift. Mysql table has a column year_month which is a reserved word in mysql and since this table is already populated and applications are ...
- 39
0
votes
0
answers
40
views
How to get top product by quantity with multiple group by
I have raw data something like this:
date
id
age
state
gender
transaction
product
product type
quantity
1/1/2023
a
25
johor
male
12ab
apple
fruit
12
1/1/2023
b
30
melaka
female
98jh
pen
stationery
...
- 41
1
vote
1
answer
165
views
Extract table name from sql query using sql query?
How to extract table name from any SQL query, using SQL query?
I have solution using Python SQL Parse library, but I need it using SQL query.
- 11
0
votes
0
answers
19
views
How to handle variables used in mysql to convert the query into Redshift
I have a query to convert from MySQL compatible to Amazon Redshift compatible:
FROM abc
INNER JOIN def ON abc.ledger_id = def.ledger_id
INNER JOIN (
...
0
votes
2
answers
116
views
Can't convert this query function to Amazon Redshift
I'm trying to migrate/create functions from a MySQL Database to Amazon Redshift. I tried to use some tools to convert MySQL to Redshift but this line isn't working:
DECLARE saldoAtual, saldoInicial, ...
1
vote
1
answer
113
views
How to use variables column name in where clause MySQL?
In this query:
SELECT *
FROM general_settings AS general_settings
JOIN settings_attribute AS settings_attribute ON settings_attribute.id = general_settings.settings_attribute_id
JOIN user_settings AS ...
- 49
1
vote
0
answers
316
views
Replicating a Redshift table in AWS Aurora RDS MySQL
I have a use case where Redshift is being used as a data lake where raw data from multiple tables from various databases owned by different services are dumped. There are certain tools that run ...
- 65
1
vote
1
answer
56
views
How to find the dependency that brought another dependency in MySQL or Redshift
There is two tables:
dependency_permission table:
id
dependency_permission_id
2
1
4
2
user_permission table:
id
user_id
permission_id
1
11111
1
2
22222
4
3
22222
2
4
11111
2
5
33333
2
I ...
- 49
0
votes
1
answer
38
views
MySQL --> Redshift Incremental Backup
Is it possible to set up Redshift (or Lake Formation, or something else) to be a replica of a MySQL RDS with a reasonable delay? (minutes or hours)
It seems like your only options are:
do a full ...
- 1,098
0
votes
0
answers
78
views
How to structure data for heavy reports in MySQL?
I am using a MySQL database for my SaaS application. The structure is mostly around the business using our platform. So we have the following tables:
business
contacts - contains business id
sales - ...
0
votes
2
answers
729
views
Newly created tables are not being migrated to Redshift in dms
I have set up a DMS with RDS MYSQL as source endpoint and REDSHIFT as target endpoint with "full and CDC".
setup is working fine and even update, delete stmts are being replicated to ...
- 27
-1
votes
1
answer
54
views
Select SQL Column
I have column name user.phonenumber, and I want to apply a select query onto it.
like select user.phonenumber from humans;
SQL error: ERROR: relation "user" does not exist [ErrorId: 1-...
- 340
-2
votes
1
answer
371
views
SQL JOIN and SUM function causes huge number differences
I've trying to get the sum of each customer on every month.
However, whenever I join the tables with the calendar table, I get a very high number.
The order of the sales are the same.. so I think the ...
0
votes
0
answers
30
views
How should I start with a large amount of data with limited computation?
I have an assignment in which there are various .csv files containing over 5-8GB of data.
My local computer has only 16GB RAM, and I have a task where I have to create an SQL query out of the files ...
1
vote
2
answers
563
views
Redshift design or configuration issue? - My Redshift datawarehouse seems much slower than my mysql database
I have a Redshift datawarehouse that is pulling data in from multiple sources.
One is my from MySQL and the others are some cloud based databases that get pulled in.
When querying in redshift, the ...
- 11
0
votes
1
answer
61
views
MySQL Dynamic assignment of month based on current date
Hope you are doing well..I am trying to convert a plan table as below
Input
Segment | Model |FC1 |FC2 |FC3 |FC4 |FC5 | FC6 | FC7 | FC8 | FC9 | FC10 |...
- 411
1
vote
2
answers
3k
views
Redshift SQL Query Between Current Date and 7 days ago
I've been trying to filter the data for the last X number of days.
All these columns work as standalone results when I remove the time filter from the where clause.
I keep on getting the error of no ...
- 11
0
votes
1
answer
2k
views
Frame clause should not be specified for ranking window functions
I am getting this error message "Frame clause should not be specified for ranking window functions" when trying to run this code for a temp table in redshift:
CREATE TEMP TABLE ...
0
votes
1
answer
2k
views
Getting a schema does not exist error when referencing a table alias
When I try to run this below query joining two tables, I get the error:
schema "u" does not exist.
Where am I going wrong?
SELECT
u.delivery_station_code AS dim_location,
u....
1
vote
1
answer
301
views
Count distinct value and group by another value in the last 24 hours SQL Redshift
I would like to do a count of B in the last 24 hours for grouped by date and A.
sample table:
| DATE | A | B |
------------------------------------------...
0
votes
0
answers
72
views
How to load timestamp properly to Amazon Redshift
I have a column which contains timestamp in format like this 2017-01-01T00:00:00.000Z.
But I cannot import the column to the table using
full_date timestamp in SQL create table command.
The error I ...
- 615
0
votes
0
answers
850
views
ERROR: Error converting text to date; Error while executing the query
I have connected Amazon Redshift as a live connection to Tableau Desktop. Table I am connecting has a date field in the format '2021-04-12 11:18:05' and also several rows have '0'. Whenever I drag ...
- 473
0
votes
1
answer
67
views
How to create row data for a date range with SQL
The table looks like this:
---+------------+------------+---------
id | start_date | end_date | amount |
---+------------+------------+---------
1 | 2021-01-01 | 2021-01-07 | 100 |
---+----------...
- 127
5
votes
2
answers
898
views
error: Table may not exist or is unsupported relation type code: 25350 context: query: 0 location: mysqlclient.cpp:255 process: padbmaster [pid=15091]
We are trying to create a AWS Redshift EXTERNAL_SCHEMA from MySQL RDS but running into a strange problem. We followed the AWS instruction to create a Secret in Secret Manager with the RDS credentials ...
- 51
1
vote
0
answers
60
views
Redshift table uses 75GB of disk while the equivalent table on Mysql uses 40GB
The below query returns 75GB on redshift while on mysql the equivalent query returns 40GB.
select size from svv_table_info where "table"='table_name'
I always assumed redshift saves on disk ...
- 13
2
votes
2
answers
1k
views
Amazon Redshift Create External Schema MySQL not enabled
We are planning to use Amazon Redshift and planning to use Federated Queries to connect and insert data from our Aurora RDS (MySQL, Provisioned). I followed this documentation to setup the secrets ...
- 25
-1
votes
2
answers
71
views
How do I take the minimum value from another row in SQL?
Apologies for the rather ambiguous title, I couldn't put it in a better way.
I have the following records in my output.
route_id
date
employee_id
stop_type
vehicle_stop_number
enter_time
1
2021-06-...
1
vote
5
answers
4k
views
Convert comma delimited values in a column into rows
I would like to convert comma-delimited values into rows in Redshift
For example:
store |location |products
-----------------------------
1 |New York |fruit, drinks, candy...
The desired output ...
- 399
1
vote
1
answer
353
views
Redshift Alter table command returns `Target table and source table attributes don't match.`
I have an airflow pipeline which creates a staging table from an existing table, loads data in it from a csv, and then the following alter command is executed.
ALTER TABLE "schema"."...
- 35
0
votes
1
answer
137
views
pandas.io.sql: syntax error at or near "top"
sql = """SELECT * FROM "%s" where 1=1;""" % table_name
df = sqlio.read_sql_query(sql, conn)
This snippet works fine. Now I want to modify the command such ...
user13067694
0
votes
1
answer
956
views
Redshift creating a new column with static string error
How my query is implemented i use a statement like SELECT 'ChickenMongoose' as animal to create a new column with the static word of "ChickenMongoose" However I get this error from redshift ...
- 263
0
votes
0
answers
70
views
Is it possible to print both the data and count of the data in SQL query?
I have a huge SQL query with multiple sub-tables (sub-table output is not retained) that is generating a final output in a specific format (format is fixed as it is expected by another service). I ...
- 39
1
vote
2
answers
45
views
Find difference between SQL tables
Can anyone help me out with syntax to find difference the between two similar tables:
Table 1:
Table 2:
I need the difference to be in absolute values like table 1 - table2 = values as output
0
votes
0
answers
153
views
Get rid of the group by variable in a case expression Redshift
I am migrating code from MySQL syntax to Amazon Redshift and I have an issue with the group by .
So, a field in the code:
,CASE WHEN condition NOT IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15) THEN 0 ELSE ...
- 27
0
votes
1
answer
218
views
How to find the next sequence of a particular event based on timestamp in SQL?
I have 2 tables:- views_tbl table (page views) and play_tbl table (which records how long a customer has play a particular video). The nowPlaying page_type from views_tbl will have the same timestamp ...
- 667
0
votes
2
answers
958
views
AWS DMS "Unsupported or commented out DDL" when CDC is enabled on MySQL source
I am running an AWS Data Migration (DMS) job from RDS MySQL to a Redshift cluster, I have enabled detailed cloudwatch logs to capture the errors that are happening. I'm getting errors when insert and ...
- 323
0
votes
0
answers
123
views
Query across mysql and redshift different database and different servers
I have a MySQL in local linux server I want to join and query with amazon redshift. Any possibilities to querying across MySQL and redshift?
2
votes
1
answer
269
views
Which AWS service to use for my large MySQL table? [closed]
I currently have 1 big app using a AWS RDS MySQL database. All my tables are small, but there is one large table with over 12M rows that will keep growing, that i'd like to separate into something ...
0
votes
3
answers
1k
views
Transform/Convert Scientific Notation [From Varchar to Decimal/Numeric] in SQL
I have a table where one of columns carries values as below. The datatype of the column is in VARCHAR. Now, for the sake of customer experience, I have to convert the below values to actual non-...
- 311
1
vote
0
answers
134
views
Running a cross-regional query in Amazon RDS with blacklisted schemas
I have a set of Amazon RDS instances spread across multiple regions. Each RDS instance has a similar set of tables prefixed in a predictable way.
For example, I have the database for 50 universities ...
- 995
0
votes
1
answer
324
views
MYSQL 8.0.19 (ON PREMISE) as source in AWS DMS?
Can someone please confirm if my on premise MYSQL 8.0.19 instance can be used as source in Aws Database migration service ,with target as aws redshift.
The articles I read show only MySQL versions 5.5,...
-3
votes
3
answers
57
views
Mysql group by a column and get the rows only with the maximum value of the third column
I have managed to construct a table such as below from my data:
How can I get the maximum of the third column B for each beam number? I want to get rid of other rows. Any help is appreciated.
My ...
- 27
1
vote
1
answer
376
views
Replication pipeline to replicate data from MySql RDS to Redshift
My problem is here to create a replication pipeline that replicates tables and data from MySql RDS to Redshift and I cannot use any managed service. Also, any new updates in RDS should be replicated ...
- 11
2
votes
0
answers
286
views
Convert SQL Query types to Python Types
I want to convert the data types of the results of a SQL query run using create_engine from SQLAlchemy to a Python object, save it as a CSV and than load into another SQL database, which may use a ...
0
votes
2
answers
579
views
Redshift Correlated Subquery error internal
I have a query in MYSQL which count products from specific vendor with product_status like Live, Pause, soldout, Partial-Soldout etc. Query include Subquery but works perfect in Mysql. For Redshift (...
1
vote
1
answer
134
views
Correlated MySQL subquery to Postgre
MySQL support Correlated Subqueries and also working properly to fecth records from my db ...but i need same query to run on AWS Redshift to bring same records but it gives error of This type of ...
0
votes
2
answers
2k
views
How Coalesce function deals with datatype
I am new to COALESCE function in REDSHIFT. I ran below four queries in mysql and Redshift.
1st and 2nd query executed as expected in both mysql and redshift. But for 3rd and 4th query I am getting ...
- 345