Query and analyze data in notebooks - Data Management - Alibaba Cloud Documentation Center

This topic shows you how to use a DMS JupyterNotebook to query and analyze data. A notebook is an interactive document that integrates code, text, and charts on a single page, making it easy to share your work with other users.

Notebook interface

The toolbar provides the following functions:

: Saves your edits in the notebook.
: Inserts a cell above the current one.
: Deletes the selected cell.
: Cuts the selected cell.
: Copies the selected cell.
: Pastes the copied content into the selected cell.
: Runs the selected cell.
: Stops the kernel.
: Restarts the kernel.
: Restarts the kernel and reruns the entire notebook.
: Switches the cell type between Code, SQL, Markdown, and Raw.
: Opens the Copilot chat interface.

DMS Jupyter Notebook features

DMS Jupyter Notebook is compatible with the open-source Jupyter Notebook. It also provides enhanced SQL query and visualization capabilities.

IPython kernel

It functions almost identically to the open-source Jupyter Notebook. You can use pip to install extension packages and access the internet.

Query table data in a notebook using Spark syntax. The following examples show the syntax:
- Syntax 1:
```
df = spark.sql("select * from customer limit 10").show();
```
- Syntax 2:
```
%%spark_sql 
select * from customer limit 10;
```
- Syntax 3:
  In a cell, select SQL > Spark SQL, and then enter your SQL statement.
```
CREATE TABLE IF NOT EXISTS 'default'.'select_2' AS SELECT 2;
```
  Note
  By default, a Spark SQL cell displays a maximum of 3,000 rows. To change this limit, modify the DMS_SPARK_SQL_DEFAULT_LIMIT environment variable by entering the following code in a cell:
```
os.environ['DMS_SPARK_SQL_DEFAULT_LIMIT'] = '3000';
```
In the notebook toolbar, switch the cell type from Code to SQL.
The syntax used in SQL cells is almost identical to logical data warehouse syntax. It supports cross-database queries and real-time analysis. The permission requirements are consistent with the fine-grained permissions in DMS.
Reference variables in SQL and Python cells (using the format${variable_name}), customize variable names, and view variable types. The following section describes how to define, output, and reference variables in a notebook.
- Define and reference variables
  You can reference an IPython variable in SQL by using ${var}, where var is the variable name in IPython.
```
target_table = 'archieve_partitions'
SELECT
    *
FROM
    `datafactory`.`${target_table}`;
```
- Output variables
  In an IPython cell, you can directly use the variable name, which appears in the lower-left corner of the result set. The variable type is pandas.core.frame.DataFrame. To rename the variable, click the variable name and enter a new name.
SQL result sets can be visualized with one click. Both table and chart views are supported.
In the chart view, the left pane is the configuration panel. You can set the Chart type (such as a line chart), the X-axis and Y-axis fields, grouping, and filter conditions. The top of the panel provides Try another and Download image buttons.

PySpark kernel

AnalyticDB Spark is supported by default. Open-source Spark is also supported.

Spark Magic provides the%%help magic command to quickly list supported commands.

Note

Spark Magic is a Jupyter Notebook extension.

AnalyticDB Spark

After you purchase an AnalyticDB cluster (Data Lakehouse Edition), enable and purchase a resource group, and create a database account, you can use the following commands:

Command	Description
`%%info`	Verifies or troubleshoots the AnalyticDB Spark configuration.
`%%sql`	Submits an SQL statement to AnalyticDB Spark.
`%%sql`	Performs DDL operations using the syntax for reading from and writing to C-Store. For more information, see Read from and write to internal tables.
`%%spark`	Submits Python code to AnalyticDB Spark.

Note

An AnalyticDB Spark session remains active for 20 minutes. After this period, the session times out and is deleted. You can restart the kernel to create a new session.

Upload and download files

Use ossutil to upload and download the datasets you need. For information about how to configure ossutil, see Configure ossutil.

Procedure

Create a notebook.
On the Files tab, click and select Notebook.
In the cell editor, write content in SQL, Code, Markdown, or Raw format.
Use Copilot to generate SQL.
After verifying that the generated SQL is correct, execute the statement and view the result set.
1. In the SQL area generated by Copilot, click Execute Query.
  The SQL statement is automatically inserted into the document on the left. The query result also appears below the SQL statement.
  - Table display
    The query returns seven rows from the ORDERS table, including O_ORDERKEY (order ID), O_CUSTKEY (customer ID), and O_ORDERSTATUS (order status). The order status for all records is O.
  - Chart display
    In the data visualization configuration panel, set the chart type to a line chart, select O_ORDERDATE for the X-axis field and [Total] O_ORDERKEY for the Y-axis field. This generates a trend line chart of O_ORDERKEY aggregated by order date.
After the SQL statement runs successfully, you can reference its result set as a variable in other SQL cells. You can also rename the variable.
For example, the SQL query result automatically creates a variable named SQL_949364. In a Python code cell, run SQL_RES=SQL_949364 to assign the result to a custom variable named SQL_RES. The return value is a pandas DataFrame.
Predict data trends.
1. Install the Python machine learning packages by running the following code in a cell.
```
pip install pandas numpy matplotlib scikit-Learn
```
2. After the packages are installed, enter Python code to predict data and visualize the results.

Next steps

Publish an AI agent

FAQ

Q: Who can view notebook documents?

A: Only members of the same workspace and tenant can view them. If the target user is not in the current tenant, add the user to the same tenant as the workspace creator, and then add the user as a workspace member.