Import data using X2Doris

Important notes

• The server where you install X2Doris must be a node in your Hadoop cluster or have a Hadoop gateway environment. This means the server can run Hadoop and Hive commands and access the Hadoop and Hive clusters normally.

• The machine where you deploy X2Doris must have Hadoop environment variables configured. Set HADOOP_HOME, HADOOP_CONF_DIR, and HIVE_CONF_DIR. Example:

  Important

  The HADOOP_CONF_DIR or HIVE_CONF_DIR directory must contain hive-site.xml.

  export HADOOP_HOME=/opt/hadoop # Hadoop installation directory
  export HADOOP_CONF_DIR=/etc/hadoop/conf # Hadoop configuration directory
  export HIVE_HOME=$HADOOP_HOME/../hive
  export HIVE_CONF_DIR=$HADOOP_HOME/conf
  export HBASE_HOME=$HADOOP_HOME/../hbase
  export HADOOP_HDFS_HOME=$HADOOP_HOME/../hadoop-hdfs
  export HADOOP_MAPRED_HOME=$HADOOP_HOME/../hadoop-mapreduce
  export HADOOP_YARN_HOME=$HADOOP_HOME/../hadoop-yarn

Installation environment

This topic uses Linux as an example. Choose the appropriate commands for your system and environment.

Procedure

1. Select an X2Doris version.

   With Spark installed

   Go to the jars directory in the Spark installation directory, locate JAR files that start with spark-yarn, and identify the Scala version that corresponds to the Spark dependency package. The following example shows how to do this:

   For spark-yarn_2.12-3.2.4.jar, the corresponding Scala version is 2.12.

   

   Without Spark installed

   Choose the X2Doris package for Scala 2.12.

2. Download X2Doris.

   • x64 (Spark 3)

     wget https://enterprise-doris-releases.oss-cn-beijing.aliyuncs.com/x2doris/v1.2.2/selectdb-x2doris-v1.2.2_2.12-x86-bin.tar.gz

   • ARM64 (Spark 3)

     wget https://enterprise-doris-releases.oss-cn-beijing.aliyuncs.com/x2doris/v1.2.2/selectdb-x2doris-v1.2.2_2.12-arm64-bin.tar.gz

   For more versions, see X2Doris tool.

3. Extract the package.

   • x64 (Spark 3)

     tar -zxvf selectdb-x2doris-v1.2.2_2.12-x86-bin.tar.gz

   • ARM64 (Spark 3)

     tar -zxvf selectdb-x2doris-v1.2.2_2.12-arm64-bin.tar.gz

4. Initialize metadata.

   X2Doris supports two metadata storage options: the built-in H2 database and a self-managed MySQL database. We recommend MySQL.

   Self-managed MySQL

   If you need persistent metadata storage, use MySQL.

   1. Install MySQL.

      sudo yum install mysql

   2. Log on to MySQL.

      mysql -h<IP> -P<port> -u<user_name> -p<password>;

   3. Run scripts.

      1. Run mysql-schema.sql to initialize table schemas.

         abs_path is the absolute path to the mysql-schema.sql file in the installation directory of X2Doris.

         source <abs_path>/mysql-schema.sql;

         After running this script, MySQL creates a database named x2doris. Run show databases; to verify.

      2. Run mysql-data.sql to initialize table data.

         abs_path is the absolute path to the mysql-data.sql file in the X2Doris installation directory.

         source <abs_path>/mysql-data.sql;

   4. Change X2Doris to use MySQL.

      Go to the conf directory in your X2Doris installation. Edit application.yml and set spring.profiles.active to mysql.

      vim application.yml

      

   5. Configure MySQL connection details.

      Go to the conf directory in your X2Doris installation. Edit application-mysql.yml to configure the data source.

      vim application-mysql.yml

      

   H2

   The default H2 database stores data in local files.

5. Start X2Doris.

   Go to the bin directory in your X2Doris installation. Run bash startup.sh.

Prerequisites

• Add the IP addresses of your source data and the X2Doris server to the ApsaraDB for SelectDB whitelist. For details, see Configure the whitelist.

• The access port for X2Doris is now open.

  Note

  The default port is 9091. Check application.yml in your X2Doris installation directory for the actual port number.

Notes

If your data source is Hive, check the environment on the machine where X2Doris is installed. For environment requirements, see Environment requirements.

Procedure

1. Configure how to retrieve Hive metadata.

   Edit the X2Doris configuration file conf/application-hive.yaml to set how to retrieve Hive metadata.

   vim application-hive.yaml

   

   Parameter descriptions

   Parameter Name	Description
   client	How to get Hive metadata. Metastore: Default method. Use this if your Hive is standard Apache Hive. JDBC: If you cannot use the metastore approach because of permission restrictions or other issues, you can use the JDBC approach and set the client to JDBC. DLF: Use this if your Hive is Alibaba Cloud Hive. Set dlf.
   jdbc	If the client is configured with JDBC, you must configure the relevant JDBC parameters. jdbcurl: JDBC connection string for your Hive source. userName: Hive username. password: Logon password.
   aliyun.dlf	If the client is configured to use DLF, you must also configure its parameters. accesskeyId: Alibaba Cloud account AccessKey ID. accessKeySecret: Alibaba Cloud account AccessKey secret. An endpoint is the domain name for accessing the external services of DLF. regionId: Region where the DLF data center is located. catalogId: DLF data catalog.

2. Log on to the X2Doris console.

   Default URL: http://<IP>:9091.

   Note

   • IP is the IP address of the X2Doris server.

   • 9091 is the default port. If you changed it in application.yml, use your custom port.

   • Default username: admin. Default password: admin.

3. Configure system settings.

   In the X2Doris console, click System Settings in the navigation pane on the left. In the System Settings panel, complete the following steps.

   1. Configure Hadoop user: Click Edit to the right of Hadoop user, enter the Hadoop user in the input field, and click Submit.

   2. Configure Spark Home: Click Edit to the right of Spark Home, enter the Spark Home in the input box, and click Submit.

   3. Set Hive metastore uris: Click Edit next to Hive metastore uris. Enter the URIs in the field. Click Submit.

      Note

      • Required when importing from Hive.

      • Get this value by running SET hive.metastore.uris; in the Hive console.

   4. Set Target doris info: Click Edit next to Target doris info. In the Doris/SelectDB Cloud Target Endpoint dialog box, configure the following items. Click OK after you finish.

      Configuration item	Description
      HTTP Nodes	HTTP endpoint. Format: <ip>:<port>. Note You can obtain the VPC Endpoint (or Public Endpoint) and HTTP Port from the ApsaraDB for SelectDB console under Instance Details > Network Information. If the X2Doris instance and the ApsaraDB for SelectDB instance are in the same VPC, use the VPC endpoint. Otherwise, use the public endpoint. Example: selectdb-cn-4xl3jv1****.selectdbfe.rds.aliyuncs.com:8080
      MySQL Nodes	JDBC connection string. Format: <ip>:<port>. Note You can obtain the VPC Address (or Public Network Address) and HTTP Protocol Port from the Instance Details > Network Information page in the ApsaraDB for SelectDB console. If the X2Doris instance and the ApsaraDB for SelectDB instance are in the same VPC, use the VPC endpoint. Otherwise, use the public endpoint. Get the VPC endpoint (or public endpoint) and MySQL port from the ApsaraDB for SelectDB console: Instance Details > Network Information. Example: selectdb-cn-4xl3jv1****.selectdbfe.rds.aliyuncs.com:9030
      User	Username for your ApsaraDB for SelectDB instance.
      Password	Password for your ApsaraDB for SelectDB instance.

4. Create a job.

   1. In the navigation pane on the left, click Job Center to open the Job Center page.

   2. Hover over New Job in the upper-right corner of the Job Center list. Select your data source to open the job configuration page.

      1. In the left area of the page, select your source database.

      2. Configure Field mapping. Click Next.

         Configuration item	Description
         DORIS field type	The system automatically maps Doris and Hive field types. Note Automatic detection is enabled by default. Specify the Doris table data type, especially for STRING fields.
         DUPLICATE KEY	Columns used to sort underlying data. Note At least one column must be set.
         DISTRIBUTED BY	Columns used to bucket underlying data. Note At least one column must be set.

      3. Configure Partition mapping.

         If the partition field in your Hive table is of type STRING, decide whether to convert the corresponding ApsaraDB for SelectDB target table field to a time type based on your actual data. If you need conversion, set the partition range.

         Configuration item	Description
         DORIS field type	The system automatically maps Doris and Hive field types. Note By default, the system automatically detects it. Specify the Doris table data type, especially for STRING fields.
         DUPLICATE KEY	Columns used to sort underlying data. Note At least one column must be set.
         DISTRIBUTED BY	Columns used to bucket underlying data. Note At least one column must be set.

      4. Create a table.

         If you already created the table in SelectDB, click Skip Table Creation to go to Job Settings.

         If you did not create the table in SelectDB, click Create Table. Review the Doris DDL statement. Click Create Doris Table. Click Next to go to Job Settings.

      5. Configure the following Job Settings items. Click Create Job.

         Configuration item	Description
         Job Name	Auto-generated name for the data import task.
         Job Tag	Auto-generated tag for the data import task.
         Master	Select local mode or YARN mode. This controls how X2Doris jobs run. Adjust based on your environment.
         YARN Queue	Queue resource used by Spark jobs.
         Resource Parameters	Memory parameters for Spark jobs, such as executor and driver core count and memory size. Adjust based on your environment.
         Batch Write	Batch size for data writes. Adjust based on your data volume. For large datasets, set this to 500000 or higher.
         Retry on Failure	Number of retries for failed jobs. Increase this value if your network is unstable.
         Spark Parameters	Custom Spark parameters. Add them in key=value format. You can use standard Spark parameters. For details, see Application Properties.
         Connection Configurer	Optimization parameters for reading from your data source or writing to Doris. See the official Spark connector documentation for details. You can use standard Doris Spark connector parameters. For details, see Common Configuration Items.

5. Start the sync job.

   1. In the navigation pane on the left, click Job Center.

   2. In the Job Center data list, find the target job and click the Start button in the Actions column.

   3. In the Start Job dialog box, configure the following items.

      Configuration item descriptions

      Configuration item	Description	Example value
      Query Condition	Filter condition for the data to import. Note Do not include the WHERE keyword. Only write the filter logic.	name='Alice'
      Clear Data	Delete existing data in the target table. Warning This operation is risky. Use with caution. OFF (default): Do not delete. ON: delete.	OFF

   4. Click OK.

6. View the job.

   1. In the navigation pane on the left, click Job Center to open the Job Center page.

   2. Find your job. View the Migration Progress and Execution Status.

Installation environment

This topic uses Linux as an example. Choose the appropriate commands for your system and environment.

Procedure

1. Select an X2Doris version.

   With Spark installed

   Go to the jars directory in the Spark installation directory, locate JAR files that start with spark-yarn, and identify the Scala version that corresponds to the Spark dependency package. The following example shows how to do this:

   For spark-yarn_2.12-3.2.4.jar, the corresponding Scala version is 2.12.

   

   Without Spark installed

   Choose the X2Doris package for Scala 2.12.

2. Download X2Doris.

   • x64 (Spark 3)

     wget https://enterprise-doris-releases.oss-cn-beijing.aliyuncs.com/x2doris/v1.2.2/selectdb-x2doris-v1.2.2_2.12-x86-bin.tar.gz

   • ARM64 (Spark 3)

     wget https://enterprise-doris-releases.oss-cn-beijing.aliyuncs.com/x2doris/v1.2.2/selectdb-x2doris-v1.2.2_2.12-arm64-bin.tar.gz

   For more versions, see X2Doris tool.

3. Extract the package.

   • x64 (Spark 3)

     tar -zxvf selectdb-x2doris-v1.2.2_2.12-x86-bin.tar.gz

   • ARM64 (Spark 3)

     tar -zxvf selectdb-x2doris-v1.2.2_2.12-arm64-bin.tar.gz

4. Initialize metadata.

   X2Doris supports two metadata storage options: the built-in H2 database and a self-managed MySQL database. We recommend MySQL.

   Self-managed MySQL

   If you need persistent metadata storage, use MySQL.

   1. Install MySQL.

      sudo yum install mysql

   2. Log on to MySQL.

      mysql -h<IP> -P<port> -u<user_name> -p<password>;

   3. Run scripts.

      1. Run mysql-schema.sql to initialize table schemas.

         abs_path is the absolute path to the mysql-schema.sql file in the installation directory of X2Doris.

         source <abs_path>/mysql-schema.sql;

         After running this script, MySQL creates a database named x2doris. Run show databases; to verify.

      2. Run mysql-data.sql to initialize table data.

         abs_path is the absolute path to the mysql-data.sql file in the X2Doris installation directory.

         source <abs_path>/mysql-data.sql;

   4. Change X2Doris to use MySQL.

      Go to the conf directory in your X2Doris installation. Edit application.yml and set spring.profiles.active to mysql.

      vim application.yml

      

   5. Configure MySQL connection details.

      Go to the conf directory in your X2Doris installation. Edit application-mysql.yml to configure the data source.

      vim application-mysql.yml

      

   H2

   The default H2 database stores data in local files.

5. Start X2Doris.

   Go to the bin directory in your X2Doris installation. Run bash startup.sh.

Prerequisites

• Add the IP addresses of your source data and the X2Doris server to the ApsaraDB for SelectDB whitelist. For details, see Configure the whitelist.

• The access port for X2Doris is now open.

  Note

  The default port is 9091. Check application.yml in your X2Doris installation directory for the actual port number.

Procedure

1. Log on to the X2Doris console.

   Default URL: http://<IP>:9091.

   Note

   • The IP address is that of the server where X2Doris is installed.

   • 9091 is the default port. If you changed it in application.yml, use your custom port.

   • Default username: admin. Default password: admin.

2. Configure system settings.

   In the X2Doris console, click System Settings in the navigation pane on the left. In the System Settings panel, complete the following steps.

   1. (Optional) Configure Hadoop user: Click Edit to the right of Hadoop user, enter the Hadoop user in the input box, and click Submit.

   2. (Optional) Configure Spark Home: Click Edit to the right of Spark Home, enter the Spark Home in the input field, and click Submit.

   3. (Optional) Set Hive metastore uris: Click Edit next to Hive metastore uris. Enter the URIs in the field. Click Submit.

   4. Configure Target doris info: Click Edit to the right of Target doris info. In the Doris/SelectDB Cloud Target Endpoint Information pop-up dialog, configure the following configuration items. When you finish configuring, click OK.

      Configuration item	Description
      HTTP Nodes	HTTP endpoint. Format: <ip>:<port>. Note You can obtain the VPC Address (or Public Address) and the HTTP Port from the Instance Details > Network Information page in the ApsaraDB for SelectDB console. If the X2Doris instance and the ApsaraDB for SelectDB instance are in the same VPC, use the VPC endpoint. Otherwise, use the public endpoint. Example: selectdb-cn-4xl3jv1****.selectdbfe.rds.aliyuncs.com:8080
      MySQL Nodes	JDBC connection string. Format: <ip>:<port>. Note You can obtain the VPC Address (or Public Network Address) and HTTP Port from the ApsaraDB for SelectDB console's Instance Details > Network Information section. If the X2Doris instance and the ApsaraDB for SelectDB instance are in the same VPC, use the VPC endpoint. Otherwise, use the public endpoint. Get the VPC endpoint (or public endpoint) and MySQL port from the ApsaraDB for SelectDB console: Instance Details > Network Information. Example: selectdb-cn-4xl3jv1****.selectdbfe.rds.aliyuncs.com:9030
      User	Username for your ApsaraDB for SelectDB instance.
      Password	Password for your ApsaraDB for SelectDB instance.

3. Add a Data Source.

   1. In the navigation pane on the left, click Data Sources to open the Data Sources page.

   2. Hover over Add Data Source in the upper-right corner of the Data Sources list. Select your data source.

   3. Complete the configuration in the panel. Click OK.

4. Create a job.

   1. In the navigation pane on the left, click Job Center to open the Job Center page.

   2. Hover over New Job in the upper-right corner of the Job Center list. Select your data source to open the job configuration page.

      1. In the Select Data Source dialog box, find your data source. Click the icon in the Use column.

      2. In the left area of the page, select your source data.

         Note

         • Select one table, multiple tables, or an entire database.

         • If you select one table, the DDL statement appears on the right side of the page.

         • If you select multiple tables, the system generates DDL using the default strategy. No DDL appears on the right side.

      3. Click the appropriate button to go to Job Settings.

         • Skip Table Creation: Use this if the table already exists in the ApsaraDB for SelectDB target.

         • Create Table Only: Use this to create a table without creating a migration job.

         • Create Table and Job: Use this to create both a table and a migration job.

      4. Configure the following Job Settings items. Click Create Job.

         Configuration item	Description
         Job Name	Name for the data migration task. Note Auto-generated. No manual input required.
         Job Tag	Tag for the data import task. Note Auto-generated. No manual input required.
         Master	How X2Doris jobs run. Note If your X2Doris server has Hadoop and YARN, set this to yarn. Otherwise, set it to local.
         YARN Queue	Queue resource used by Spark jobs.
         Resource Parameters	Memory parameters for Spark jobs. Examples: Executor and driver core count and memory size. For more parameters, see Spark Configuration.
         Batch Write	Batch size for data writes. For large datasets, set this to 500000 or higher.
         Retry on Failure	Number of retries for failed jobs. If your network is unstable, increase this value.
         Spark Parameters	Custom Spark parameters. Note Add extra Spark parameters in key=value format. For more parameters, see Application Properties.
         Connection Configurer	Optimization parameters for reading from your data source or writing to SelectDB. For example, if your data source is Doris, see Spark Doris Connector.

5. Start the sync job.

   1. In the navigation pane on the left, click Job Center.

   2. In the Job Center data list, find the target job and click the Start button in the Actions column.

   3. In the Start Job dialog box, configure the following items.

      Configuration item descriptions

      Configuration item	Description	Example value
      Query Condition	Filter condition for the data to import. Note Do not include the WHERE keyword. Only write the filter logic.	name='Alice'
      Clear Data	Delete existing data in the target table. Warning This operation is risky. Use with caution. OFF (default): Do not delete. ON: delete.	OFF

   4. Click OK.

   1. In the navigation pane on the left, click Job Center to open the Job Center page.

   2. Find your job. View the Migration Progress and Execution Status.

6. View the job.

   1. In the navigation pane on the left, click Job Center to open the Job Center page.

   2. Find your job. View the Migration Progress and Execution Status.