Analyze GitHub data with Function Compute

In this tutorial, you can use an ETL workflow template to import the task code with a single click. After you import the template, you can go to the target workspace to perform subsequent operations, such as task operations and maintenance (O&M).

• Only users with the Workspace Administrator role can import ETL templates to a target workspace. For more information about how to grant the Workspace Administrator role to an account, see Manage permissions for workspace-level modules.

• For more information about how to import an ETL workflow template, see Get started with ETL workflows.

• To access the ETL workflow template, click Top 10 GitHub programming languages.

Prerequisites

Before you begin, make sure that you have completed the following tasks:

• Activate DataWorksand create a workspace. This tutorial uses a standard-mode workspace as an example. For more information about the differences between workspaces in simple mode and standard mode, see Differences between workspaces in standard mode and workspaces in simple mode.

• Add a resource group for data integration and bind it to the workspace.

• Activate MaxCompute.

• Activate Function Compute (FC).

• Activate Object Storage Service (OSS) and create an OSS bucket.

• Ensure your account has the AliyunOSSFullAccess (for OSS) and AliyunFCFullAccess (for FC) permissions. For more information, see View the permissions of a RAM user and Manage the permissions of a RAM user.

Step 1: Go to the target workspace

1. Log on to the DataWorks console.

2. In the upper-left corner, select a region based on your business requirements.

3. In the left-side navigation pane, click Workspace. Click the name of the target workspace to go to its details page.

Step 2: Create and bind a MaxCompute data source

1. In the left-side navigation pane, click Management Center to go to the configuration page.

2. In the left-side navigation pane, click Data Source.

3. Click Add data source, select MaxCompute as the data source type, and then follow the on-screen instructions to bind MaxCompute compute resources.

4. Go to Data Studio > Computing Resource, find the MaxCompute data source that you created, and click Associate.

Step 3: Create an OSS data source

1. In the left-side navigation pane, click Management Center to go to the configuration page.

2. In the left-side navigation pane, click Data Source.

3. Click Add data source, select OSS as the data source type, and then create an OSS data source.

   Parameter	Description
   Data source name	Enter a name for the data source. It must start with a letter, be up to 60 characters long, and contain only letters, digits, and underscores (_).
   Data source description	A brief description of the data source, up to 80 characters in length.
   Applicable environment	This tutorial uses a standard-mode workspace, so select both Development and Production.
   Endpoint	This tutorial uses the China (Shanghai) region, so enter http://oss-cn-shanghai.aliyuncs.com. For endpoints in other regions, see Regions and endpoints.
   Bucket	Enter the name of an OSS bucket. If you do not have an available bucket, create a bucket.
   Access mode	RAM role authorization Access key mode
   Select role	If you set RAM role authorization mode to RAM role authorization mode, select a RAM role. For more information, see Configure a data source by using RAM role-based authorization.
   Access key ID	If you set Access Key mode to Access Key mode, you must enter the access key information. For more information, see View the access key information of a RAM user.
   Access key secret
   Resource group connectivity	On the Data Integration tab, find your bound data integration resource group and, in the Operation column, click Test Connectivity. The Connected state should change to Connectable after the test is complete.

Step 4: Configure the use case

In the left-side navigation pane of the DataWorks console, choose Lab > ETL workflow templates. Click the Top 10 GitHub programming languages template, and then click Load Template to configure the template parameters.

After you configure the parameters, click OK to go to the business process page.

Step 5: Debug the workflow

1. At the top of the business process page, click Running to debug and run the business process.

2. After the run is complete, check the recipient's email for the data processing results.

Prerequisites

Before you begin, activate the required Alibaba Cloud services and complete the following steps.

• Activate DataWorks and create a workspace. This tutorial uses a workspace in standard mode as an example. The procedure for a workspace in simple mode is similar. For more information, see Activate DataWorks and Create a workspace.

• Activate MaxCompute and create a MaxCompute project. For more information, see Activate MaxCompute.

• Activate Function Compute. For more information, see Step 1: Activate Function Compute.

• Activate Object Storage Service (OSS) and create an OSS bucket. For more information, see Quick start and Step 1: Create a bucket.

OSS: Create a bucket

Log on to the OSS console. On the Bucket List page, click Create Bucket. Configure the Bucket Name and Region, and then click Determine to create the OSS bucket.

You can keep the default values for the other settings. Set Storage Class to Standard, Redundancy Type to Zone-Redundant Storage, and Read/Write Permissions to Private. Keep Versioning and Server-Side Encryption disabled or set to None.

Function Compute: Create and develop a function

1. Log on to the Function Compute console to create a service and grant it OSS permissions.

   The function reads data from an OSS bucket and sends it to a specified email address. To enable this, you must grant the Function Compute service the necessary permissions to access OSS.

   1. In the upper-right corner, click Back to Function Compute 2.0 to return to the Function Compute 2.0 console.

   2. On the Function Compute 2.0 page, go to the Services & Functions page. In the upper-left corner, select a region and click Create Service. Configure the service Name and click Determine.

   3. Click the service you created. In the navigation pane on the left, click Service Details. In the Configure Role section, click Edit. Select AliyunFcDefaultRole as the service role and click Save. Return to the Service Details tab. In the Configure Role section, click the AliyunFcDefaultRole service role link to open the role page in the Resource Access Management (RAM) console.

   4. Click Add Authorization and select the AliyunOSSReadOnlyAccess system policy. Follow the on-screen instructions to add the policy. This grants the Function Compute service read-only access to OSS.

2. Create a function and develop the function logic.

   1. Create a function.

      Return to the service page in the Function Compute console. In the navigation pane on the left, click the Function Management tab and click Create Function. Configure the Function Name and set the Environment to Python 3.9. You can keep the default values for the other parameters. After the configuration is complete, click Create.

   2. Install dependencies for the function environment.

      Note

      This tutorial requires the Alibaba Cloud oss2 package and the open-source pandas package. The oss2 package is pre-installed in the Python 3.9 runtime. However, you must install the pandas package by following the steps below.

      Click the function you created. On its Function Configuration tab, click Edit in the Layer section. Click Add Layer, select Add Official Public Layer, and then select Pandas1.x. Finally, click Determine.

   3. Return to the function page and click the Function Code tab. After the WebIDE for Python is loaded, copy the following code into the Index.py file and modify the OSS internal endpoint and email parameters.

      # -*- coding: utf-8 -*-
      import logging
      import json
      import smtplib
      import oss2
      import pandas as pd
      from email.mime.text import MIMEText
      from email.mime.multipart import MIMEMultipart
      from email.mime.base import MIMEBase
      from email.mime.text import MIMEText
      from email.utils import COMMASPACE
      from email import encoders
      def handler(event, context):
        evts = json.loads(event)
        bucket_name = evts["bucketName"]
        file_path = evts["filePath"]
        auth = oss2.StsAuth(context.credentials.access_key_id, context.credentials.access_key_secret, context.credentials.security_token)
        endpoint = 'https://oss-{}-internal.aliyuncs.com'.format(context.region)
        bucket = oss2.Bucket(auth, endpoint, bucket_name)
        file_name = file_path
        for obj in oss2.ObjectIteratorV2(bucket, prefix=file_path):
          if not obj.key.endswith('/'):
            file_name = obj.key
        csv_file = bucket.get_object(file_name)
        logger = logging.getLogger()
        logger.info('event: %s', evts)
        mail_host = 'smtp.***.com'                 ## Email service host
        mail_port = '465';                         ## SMTP port
        mail_username = 'sender_****@163.com'      ## Authentication username (your full email address)
        mail_password = 'EWEL******KRU'            ## Authentication password (your email's SMTP authorization code)
        mail_sender = 'sender_****@163.com'        ## Sender email address
        mail_receivers = ['receiver_****@163.com'] ## Recipient email address
        message = MIMEMultipart('alternative')
        message['Subject'] = 'GitHub Data Processing Results'
        message['From'] = mail_sender
        message['To'] = mail_receivers[0]
        html_message = generate_mail_content(evts, csv_file)
        message.attach(html_message)
        # Send email
        smtpObj = smtplib.SMTP_SSL(mail_host + ':' + mail_port)
        smtpObj.login(mail_username,mail_password)
        smtpObj.sendmail(mail_sender,mail_receivers,message.as_string())
        smtpObj.quit()
        return 'mail send success'
      def generate_mail_title(evt):
        mail_title=''
        if 'mailTitle' in evt.keys():
            mail_title=evt['mailTitle']
        else:
            logger = logging.getLogger()
            logger.error('msg not present in event')
        return mail_title
      def generate_mail_content(evts, csv_file):
        headerList = ['Language', 'Commit Count']
         # Read csv file content
        dumped_file = pd.read_csv(csv_file, names=headerList)
        # Convert DataFrame to HTML table
        table_html = dumped_file.to_html(index=False)
        mail_title=generate_mail_title(evts)
        # Email body
        html = f"""
        <html>
        <body>
        <h2>{mail_title}</h2>
        <p>Here are the top 10 languages on GitHub in the past hour:</p>
        {table_html}
        </body>
        </html>
        """
        # Attach HTML message
        html_message = MIMEText(html, 'html')
        return html_message

      Note

      The DataWorks Function Compute node passes the values for these variables, so you do not need to modify them in the code.

      Parameter	Description
      OSS internal endpoint (Line 20)	Replace 'https://oss-{}-internal.aliyuncs.com' with the OSS internal endpoint for your region. For example, if you are in the China (Shanghai) region, change the parameter to 'https://oss-cn-shanghai-internal.aliyuncs.com'. For information about OSS internal endpoints for different regions, see Regions and endpoints.
      Email parameters (Lines 31–36)	Modify the following parameters: Modify lines 31–35 to provide your email service address, SMTP port number, email username, and password. Modify line 36 to specify the recipient's email address. Note You can find this information in the help documentation for your email provider. For example, if you use a 163.com email address, see What is an SMTP server? and How to use an authorization code for more information.

   4. After you finish developing the code, click Deploy Code.

DataWorks: Create data source and compute resource

1. Create a MySQL data source.

   The public GitHub data for this tutorial is in a public MySQL database. You must create a MySQL data source to connect to this database and sync data to MaxCompute.

   1. 1. Log on to the DataWorks console. In the target region, click More > Management Center in the left-side navigation pane. Select a workspace from the drop-down list and click Go to Management Center.

      2. On the Workspace Management page, click Data Sources in the left-side navigation pane to open the data source page.

   2. Click Add data source and select MySQL as the data source type. Configure the data source name and other parameters as prompted. The key parameters are described in the following table.

      Parameter	Description
      Data Source Type	Select User-created Data Store with Public IP Addresses.
      Data Source Name	Enter a custom name. This tutorial uses github_events_share as an example.
      JDBC URL	Set this parameter to jdbc:mysql://rm-bp1z69dodhh85z9qa.mysql.rds.aliyuncs.com:3306/github_events_share. Important This data source can be used only for reading data in data synchronization scenarios and is not supported by other modules.
      Username	Set this parameter to workshop.
      Password	Set this parameter to workshop#2017. This password is for demonstration purposes only. Do not use it in your actual business.
      Authentication Method	No Authentication.
      Resource group connectivity	For the public resource group for data integration, click Test Connectivity. Wait for the connectivity status to become Connectable.

2. Create a MaxCompute compute resource.

   To sync GitHub data to MaxCompute, you must create a MaxCompute compute resource.

   1. Log on to the DataWorks console. In the target region, click More > Management Center in the left-side navigation pane. Select a workspace from the drop-down list and click Go to Management Center.

   2. On the management page, in the navigation pane on the left, click Computing Resource to go to the Computing Resource page in Data Development.

   3. Click Create Computing Resource and select MaxCompute as the resource type. Configure the resource name, the corresponding MaxCompute project, and other parameters as prompted. For more information, see Bind a MaxCompute compute engine.

DataWorks: Create business flow and develop tasks

1. Log on to the DataWorks console. In the target region, click Data Development and O&M > Data Development in the left-side navigation pane. Select a workspace from the drop-down list and click Go to Data Development.

2. Create a business flow.

   In the upper-left corner, choose Create > Create Workflow. Configure the Workflow Name and click Create.

3. Create nodes and configure dependencies.

   After creating all the nodes, connect them on the canvas by dragging lines in the following order to configure their dependencies: di_GitToMc → sql_last1h → fc_sendmail.

   1. Double-click the business flow that you created to open its page.

   2. On the business flow page, click Create Node. Drag a Batch Synchronization node to the canvas. Configure the node name and click OK.

   3. Repeat the preceding step to create an ODPS SQL node and a Function Compute node.

4. Configure the Batch Synchronization node.

   1. Double-click the Batch Synchronization node in the business flow to open its configuration page.

   2. Configure the network and resources for the batch synchronization task.

      Parameter	Description
      Data Source	Select MySQL as the data source and choose the MySQL data source that you created earlier.
      Data going	Select MaxCompute as the data destination and choose the MaxCompute data source that you created.
      My Resources	In the lower-right corner, choose More > Shared Resource Group (Shared Resource Group for Data Integration (Debugging)).

      After you complete the configuration, click Next and follow the on-screen instructions to complete the network connectivity test.

   3. Configure the batch synchronization task. The key parameters are described in the following table. You can keep the default values for the other parameters.

      Parameter	Description
      Data Source	Table: Select github_public_event from the drop-down list. Data Filter: Set this parameter to created_at >'${day1} ${hour1}' and created_at<'${day2} ${hour2}'
      Data going	Table: Click Generate Destination Table Schema. In the dialog box that appears, click Create Table. Partition information: Set this parameter to pt=${day_hour}.

   4. In the right-side pane, click Scheduling and configure the scheduling parameters. The key parameters are described in the following table. You can keep the default values for the other parameters.

      Parameter	Description
      Scheduling Parameter	Click Load Parameters in Code, add the following five parameters, and configure their value logic as follows: day1: $[yyyy-mm-dd-1/24] hour1: $[hh24-1/24] day2: $[yyyy-mm-dd] hour2: $[hh24] day_hour: $[yyyymmddhh24]
      Schedule	Scheduling Cycle: Set this to Hour. Rerun attribute: Allow rerunning after both successful and failed attempts.
      Scheduling Dependency	Select Add Root Node.

   5. In the upper-right corner, click Save.

5. Configure the ODPS SQL node.

   1. Double-click the ODPS SQL node in the business flow to open its configuration page.

   2. Paste the following sample code into the node.

      Important

      The following sample code creates an OSS external table to store the processed data. If this is your first time using an OSS external table, you must grant permissions to your account. Otherwise, the business flow will fail. For authorization instructions, see Grant permissions to a RAM role for OSS.

      -- 1. Create an ODPS external table for OSS to receive the processed results from the public GitHub dataset.
      -- This external table is named odps_external and is stored in the OSS bucket that you created in a previous step. 
      -- In this example, the OSS bucket name is xc-bucket-demo2. You must replace it with your actual bucket name.
      CREATE EXTERNAL TABLE IF NOT EXISTS odps_external(
      	language STRING COMMENT 'repo full name: owner/Repository_name',
      	num STRING COMMENT 'commit count'
      ) 
      partitioned by (
      direction string
      )
      STORED BY  'com.aliyun.odps.CsvStorageHandler' 
      WITH SERDEPROPERTIES(
      	'odps.text.option.header.lines.count'='0',
      	 'odps.text.option.encoding'='UTF-8',
      	 'odps.text.option.ignore.empty.lines'='false',
      	 'odps.text.option.null.indicator'='') 
      LOCATION 'oss://oss-cn-shanghai-internal.aliyuncs.com/${YOUR_BUCKET_NAME}/odps_external/';
      -- 2. Process the GitHub data synced to MaxCompute and write it to the OSS external table.
      -- Get the top 10 programming languages and their commit counts on GitHub in the past hour.
      SET odps.sql.unstructured.oss.commit.mode=true;
      INSERT INTO TABLE odps_external partition (direction='${day_hour}') 
      SELECT
          language,
          COUNT(*) AS num
      FROM
          github_public_event
      WHERE language IS NOT NULL AND pt='${day_hour}' 
      GROUP BY
          language
      ORDER BY num DESC
      limit 10;

   3. In the right-side pane, click Scheduling and configure the scheduling parameters. The key parameters are described in the following table. You can keep the default values for the other parameters.

      Parameter	Description
      Scheduling Parameter	Click Load Parameters in Code, add the following parameters, and configure their value logic: YOUR_BUCKET_NAME: The value of this parameter is the name of the OSS bucket that you created. day_hour: $[yyyymmddhh24]
      Schedule	Scheduling Cycle: Set this to Hour. Rerun attribute: Allow rerunning after both successful and failed attempts.

   4. In the upper-right corner, click Save.

6. Configure the Function Compute node.

   1. Double-click the Function Compute node in the business flow to open its configuration page.

   2. Configure the Function Compute node task.

      Parameter	Description
      Select Service	Select the service that you created in the Function Compute console.
      Select Function	Select the function that you created in the Function Compute console.
      Invocation Method	Select and synchronizes.
      Variable	Set this parameter to the following content. { "bucketName": "${YOUR_BUCKET_NAME}", "filePath": "odps_external/direction=${day_hour}/", "mailTitle":"Top 10 GitHub programming languages and their commit counts in the past hour" }

   3. In the right-side pane, click Scheduling and configure the scheduling parameters. The key parameters are described in the following table. You can keep the default values for the other parameters.

      Parameter	Description
      Scheduling Parameter	Click Add Parameter, add the following parameters, and configure their value logic as follows: YOUR_BUCKET_NAME: The value of this parameter is the name of the OSS bucket that you created. day_hour: $[yyyymmddhh24]
      Schedule	Scheduling Cycle: Set this to Hour. Rerun attribute: Allow rerunning after both successful and failed attempts.

   4. In the upper-right corner, click Save.

DataWorks: Debug workflow

1. On the DataWorks Data Development page, double-click the business flow that you created to open its page.

2. At the top of the page, click the Running button to debug and run the entire business flow.

3. After the run is complete, log on to the recipient's email account to check for the email containing the data processing results.

DataWorks: Submit and deploy workflow

(Optional) To periodically sync and process data in MaxCompute and then email the results, you must submit and deploy the business flow to the DataWorks Operations Center.

1. On the Data Development page, double-click the business flow that you created to open its page.

2. On the business flow page, click Submission. Follow the prompts to submit and deploy the business flow to the Operations Center. For more information, see Deploy tasks.

   The business flow will then run periodically according to its configured scheduling cycle.