Importing Data into SPICE - Amazon QuickSight

Importing Data into SPICE

When you import data into a dataset rather than using a direct SQL query, it becomes SPICE data because of how it's stored. SPICE is the Amazon QuickSight Super-fast, Parallel, In-memory Calculation Engine. It's engineered to rapidly perform advanced calculations and serve data. In Enterprise edition, data stored in SPICE is encrypted at rest.

When you create or edit a dataset, you choose to use either SPICE or a direct query, unless the dataset contains uploaded files. Importing (also called ingesting) your data into SPICE can save time and money:

  • Your analytical queries process faster.

  • You don't need to wait for a direct query to process.

  • Data stored in SPICE can be reused multiple times without incurring additional costs. If you use a data source that charges per query, you're charged for querying the data when you first create the dataset and later when you refresh the dataset.

SPICE capacity is allocated separately for each AWS Region. Default SPICE capacity is automatically allocated to your home AWS Region. For each AWS account, SPICE capacity is shared by all the people using QuickSight in a single AWS Region. The other AWS Regions have no SPICE capacity unless you choose to purchase some. QuickSight administrators can view how much SPICE capacity you have in each AWS Region and how much of it is currently in use. A QuickSight administrator can purchase more SPICE capacity or release unused SPICE capacity as needed. For more information, see Managing SPICE Capacity.

Capacity Planning for SPICE Datasets

The amount of SPICE capacity a dataset uses isn't the same as the size of its source file or table. The logical size computation occurs after all the data type transformations and calculated columns you define during data preparation. These fields are materialized in SPICE in a way that enhances query performance. Any changes you make in an analysis have no effect on the logical size of the data in SPICE. Only changes that are saved in the data et apply to SPICE capacity.

In capacity planning for SPICE, consider what data types you plan to define in the dataset. For example, the file you want to import might contain all strings (text). But for these to be used in a meaningful way in an analysis, you prepare the data by changing the data types to their proper form. For example, fields containing prices are changed from strings to decimals, and fields containing dates are changed from strings to dates. If you create a calculation to make the conversion, you can remove the original field from the dataset and substitute the formatted calculated field. In that case, you don't need to include the size of the original field in your capacity planning. Only included fields are stored in SPICE.

Note

Geospatial data types use metadata to interpret the physical data type. Latitude and longitude are numeric. All other geospatial categories are strings.

To calculate how much SPICE capacity your dataset needs, multiply the number of rows by the number of bytes SPICE uses per row. Currently, SPICE needs 8 bytes per field for decimal, int, and date fields. For each string field (text), SPICE needs 8 bytes plus the UTF-8 encoded character length. The formula looks like the following.

Total logical row size in bytes = (Number of Numeric Fields * 8 bytes per field) + (Number of Date Fields * 8 bytes per field) + (Number of Text Fields * (8 bytes + UTF-8 encoded character length per field) ) Total bytes of data = Number of rows * Total logical row size in bytes GB of SPICE Capacity Needed = Total bytes of data / 1,073,741,824

For example, let's say you have a table with 5,000,000 rows that you want to import into SPICE. It has 30 numeric fields, 20 date fields, and10 string fields of 100 bytes each. Your formula looks like the following.

Total logical row size in bytes = 1480 bytes = (30 * 8) + (20 * 8) + (10 * (8 + 100) ) Total bytes of data = 5,000,000 rows * 1480 bytes = 7,400,000,000 bytes GB of SPICE Capacity Needed = 7,400,000,000 / 1,073,741,824 = 7 GB