Help Center > > Developer Guide> DLI Client Operation Guide> Submitting a Job Using Beeline

Submitting a Job Using Beeline

Updated at: Mar 18, 2020 GMT+08:00

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 secure access to your HUAWEI CLOUD resources.

    With IAM, you can use your HUAWEI 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.ap-southeast-1.myhuaweicloud.com/8fc20d97a4444cafba3c3a8639380003
    Connected to: DLI service
    jdbc:dli://dli.ap-southeast-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.ap-southeast-1.myhuaweicloud.com/8fc20d97a4444cafba3c3a8639380003? authenticationmode=aksk'
    Start Beeline
    Connecting to jdbc:dli://dli.ap-southeast-1.myhuaweicloud.com/8fc20d97a4444cafba3c3a8639380003?usehttpproxy=true;proxyhost=10.186.60.154;proxyport=3128;authenticationmode=aksk
    Enter region name: ap-southeast-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.ap-southeast-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.

Table 1 Commands Supported by DLI Beeline

Command

Description

!connect

This command is used to connect to DLI by setting connection parameters. If no parameters are specified, the default connection.properties file is loaded.

!help

This command is used to print the help document of the command line.

!history

This command is used to display the command execution history.

!outputformat

This command is used to set the output format of the query result. The available output formats include table, vertical, csv2, dsv, tsv2, xmlattr, and xmlelements. For details about each format, see Output Formats of Query Result.

!properties

This command is used to connect to DLI by loading the connection.properties file. This command delivers the same function as the !connect command, but allows you to specify the configuration file.

!quit

This command is used to exit the DLI Beeline session.

!run

This command is used to run a SQL statement. The method is as follows:

!run <scriptfile>

!save

This command is used to save the current session attributes to the beeline.properties file. These attributes will be automatically loaded when DLI Beeline is enabled next time.

!script

This command is used to save the executed commands to a file. Sample code is provided as follows:

!script /tmp/mysession.script

After this statement is executed, subsequent commands will be saved to /tmp/mysession.script.

Run the !script command again to end script recording. The recorded commands will be executed again if you run the following command:

!run /tmp/mysession.script

!set

This command is used to set the DLI Beeline variables. A command example is provided as follows:

!set color true

If no parameter is specified after !set, all variable values will be returned.

!sh

This command is used to run a Shell script. Sample code is provided as follows:

!sh <shellscript>

!sql

This command is used to run an SQL statement explicitly. In DLI Beeline, a statement without commands is converted into the !sql command by default. The SQL statement must end with a semicolon (;). Sample code is provided as follows:

!sql <sql>

!dliconf

This command is used to view the user-defined configurations of DLI.

Command Line Options Supported by DLI Beeline

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

Table 2 Command line options supported by DLI Beeline

Command Line Option

Description

-u <database URL>

Indicates the URL for connecting to DLI JDBC. The URL must be enclosed in single quotation marks ('). An example is provided as follows:

beeline –u db_URL

-e <query>

Indicates the SQL statements to be executed. Multiple statements can be entered, separated by semicolons (;). Each statement must be enclosed in single quotation marks (').

-f <file>

Indicates the script file to be executed.

--dliconf property=value

DLI property to be set.

--property-file=<property-file>

Indicates to obtain the attribute file in a specified mode and connect to DLI.

--help

Indicates to display help information about command line options.

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>

Did you find this page helpful?

Submit successfully!

Thank you for your feedback. Your feedback helps make our documentation better.

Failed to submit the feedback. Please try again later.

Which of the following issues have you encountered?







Please complete at least one feedback item.

Content most length 200 character

Content is empty.

OK Cancel