Supported data types and values - Amazon QuickSight

Supported data types and values

Amazon QuickSight currently supports the following primitive data types: Date, Decimal, Integer, and String. The following data types are supported in SPICE: Date, Decimal-fixed, Decimal-float, Integer, and String. QuickSight accepts Boolean values by promoting them to integers. It can also derive geospatial data types. Geospatial data types use metadata to interpret the physical data type. Latitude and longitude are numeric. All other geospatial categories are strings.

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 fields or columns that can't be converted. If you get an error that says "fields were skipped because they use unsupported data types", alter your query or table to remove or recast unsupported data types.

String and text data

Fields or columns that contain characters are called strings. A field with the data type of STRING can initially contain almost any type of data. Examples include names, descriptions, phone numbers, account numbers, JSON data, cities, post codes, dates, and numbers that can be used to calculate. These types are sometimes called textual data in a general sense, but not in a technical sense. QuickSight doesn't support binary and character large objects (BLOBs) in dataset columns. In the QuickSight documentation, the term "text" always means "string data".

The first time you query or import the data, QuickSight tries to interpret the data that it identifies as other types, for example dates and numbers. It's a good idea to verify that the data types assigned to your fields or columns are correct.

For each string field in imported data, QuickSight uses a field length of 8 bytes plus the UTF-8 encoded character length. Amazon QuickSight supports UTF-8 file encoding, but not UTF-8 (with BOM).

Date and time data

Fields with a data type of Date also include time data, and are also known as Datetime fields. QuickSight supports dates and times that use supported date formats.

QuickSight uses UTC time for querying, filtering, and displaying date data. When date data doesn't specify a time zone, QuickSight assumes UTC values. When date data does specify a time zone, 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.

For each DATE field in imported data, QuickSight uses a field length of 8 bytes. QuickSight supports UTF-8 file encoding, but not UTF-8 (with BOM).

Numeric data

Numeric data includes integers and decimals. Integers with a data type of INT are negative or positive numbers that don't have a decimal place. QuickSight doesn't distinguish between large and small integers. Integers over a value of 9007199254740991 or 2^53 - 1 might not display exactly or correctly in a visual.

Decimals with the data type of Decimal are negative or positive numbers that contain at least one decimal place before or after the decimal point. When you choose Direct Query mode, all non-integer decimal types are marked as Decimal and the underlying engine handles the precision of the datapoint based on the data source's supported behaviors. For more information on supported data source types, see Supported data types and values.

When you store your dataset in SPICE, you can choose to store your decimal values as fixed or float decimal types. Decimal-fixed data types use the format of decimal (18,4) that allow 18 digits total and up to 4 digits after the decimal point. Decimal-fixed data types are a good choice to conduct exact mathematical operations, but QuickSight rounds the value to the nearest ten thousandth place when the value is ingested into SPICE.

Decimal-float data types provide approximately 16 significant digits of accuracy to a value. The significant digits can be on either side of the decimal point to support numbers with many decimal places and higher numbers at the same time. For example, the Decimal-float data type supports the number 12345.1234567890 or the number 1234567890.12345. If you work with very small numbers that are close to 0, the Decimal-float data type supports up to 15 digits to the right of the decimal point, for example 0.123451234512345. The maximum value that this data type supports is 1.8 * 10^308 to minimize the probability of an overflow error with your data set.

The Decimal-float data type is inexact and some values are stored as approximations instead of the real value. This may result in slight descrepencies when you store and return some specific values. The following considerations apply to the Decimal-float data type.

  • If the dataset that you're using comes from an Amazon S3 data source, SPICE assigns the Decimal-float decimal type to all numeric decimal values.

  • If the dataset that you're using comes from a database, SPICE uses the decimal type that the value is assigned in the database. For example, if the value is assigned a fixed-point numeric value in the database, the value will be a Decimal-fixed type in SPICE.

For existing SPICE datasets that contain fields that can be converted to the Decimal-float data type, a pop-up appears in the Edit dataset page. To convert fields of an existing dataset to the Decimal-float data type, choose UPDATE FIELDS. If you don't want to opt in, choose DO NOT UPDATE FIELDS. The Update fields pop up appears every time you open the Edit dataset page until the dataset is saved and published. The image below shows the Update fields pop up.

The Update fields pop up.