https://www.gooddata.com/ logo
Join Slack
Powered by
Hello guys, I'm trying to connect to redshift as d...
# gd-beginners
a
Hello guys, I'm trying to connect to redshift as data source, it's working fine when connecting to internal redshift schema, but I got Connection failed! Connection validation failed when trying to connect to external schema any advice please?
j
Hi Ahmad, I would suggest to check that your user has access rights to the datasource. Could you please check this on your end?
a
Thanks Joseph, I checked it and I apply the access for the user that I'm using to the external schema in redshift, but still I got 
Connection validation failed
j
a
Hi, to clarify my problem, I have a success connection to redshift and I connect to test schema that created in redhsift but when trying to connect to external schema I got 
connection validation faild
and when trying to use a different SQL tool to connect the redshift, its working properly and I can see the external schema and select data
j
Could you provide the workspace ID you are working with and a possible screenshot of where you are getting the error?
a
pdwq36dww3awc4wxokjjx8pxzstyj6zc
j
Hey Ahmad, sorry for the delay on this. It’s still a bit tough to narrow down the exact error in the logs. It either appears to be some permission/authentication error, or the schema was incorrectly set in the search_path parameter. Could you perhaps try again and provide an approximate timestamp of the attempt?
a
I test the connection right now so around 07:42 PM GMT time
with database username gooddata
j
Hi Ahmad, I can see the following in our logs: [Amazon](500310) Invalid operation: External schema “inventory” cannot be set in search_path;
a
Thank Joseph, as we agree we will try to check what we can do to resolve this issue
a
Hello @Ahmad Mouaness, Andrey from the GoodData’s Product team here. How are you doing? 🙂 Unfortunately our data integration does not support the external schemas and tables currently. Would you be open to talk to us about your use case and the reason why you utilise the external tables? That would help us understand your needs and context better. Can you please describe your setup and motivation? Many thanks!
a
Hello @Andrey Skripnikov, Thanks for your support
I have a separate databases RDS for online transaction for quick response and fast writs and for data warehouse Redshift for analysis and large aggregations so I connect the redshift to RDS using External DataSource, the problem is as you told us that we can't connect to external schema cause not supported and we can lose some functionality, Next, I create Internal schema with VIEW that read from external table in the external schema and again they didn't appeared in the GD Datasource
a
Thanks, Ahmad! Makes perfect sense and I understand it much better now. I will take it for a discussion with the engineering team and hope to come back to you soon 🙂
Ahmad, We have tried a workaround with a setup similar to yours creating a VIEW in the Redshift pointing to a VIEW or TABLE in the RDS using an external schema and it seems to work.
Copy code
CREATE VIEW thong_test.ex_mysql_view AS
   SELECT employee_id FROM thong_ex_mysql.salary
   WITH NO SCHEMA BINDING;
The 
thong_test
is regular schema in Redshift 
thong_ex_mysql
is external schema point to RDS mysql database. The datasource connect to schema 
thong_test
. We can create model, run data loads (ADDv2) from the view 
ex_mysql_view
Please check the type to be exactly view or table. In the data source properties, the internal schema of the Redshift.
a
Hi Again, yes same case what do you mean by "the type view or table. in the datasource properties ..."
I create the view in the internal database from the external table
Copy code
CREATE VIEW facility.client_characteristic_translation AS SELECT * FROM facility_ex.client_characteristic_translation with no schema binding;
image.png
it's a table in the external schema RDS
a
The database part looks good to me. Then it must be something in the networking or IAM. The command to create external schema we used:
Copy code
CREATE EXTERNAL SCHEMA IF NOT EXISTS thong_ex_mysql
FROM MYSQL
DATABASE 'test'
URI 'something'
IAM_ROLE 'arn:aws:iam::something'
SECRET_ARN 'arn:aws:secretsmanager:us-east-1:something';
Open in Slack
PreviousNext
Powered by