# Use the Athena notebook editor Notebook editor **Note** The Athena notebook editor is supported in the Pyspark engine version 3. For using notebooks with Apache Spark version 3.5, see [SageMaker Notebooks](https://docs.aws.amazon.com/sagemaker-unified-studio/latest/userguide/what-is-sagemaker-unified-studio.html). You manage your notebooks in the Athena notebook explorer and edit and run them in sessions using the Athena notebook editor. You can configure DPU usage for your notebook sessions according to your requirements. When you stop a notebook, you terminate the associated session. All files are saved, but changes underway in declared variables, functions and classes are lost. When you restart the notebook, Athena reloads the notebook files and you can run your code again. The Athena notebook editor is an interactive environment for writing and running code. The following sections describe the features of the environment. ## Understand notebook sessions and calculations Each notebook is associated with a single Python kernel and runs Python code. A notebook can have one or more cells that contain commands. To run the cells in a notebook, you first create a session for the notebook. Sessions keep track of the variables and state of notebooks. Running a cell in a notebook means running a calculation in the current session. Calculations progress the state of the notebook and may perform tasks like reading from Amazon S3 or writing to other data stores. As long as a session is running, calculations use and modify the state that is maintained for the notebook. When you no longer need the state, you can end a session. When you end a session, the notebook remains, but the variables and other state information are destroyed. If you need to work on multiple projects at the same time, you can create a session for each project, and the sessions will be independent from each other. Sessions have dedicated compute capacity, measured in DPU. When you create a session, you can assign the session a number of DPUs. Different sessions can have different capacities depending on the requirements of the task. ## Switch between command mode and edit mode The notebook editor has a modal user interface: an edit mode for entering text into a cell, and a command mode for issuing commands to the editor itself like copy, paste, or run. To use edit mode and command mode, you can perform the following tasks: + To enter edit mode, press **ENTER**, or choose a cell. When a cell is in edit mode, the cell has a green left margin. + To enter command mode, press **ESC**, or click outside of a cell. Note that commands typically apply only to the currently selected cell, not to all cells. When the editor is in command mode, the cell has a blue left margin. + In command mode, you can use keyboard shortcuts and the menu above the editor, but not enter text into individual cells. + To select a cell, choose the cell. + To select all cells, press **Ctrl\$1A** (Windows) or **Cmd\$1A** (Mac). ## Use actions in the notebook editor menu The icons in the menu at the top of the notebook editor offer the following options: + **Save** – Saves the current state of the notebook. + **Insert cell below** – Adds a new (empty) cell below the currently selected one. + **Cut selected cells** – Removes the selected cell from its current location and copies the cell to memory. + **Copy selected cells** – Copies the selected cell to memory. + **Paste cells below** – Pastes the copied cell below the current cell. + **Move selected cells up** – Moves the current cell above the cell above. + **Move selected cells down** – Moves the current cell below the cell below. + **Run** – Runs the current (selected) cell. The output displays immediately below the current cell. + **Run all** – Runs all cells in the notebook. The output for each cell displays immediately below the cell. + **Stop (Interrupt the kernel)** – Stops the current notebook by interrupting the kernel. + **Format option** – Selects the cell format, which can be one of the following: + **Code** – Use for Python code (the default). + **Markdown** – Use for entering text in [GitHub-style markdown](https://docs.github.com/en/get-started/writing-on-github) format. To render the markdown, run the cell. + **Raw NBConvert** – Use for entering text in unmodified form. Cells marked as **Raw NBConvert** can be converted into a different format like HTML by the Jupyter [nbconvert](https://nbconvert.readthedocs.io/en/latest/usage.html) command line tool. + **Heading** – Use to change the heading level of the cell. + **Command palette** – Contains Jupyter notebook commands and their keyboard shortcuts. For more information about the keyboard shortcuts, see the sections later in this document. + **Session** – Use options in this menu to [view](notebooks-spark-getting-started.md#notebooks-spark-getting-started-viewing-session-and-calculation-details) the details for a session, [edit session parameters](notebooks-spark-getting-started.md#notebooks-spark-getting-started-editing-session-details), or [terminate](notebooks-spark-getting-started.md#notebooks-spark-getting-started-terminating-a-session) the session. ## Use command mode keyboard shortcuts for productivity The following are some common notebook editor command mode keyboard shortcuts. These shortcuts are available after pressing **ESC** to enter command mode. To see a full list of commands available in the editor, press **ESC \$1 H**. **** | Key | Action | | --- | --- | | 1 - 6 | Change the cell type to markdown and set the heading level to the number typed | | a | Create a cell above the current cell | | b | Create a cell below the current cell | | c | Copy the current cell to memory | | d d | Delete the current cell | | h | Display the keyboard shortcut help screen | | j | Go one cell down | | k | Go one cell up | | m | Change the current cell format to markdown | | r | Change the current cell format to raw | | s | Save the notebook | | v | Paste memory contents under the current cell | | x | Cut the selected cell or cells | | y | Change the cell format to code | | z | Undo | | Ctrl\$1Enter | Run the current cell and enter command mode | | Shift\$1Enter or Alt\$1Enter | Run the current cell and create a new cell below the output, and enter the new cell in edit mode | | Space | Page down | | Shift\$1Space | Page up | | Shift \$1 L | Toggle the visibility of line numbers in cells | ## Customize command mode shortcuts The notebook editor has an option to customize command mode keyboard shortcuts. **To edit command mode shortcuts** 1. From the notebook editor menu, the choose the **Command palette**. 1. From the command palette, choose the **Edit command mode keyboard shortcuts** command. 1. Use the **Edit command mode shortcuts** interface to map or remap commands that you want to the keyboard. To see instructions for editing command mode shortcuts, scroll to the bottom of the **Edit command mode shortcuts** screen. For information about using magic commands in Athena for Apache Spark, see [Use magic commands](notebooks-spark-magics.md). **Topics** + [ ## Understand notebook sessions and calculations ](#notebooks-spark-sessions-and-calculations) + [ ## Switch between command mode and edit mode ](#notebooks-spark-command-mode-vs-edit-mode) + [ ## Use actions in the notebook editor menu ](#notebooks-spark-notebook-editor-menu) + [ ## Use command mode keyboard shortcuts for productivity ](#notebooks-spark-command-mode-keyboard-shortcuts) + [ ## Customize command mode shortcuts ](#notebooks-spark-editing-command-mode-shortcuts) + [ # Use magic commands ](notebooks-spark-magics.md) # Use magic commands Magic commands Magic commands, or magics, are special commands that you can run in a notebook cell. For example, `%env` shows the environment variables in a notebook session. Athena supports the magic functions in IPython 6.0.3. This section shows some key magic commands in Athena for Apache Spark. + To see a list of magic commands in Athena, run the command **%lsmagic** in a notebook cell. + For information about using magics to create graphs in Athena notebooks, see [Use magics to create data graphs](notebooks-spark-magics-graphs.md). + For information about additional magic commands, see [Built-in magic commands](https://ipython.readthedocs.io/en/stable/interactive/magics.html) in the IPython documentation. **Note** Currently, the `%pip` command fails when executed. This is a known issue. **Topics** + [Cell magics](notebooks-spark-magics-cell-magics.md) + [Line Magics](notebooks-spark-magics-line-magics.md) + [Graph magics](notebooks-spark-magics-graphs.md) # Use cell magics Cell magics Magics that are written on several lines are preceded by a double percent sign (`%%`) and are called cell magic functions or cell magics. ## %%sql This cell magic allows to run SQL statements directly without having to decorate it with Spark SQL statement. The command also displays the output by implicitly calling `.show()` on the returned dataframe. ![\[Using %%sql.\]](http://docs.aws.amazon.com/athena/latest/ug/images/notebooks-spark-magics-1.png) The `%%sql` command auto truncates column outputs to a width of 20 characters. Currently, this setting is not configurable. To work around this limitation, use the following full syntax and modify the parameters of the `show` method accordingly. ``` spark.sql("""YOUR_SQL""").show(n=number, truncate=number, vertical=bool) ``` + **n** `int`, optional. The number of rows to show. + **truncate** – `bool` or `int`, optional – If `true`, truncates strings longer than 20 characters. When set to a number greater than 1, truncates long strings to the length specified and right aligns cells. + **vertical** – `bool`, optional. If `true`, prints output rows vertically (one line per column value). # Use line magics Line Magics Magics that are on a single line are preceded by a percent sign (`%`) and are called line magic functions or line magics. ## %help Displays descriptions of the available magic commands. ![\[Using %help.\]](http://docs.aws.amazon.com/athena/latest/ug/images/notebooks-spark-magics-2.png) ## %list\$1sessions Lists the sessions associated with the notebook. The information for each session includes the session ID, session status, and the date and time that the session started and ended. ![\[Using %list_sessions.\]](http://docs.aws.amazon.com/athena/latest/ug/images/notebooks-spark-magics-3.png) ## %session\$1id Retrieves the current session ID. ![\[Using session_id.\]](http://docs.aws.amazon.com/athena/latest/ug/images/notebooks-spark-magics-4.png) ## %set\$1log\$1level Sets or resets the logger to use the specified log level. Possible values are `DEBUG`, `ERROR`, `FATAL`,`INFO`, and `WARN` or `WARNING`. Values must be uppercase and must not be enclosed in single or double quotes. ![\[Using %set_log_level.\]](http://docs.aws.amazon.com/athena/latest/ug/images/notebooks-spark-magics-5.png) ## %status Describes the current session. The output includes the session ID, session state, workgroup name, PySpark engine version, and session start time. This magic command requires an active session to retrieve session details. Following are the possible values for status: **CREATING** – The session is being started, including acquiring resources. **CREATED** – The session has been started. **IDLE** – The session is able to accept a calculation. **BUSY** – The session is processing another task and is unable to accept a calculation. **TERMINATING** – The session is in the process of shutting down. **TERMINATED** – The session and its resources are no longer running. **DEGRADED** – The session has no healthy coordinators. **FAILED** – Due to a failure, the session and its resources are no longer running. ![\[Using %status.\]](http://docs.aws.amazon.com/athena/latest/ug/images/notebooks-spark-magics-6.png) # Use magics to create data graphs Graph magics The line magics in this section specialize in rendering data for particular types of data or in conjunction with graphing libraries. ## %table You can use the `%table` magic command to display dataframe data in table format. The following example creates a dataframe with two columns and three rows of data, then displays the data in table format. ![\[Using the %table magic command.\]](http://docs.aws.amazon.com/athena/latest/ug/images/notebooks-spark-magics-graphs-1.png) ## %matplot [Matplotlib](https://matplotlib.org/) is a comprehensive library for creating static, animated, and interactive visualizations in Python. You can use the `%matplot` magic command to create a graph after you import the matplotlib library into a notebook cell. The following example imports the matplotlib library, creates a set of x and y coordinates, and then uses the use the `%matplot` magic command to create a graph of the points. ``` import matplotlib.pyplot as plt x=[3,4,5,6,7,8,9,10,11,12] y= [9,16,25,36,49,64,81,100,121,144] plt.plot(x,y) %matplot plt ``` ![\[Using the %matplot magic command.\]](http://docs.aws.amazon.com/athena/latest/ug/images/notebooks-spark-magics-graphs-2.png) ### Use the matplotlib and seaborn libraries together [Seaborn](https://seaborn.pydata.org/tutorial/introduction) is a library for making statistical graphics in Python. It builds on top of matplotlib and integrates closely with [pandas](https://pandas.pydata.org/) (Python data analysis) data structures. You can also use the `%matplot` magic command to render seaborn data. The following example uses both the matplotlib and seaborn libraries to create a simple bar graph. ``` import matplotlib.pyplot as plt import seaborn as sns x = ['A', 'B', 'C'] y = [1, 5, 3] sns.barplot(x, y) %matplot plt ``` ![\[Using %matplot to render seaborn data.\]](http://docs.aws.amazon.com/athena/latest/ug/images/notebooks-spark-magics-graphs-3.png) ## %plotly [Plotly](https://plotly.com/python/) is an open source graphing library for Python that you can use to make interactive graphs. You use the `%ploty` magic command to render ploty data. The following example uses the [StringIO](https://docs.python.org/3.13/library/io.html#io.StringIO), plotly, and pandas libraries on stock price data to create a graph of stock activity from February and March of 2015. ``` from io import StringIO csvString = """ Date,AAPL.Open,AAPL.High,AAPL.Low,AAPL.Close,AAPL.Volume,AAPL.Adjusted,dn,mavg,up,direction 2015-02-17,127.489998,128.880005,126.919998,127.830002,63152400,122.905254,106.7410523,117.9276669,129.1142814,Increasing 2015-02-18,127.629997,128.779999,127.449997,128.720001,44891700,123.760965,107.842423,118.9403335,130.0382439,Increasing 2015-02-19,128.479996,129.029999,128.330002,128.449997,37362400,123.501363,108.8942449,119.8891668,130.8840887,Decreasing 2015-02-20,128.619995,129.5,128.050003,129.5,48948400,124.510914,109.7854494,120.7635001,131.7415509,Increasing 2015-02-23,130.020004,133,129.660004,133,70974100,127.876074,110.3725162,121.7201668,133.0678174,Increasing 2015-02-24,132.940002,133.600006,131.169998,132.169998,69228100,127.078049,111.0948689,122.6648335,134.2347981,Decreasing 2015-02-25,131.559998,131.600006,128.149994,128.789993,74711700,123.828261,113.2119183,123.6296667,134.0474151,Decreasing 2015-02-26,128.789993,130.869995,126.610001,130.419998,91287500,125.395469,114.1652991,124.2823333,134.3993674,Increasing 2015-02-27,130,130.570007,128.240005,128.460007,62014800,123.510987,114.9668484,124.8426669,134.7184854,Decreasing 2015-03-02,129.25,130.279999,128.300003,129.089996,48096700,124.116706,115.8770904,125.4036668,134.9302432,Decreasing 2015-03-03,128.960007,129.520004,128.089996,129.360001,37816300,124.376308,116.9535132,125.9551669,134.9568205,Increasing 2015-03-04,129.100006,129.559998,128.320007,128.539993,31666300,123.587892,118.0874253,126.4730002,134.8585751,Decreasing 2015-03-05,128.580002,128.75,125.760002,126.410004,56517100,121.539962,119.1048311,126.848667,134.5925029,Decreasing 2015-03-06,128.399994,129.369995,126.260002,126.599998,72842100,121.722637,120.190797,127.2288335,134.26687,Decreasing 2015-03-09,127.959999,129.570007,125.059998,127.139999,88528500,122.241834,121.6289771,127.631167,133.6333568,Decreasing 2015-03-10,126.410004,127.220001,123.800003,124.510002,68856600,119.71316,123.1164763,127.9235004,132.7305246,Decreasing """ csvStringIO = StringIO(csvString) from io import StringIO import plotly.graph_objects as go import pandas as pd from datetime import datetime df = pd.read_csv(csvStringIO) fig = go.Figure(data=[go.Candlestick(x=df['Date'], open=df['AAPL.Open'], high=df['AAPL.High'], low=df['AAPL.Low'], close=df['AAPL.Close'])]) %plotly fig ``` ![\[Using the %ploty magic command.\]](http://docs.aws.amazon.com/athena/latest/ug/images/notebooks-spark-magics-graphs-4.png)