How do I troubleshoot issues with joined data sources in Quick Suite?

Short description

The following scenarios can prevent you from joining data from different sources in Quick Suite:

• You can't see the Add data button and grid panel on the Edit dataset page on the Quick Suite console.
• You used geographic fields.
• You tried to create a third dataset from two Amazon datasets.
• Quick Suite 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 grid 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:

1. Open the Quick Suite 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, complete the following steps:

1. Open the dataset, 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

The join interface doesn't support geographic fields. To resolve this issue, complete the following steps:

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, complete the following steps:

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 Quick Suite.

Quick Suite ran out of join memory

When you join cross-data source datasets that have tables from different sources not stored in SPICE (Super-fast, Parallel, In-memory Calculation Engine), there's a size restriction. If more than one dataset is larger than 1 GB, then Quick Suite runs out of memory. To resolve this issue, use a custom SQL query to join them.

If you join two datasets that you store 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 Quick Suite from the table or view.

For more information, see Joining across data sources on Amazon Quick Suite.

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';

If the common column name isn't qualified by the table name or alias, then duplicate and ambiguous column errors occur. 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 Quick Sight

How do I resolve SQL exception errors with custom SQL data sources in Quick Suite?

How do I resolve "Access denied" errors when I use Athena as a data source in Quick Suite?