Exporting Doris Data to HDFS

Notes and Constraints

• You are not advised to export a large amount of data at a time. It is recommended that a maximum of dozens of GB data be exported for an export job. Large exports result in more junk files and higher retry costs.
• If a table contains a large amount of data, you are advised to export the data by partition.
• If the FE is restarted or an active/standby switchover occurs when the export task is running, the task will fail and you will need to submit it again.
• If the export job fails, the temporary directory __doris_export_tmp_xxx generated in the remote storage and the generated files will not be deleted. You need to manually delete them.
• If the export job is successfully executed, the __doris_export_tmp_xxx directory generated in the remote storage may be retained or cleared based on the file system semantics of the remote storage.

  For example, in object storage (supporting the S3 protocol), after the last file in a directory is moved through the rename operation, the directory is also deleted. If the directory is not cleared, you can manually clear it.

• After the export is complete (successful or failed), if the FE is restarted or an active/standby switchover occurs, some job information displayed in SHOW EXPORT will be lost and cannot be viewed.
• The Export job exports only Base table data and does not export Rollup Index data.
• The Export job scans data and occupies I/O resources, which may affect the query delay of the system.

Syntax

• Exporting Doris Data to HDFS
  • Kerberos authentication is enabled for the cluster (in security mode)

    EXPORT TABLE db1.tbl1
    PARTITION (p1,p2)
    [WHERE [expr]]
    TO "hdfs://IP address of the active NameNode instance:RPC port number/tmp/export/"
    PROPERTIES
    (
    "label" = "mylabel",
    "column_separator"=",",
    "columns" = "col1,col2",
    "exec_mem_limit"="2147483648",
    "timeout" = "3600"
    )
    WITH BROKER "broker_name"
    (
    "hadoop.security.authentication"="kerberos",
    "kerberos_principal"=${doris_keytab_principal}",
    "kerberos_keytab"="/home/omm/doris_keytab/doris.keytab",
    "dfs.nameservices" = "hacluster",
    "dfs.ha.namenodes.hacluster" = "37,36",
    "dfs.namenode.rpc-address.hacluster.37" = "IP address of the active NameNode instance:RPC port number",
    "dfs.namenode.rpc-address.hacluster.36" = "IP address of the standby NameNode instance:RPC port number",
    "dfs.client.failover.proxy.provider.hacluster" = "org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider"
    );

  • Kerberos authentication is disabled for the cluster (in normal mode)

    EXPORT TABLE db1.tbl1
    PARTITION (p1,p2)
    [WHERE [expr]]
    TO "hdfs://IP address of the active NameNode instance:RPC port number/tmp/export/"
    PROPERTIES
    (
    "label" = "mylabel",
    "column_separator"=",",
    "columns" = "col1,col2",
    "exec_mem_limit"="2147483648",
    "timeout" = "3600"
    )
    WITH BROKER "broker_name"
    (
    "username" = "user",
    "password" = "passwd",
    "dfs.nameservices" = "hacluster",
    "dfs.ha.namenodes.hacluster" = "37,36",
    "dfs.namenode.rpc-address.hacluster.37" = "IP address of the active NameNode instance:RPC port number",
    "dfs.namenode.rpc-address.hacluster.36" = "IP address of the standby NameNode instance:RPC port number",
    "dfs.client.failover.proxy.provider.hacluster" = "org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider"
    );

  To view the IP addresses of the active and standby NameNode instances, log in to Manager and choose Cluster > Services > HDFS > Instances.

  You can log in to Manager, choose Cluster > Services > HDFS > Configurations, and search for dfs.namenode.rpc.port to view the RPC port.

  Table 1 describes other parameters.

  Table 1 Parameters in the command for exporting Doris data to HDFS

  Parameter	Description
  label	Identifier of the export job. You can use this identifier to view the job status.
  column_separator	Column separator. The default value is \t. Invisible characters are supported, for example, \x07.
  columns	Columns to be exported. Use commas (,) to separate them. If this parameter is not set, all columns in the table are exported by default.
  line_delimiter	Line separator. The default value is \n. Invisible characters are supported, for example, \x07.
  exec_mem_limit	Memory usage limit of a query plan on a single BE in an export job. The default value is 2 GB. The unit is byte.
  timeout	Job timeout interval. The default value is 2 hours. The unit is second.
  tablet_num_per_task	Maximum number of shards allocated to each query plan. The default value is 5.
  broker_name	Broker name. You can run the show broker; command on the MySQL client to view the broker name.
  kerberos_principal	Principal of the keytab file for accessing the Hadoop cluster. For versions earlier than MRS 3.5.0, the value is doris/hadoop.hadoop.com@HADOOP.COM. For MRS 3.5.0 and later versions, the format is doris/hadoop.${System domain name in lowercase}@${System domain name}. To obtain the system domain name, log in to Manager, choose System > Permission > Domain and Mutual Trust, and view the value of Local Domain. For example, if the system domain name is A39A7DF8_953D_4772_B909_035A594FFA55.COM, the value of this parameter is doris/hadoop.a39a7df8_953d_4772_b909_035a594ffa55.com@A39A7DF8_953D_4772_B909_035A594FFA55.COM.
  kerberos_keytab	keytab file used to access the Hadoop cluster. The file is stored in the ${BIGDATA_HOME}/FusionInsight_Doris_*/install/FusionInsight-Doris-*/doris-be/bin/doris.keytab directory on the FE node. You need to copy the keytab file to all Broker nodes, for example, in /home/omm/doris_keytab, and run the following command to set the owner group of the doris.keytab file: chown omm:wheel /home/omm/doris_keytab -R
  dfs.nameservices	Name of the NameService in the cluster. The value can be found in hdfs-site.xml, which is in the ${BIGDATA_HOME}/FusionInsight_HD_*/1_*_NameNode/etc directory where NameNode is deployed.
  dfs.ha.namenodes.hacluster	Prefix of the NameService in the cluster, containing two values. The value can be found in hdfs-site.xml, which is in the ${BIGDATA_HOME}/FusionInsight_HD_*/1_*_NameNode/etc directory where NameNode is deployed.
  dfs.client.failover.proxy.provider.hacluster	Java class for the HDFS client to connect the nodes in Active state in the cluster. The value is org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider.

Prerequisite

• A cluster containing the Doris service has been created, and all services in the cluster are running properly.
• The node to be connected to the Doris database can communicate with the MRS cluster.
• A user with the Doris management permission has been created.
  • Kerberos authentication is enabled for the cluster (in security mode)

    On FusionInsight Manager, create a human-machine user, for example, dorisuser, create a role with Doris administrator permission, and bind the role to the user.

    Log in to FusionInsight Manager as the new user dorisuser and change the initial password of the user.

  • Kerberos authentication is disabled for the cluster (in normal mode)

    After connecting to Doris as user admin, create a role with administrator permissions, and bind the role to the user.

• The MySQL client has been installed. For details, see Using the MySQL Client to Connect to Doris.

Example of Exporting Doris Data to HDFS

When executing an export job, you can add custom parameters in Related Configuration Parameters as required on the Doris configuration page to improve job export efficiency.

1. Log in to the node where MySQL is installed and run the following command to connect to the Doris database.

   If Kerberos authentication is enabled for the cluster (in security mode), run the following command.

   export LIBMYSQL_ENABLE_CLEARTEXT_PLUGIN=1

   Connect to the Doris database.

   mysql -uDatabase login user -pDatabase login password -PDatabase connection port -hIP address of the Doris FE instance

   • The database connection port is the query connection port of the Doris FE. To obtain the query connection port, you can log in to Manager, choose Cluster > Services > Doris > Configurations, and query the value of query_port of the Doris service.
   • To obtain the IP address of the Doris FE instance, log in to Manager of the MRS cluster and choose Cluster > Services > Doris > Instances to view the IP address of any FE instance.
   • You can also use the MySQL connection software or Doris web UI to connect the database.

2. Run the following command to create a database.

   create database if not exists example_db;

3. Run the following statement to create a table.

   CREATE TABLE example_db.test_export_tbl (
   `c1` int NOT NULL,
   `c2` int NOT NULL,
   `c3` string NOT NULL,
   `c4` date NOT NULL
   ) ENGINE=OLAP
   DUPLICATE KEY(`c1`, `c2`)
   DISTRIBUTED BY HASH(`c1`) BUCKETS 1;

4. Run the following command to insert data.

   insert into example_db.test_export_tbl values(1,1,1,"2020-02-21"),(2,2,2,"2020-03-21"),(3,3,3,"2020-04-21");

5. Run the following command to export data from the test_export_tbl table to HDFS.

   EXPORT TABLE example_db.test_export_tbl
   TO "hdfs://IP address of the active NameNode instance:RPC port number/tmp/export/"
   PROPERTIES
   (
   "label" = "label_exporthdfs_20230218031",
   "column_separator"=",",
   "columns" = "c1,c2,c3,c4",
   "exec_mem_limit"="2147483648",
   "timeout" = "3600"
   )
   with broker "broker_192_168_67_78"
   (
   "hadoop.security.authentication"="kerberos",
   "kerberos_principal"="${doris_keytab_principal}",
   "kerberos_keytab"="/home/omm/doris_keytab/doris.keytab",
   "dfs.nameservices" = "hacluster",
   "dfs.ha.namenodes.hacluster" = "37,36",
   "dfs.namenode.rpc-address.hacluster.37" = "IP address of the active NameNode instance:RPC port number",
   "dfs.namenode.rpc-address.hacluster.36" = "IP address of the standby NameNode instance:RPC port number",
   "dfs.client.failover.proxy.provider.hacluster" = "org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider"
   );

6. Run the following command to query the status of the export job.

   SHOW EXPORT;

Related Configuration Parameters

Log in to FusionInsight Manager, choose Cluster > Services > Doris > Configurations > FE(Role) > Customization, and add the parameters in Table 3 to the custom parameter fe.conf.customized.configs.