Menu
Amazon QuickSight
User Guide

Creating a Database Data Set

The following procedures walk you through connecting to database data sources and creating data sets. Use Creating a Data Set from an Autodiscovered Amazon Redshift Cluster or Amazon RDS Instance to create data sets from AWS data sources that your Amazon QuickSight account autodiscovered, or use Creating a Data Set Using a Database That's Not Autodiscovered to create data sets from any other database data sources.

Creating a Data Set from an Autodiscovered Amazon Redshift Cluster or Amazon RDS Instance

Use the following procedure to create a connection to an autodiscovered AWS data source.

  1. Check Data Source Limits to make sure that your target table or query doesn't exceed data source limits.

  2. Confirm that the database credentials you plan to use have appropriate permissions as described in Required Permissions for Database Credentials.

  3. Make sure you have configured the cluster or instance for Amazon QuickSight access by following the instructions in Network and Database Configuration Requirements.

  4. On the Amazon QuickSight start page, choose Manage data.

  5. On the Your Data Sets page, choose New data set.

  6. In the FROM NEW DATA SOURCES section of the Create a Data Set page, choose either the RDS or the Redshift Auto-discovered icon, depending on the AWS service you want to connect to.

  7. Enter the connection information for the data source, as follows:

    • For Data source name, type a name for the data source.

    • For Instance ID, choose the name of the instance or cluster you want to connect to.

    • Database name shows the default database for the Instance ID cluster or instance. If you want to use a different database on that cluster or instance, type its name.

    • For Username, type the user name of an account that has permissions to access the target database and also to read (perform a SELECT statement on) any tables in that database that you want to use.

    • For Password, type the password associated with the user account you entered.

  8. Choose Validate connection to verify your connection information is correct.

  9. If the connection validates, choose Create data source. If not, correct the connection information and try validating again.

    Note

    Amazon QuickSight automatically secures connections to Amazon RDS instances and Amazon Redshift clusters by using Secure Sockets Layer (SSL). You don't need to do anything to enable this.

  10. For Schema: contain sets of tables, choose Select and then choose a schema. In some cases where there is only a single schema in the database, that schema is automatically chosen and the schema selection option isn't displayed.

  11. Choose one of the following options:

    • To prepare the data before creating an analysis, choose Edit/Preview data to open data preparation. Typically, you would choose to prepare data at this point if you are planning on writing a SQL query rather than selecting data from a single table. For more information about data preparation, see Preparing Data Sets.

    • Otherwise, choose a table and then choose Select.

  12. Choose one of the following options:

    • To prepare the data before creating an analysis, choose Edit/Preview data to open data preparation for the selected table. For more information about data preparation, see Preparing Data Sets.

    • To create a data set and an analysis using the table data as-is, and to import the data set data into SPICE for improved performance (recommended), check the table size and the SPICE indicator to see if you have enough capacity.

      If you have enough SPICE capacity, choose the Import to SPICE for quicker analytics radio button and then create an analysis by choosing Visualize.

      Note

      If you want to use SPICE and you don't have enough space, choose Edit/Preview data. In data preparation, you can remove fields from the data set to decrease its size, apply a filter, or write a SQL query that reduces the number of rows or columns returned. For more information about data preparation, see Preparing Data Sets.

    • To create a data set and an analysis using the table data as-is, and to have the data queried directly from the database, choose the Directly query your data radio button and then create an analysis by choosing Visualize.

Creating a Data Set Using a Database That's Not Autodiscovered

Use the following procedure to create a connection to any database other than an autodiscovered Amazon Redshift cluster or Amazon RDS instance. This includes Amazon Redshift clusters and Amazon RDS instances that are in a different region or are associated with a different AWS account, and MariaDB, Microsoft SQL Server, MySQL, and PostgreSQL instances that are on-premise, in Amazon EC2, or in some other accessible environment.

  1. Check Data Source Limits to make sure that your target table or query doesn't exceed data source limits.

  2. Confirm that the database credentials you plan to use have appropriate permissions as described in Required Permissions for Database Credentials.

  3. Make sure you have configured the cluster or instance for Amazon QuickSight access by following the instructions in Network and Database Configuration Requirements.

  4. On the Amazon QuickSight start page, choose Manage data.

  5. On the Your Data Sets page, choose New data set.

  6. In the FROM NEW DATA SOURCES section of the Create a Data Set page, choose the Redshift Manual connect icon if you want to connect to an Amazon Redshift cluster in another region or associated with a different AWS account. Or, choose the appropriate database management system icon to connect to an instance of Amazon Aurora, MariaDB, Microsoft SQL Server, MySQL, or PostgreSQL.

  7. Enter the connection information for the data source, as follows:

    • For Data source name, type a name for the data source.

    • For Database server, type or paste one of the following values:

      • For an Amazon Redshift cluster or Amazon RDS instance, type the endpoint of the cluster or instance without the port number. For example, if the endpoint value is clustername.1234abcd.us-west-2.redshift.amazonaws.com:1234, then type clustername.1234abcd.us-west-2.redshift.amazonaws.com. You can get the endpoint value from the Endpoint field on the cluster or instance detail page in the AWS console.

      • For an Amazon EC2 instance of MariaDB, Microsoft SQL Server, MySQL, or PostgreSQL, type the public DNS. You can get the public DNS value from the Public DNS field on the instance detail pane in the Amazon EC2 console.

      • For a non-Amazon EC2 instance of MariaDB, Microsoft SQL Server, MySQL, or PostgreSQL, type the hostname or public IP address of the database server. If you are using Secure Sockets Layer (SSL) for a secured connection (recommended), you will likely need to provide the hostname to match the information required by the SSL certificate. For a list of accepted certificates see Amazon QuickSight SSL and CA Certificates.

    • For Port, type the port that the cluster or instance uses for connections.

    • For Database name, type the name of the database that you want to use.

    • For Username, type the user name of an account that has permissions to access the target database and also to read (perform a SELECT statement on) any tables in that database that you want to use.

    • For Password, type the password associated with the user account you entered.

  8. (Optional) If you are connecting to anything other than an Amazon Redshift cluster and you don't want a secured connection, uncheck Enable SSL. We strongly recommend leaving this checked, as an unsecured connection can be open to tampering. For more information on how the target instance uses SSL to secure connections, refer to the documentation for that database management system. Amazon QuickSight doesn't accept self-signed SSL certificates as valid. For a list of accepted certificates see Amazon QuickSight SSL and CA Certificates.

    Amazon QuickSight automatically secures connections to Amazon Redshift clusters by using SSL. You don't need to do anything to enable this.

    Some databases, such as Presto and Apache Spark, must meet additional requirements before Amazon QuickSight can connect. For more information, see Creating a Data Source Using Presto , or Creating a Data Source Using Apache Spark .

  9. (Optional) Choose Validate connection to verify your connection information is correct.

  10. If the connection validates, choose Create data source. If not, correct the connection information and try validating again.

  11. For Schema: contain sets of tables, choose Select and then choose a schema. Note that in some cases where there is only a single schema in the database, that schema is automatically chosen and the schema selection option isn't displayed.

  12. Choose one of the following options:

    • To prepare the data before creating an analysis, choose Edit/Preview data to open data preparation. Typically, you would choose to prepare data at this point if you are planning on writing a SQL query rather than selecting data from a single table. For more information about data preparation, see Preparing Data Sets.

    • Otherwise, choose a table and then choose Select.

  13. Choose one of the following options:

    • To prepare the data before creating an analysis, choose Edit/Preview data to open data preparation for the selected table. For more information about data preparation, see Preparing Data Sets.

    • To create a data set and an analysis using the table data as-is, and to import the data set data into SPICE for improved performance (recommended), check the table size and the SPICE indicator to see if you have enough space.

      If you have enough SPICE capacity, choose the Import to SPICE for quicker analytics radio button and then create an analysis by choosing Visualize.

      Note

      If you want to use SPICE and you don't have enough space, choose Edit/Preview data. In data preparation, you can remove fields from the data set to decrease its size, apply a filter, or write a SQL query that reduces the number of rows or columns returned. For more information about data preparation, see Preparing Data Sets.

    • To create a data set and an analysis using the table data as-is, and to have the data queried directly from the database, choose the Directly query your data radio button and then create an analysis by choosing Visualize.