Blog

In this guide, we will discuss How to Using Data Sources in AWS Quicksight. AWS Quicksight accepts data from various sources. Once you click 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 list 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 click 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",
   "delimiter": ",",
   "textqualifier": "'",
   "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. Click on “Validate Connection”. Once the connection is validated, click 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 click on “Use custom SQL” to run query on Athena tables.

Once done, you can click on “Edit/Preview data” or “Visualize” to either edit your data or directly visualize 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 ability 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.

Next Topic : Click Here