Submitting a SQL Job

Updated on 2022-07-04 GMT+08:00

View PDF

Use DLI to submit a SQL job to query data. The general procedure is as follows:

Step 1: Logging in to the Cloud

Step 2: Uploading Data to OBS

Step 3: Logging In to the DLI Management Console

Step 4: Creating a Queue

Step 5: Creating a Database

Step 6: Creating a Table

Step 7: Querying Data

The following illustrates how to query OBS data using DLI. Operations of DLI data query are similar.

Step 1: Logging in to the Cloud

Open the DLI home page.
On the login page, enter the Username and Password, and click Login.

Step 2: Uploading Data to OBS

DLI allows you to query data stored on OBS. Before querying the data, you need to upload the data file to OBS.

In the services displayed, click Object Storage Service in Storage.
The OBS console page is displayed.
Create a bucket. The bucket name is globally unique. In this example, assume that the bucket name is obs1.
1. Click Create Bucket in the upper right corner.
2. On the Create Bucket page that is displayed, enter the Bucket Name.
3. Click Create Now.
Click obs1 to switch to the Summary page.
In the left navigation tree, click Objects. Click Upload Object. In the displayed dialog box, drag files or folders to upload or add file, for example, sampledata.csv to the file upload box. Then, click Upload.
After the file is uploaded successfully, the file path to be analyzed is obs://obs1/sampledata.csv.

For more information about OBS, see the Object Storage Service Console Operation Guide.

You are advised to use an OBS tool, such as OBS Browser+ or obsutil, to upload large files because OBS Console has restrictions on the file size and quantity. OBS Browser+ is a graphical tool that provides complete functions for managing your buckets and objects in OBS. You are advised to use this tool to create buckets or upload objects. obsutil is a command line tool for accessing and managing OBS resources. If you are familiar with command line interface (CLI), obsutil is recommended as an ideal tool for batch processing and automated tasks. For details about how to upload files to OBS, see the OBS Tool Guide.

Step 3: Logging In to the DLI Management Console

In the service list displayed, click Data Lake Insight in Enterprise Intelligence.
The DLI management console page is displayed. If you log in to the DLI management console for the first time, you need to be authorized to access OBS.

Step 4: Creating a Queue

A queue is the basis for using DLI. Before executing an SQL job, you need to create a queue.

An available queue default is preset in DLI.
You can also create queues as required.
1. On the DLI management console, click SQL Editor in the navigation pane on the left. The SQL Editor page is displayed.
2. On the left pane of the displayed SQL Editor page, click to the right of Queues.
  For details about how to create a queue, see Creating a Queue.

Step 5: Creating a Database

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

NOTE:

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

On the DLI management console, click SQL Editor in the navigation pane on the left. The SQL Editor page is displayed.
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 database db1 is successfully created, db1 will be displayed in the Database drop-down list box of Databases.

NOTE:

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 6: Creating a Table

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

In the upper right corner of the SQL Editor page, select the queue default and database db1.
Enter the following SQL statement in the job editor window and click Execute.
```
create table table1 (id int, name string) using csv options (path 'obs://obs1/sampledata.csv');
```
After the table is created, click the Databases tab and then select db1. The created table table1 is displayed in the lower part of the Table area.

Step 7: Querying Data

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

In the Table area 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. The following statement shows how to query 1,000 records in the table1 table:
```
select * from db1.table1 limit 1000;
```
Click Execute. The system starts the query.
After the SQL statement is executed successfully, you can view the query result in View Result under the SQL job editing window.