I want to join data sources in Amazon QuickSight, but I encountered issues.
Short description
The following are common scenarios that can prevent you to join data from different sources in Amazon QuickSight:
- You can't see the Add data button and grid panel on the Edit dataset page.
- You used geographic fields.
- You tried to create a third dataset from two Amazon datasets.
- QuickSight ran out of join memory.
- You get a duplicate column or ambiguous column error.
Resolution
You can't see the Add data and grid panel on the Edit dataset page
If you can't see the Add data button and edit panel, then you must request access to the data source from the dataset owner.
To share a dataset that you own with other users, complete the following steps:
- Open the Amazon QuickSight console.
- Choose Datasets, and then choose New dataset.
- Select the dataset that you want to share.
- Choose Share data source, and then choose Invite users.
- Enter the username and required permission.
- Choose Share.
To add more data to the dataset, complete the following steps:
- Open the dataset, and then choose Edit dataset.
- On the Data preparation page, choose Add data.
- Choose how you want to add the data. You can add it from the dataset, data source, or upload a file.
Note: You can also choose Use Custom SQL to open the query editor and then write a query for the SQL data source.
- Choose the red dots to configure the join.
- Select your preferred join type: Inner, Left, Right, or Full.
- Choose Apply.
You used geographic field
Geographical fields aren't supported in the join interface. To resolve this issue, complete the following steps:
- Change the data type from Geospatial to String.
- Apply your preferred join type.
- Return to the dataset page, and select the dataset.
- Choose Use in a new Dataset to create a new child dataset in the parent dataset.
- Change the field type back to Geospatial.
- Choose Save.
For more information, see Adding geospatial data.
You tried to create a third dataset from two datasets
You can't join two datasets to create a third dataset. However, for Amazon Athena datasets, complete the following steps:
- Write a query in Athena to join two tables, and then create a view.
- Create an Athena data source on the view.
- Write a custom SQL, and then use the new Athena data source to create a dataset in QuickSight.
QuickSight ran out of join memory
To join cross-datasource datasets that have tables from different sources not stored in SPICE, only one of the datasets can be larger than 1 GB. If more than one dataset is larger than 1 GB, then QuickSight runs out of memory. To resolve this issue, use a custom SQL query to join them.
If you join two datasets that are stored in SPICE, then the combined size of all secondary tables in the join can't exceed 20GB. For more information, see Joining data.
If you have large table joins, then it's a best practice to first run a join conditions query at the database. Then, create a table or view, and finally create the dataset in QuickSight from the table or view.
For more information, see Joining across data sources on Amazon QuickSight.
You get a duplicate column or ambiguous column error
You receive the following duplicate column error:
"ERROR - Duplicate column name 'column name' "
The following example query results in a duplicate column error:
select * from schema.sales, schema.date where sales.dateid = date.dateid;
You receive the following ambiguous column error:
"ERROR - column reference "dateid" is ambiguous"
The following example query results in an ambiguous column error:
select sid, dateid from schema.sales, schema.date, schema.users where sales.sid = users.userid and dateid = dateid and year = 2010 and city = 'dallas';
Duplicate and ambiguous column errors occur when the common column name isn't qualified by the table name or alias. To resolve duplicate and ambiguous column errors, specify the joined column name, with the table name or alias as the qualifier.
Related information
Data source connectivity issues for Amazon QuickSight
How do I resolve SQL exception errors with custom SQL data sources in QuickSight?
How do I resolve "Access denied" errors when using Athena as a data source in QuickSight?