Assume I have two tables a and b, each containing a series of dates: Table a: day_date 2020-1-1 2020-1-2 2020-1-3 2020-1-4 2020-1-5 2020-1-6 2020-1-7 2020-1-8 2020-1-9 2020-1-10 and table b: id some_date 0 2020-1-3 0 2020-1-6 0 2020-1-8 1 2020-1-2…
I have a my_table like this. Col2 is a string type: | Col1 | Col2 | |--------------|------------------------| | some_string |{key1:0, key2:1, key3:0}| | some_string |{key1:0, key2:0, key3:0}| | some_string |{key1:1, key2:2, key3:3}| | some_string |{key1:1, key2:1, key3:0}| I would like…
I am trying to combine stv_tbl_perm,pg_class and stl_query to get queries ran against tables with 0 rows using EXISTS. Current Query SELECT z.* FROM stl_query WHERE EXISTS ( SELECT b.relname FROM (SELECT db_id, id, name, MAX(ROWS) rows_all_dist, SUM(ROWS) "rows" FROM…
I consider a row duplicate when the combination of hash_key and load_date is repeating. I can find such rows using this CTE: with w_duplicated_rows as ( select hash_key, load_date, count(*), --row_number() over (partition by hash_key, load_date order by load_date desc)…
How can I prevent duplicate data transfer to an Amazon Redshift table using an AWS Glue job? I have a scenario where daily CSV files are added to an S3 bucket, and my Glue job, which transfers data from these…
I have a bunch of data (100 columns and around 30M rows) Im looking to copy from S3. Its crazy slow -3-4 hours to copy.I have a dist key and sort keys defined on the target table. Im wondering what…
I have a code that I have been using to get data for the last few months but when the new year rolled around, I realized that it was not populating any data cause it was not counting the weeks…
TL;DR Base table is incorrect, need to refine data using query. Final value is incorrect (slightly less) as nesting on itself leading to some records omission as it's a partial join. Detail- This is a query that I am trying…
I have data in table as below: Account Id Amount Start_date End_date 123 10 2023-11-16 17:00:00 2023-11-16 18:00:00 123 10 2023-11-16 02:00:00 2023-11-17 02:00:00 123 20 2023-11-17 17:00:00 2023-11-17 18:00:00 123 30 2023-11-18 02:00:00 2023-11-20 02:00:00 123 10 2023-11-18 17:00:00…
I want to move data from our dynamo DB to redshift, but I want to do this is real time as we are currently under a migration and still using the dynamo DB, any suggestions would be greatly appreciated. I…