Scenario

In Windows, you can use an ODBC application to connect to the DLI server and submit jobs.

Precautions

OS requirement: Windows 2012 or later

Prerequisites

Before using ODBC, 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.

Procedure

1. Decompress the installation package.

   Decompress huaweicloud-dli-odbc-<version>.zip to any directory of the system.

2. Install the ODBC driver.
   1. Double-click the huaweicloud-dli-odbc-<version>-setup.exe file in the decompression directory.
   2. Click Next to install the ODBC driver.
      Figure 1 ODBC driver installation wizard
      
      1. Specify the installation path, which is C:\huaweicloud-dli-odbc by default. Then, click Next.
         Figure 2 Selecting the installation path
         
         

         You are advised to use the default installation path. If you change the installation path to another one, you need to manually modify the log configuration file.

      2. Specify the start menu folder and click Next.
         Figure 3 Selecting the start menu folder
         
      3. After confirming the installation path and start menu folder, click Install.
         Figure 4 Confirming the installation path and start menu folder
         
      4. Click Finish.
         Figure 5 Installation completion
         

3. (Optional) Configure log parameters.
   

   This step is required if the ODBC driver installation directory is not C:\huaweicloud-dli-odbc.

   1. Open the log.properties file in the driver installation directory, for example: C:\xxx\windows\log.properties.
   2. Change the log output path. For example: log4cplus.appender.DLILog.File=C:\xxx\windows\log\dli_odbc.log.
      

      Set the log output path based on the actual installation path. The log directory in the installation path is recommended.

4. Configure a data source.
   1. Open Control Panel. Click Administrative Tools. Double-click ODBC Data Sources (32-bit) to start the 32-bit ODBC data source manager.
      

      You can start the 32-bit ODBC data source manager using the following programs:

      • For a 64-bit Windows OS, use C:\Windows\SysWOW64\odbcad32.exe.
      • For a 32-bit Windows OS, use C:\Windows\System32\odbcad32.exe.
   2. Add a data source.

      To add a user data source, click Add on the User DSN page. To add a system data source, click Add on the System DSN page.

      Figure 6 ODBC data source management
      
   3. Select Huawei Cloud DLI Driver and click Finish.
      Figure 7 Creating a data source
      
   4. Set the ODBC driver parameters and click OK. For details about the ODBC driver parameters, see Table 1.
      Figure 8 DLI ODBC driver configuration
      
      

      • All parameters, except for DSN, can be configured through the ODBC connection parameters in the application. If a parameter is configured during both the ODBC connection parameter configuration and the data source configuration, the ODBC driver preferentially uses the configuration for this parameter in the connection parameter configuration.
      • If there are multiple configurations in the Extra Configuration area, use the format of KEY1=VALUE1; KEY2=VALUE2.

      Table 1 DLI ODBC driver configuration parameters

      Item	Mandatory	Default Value	Description
      DSN	Yes	-	Name of the ODBC data source, which is used in the connection string of the application.
      REGION	Yes	-	Region of interconnected DLI, for example: cn-north-1.
      HOST	No	-	Domain name of interconnected DLI.
      PROJECTID	Yes	-	Project ID used by a user to access DLI.
      AUTHMODE	Yes	-	Authentication mode used to connect to DLI. Currently, token authentication and AK/SK authentication are supported. For details, see Performing Authentication. Value 0 indicates that token authentication is used. Value 1 indicates that AK/SK authentication is used.
      ACCOUNT	No	-	Account name for logging in to the public cloud. This parameter is mandatory when AUTH MODE is set to TOKEN.
      USER	No	-	IAM usb-user name for logging in to the public cloud. This parameter is mandatory when AUTH MODE is set to TOKEN. If a tenant account is used for logging in to the public cloud, the value of this parameter is the same as that of ACCOUNT.
      PASSWORD	No	-	User password for logging in to the public cloud. This parameter is mandatory when AUTH MODE is set to TOKEN.
      AK	No	-	Access key of a user. This parameter is mandatory when AUTH MODE is set to AK/SK.
      SK	No	-	Secret key of a user. This parameter is mandatory when AUTH MODE is set to AK/SK.
      DATABASE	No	default	Name of the database used by a user.
      QUEUENAME	No	default	Name of the queue used by a user.
      USEPROXY	No	0	Whether to use an agency to access DLI. Value 0 indicates that no agency is used. Value 1 indicates that an agency is used. This parameter is configured in Extra Configuration.
      PROXYHOST	No	-	Proxy server address. This parameter is configured in Extra Configuration.
      PROXYPORT	No	-	Proxy server port. This parameter is configured in Extra Configuration.
      JOBTIMEOUT	No	300	Timeout interval for submitting a DLI job, in the unit of seconds. This parameter is configured in Extra Configuration.

   5. After the configuration is completed, view the configured data source in the driver manager.
      Figure 9 Viewing the configured data source
      

Example

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.Odbc;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            string connectionString = "DSN=DLIODBCSysDS;AUTHMODE=0;ACCOUNT=xxx;USER=xxx;PASSWORD=xxx";

            string queryString = "show tables;";
            OdbcCommand command = new OdbcCommand(queryString);

            using (OdbcConnection connection = new OdbcConnection(connectionString))
            {

                OdbcDataReader reader;
                try
                {
                    command.Connection = connection;

                    connection.Open();

                    reader = command.ExecuteReader();

                    while (reader.Read())
                    {
                        Console.WriteLine("\t{0}\t{1}\t{2}", 
reader[0], reader[1], reader[2]);
                    }

                    reader.Dispose();
                    reader.Close();

                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }
            }

            command.Dispose();
        }
    }
}

1. In this example, C# sample code is used.
2. In actual conditions, replace the DSN specified by string connectionString in the example code with the data source name configured in 4.