I have an array of unknown length in AWS Athena. I want to get all elements expect for the first one and concatenate into a string. I can do with a known length, but I don’t see how for unknown length. In this example: What I want is myslice_joined. I could use slice because I knew it had four elements,
Tag: amazon-web-services
Querying latest snapshot partition with Athena
I have a partitioned table with daily snapshots from from glue. When I use athena to query it queries across all partitions. Is there a way to get Athena to automatically only get the latest snapshot? Or do I have to explicitly state what partition I want to query if I want to avoid querying across all snapsh…
Amazon Athena set location to single csv file
I would like to set the location value in my Athena SQL create table statement to a single CSV file as I do not want to query every file in the path. I can set and successfully query an s3 directory (object) path and all files in that path, but not a single file. Is setting a single file as
How can I make TO_DATE in Amazon Redshift work with multiple date formats in the same column?
I have a string column with date-time values that I need to convert into dates in order to calculate a dense rank. This column can have any of the below 3 formats: My SQL Statement to calculate the dense rank is based on this date-time column that I need to convert to date in order to rank descending: However…
Redshift Running SUM challenge
Given the following data sample, I’m trying to perform a running sum() isolated to date, account_no, service. Hoping someone here can help. I started with this code: The above worked as expected but then I wanted to see if I could add in other data features such as account_name and service such that I c…
AWS Athena: How can we get integer value as string with thousand comma separator in AWS Athena
How can we show integer numbers with thousand comma separator. So, by executing the below statement select * from 1234567890 How can we get the result as 1,234,567,890 Answer You can achieve this by casting number to string and using regex: Output: _col0 1,234,567,890 123,456,789 12,345,678 1,234,567 123,456 …
SQL, extract everything before 5th comma
For example, my column “tags” have I’m trying to return everything before 5th comma. below is the result example I’ve tried below code but it’s not working. Answer You can use See the regex demo. The REGEXP_REPLACE will find the occurrence of the following pattern: ^ – star…
Redshift: Support for concurrent inserts in the same table
I have a lambda code that fires some insert queries to the same Table concurrently through redshift data api. Considering such queries will be fired concurrently, does Redshift apply a lock to the Table for each insert? Or does it allow parallel insert queries in the same table? I’m asking because postg…
Redshift table access history
I wonder are there any way to get table access history in Redshift cluster? Our cluster has a lot of tables and it is costing us a lot. I would like to discover what specific tables have not been accessed for a given period and then I would drop those tables. Are there any ways to get table access history?
Pivot and Sum in Amazon Redshift
I have a following tables table1 table2 first I’d like to join table2 as follows and then join table2 with id and pivot and sum up them I guess it is very complicated, are there any way to achieve this? In Redshift it seems that there is no way to pivot them. Thanks. Answer This looks to be a pivot