Joining data using SQL from different data schemes

Question & Answer
1

I am working on an analysis that require datasets from our own MySQL database (already connected to QS) and a dataset from Tableau (also in QS), that is, from different data schemes.

I dont have any issue joining them in QuickSight by having one dataset and adding another and using the join function. However, I dont want to do this, because the measures I create between both datas are rather complex and QuickSight is not able to load the visual (it keeps loading until it times out).
The only solution I found to get those measures to work is if I join both datasets through SQL and create all measures in SQL - I do have values that are equivalent to those from the Tableau’s data, but they are not the same and therefore not the result I want. Because of that, I could write all measures in SQL to see if they load in the view and it works.

Because I still need the values from the Tableau’s data scheme, I need to join this data with the other data through SQL, but I haven’t been able to do that.

Is it possible to do it?

image
image999Ă—427 33.9 KB

image
image763Ă—393 30 KB

2

Hi @Alexandre_Kniepert - Is your both data sets are different schema within same instance of MYSQL. Then you can give the select privilege to the use by which your are connecting the first data source and access the second data source in the same sql as well ( by giving the schema. table name). I have not tested it but theoretically it should work.

Regards - Sanjeeb

1 Like
3

Hi,

I am not sure how to test it. I know I can add different tables from the same instance of MySQL, I actually do this on this query, but i don’t know how I would work on this particular one

this one is:

“SELECT *
FROM
ci_inputs.ci_entry_metadata”

if I insert this in my sql code I get an error - Amazon QS cant find the query schema. verify the schema specified in the dataset or in the custom SQL.

I also tried what you said:

"SELECT *

FROM

siphon_ci_entry.ci_inputs.ci_entry_metadata"

but it still does not work. here I get
“Your database generated a SQL exception. This can be caused by query timeouts, resource constraints, unexpected DDL alterations before or during a query, and other database errors. Check your database settings and your query, and try again”

4

Hi @Alexandre_Kniepert - Ok, there is a work around solution ( which is not good but you can try), you can create a view in the first schema which will reference to other schema and the view definition is the select statement. Then when you run your custom sql both tables and view will be in one schema and you should not face any sql error.

Regards - Sanjeeb

5

Hi Sanjeeb, I did not quite follow what you mean. how do I create a view in one to reference another?

6

Hi @Alexandre_Kniepert - You can create a view in database with the exact select statement that you are trying run via QuickSight. The sql statement will be like
create or replace view <> as <>

Ensure your view should be in the same schema where you have access to the tables from QuickSight.

Regards - Sanjeeb