# SQL Autocompletion and Introspection

These features are the result of Xing-Zhi Jiang's work during [Google Summer of Code 2021](https://summerofcode.withgoogle.com/archive/2021/projects/6374646231859200/) and they are being fine-tuned and improved constantly to get a decent code completion and introspection experience in the MariaDB Jupyter Kernel.

## Autocompletion

The MariaDB Jupyter kernel is able to provide SQL autocompletion with a basic internal understanding of the context within your statements. We are trying to make the suggestions as precise as possible, but any SQL autocompletion is imperfect unless you either duplicate the entire grammar logic of MariaDB Server or the MariaDB Server itself develops mechanisms for external tools to poke into its parsing logic.

Autocompletion in Jupyter can be triggered with the key `TAB`. In the classic Juptyter Notebook interface, it's possible to install some extensions to enable continuous hinting, but at the time this article is written there is no mechanism to enable continuous hinting in the new JupyterLab interface.

Here's a summary of our autocompletion capabilities, but we strongly recommend you play with the feature yourself and discover what it can do.

**SQL keywords and functions completion**

![](https://1013232429-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FCjGYMsT2MVP4nd3IyW2L%2Fuploads%2Fgit-blob-2f26b66bca1ce3cc9a77bed99b77a2c9a15d4f9a%2Fkeywords.png?alt=media)

**Completion of database names in `USE` statements**

![](https://1013232429-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FCjGYMsT2MVP4nd3IyW2L%2Fuploads%2Fgit-blob-2e1c3a31ac8d16b4f0bdb4171f85a86f29fc8742%2Fuse.png?alt=media)

**Completion of database names in constructs like `database_to_autocomplete.table_name`**

![](https://1013232429-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FCjGYMsT2MVP4nd3IyW2L%2Fuploads%2Fgit-blob-baf7196bc9287a087b71430563b94755f34f6ee7%2Fdatabase1.png?alt=media)

**Completion of table names in constructs like `db.table_name_to_autocomplete`**

![](https://1013232429-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FCjGYMsT2MVP4nd3IyW2L%2Fuploads%2Fgit-blob-ad66d1f2b59e5c25d6e42149d5f13ca0c5726961%2Ftables1.png?alt=media)

**Completion of column names in the `WHERE` clause**

![](https://1013232429-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FCjGYMsT2MVP4nd3IyW2L%2Fuploads%2Fgit-blob-f7c922ac5010b6248210a9b00ef9e67479013b16%2Fwhere.png?alt=media)

**Completion of column names in `SELECT` queries**

![](https://1013232429-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FCjGYMsT2MVP4nd3IyW2L%2Fuploads%2Fgit-blob-0e6bea0140b9c544d90cf38992551c965bdbdefa%2Fcolumns.png?alt=media)

**Completion of column names in `INSERT` statements**

![](https://1013232429-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FCjGYMsT2MVP4nd3IyW2L%2Fuploads%2Fgit-blob-6fcdf3b6615624356a845551ba0f6edf3cbfb0a8%2Finsertcol.png?alt=media)

**Resolving aliases and completion of column names in constructs like `alias.column_to_autocomplete`**

![](https://1013232429-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FCjGYMsT2MVP4nd3IyW2L%2Fuploads%2Fgit-blob-6dfa67f5dd0019620b6b6ae47ca53314c9f885aa%2Falias.png?alt=media)

**Completion of `SHOW` statments**

![](https://1013232429-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FCjGYMsT2MVP4nd3IyW2L%2Fuploads%2Fgit-blob-c7dde9c48d1e787141a48e93e1c286c7f2383002%2Fshow.png?alt=media)

**Completion of user accounts**

![](https://1013232429-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FCjGYMsT2MVP4nd3IyW2L%2Fuploads%2Fgit-blob-a3c7f2798a875a70fabb30fc88f834439eba3468%2Fusername.png?alt=media)

**Completion of global and session variables**

![](https://1013232429-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FCjGYMsT2MVP4nd3IyW2L%2Fuploads%2Fgit-blob-411e329492bf3ae4010d742c798c8165595347da%2Fvars.png?alt=media)

## Introspection

Code introspection in Jupyter can be triggered with the `SHIFT-TAB` combination.\
This feature was designed to help you understand your database environment faster whilst typing SQL statements, for instance checking the table schema by inspecting on the table name before selecting a bunch of columns, or even checking the documentation of a SQL function to see the function signature and some practical examples and spare you an extra search on [MariaDB Documentation](https://mariadb.com/docs/connectors/other/mariadb-jupyter-kernel/using-the-kernel/broken-reference).

Although we tried to make introspection look exactly the same in both classic Jupyter Notebook and Jupyterlab interfaces, it wasn't possible due to some fundamental difference in how Notebook renders the introspection tooltip in comparison to the newer JupyterLab interface.\
For the moment, to see the full introspection information in Notebook that the MariaDB kernel sends to the frontend, you'll need to hit `shift-tab` then click on the expand button from the tooltip to get the `HTML` representation of the introspection information.

Here's a visual summary of our currently supported introspection capabilities.

**Database introspection**

![](https://1013232429-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FCjGYMsT2MVP4nd3IyW2L%2Fuploads%2Fgit-blob-b7b15e0a36b6505754899a4ddcb1d1a1006b8028%2Fintro1.png?alt=media)

**Table schema and data summary**

![](https://1013232429-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FCjGYMsT2MVP4nd3IyW2L%2Fuploads%2Fgit-blob-2fa9d181a4dc99ba3062c0b216b52710134ae8f2%2Fintro2.png?alt=media)

**Column datatype and sample data**

![](https://1013232429-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FCjGYMsT2MVP4nd3IyW2L%2Fuploads%2Fgit-blob-c9d6b06c074b1486a4e205cd9bef676d87e3c2c4%2Fintro3.png?alt=media)

**SQL function documentation and examples**

![](https://1013232429-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FCjGYMsT2MVP4nd3IyW2L%2Fuploads%2Fgit-blob-b5881e46ef4384f9d369e709b7ef5a82f794d23d%2Fintro4.png?alt=media)

**User accounts introspection**

![](https://1013232429-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FCjGYMsT2MVP4nd3IyW2L%2Fuploads%2Fgit-blob-8532277e59693f7229a80dd38154152109441878%2Fintro5.png?alt=media)

{% @marketo/form formId="4316" %}