Amazon QuickSight
User Guide

Data Source Limits

Data sources that you use with Amazon QuickSight must conform to the following limits.

SPICE Limits

When you create a new data set in Amazon QuickSight, SPICE limits the number of rows you can add to a data set. You can ingest data into SPICE from a query or from a file. Each file can have up to 1,000 columns. Each column name can have up to 127 Unicode characters. Each field can have up to 2,047 Unicode characters. To retrieve a subset of data from a larger set, you can deselect columns or apply filters to reduce the size of the data. If you are importing from Amazon S3, each manifest can specify up to 1,000 files.

Limits for SPICE are as follows:

  • 25 million (25,000,000) rows per data set in Standard edition

  • 100 million (100,000,000) rows per data set in Enterprise edition

  • 2047 Unicode characters for each field

  • 127 Unicode characters for each column name

  • 1,000 columns for each file

  • 1,000 files for each manifest

In rare cases, if the rows that you are ingesting into SPICE are large in size, you might encounter the limit for the number of GB per data set. Currently, the size limit per data set is 25 GB for Standard edition, and 200 GB for Enterprise edition. The size is based on the SPICE capacity the data occupies after ingestion into SPICE. You can calculate your capacity by using the formula described in Capacity Planning for SPICE.

Query Limits

If you aren't importing data into SPICE, different limits apply for space and time. For operations such as connecting, sampling data for a data set, and generating visuals, timeouts can occur. In some cases, these are timeout limits set by the source database engine. In other cases, such as visualizing, Amazon QuickSight generates a timeout after 2 minutes.

However, not all database drivers react to the 2-minute timeout, for example Amazon Redshift. In these cases, the query runs for as long as it takes for the response to return, which can result in long-running queries on your database. When this happens, you can cancel the query from the database server to free up database resources. Follow the instructions for your database server about how to do this. For example, for more information on how to cancel queries in Amazon Redshift, see Canceling a Query in Amazon Redshift, and also Implementing Workload Management in Amazon Redshift in the Amazon Redshift Database Developer Guide.

Each result set from a direct query can have up to 1,000 columns. Each column name can have up to 127 Unicode characters. If you want to retrieve data from a larger table, you can use one of several methods to reduce the size of the data. You can deselect columns, or apply filters. In a SQL query, you can also use predicates, such as WHERE, HAVING. If your visuals time out during a direct query, you can simplify your query to optimize execution time or you can import the data into SPICE.

Limits for queries are as follows:

  • 127 Unicode characters for each column name.

  • 1,000 columns for each data set.

  • 2-minute limit for generating a visual, or an optional data set sample.

  • Data source timeout limits apply (varies for each database engine).

Supported Data Types

Amazon QuickSight currently supports the following primitive data types:

  • Date – Dates must be in one of the supported data formats.

  • Decimal – The decimal data type supports up to four decimal places to the right of the decimal point. Values that have a higher scale than this are truncated to the fourth decimal place when displayed in data preparation or analyses and when imported into SPICE. For example, 13.00049 is truncated to 13.0004.

    During data preparation, calculated fields that use decimal data with more than four decimal places use the full value to perform the calculation. If the result is again decimal data that uses more than four decimal places, the result is then truncated as described preceding. For more information, see Handling Decimal Values in Calculated Fields.

  • Integer

  • String

Make sure that any table or file that you use as a data source contains only fields that can be implicitly converted to these data types. Amazon QuickSight skips any data rows that can't be converted.

The following table lists the source data types that are supported. Boolean data types are converted to integers in Amazon QuickSight.

Database Engine or Source Numeric Data Types String Data Types Datetime Data Types Boolean Data Types

Amazon Athena, Presto

  • bigint

  • decimal

  • double

  • integer

  • real

  • smallint

  • tinyint

  • char

  • varchar

  • date

  • timestamp

  • boolean

Amazon Aurora, MariaDB, and MySQL

  • bigint

  • decimal

  • double

  • int

  • integer

  • mediumint

  • numeric

  • smallint

  • tinyint

  • char

  • enum

  • set

  • text

  • varchar

  • date

  • datetime

  • timestamp

  • year

PostgreSQL

  • bigint

  • decimal

  • double

  • integer

  • numeric

  • precision

  • real

  • smallint

  • char

  • character

  • text

  • varchar

  • varying character

  • date

  • timestamp

  • boolean

Apache Spark

  • bigint

  • decimal

  • double

  • integer

  • real

  • smallint

  • tinyint

  • varchar

  • date

  • timestamp

  • boolean

Snowflake

  • bigint

  • byteint

  • decimal

  • double

  • doubleprecision

  • float

  • float4

  • float8

  • int

  • integer

  • number

  • numeric

  • real

  • smallint

  • tinyint

  • char

  • character

  • string

  • text

  • varchar

  • date

  • datetime

  • time

  • timestamp

  • timestamp_*

  • boolean

Microsoft SQL Server

  • bigint

  • bit

  • decimal

  • int

  • money

  • numeric

  • real

  • smallint

  • smallmoney

  • tinyint

  • char

  • nchar

  • nvarchar

  • text

  • varchar

  • date

  • datetime

  • datetime2

  • datetimeoffset

  • smalldatetime

  • bit

Unsupported Data Values

If a field contains values that don't conform with the data type that Amazon QuickSight assigns to the field, the rows containing those values are skipped. For example, take the following source data.

Sales ID Sales Date Sales Amount -------------------------------------- 001 10/14/2015 12.43 002 5/3/2012 25.00 003 Unknown 18.17 004 3/8/2009 86.02

Amazon QuickSight interprets Sales Date as a date field and drops the row containing a nondate value, so only the following rows are imported.

Sales ID Sales Date Sales Amount -------------------------------------- 001 10/14/2015 12.43 002 5/3/2012 25.00 004 3/8/2009 86.02

In some cases, a database field might contain values that the JDBC driver can't interpret for the source database engine. In such cases, the uninterpretable values are replaced by null so that the rows can be imported. The only known occurrence of this issue is with MySQL date, datetime, and timestamp fields that have all-zero values, for example 0000-00-00 00:00:00. For example, take the following source data.

Sales ID Sales Date Sales Amount --------------------------------------------------- 001 2004-10-12 09:14:27 12.43 002 2012-04-07 12:59:03 25.00 003 0000-00-00 00:00:00 18.17 004 2015-09-30 01:41:19 86.02

In this case, the following data is imported.

Sales ID Sales Date Sales Amount --------------------------------------------------- 001 2004-10-12 09:14:27 12.43 002 2012-04-07 12:59:03 25.00 003 (null) 18.17 004 2015-09-30 01:41:19 86.02

Handling Date Time Zones

Amazon QuickSight uses UTC time for querying, filtering, and displaying date data.

When date data doesn't specify a time zone, Amazon QuickSight assumes UTC values. When date data does specify a time zone, Amazon QuickSight converts it to display in UTC time. For example, a date field with a time zone offset like 2015-11-01T03:00:00-08:00 is converted to UTC and displayed in Amazon QuickSight as 2015-11-01T15:30:00.

Supported Date Formats

Data in date fields must be in one of the following supported formats, depending on the data source type.

For file uploads, Amazon S3 sources, Athena, and Salesforce, Amazon QuickSight supports the use of date and time formats described in the Joda API documentation. The time formats include both 24-hour and 12-hour clocks. For a complete list of Joda date formats, see Class DateTimeFormat on the Joda website.

For relational database sources, including Amazon Redshift, Amazon RDS, PostgreSQL, MySQL, Aurora, MariaDB, and Microsoft SQL Server, Amazon QuickSight supports the following date and time formats (24-hour only).

Amazon QuickSight supports dates in the range from Jan 1, 1400 00:00:00 UTC to Feb 26, 2364 23:59:59 UTC for SPICE data sets.

  1. dd/MM/yyyy HH:mm:ss, for example 31/12/2016 15:30:00.

  2. dd/MM/yyyy, for example 31/12/2016.

  3. dd/MMM/yyyy HH:mm:ss, for example 31/DEC/2016 15:30:00.

  4. dd/MMM/yyyy, for example 31/DEC/2016.

  5. dd-MMM-yyyy HH:mm:ss, for example 31-DEC-2016 15:30:00.

  6. dd-MMM-yyyy, for example 31-DEC-2016.

  7. dd-MM-yyyy HH:mm:ss, for example 31-12-2016 15:30:00.

  8. dd-MM-yyyy, for example 31-12-2016.

  9. MM/dd/yyyy HH:mm:ss, for example 12/31/2016 15:30:00.

  10. MM/dd/yyyy, for example 12/31/2016.

  11. MM-dd-yyyy HH:mm:ss, for example 12-31-2016 15:30:00.

  12. MM-dd-yyyy, for example 12-31-2016.

  13. MMM/dd/yyyy HH:mm:ss, for example DEC/31/2016 15:30:00.

  14. MMM/dd/yyyy, for example DEC/31/2016.

  15. MMM-dd-yyyy HH:mm:ss, for example DEC-31-2016 15:30:00.

  16. MMM-dd-yyyy, for example DEC-31-2016.

  17. yyyy/MM/dd HH:mm:ss, for example 2016/12/31 15:30:00.

  18. yyyy/MM/dd, for example 2016/12/31.

  19. yyyy/MMM/dd HH:mm:ss, for example 2016/DEC/31 15:30:00.

  20. yyyy/MMM/dd, for example 2016/DEC/31.

  21. yyyy-MM-dd HH:mm:ss, for example 2016-12-31 15:30:00.

  22. yyyy-MM-dd, for example 2016-12-31.

  23. yyyy-MMM-dd HH:mm:ss, for example 2016-DEC-31 15:30:00.

  24. yyyy-MMM-dd, for example 2016-DEC-31.

  25. yyyyMMdd'T'HHmmss, for example 20161231T153000.

  26. yyyy-MM-dd'T'HH:mm:ss, for example 2016-12-31T15:30:00.

Note

When you create a calculated column formula using a date format that contains apostrophes, make sure to escape them. Examples are formatDate({myDateField}, "yyyyMMdd'T'HHmmss") or formatDate({myDateField}, 'yyyyMMdd\'T\'HHmmss').