By using AWS re:Post, you agree to the AWS re:Post Terms of Use
AWS re:Postre:Post

How to make Redshift Query Editor V2 utilize external schemas for auto-complete.

0

Hello!

I am using Redshift Serverless and am having some difficulty with auto-complete. This understandably leads to tedious typing of queries and introduces unneeded typos.

We have several external tables stored in the AWS Glue Catalog and this is where most of our data is stored. Redshift does a great job of auto-populating the entire glue catalog under the "awsdatacatalog". All tables and their columns and types can be seen by drilling down the hierarchy in the left panel. However the query editor itself does not offer any auto-complete suggestions when typing our queries.

An example would be if I have a table "users" stored in an external schema "main_app_data". If I want to get "user_id" and "first_name" I would have to type out select user_id, first_name from awsdatacatalog.main_app_data.users completely manually. None of these items are auto-populated with a tab-to-complete functionality.

What I've tried:

  • Ensuring that I'm superuser and admin (so no restrictions on permissions!)
  • Refreshed the connection and clicked "refresh autocomplete"
  • Turned on the Generative SQL setting in user preferences.
  • Saved a large query that uses many of my tables to help with the Generative SQL (this is recommended in a tooltip in the user preferences).

Can anyone help out with this?

Topics
Analytics
Tags
AnalyticsAWS GlueAmazon RedshiftAmazon Redshift Serverless
Language
English
octaura_nyc_data_engineer
asked 4 months ago425 views
1 Answer
0

To configure Redshift Query Editor V2 to utilize external schemas for auto-complete, you can follow these steps:

Open the Redshift Query Editor V2 in the AWS Management Console. Choose the "Editor" view and select the database you want to work with. In the tree-view panel, navigate to the external schema you want to utilize for auto-complete. Choose "Refresh autocomplete" to refresh the displayed suggestions when authoring SQL. To enable the auto-populated with a tab-to-complete functionality in Redshift Query Editor V2, you can use the keyboard shortcut or the "More" icon to access the "Refresh autocomplete" option. This will refresh the displayed suggestions when authoring SQL. The autocomplete suggestions are automatically activated or can be triggered by a simple shortcut within Amazon Redshift Query Editor V2. You can also turn on (or off) Autocomplete to show suggestions as you enter your SQL in the Editor preferences.

In Redshift Query Editor V2, you can use the following keyboard shortcuts to enable the auto-populated tab-to-complete functionality:

To trigger auto-complete: Press "Ctrl + Space" on your keyboard to invoke the auto-complete suggestions.

To complete the currently highlighted suggestion: Press "Tab" after the desired suggestion is highlighted to auto-complete the suggestion.

These shortcuts help streamline the SQL writing process in Redshift Query Editor V2, making it easier to utilize the autocomplete functionality and quickly select the suggested commands or keywords.

profile pictureAWS
Sandhya Khanderia
answered 4 months ago
profile picture
EXPERT
Giovanni Lauria
reviewed 25 days ago
  • octaura_nyc_data_engineer
    4 months ago

    Hi Sandhya,

    Thank you for your answer. However, it still does not seem to work.

    To give more info, I have my tables and schemas defined in the AWS Glue Catalog. This shows up automatically in Redshift in the tree view as demonstrated below:

    Serverless: redshift-my-workgroup
  |__ awsdatacatalog
      |__ example_schema
          |__ users_table
          |__ orders_table

    If I would like to select ID from users_table I would need to fully type out SELECT id FROM awsdatacatalog.example_schema.users_table

    I am able to find the "refresh auto-complete" at the top right three dots, however running this does not seem to change anything. Pressing "Ctrl +Space" after FROM does not offer awsdatacatalog nor will it give me example_schema if I press it after the awsdatacatalog.

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions

Relevant content