AnalyticDB:Use Notebook to develop Spark SQL jobs

Step 1: Create a workspace

1. Log on to the DMS console V5.0.

2. In the upper-left corner, hover over the icon and choose All Features > Data+AI > Notebook.

   Note

   In normal mode, choose Data+AI > Notebook in the top navigation bar.

3. Click Create Workspace. In the dialog box, set the Workspace Name and Region, then click OK.

4. In the Actions column of the workspace, click Go to Workspace.

Step 2: (Optional) Add workspace members

If multiple users share the workspace, add members and assign roles before proceeding.

Step 3: Configure code storage

1. Open the tab and click Storage Management.

2. In the Code Storage section, configure the OSS path where notebook code will be stored.

Step 4: Add a compute resource

1. Open the tab and click Resource Configuration.

2. Click Add Resource and configure the following parameters.

1. Click Save.

2. In the Actions column of the resource, click Start.

Step 5: Register an OSS instance

This step connects your OSS bucket to the workspace so that Notebook can read and write data lake files.

1. Hover over the icon and choose All Features > Data Assets > Instances.

2. Click +New. In the Add Instance dialog box, configure the following parameters.

1. Click Test Connection in the lower-left corner.

   Note

   If the connection test fails, check the instance information based on the error message.

2. After the Successful connection message appears, click Submit.

3. Go back to the workspace and open the tab.

4. On the Data Lake Data tab, click Add OSS and select the bucket you registered.

Step 6: Create a notebook

On the tab, click the icon and select Notebook.

Step 7: Write and run Spark SQL

Each notebook cell runs either Python code or SQL. Switch the cell type using the language selector in the cell toolbar.

1. In a Code cell, install the Delta Lake Python library:

   pip install delta

2. Switch the cell type to SQL and create a database with an OSS storage path:

   Note

   The db_delta database and the sample_data external table created in the next step automatically appear in the AnalyticDB for MySQL cluster. You can query the sample_data table directly from the AnalyticDB for MySQL console.

   CREATE DATABASE db_delta
   LOCATION 'oss://testBucketName/db_delta/';    -- Specify the storage path for data in the db_delta database.

   

3. Switch the cell type to Code and run the following script to create the sample_data external table and load sample data. The data is stored in the OSS path specified by the db_delta database location.

   # -*- coding: utf-8 -*-
   
   import pyspark
   from delta import *
   from pyspark.sql.types import *
   from pyspark.sql.functions import *
   
   
   print("Starting Delta table creation")
   
   data = [
       ("Robert", "Baratheon", "Baratheon", "Storms End", 48),
       ("Eddard", "Stark", "Stark", "Winterfell", 46),
       ("Jamie", "Lannister", "Lannister", "Casterly Rock", 29),
       ("Robert", "Baratheon", "Baratheon", "Storms End", 48),
       ("Eddard", "Stark", "Stark", "Winterfell", 46),
       ("Jamie", "Lannister", "Lannister", "Casterly Rock", 29),
       ("Robert", "Baratheon", "Baratheon", "Storms End", 48),
       ("Eddard", "Stark", "Stark", "Winterfell", 46),
       ("Jamie", "Lannister", "Lannister", "Casterly Rock", 29)
           ]
   
   schema = StructType([
       StructField("firstname", StringType(), True),
       StructField("lastname", StringType(), True),
       StructField("house", StringType(), True),
       StructField("location", StringType(), True),
       StructField("age", IntegerType(), True)
   ])
   
   sample_dataframe = spark.createDataFrame(data=data, schema=schema)
   
   sample_dataframe.delta').mode("overwrite").option('mergeSchema','true').saveAsTable("db_delta.sample_data")

4. Switch the cell type to SQL and query the table to verify the data was loaded:

   SELECT * FROM db_delta.sample_data;