How do I troubleshoot issues with joined data sources in QuickSight?

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.

If you own the dataset and want to share it with other users, then complete the following steps:

1. Open the Amazon QuickSight console.
2. Choose Datasets, and then choose New dataset.
3. Select the dataset that you want to share.
4. Choose Share data source, and then choose Invite users.
5. Enter the username and required permission.
6. Choose Share.

To add more data to the dataset:

1. Open the dataset that you want to add data to, and then choose Edit dataset.
2. On the Data preparation page, choose Add data.
3. 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.
4. Choose the red dots to configure the join.
5. Select your preferred join type: Inner, Left, Right, or Full.
6. Choose Apply.

You used geographic fields

Geographical fields aren't supported in the join interface. To resolve this issue, you can:

1. Change the data type from Geospatial to String.
2. Apply your preferred join type.
3. Return to the dataset page, and select the dataset.
4. Choose Use in a new Dataset to create a new child dataset in the parent dataset.
5. Change the field type back to Geospatial.
6. 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, you can do the following:

1. Write a query in Athena to join two tables, and then create a view.
2. Create an Athena data source on the view.
3. Write a custom SQL, and then use the new Athena data source to create a dataset in QuickSight.

QuickSight ran out of join memory

When you join datasets, one of the datasets must be within 1 GB in size. 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 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?