Questions tagged with Amazon Redshift Spectrum

Questions for querying from spectrum data stored STRUCT-STRUCT-ARRAY data type. There is a table "psn_member_chnl" in spectrum with multi level STRUCT type data such as ``` struct<publish_info: struct<category: struct<svc_genre_id_list:array[svc_genre_id:bigint]> > > ``` I run a query from psn_member_chnl like bellow: ``` select a.member_chnl_id, a.character_no, a.eval_info.eval_status_type , b.track_id , c.svc_genre_id from rs_flo_poc.psn_member_chnl a, a.track_list b, a.publish_info.category.svc_genre_id_list c ``` Then error comes up as bellow: ``` SQL Error [XX000]: ERROR: "c.svc_genre_id" is of scalar type BIGINT. No further nested access is permitted. ``` Please give me any hint to solve this error.

CREATE EXTERNAL SCHEMA on redshift requires an IAM_ROLE or equivalent. I'm wondering if its possible to create this in such a way as to give redshift spectrum the ability to read data across **multiple** accounts ? Getting access to local S3 data is easy enough, getting access to one 'external' accounts S3 data is easy enough - its combining the two (or more) thats proving hard. Its beyond my capabilities with IAM.

Two regions, S3 replication configured with replica modification sync on the prefix where Hudi dataset is located. Hudi writes are exclusive to a single region. - will S3 replication maintain consistent Hudi dataset in the replicated region ? - If we move Hudi writes to the replication region (failover), will hudi dataset stay consistent in the original region, maintained by replica modification sync (failback) from region 2?

I'm running the following in RedShift query editor ``` create external schema customer_schema from data catalog database 'customer' region 'us-west-2' iam_role 'arn:aws:iam::<account-id>:role/RedshiftSpectrumRole' create external database if not exists; ``` And getting the following error: >ERROR: Unknown std exception when calling external catalog API: ----------------------------------------------- error: Not authorized to get credentials of role arn:aws:iam::<account-id>:role/RedshiftSpectrumRole code: 30000 context: query: 0 location: xen_aws_credentials_mgr.cpp:402 process: padbm aster [pid=15652] ----------------------------------------------- [ErrorId: 1-6222317d-1fb7af7e7869388e5a5866f3] The role has access to S3 and Glue, and it has been attached to the RedShift Serverless permissions. I also submitted a ticket to AWS over 4 days ago and have not heard anything back. Can someone help out? Is there something I missed? Or is this an internal AWS error? Below is the trust relationship for the role: ``` { "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Principal": { "Service": [ "redshift.amazonaws.com", "redshift-serverless.amazonaws.com" ] }, "Action": "sts:AssumeRole" } ] } ```

We are exploring usecases where we want to achieve in-place transformation and querying of S3 data lake data. We don't want to provision database and create tables (so we are not keen to consider Redshift or Athena) and we want the querying to be most cost-efficient. While we can use S3 Select to directly query S3 data, it has its own limitations such as we can query single object using S3 Select, etc. Are there any alternatives to achieve this? Please guide

Hello everyone, I discovered a problem during a Redshift Spectrum `SELECT` query that works as expected when it's ran inside the RDS (Postgres) DB. The query is like the following: ``` SELECT MIN(column) FROM table; ``` When ran inside the RDS, the result is correct, when ran via Redshift Spectrum it raises the following exception: ``` SQL Error [XX000]: ERROR: Decimal input parse error Detail: ----------------------------------------------- error: Decimal input parse error code: 25101 context: str:0.5507626528024723909068 query: 10256 location: federation_fetchers.cpp:194 process: query0_121_10256 [pid=8648] ----------------------------------------------- ``` The data type is set to `NUMERIC` in the RDS DB. Please, notice there is no `0.5507626528024723909068` value in the table. I can't find any similar question anywhere and I would be really grateful if anyone had any advice on this. Thank you in advance!

Every day, we export to S3 the snapshot of an Aurora database using [this feature](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/USER_ExportSnapshot.html). Exports are saved by RDS to S3 under a prefix that includes the export identifier. How can we have Redshift Spectrum tables always point to the latest export? I thought of two solutions but I'm not too satisfied with them and was hoping there's a better way I'm missing: * After an export is completed, modify every external table so they point to the new path * Copy the Parquet files to a "latest/" prefix which the external table always point to

Hi! I'm trying to make some data available via Spectrum to our reporting platform. I chose Spectrum because it offers lower latency to our data lake due to not needing a batched ETL process. One of the queries I have looks like this ``` with txns as (select * from spectrum_table where ...) select field1, field2, ... from txns t1 left join txns t2 on t2.id = t1.id left join txns t3 on t3.id = t1.id where... ``` Intuitively, this should cache the Spectrum query in a temp table with the CTE, and make it available to query later in query without hitting S3 a second (or third) time. However, I checked the explain plan, and with each join the number of "S3 Seq Scan"s goes up by one. So it appears to do the Spectrum scan each time the CTE is queried. Questions: 1. Is this actually happening? Or is the explain plan wrong? 2. If it is happening, what other options are there to achieve this? Other than manually creating a temp table (this will be accessed by a reporting tool, so I'd prefer to avoid allowing explicit write access or requiring multiple statements) Thanks!