AWS Quicksight - Using Data Sources

AWS Quicksight accepts data from various sources. Once you cpck on “New Dataset” on the home page, it gives you options of all the data sources that can be used.

Below are the sources containing the pst of all internal and external sources −

Let us go through connecting Quicksight with some of the most commonly used data sources −

Uploading a file from system

It allows you to input .csv, .tsv, .clf,.elf.xlsx and Json format files only. Once you select the file, Quicksight automatically recognizes the file and displays the data. When you cpck on Upload a File button, you need to provide the location of file which you want to use to create dataset.

Using a file from S3 format

The screen will appear as below. Under Data source name, you can enter the name to be displayed for the data set that would be created. Also you would require either uploading a manifest file from your local system or providing the S3 location of the manifest file.

Manifest file is a json format file, which specifies the url/location of input files and their format. You can enter more than one input files, provided the format is same. Here is an example of a manifest file. The “URI” parameter used to pass the location of input file is S3.

{
   "fileLocations": [
      {
         "URIs": [
            "url of first file",
            "url of second file",
            "url of 3rd file and so on"
         ]
      },
   
   ],
}
"globalUploadSettings": {
   "format": "CSV",
   "depmiter": ",",
   "textquapfier": " ",
   "containsHeader": "true"
}

The parameters passed in globalUploadSettings are the default ones. You can change these parameters as per your requirements.

MySQL

You need to enter the database information in the fields to connect to your database. Once it is connected to your database, you can import the data from it.

Following information is required when you connect to any RDBMS database −

DSN name

Type of connection

Database server name

Port

Database name

User name

Password

Following RDBMS based data sources are supported in Quicksight −

Amazon Athena

Amazon Aurora

Amazon Redshift

Amazon Redshift Spectrum

Amazon S3

Amazon S3 Analytics

Apache Spark 2.0 or later

MariaDB 10.0 or later

Microsoft SQL Server 2012 or later

MySQL 5.1 or later

PostgreSQL 9.3.1 or later

Presto 0.167 or later

Snowflake

Teradata 14.0 or later

Athena

Athena is the AWS tool to run queries on tables. You can choose any table from Athena or run a custom query on those tables and use the output of those queries in Quicksight. There are couple of steps to choose data source

When you choose Athena, below screen appears. You can input any data source name which you want to give to your data source in Quicksight. Cpck on “Vapdate Connection”. Once the connection is vapdated, cpck on the “Create new source” button

Now choose the table name from the dropdown. The dropdown will show the databases present in Athena which will further show tables in that database. Else you can cpck on “Use custom SQL” to run query on Athena tables.

Once done, you can cpck on “Edit/Preview data” or “Visuapze” to either edit your data or directly visuapze the data as per your requirement.

Deleting a data source

When you delete a data source which is in use in any of the Quicksight dashboards, it can make associated data set unusable. It usually happens when you query a SQL based data source.

When you create a dataset based on S3, Sales force or SPICE, it does not affect your abipty to use any dataset as data is stored in SPICE; however refresh option is not available in this case.

To delete a data source, select the data source. Navigate to From Existing Data Source tab on creating a dataset page.

Before deletion, you can also confirm estimated table size and other details of data source.