EXPORT
Description
Exports the data of a table to a specified location.
This is an asynchronous operation. The export result is returned after you submit the export task. You can use SHOW EXPORT to view the progress of the export task.
NOTICE
You can export data out of StarRocks tables only as a user who has the EXPORT privilege on those StarRocks tables. If you do not have the EXPORT privilege, follow the instructions provided in GRANT to grant the EXPORT privilege to the user that you use to connect to your StarRocks cluster.
Syntax
EXPORT TABLE <table_name>
[PARTITION (<partition_name>[, ...])]
[(<column_name>[, ...])]
TO <export_path>
[opt_properties]
WITH BROKER
[broker_properties]
Parameters
-
table_name
The name of the table. StarRocks supports exporting the data of tables whose
engine
isolap
ormysql
. -
partition_name
The partitions from which you want to export data. By default, if you do not specify this parameter, StarRocks exports the data from all partitions of the table.
-
column_name
The columns from which you want to export data. The sequence of columns that you specify by using this parameter can differ from the schema of the table. By default, if you do not specify this parameter, StarRocks exports the data from all columns of the table.
-
export_path
The location to which you want to export the data of the table. If the location contains a path, make sure that the path ends with a slash (/). Otherwise, the part following the last slash (/) in the path will be used as the prefix to the name of the exported file. By default,
data_
is used as the file name prefix if no file name prefix is specified. -
opt_properties
Optional properties that you can configure for the export task.
Syntax:
[PROPERTIES ("<key>"="<value>", ...)]
Property Description column_separator The column separator that you want to use in the exported file. Default value: \t
.line_delimiter The row separator that you want to use in the exported file. Default value: \n
.load_mem_limit The maximum memory that is allowed for the export task on each individual BE. Unit: bytes. The default maximum memory is 2 GB. timeout The amount of time after which the export task times out. Unit: second. Default value: 86400
, meaning 1 day.include_query_id Specifies whether the name of the exported file contains query_id
. Valid values:true
andfalse
. The valuetrue
specifies that the file name containsquery_id
, and the valuefalse
specifies that the file name does not containquery_id
. -
WITH BROKER
In v2.4 and earlier, input
WITH BROKER "<broker_name>"
to specify the broker you want to use. From v2.5 onwards, you no longer need to specify a broker, but you still need to retain theWITH BROKER
keyword. -
broker_properties
The information that is used to authenticate the source data. The authentication information varies depending on the data source. For more information, see BROKER LOAD.
Examples
Export all data of a table to HDFS
The following example exports all data of the testTbl
table to the hdfs://<hdfs_host>:<hdfs_port>/a/b/c/
path of an HDFS cluster:
EXPORT TABLE testTbl
TO "hdfs://<hdfs_host>:<hdfs_port>/a/b/c/"
WITH BROKER
(
"username"="xxx",
"password"="yyy"
);
Export the data of specified partitions of a table to HDFS
The following example exports the data of two partitions, p1
and p2
, of the testTbl
table to the hdfs://<hdfs_host>:<hdfs_port>/a/b/c/
path of an HDFS cluster:
EXPORT TABLE testTbl
PARTITION (p1,p2)
TO "hdfs://<hdfs_host>:<hdfs_port>/a/b/c/"
WITH BROKER
(
"username"="xxx",
"password"="yyy"
);
Export all data of a table to HDFS with column separator specified
The following example exports all data of the testTbl
table to the hdfs://<hdfs_host>:<hdfs_port>/a/b/c/
path of an HDFS cluster and specifies that commas (,
) is used as the column separator:
EXPORT TABLE testTbl
TO "hdfs://<hdfs_host>:<hdfs_port>/a/b/c/"
PROPERTIES
(
"column_separator"=","
)
WITH BROKER
(
"username"="xxx",
"password"="yyy"
);
The following example exports all data of the testTbl
table to the hdfs://<hdfs_host>:<hdfs_port>/a/b/c/
path of an HDFS cluster and specifies that \x01
(the default column separator supported by Hive) is used as the column separator:
EXPORT TABLE testTbl
TO "hdfs://<hdfs_host>:<hdfs_port>/a/b/c/"
PROPERTIES
(
"column_separator"="\\x01"
)
WITH BROKER;
Export all data of a table to HDFS with file name prefix specified
The following example exports all data of the testTbl
table to the hdfs://<hdfs_host>:<hdfs_port>/a/b/c/
path of an HDFS cluster and specifies that testTbl_
is used as the prefix to the name of the exported file:
EXPORT TABLE testTbl
TO "hdfs://<hdfs_host>:<hdfs_port>/a/b/c/testTbl_"
WITH BROKER;
Export data to AWS S3
The following example exports all data of the testTbl
table to the s3-package/export/
path of an AWS S3 bucket:
EXPORT TABLE testTbl
TO "s3a://s3-package/export/"
WITH BROKER
(
"aws.s3.access_key" = "xxx",
"aws.s3.secret_key" = "yyy",
"aws.s3.region" = "zzz"
);