The easiest way to load data to QuickSight is to just upload it to SPICE, which was explained in Chapter 1, A Quick Start to QuickSight. Another alternative to uploading text files is to use S3 storage and this section will detail this flow.
For this demonstration, we will use the file USAStateAbbr.csv, which has a list of USA state codes and the corresponding full names.
Note
The sample files are in GitHub at https://github.com/rnadipalli/QuickSight/tree/master/sampledata.
Here are the detailed steps to upload a file to an S3 filesystem:
- Download the file to your local system (laptop).
- Upload the file to AWS S3, login to our account, and from the Services menu select S3.
- Select the S3 bucket or create a new S3 bucket. In the next screenshot, I have selected the
collegescorecardbucket that I created earlier. - Click on the Upload button, select the local file from your system, and then upload.
Figure 2.7: Upload text file to S3
- Now that the file is uploaded to S3; review its properties, which will be needed in later steps, as shown in the following screenshot:
Figure 2.8: S3 file properties
To upload files to SPICE, we first need to create a manifest file, which is a JSON file with the following structure:
{
"fileLocations": [
{"URIs": ["uri1", "uri2", "uri3"]},
{"URIPrefixes": ["prefix1", "prefix2", "prefix3"]}
],
"globalUploadSettings": {
"format": "CSV",
"delimiter": ",",
"textqualifier": "'",
"containsHeader": "true"
}
}
The manifest file elements are described as follows:
fileLocations: This is used to specify the files to be uploaded. You can specify a list of fileURIsin an array or specifyURIPrefixeswhere all files in the specific folder will be imported. QuickSight does not recursively retrieve files from child folders.globalUploadSettings(optional): This is used to specifyformat,delimiter,textqualifier, andcontainsHeader.
The following is the manifest example to load the USAStateAbbr file:
{
"fileLocations": [
{
"URIs": [
"https://s3.amazonaws.com/collegescorecard/USAStateAbbr.csv"
]
}
],
"globalUploadSettings": {
"format": "CSV",
"delimiter": ",",
"containsHeader": "true"
}
}
Next, we will see how this file can be imported to QuickSight using the following steps:
- From the QuickSight home page, click on Manage data.
- Next, select New data set and then select S3. Next, upload the manifest file created in the previous steps and click on Connect, as shown in the following screenshot:
Figure 2.9: New S3 dataset
This completes the import of S3 data as a dataset in QuickSight that can be further visualized for analysis.