Introduction to DLI Beeline

DLI Beeline is a command line tool used to connect to DLI. Powered on DLI JDBC, DLI Beeline provides the functions of SQL command interaction and batch SQL script execution.

Initial Preparations

Before using DLI Beeline, perform the following operations:

1. Getting authorized.

   DLI uses the Identity and Access Management (IAM) to implement fine-grained permissions for your enterprise-level tenants. IAM provides identity authentication, permissions management, and access control, helping you securely access to your public cloud resources.

   With IAM, you can use your public cloud account to create IAM users for your employees, and assign permissions to the users to control their access to specific resource types.

   Currently, roles (coarse-grained authorization) and policies (fine-grained authorization) are supported. For details about permissions and authorization operations, see the Data Lake Insight User Guide.

2. Create a queue. Set Queue Type to SQL Queue, which is the computing resource of the SQL job.
   You can create a queue on the Overview, SQL Editor, or Queue Management page.

   • In the upper right corner of the Dashboard page, click to create a queue.
   • To create a queue on the Queue Management page:
     1. In the navigation pane of the DLI management console, choose Queue Management.
     2. In the upper right corner of the Queue Management page, click to create a queue.
   • To create a queue on the SQL Editor page:
     1. On the top menu bar of the DLI management console, click SQL Editor.
     2. On the left pane of the displayed Job Editor page, click . Click to the right of Queues.
   

   If the user who creates the queue is not an administrator, the queue can be used only after being authorized by the administrator. For details about how to assign permissions, see the Data Lake Insight User Guide.

Downloading the DLI Client Tool

You can download the DLI client tool from the DLI management console.

1. Log in to the DLI management console.
2. Click SDK Download in the Common Links area on the right of the Overview page.
3. On the DLI SDK DOWNLOAD page, click huaweicloud-dli-clientkit-<version> to download the DLI client tool.
   

   The Beeline client is named huaweicloud-dli-clientkit-<version>-bin.tar.gz, which can be used in Linux and depends on JDK 1.8 or later.

Connecting to the DLI Server Using DLI Beeline

Ensure that you have installed JDK of 1.8 or a later version and configured environment variables on the computer where DLI Beeline is installed. You are advised to use DLI Beeline on the computer running the Linux OS.

1. Download and decompress huaweicloud-dli-clientkit-<version>-bin.tar.gz. In this step, set version to the actual version.
2. Go to the directory where dli-beeline-<version>-bin.tar.gz is decompressed. In the directory, there are three subdirectories bin, conf, and lib, which respectively store the execution scripts, configuration files, and dependency packages related to DLI Beeline.
3. Go to the conf directory, rename connection.properties.template as connection.properties, and set connection parameters.
   

   For details about the connection parameters, see Table 2-Database connection parameters and Table 3-Attribute items in Submitting a Job Using JDBC.

4. Enter the bin directory, start the beeline script to execute the SQL statement, as shown in the following:

   %bin/beeline
   Start Beeline
   Welcome to DLI service !
   beeline> !connect
   Connecting from the default connection.properties
   Connecting to jdbc:dli://dli.cn-north-1.myhuaweicloud.com/8fc20d97a4444cafba3c3a8639380003
   Connected to: DLI service
   jdbc:dli://dli.cn-north-1.myhuaweicloud... (not set)> show databases;
   +--------------------+
   |    databaseName    |
   +--------------------+
   | bjhk               |
   | db_xd              |
   | dimensions_adgame  |
   | odbc_db            |
   | sdk_db             |
   | tpch_csv_1024      |
   | tpch_csv_4g_new    |
   | tpchnewtest        |
   | tpchtest           |
   | xunjian            |
   +--------------------+
   10 rows selected (0.338 seconds)

   You can also set connection parameters by using the CLI when starting the DLI Beeline script. If the connection parameters are incomplete, DLI Beeline will prompt you to supplement related information.

   %bin/beeline -u 'jdbc:dli://dli.cn-north-1.myhuaweicloud.com/8fc20d97a4444cafba3c3a8639380003? authenticationmode=aksk'
   Start Beeline
   Connecting to jdbc:dli://dli.cn-north-1.myhuaweicloud.com/8fc20d97a4444cafba3c3a8639380003?usehttpproxy=true;proxyhost=10.186.60.154;proxyport=3128;authenticationmode=aksk
   Enter region name: cn-north-1
   Enter service name: DLI
   Enter access key(AK): <real access key>
   Enter secret key(SK): ****************************************
   Enter queue name: default
   Connected to: DLI service
   Welcome to DLI service !
   jdbc:dli://dli.cn-north-1.myhuaweicloud... (not set)> 

Commands Supported by DLI Beeline

DLI Beeline supports a series of commands. Each command starts with an exclamation mark (!). for example,!. connect. For details, see Table 1.

Command Line Options Supported by DLI Beeline

Table 2 lists the startup command line options supported by DLI Beeline.

Output Formats of Query Result

DLI Beeline supports multiple output formats for the query result. The output format can be specified by running the !outputformat command. DLI Beeline supports the following output formats: table, vertical, csv2, dsv, tsv2, xmlattr, and xmlelements.

• table

  The result in the table format is displayed in a table. For example:

  !outputformat table

  select id, value, comment from test_table;

  +-----+---------+-----------------+
  | id  |  value  |     comment     |
  +-----+---------+-----------------+
  | 1   | Value1  | Test comment 1  |
  | 2   | Value2  | Test comment 2  |
  | 3   | Value3  | Test comment 3  |
  +-----+---------+-----------------+

• vertical

  In the vertical format, the result data is organized by rows and each attribute is displayed in key-value format. For example:

  !outputformat vertical

  select id, value, comment from test_table;

  id       1
  value    Value1
  comment  Test comment 1
  id       2
  value    Value2
  comment  Test comment 2
  id       3
  value    Value3
  comment  Test comment 3

• csv2

  Store table data (digits and texts) in plain text and use commas (,) as separators. For example:

  !outputformat csv2

  select id, value, comment from test_table;

  id,value,comment
  1,Value1,Test comment 1
  2,Value2,Test comment 2
  3,Value3,Test comment 3

• dsv

  Each line stores a record. Fields in each record are separated by tabs. For example:

  !outputformat dsv

  select id, value, comment from test_table;

  id|value |comment
  1 |Value1|Test comment 1
  2 |Value2|Test comment 2
  3 |Value3|Test comment 3

• tsv2

  Each line stores a record. Fields in each record are separated by spaces. For example:

  !outputformat tsv2

  select id, value, comment from test_table;

  id value      comment
  1  Value1Test comment 1
  2  Value2Test comment 2
  3  Value3Test comment 3

• xmlattr

  Function used to set attributes in the XML element returned by the SQL query. For example:

  !outputformat xmlattr

  select id, value, comment from test_table;

  <resultset>
    <result id="1" value="Value1" comment="Test comment 1"/>
    <result id="2" value="Value2" comment="Test comment 2"/>
    <result id="3" value="Value3" comment="Test comment 3"/>
  </resultset>

• xmlelements

  Function for converting a relationship value to an XML element. The format is <elementName>value</elementName>. For example:

  !outputformat xmlelements

  select id, value, comment from test_table;

  <resultset>
    <result>
      <id>1</id>
      <value>Value1</value>
      <comment>Test comment 1</comment>
    </result>
    <result>
      <id>2</id>
      <value>Value2</value>
      <comment>Test comment 2</comment>
    </result>
    <result>
      <id>3</id>
      <value>Value3</value>
      <comment>Test comment 3</comment>
    </result>
  </resultset>