I need to retrieve a specific subset of data from my database using ConverSight. What features should I consider for this task?
Hello @keerthana.kalimuthu, for the use case you have mentioned, you can utilize the Smart Query feature. This feature involves creating a custom SQL query to retrieve data according to your requirements. Here are the steps to create one:
- To access the SME screen, navigate to Data Management and click on the settings icon for the dataset you want to work with. Then, select Configure SME.
- Once you are on the SME screen, you will see a settings icon on the top right corner. Click on it and select Create Custom Field. On the third table that appears, click on Smart Query.
- In the Smart Query section, you can type in the SQL query to retrieve the data. To list all columns, use @ and to list all tables, use #. These prefixes are necessary to resolve the schema of the dataset.
- To load the data into Athena’s knowledge base, select Materialized from the drop-down menu. If it is only a reference for another Smart Query, select Reference to avoid loading it into the knowledge base.
- To create the view as a separate table, enabling the Materialized View flag is necessary. This means that any changes made to the source tables used in the view will not affect the view’s data. If you only want a reference to the source table, disable this option.
- Once you have entered the SQL query, click on Validate Query to check the validity of your SQL.
- If the query validation is successful, click on the Save icon to save your changes.
- Finally, click on Publish to make your changes live.
- You can access the data by querying the view name in Athena. You can also retrieve individual columns of the view.
Please refer to the attached screenshot for further guidance.