Using a SQL Query

When creating a new data set based on a direct query to a database, you can choose
an
existing SQL query or create a new SQL query. You can use either an existing or
new
query to refine the data retrieved from a database, or to combine data from multiple
tables. Using a SQL query, you can specify SQL statements in addition to any join
criteria to refine the data set. If you want to join tables only by specifying
the join
type and the fields to use to join the tables, you can use the join interface instead.
For more information about using the join interface, see Joining Tables.

You can specify a SQL query only for data sets based on SQL database data sources.

Important

If you chose a table and made any changes to the fields (for example, changing a
field name or adding a calculated field), these changes are discarded when you
switch from the table selector to the Custom SQL tool.

Creating a Custom SQL Query

Use the following procedure to create a custom SQL query for a data set.

On the next screen, you can choose to write a query with the Use custom
SQL option. Doing this opens a screen named
Enter custom SQL query, where you can type
a name for your query, and then enter the SQL. For best results,
compose the query in a SQL editor, and then paste it into this
window. After you name and enter the query, you can choose
Edit/Preview data or Confirm
query. Choose Edit/Preview data
to immediately go to data preparation. Choose Confirm
query to validate the SQL and make sure that there
are no errors.

Choose
tables

If you prefer to connect to specific tables, for Schema: contain sets of
tables, choose Select and then
choose a schema.

To prepare the data before creating an analysis, choose Edit/Preview
data to open data preparation. Use this option if you
want to join to more tables.

Otherwise, after choosing a table, choose Select.

After you choose Use SQL, the
Tables pane is renamed to Custom
SQL. You can choose this new pane to switch back to using
tables.

Note

You can explore the fields in your tables by using the
Tables pane. After you select a table, its
structure displays in the viewer. This view can be useful if you are
unfamiliar with the field names, and want to write SQL.

In some cases, Amazon QuickSight can't change a table data source into a query. In
this case, the screen doesn't display the option to switch to a custom
SQL query. To use a query instead, create a new data set that is based
on the query you want to use.

If your newly created data set doesn't appear on Your Data Sets
screen, refresh your page. Likewise, if you choose your new data set and
its dialog box has no options except Create
analysis, close this dialog box and reopen it.

Enter information for a new SQL query:

In the Custom SQL pane, choose Use
SQL.

For Custom SQL name, type a query
name.

For Custom SQL, type or paste in a
SQL query. The query must conform to the SQL syntax of the
target database engine in terms of capitalization, command
termination, and other requirements.

Note

The Custom SQL box has no query editing functionality.
It's easier to create the query that you want in your SQL
editor of choice and then paste it in.

Choose Finish. The query is processed and a sample of the query
results displays in the data preview pane.

After you have selected or created a SQL query, the
SPICE indicator updates to reflect the size of the data set returned by
the query.

Switching Back to Using a Table

To stop using a SQL query and use regular table data instead, choose
Switch to table selector in the Custom
SQL pane, and then choose a table. You can only do this with new data
sets. Once you have saved the data set to use a SQL query, you can edit the
query, but you can't switch to using a table.

Modifying Existing Queries

To update an existing data set based on a SQL query, open the
Fields pane, and choose Edit
SQL to open the SQL pane and edit the query.

Javascript is disabled or is unavailable in your browser.

To use the AWS Documentation, Javascript must be enabled. Please refer to your browser's
Help pages for instructions.