How to add fully created sql query to the QuickSight in order to use for visualization?

Question & Answer
,
1

Hello,
Currently a fully completed sql query runs in redshift, then the csv file is downloaded and used as a report to analyze.
Is it possible to create the data source in QuickSight combining all data tables, used in this query, and then add this query to have the report? I am new in QuickSight, not sure how to combine all these parts.
thank you

2

Hi @olgashi First of all, welcome to the QuickSight community :slight_smile:

If you already have a fully built SQL query that runs in Redshift, it is really easy to use the same query within QuickSight to create a dataset that you can use to create your analyses / dashboards.

This page walks you through how to create a connection to your Redshift cluster and use custom SQL to create a dataset: Creating a dataset from a database - Amazon QuickSight

Also, here is a QuickSight Learning Series video from our very own @royyung that demonstrates using Custom SQL: Custom SQL in QuickSight: 2023 Amazon QuickSight Learning Series - YouTube

Hope this answers your question. If it does, please mark this as a “Solution” as it helps others in the community as well. Thanks!

2 Likes
3

Thanks @SD_QS for sharing the details. The custom sql is one of the best approach to do the same. However if there is NO change in the data, better to use custom sql with SPICE so that the data can be in memory and all reporting can be done on top of that. However user needs to refresh the SPICE accordance to the data refresh schedule.

Regards - Sanjeeb

4

hi @SD_QS ,
thank you for the response and the links.
if the redshift query contains more than 1 dataset. what should be done?
should I add all the dataset into QuickSight,
then join them (if so what type of join will work always)
then edit and use custom SQL?
OR
when a first dataset is creating I can directly use Custom SQL function and it will auto add all related dataset?
would appreciate if you respond with the orders how it should be done.
thank you

5

hi @Sanjeeb2022,
thank you for your response. how the users can refresh the Spice? can you provide the steps please? do users need any additional authorizations?

thank you

6

Hi @olgashi
When you create a dataset in QuickSight and select Redshift as your data source, you can use custom SQL directly that could be leveraging multiple tables, you don’t have to create a QuickSight dataset for each table…

Also, for your question around spice, you could refresh the data in spice manually or on a schedule, and you could do full refresh or incremental. Details here: Refreshing SPICE data - Amazon QuickSight

Hope that helps, thanks!

1 Like
7

Hello SD_QS,
your help is tremendously important!

I ran the query following your instruction, but got an error.
Are you able to guide how to find the error, meaning what does it mean ErrorCode number, Position, source error state?

sourceErrorCode:500310
sourceErrorMessage:(500310) Invalid operation: syntax error at end of input
Position: 8068;
sourceErrorState:42601
sourceException:java.sql.SQLException
sourceType:REDSHIFT

8

@olgashi that seems like a syntax error at the position specified, please check your code… This is not a QuickSight issue