DataWorks:Develop with Notebooks

SQL cell

You can write various types of SQL scripts in a cell. After you run a SQL script, the results are printed below the cell.

• Scenario 1: If the SQL does not contain a SELECT statement, only the run log is displayed by default after the cell is executed.

  CREATE TABLE IF NOT EXISTS product (
      product_id BIGINT,
      product_name STRING,
      product_type STRING,
      price DECIMAL(10, 2)
  )
  LIFECYCLE 30; -- The data lifecycle is 30 days. Data is automatically deleted after this period. This setting is optional.

• Scenario 2: If the SQL contains a SELECT statement, the run log is displayed, and the results can be viewed in two ways: as a table or as a visual chart. The system also automatically generates a DataFrame variable from the query results.

  SELECT 
  product_id,
  product_name,
  product_type,
  price 
  FROM product;

  • Generate a DataFrame data object:

    The SQL cell automatically generates a return variable. You can click the df_* variable name in the lower-left corner of the SQL cell to rename the generated DataFrame variable.

    

  • View the SQL query table:After the SQL query runs, the results are displayed in a table by default in the log area.

    The results of an SQL query are displayed in a table in the log area by default.

    

  • View the visual chart for the SQL query

    After the SQL query runs, click the icon on the left of the log area to view a visual chart of the data generated by the query.

    

Python cell

You can write Python scripts in a cell. After you run a Python script, the results are printed below the cell.

• Scenario 1: Print only text output.

  print("Hello World")

• Scenario 2: Use a Pandas DataFrame.

  import pandas as pd
  
  # Define product data, including details: product name, region, and login frequency.
  product_data = {
      'Product_Name': ['DataWorks', 'RDS MySQL', 'EMR Spark', 'MaxCompute'],
      'Product_Region': ['East China 2 (Shanghai)', 'North China 2 (Beijing)', 'South China 1 (Shenzhen)', 'Hong Kong'],
      'Login_Frequency': [33, 22, 11, 44]
  }
  
  # Create a DataFrame from the given data.
  df_products = pd.DataFrame(product_data)
  
  # Print the DataFrame to show the product information.
  print(df_products)

  

• Scenario 3: Plot a chart.

  import matplotlib.pyplot as plt
  
  # Data
  categories = ['DataWorks', 'RDS MySQL', 'MaxCompute', 'EMR Spark', 'Hologres']
  values = [23, 45, 56, 78, 30]
  
  # Create a bar chart
  plt.figure(figsize=(10, 6))
  plt.bar(categories, values, color=['blue', 'green', 'red', 'purple', 'orange'])
  
  # Add a title and labels
  plt.title('Example Bar Chart')
  plt.xlabel('category')
  plt.ylabel('value')
  
  # Show the chart
  plt.show()

  

Markdown cell

• After you finish writing, click the icon to display the formatted Markdown text.

  # DataWorks Notebook

Scenario 1: Establish a MaxCompute MaxFrame Session connection

When developing in a Python cell, you can use the following built-in Magic Command to open the MaxCompute computing resource selector and access the MaxCompute MaxFrame service.

• Use a Magic Command to connect to and access a MaxCompute MaxFrame Session.

  mf_session = %maxframe

• Use a Magic Command in a Python cell to release the MaxCompute MaxFrame connection:

  mf_session.destroy()

Scenario 2: Connect to a MaxCompute computing resource

When developing in a Python cell, you can use the following built-in Magic Command to open the MaxCompute computing resource selector. This lets you interact with MaxCompute using Python for operations such as data loading, queries, and DDL operations.

1. Use a Magic Command to create a MaxCompute connection.

   Entering the following command in a cell opens the MaxCompute computing resource selector.

   o=%odps 

2. Use the obtained MaxCompute computing resource to run a PyODPS script.

   For example, to retrieve all tables in the current project:

   with o.execute_sql('show tables').open_reader() as reader:
       print(reader.raw)

Create a new Python resource

1. Add a Python resource file.

   1. Go to the DataWorks Workspaces page. In the top navigation bar, switch to the destination region. Find the created workspace and click Quick Access > Data Studio in the Actions column to go to DataStudio.

   2. In the navigation pane on the left, click to go to Resource Management.

   3. On the Resource Management page, click the New button or . You can also first Create a Folder to organize your resources, and then right-click the folder and choose New to select the specific resource or function type to create.

   4. Create a MaxCompute Python resource.

      In this example, the Python resource is named hello.py.

2. Edit the content of the Python resource file. The following is sample code:

   # your_script.py
   def greet(name):
       print(f"Hello, {name}!")

   After editing, click Save to save the Python code.

3. After you edit and save the code, click the icon to commit the hello.py resource.

4. After the resource is committed, click the icon to publish the hello.py resource to the development and production environments.

Reference the Python resource

1. Add a Notebook node. For more information, see Create a Notebook node.

2. Add a Python cell to the Notebook. For more information, see Add a cell.

   In the Python cell, write ##@resource_reference{"hello.py"} to reference the new MaxCompute Python resource. The following is sample code:

   # This comment references a Python resource named hello.py during scheduling.
   ##@resource_reference{"hello.py"}
   
   import sys
   import os
   
   # Add the current working directory to the path.
   sys.path.append(os.path.abspath('./hello.py'))  # Or use a relative path, adjust as needed.
   from hello import greet  # Replace with the actual function name.
   greet('DataWorks')
   

3. After you write the code in the Python cell and configure the node scheduling, save and publish the Notebook node.

4. Go to the Operation Center (Workflow). On the Recurring Task O&M > Auto Triggered Tasks page, find the published Notebook node. In the Actions column, click Backfill Data to perform a data backfill for the Notebook node. For more information about data backfill, see Perform data backfill and view the data backfill instance (new version).

5. After the data backfill is complete, you can view the run log of the Notebook node to confirm whether the Python cell was executed successfully.

Connect to a computing resource using Python

In a Notebook's Python cell, you can use the following commands to quickly create, connect to, or release a Livy service on the target computing resource.

Submit and execute Spark code using Python

You can add a Python cell in a Notebook to edit and execute PySpark code.

1. Ensure that you are connected to the target computing resource. In a preceding Python cell, you must have already used a Magic Command (such as %emr_serverless_spark or %maxcompute_spark) to connect to the target computing resource. For more information, see Connect to a computing resource using Python.

2. Write PySpark code.

   In a new Python cell, add the %%spark command to use the Spark computing resource connected in the previous step, and then edit your PySpark code. For example:

   %%spark
   spark.sql("DROP TABLE IF EXISTS dwd_user_info_d")
   spark.sql("CREATE TABLE dwd_user_info_d(id STRING, name STRING, age BIGINT, city STRING)")
   spark.sql("INSERT INTO dwd_user_info_d SELECT '001', 'Jack', 30, 'Beijing'")
   spark.sql("SELECT * FROM dwd_user_info_d").show()
   spark.sql("SELECT COUNT(*) FROM dwd_user_info_d").show()

   Note

   • If a Python cell includes the %%spark command, it can connect to and run on the target computing resource's Spark engine.

   • If a Python cell does not include the %%spark command, it can only run on the local environment.