SQL editor features of the JupyterLab SQL extension - Amazon SageMaker AI

SQL editor features of the JupyterLab SQL extension

The SQL extension provides magic commands that enable the SQL editor functionalities within your JupyterLab notebook cells.

If you are a user of the SageMaker distribution image version 1.6, you must load the SQL extension magic library by running %load_ext amazon_sagemaker_sql_magic in a JupyterLab notebook. This turns on SQL editing features.

For users of SageMaker distribution image versions 1.7 and later, no action is needed, the SQL extension loads automatically.

Once the extension is loaded, add the %%sm_sql magic command at the beginning of a cell to activate the following capabilities of the SQL editor.

  • Connection-selection dropdown: Upon adding an %%sm_sql magic command to a cell, a dropdown menu appears at the top of the cell with your available data source connections. Select a connection to automatically fill in the parameters needed to query that data source. The following is an example of an %%sm_sql magic command string generated by selecting the connection named connection-name. 

    %%sm_sql --metastore-type GLUE_CONNECTION --metastore-id connection-name

    Use the SQL editor's features below to build your SQL queries, then run the query by running the cell. For more information on the SQL execution capabilities, see SQL execution features of the JupyterLab SQL extension.

  • Query result dropdown: You can specify how to render query results by selecting a result type from the dropdown menu next to your connection-selection dropdown menu. Choose between the following two alternatives:

    • Cell Output: (default) This option displays the result of your query in the notebook cell output area.

    • Pandas Dataframe: This option populates a pandas DataFrame with the query results. An extra input box lets you name the DataFrame when you choose this option.

  • SQL syntax highlight: The cell automatically visually distinguishes SQL keywords, clauses, operators, and more by color and styling. This makes SQL code easier to read and understand. Keywords such as SELECT, FROM, WHERE, and built-in functions such as SUM and COUNT, or clauses such as GROUP BY and more are highlighted in a different color and bold style.

  • SQL formatting: You can apply consistent indents, capitalization, spacing, and line breaks to group or separate SQL statements and clauses in one of the following ways. This makes SQL code easier to read and understand.

    • Right-click on the SQL cell and choose Format SQL.

    • When the SQL cell is in focus, use the ALT + F shortcut on Windows or Option + F on MacOS.

  • SQL auto-completion: The extension provides automatic suggestions and completion of SQL keywords, functions, table names, column names, and more as you type. As you start typing an SQL keyword such as SELECT or WHERE, the extension displays a pop-up with suggestions to auto-complete the rest of the word. For example, when typing table or column names, it suggests matching table and column names defined in the database schema.

    Important

    To enable SQL auto-completion in JupyterLab notebooks, users of the SageMaker AI distribution image version 1.6 must run the following npm install -g vscode-jsonrpc sql-language-server command in a terminal. After the installation completes, restart the JupyterLab server by running restart-jupyter-server.

    For users of SageMaker distribution image versions 1.7 and later, no action is needed.

    The cell offers two methods for auto-completing recognized SQL keywords:

    • Explicit invocation (recommended): Choose the Tab key to initiate the context-aware suggestion menu, then choose Enter to accept the suggested item.

    • Continuous hinting: The cell automatically suggests completions as you type.

    Note

    • Auto-completion is only triggered if the SQL keywords are in uppercase. For instance, entering SEL prompts for SELECT, but typing sel does not.

    • The first time you connect to a data source, SQL auto-completion indexes the data source's metadata. This indexing process may take some time to complete depending on the size of your databases.