Using DLI to Submit a SQL Job to Query OBS Data

Scenario

DLI can query data stored in OBS. This section describes how to use DLI to submit a SQL job to query OBS data.

To illustrate, create a new file called sampledata.csv and upload it to an OBS bucket. Then, create an elastic resource pool, create queues within it, and use DLI to create a database and table. Finally, use DLI's SQL editor to query 1,000 data records from the table.

Procedure

Table 1 shows the process for submitting a SQL job to query OBS data.

Complete the preparations in Preparations before performing the following operations.

Preparations

• Register a Huawei ID and enable Huawei Cloud services. Make sure your account is not in arrears or frozen.
• Configure an agency for DLI.
  To use DLI, you need to access services such as Object Storage Service (OBS), Virtual Private Cloud (VPC), and Simple Message Notification (SMN). If it is your first time using DLI, you will need to configure an agency to allow access to these dependent services.

  1. Log in to the DLI management console using your account. In the navigation pane on the left, choose Global Configuration > Service Authorization.
  2. On the agency settings page, select the agency permissions under Basic Usage, Datasource, and O&M and click Update.
  3. View and understand the notes for updating the agency and click OK. The DLI agency permissions are updated.
     Figure 1 Configuring an agency for DLI
     
  4. Once configured, you can check the agency dli_management_agency in the agency list on the IAM console.

Step 1: Upload Data to OBS

Upload data files to OBS.

1. Log in to the OBS management console.
2. Create a bucket. In this example, the bucket name is obs1.
   1. Click Create Bucket in the upper right corner.
   2. On the displayed Create Bucket page, specify Region and enter the Bucket Name. Retain the default values for other parameters or adjust them as needed.
      

      Select a region that matches the location of the DLI console.

   3. Click Create Now.
3. Click obs1 to access its Objects tab page.
4. Click Upload Object. In the displayed dialog box, drag a desired file or folder, for example, sampledata.csv to the Upload Object area. Then, click Upload.

   You can create a sampledata.txt file, copy the following content separated by commas (,), and save the file as sampledata.csv.

   product_id,product_name
   113,office_13
   22,book_2
   29,book_9

   After the file is uploaded successfully, the file path is obs://obs1/sampledata.csv.

   For more operations on the OBS console, see the Object Storage Service User Guide.

Step 2: Create an Elastic Resource Pool and Add Queues to the Pool

Step 3: Create a Database

Before querying data, create a database, for example, db1.

The default database is a built-in database. You cannot create the default. database.

1. In the left navigation pane of the DLI management console, choose SQL Editor.
2. In the editing window on the right of the SQL Editor page, enter the following SQL statement and click Execute. Read and agree to the privacy agreement, and click OK.

   create database db1;

   After the database is successfully created, click in the middle pane to refresh the database list. The new database db1 is displayed in the list.

   

   When you execute a query on the DLI management console for the first time, you need to read the privacy agreement. You can perform operations only after you agree to the agreement. For later queries, you will not need to read the privacy agreement again.

Step 4: Create a Table

After database db1 is created, create a table (for example, table1) containing data in the sample file obs://obs1/sampledata.csv stored on OBS in db1.

1. In the SQL editing window of the SQL Editor page, select the default queue and database db1.
2. Enter the following SQL statement in the job editor window and click Execute:

   create table table1 (product_id int, product_name string) using csv options (path 'obs://obs1');

   When creating a table, you only need to specify the OBS storage path where the data file is located, without specifying the file name at the end of the directory.

   After the table is successfully created, click the Databases tab then db1. The created table table1 is displayed in the table list.

Step 5: Query Data

After performing the preceding steps, you can start querying data.

1. In the Table tab on the SQL Editor page, double-click the created table table1. The SQL statement is automatically displayed in the SQL job editing window in the right pane. Run following statement to query 1,000 records in the table1 table:

   select * from db1.table1 limit 1000;

2. Click Execute. The system starts the query.

   After the SQL statement is successfully executed or fails to be executed, you can view the query result on the View Result tab under the SQL job editing window.